I have a fairly complicated stored procedure that assembles a string by replacing various wildcards. The replacement values are obtained by building a command and executing it dynamically via sp_executesql. Everything works fine except for one thing - performance. Basically, I'm using two per...more >>

I have a database that is showing quite a few rid errors in the event logs....I know this is not a good thing, however I was able to restore a good backup which at this point that seems to be clean. My question is: Can anyone explain, or point me to a website that explains in detail what exa...more >>

I have a list of numbers seperated by commas. Is there are way to run a stored procedure like the one below. I know the SP is wrong, but you get the idea what I would like to do theNums = 3,4,6,9 for x = 1 to theNums.length UPDATE tempSalesDetail SET BTI = 1 WHER...more >>

Hi SQL Gurus, I need to make the SQL Server error message more user friendly : Is there any way to 'Redirect ' CHECK Constraint Violation into a User Defined Error Message ? So that, when a Check Constraint violated, my own error message will be raised/displayed ? Thank you for your help...more >>

I'm looking to find the numerical position of a row within an ordered result set. Is there a SQL function that does this? Regards, Jon ...more >>

OK, I have some familiarity with building basic to complex select statements to retrieve data but I am just getting into learning to use SQL to create program code in stored procedures. I have built a couple of short ones to manually build the select based on whether or not parameters are passed...more >>

Is there an SQL statement i can run to get all fields that are used as primary keys on a table. Even if there is a compound primary key, i need to select all the Primary Key fields. Thanks. ...more >>

I have a question about dynamic SQL in SP's. Let's say, I have a SQL statement made up of two strings a="SELECT * FROM tablename " b="WHERE this = that" How do I cat together two string and execute them? The help file isn't very helpful. -- BV. WebPorgmaster - www.IHeartMyPond.com ...more >>

I added the following statement inside the cursor listed below and received this error. INSERT INTO TABLE1 (1, 2, 3, 4, 5) EXEC ('sp_depends ' + @TABLE_NAME) __________________________________________________________ Error: Invalid object name 'TABLE1'. Please help me resolve this ...more >>

Is there a simple query that I can run to return table names and the number of records in the table? -- HTH Dale Fye ...more >>

Hi, I have a asp.net application with a SQL server 2000 as DB. I use a SQL authentication so on my audit trail will show only the userid of that DB as the person that does delete/insert or update.. How can I capture the person that logs in the application insted of that user I use at the db ...more >>

Hi, I've found a weired problem wtih SQL Server and was wondering if anyone could shed any light on it. If I create a temp table (or non-temp for that matter) the use the BULK INSERT command to import some data from a file. Then mod the table to add an identity field all goes well, until I...more >>

I am try to send table structures without data in it. What is the best way to recreate a script for table structure? Thanks, Culam...more >>

Hi All, I am trying to store scanned images in a SQLServer 2000 database using C++. Are there any samples etc or does anyone have some code I can look at to guide me? I am having problems using a CFile to load the data so I have tried using an ADODB.Stream, but I can not open it with th...more >>

I would like to create a trigger fires when a new table is created. Books Online States triggers can not be created on system tables so an insert trigger on sysobject is not going to work. Books Online recommends using INFORMATION_SCHEMA's to create trgiggers on. If I issue a select statem...more >>

Is it possible to write the results of an insert trigger to a csv file? I have a trigger that puts the results into the message of an email, but would like to have it create the csv file and then add it as an attachment. Can someone point me in the right direction? Thanks for the help. Here is ...more >>

i want to list only those servers which have SQL 2000 on the domain and not the earlier versions isql -L lists all the server Is it possible to do so Prasad Pa ...more >>

I have an Access Database that uses an Boolean Datatype. I would like to convert this database to Microsoft SQL server 2000. My problem is MS SQL server uses a Bit Datatype and the program that update the table uses an insert statement that send True or False. Here is the error returned...more >>

