I have a stored procedure 'MyProc' that takes sql statement as parameter and returns max id. I need to create another stored procedure MyProc2 that executes 'MyProc': MyProc2: @MyID = EXEC MyProc 'SELECT MAX (id) FROM EMPLOYEES' and then: @MyNewID = @MyID + 100 print @MyNewID How do I...more >>

I have a table that contains the following fields Startdate, Enddate, and Amount. The Startdate is always 7 days prior to the Enddate. What I am looking to do is specify the Enddate which returns an amount but then in another view have the Enddate be 7 days prior to the Enddate specfied in the ...more >>

SQL 2k sp4 I have a performance issue that is perplexing. On the same server I have a dev and qa db. Tonight I copied from dev to qa a table that I'd been working on. When I started running queries against the table in the qa db I noticed performance was quite a bit slower than on the de...more >>

Hi, I have two tables tblA and tblB. Lets say the structure is like this: TblA sno int, fname varchar lname varchar email varchar TblB sno int, xyz varchar abc varchar In "TblB" sno would be duplicated. I want to write an query which would return the following result when ...more >>

According to BOL "@@IDENTITY and SCOPE_IDENTITY return the last identity value generated in any table in the current session. However, SCOPE_IDENTITY returns the value only within the current scope; @@IDENTITY is not limited to a specific scope." Could someone guide me what is "scope" an...more >>

I own a small business. Sometimes ago I bought a custom application to help me manage my inventories and customers infos. The vendor set up his software to run off SQL. Now I have an urgent need to go directly into the database to edit some infos. This custom application wouldn't let me alter ...more >>

Does anyone know if sql server has the capacity to embed a select statement within a value in the insert statement? insert into table1 ( report_date, employee ) values ( select rpt_dt from reports where rpt_dt = somevalue, select emp from employees where emp = id ) Thanks ...more >>

I'd like to replace some line feed in the varchar fields and can use some help. Thanks. -- bic...more >>

I need to to an update query that involves three tables: table Table_Dealer needs to be updated with data from table so_personnel and table so_personnel_title. Table_Dealer is joined to so_personnel where Table_Dealer.Dealer_Code = so_personnel.so_cd so_personnel is linked to so_personnel_t...more >>

I've been having some issues so forgive me if this double-posts. Because I have some large but static Filegroups within a database I prefer to backup my filegroups instead of everything at once. This is SQL Server 2000. The other day I backed up a file with this command: BACKUP DATABASE ...more >>

I was trying to do dynamic sorting in a sproc, but I am having trouble with columns of different datatypes combined with a sort direction. I tried this but as soon as I added the outer CASE for the sort order it blew up (Incorrect syntax near the keyword 'desc'.) Is there a good way to do th...more >>

HI, create table x ( a int ) ; insert x values(1); insert x values(2); insert x values(3); I want to get the print out result as the following. Can I do a loop here? x is 1 x is 2 x is 3 Thanks a lot!! Michael ...more >>

Hello , I am an admin on the production box and our production data base is running on SQL Server 2000. Couple of hours ago I did a select * from <Table> and I got the results. But all of a sudden the table dissappeared from the database. now it says invalid object. Me as an admin...more >>

I know there is not a single answer to this, but... How many indexes are too many on a busy table? In a book, I read not more than THREE if the table is busy! How do you cover all your queries with so few?? Thanks. ...more >>

We have two SQL Server 2005 databases on different locations. We want data to be sent from one database to another other over the internet several times a day. The size of the data could be around 5000 records and they could be either new ones or updates to present ones. I was thinking of ...more >>

I am trying to collect a few examples of SQL queries that were done with a "procedural mindset" and another solution done with a "set- oriented mindset". I have a short article at http://www.dbazine.com/ofinterest/oi-articles/celko5 with an example of what I want. Anyone got some examples? ...more >>

I am using SQL 2005 for an active database. I have auto_update_statistics turned on. I also have a scheduled statistics update every night. The problem is, the statistics seem to get stale rather quickly, and queries start timing out during production hours. If I manually update the stats,...more >>

