I have a db named 90db. When I try to execute "Use 90mydb" an error occurs (syntax error near mydb). Apparently the compiler interprets the beginning as a number and cant swallow the dbname. Is this a known phenomenen? Any documentation? Thanks!...more >>

ss2k ... i need to implement a "1-stage undo" for various stored-procedures (sps that typically manipulate multiple relational tables) ... so, basically, revert the various records associated with a given unique-key of the ultimate-parent-table to the state they were in immediately prior to th...more >>

Hello, Do you ever consider the idea of outsourcing some of your SQL development to a remote developer? Do you ever want to complete complex chunks of work, and not do it yourself? I sincerely appreciate all responses I get! Thank you for your response! Richard Rogers http://www.SQLSolut...more >>

Hi , Pls help me to find solution .I get Syntaxt error , when try to declare datatype as table DECLARE @tblResource AS TABLE OR DECLARE @tblResource AS TABLE( iPlant int, iProduct int,) Any idea how can i declare data type as TAB...more >>

Dear All, There is a table Customer with CustomerID: (Primary Key) CustomerName: and a table Transaction which holds all the transactions with TransID: (Primary Key) CustomerID: TransDate: What is the BEST SQL or Best way to get all the custo...more >>

I created a vb dll on my db server, how do I instantiate and call it's methods from a stored procedure? Thanks in advance, Chris...more >>

Hello All: I am attempting to use the BINARY_CHECKSUM function to determine the checksum associated with a database "record" that spans more than one table (to manage concurrency). For example: SELECT BINARY_CHECKSUM(*) FROM dbo.Consumers INNER JOIN dbo.ConsumerPhoneNumbers ON dbo.Consu...more >>

BlankHi Group, Please help me writing to flat file in a particular directory in a SQL SERVER Procedure. Also what I am writing in the file, I am getting as a parameter. --Amit ...more >>

I am trying to create a table on the fly using a select statement. What I want to do is to signify the table name with the month it was produced. I have the following but cannot seem to make it work. Can anyone tell me where I am going wrong declare @box_name varchar (20) set @box_name =...more >>

