Can I use xp_shell with msde? Do the extended stored procedures get installed with msde? Thanks, *** Sent via Developersdex http://www.developersdex.com ***...more >>

Hi guys, I am developing an application that front-ends to SQL Server 2000 Enterprise. I am trying to find a way of setting a custom database property, to let my application know whether the application is currently connected to the development db, or the production db. Neither the appli...more >>

I have created a linked server (LAB) to an Access 2K DB on my local drive. From within a DB on the SQL server I started to create a view, and using the syntax: SELECT * FROM LAB...tblName tblName_1 It displays all the required data. When I attempt to save the view though, then I get problem...more >>

Long-running transactions are generally a bad idea. But what about this: A group in my company is creating a report queueing and generation engine. They want to create a server collect and queue reports requests in a table, and subsequently run reports at later times depending on certain pa...more >>

I guess my question is that in my sp how can I make sure wether a substring exists ina string or not.I'm using this now,is there something better : SELECT @tempString = REPLACE(UPPER(mySpParamter),'MEMBER','wow!') IF @tempString <> mySpParamter --This means that the MEMBER keyword is pa...more >>

Would really appreciate some help/ideas. The stored procedures for my application are all saved to a folder in the file system of my deeloper work station. What is the best way to loop through the folder and execute the sql (create procedure...)in each file? Thanks for any tips ...more >>

I have tempDB set up with filegroups. TempDb is four 10 GB files. Usually very little % of the files is actually used. Sometimes a have a run away process that increase the % of files used. I would like to write a SQL query to capture the SPID that increases the % of files used. I ...more >>

if i use: --code-- DECLARE @CMD VARCHAR(8000) SET @CMD = 'bcp "select * from TABLENAME" queryout C:\testfile.xls -c -S"' + @@servername + '" -T' exec master..xp_cmdshell @CMD --end code-- how could i put the column headers in this spreadsheet? or is there a better way of getting a sql ...more >>

How do I query the schema views (preferably) or sys tables to find all triggers on my db? tia chris...more >>

I just wanted to know does SQL server database support 'UNDO' SQL. For example: I have testing SQL statement, but it deleted my live data by accident. Is it possible to get them back by using UNDO method? Any informaiton is great appreciated,...more >>

Pardon my newbieness. I have four tables to join. I'll just call them Table1, Table2, Table3 and Table4. Table1 is the main table. I want to do outer joins pointing from fields in Table1 to fields in each of the other tables. So all records in Table1 regardless of whether they match in the ...more >>

Im running a scheduled task that uses dates to query last months records for billing. The @EndDate needs to be set to the Last day of the prior month at 11:59PM in this format, Apr 30 2005 11:59PM. How can I set the date in that format? Thank you in advance. select @EndDate = getdate()?...more >>

First of all, I DON'T want to convert the following query into the SQL92 equivalent, I just want to learn how to do it. Most of the left and right join examples I've found on the Net usually deal with 2 or three tables at the maximum. What happens if my query uses 7 tables and only two of t...more >>

Hello, I need to simulate a boolan data type in the database so users can user "alter mytable alter column myColumn sysBoolean" I created sysBoolean as BIT, what I need to do is create a rule or filter or translator which will allow users to use insert into mytable (myColumn) values (true) ...more >>

Hello NG, I have the following view: CarID | Hersteller | Typ | Label_Plane 1 DAF 1 XF 1 Coca-Cola 1 Pepsi n...more >>

Hi list, i wanto to use an ASP page to create tables from the devolopers intranet. Inside Code, i have the following conn string: set objRS = CreateObject("ADODB.Recordset") objcon.connectionstring = "Provider=SQLOLEDB.1;User ID=sa;Password=XXXXXX;Persist Security Info=True;Initial Catalog=...more >>

After doing a lot of research, I found out that you can't send html mail through SQL with Xp_sendmail. One way to do it is using CDONTS. I do not actually need full support of html, only basic formatting, like bolds and italics. can this be done with xp_sendmail without using cdonts? or do I h...more >>

Hello, Can triggers be written such that they update tables which are present on different databases onn different servers? Thanks, Reshma ...more >>

Are there any references and samples of writing extended stored procedures using vb.net/c#? Since we can't use vb or java, does the same restriction apply?...more >>

Hi I've these tables DB_Orders id domain ext srvc_id 1 pippo .it 1 2 pluto .comune.it 1 3 pippo null 2 4 minni .com 1 DB_Prezzi ext_id price level srvc_id 1 12 ...more >>

I am running an executable from a DTS paskage which opens a file with it's associated program and saves it as another format. It works fine if I run it manually but if I let the scheduled job try to run it, it hands and times out. I believe this is because the program is trying to write to t...more >>

Dear fellows, If I do this, no problem at all: select * into #test from tbl_periods But then, when I pretend launch this, I obtain a warning: select * from #test or this one: select * from dbo.tempdb.#test Server: Msg 208, Level 16, State 1, Line 1 Invalid object name '#test'. Dat...more >>

