Here is my issue. For fun, I'm working on a database abraction class.... Right now I"m planning on supporting SQL Server/MySql/OleDB written in C#. One of the nifty properties I've decided to add to my class is a DBCommon.LastAutoID int64 Currently, in my ExecuteNonQuery() method, the step...more >>

Hi, I would like to result a result like this: CustomerName, Partners "Test 1", "Partner 1, Partner 2, Partner 3" "Test 2", "Partner 3" I have two tables Customers and Partners with a join between then. Is it possible to get the list of Partners as a comma separated string? Thanks ...more >>

In SS05 , is a col config'd as Identity datatype necessarily the PK ? If not , then for a table with an Identity col defined , how to set the PK in SS Mgmt Studio ? ...more >>

Hi, Is there workaround to use MAX aggregate funcion in indexed views? I use SS2000, SP4, Win 2000 Advance, SP4. Thanks in advance Nikola Milic ...more >>

I'm trying to do a select statement that queries two columns This is my present SQL What I want to do is check and see if the DateReport AND TestID are not in the table PatLabVal2, and if not Insert it into the table. Thanks Stephen K. Miyasato MDsync Insert into PatlabVal2 (PatNo...more >>

Can you put a Case statement in a Where clause? I have the following Cursor I am setting up that is giving me the error: Server: Msg 170, Level 15, State 1, Line 26 Line 26: Incorrect syntax near '>'. Declare @SearchCursor Cursor Set @SearchCursor = Cursor for Select CommandText,WhereCl...more >>

