I have a situation where I need to select the values out of 2 tables that are joined multiple times. I have a table "Family" that has 4 members "Father", "Mother", "Child_1" and "Child_2" who are of type "Person". Family ================= ID as uniqueidentifier not null Father as uniquei...more >>

Hi Guys, I have created a table containg a column with IDENTITY keyword. I have data in the table. Now i need to alter this table without IDENTITY for that column. Please help me get a sql script to do this one. The creation scirpt for the table is : if exists (select * from dbo.sysobje...more >>

your help is appreciated in advance ... I'd like to query a Table in SQL Server 2000 from an XSLT. What functions are available using what language that can be embedded in an XSLT document. Thanks...more >>

So I have the three giant store procedures that I wrote last fall, and they are in production. Now the business rules have changed so I have to tweak them a bit. Now that I come back to them, they are ENTIRLY DIFFERENT so now I have to waste a couple days analyzing how and why. Giving en...more >>

Hi All, if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Table1] GO CREATE TABLE [dbo].[Table1] ( [Col1] [int] NULL , [Col2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRI...more >>

How can I get all parameter information given a stored procedure name? Something like: SELECT paramname, len, type, directio, ... from x inner join y where spname = 'mySpName' Thanks! Jose Cruz ...more >>

Is there a system stored procedure to get the names of the tables in a database? thanks!...more >>

Thanks for your response. I got that query working, SELECT P.productname, COUNT(S.productname) FROM Products AS P LEFT JOIN Sales AS S ON P.productname = S.productname GROUP BY P.productname ; but what if there was another column CustomerId, which exists in both tables and I want...more >>

Hi, I have a table in sql 2005 and I would really like to export the results of select calls into a plain text file with formatting based around the columns. Is there some code (in C#) that will do this? Thanks. ...more >>

Hi all! Using VB6, SP5, SQL Server 2000. A third party ordering application is in use that uses SQL Server 2000 as its back end. I was assigned the task of developing a much smaller application that would take orders from the clients. Each order has a confirmation number that must be u...more >>

I have a large table 34 million records and I need to update a field. I am doing this in batches of 1 million records (by joining a temp table to the primary key). I am trying to monitor the update using SELECT COUNT(*) FROM myTable with(nolock) where newKey is null What is weird is t...more >>

Is there a way to get sql to accept a variable in the from clause? I needed to find all the tables in my application containing the field tag_no which was pretty easy. Hard part is getting counts on the table list. The tags are of two types, distinguished by an I or an M in column 6. My probl...more >>

I have productstable and a sales table. the only relation between them is through productname. Each Sales Row will contain a productname. I know a productid would have been a better candidate for relationship but for some important reasons thats how it was kept. Any way, I want to grab sales...more >>

I'm working with a team on a migration of a database from Sybase to SQL Server 2000. Currently I'm in the process of debugging a sequence of stored procedure calls wich are relatively long and nested. In the process of returning from the stored procedures, after the job done in the lowest...more >>

Hi, I want to deny server access to an SQL login account. I realize that this function is only available for WIN NT/2000 domain users, but I was wondering if there was a way to simply disallow access to server for SQL logins. I want to be able to suspend access to users, without having to ...more >>

Is there anyway for a stored procedure to delete a record but also return the data from the record? My first guess would be to copy all the returned fields to temp variables but this seems awkward. The call will be in a transation so if I fail to receive the data the transaction will be roll...more >>

Hi Freinds, SQL 2000 and Vision 2003 Pro I have a data model in visio and want to export to SQL database . I create the deault driver. but when I go through export database, it is givining me wrong datamodel to export, it is not picking my tables in visio that I create to export. What sh...more >>

Is there any way to block a truncate command on a table via a trigger or something else in MS SQL Server 2000? Thanks, -- Jason...more >>

Is there any way to configure and Identity column to recycle when it reaches the max value for the data type it is defined as? -- Jason...more >>

hi i want to know how can I catch an error occured in sql server with DELPHI when an error occurs in sql server the number of error and description of the error is sent to any programming language. for example I want to change the message in my application when the error occures? but I don'...more >>

We have a database that has about 50 tables, each with approximately 800K rows. These tables are imports of data from another system that are updated daily. All the tables have the same column, Id, for a primary key. The tables are accessed via views that serve mainly to assign meaningful name...more >>

Ok heres the scenario: 2 Tables Properties PropID - Int - Identity Class - Int Address - varchar Etc.. Property_Classes ID - Int - Identity PropID - Int Class - Int The properties table has one entry per property. The Property classes table can hav...more >>

I have a table that has a bit field called PHI, the user will specift true (1) or false (0) in the application, but I want to if it is true return all 1 and 0 marked fields and if false only the 0 bit marked fields. How would I write a query to include both if true and only false ones if false...more >>

We have a transactional system and upon update of certain fields, We want to copy some data from one database to another database when certain criteria is met. I want the process to be asynchronous such that the source process does not wait for the data copy to complete. Also, the information ab...more >>

Hi all, I have two tables: workgroups (wg_id, wg_name) workgroups_keywords (wgk_wg_id, wgk_keyword) Each workgroup has an associated list of one or more keywords. What I wanted do to at first was given a particular list of keywords bring back a list of workgroups that have at least o...more >>

Hi all, We have a sp that sends http requests. It uses Microsoft XMLHTTP component that is part of XML pack that is standard with SQL Server 2000 SP3. For an unknown reason the XMLHTTP component started to fail raising the error: OLE Automation Error Information HRESULT: 0x800c0005 I...more >>

I would like to know when my table schema was last changed or modified. unfortunately the company was not running a c2 audit nor sql profiler trace. Is there a way for me to know when was the object last schema change....more >>

Due to the large size db, I need to generate a SQL script and keep it in VSS. The script should contain all create objctes statemnts. Using 'Generate SQL Script' in the Enterprise Manager by right clicking the db is not helping. What I am missing here? Thanks! *** Sent via Developers...more >>

hi, in my event log I have the alarm Backup failed to complete command sp_executesql;1 The point is that I didn't find any scheduled backup jobs and I didn't find any *.bak file relative to the DB mentioned in the alarm. (I can see the DB file name in the text window at the bottom of t...more >>

i have 2 questions: 1. is there any way to watch the a SELECT as it running? i mean to c how it acts for each value that it meets and how it ignores each value that not contains in the 'where' clause 2. im trying to run a select query with variable on a COLUMN name for example : declare...more >>

I'm wondering if its possible to have a stored procedure that uses a variable as the "Top" number. ex. Select Top @Integer * from table *** Sent via Developersdex http://www.developersdex.com ***...more >>

I am looking for a solution to return control from a fired trigger to a store procedure. In case of error the trigger has ROLLBACK TRAN and RAISERROR and entire batch is terminated. The store proc has insert within transaction: Begin Tran Insert Into customers(Lastname) values ('SMITH'...more >>

The title is fairly self-explanatory, but basically, here's the SP in question. Let's say a user on the front-end enters in the @sdate and @Shipper parameter, leaving the others blank. Let's say an @sdate of '01-16-2005' and a @Shipper of 'BLAH.' The results that come back show a varying number ...more >>

Dear all, I'd need to retrieve data from a text file located in a folder from a SELECT statement. How could I do such thing? Is it possible obtain all the data and then show them on the screen or better, import them to a SQL table?? Best regards, Enric...more >>

Can someone please tell me if it is possible to create variables dynamically or with the use of a loop and if so how... I have the following SQL statement and would like to cut down the number of variables by using some kind of loop to generate the variables and update statement! Thanks....more >>

Hi! I have a table set with fields of numbers with 8 digits precision. I want to change the accuracy of those fields to only 4 digits precision so that I will be able to group all the rows that have the same value in these fields. How can I do this? thanks, Einav...more >>

CREATE PROCEDURE sp_test AS RAISERROR ( ' Hello!', 16, 1) GO ---- SQL query 'EXEC sp_test' from Access database (mdb file) returns 'Hello!' in popup window (ODBC Call). The same from Access project - nothing. Please help! MSDE2000sp3, Access2003, Win2003 (all updates installed) ...more >>

Hi anyone, I wish to build an ASP calendar table and when I click on the calendar specific date, it will automatically add the date into the SQL Server table. The table must be in Year, month and date -- regards seowhua...more >>

Dear all, There's a ghost inside the loop below posted. It's a very strange behaviour although it could be due to I am out of order. Never the field n_id own 4800 as value and besides of that, the rowcount is lost if you compare the value of that field with the value showed to the left ...more >>

Sorry for anyone who has seen this query and dataset before but this is a seperate question/issue which I am working on. I am struggling trying to get my insert statement not to insert record 5 because the ToURN has been used before in a previous record(1). Basically I am trying to write...more >>

Who invented the nested sets algorithm for SQL? Was it Kamfonas or Celko? The algorithms are so similar that clearly Celko must have copied from Kamfonas, or Kamfonas from Celko .. or both from someone else. (If you don't know what I'm on about, it's this: http://www.dbpd.com/vault/98...more >>

Hi, Why is it that SP passing in parameter of '' end up with LEN() giving 1 in the SP when it is empty('') string ? For example, my sample SP Create store procedure Test(@para1 char(10), @para2 char(10)) as begin if len(@para1) > 0 <<===== why this if condition is true when I v...more >>

Hi all, Is it possible to debug sp in client workstation by using sql query analyzer? how? It's only work to me when debug sp in the SQL server by using sql query analyzer Thanks in advance!! Martin ...more >>

First off. I am impressed with the new XML functionality and SQL 2005 in general. It's truly what I have been waiting for. As a partner we received the SQL 2005 CTP and I immediately started to address an issue I had with SQL 2000 using SQLXML and that is nested elements.. So what I did wa...more >>

Hi, What does this SP do? What is locked using this kind of locking? Thanks, Leila ...more >>

Hi, What does this SP do? What is locked using this kind of locking? Thanks, Leila ...more >>

Hi. This is a problem I have with SQL Server 2005 CTP, but I assume it must be applicable to SQL Server 2000 as well as it is quite ordinary SQL. I have a query that has a subquery in it's where clause. The thing is that the subquery is only necesary under certain conditions. I have a bit ...more >>