I am trying to do something very simple: CREATE PROC counter @var1 TINYINT = 0, @var2 TINYINT = 0, @var3 TINYINT = 0 AS BEGIN DECLARE @numNotZero TINYINT SET @numNotZero = (@var1 != 0) +(@var2 != 0) +(@var3 != 0) END It should be obvi...more >>

Hi In a stored procedure I'm going to detach a db. Before doing that I will make sure the operation will be successful. How can I make sure the database is not in use?...more >>

Message ---------- Id int 4 (Primary Key) SenderId int 4 SentOn datetime 8 Subject Title 500 PriorityTypeId 4 TypeId int 4 Message text 16 SentState varchar 2 SentFlag bit 1 FolderId int 4 MessageRecipient -------------------- MessageId int 4 (Foreign Key refers Message (Id)...more >>

does anyone know of an equivalent to the old compuserve computer consulting newsgroup? the old compuserve group was very well moderated and had some very knowledgable folks talking about strategy, business, legal, tax issues, etc .... it was invaluable ...more >>

I'm attempting to implement parts of the upcoming culture/locale standard outlined in RFC 3066BIS. Examples for culture identifiers are en, en-GB, en-US, az-Cryl-AZ, az-Latn-AZ, ... I've read numerous warnings about using a computed column as PK. But consider the following example where the...more >>

Dear all SELECT CONVERT(char(10), ISNULL(dbo.tblDate.Date, ''), 110) AS DateOfBirth From table1 I want to select datetime if null then return "" else return 31-05-2004 format...more >>

My program executes INSERT on a table with an identity column and SELECTs back a new record id with "SELECT SCOPE_IDENTITY()". This works without an error. After it, the program inserts a new record into a detail table using the id value obtained in the previous step. All above INSERTs are execu...more >>

Hello... I want to duplicate the exact same user permissions (select, insert, update, etc. on tables, execute permissions on stored procedures and udfs) that I have for an existing user and apply those permissions to a new user that I created in the database. How do i that without manuall...more >>

Hi everybody! I am using a query to navigate in a SQL Server Table (MSDE) wich contains 73 fields in almost 70 000 records. Heres my VB code to access database ' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Se...more >>

from a newbie: In declaring variables in a SP, if the table datatype is smallint, can I just say int? Same for smalldatetime and datetime. Thanks! -- sam...more >>

I have 3 stored procedures that each do some complex SQL-Selects all on the same table. I would like to "Union" the 3 stored procedure outputs into a single stored procedure without using a #temp table. Is that possible? Thanks. ...more >>

Hi. My application needs to do distributed transaction on remote Sql server.My application can be run from workstations(XP professional) or from SQL server 2000... Limitations for this application are Worksations wont be having SQL server installed. I cannot give administrative rights on t...more >>

Hello, can somebody help me with the following problem within this kind of Select statement: DECLARE @dee INT SELECT something AS A, something AS @dee, (A x @dee) AS C .... I need the value @dee for further calculations. This way is not possible. How can I use variables dynamicall...more >>

Hi All, We have numerous scheduled jobs on numerous SQL 2000 SP3a SQL servers. Occasionally and randomly, some of these jobs fail to send job completion notifications to the SQL Operator configured in the SQL Job properties. The job history provides the following information: "NOTE: Failed to ...more >>

Is it bad practice to create views on views? Would the query optimizer be able to optimize queries based on these views?...more >>

Is it possible to make SQL Server overwrite backups in a single file? For instance I will run following backup statement every hour backup LOG backup_test to disk = 'D:\log_backup.bak' and I want SQL server overwrite backups older than 1 day. I know I can overwrite the whole file by specifying...more >>

I know this should be easy, but I just seem to be missing the syntax. This is a query for MSDE . It works until I start to try and put a CR/LF between each of the strings in the query. I have tried all sorts of possible syntaxes (syntaxii?) with no luck. Can someone please enlighten me? update...more >>

Hi All We have a table with a persons Employee number (Resource tag) , Date Worked and the Shift Worked on that date. Like So resource tag date worked shift type ------------ ------------------------------------------------------ -----...more >>

Is there a system stored procedure or t-sql command to add a new windows local machine user account to a Win2k box? -- _____ DC G ...more >>

SQL Server 2000 EE edition sp3a Running this in QA select 322 / 1000 returns 0 I expect 0.322 even if I declare @j decimal(10,10) set @j = 3/10 print @j I still get 0 I know I am overlooking something silly. What is it? ...more >>

hello there I want to know how to look at the code of a trigger using t-sql ? I used enterprise manager till now, but all I know is to alter a triger command ! can somebody tell me how I do it using t-sql ? thanks...more >>

what will be the command to send an email to a user ? Thanks, Raul rego NJPIES rrego@njpies.org ...more >>

Is there an easy way to edit an INSTEAD OF trigger on a view, like there is on tables by highliting the table, then clicking "design" ? I wrote a lot of nice logic in my INTEAD OF triggers to give meaningful error mesages when normalizing inserts and updates on my views, but I didn't know tha...more >>

Hi, this statement works: DECLARE @Username varchar(20) SET @Username = 'xxx' IF NOT EXISTS (SELECT NAME FROM SYSUSERS WHERE NAME = @USERNAME) EXEC [pubs].[DBO]. sp_grantdbaccess @Username But when I pass the database as a parameter ('Exec [pubs]' with 'Exec @Database +) ...' it simply do...more >>

I have a table that I need to draw information of to update another table. The table that I'm getting information from has two fields - CID and EMAIL I tried this: INSERT INTO Feature (AID, VID, Cat, Date, LinkID) VALUES (3710, 3711, 2,'9/21/2004 3:18:00', (SELECT CID FROM BTTempTable)) ...more >>

Hi Freinds, What is xp_smtp_sendmail ? where should I find it? I don't have it on master ? Thanks, Pat ...more >>

First of all, let me note that I am a System Administrator, not a programmer. That means when you start spitting out ADO, ADSI, xyzpeot, etc. you may need to talk in little bitty short words! I am trying to do something that I thought would be fairly simple. I want to import data from Activ...more >>

i need to check to make sure a string is numeric - however, i also need to allow a '-' to be considered "ok" as far as my validation goes. is there a way to do this without having to loop through my string one digit at a time? so for example: 12345 = "ok" 1234-556 = "ok" a234-556 = "not ...more >>

in Query Analyzer > Tools > Connection Properties, are the properties set on a "per logged-in user" basis ? ...more >>

in Query Analyzer > Tools > Connection Properties, are the properties set on a "per logged-in user" basis ? ...more >>

Hi, I'm a starter of SQL programming. I try to update a field to "0x" with the value "x" less than 10. For example, if value in the table is "0", I will update it to "00". But the thing is I have to do 10 times for each value from 0 to 9. Is there any script that can do them all at one time...more >>

hi, I am not sure how to design a table to hold the histoical data. Let's say i have a CustomerContract table and it has CustId (PrimaryKey) : Airline ContractStartDate : 1/1/00 ContractEndDate : 12/31/04 Right now, I would like to change the ContractStartDate to 1/1/05 and C...more >>

Hi All, I have a procedure that BCP's a table out to an excel file. I noticed that the column headings do not get exported with the data. Is there a comand in the BCP string to export the Headings with the data? TIA, Joe...more >>

I have the following code: 'Program Requirement' = case when distr_channel in ('DN','DS') then '$1250 Early/$2000 Late' when distr_channel in ('DL') then '$1250 Early/$2000 Late' when distr_channel in ('CH') then cast(250 as varchar) when left(distr_channel,1) = '...more >>

Hi all, In this following code, I am getting an error as Syntax error converting the nvarchar value @nvchrEventType Syntax error near @sintEventYear ,it is a bit datatype. Could anyone help how to convert the variables to appropriate datatypes in the table. Begin ...more >>

Hello there Before this weekend I wrote a trigger on a sql 7 DB and sadly enough I didn't wrote it in the DB documentation. So now i am standing in front of SQL 7 user DB and want to modify it and have no idea what the name of it is and how to access it or anything ? what do I do to find it...more >>

Hi, I'm going to need to borrow someone who has a lot of experience building complex queries in SQL Server and who also has experience with ASP.NET (just in case). I have two queries to build and while I know my way around SQL, I need someone who can write two extremely efficient stored proced...more >>

Came across a situation where ISNUMERIC('00D555000') results is 1. I realize that 0E5 will be considered a exponential, which sucks in it's own right, but what is the D? TIA ...more >>

Hi, I am using SQL Query analyzer to produce output that I am copying to excel. One of the columns displays the output with carriage returns. What I would like to see is the data pasting into Excel as one row. I would like to achieve this in the SQL analyzer window if possibe. Example: ...more >>

Hello Sql Gurus, I need to write an SP something like this.. A Column in a table holds a particular state A based on some conditions and the current state it moves to state B similarly State B to State C etc...etc... my procedure is almost 8 to 10 pages but it looks something like be...more >>

I have a sales table that looks like { ID (PK), Week1Qty (int), Week2Qty (int), Week3Qty (int), Week4Qty (int), Avg as ((Week1Qty + Week2Qty + Week3Qty + Week4Qty) / 4) } Is there an elegant way to compute the stdev of these values and maybe embed it into a fie...more >>

I've got a list of integers that I want to insert into a temp table, but I can't get the data into a form that I'm familiar enough with which I can easily do this. Normally I would create a bunch of ="Insert #Test Values("& A1&")" in excel - is there a better way to do this in QA or will I ha...more >>

I'm having trouble in a SQL statement that queries from a view that has non-numeric, null, and zero-length data in a varchar column. I'm trying to cast numeric data as a real number to compare against a user-entered value. Part of my SQL looks like: where ISNUMERIC(static_mos) = 1 and CAS...more >>

Hi, (Sorry for the over-zealous cross posting - not sure which one to post to.) I need to get SQL server to receive certain mail and process it directly. If I go to SQL Mail in EM, properties it asks for a profile name to test, but there's none their. The server is question has a mail prof...more >>

(Simplified version of a previous question) I have the following table. I need to select a record where the current value of XBX is greater than the previous values of XBX. all datatypes are int. note that the date is not consecutive. It would be great if i could select only the first occurr...more >>

Hi How can I create a custome server role and assign my users to that ? Thanks in adbvance, Pat ...more >>

Hello all, I need everyones advice. I will make the story short and sweet. Basically we have this client that is recommending we get the following hardware to support their new software. I think they are off their rocker, but that is why I am posting this. It is for a claims system whic...more >>

I am submitting this in both a Java JDBC and in a Microsoft SQL Server 2000 forum else where: Scenario: We have varcahr fields in SQLServer 2000 database at 8.00.760 that are getting some odd/different results: Value we are trying to find is ZXYAA001 FIrst: SELECT * FROM objecttable ...more >>

Hi, I need to compare a current value and a previous value for two columns. When the absolute value of the difference is greater for one or the other column and they do not equal I need to stop and (tag) update that record only with the an X if column XBH is greater or tag with an O if OBL is ...more >>

Hi all, When I run the following quey I am getting an error as Invalid object name '#cysubteamevent'. and Warning: The join order has been enforced because a local join hint is Could any one help me why I am geeting this error. I want records from #cysubteamevent --- @nvchr...more >>

What data type would you recommend for a SalesQuantity ? At the moment I have a Sales table with SalesQuantity and SalesAmount. SalesAmount is money, and SalesQuantity is float, but I end up with things like 3.00000001 for the SalesQuantity. The obvious choice seems like decimal, but is t...more >>

Hi all, I need to be able to detect if an instance of SQL Server is running on a remote machine. I have a central SQL Server and I run stored proc which access remote servers with dynamic SQL (all the remote servers are defined like linked servers). Before my processes I want to update a tabl...more >>

Hi, Let us assume lock is acquired on a table through an application. I want to know which statement locked the table. How can I get this information. PLease let me know ASAP. Thanks, sathya...more >>

Hi, This is my query execution plan which i got when executing the query and the statistics while executing the query |--Compute Scalar(DEFINE:([Expr1006]=If ([MSGS].[SenderId]=-1) then 'Publisher' else ([Resource].[FirstName]+' '+[Resource].[LastName]))) |--Sort(ORDER ...more >>