I have a problem coming up with a query, here is the info: CREATE TABLE [dbo].[Table1] ( [PM_ID] [int] IDENTITY (1, 1) NOT NULL , [PM_SNID] [int] NOT NULL , [PM_Desc] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [PM_Date] [smalldatetime] NOT NULL , [PM_Amount] [numeri...more >>

Select top 5000 ClientID From tblClients, tblRange Where tblClients.ClientID not between tblRange.[Lower] AND tblRange.[Upper] I want to select the top X records from my table where there ID's are not between any of the numbers specified between the two columns...does that make sence? ...more >>

I have created a script that combines data from two different databases on the same SQL Server (2000.8.00.760). The script executes several GO's (steps) in order to ultimately do a join. It works in T-SQL great. However, when I try to create a stored procedure and stick the sp in one of t...more >>

I'm using VB 6.0 SP 5 with ADO against SQL 2000. Two questions: I would like to reset an auto number field to 1 in a table when the table is empty. I was successful doing this in Access 2000, but to no avail using SQL 2000. I would like to be able to delete all rows in a table at t...more >>

I have a stored procedure set up on my computer that runs a query and sends out an automatic email. I followed the instructions for xp_sendmail and everything seems to work great with the Outlook 2000 set up on my client machine. However, we need this to run on our server. When we installed Outlo...more >>

Hi... I'm trying to decide on which datatype to use for a property in a .Net class which will get it's value from a SQL Server timestamp. It's an 8 byte value so it looks like it will fit in a Long (but only in .Net) so I thought I might convert it to a string (in the object) instead. It is nev...more >>

Hi All I have a connection problem on the following VB command. s = "ODBC;DRIVER=SQL server; UID=x; PWD=123; SERVER=SRV2003; DATABASE=db1" Set pbcn = OpenConnection("", dbDriverNoPrompt, False, s) x is Windows 2003 user account. Authentication is Windows But, if I replace ...more >>

I built an Intranet site with .NET and the users can access reports built with Crystal 9.0. The data comes from SQLserver 2000. I created all these reports using views. The data is for read only purposes. Would there be advantages of using Stored Procedures. I am having a hard time ...more >>

Running a query based on a view, based in turn on a union, gives me this error List Index out of bounds Any help, please? -- Thank you, Doru ...more >>

I have a query, shown below, which returns about 35 rows with the current data set. I'd like to instead return one row that has the plant_amt and oblig_amt totaled, so I would have one line, instead of 35 individuals. I am having a massive brain block. This should be easy. Can someone help? ...more >>

I'd like to create a stored procedure where I can pass in a list of column names to use in a select statement, in one variable. How would I do that? eg. Select X from Customers (where x = first name, last name, phone) Thanks, RickN...more >>

Hi, What is wrong with this? select count(*) from ( select * from DB1..T1 union select * from DB2..T2 ) The error message is: Line 5: Incorrect syntax near ')' Can somebody help? -- Thank you, Doru ...more >>

Hi All, I have done some work with SQL Queries in VBA and Access (mostly) but now have a need to brush up on Transact SQL which I have been told has some subtle differences in the syntax and functions available. If you know of a good site or MSDN article/whitepaper that would help to bring ...more >>

I have a trigger sitting on the table that supposed to log in tblChanges evry action query ran against that table. I mean if it was "update table set blah='f' where id=1" that query literally should be inserted into the tblChanges. Is there a global variable or anything i can use to get that act...more >>

Hello, I am trying to get a single result set by comparing 3 tables from the same field. These are the tables I am trying to write a query for and the result set below is what I am trying to get. Table: employee empid name updatedate 1112 Peter ...more >>

I'm still new to SQL Server. Does SQL Server have an object like a sequence in Oracle? Is the 'Microsoft SQL Server 2000 Reference Library' the products manuals? Isn't there a SQL referance in PDF one can download from Microsoft? Thanks Pete ...more >>

Hi to all, I want to know if there is a way of getting the position of the particular record in the record let say for example we have a record.. cntID Name 1 Richard 2 Lucas 3 Jesus I want to know the...more >>

Say I have a table which contains a bunch of transaction for someone. Anotherwords a person's id, a date time, and some information about that transaction, like CREATE TABLE MyTable ( [TransactionID] [int] IDENTITY (1, 1) NOT NULL, [TransactionDateTime] [datetime] NOT NULL , [TransactionC...more >>

I want to build a query that returns this info: CLIENT ThisYearRevenue LastYearRevenue Where the revenue columns are a SUM of data within different date ranges. Obviously, very easy to do SELECT Client, SUM(Revenue) WHERE daterange IS BETWEEN '2003-01-01' AND '2003-12-31' .. However,...more >>

Hi all, Had a poke on google put can't find anything pertinent; does anyone know of a stored procedure, or bunch of SQL, that will create DDL (mostly CREATE TABLE) in a similar vein to that of Enterprise Manager's/Query Analyzer's "Script Object To..." functionality? Currently using SQL Ser...more >>

Can any one help me understand why the execution plan changes on the same table if the date range is changed from "1 may 2003 - 28 oct 2003" to "1 jun 2003 - 28 oct 2003"? Why does it ignore the PK in the second case and therefore the query takes 8 minutes instead of 7 seconds? Is there a w...more >>

I saved a DTS package to a VB module, now I want to create an application that installs the package using a VS/VB.NET 2003, what project references do I need to make in order to run the code....? I've done this before, but it has been a long time, unfortunately I don't get to keep my sql server s...more >>

can any one help me how can i get columnname,constraint-name,constraint-string for a given table name. appreciate any help thanks ...more >>

Is there a way to programatically control a SSA job the way we can control a DTS?...more >>

I have another UPDATE trigger that I want to fire when the following fields are updated in the CONTACT table: Firstname Middlename Lastname Suffix Ssn Spouse Spousessn Homephone I’ve put together the following code: CREATE TRIGGER Update_Contact ON sysdba.CONTACT FOR UPDATE...more >>

Hi I would like to know how to grant access (SELECT, DELETE, etc) to a certain account in a stored procedure I need to do a stored proc that will change the level of priviledge of a user in the database. I need to give hi only SELECT and DELETE priviledges Any help or code example would help a...more >>

Can some one please help me out here. Normally i get these in the end but have been strugling with this one... If i have 2 tables, Table1 holds 2 IDs and Table2 holds ID/Description, how would i structrue a query to return 2 Descriptions? TABLE1 EntryTime ID1 ID2 12:00:00 5...more >>

Is there any way other than login name to determine who is (or what machine) caused the trigger? Thanks ...more >>

What Running processes are normal to exist on SQL Server 2K when nobody is logged in? Thanks ...more >>

I have a table in linked server which has 10000 rows. Iam using openqueyr and select * into statement to get the table in sql. Is it possible to load data in batches so that i can show %progress in user interface in VB. Thanks...more >>

I have to append a table every week. I run a SELECT statement with some calculated fields - about 15,000 records are in the result set. What is the best way to append a table from a SELECT result set? Thanks. Lynn. ...more >>

HOw do I add Xeon Extensions to my SQL db? thanks...more >>

I created a column with name [NOE-002] when i do a COL_NAME function i get NOE-002 how can i know that this column has to be accessed using square brackets thanks ...more >>

I'm generating dynamic sql statements and the executing them by doing something like this set @sql = somestrin exec (@sql I want to capture the results by doing something like this set @sql = somestrin set @var1 = exec (@sql TSQL doesn't seem to like it as the exec of a string spawns anoth...more >>

I have 3 tables: Transfer, Remove and Queue. Transfer has a Transfer_key column and Remove has a Remove_key column. Queue has a Type column to store 'Transfer' or 'Remove' value, and it has a Key column to store 'Remove_key' or 'Transfer_Key'. If Type column has 'Remove' in it, I have t...more >>

Hi, I'm trying to do a bulk insert with a csv file that looks like: "la la la", "more stuff", "la la la", "etc etc" "la la la", "more stuff", "la la la", "etc etc" "la la la", "more stuff", "la la la", "etc etc" "la la la", "more stuff", "la la la", "etc etc" ...... Is there a way to lo...more >>

hi, i do not know how to find the names of the servers running SQL Server 2000 on my network. i'll prepare a combo box for admins and they will select the server which they will work on. should i search the registries of the network computers? what should i do? thanx by name...more >>

Hi, I'm trying to dynamically create a column name and I can't seem to get this to work. I have a cursor, looping and adding columns to a table. This should give you an idea of what I'm trying to do: ALTER TABLE #TmpPointsLog_1 ADD 'Round_' + @Round_No varchar(255) NULL Can someone...more >>

How can you get the machine ID of the server that SQL Server is running on....more >>

I'm sorry for the long posting, I have tried to cook down my problem to as little as possible. I have a table that is structured to hold information of less than 1000 business objects that potentially all can have more than 5000 properties, a few of these properties are use very often and the ...more >>