Why can I not print @@error directly? In the following code, @@error displays as 0 (when it should be 220 - overflow error). ******************************** declare @temp smallint, @error smallint select @temp = 32000 + 32000 print "@@error" print @@error ************************...more >>

I have a select statement that returns 76 rows. Following is a piece of the procedure *********************************************************************** exec ("select * into " + @tableName + " from openquery(north,'select * from tblCustomers')") if @@error <> 0 begin print "@@er...more >>

I need help with the following sql statement: select ct_off_time, least(2+(0.044117*CT_OFF_GEN),8)/count(*) as mydata2 calculate avg from B_NRatio it returns: ct_off_time mydata2 -------------------- -------------------------------- 05-MAY-03 18:57:57.7 ...more >>

I have an ASP page that submits a form to a SQL database. There is a textarea that submits to an ntext field. When I output this to an ASP page using a Select statement it is all one string. Is there a way to make this appear exactly like the user enters it into the original text box? I have ...more >>

Is thera a way to do a drop or select on a table where I don't know the name until I pass it. For example, in the following I have a variable @tableName that would normally be passed (here it is just set from a select statement). But the drop statement gives me an error. ***************...more >>

How could I resolve the result provided by sp_locks to list which IP address is locking which table? Something like: ip_address table lock ----------- ------ ---- 1.2.3.4 INVOICE IX 2.3.4.5 ITEMS S -- .~. Might, Courage, Vision. In Linux We Trust. / v ...more >>

I am setting a bunch of stored procedures and am looking for an easy way to gather some statistics about it. For example, I have a statement such as: select * into tom2 from openquery(north,'select * from tblCustomers') and I want to get the time it starts, the time it ends and the number...more >>

Hi, I am trying to write a script that will output two columns : table name and the total rows in that table. The scripts should read all the user tables in the database. Eg : Customer 100 Invoice 2000 Sales 145 Any ideas of how i can achieve this. I tried writing a...more >>

Hi I have a large database cleansing job (sql server agent) that takes many hours to run. I wondering if i can get it to run over a number of days, ie to start at a certain time and then run for couple of hours and then sleep till the next nite. When it starts it just continues from...more >>

Hi, I have a stored procedure that when run on my pc will return NO FIELDS (let alone records), however when I have run this stored procedure on other pcs (both here at work and elsewhere) the SP runs successful with data returned. Is there some kind of setting etc that I need to adjust. ...more >>

How to protect the transaction logs from disaster considering the loss of hardware mirror? Can we use remote mirror for these files?...more >>

Hi, I am having the following trouble. I have a table that contains a varchar field and the string in the field contains table names and column refernces. I want to reference the field and have the tables and columns in the string be referenced as well. For example: Table: Reports Colum...more >>

Hii All I have a table called machine master machine_id machine_name parent_machine_id now i have to write a query that will return me the machine names along with its parent machine name if it has one...if a machine is a parent machine then i store 0 as parent_machine_id .. other wise machin...more >>

Hi, I am having the following trouble. I have a table that contains a varchar field and the string in the field contains table names and column refernces. I want to reference the field and have the tables and columns in the string be referenced as well. For example: Table: Reports Colum...more >>

Hi all, I have two tables: Table 1 contains Table1ID and a Completed field Table 2 contains Table2ID, Completed, Table1ID fields There are an average of 7 records in Table2 with the same Table1 ID. I need to loop through table2 and group all the records with the same table1id, then check ...more >>

How do most people design postal codes? Do you seperate the first 5 char from the plus 4 chars, or do you make a 9 char field? Thanks Dwight...more >>

Here's my problem... I have a users database that stores user information, stuff that will be available always no mater what, then we have data databases which the user can pick from which they want to work with, they are all the same, but there are ones like development ones and live ones and...more >>

I have a date/time value how would you do the following with on the date portion of the date/time stamp. 1st i want to convert the date/time to date only then i want to say where date >=current date -3 Thanks. ...more >>

Hi all, I need to replace some invalid characters in a text field with a valid character. In ordert o do this I am using UPDATETEXT. Here is that line... UPDATETEXT mytable.mycolumn @textptr @patindex 1 @ValidChar This works if the invalid character is not the last...more >>

