Can someone please explain why I get the following error when running the following code. Error message 'Server: Msg 266, Level 16, State 2, Procedure sp_test, Line 3 Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1,...more >>

Hi, I use the exportdata method under the DMO: Dim oServer As SQLDMO.SQLServerClass Dim oDatabase As SQLDMO._Database Dim oBCP As SQLDMO.BulkCopyClass oServer = New SQLDMO.SQLServerClass() oBCP = New SQLDMO.BulkCopyClass() oServer.EnableBcp = True oServer.LoginSecure = True oServer.Conn...more >>

Hi, I'm facing a problem I can't understand: I have a table with a unique constraint on a varchar field. The ANSI PADDING is ON. When I create an entry with value 'AB ' (with a space after), the space seems to be part of the column value. After that, when I insert an entry with value 'AB' (...more >>

Hellow everybody: I have a question below: TABLE:TA COLUMN:CL1,CL2,CL3 Data: CL1 CL2 CL3 ================ 001 1 This is 001 2 the first 001 3 data 002 1 Now 002 2 is 002 3 4:00 How can I use "Select" command to become 001 This is the first data 002 ...more >>

How can I use the Balance value (calculated in a User Defined Function) t calculate another value in one select statement Example SELECT dbo.BalanceSum(@EndDate, @Value1, @Value2) As Balance Balance * aTable.Qty AS Result -- this is not valid when I try to parse the SQL Synta...more >>

I'm use SQL Server 2000. And have some question on using lock hints i've a table [SYMBOL] with a single column named [ID] and with the = following 3 records: [ID] 1.) 9999 2.) 2222 3.) 3333 In the first query window. i enter QUERY A as follow BEGIN TRAN UPDATE [SYMBOL] SET [ID] =3D '1111'...more >>

Hello, Given: a Parent-Child table PC1 and another Parent-Child table PC2 I would like to copy a selected Rows from PC1 to PC2 and keep the relationship I want the selected Rows in PC2 to have new Identity given by PC2 forget the old identity The number of rows selected is about 50 or les...more >>

Is there any query which will delete the dulpicate rows in all manner of a table. But one row of each, duplicate row should remain the table after deletion. The table does not contain any primary key. Oracle uses rowid in this situation. Do SQL Server have any trick to do it in one line? T...more >>

Hello everyone, Please could someone help me with the following select. Column b is the property id and sometimes has two or more records.I need to select 3 from cloumn C when 3 exists else 1 and group by column b result: records 2,3,5 Thanks again, Andrew. --**********************...more >>

Is it possible to have an SQL select statement where rather than giving an exact date for comparison it would check whether the record insertion date is after January 01 <<current year>> ? ...more >>

Here's my Stored Procedure CREATE PROCEDURE [dbo].[p_exam_user] @iUserID smallint, @SecNo smallint AS DECLARE @QryDef varchar(800), @ExecThis varchar(8000) SET @QryDef = 'SELECT DataID, UserID FROM exam WHERE (UserID= ' + @iUserID + ' OR UserID Is Null)' SET @ExecThis = 'SELECT D...more >>

The following SP run without problems: CREATE PROCEDURE sp_graph @name varchar(30), @query varchar(10) AS DECLARE @oid int Declare @sd datetime DEclare @ed datetime select @oid = ([BAMStats].[dbo].[fn_OID] (@name)) SELECT @ed = [BAMStats].[dbo].[fn_LastH](@oid) if @query = '24H' ...more >>

Hi, The latest copy of SQL Server magazine (Feb 2004) has an article on how 'UDM' will, in Yukon, bring together Analysis Services and Reporting Services. We heavily use the first and have been looking carefully at the second. The article describes the 'problem' whereby applications need...more >>

How is this usually done? Returning a recordset that has an updateable column for record selection. The remaining columns should/could be non-updateable. This allows some further function to be performed only on those records that the user has selected. Thanks! ...more >>

Hi, I'm working on a procedure to get data from multiple tables. In order to get all the data, I need to perform several selects, or join the tables. In this case I store the PK from the first lookup in the parameter @PACKAGING and use it in the next select-statements. The first output ...more >>

Can anybody tell me why this SQL statment works on one SQL Server but fails on another SQL server. heres the problem I have a simple table with for rows of data say Yellow Green Blue Purple the SQL statment is as follows declare @sline varchar(75) set @sline='' select @sline=@sline...more >>

I would like to add two tables to my SQL Server 2000 transactional article. I would like to create a TSQL statement that will add the two tables to the article and synchronize the publisher and subscriber database. Please help complete this task. Thank You, Mark ...more >>