using sql 2005 i have a function that is being used like a stored procedure (i didn't write it). the function returns an int value and is not being used in any query it is just called standalone. is there a performance difference between calling a function from a stored procedure versus call...more >>

I understand why this query results in an Ambiguous column name error - it's documented in BOL that ambiguities in the ORDER BY are no longer ignored in SQL 2005: USE AdventureWorks; GO SELECT AddressID, AddressID, AddressLine1 FROM Person.Address ORDER BY AddressID; But why do...more >>

When I try and create an update audit log using an update trigger for an update statement that updates multiple rows, I get an error because the Inserted/Deleted tables will keep a record of each row being updated until the statment is committed. How can I get around this problem? For Exampl...more >>

I finally have a good opportunity to use a recursive table. However, i'm not clear on how to do it. i need to get the sum of the past 10 days data for hi and sum for low for each day so that on 04/10/2007 i get the sum from this date minus 10 days 04/09/2007 to munus 1 days 04/08/2007 to...more >>

Hi all, Using SQL 2000 sp4 select top 10 CAST(Duration AS decimal(15,2)) / 60 from usage_bd..usage returns this in less then a second ..300000 1.800000 ..500000 2.500000 ..300000 1.100000 2.000000 ..300000 ..700000 38.100000 then when I add "SUM" select top 10 SUM(CAST(D...more >>

I always hear Raid 10 is the ultimate (esp. if you have over about 10% writes). And of course that makes sense. But what I don't understand is how Raid 10 performs better than Raid 1. Does Raid 10 perform better than Raid 1 for reads and/or writes? Or is Raid 10 just the ultimate because o...more >>

Hello - I am trying to use one stored procedure that returns information from multiple tables and use those fields to fill listviews in Visual Basic 6. First of all is it possible to do in VB6 and if so, can someone post some sample code or point me in the right direction? Below is my sto...more >>

I am trying to find a script that will list a user and/or roles server roles on a system. Basically I am looking for the name, and what type of roles are associated with that user/role I created a role called TestDBRole and assigned it permission of db_datareader db_denydatawriter but I have y...more >>

using sql server 2005 i have a number of stored procedures that call themselves recursively & i wasn't sure if i could improve them. here is a sample of one of them and i wasn't sure if there was some sql server 2005 trick i could use to make it run faster. my example is simple but in reali...more >>

When I say 'faster', I mean two seconds v. four hours. I have this view, and I'm joinining it to a table, SELECT vw.Field3, vw.Field4, tb.PK1, tb.PK2 FROM VIEW1 vw INNER JOIN TABLE1 tb ON vw.Field1 = tb.IX1 and vw.Field2 = tb.IX2 Now, PK1, PK2 is the primary key of TABLE1, and the only other i...more >>

SELECT COUNT(*) FROM myLinkedServer..myDB.myTable The above query gives me the following error: OLE DB provider "MSDAORA" for linked server "myLinkedServer" returned message "Data type is not supported.". myLinkedServer is an Oracle server and the Oracle table myTable has a CLOB field. Cha...more >>

Hello Eceryone! I am trying to convert an IF... ELSE statement to a CASE statement and getting Server: Msg 156, Level 15, State 1, Line 119 Incorrect syntax near the keyword 'CASE'. Server: Msg 156, Level 15, State 1, Line 120 Incorrect syntax near the keyword 'WHEN'. Server: Msg 156, Level 15...more >>

I have the following query, "SELECT DISTINCT TOP 10 eventTitle, eventDate, theDate FROM(SELECT DISTINCT TOP 10 eventTitle, theDate, (DATENAME(weekday, theDate) + ' ' + CONVERT(CHAR(12),theDate,107) ) as eventDate FROM dbo.tblEvents WHERE (onGoing = 'N') AND (theDate >= GETDATE()) ORDER BY theD...more >>

hi i have some indexes with very vague names that i want to rename to something more sensical if i use sp_rename like this table name is tbl_customer index is idx_1 new name i want idx_customername sp_rename 'TBL_CUSTOMER.IDX_1', 'IDX_CUSTOMERNAME', 'INDEX' does this cause the st...more >>

Dear Sir / Madam, I have a scenario where one of our production servers name have been changed a long time ago and I needed to diable some jobs that were failing. When I attempted to diable the job I got the following error: Error 14274: Cannot add, update, or delete a job (or it steps or...more >>

I'd like to download SQL Server 2005 Express Edition. Is it okay to install it on a PC that is running MSDE? How many concurrent users can 2005 Express support? Thanks....more >>

I have an SP that is having a problem returning and i can't figure out why. If i run the SQL within the proc as TSQL it returns but when run as an SP, it just hangs. I have greated exec permissions to the user account it runs under with no avail. It does not run from Query Analyzer. I have t...more >>

Server: Msg 2627, Level 14, State 1, Line 21 Violation of PRIMARY KEY constraint 'PK_my_table'. Cannot insert duplicate key in object 'my_table'. The statement has been terminated. is there a way to capture this error and continue? i want to insert 'NEW' values only and i do not have an id...more >>

I have a table that has records timestamped in the mm/dd/yyyy hh:mm:ss time format. I have created a view that summarizes the transactions based on mm/dd/yyyy. However, my problem now is that if i try to select from the view with criteria of date > xx/xx/xxxx I get all records returned. The...more >>

Hello, I have one table which is used to build tree structures (Tree structure is just like Windows explorer with folder's or files having a document_id and parent folder is category_id for that document_id). I need to find what are the document_id's present under a particular folder. The ...more >>

Hi, I found out one thing about SQL 05. It has sys.tables, sys.views, sys.procedures, but there is no sys.functions. Do I have to get it from sys.objects or from information_schema.routines? Thanks Ed...more >>

Hi All, I need to pass table name as a parameter to the stored procedure below. Stored procedure will perform some operations on each row. I am thinking to iterate over all the rows in the table using cursors. Can I use/open cursors with dynamic table name? I want something like DECLARE @te...more >>

There are times when I need to create a batch of SQL statements to be executed within a .sql file and import data into SQL Server 2005. To be able to do this I use the bcp utility, but I have to make sure that the xp_cmdshell feature is enabled. To accomplish this I place the following state...more >>

Good Morning Folks, I was wondering if someone may help me to understand and possibly fix, why my Select @@Identity from xxx... takes as much as 100 times longer than any other query run against my database. Normally the bulk of my queries run from 15 - 30 milliseconds. but my select @@Iden...more >>

hello, not sure if this is the correct place to ask this but i'm stuck and need assistance. I've have a e-commerce website built for my business. it's database driven and built using asp. the site has different 'departments', e.g. men, women, accessories etc.. the problem is that I am...more >>

Hey This problem occur when using the basic version of SQL Server 2005 which get installed when installing Visual Studio 2005! This database has a table named "Test" which has a column named Id of datatype uniqueidentifier. Below is the source code for a SP in my web project. I want this...more >>

Hello I was wondering if you guys could help me with this one before i use too many derived tables and subqueries :) CREATE TABLE #OrdersDetail ( OrderId int NOT NULL, ProductId int NOT NULL, Quantity int NOT NULL, UnitPrice smallmoney NOT NULL ) GO INSERT INTO #OrdersD...more >>

Hi i have to add in the users email address to a field within a table. The email address is firstname.lastname@domain.com. The first name and last name is already in a field of the table. Is there a way i can update every row in this table to format the email as above. Thanks Angela ...more >>

Howdy, I'm trying to run this simple query SELECT a.X, a.Y FROM table1 a WHERE a.X + ' ' + a.Y NOT IN ( SELECT b.X + ' ' + b.Y FROM table2 b ) and it just runs and eventually times out. Table1 has about 7000 records and table2 has about 20000 records, would that have something to do wit...more >>

Hi all, been trying to find an easy solution to this but unfortunately no luck so far. Inside a stored procedure I need to call another stored procedure which expects varchar(8000) - this is an xml string. I have the xml in a table and since FOR XML returns a string I do not think that I shou...more >>

Hi all, I have a claims table that has a date of loss column. I also have a policy period table which has a start date and a end date I am trying to join each claim row and get the policy period id, but where the date of loss falls with in the start and end dates of the policy peroid. Can s...more >>

Hi, I want to use for loop in sql 2000 stored procedure e.g. for a = 1 to 10 Loop Any help will be sincerely appreciated. Hardik Shah ...more >>

What is the most efficient way to retreive data from a view with appr. 150 fields over a TCP/IP network message system concidering network load versus work load in MS SQL selecting only requested fields? A) Select * from View or b) Select a, b, c, d and so on for about 100 fieldnames from V...more >>

Hello! How to determine new records in table of SQL 2000 database from Windows Application? I need to show them in the same moment as their show in database. Leszek ...more >>

Hi, I want to create a user to a database. The server may be mixed mode or Windows authentication. eg. database = 'Employee' Mixed mode: user = 'JohnS' password='john1234' Windows mode: user='johnPC' Thanks ...more >>

Hi All - I have a SQL 2000 database of 60 some tables, with constraints, indexes, PK- FK, and other relationships on them. I would like to drop all data in all tables, knowing that a lot of the tables have full PK-FK-PK-FK .... relationships cascading, and indexes and such. I want to keep int...more >>

Joe Celko seems to be on a continuous rant against treating the Bit data type as a boolean in SQL Server (because SQL defines no Boolean data type). In some cases I can see his point. However, if we take his advice to the extreme and never use the Bit data type to represent a boolean, what did...more >>

Hey This problem occur when using the basic version of SQL Server 2005 which get installed when installing Visual Studio 2005! This Message table has 2 records, so this SP should return 2 records. I even commented out the where clause because I thought there was the error. But even after ...more >>

Hello there I have now problem in one of my tables. I need to know historicly when each record has entered to the system. Is there a way to know it? ...more >>