Hi all. I have the following join: Select * From Projects Left Outer Join Ownership On (Projects.ProjectID = Ownership.RecordID And Ownership.RecordTypeID = 6) Which joins a projects table with an ownership table. The ownership table however contains other types of ownership details (for ot...more >>

We have been experiencing an unpredictable behaviour in our SQL Server 2000 sp3a. When we use the following delete stmt: DELETE tb1 FROM tb1 INNER JOIN tb2 ON tb1.c1=tb2.c1 INNER JOIN tb3 ON tb3.c2=tb2.c2 WHERE tb3.c3=1 We get error 'Server: Msg 8624, Level 16, State 1, Line 3 Internal S...more >>

Hi All, During deployment , should I always install Client Connectivity for all Clients ? Is there any condition where Client Connectivity need not to be installed ? Thank you, Trist ...more >>

Hi Again! Is it possible to delete records with reference to another table view? for example i want to erase all the records resulting from this query SELECT * FROM tableA, viewA WHERE tableA.col1 = '2' and tableA.col2 = '1' and tableA.col3 = viewA.col3 thanks a lot! ...more >>

using DBCC stinstance and create the alert after? thanks LMT...more >>

Does anyone know of a method to find the next available number, out of a list of numbers that may not necessarily be in sequence or order? Per the DDL below, the next available number should be '3'. I realize that I can query the table, ordering by IDNo, and loop through the records until I find ...more >>

Folks, I was browsing through some old posts on google groups and came across this from John Peterson in 1999.... --------------------- -Hello, all! - -I'm looking to write a Transact-SQL stored procedure that will parse a SQL -statement into its component parts. Before embarking on this p...more >>

Hello, here's my problem: I've got a first table named "CRITERIAS" with cols : id_crit (key),client_number,name1,name2,area Given a client number i would like to build a CONTAINS query with his criteria(s) and search within a "bundle" column in a second table named "DATA" Operators (AND o...more >>

I have a client who I have provided an asp/msql2k application, this software is critical to his business and want access to data in the db for creating adhoc reports from excel. What is the best practice or alternateoptions to allow him (his staff) to qry none user data ie. his business raw data...more >>

Thank you for the help. I want to create a database that will trigger reports from the MailProcessingEquip. Please verify my code and let me know. CREATE TRIGGERS Reports_Completed ON MailProcessingEquip FOR INSERT AS DECLARE @newReport varchar(50) SELECT @newReport = (SELECT Device_...more >>

I've got a commercial Oracle database that I need to reverse engineer to SQL server. Using ER/Studio, I've generated the DDL for the tables, but one of the lines references setting an image datatypes default to Empty_Blob, not Null, and Query Analyzer gives me an error when it tries to interpr...more >>

Hi all, how can i convert datetime format value into seconds. for example i have 1900-01-01 00:06:12.000 which should be converted to 372 secs. thanks, joel ...more >>

For deleting the duplicate row .. In Oracle we have- Delete from AG t1 where t1.Name in (select Name from AG t2 where t1.Name = t2.Name and t1.ROWid < t2.Rowid) how can we do same task in MS SQL ? Is there keyword like "Rowid" in MS SQL ? MrBug ...more >>

We are trying to Commit and rollout transactions that one sp will update = a table in one server(2000), then it will call a stored procedure that = will update another table in another database in another server(7.0). I get the following error message Server: Msg 8525, Level 16, State 1, Proc...more >>

I have the Month and the Year in separate passed in parameters to a stored procedure. I need to create a start date such as 10/1/03 from the Month and Year. I did this before about a year ago but just can't remember what I did. Nothing I try works. Please Help. Thank You....more >>

How would you implement a unique constraint on a pairing of a VARCHAR and a BIT? e.g. EmpName(VARCHAR(25)) & Bonus (BIT) ...more >>

How do I take the results of a stored proc and put those results to a file which would include headers and line feeds? Thank in advance for your assistance! ...more >>

Hello, I am not sure how to do the following in SQL. I need to be able to determine how many years old someone is<EX: 1,2,3,4,5,...> inside an SQL Statement. I am planning on using getdate() and a field in my selected table that has the date of birth. All Help in what options or how to do ...more >>

MSS2000 Is there a way to know, at the moment you are getting a local date, if you are in Daylight Savings Time or not? Thanks! Jason ...more >>

How can I return transactions in a query that occured during the last week? Is there a function in SQL Server that gets the beginning and the end of a specific week? ...more >>

After a lot of poking around on Google Groups, BOL and Query Analyzer, I've finally figured out a way to make nested transactions work. However, I'd like to know if what I'm doing is a Good Thing(tm), and if not, why. A post in August 2001 (http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&o...more >>

Starting from scratch here... How do I create indexes in SQL Server tables? Any help is appreciated. lq...more >>

Hello, someone knows the way to do this selects executing once ??? I dont like use UNION, because I am doing two read to my databases, and my database is huge it is aprox 6 millions of records. So, what can I do in order to do just one select applying the condition ? For Instance: ...more >>

Anyone have any (stored proc) code to pivot/rotate text data? See below: I want to convert this: ID Field Value -- ----- ---------- 1 Name John Smith 1 City New York 2 Name Jane Doe 2 City Boston to this: ID Name City -- ---- ---- 1 ...more >>

I am having trouble coding this if statement in a stored proc. I want to say: if text10 is not null use text10 as lastroute else if text9 is not null use text9 as lastroute else if text8 is not null use text8 as lastroute else if text7 is not null use text7 as lastroute....etc Can so...more >>

Hi, I'm trying to figure out the best way to store a TimeSpan in SQL Server. The TimeSpan represents a time of day, so any value from 0 to 24 hours. At the moment I'm storing the Ticks value in a BIGINT field, but this is obviously using more storage than I really need, and has the effect of ...more >>

Hello, I want to store passwords for an application in a SQL table. In Access there is a password data type that blanks the contents of the field out so you can't see the password. Can anyone tell me if there is anyway to do this in SQL 2000 as I can't see a password data type? Thanks Ri...more >>

Hello I have many different tables, that most have the same structure.. they all have some primary key, some fields and mylinkid ... so what I want to make is to create a stored procedure, where I can pass the table-name, primary key, and the new mylinkid, what should happen is, that the proce...more >>

I'm about to create an "INSTEAD OF INSERT, UPDATE" Trigger, but I have a problem: How can I distinguish between INSERT mode and UPDATE mode, i.e., how can I know if the triggering statement was an INSERT or an UPDATE? Thanks for the help. PS: Sorry for my english. Jose Marciano (josema...more >>

(SQL Server 2000) I have what I thought would be an easy thing to pull off in SQL Server. Simply put, I am trying to get the value of the first column of the first row returned from a dynamically built SELECT statement. Sounds pretty simple. The catch is that the number of columns being r...more >>

The client I am working for develops and publishes websites for insurance companies. The sites contain healthcare provider information (Dr.s, Hospitals, etc.) that are approved on the searcher's "plan". We refresh the data on these sites weekly with new data provided by the insurance companie...more >>

Hi! I have online store based on SQL Server 2000 DB. I want to add search functionality so user can search for items. All items stored in DB and I use char/varchar for titles/descriptions. What logic people use to perform searches like this? Is there any online sources for general princ...more >>

How do I trap an error for the "exec (@stmt)" below when it fails because the linked server is down? This code does not do it. drop proc test_disconnects go create proc test_disconnects as declare @stmt varchar(200) select @stmt = 'select srvname from MAINSRV.master.dbo.sysservers' e...more >>

I'm using Dharma ODBC to create a linked server to a databalse residing on an SCO Unix box. The linked server looks good. However, when I tried to run select statments (or creating views), the following error messages always came up: Server: Msg 7315, Level 16, State 1, Line 1 OLE DB provider...more >>

HI All, I need help on another query. I want to display a fields value into column. DDL: CREATE TABLE [dbo].[Table1] ( [SSN] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Field2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] GO ...more >>

Hello Does using SQLSetStmtOption with 'SQL_MAX_ROWS' option really help in improving performance and reducing network traffic? Have you noticed and drastic difference in performance by using this? Shree ...more >>

I have 8 fields in my table: century1,year1,month1,day1,century2,year2,month2,day2 20 01 12 01 Null Null Null Null 19 98 01 01 Null Null Null Null I need to set century2,century2,year2,month2,day2 to the next day after century1,year1,month1,day1, so my date will look like this: century1,ye...more >>

Hi all, Is this possible to check the last modified date for stored procedures or user defined functions? Any built in function or system tables store such information ? Thanks in advance. Terence ...more >>

I want get the values in a row Maxa, Mina ,Maxx, Miny, Hi, I have a table with Values Like this Id =111 a=20031030 x=82711 Process=0 Id =111 a=20031030 x=82710 Process=1 Id =111 a=20031029 x=82811 Process=0 Id =111 a=20031029 x=82811 Process=1 Result should be Columns=id,Maxa, ...more >>

Hi guys, Here's a simple problem I have. I'm trying to overwrite a column in one table, with a column from another. As simple as that. Here's the statement that I wrote: update item set [description] = (select [basic description] from old_item) And here's the error I get: Server: ...more >>

Hi, I'm using the binary_checksum command to identify my changed rows. I'm using 4 columns in my table, 2 varchar and 2 int. binary_checksum(varcharcol1, varcharcol2, intcol1, intcol2) When I compare this result with the same binary_checksum on my destination table, then the result is allwa...more >>

Below is my WHERE clause: WHERE SOP10200.SOPTYPE = 2 and SOP10200.QUANTITY > 0 AND SOP10100.DOCID NOT IN ('SP','DF','TS') AND SOP10100.VOIDSTTS = 0 AND SOP10100.DOCDATE >= @StartDate and SOP10100.DOCDATE <= Left(@EndDate,12) AND (CASE @SelectBy WHEN 1 THEN (IV00101.USCATVLS_1 BETW...more >>

Is there any built-in property reflecting when any change (insert/update/delete), occurs to a table's data? Thanks...more >>

Hi, After doing a full backup, is it possible to run a TSQL command to verify that the backup file is ok. for example I might want to check that the actual back up file is indeed restorable or I may want to check that a backup file on my system is not just a text file that somebody has chan...more >>

My ddl: SELECT cnt,CONVERT(varchar(10),OpnDt, 101)AS Dt, SUM(CASE WHEN stype = 'Completed Work' THEN cnt ELSE 0 END) AS Comp, SUM(CASE WHEN stype = 'Process Capability' THEN cnt ELSE 0 END) AS Cap, SUM(CASE WHEN stype = 'Staged Work' THEN cnt ELSE 0 END)AS Stag FROM Lea...more >>

Hi All, I need a help on SQL (server 2000). CREATE TABLE [dbo].[Table1] ( [SSN] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Age] [smallint] NULL , [Address] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] Insert into table1 (SSN, Age, ...more >>

Hello, I have a database that was assigned to a specific user and the SP's on the database are owner specific. The database was recently changed to dbo. Is there any way to replace the owners identifier from the tables and views in a store procedure without opening them and doing a find a...more >>

Does anyone know of another (better?) way of investigating deadlocks, other than using trace flags 1204 and 1205? I've alwayes been using these flags and I was able to do my job but recently I got into an argument with someone else and I'm trying to learn if there is another (better?0 way. ...more >>

can i pad an integer value with 0's so it's like 0001 for instance... and if i cast it to a string the Lpad does not seem to work on a variable... I'm using SQL server 2000.... Here is a few things i tried... Declare @testint int, @testchar char(4) select @testint = 1 Select @t...more >>

I've noticed that not all table dependencies appear in Query Analyzer. I have a stored proc that I know is dependent on a certain table, but when I look at the dependencies for that table, that stored proc is not listed. I'm figuring that there is a 4,000 character limit that Query Analyz...more >>

I have 3 months data in my db, but I want to pull data by month e.g Aug, Sep, and Oct date format is: 2003-10-06 15:43:44.000 what function I have to use here. Thanks ...more >>

I'm hoping someone can respond with a document or feedback that i can show to management on design for stored proc's. here is the debate. our DBA wants the developers to put all action queries or select queries into 1 store proc. I said this will effect the query optimizer statistics an...more >>

I want to check for the existance of a record (with multiple column key) before I do an insert. A subquery in the insert statement would seem to be most efficient. What is the syntax of an insert to accomplish this? I have tried using "INSERT...WHERE NOT EXISTS (SELECT..." but I get an e...more >>

I have an import routine that takes submitted Excel data, validates the records, then either excepts it or returns the errors. One of the steps is to round submitted numerical data with the following procedure: DECLARE @FEE FLOAT SET @FEE = ROUND(@FEE, 2) UPDATE tblIMPORT SET FEE = CON...more >>

Hi All. I want to write a function into a cursor i.e. CREATE PROCEDURE sp_Name declaring parameters As SET NOCOUNT ON Declare @prmid Int, @B2Amt Numeric(9,2), @Paid2 Numeric(9,2),@Pro2 Numeric(9,2) Create Table name_ga(prmid INT) Declare MyCursor Cursor Fast_Forward For Select State...more >>

Hi, I'm pulling my hair out. I need to find all the records for a PRODUCT# where there was any drop in COST over time (SALEDATE) In other words, eliminate any records where the COST only went up over time. If we have 2 or more COSTs on the same date, include them. In the example below we wo...more >>