I have a stored procedure below and I don't understand why it's failing in the Query Analyze Update_GeoFence('test 12 ', 40.944, -81.117, 0.0189, 1690 When I run the above procedure I get the following message: /*---------------------------- Update_Ge...more >>

When DBCC CheckDB is run, will all severe (non informational) errors get written to the Windows Event log? I cannot test this myself because I don't currently have any databases with severe errors. Thanks. Amos. ...more >>

Good afternoon, I have a stored procedure that returns a set of results. I would like to use that set of results within another stored procedure. For example: In storedProc1: ... EXECUTE dbo.storedProc2 -- I would like to iterate through the results of storedPro...more >>

What is the sql equivalent of the oracle command SYSDATE() SYSDATE() takes a date/time stamp and convert it to date only. i.e. 1/1/04 11:59pm and changes it to 1/1/04 Thanks. ...more >>

We are upgrading an Access 2000 app to SQL server 2000. There is a huge query in the current Access program. It uses a bunch of accumulative references to previously generated fields, such as Select 10 as A, (A+5) as B, (B+2) as C.......... But it's not working in SQL server, columns like A an...more >>

I have to bcp some data from a server to another. I add themselves as "linked servers". Now I want to ask you 1) Is there the way to bcp an entire database instead of only a table? 2) I have to bcp a large table(s) (500 MB of data) to linked server, is there any problem to do this during ...more >>

I have created an error-logging ActiveX component for use in my other VB projects. It can log errors to a file, to the event log, and to a DB (via ADO). It seemed to work fine on my system (& my colleagues too), but when we install it on the live machines we have a problem - there is a date con...more >>

I have set up a linked server that I am having problems with on my Sql Server 7.0 server Where do I look to see what linked servers are defined as well as what the path to the data is (in this case it is an Access database). Thanks, Tom. ...more >>

Hey people I am about to deploy a huge web-application, but ran into a strange problem. Here comes a little description When a user logs in (uses a very simple stored procedure) for the first time, the execution time makes my application time out. Then I watched the query that was executing in th...more >>

This mail is regarding a trouble I'm facing with SQL Server 2000. I'm working with ASP .NET V 1.1 and SQL Server 2000. I have a reporting module that generates XML reports from the SQL Server 2000 database. Things are fine and running, but at times I get a fatal SQL Server Exception when I acces...more >>

I have a query that performs an index seek and bookmark lookups on a single table, returning 10000 rows, and am using the SET STATISTICS IO command to measure reads. When I run the following: DBCC DROPCLEANBUFFERS GO SELECT ..... the statistics reported 5303 logical reads. This stays con...more >>

the specification reads like so: | [ [ FOREIGN KEY ] REFERENCES ref_table [ ( ref_column ) ] [ ON DELETE { CASCADE | NO ACTION } ] [ ON UPDATE { CASCADE | NO ACTION } ] [ NOT FOR REPLICATION ] ] I think I understand: F...more >>

Hi...Is it possible to convert the day of a week into an int(not the date but the day, so i would like maybe sunday as 1.....tuesdays as 3 n so on)..i tried to do the above using convert function and it gives an error saying cannot convert from nvarchar to int... any ideas are greatly appreica...more >>

Over the next several years we will be setting up several hundred SQL Server boxes, and each box will be set up identically. I would like to create a TSQL script that as much of the setup as possible. I would like to be able to do the following via a script if possible: 1) Set the SQL Server ...more >>

I have got a query like this : SELECT T1.invitation AS INVITED, T1.Completes AS COMPLETED, COUNT(*) AS total_panelists FROM (SELECT distinct P.PanelistID,count(*)AS total_records, (SELECT count(*) FROM response where response.PanelistID=P.PanelistID AND response.StartedDateTime ...more >>

I am trying to form a SQL query to run on a SQL2k database/customer table to select the customers that haven't ordered anything for the past year. Basic syntax of the query (shortened here for brevity) that I have is Select Customer.MailingInfo From Customers Where [Date Changed] < 4/2...more >>

Not sure why but I get a message: Invalid column name 'var_1' ... etc DECLARE @result AS VARCHAR(255) DECLARE @field AS VARCHAR(5) WHILE @var < 30 BEGIN SET @field = 'var_' + CAST(@var AS VARCHAR) SELECT @result=@field FROM CourseUserData WHERE EmployeeID LIKE '' + @EmployeeID + '' AND...more >>

hi, i have a table like below: pk V 1 1 2 2 3 3 4 4 5 2 6 2 7 1 8 4 I want to pick out pk 3,6,7,8 because they are the latest happening of the 1,2,3,4. How can i accomplish this? I appreciate your help! ...more >>

Hello, I'm trying to improve the performance of a query, and one of the tools I'm turning to in this regard is the Estimated Execution Plan shown in Query Analyzer. I have the following questions: 1. I notice that each of the icons shown in the plan has a 'cost' associated with it, which i...more >>

Hello, We are considering using the image datatype in MSSQL 2000 (Standard Edition on Windows NT 5.0, Build 2195: Service Pack 4) to store receipt images used in receipts generated and delivered to clients. We do not have a lot of experience utilizing this datatype, and in fact our only real ...more >>

Hello I have one table called Citrix and the other called Companyusers. These tables both have 2 columns named Firstname and Lastname and Datatype is nvarchar. I want to get a list and count of how many Companyusers are in the citrix table. Any Help would be great!...more >>

I have a stored procedure that has the following select statement: SELECT DISTINCT TOP 10 NameShort AS label, Revenue / 1000000 AS Revenue, (Fees / Revenue) AS Fee Percent From Table1 I just ran into a case where the Revenue was zero which caused a divide by zero error (fees/revenue). Ho...more >>

I am trying to perform operations against the database from within an extended stored procedure, and have not had much success thus far. I cant locate any information on how to do it with the active connection, so I thought I would open a new connection and bind to the existing session (using ...more >>

Folks: I'm looking at the different recover modes. I have setup a loop which dumps several thousand records into my test db. I am not noticing any log-space benefits by switching to the 'BULK_LOGGED' mode. I have setup my T-log at 2 Meg and it does not grow. I have a 10 meg database Fil...more >>

I have a query like below in storedprocedure : SELECT 'Started' AS title,COUNT(*) AS counter1 FROM response WHERE StudyGID= @Studyid AND IsTest <> 1 UNION SELECT 'Disqualified' AS title,COUNT(*) AS counter2 FROM response WHERE Isdisqualified =1 AND StudyGID = @Studyid AND IsTest <> 1 ...more >>

I have a table that contains a count in Monthly buckets. I need the year to start at 8/1/XXXX and end on 7/31/XXXX How would be sure to add the records that are only between this range? Table set up FacilityID int StatMonth int STATYEAR int Volume int THANK...more >>

I have a .Net application that calls my insert stored procedure to insert a record. I am trying to design my stored procedure to test to see if this record already exists based on ID and if so I want to call my update stored procedure passing the values to it. I am having problems getting it to...more >>

Hi guys, I have a "text" column in a table. I ran following query to get the texts which contain exact word "port". how come it doesn't work? It seems that I can't do [a-z,1-9] in the expression. select text from tableA where text not like '%[a-z,1-9]port%' and text not like '%po...more >>

I have a field with a numeric dataype. The scale and precision are both equal to 7. I am unable to enter -1, -2, etc. I can enter positive numbers and negative numbers such as -0.1. Can anyone explain why I cannot enter -1? I know that it must be related to the precision and scale being equ...more >>

Hi, ....giving a very 'summarized' scenario of the problem I have trying to solve all day (make it 2 days now). Below are the relevant DDLs... I am not listing the DDLs of my other tables: CREATE TABLE [SalesFACT] ( [UniqueProdCode] [varchar] (10), [TransDate] [varchar] (10), [SaleAm...more >>

I have serial numbers that may have been tested more than once, but I want to query just the most recent records The table looks like this table sn date1 time2 data 1 04/19/2004 9:46:00 AM 34. 1 04/19/2004 7:42:34 PM 55. 1 04/19/2004 ...more >>

Hi, i'm stuck with a SQL insert statement (in VB.NET enviroment): cm1.CommandText = "UPDATE modKarCSN SET HasVoted = 1, VotedDate = CONVERT(DATETIME, '" & Date.Now & "', 102) WHERE Pnr = '" & Viewstate("Pnr") & "'" My webapplication supports different languages by using the following code: ...more >>

I've written a few extended stored procedures in Visual C++, with great success. Now I have a task that I am having problems with. I need to return a table of values instead of just one value. (basically an array of strings (360 rows with 4 columns). I can either return this as a resul...more >>

Hey all, I am running SQL 2k on Win 2k Server. I need to be able to compare two dates in a WHERE clause. The trick is the date information in my tables is NVARCHAR, as is the date to be searched. When I try to CONVERT these to DATETIME format in an SP, it says it can't convert. How ...more >>

I would suspect this should be relatively simple, but......... I need to preserve the date for sorting purposes, and cannot split it into 2 fields. There would be NO duplication of month and year in the table. Thanks to all in advance ...more >>

Hi Anyone know where I can get a stored proc to do this ? thanks Bruce ...more >>

I have a tricky situation, I'm looking for suggestions as to the best way to handle this deadlock situation. I have two processes. One process is calling a stored procedure that looks as follows: CREATE PROCEDURE [usp_insert_MyTable_1] (@SerialNumber binary (6), @SequenceNumber int...more >>

This is driving round the bend so I would appreciate your help... I want to send an email to a person which contains the details of a table. ie table=MailSend name emailaddress message john john@sql.blah this is the first message peter peter@sql.blah th...more >>

Hi - I have a table with one colum that have data with trailing little squares. How can I remove the trailing little squares in my table? ...more >>

Hi all, I posted a question yesterday - here's the reference: <Xns94D187BB3330Fmkisphemde@130.133.1.4> Since nobody replied to this question yet, I'd like to ask if there's more input needed to go. I didn't post the entire SP intentionally, because it's quite large. One thing I foun...more >>

Hello all, About a year ago I wrote a VB 6 app that opens a flat file, does some data cleansing and formatting then inserts it into a SQL Server 2000 database. We recently upgraded the database server from 1x2.0GHz with 2Gig of RAM to a 8x2.8GHz with 4Gig Ram. All I changed in the pro...more >>

Hi all, I am trying to insert the NT user information into a temp table, before modifying the data. Alas, the table i insert the user info into never contains any rows. I have tried other net commands such as stop and this works. I did wonder about the scope of the temp table, and changed it t...more >>

I want to put the last 20 LRU record in a combo, so (using C++ code) I save in .INI the last PK of records. The problem arise when I create something like: SELECT Code, Description FROM MyTable WHERE Code IN( 3, 55, 2, 18, 108, 1) and the order is 1, 2, 3, 18, 55, 108 (I suppose that...more >>

I should select the min value from a big table. Is better to us SELECT MIN(MYFIELD) FROM MYTABL or SELECT TOP 1 MYFIELD FROM MYTABLE ORDER BY MYFIEL thanks...more >>

hi, I have a few identity are missing sequential in two SQL tables. For example, id: [1, 2, 3, 6, 8] => would like to re-order the identity back to: [1, 2, 3, 4, 5] with/without relational db structure. Any suggestion is appreciated. Thanks. jj ...more >>

Hi there, i've created a function in Sql Server to do a cross join between two tables in order to then use the results in another stored procedure. Initially i did this as an in-line function and it worked fine, but then wished to expand on it to allow for other situations so moved it to a tabl...more >>

I am using XML to send data to SQL for data updates. I have a master table and a detail table. I am a little confused how to handle inserts of new records. First I am calling a stored procedure to update a Master table. The XML data I am sending to that procedure has both Master and Detail data...more >>

hi friends When i give the query in vb like name='" & text1.text & "'" if the value of the text box is (Solomon's road) then it consider ' (singl qute) as a end of the query. how to give value with singel quotation in sql query( Mic's) pls help me solomon...more >>

Hi - I am trying to create a database diagram (it does not exist) - for a database with around 150 tables - the DBA has left, and there is very little info kicking around. I have a starting point/table, and know what information, when queried correctly, should be returned. However, I do not...more >>

I have the following job step which copies the backup device and moves it to a different location declare @now varchar(20 declare @filename varchar(50 declare @cmdcopy varchar(200 declare @cmdmove varchar(200 SET @now = convert(varchar,getdate()- 1,2 SET @filename = 'VIS_' + @now + '.BAK SE...more >>

Can anyone tell me if this syntax is valid? SELECT * from yalog where Logid in ( case 'asdf' when 'asfd' then select Logid from yalog where id = 1 end ) ...more >>

Struggling to write this script: DESCR TYPE SELL StartDate EndDate Number 65048 04 Price A 4/21/2004 4/26/2004 35456 65048 06 Price C 4/20/2004 4/27/2004 35459 65048 08 Price B 4/22/2004 4/28/2004 ...more >>

hi, If I want to evaluate two statement to be true first before commit a transaction, is there a way that the following code will work? IF (select * from db1.dbo.VC_Process_Stat where process_name = 'process1' and Run_Stat = 'end') > 0 AND IF (select * from db1.dbo.VC_Process_Stat ...more >>