Does anyone have a script to perform natural sorting order for strings containing both numbers and characters? i.e. mw-1 mw-2 mw-3 mw-11 mw-12 ... What I am trying to avoid is mw-1 mw-11 mw-12 mw-2 mw-3 ... Thanks for your help. Archer ...more >>

Im running a scheduled task that uses dates to query last months records for billing. The @StartDate needs to be set to the 1st day of the prior month at midnight in this format, Apr 1 2005 12:00AM. How can I set the date in that format? Thank you in advance. select @StartDate = ??getda...more >>

Hi, I want my transactions to abort when a runtime error (i.e. foreign = key constraint violation) occurs. I currently accomplish this by = prefixing all of my queries with: SET XACT_ABORT ON Is there a way to set this option globally so that I don't have to = continually set thi...more >>

I wonder is it possible to see just one row returned with 217 datediff showing and not the 0. Quick SQL IF NOT EXISTS (select * from sysobjects where id = object_id(N'FAt_bse_events') and OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN CREATE TABLE FAt_bse_events ( dim_one_id int...more >>

When replicating between 2 sql 2000 databases, what port is used? Is it 1433? Best regards Trond ...more >>

I need to write an INSERT trigger so that i can force the value of a column of the newly inserted row. i.e. CRM software creates a new customer record, BUT due to a bug in this software (still waiting for a fix !!) the value of a certain column isnt being set correctly, therefore i need to force...more >>

I wanted to insert stored procedure result into my temp table. I tried 'SELECT * INTO FROM EXEC MYPROC @MYPARAM = 1', but it does not work. any information is great appreciated, ...more >>

i ...more >>

What would be a good way to identify all stored procedures in one db that are NOT in another db? Just trying to sync a development database with a production database - each has hundreds of stored procedures; I don't want to do this manually. Please note that I just want to identify the proced...more >>

hi I have to tables (T1 and T2) T1 NroTicket Date Vendor 0000 01/12/2000 1 0001 01/12/2000 2 T2 NroTicket Sales Vendor 0000 2000 1 0000 3500 2 ...more >>

What can I add to this query so that the resultset only displays the records in tempo where t.emplcode is null? I know I'm using legacy sql, but I'm trying to change to the new syntax and it's hard to get used to it. Also, the query I'm modifying is all in legacy (plus all the other queries), ...more >>

We currently have a web application (ASP/VBScript) that utilizes Access. We want to upsize to SQL Server, but I have found that some of our code contains string functions that do not have an equivalent in SQL Server. For example: sSql = "DELETE * FROM admin WHERE administrator_id =" & trim(...more >>

Hi, I am trying to run a update stored procedure where one of the fields is dynamic eg. UPDATE table SET field_ + @number = @a_value WHERE (key = @key_value) @number is chosen by the user and the field_## can be anything from field_01 to field_99. Is there a way i can do this? The ...more >>

I am having troubles with a query that may have to use an Outer Join. I would appreciate any help with this problem. I will pass two parameters to the stored procedure. @OldStnType and @NewStnType. I want to see which records exist in @OldStnType and not in @NewStnType. In this example: ...more >>

I have a products table that contains CategoryId column. Based on a productid, I want to be able to delete all the products which are in the same category as of this product. I have written this query.. DELETE FROM Products WHERE ProductId IN ( SELECT ProductId FROM Products WHERE Category...more >>

I'm trying to carry out an else if statement within my sql but i'm getting loads of errors. Can some please let me know where i'm going wrong. it doesn't seem to like the text beside then. CREATE PROCEDURE [EJOCaseTrackingUserDetails] ( @SearchCriteria int ) AS SET NOCOUNT ON BEGIN...more >>

The query below is suppose to join customer information with sales and vendor information. An outer join and a union is used to insure I get all customers and vendors within the selection criteria even though either entity may not have sales. The query is: SELECT cs.customer_ident, ss....more >>

Dear all, I would like to have available any kind of formula or method in order to reckon how many space could I be able to cut off if delete rows of a table. For example, a convetional table with a primary key based on indentity and a cluster index per four fields. That table own 80,000,0...more >>

There is a good article at http://support.microsoft.com/default.aspx?scid=kb;en-us;315968 that shows how to use OPENXML to update records that exist and add new records that don't to a table. The question is how do you modify the SQL code to work with a table with a three column primary k...more >>

I've cleaned up and parsed a delimited file into a dataset. Now I want to add that dataset to an sql table. Simple, right? My code is below. It runs, but no records from the parsed file are added to the table. Can anyone look at this code and make it work? Thanks Paul PS Bulk Insert is a n...more >>

Dear fellows, Sometimes when I obtain a script for tables and then I copy them to another Sql Server, collation at field level is lost. Why? Thank you very much,...more >>

Hey I've been doing for SQL for a while but recently I've been trying to get some whitepaper for it, I'm now working through some questions I obviously first I google the questions before I came here to ask. I ran into the questions. Give an example of a rowbased statement, and a set bas...more >>

The below select statement seems to be pulling back records with u_ejostatus = 'NotYetRequested' even though I have not asked for this. The reason it does this is because this record has a u_creditor_ref value of '1'. Does anyone know how I can get around this problem?? I'm not very good at S...more >>

> i am trying to connect to sql server in a remote database by using windows > authentication. I tried to connect using php and cgi. The apache service > under which i am running my cgi or php is under localsystem account. If i > change it to administrator account, i am able to connect to remo...more >>

(SQLServer 2000 sp4 and earlier) I am trying to making select on view. The result is message: Internal SQL Server error, Msg 8624, Level 16, State 13, Line 1 Write to errorlog: * Short Stack Dump * 009BA08C Module(sqlservr+005BA08...more >>

i am trying to connect to sql server in a remote database by using windows authentication. I tried to connect using php and cgi. The apache service under which i am running my cgi or php is under localsystem account. If i change it to administrator account, i am able to connect to remote sql s...more >>

I've got a strange problem with my sql not working. I'm trying to query a table and have a where clause to return all the values in a field which are not null(in other words left blank) The query can be seen below. However this query is still returning all of the records in the table even ...more >>

I'm having seriouos trouble after installing SP4. I have an application written in ASP.NET 1.1 which worked fine with SQL 2000 SP3, but after upgrading to SP4, the application totally crashed while trying to access the database. It gives an error saying a severe error has occued on the server. The ...more >>

Hi, Basically I Call a SP from VB. Till SP Execution I am showing progressbar. Prob. is, I don't know SP excution time that is why I cannot increase the progress bar value symetrically. Any Idea to fulfil my purpose...... Regards ...more >>

(SQL Server 2000 SP3, Developer Edition, Windows XP Professional) I am getting an error "Invalid column name 'sys_login_name'." when running the following query. The query is a minor conversion of a table between versions. One column (userid_fk) had been added in a previous batch. In this b...more >>

I'll illustrate my problem with a fictitious example that illustrates the kind of thing I'm trying to do. I have a table roughly like this : Name Surname Age ======== ========= ==== Steve Brown 13.0 Steve Smith 22.0 Steve Smith 24.0 John ...more >>

we are developing an ASP.NET application with SqlServer at backend.. there are an supplier and about 3000 customer, and each customer has about 3-4 users. they are selling mobile phone counters. while a sale occurs, we are selecting customers stock amount, if it is bigger then sale amount,...more >>

I have the following code....... SELECT a..acct_nbr ,p.pric_uval_amt AS 'Unit Price' ,MAX(pp.pric_asof_dt) AS 'Unit_Price_Date' FROM account_t a LEFT OUTER JOIN price_t p ON a.ser_id = p.ser_id GROUP BY a.acct_nbr ,p.pric_uval_amt Its purpose is ...more >>

Hi All, My application framework will handle calling required stored procedures when ever we click buttons in the GRID control. When ever I have saved a record by clicking save button on the grid. i.e. after record got commited I need to get a message as record s...more >>

when i calling stored procedure can i make one of the output parameter be optional? not sent the parater to the procedure Tnx in advance ...more >>

First. Usually, SQL Server name is equal to computer name. Can I change SQL Server name? So example: My computer name is ADIW, But SQL Server name is ALI. Can I do like it? Second. Can/may one computer have 2 SQL Server? If Can, So how to make it? ...more >>

When I try to debug a stored procedure in a SQL Server 2000 database through SQL Server 2000 query analyzer, the execution doesn't stop at my breakpoints. It seems to proceed to the end and display messages at the bottom. When I try to debug from Visual Studio 6, I get an error message: ...more >>

Hi there, By using the following statement: Select * From MemberHistory OrderBy MHMemberID, MemberHistoryID DESC The query results are: MemberHistoryID MHMemberID MHAction 6 1 Left 5 1 Transferred 4 1 Joined 3 ...more >>

Hello ! How to use correctly catesian join to get follwing result Source table Col1, Col2, KOKKU 1 1 4 1 2 2 1 3 1 And result must be (the column KOKKU defines how much every row must by multiplied) Col1, Col2 1 1 1 1 1 1 ...more >>

Hi, Actually the stored procedure ' Update ' is being called when ever I click new button or edit button . But when i click the save button in the grid the record is commited. But it is taken care by framework.Now when i click the save button i must get the message as the record sa...more >>

Hi All I have a DTS package which is used to extract the data from one server to another. A linked server connection is set up b/n these two servers. Till recently there were no problems with the DTS. Recently one of the server was upgraded to Windows 2003 SP1 and from then onwards Iam ...more >>