I am trying to return a two character result for the day number and was wondering why I get a one character day returned in Statement A, and a two character result in Statement B? declare @day varchar(2) --Statement A: set @day = case len(day(getdate())) when 1 then cast('0' + ...more >>

Hi, how can i create a new table from a select statement? i use select into, but my select query has case columns and query analyzer says that i did not specified a column for them. how do i do it? thanks in advance for your help ...more >>

Hi , When I try running the below sql on MSDE i am getting this error, Can some one pls help. It works fine on other systems where SQL Server 2000 is installed. Select * into Brand FROM OPENROWSET('MSDASQL.1','test1';'sa';'pat','select * from [BRAND]') This is the detail error msg ...more >>

I'm using user defined data types in my stored procedures and they seem to work fine. However, if I use the query analyzer to call the stored procedure, and try to define the parameters using the user defined data type, it says that data type doesn't exist. Is there something special to sp...more >>

Hi, when I add new entries into a table I create a temp table first in order to create a proper id, e.g. CREATE TABLE #artists ( artistID int identity(1,1), name nvarchar(20), ... ) Since in the permanent artist table there are akready entries I was thinking to calculate the ...more >>

Can you setup a relationship between two tables, using in one or both sides of the relationship a formula field? Thanks in advance, - Arthur Dent. ...more >>

Hi all, I have a table in sql server that stores email message records. This table is used by a third party application that our users use to check emails. When emails have any attachment, the application stores the attached files in a folder called "Mailbox" the then saves the path to the...more >>

Hi, In the code below, I am trying to update a column to '0'. But, I dont know which column to update until my variable @ Month fetches the column name. DECLARE @Month Varchar(20) SELECT @Month = MD.sFieldName FROM MonthDefinition MD, DTSScheduler DTS WHERE MD.lMonthId = DTS.JobMonth an...more >>

Hi Everyone, I have got a problem with date calculation. I have a procedure that all me to insert date into a Table based on user input. The input is a Event Date and Reminder Example: if the user Enter an Event Date and choose to a reminder for a certain event... I need to calculate a da...more >>

Hi this question has been asked several times and some solution has been provided already. But the one I am facing is with a twist. I need to use the IN() clause with a variable as its parameter. The variable is a list of comma separated character values all enclosed in pairs of single quotes....more >>

Hi this question has been asked several times and some solution has been provided already. But the one I am facing is with a twist. I need to use the IN() clause with a variable as its parameter. The variable is a list of comma separated character values all enclosed in pairs of single quotes....more >>

Hi, Could anybody tell me why I get the error Server: Msg 208, Level 16, State 1, Line 10 Invalid object name '#subscribers'. When I run this in the QA: declare @csql varchar(2000) set @csql='CREATE TABLE [#Subscribers] ( [SubscriberID] [int] NOT NULL ) ON [PRIMARY]' exec (@c...more >>

How can I reuse the result of a subquery in the same query where-clause without having to duplicate the subquery itself? Say I have a query like: select a,b (select count(c) from MyOtherTable where MyOtherTable.d = a) as someCount from MyTable order by someCount and I want t...more >>

I need to check a value in my Users table before I insert a value in my UserRoles table. I don't want anything inserted into UserRoles without first checking that there is a corresponding userid in Users. Which is best suited for this cross table sort of check -- a check constraint or a trig...more >>

I have an issue that I keep banging my head on. Basic business problem is that I need to return all records in a given time period and any records which have not been completed. There are several different queries inside of the SP which all have the same basic problem with the or statement. ...more >>

This is probably something simple I'm missing, but here it is anyway. I have a table called "Employee". Primary key is "pk_EmployeeID". There is a foreign key field called "fk_SupervisorID" which relates to pk_EmployeeID. The object is to pull an employee's supervisor from the same table. ...more >>

Is there any known connection speed level where a low connection speed will cause an application to not connect to sql server? I periodically have problems with applications connecting to the sql server and the only thing I have noticed is that when this is occuring the speed on the network is v...more >>

Hi All, I want to call an extended proc from a trigger. Is there a way to access the "Inserted" and "Deleted" tables from the extended proc?? Thanks, Ashkan ...more >>

I want to calculate a string, so I have created this Function, but its giving error any idea please ? Best Regards, Luqman Create function eval (@Mycalcstring varchar(50)) returns decimal(12,2) as begin declare @myString varchar(50) select @mystring=exec('select '+@Mycalcstring) ret...more >>

I have ArticleNumber, and DateSell I need query which will give me oldest DateSell for each ArticleNumber. How to do that? I try SELECT ArticleNumber,DateSell FROM StavkaArtikla as a WHERE NOT EXISTS (SELECT ArticleNumber, DateSell FROM Stavka_artikla AS b ...more >>

I am sql server newbie who is trying to migrating a DB2 system(MF) to SQLserver. I have a few questions, it would be great if someone can help me out : 1. When I am porting the app to sqlserver, is it advisable to make the queries to SP or keep it as a regular SQL ? Does SP perform any better...more >>

Hello, I am using VB.net & sql2000 for developing some s/w. I am open the connection and close that after using. but after continuous using the following error is coming how can I avoid this error please help me.. I am sending the code also Publicc Sub FillCombo(ByVal SQLCombo As Str...more >>

I need to write a stored procedure that will do the following... 1 go through ~ 100,000 records one at a time. Looking at each record do several lookups in a few other tables then insert this modified record into another table. would tsql be the best solution...... I have written co...more >>

Hi there. I have an empty table in a database. For some reason, whenever I try to call TRUNCATE TABLE mydb.dbo.mytable on it, the query seems to lock in SQL Query Analyser and keeps processing until I manually cancel it. Strangely, I can run DELETE FROM mydb.dbo.mytable in the same query w...more >>

Here's a poser (at least to me!) When I run a select statement in QA the statement uses available indexes (Index Scan) and is quite fast, but when I take the same script, place it in a sproc, and execute the sproc, it uses a Table Scan rather than an Index Seek on the Juvenile table - killi...more >>

I have a table with two columns. two columns are of datetime datatype I have two records in the table 2/1/2004 12/30/2004 2/1/2004 6/30/2004 I need to retreive the record from the range 2/1/2004 to 7/15/2004. When I use the < and > symbol. it is not working with the datetime feild...more >>

Does the record count inside subquery in the Exists clause affect the overall output ? eg. SELECT * FROM table1 WHERE EXISTS ( SELECT * FROM table2 WHERE table2.field1 = blah) Will the overall output the same when 1) the subquery returns 1 record 2) the subquery returns 100 records ...more >>

I need help with determining which salespeople should be assigned bonuses based off a relationship between the salesperson and their manager and also the relationship between the salesperson and the customer. Below are 3 tables, the salesperson table which contains the salesperson's numbe...more >>

How do I do this in SQL? I have a table I'm querying with the following fields (simplified here): facility_name char(15), drug_product char(64), total_cost money How can I produce the following in SQL so my result can answer the question What are the top 10 drugs dispensed by facility_id when ...more >>

Here's what I got CREATE PROCEDURE [cp_GetUserInfo] @Username VARCHAR, @Password VARCHAR, @Status VARCHAR OUTPUT, @Fullname VARCHAR OUTPUT, @UID INT OUTPUT, @SecLevel INT OUTPUT, @SiteAdmin BIT OUTPUT AS SELECT * INTO #tblUser FROM tblUsers WHERE strUserName =3D @UserName = AND bit...more >>

Hi Everyone, I have got a problem with date calculation. I have a procedure that all me to insert date into a Table based on user input. The input is a Event Date and Reminder Example: if the user Enter an Event Date and choose to a reminder for a certain event... I need t...more >>

background sql2k... this a puzzle to me. one of the sql servers ran out of disk space for the data drive and i tried to truncate some tables to free up space, but after truncating millions of record, and did the shrink database and shrinkfile (with turncateonly option) commands, the size of t...more >>

Hi All, I am writing my first "INSTEAD OF DELETE" trigger and what I need to do is delete the actual records that would have been deleted if not for the "INSTEAD OF DELETE" My question is that when I run a trigger I get back a result set in QA (I'm not even sure if that matters) and was ...more >>

I was wondering, is there any way to get something like a reference count, or even just an "InUse" yes/no kind of field, for primary keys? For example, i have a MARKETING_TEXT table, which simply has an ID and TEXT columns. ID is the primary key. What i want to be able to do is find out, fo...more >>

Hi, I have a loopback connection using ODBC in the DLL initialization code of the SQL Server ESP Module (SQL Server 2000). The loopback connection works fine when the DSN is specifed with the "NT Authentication", however the same fails when specified with the "SQL Server user authentication...more >>

Hi, We are using ADO to access SQL Server 7 and SQL Server 2000 databases. Our clients want friendly error messages and do not like the technical SQL errors at all. How can I identify the column that generated the NOT NULL error? I can get the NativeError as 515 easily but getting the of...more >>

Hello im writing an inventory application for a customer that needs to calculate item cost by Moving Average method which requires calculating the cost after each operation, i have a good experience with TSQL but so far i failed to write the statement that can do this WITHOUT writing cursors ...more >>

Hi there. I have a scheduled task that is to call a stored procedure daily. I would like for the results (the stored proc just does a select * from table) to be output to a file on disk - preferably in .csv format. Any ideas on how I'd go about this? Would a DTS task suit? Any info wo...more >>

How can a table column be identified to be a primary key and auto-incrementing when a new row is added. Example Auto Inc Name State 1 IBM CT 2 Netscape CA 3 Sun CA Thanks, Marc...more >>

Hi All, I have a table with two columns that I want to match but am unsure of how to. The Columns are called "User_Name" and Managed_By" the user_name is entered as "Fred Flintstone" while the Managed_By is entered are "Flintstone, Fred". To the human eye you can see that they are the sa...more >>

I'm fairly new to the stored procedure world, I can create one to return = a record set, look at me go. However, I need to get a little more = advanced. I need to do the following in a proc: Query a honken huge query and check if login is valid. If so, continue = through proc and return the ne...more >>

Hi All, I am having to run a "batch update" on a LARGE table that is used by a report. i.e. BEGIN TRANSACTION EXEC 5_minute_Procedure_On_Large_Table COMMIT TRANSACTION This batch update takes 5 minutes to run and during this time the LARGE table is locked and prevents anyone from accessi...more >>

I wanted to know if there was anyway to pick out duplicates in a certain day. Days are setup as a settlement_batch_number. So on a certain settlement_batch_number is there a way to pick out duplicates when store number , card number , and amount are all the same. Does a group by functio...more >>

Hey I got this noob problem, I cannot drop a function. IF EXISTS (SELECT * FROM sysobjects WHERE name = N'NONUMBER') DROP FUNCTION NONUMBER This works fine on one of my other servers, first thing I though is security. Since this code works on another server. My permi...more >>

HI, I've got a little problem. I have 2 rows in different tables ... is there any way how to simply get these two 2 rows in table? The rows are similar, only datas are different. Thanks a lot. S....more >>

Hi All, I have a table with two columns that I want to match but am unsure of how to. The Columns are called "User_Name" and Managed_By" the user_name is entered as "Fred Flintstone" while the Managed_By is entered are "Flintstone, Fred". To the human eye you can see that they are the sa...more >>

I have a table with varchar fields & I need to covert the fields to money fields. My problem is if the varchar data is of format (581.63) how can I change that to -581.63 before then doing the convert ie using CAST(July AS money). Using there CAST function fails because of the brackets....more >>

Hi there. Here's a question that I probably should know the answer to but.... Anyways: If I want to use Linked servers in some simple queries (select * from table...), do I need to have MSDTC running? In other words, do I need MSDTC even if I'm not loooking to coordinate a multi-server beg...more >>

Hi there, Here's the problem: If i do a query like so: ================================ sql_string ="select field from table where field = this" set RS = dbConnection.open(sql_string) 'loop recordset here rs.close set rs = nothing ================================ This HANGS... to ret...more >>

Hello All, We have a Contract application which has a contract table. As per the design, once a contract is created, it can have lot of amendments to it. However, the original contract should not be overwritten for any reason. The cuurent design is of the following form : CREATE TABLE [d...more >>