Thanks in Advance, I have had a situation in a cursor that I built (see example 1 below) where I discovered that the first fetch was not being inserted into my table and the last one was going in twice. After consulting BOL, I figured out why (1. the variables were loaded with the second f...more >>

Hi. I have an input in my web page to insert a date by hand. Once I have the date and using request.form pass the variable then I using the typical insert in sql CONVERT(DateTime, '" & Request.Form("myDate") & "', 103), but doesnt work. The field is smalldatetime any suggestions++++ Th...more >>

in an update statement where I have a self join as: update table1 set t1.col3 = t2.col4 from table1 t1 inner join table1 t2 on t1.col1 = t2.col1 WHERE t2.col2 = 'value' is the " WHERE t2.col2 = 'value' " part means: specify the condition to be met for the row in table t1 in order to be ...more >>

I have a very long list of duplicate usernames. To make them unique, I'd like to append and increment a number to the end of each duplicate. For example, if I have 5 userid's = 'SJones', I'd like to append the number one to the second, 2 to the third, etc. The result would look like this: ...more >>

We are attempting to copy data from one SQL-Server db to another using a stored procedure. 1. Capture the error description (which includes the table name and index/constraint name and the column it occurred on) 2. Suppress the error (Key violations & Foriegn key constraint errors) 3. Conti...more >>

Hi there, Can anyone tell me what's happening here? I have the following code snippet that opens a recordset using some stored procedure that returns some rows. Dim rst as new ADODB.recordset Dim Count as long 'rst.CursorType = adOpenStatic rst.Open "Execute my_storedprocedure" & Ite...more >>

Ok I'm confused on this... in T-SQL Say I have TableA and it has no select permissions on it for the user... but the user has a stored procedure with execute permission on it and contents of it is SELECT * from TableA... well of course this executes! and returns the TableA contents... where do...more >>

i'm looking for a way to retrieve a list of files that exist in a directory on a remote server from within sql server. is there a way to do this without using the xp_cmdshell command? jt ...more >>

We have a large application in VB6 that cannot be ported to .net at this time because it is still being enhanced and upgraded. We have 125 clients across the country running anywhere from 1-8 computer systems at a time on this application utilizing Access 2000 as the backend. We are suf...more >>

In my SQL SERVER 2000, my Database(DB) say for e.g. XYZ got corrupted getting message SUSPECT. So i created new DB with initial size of 5GB to try to restore 50GB the original DB & it failed. Finally i was able to restore, by increasing size to substantial level where my restore was succ...more >>

Hi -- I am new to SQL programming, so this may be an easy one. My query is picking up a date out of my database table. Then, I need to find the number of days in the month that the date falls into (for example, the if date in my table is 2/1/2006, then I need to know the number of days in Fe...more >>

I'm trying to select three fields, 2 of which I want to be distinct. I understand DISTINCT is often frowned upon, but I'm not good with SQL, and have just been trying to get it to work properly. This is the approach I'm trying now: SELECT rateid, dis_mag, dis_size FROM (Select Distinct (magsec)...more >>

I had a stored procedure in SQL server 2000 which had this query in it: select * from (SELECT top 100 percent HitID, HitDate, IPAddress, ReverseLookup, ISP, VisitLength=ISNULL(VisitLength,0), VName=ISNULL(VName,' ') FROM Journal_Hits order by hitdate desc) DERIVEDTBL When I moved the datab...more >>

Hi all I am having trouble getting started writing a trigger that fires when a logical check box changes. I have searched high and low looking for a if statement inside a update trigger with not much luck. I need to set the date in a column (custDate1) when the logical is checked and then ...more >>

Does anyone know why arithabort is defaulted to OFF in SQL Server? ...more >>

Without using locks and with only using pure ANSI-92 SQL, I need to pull off the concept of record checkouts. Imagine a table full of work orders. I can only let someone use the web app to edit the work order for 15 minutes. If someone has the work order checked out, and someone else wants to ed...more >>

Hello, Select ID, Count(ID) From tbl1 Group By ID Having Count(ID) > 1 this retrieves only IDs where there are duplicate IDs. Select ID, fldx from tbl1 Group By ID, fldx Having Count(ID) > 1 this is the same table, but does not return any rows. How can I retrieve only the rows (the...more >>

What is the most efficient way to strip time from a date (and end up with a date not a string)? Do you have to do something like: CAST(CONVERT(char(10),Date_Rcvd,101) AS datetime or is there a better way? Thanks for help. Mia J. *** Sent via Developersdex http://www.developersdex.co...more >>

I have a simple Bulk Insert statement I want to run in a stored procedure. Here it is: Set @bulk_cmd = 'BULK INSERT MyTable FROM ''C:\mydump.txt'' WITH (FIELDTERMINATOR = ''\t'', ROWTERMINATOR = '''+CHAR(13)+CHAR(10)+''')' EXEC(@bulk_cmd) It runs fine but does the insert twice. There ar...more >>

I am now trying to send email using CDOSYS from a stored procedure on a Windows 2003 server and Sql Server 2000. I got this code from the net and trying to figure out why it won't work. It seems to work ok until I do the "send". I am getting an error: Source: CDO.Message.1 Descripti...more >>

?err.Description Invalid length parameter passed to the substring function. Ok, I am trying to find in a text field an occurance of =901 If found >0 then substitute =901 whatever to the next = sign a new piece of string =901 plus field ID value ?sqlquery UPDATE BookData SET marcdata=sub...more >>

I have a procedure I am trying to run on my Sql Server 2000 that will send emails. I am running this from Query Analyser to test it out and can't seem to create the object, but get an error. The procedure is: ****************************************************************************** ...more >>

Here is my problem, I am trying to loop a query (its has been precalculated into a temp table) until it get to the end of the temp table. I was hoping that I could do it with a simple SET command, but when I run the command. I keep getting the error "The multi-part identifier "#Critical_Alert.Co...more >>

Is there any difference in performance between a stored procedure that uses a view and a stored procedure that includes the full select statement defined by the view. Simple example. A real example would involve a view with more joins, etc. View: viewEmployees select * from tblEmploye...more >>

I have a problem with my error handling in nested stored procedures (SQL Server 2000). Below is an example of stored procedures that are causing this problem. If you install the scripts and run sp1, you'll get 2 errors: (1) Divide by zero error encountered. -- This is expected due to : sele...more >>

Hi there, I just want to play with Pubs data base that is available in Management Studio and apply all the enhancements of T-SQL in SQL '05. How would i install the PUBs and Northwind databases to the Management Studio databases? Thanks -D ...more >>

I have a query I am trying to run, and I want to do calculate the difference in drive space as I go, but for the life of me, I totally forgot how to do it? Here is what I have DECLARE @Critical_Value INT DECLARE @UsedSpace DECIMAL (28,2) SET @UsedSpace = (DiskSize - FreeSpace) SET @Critic...more >>

I have been asked to write a procedure by which email will be sent to an Account Exec. after a quote has been created. After much reading, I have come up with the code below for a trigger and the stored procedure but on testing I get ""Procedure 'qt_mail' expects parameter '@recipient', which...more >>

Hello, I have to put some data from an SQL Server 2000 DB to a Word document containing some numeric values (money). I use a stored procedure like this select field1 + ' ' + field2 + ' ' + field3 ... from table1 where ... So I get back one long string. How can I get all numeric values...more >>

I recently migrated some user tables from Access to SQL Server while leaving the forms, reports, and queries in Access with Linked tables to the SQL instance. Some of the users queries/forms are not working correctly? It seems that Access Queries return a single empty row when no matches are...more >>

Hello all, It would be very handy if I could do something like: declare @database varchar(30) set @database = 'dbo.db.table1' select * from @database is it possible something like that? how? thanks in advance ...more >>

Hello togehter! If I try to run the following Statement I get the following error: INSERT INTO [Topic].[Confirmation]([Guid], [GuidOffer], [GuidTopicEngineer], [Allowance], [GuidAccount], [Canceled], [LeadinText], [LeadoutText], [Number], [CreationDate], [Date], [GuidCustomer], [GuidTopicAge...more >>

Hi friends, I have a table invoice and its detail table invcontent Master : InvoiceID autoincrement field Date Customer ..... LastChildID Detail : DetailID (Master LastChield+1) Item ..... I am using Erwi...more >>

Hi all, Can one determine the order of triggers, or set the order of triggers. I have a two insert triggers, generally I would like one trigger to fire first all the tme. Is this possible. Robert ...more >>

We have a number of tables containing an ID field - it's an integer and mostly the primary key. To access these tables, we use a stored proc - one for each table. One of the parameters on the SP is optional (Defaulted to -1). It's used like below: select ..... from MyTable t i...more >>

Returning a number instead of text Hi I have a query Select filename, description, record_id from myTable Instead of returning the filename I would like to return the value 1 if there is a filename present and 0 if the filename is '' or NULL. There might be a descri...more >>

Hi, I want to write a application works in same manner as SQL Query Analyzer works in Text mode option. I studied the behavior of query analyzer and find out that in text mode option query analyzer does not wait for the query to get finished, for displaying the result. It diplays the resul...more >>

Hi, Does anybody know how to get the following information for a particular database in SQL 2000 1. Whether it is a System Object or not. 2. Create for Attach 3. Replication Status We can get this values through SQL-DMO, but can I get these values from some system tables or in-built f...more >>

I got one same tables in 10 database, and I need to amend one field from 'null' to 'not null' Can I do it by script ?? Please help ...more >>