Hi Everyone, I have 3 tables -stocktbl(GSC,title,code),PK:GSC -invoicedetailtbl(GSC,Comment,quantity,fee,fk_invoicesyscode,Syscode),PK:Syscode,FK:fk_invoicesyscode -invoicetbl(customername,comment,invoiceserialno,invoicedate),PK:invoiceserialno I want to find a query of the last date and fe...more >>

In a stored procedure (sql 2k) I'm passing a parameter for whehter or not the result set returns rows if a column is null. @ReturnTimedOrNotTimed CHAR(1) IF @ReturnTimedOrNotTimed = 'T' SELECT ...... FROM ... WHERE PickupTime IS NOT NULL ELSE IF @ReturnTimedOrNotTimed...more >>

Wishing all of you a splendid 2007. Thanks to all of your contributions... It sure is nice not just to have questions answered but also read the answers to questions posted by others. And a special thank you to those answering our questions. Sure wouldn't be fun resolving SQL issues if not ...more >>

Hi, Why the result of my calculation is not simply 3.7? declare @x real declare @y real set @x=23.5 set @y=19.8 select @x-@y I just want to know its reason, please don't suggest Money or other data types! Thanks in advance, Leila ...more >>

Using ms sql 2k I used to write a lot of stored procedures as sources for client side reports. Recnetly I've realized that many times it would be much easier to use views. Much of the time this has proved to be the case. However, I'm afraid that sometimes I'm going too far in that direction. ...more >>

I have a query based on several tables that returns something like this (the number of palettes is calculated based on info in the other tables): Customer Route NumPalettes --------- ------- ----------- Fred A 3 George ...more >>

I'm looking for help in retrieving errors correctly. Below is a sql example that I'm using. The table BRENT has been created with one column, that column being the PK and a single row sucessfully inserted. When attempting to insert the same row I should and do get a constraint error. While the...more >>

Since you guys were AWESOME! at helping me with my first problem here is another... if you can... I have two tables in two different databases. db1 has lets say 10 columns and db2 has 5. All the columns in db1 and db2 are nullable. I can't figureout how to take the five columns in d...more >>

Ok, I have been trying to figureout something for a long time and by that I mean a couple of days..... What I want to do is take the data from two colums and add them together into one column. All three columns have the same properties. nvarchar(19) and allowing nulls I want to do this...more >>

Hi, I was wondering, since I use SqlServer Express, all I need to do is simply make a backup of the mdf and ldf files of the database, and that's it, I have a backup of my database. Is this correct, or do I need to do some other things in addition? Thanks, Ben...more >>

Hi - I'm importing an excel sheet into a SQL table using the syntax: ----------------------------------- INSERT INTO tblExcelImport SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=YES;IMEX=2;DATABASE=c:\files\importfile.xls', 'SELECT * FROM [ThisSheet$...more >>