I have a query (see below) that I could use some help with. The problem is it is returning a row I dont need, I have the information from that row already being placed in a column. My question is how can I get rid of the row. Select p.id, p.referencenumber, a.accountnumber, aip.polic...more >>

I have a stored proc that can take up to three optional parameters: Name, Phone, and ID Name and Phone are varchar, and ID is int I wish to select records based on the values in these fields, but the fields are optional. meaning that if I do not specify a value for a field, I don't care wh...more >>

Hello all, I have a data set that needs to be appended to a temp table. This data-set has unique rows appearing twice. How do I add append this data-set in the temp table as appearing just once. Here's what the data looks like: ID CARRIER CNT =================== 1 AIR ...more >>

Hey there I have a small not important performance problem.. .. How do I get at jobs current execution status the easiest way? I know the sp_help_job gives me the info. But I would like to get only the status not all the other information, without using temp tables and such. Please help !...more >>

I'm trying to query a database on a linked server (both are SQL 2000 sp3a), and I'm seeing some major performance problems When I run the following select column1 from table The query runs fairly quick When I run select function1(column1) from table The query takes several minutes The fu...more >>

I am brand new to SQL, so I apologize in advance if this is a simple issue. Given this table: Client Class Value A 1 100 A 1 200 A 3 100 B 2 300 B 1 400 B 3 200 ...more >>

HI, Something really strange is happening. I did the following: 1) Using the Profiler I watched a application call and execute a stored procedure. 2) I copied the EXEC line to the Query Analyzer and turned on the Show Execution Plan option. 3) I executed the Stored procedure and determined ...more >>

CREATE TABLE MMF (Section varchar(50), SortOrder int) INSERT INTO MMF (Section, SortOrder) VALUES('Test1', 1000) INSERT INTO MMF (Section, SortOrder) VALUES('Test1', 1000) INSERT INTO MMF (Section, SortOrder) VALUES('Test1', 1000) INSERT INTO MMF (Section, SortOrder) VALUES('Test3', 4000) INS...more >>

E' una settimana che ogni tanto ho difficoltà di connessione a certi siti (ho un'ADSL 256k tin.it, sempre funzionato alla perfezione). I siti cambiano di giorno in giorno (talvolta ho problemi con google, talvolta con slashdot, talvolta con pvponline), ma sono tutti esteri. Di solito riesco a sca...more >>

I have a table with a column that must be auto-computed. This column contains a progressive number, by year. I can't use computed columns (because I need to save the data and because the formula is based on a subquery). So I created a simply AFTER Trigger that calculates this column and saves th...more >>

Hi, I've successfully imported data using DTS from an Oracle Database to SQL Server. Now, I'd like to write a SP that will be called periodically to append the "new" rows of data that were added since the last update. I have no idea how to code a remote connection in a SQL Server Stor...more >>

I have a table with a datetime field that contains values like FIGURE 1. I want to chop the time and only return the date like 6/10/2003, but it does need to be DATETIME format. My question is my formula in FIGURE 2 the most efficient method? FIGURE 1 6/10/2003 5:28:45 AM FIGURE 2 ...more >>

A friend of mine is using a cursor to go through a table that only has four rows in it at the moment. The time to create the cursor and fetch the first row of data is currently about 10 seconds. Can anyone offer ideas as to why that might be the case, assuming the server capacity is fine? The...more >>

Hi All, I'm running Ent SQLServer 2000 and have an issue with Log shipping. My guess is my problem centers around non-logged transactions. Anyhow, we're still in the process of fine tuning the db and here's where the problem comes in: I use a DTS job to copy modified stored procedures...more >>

I tried to add Performance Monitor counters for "SQL Server" and they are not there. Memory,Physical Disk, etc. are there. I last used the above counters like SQL Server: Buffer Manager ten days ago. Checked all logs. It looks like they were disabled somehow. Please help. Thank you...more >>

Can you reference a table name from a variable? declare @version Char(10) set @version = (Select version from test1) insert into @version select * from test2 Thanks Dave ...more >>

Hi! I wonder if there any tricky solution for this using pure sql... I have SQL Query which returns set of rows. Is there any way to return this set N times? For Example: SELECT Employee FROM tEmployee WHERE emp_id = 1 Returns: John Smith I want SELECT 5 TIMES FROM tEmployee WH...more >>

Hi All, I am currently building an Intranet Application with SQL Server Backend. The NULL values in the table caused complication for my calculations and formating issues. I end-up creating error-trapping for a lot of those errors. My question is when designing tables to store data, is it...more >>

