Let's say I have a big fat SP that goes something like this: BEGIN TRANSACTION -- Outer transaction -- Do some stuff. BEGIN TRANSACTION -- Inner Transaction 'A': Do other Stuff COMMIT TRANSACTION BEGIN TRANSACTION -- Inner Transaction 'B': Do More ...more >>

I am writing an engineering solution on top of Office and SQL Server. Dealing with IEEE double-precision floating point is always a pain, even with software tools that support it. It appears SQL Server does not even claim to support it. Exhaustive searching has not turned up some basic answers...more >>

Hi there, Please take a look at the code here. USE Northwind GO DECLARE emp_cursor CURSOR FOR SELECT * FROM Employees SET ROWCOUNT 2 -----------------> I try to retrive two records at the time but with no sucsess. GO OPEN emp_cursor GO FETCH NEXT FROM emp_cursor WHILE (@@FETCH_ST...more >>

I would like to get space used for the data and index for each database with a query.. Such as DBname Data Index DB1 1000MB 2000MB DB2 500MB 100MB I looked at sp_spaceused , but cant seem to figure out how to write the SQL to get me what i w...more >>

Dig this: IF EXISTS ( SELECT * FROM SysObjects WHERE Type = 'U' AND Name = 'CMRPhone' ) DROP TABLE dbo.CMRPhone GO CREATE TABLE dbo.CMRPhone ( CMRKey INT NOT NULL IDENTITY(1, 1), PhoneNumber NVARCHAR(40), PhoneNumberKey INT ) GO ALTER TABLE dbo.CMRPhone ADD CONSTRAI...more >>

Hi, I have a very strange situation. I have a stored procedure to insert data into a table called "Job", in the Job table, I have an Insert Trigger to insert another table called "Audit". The strange thing is the whole process always stops at the end of the trigger and doesn't return to...more >>

Only 3 records out of 40 are being returned? Does anyone know why? Thanks! Ed 01: SET NOCOUNT ON 02: 03: DECLARE @pagenum INT 04: DECLARE @perpage INT 05: SET @pagenum = 1 06: SET @perpage = 20 07: 08: DECLARE @ubound INT 09: DECLARE @lbound INT 10: DECLARE @pages INT 11: DECLARE...more >>

Hello - I am getting the following error when debugging in vb .net. Naturally, I am getting no connection to the database - ServerVersion <error: an exception of type: {System.InvalidOperationException} occurred> String I originally built this database in a trial version of Sql Server ...more >>

Hi folks, I am creating a linked server to connect to IBM DB2 UDB Server. I succeeded when I created a DSN using "IBM DB2 ODBC DRIVER" and use the DSN in Linked Server after selecting "Microsoft OLE DB Provider for ODBC Drivers" as the Provider Name. But if I select "IBM OLE DB Provider for...more >>

Hi , I have twoTables. 1>tblSchedule and 2>tblActive. tblSchedule has the predefined times events. tblSchedule stores thousand of events. Typical record of tblSchedule is like: EventID EventDateTime Description --------------------------------------- 1 06/24/05 9:15 B'Day 2 ...more >>

Hello, Is there a way to initialized multiple local variables using a single select statement? For example: declare @var1 char declare @var2 char -- I know that this is valid: set @var1 = (select name from CUSTOMER where customerNumber = 1200) -- however, this is invalid syntax:...more >>

Hello I need to define a user of SQL Server 2000 that could run jobs. At present time I succeeded only in the case that the user has rights of system administrator (in Enterprise Manager in Logins I click right on the user and in Properties/Server Roles I check box System administrator). B...more >>

Hi, I would like to do a bulk insert from a file in my webserver but I get an error when I try something like this: (I will be using this from an ASP.Net page) Bulk Insert temp_supress_emails from //servername/folder/filename 1) How can i solve this? 2) Can I give a condition so that only th...more >>

I have a Users table which contains a nvarchar column LastName. The first query below returns me all the records with LastName containing "opez" or "ópez" in them. But the second query returns only records with "lopez" in the lastname and not records that contain "lópez". Can anyone please tel...more >>

I have a function I am creating as a UDF and was curious as to whether it is better to use the UDF or to just do the conversion in the select statement. What I am doing is formatting a money field in various forms depending whether the amount is >= 1000. If the amount >= 1000, it rounds th...more >>

Hi, I always used ACCESS, after update my Yes/No fields are converted to BIT and since than i can't use TRUE or FALSE in my Sql ? With =0 or =1 it works fine. Is this normal in MS SQL ? or is there another way to use True and False. Example "Select * From table where myField=True" (not ...more >>

Dear all, Regarding the aforementioned question I don't know if launching one script in VB and retrieving all the names is compulsory. Is there any way or method for obtain that info via job/sp? I hope have been sufficiently explicit. Thanks in advance and regards, ...more >>

Hi, First of all, I realise that this is a big no-no and that date formating should be done on a client. However my application at the moment requires that a Shift and WorkDay is obtained from the transaction dates. As the shifts are quite obscure I have created a table in which I take shif...more >>

Hi, Is it possible to retrieve the body text of a trigger through SQL? Regards Henrik Skak Pedersen ...more >>

We set our SQL Server database to "Automatically grow file" and "Unrestricted file growth". Why in the TaskPad view, the free "space allocated" is 0 ? Thank you. ...more >>

Hi I am trying to run following query insert into work_viewtable exec ATH.At.dbo.ContactInfo It gives following error Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'SQLOLEDB' reported an error. [OLE/DB provider returned message: Cannot start transaction while in fir...more >>

I have a UDF that I am starting to build: **************************************** Create Function Money2String ( @Amount Money ) returns varChar(80) As begin Declare @NewAmount varchar(80) select @NewAmount = convert(varChar(80),@Amount,1) return @NewAmount end *********************...more >>

I'm scripting a SQL Server job so that it runs on different server instances. I generate this job script from our development machine and store it in Source Safe then pass the script to an osql script that runs it. When I create the script it has @owner_login_name = N'servername\username',...more >>

Hello I have a quick question about sum. im working on summing very large numbers i.e.=20 14,289,648,007=20 But it not summing up right for some reason. does the sum function have a limit on how big of a number it can sum?...more >>

I have a situation with a c# application attempting to store and look up some unicode value in an NVARCHAR. Apparently the system is having issues dealing with some Unicode values on inserts or lookups. My tables: /* table containing terms */ CREATE TABLE [dbo].[TermTable] ( [TermID] [i...more >>

Hi, I'd like your advice on the following matter: I have 2 (or more) sql server on my network all familiar with one another. I'd like to execute a procedure on server A which executes another procedure on server A and B at the same time. is it possible using a stored procedure, or i nee...more >>

hi all got a situation here, there are 3 app server that are running a same application to delete records in the sql server periodically. what happen is that they all start up at the same time to delete records on the same table.(this could be fixed by varying the start up time on each app s...more >>

I was recently running over the a Query feature I set up for a datbase application I produced and discovered a strange error. The Error is with a Year fields. The user, can enter a range of type year to query ie. 1970 - 1983 and so on... the first 14 records stored are of the year 1969 i...more >>

Hello, I want to use a table variable to simulate an array in a stored procedure. Has anyone done this? Or can someone help me to write a short SQL script to process a table variable as an array? For example below, I have a table variable @Tbl contains 2 columns RowIndex and VoteCount. ...more >>

Hi, on my MS SQL Server 2000 I created a temporary Table in which i insert Data trough an Office Web Component. This temporary table is created from a querry over a few other tables. Now my idea is to create a trigger on this temp table, so that every time a value is updated this change is...more >>

Hello all I need to call a stored procedure wich returns a select statement with 3 values. Is there a way to assign one of the 3 returned values to a variable (or all). Only one value returned matters. THANKS IN ADVANCE...more >>

HI all, question for you please: i created temp table and populated with data. all the columns in temp table has data and there are no NULL’s in any column. Here when i try to join on the permanent table i got this error message: Warning: Null value is eliminated by an aggregate or ...more >>

We call Stored Procs from Visual Basic/ADO. Does anybody know of a way I can generate the execution plan for the proc and retrieve it back over the ADO connection so I can store it. The aim is to to be able to tag certain stored procs to store their query plans so we can analyse them later...more >>

Hello, I have created an audit trigger, which I am using on all my tables. But I have a problem if my primary key changes. Then the trigger create an update audit transaction with the new primary key, which I guess is ok, but I somehow also have to create a primary key change audit transact...more >>

I have table that holds dates when some accommodation is occupied I have problem to check if that accommodation is occupied between '2005-07-01' and '2005-07-08' How to do that select? --table CREATE TABLE [Periods] ( [id] [int] IDENTITY (1, 1) NOT NULL , [fromdate] [smalld...more >>

Hello: I have a stored procedure that is called by a job that unzips a file and then sends the name of the file to another stored procedure for processing.... However, recently (we upgraded our server but that may have nothing to do with it), the second stored procedure just stops midway. I...more >>

Hi NG, when select rights in table XYZ have been revoked for column A then the query select * from XYZ raises the error "#230, Severity 14 The select right for the A-column of the XYZ-object, test database, owner 'dbo' has been denied." (just a rough translation, I only have the Ge...more >>

Hi All, I have a sample CSV text file below that I want to insert in a table with the ff columns. c_event char(10), c_date char(15), c_time char(15), c_id char(20), c_info char(20) ====sample CSV===== start,1/16/2005,16:15:20,eac99e1d,3233155101:? start,1/16/2005,16:15:21,eac99e1d,800...more >>

How do I display the datetime in a format: dd/mm/yyyy ? I tried SELECT Name, CAST(CONVERT(datetime, DateOfBirth, 103) as varchar(12)) FROM Employee It displays the format as like: Jul 12 2000 Jan 1 1988 ...more >>

I have the following table: Employee Login Logout 1 08:00:00 13:30:00 2 09:25:00 16:25:00 3 13:00:00 19:00:00 How can I add these up to get a total calltime that looks like this: Emp1: 05:30:00 Emp2: 07:00:00 Emp3: 06:00:00 Thanks, Ninel ...more >>

Why the call of this function COALSEC always has an empty string inside ? eg COALSEC(myName, '') ...more >>

How to know list of relationship on a database? Because I wanna make a document of relationship. ...more >>