FYI: my sincere apologizes if this group is not the right place for my questions. Hello, 1. My hosting provider hosts ~500 databases on the server where my DB resides. To open my database I use SQL Server 2000 Enterprise Manager. While server connection timeout is short enough (about 1-...more >>

I am trying to get my results to look like this: ResponseID FirstName LastName Email ------------------------------------------------------------------ 1 Bob Smith bsmith@email.com The values look like this in...more >>

I have a relatively simple query that I can execute on my database and get the expected results. It looks like this: Select databasename, sum(data) from SpaceUsed where databasename not in ("master", "msdb") Group By databasename Order by databasename I put this query into an 'exec sp_sen...more >>

Hi, I tried to create a Set Statistics XML on before running a query and I got an XML link at the end of the resultl panel. I clicked on it and it showed all the XML codes. My question is what is the use of all the XML codes? Can I same the XML codes and run it somewhere else so that it...more >>

I'm developing a Window Form project in VS2005 (C#, WinForm) and SQL Server 2005 Standard Edition as database. When I try adding "SQL Database" by right clicking project in Solution Explorer => Add => New Item => SQL Database it shows following error message. ------------------------------...more >>

Hi, i want to protect my sql server database and desable full access to all table structures and data, because it is a yearly hard work. is there a way to protect my database after livraison with the application? is there a way to desable full database management? Thanks ...more >>

Hi, I have the following T-SQL script but I'm not able to convert the logic to T-SQL syntax correctly. I'm stucked with the syntax to do looping What I have in mind is to only UPDATE individual records that have the condition of timebased of "1" --------------------------------------...more >>

Hello Everyone, I have 3 tables: Telbook(Id:int,Name:char,address:char,comment:ntext,owneruserid:int), PK:id TelNumbers(telbookid:int,telno:char,telNotype:int,syscode:int),PK:syscode,F= =ADK:telNumbers.telbookid=3Dtelbook.id Teltypes(teltypes:char,fk:int),FK:telnumbers.telnotype=3Dteltypes...more >>

When I use the following in a stored procedure (sql 2k), I get the error that #temp1 already exists. I understand why. IF .. some condition SELECT c1, c2, c3 INTO #temp1 FROM Customers ELSE SELECT c1, c2, c3, c4, c5 INTO #temp1 FROM Customers I know I can get around this by creatin...more >>

Hello , My question is: does ms sql support two phase commit protocol? and Why if no. How if yes. Thank you ...more >>

Within a stored procedure I'm constructing a string and executing it. The technique I'm using is like this: CREATE PROCEDURE myTestProc @tablename varchar(20) AS DECLARE @sql_string varchar(500) SET @sql_string = 'SELECT * FROM '+@tablename EXEC(@sql_string) The actual ...more >>

Hi, I got a Query DECLARE @EndDate DATETIME SET @EndDate = '20061211' --dec 11 DECLARE @tab TABLE(Emp_Id int , start_Date datetime, End_date datetime ) INSERT INTO @tab SELECT 100,NULL,null INSERT INTO @Tab SELECT 100, '20061210','20061212' -- dec 10 and Dec 12 INSERT INTO @tab SELECT 10...more >>

Hi all, Using SQL 2000 with SP4 I have a job that restores a Database every day and usually runs fine, sometimes though it fails becuase the BAK file is corrupt. Will after it fails the database has a statas next to it that says "loading". Ok how do I get this out of loading state so I c...more >>

Hi, The following syntax with a "UNION" doesn't work: INSERT INTO #PrintReconcilication ( ClientName, Method, Location, AccountName, ReportInstanceID, Status, ...more >>

Hi, I've tried the following syntax with a "UNION" but it doesn't work: INSERT INTO #PrintReconcilication ( ClientName, Method, Location, AccountName, ReportInstanceID, St...more >>

1. Primary keys must be unique. 2. One of the touted purposes of INSTEAD triggers is to let the classic "insert if new, update if exists" decision be done in the trigger, so that the calling logic can just do INSERT and not worry about it. For instance, see http://msdn2.microsoft.com/en-us/li...more >>

"GetPrintReceipts" is a stored procedure. Anyone can help me to correct the "EXEC" syntax error in the following: ----------------------------------------------------------------------------------------------------------------------- INSERT INTO #PrintReconcilication ( ClientName, Me...more >>

Hi All, I have an intranet app using asp.net. The app is getting popular and now is going over the internet for all sales people and external users. I'm using windows authentication for my login. The challenge is... How can I prevent a single record to be update twice? Means if two users brin...more >>

I have a SQL statement as follows: -- Insert into the temporary table the Pace data INSERT INTO #PrintReconcilication ( --AccountID, --PackageName, ClientName, Method, Location, AccountName, ReportInstanceID, Status, ScannedBy, TimeScanned, DistributionCommon...more >>

I am working on an MS Access client application linked by ODBC to a SQL Server 2000 database. I have noticed that all foreign key constaints have been dropped on one table on the production database. Is there any way that foreign keys could be dropped by SQL Server without human intervention? ...more >>

Hi, I would like to use ADODB.Connection - BeginTrans method as guard for update of database structure SQL Server 2000 and up. Example vb6 code Sub RunUpdate(c as ADODB.Connection) On Error GoTo ErrH c.BeginTrans c.Execute "drop procedure ...", ,adExecuteNoRecords + adCmdText c.Execut...more >>

Hi all I'm having a problem with service broker. I started playing with it and created 2 databases that I try to exchange messages between them. The problem that I have is that some of the messages disappear. I'm sending the message, but it doesn't get to the queue that it should get int...more >>

I need to write a query to count up the amount of sales done by different sales persons. Each sale can be contributed by multiple sales reps, and the count need to be apportioned. If Status=Order, then count as 1, if REFUND, count as -1 e.g. invoiceID salesRepID Status Date 92812 ...more >>

Hi all I Have Tow Table Like Table 1 Id , Total 1 , 10000 Table 2 Id , Items 1 , 400 1 , 300 1 ,200 2 , 490 what I need Table 3 like Id , Items , Total Reminder 1 , 400 , 600 1 , 300 , 300 1 , 200 , 100 Plz Help me Thanks ...more >>

I am trying to use event notification functionality provided in SQL Server 2005 for AUDIT_LOGIN. The field provied by this are not sufficient for me so that i am trying to exatract extra information for each login from system view sys.dm_exec_connections. But when i do a select on this view in t...more >>

Hi I'm a 'C' & c++ programmer but unfortunatly i have to write a applicaton in C who will access ( .MDB ) files. I don't know where to start..... I need to know which header file i have to use what are the function to open close write and retrieve. Please can you tell me where to...more >>

i have a stored procedure (in sql server 2005) made for retrieving paged and occasionaly filtered data. i'm using a construct SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY ' + @col + ') AS RowRank FROM table ' + @filter + ') c WHERE RowRank >= ' + CONVERT(nvarchar(10), @start) + ' AND...more >>

Hello, I am using SQL Server 2000 as a back end. I receive Excel files with some information and i import that information into SQL Server. Now I have a problem with one of the fields in the file.One user sends it in format 'MAY 2007' while another sends it as 'MAY 07'. I need to get the da...more >>

Hi Guys, When I try to create the following UDF I get an error "The last statement included within a function must be a return statement.". create function dbo.udfItExists(@that int) RETURNS bit AS Begin if (Exists(select ID from my_table where this_col = @that)) re...more >>

Hi, How can I get the current Thursday of the week date if I run a query from Sunday to Saturday? i.e...If i run the query on Mon,Tue,...or Fri,Sat, I should display the current week's Thusday date. I don't have a calender table at the moment. Thanks...more >>

Hey there! does anyone know a good site/documentation about the SSIS expression script language (e.g.: used in Derived Columns)? Thx, Chris ...more >>

Hi, I am wonderning what the difference is betweem using a unique index Vs. a unique constraint, seeing as though a constraint creates an index by default. Should I just only ever use a unique constraint & forget about using the index? Thanks for any suggestions. ant...more >>

Hi, In my SQL Server 2000 + C#, does the SqlTransaction has a limit on a SqlCommand? Thanks for help. Jason ...more >>

I am working on SQL book #7 and I cannot find who invented "MUCK" fopr Massive Unified Code Keys" (aka OTLT -- "One True Lookup Table") much less when and where? Help! Google failed me! ...more >>

I am working on SQL book #7 and I cannot find who invented "MUCK" fopr Massive Unified Code Keys" (aka OTLT -- "One True Lookup Table") much less when and where? Help! Google failed me! ...more >>

trying to confirm my understanding of this topic nested loops occur when both tables are indexed but the contents of one result set is larger than the other. merge joins when both result sets are clustered and are the same size do i have this understood correctly. ...more >>

hi, How to delete my own post from a news group? this is not related to sqlserver programming, but generally i use this NG the most, so asking the more skilled Ng users... regards, Sugandh ...more >>

Hi, I have a stored procedure that contains the following SQL statement: CREATE TABLE #PrintReconcilication ( AccountID int, PackageName varchar(40), ClientName varchar(80), Location varchar(80), AccountName varchar(80), ReportInstanceID int, Status varchar(20), Scanne...more >>

I've been asked to cache the output of a stored procedure. My first thought is the new xml column. I'm thinking it would be pretty slick to take the multiple resultsets from the stored procedure and convert them to xml and place them into an Xml column and at a later point in time, query the...more >>