Situation : table contains a varchar field, inserts using hard returns are necessary, how to keep the content when retrieving this from an asp page. Sorry for the short questioning, I'm very busy currently -- Regards, Dandy Weyn MCSE, MCSA, MCDBA, MCT www.dandyman.net ...more >>

I am new to SQL Server. Trying to take a database from another serve and put onto my development machine. When I simply copy the file into the Data dir of my dev server, cannot see the database when I open Enterprise Manager. I cannot see to find any menu functions that relate to this ta...more >>

I have a stored procedure which accepts a parameter as below @type_entry char(2) What I want to do is check the value of the type_entry and perform the following IF passed value is 'EN' then @type_entry = 'EN' if passed value is between 12 or 14 then @type_entry = '12' if passed v...more >>

Hi, What exactly does the 102 do in the following statement CONVERT(DATETIME, 'Dec 31 2004 12:00AM', 102)). In BOL it says that its the style of date format used to convert datetime or smalldatetime data to character data. It was automatically generated when creating a view. Thanks...more >>

Hello, Is there a select query to bring all tables and fields on a database? Thanks ...more >>

I am attempting to create a stored Procedure with the capabilities of the Access Dlookup function Everthing works fine except I cannot figure out how to pass the value back to the @Value output. See the last line of code before GO. Thanks for any help CREATE PROCEDURE dbo.procDLookup @strFld var...more >>

sql2k sp2 Ive been getting this message on a new long running query in QA; (snippet) [MS][ODBC SQL Server Driver] Unknown token recevied from SQL Server [MS][ODBC SQL Server Driver] Protocol error in TDS Stream [MS][ODBC SQL Server Driver] TDS Buffer length too large [MS][ODBC SQL Serv...more >>

Hi all, The following query works fine on getting the 3 shortest times, but lets say the 3 shortest times are the same for example: 5, 5, 5. I want to get the next number thats not the same(duplicate), meaning the 2nd and 3rd would not be 5. Nor would the 3rd equal the second? the Answer...more >>

Hi, This code cleans up a string - removing all characters except space, = A-Z, 0-10 - the only problem is that it is too sloooow when used on = large amounts of data: declare @cn varchar(255) declare @LoopCount int declare @tempChar char(1) declare @tempString varchar(255) select @cn ...more >>

Hey all, I am trying to write a cursor with fetches some data and later sends an email to the managers. I have the cursor working allright but when I send an email to the managers all they get is the last row from the cursor.. I just cannot figure out what seems to be the problem. Can an...more >>

Hi, I wish to create a simple nonclustered index on a temporary table. I've read that it it just like creating an index on a normal table, e.g. CREATE INDEX IX_MyTemporaryTable_MyColumn ON #MyTemporaryTable(MyColumn) Is there a need to check for the existence of the index in the sysinde...more >>

the Query produces the following resil Licence Companyname status FileCount FaxCount XPages Counts TransBand1 IrishTrans IrishFiles EDDBand1 ------- ------------------------------ ----------- ----------- ----------- ----------- ------------ ------------ -----...more >>

*** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it!...more >>

I need to create an UPDATE trigger that can be delivered as a patch to multiple versions of the software. The problem is that it needs to update up to 100000 rows on another table. This results in tempdb problems (also due to the trigger needing to be rolled out at both 6.5 and 2000 versio...more >>

Hello, I am looking to print the table structure of a Microsoft SQL Server 7 database through the Enterprise Manager Utility. I would like to print it to a plotter but the print options in the Enterprise Manager are inadequate. I believe I will need to use another program to manipulate the o...more >>

How do you write a stored procedure which returns the first 100 records only? Is there a keyword that does this, if it is at all possible? thanks ...more >>

Hi All I am trying to backup just the transaction log in a SQL 7 Database. I then want to restore that log against an exact copy of the database from which I backed up the log. I am using the following command to restore the log: 'Restore Log Paymax To PaymaxLog1 With NORECOVERY' When I ...more >>

I create .NET wrapper for SQLDMO.dll using tlbimp utility. In C# I write the code: SQLDMO.Application app = new SQLDMO.Application(); SQLDMO.NameList nl = app.ListAvailableSQLServers(); After run the code, at the second line exception happend: " An unhandled exception of type 'System.In...more >>

Hi, Why these don't work and which would be better / safer to use ? ======= Example 1 ======= select * from dbo.syscolumns where id = OBJECT_ID('myTable') and colid = '2' or colid = '5' ======= Example 2 ======= select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'myTable' a...more >>