I have a table called e_next_number that holds a variety of different IDs currently in use. The columns are NumberType (nchar) and ID (int). My question is, what is the best way to insure a inquire number is returned, currently my stored procedure looks like this: CREATE PROCEDURE _GetNextNu...more >>

Few tables in my SQL DB are over 10GB and I need to modify the structor for these tables. I need to remove few columns and add few colums. What is the best way to handle this issue....more >>

Hi, I need to calculate or get effective permissions on database objects for db users and roles using SMO. If anyone has piece of code for it, thanks a lot! Regards, Brad ...more >>

Hello. I wish to update my TEST and a DEV databases from my PROD database. However, I only wish to update my lookup tables, as these values have changed in PROD. I cannot restore from PROD as I have stored procedure/other known changes in TEST and DEV that need to persist. I cannot delete t...more >>

I have a strange SQL Server 2005 issue. I can mimic this exact issue by creating two very simple tables, and a view and then selecting the view. The odd thing is that this does not happen in other installs of SS2005, even with the same service pack (SP2). create table foo ( f1 int ) cre...more >>

Hi, In my program i need to duplicate a table in a current data base. I'm thinkin' of reading the data base columns and then rows and so i create another table Is there any other easy and fast method with SQL Server 2005, because my idea is so slow I'm using VB 2005 Express with SQL Server 200...more >>

Hi everybody, I have a stored procedure on a local database that errors at the same line each time. That line is a delete statement that affects a database on a remote server. For example: Delete from remoteserver.dbname.dbo.mytable where datefld < @today is causing the stored procedure...more >>

we are going to add a rowversion col. to some 70 tables in our db for ETL timestamping etc. the problem that tables are larg and the alter stmt contain an update in it and the transaction log is filling up and running out of disk space. is there a way to do this alter with no log?? thanks ...more >>

Hi, I have to write a VIEW (must be a view) that returns all employees direct and indirect under the manager in the table. For example, I have this table and the result must be return as follow: Mgr Emp Deniz Kevin Deniz John Kevin ...more >>

I am trying to use the Information Schema to produce a listing of all views, columns, and what tables the columns come from. This works well, but it omits the columns that are aliases based on calculations. Is there any way to get this information? Even if I can just get a listing of aliases f...more >>

I'm working on reverse engineering project. I'm analyzing an existing database in order to find all tables for each particular screen of the existing application (code is not available). I created a stored procedure which can find some value displayed on the screen in all tables. My SP works ...more >>

hey all, i have a table: table1 -------- fld1 fld2 fld3 And i want search for text in all three fields. select * from table1 where fld1 like @parm or fld2 like @parm or fld3 like @parm is this what everyone has in mind for searching for text? thanks, rodchar...more >>

hey all, i have a table: table1 -------- fld1 fld2 fld3 And i want search for text in all three fields. select * from table1 where fld1 like @parm or fld2 like @parm or fld3 like @parm is this what everyone has in mind for searching for text? thanks, rodchar...more >>

I have an INSERT trigger in a table that inserts a new record in another table in another database (let's call it "D2"). Both databases are on the same server. The trigger works fine when I run it as System Admin. However, other users get Error 916, "Server user SMITH# is not a valid user in...more >>

we are planning to migrate SQL Server 2000 data into SQL Server 2005. Right now its 45GB data. System is very critical. Is there any quick and fast way to migrate data? can I use Attach/deattach database utility to migrate data? is there any good online tutorial for microsoft? thanks fo...more >>

I can not make a new table, or design any table because I get an ODBC error 207 that states I have four invalid column names. It says this same message for ANY table I try to design. I did a restore from before this started and it still happens. What is strange is this just started last Fr...more >>

Hi, I'd like to select some data order by count(*). Also, I'd like to group those count(*) in ranges. For example, I'd like to group count() between 1 and 5, 5 and 10, etc. So I would like to have something like have this: Range | count ----------------- 1-5 | 15 ---------...more >>

I keep getting this error when trying to import from a text file. I've saved the file in tab-deliminated *.txt format, in CSV format, and nothing seems to work. an example of a row of data is... 49 GCMD 4 2007 Act Total Business Deprctn 1563 1595 1577 1550 418 0 0 0 0 0 0 0 ...more >>

I am trying to define a stored procedure that will disconnect SQL Server inactive user connections that are inactive for 8 hours or longer. The function should ensure that no query, activity and/or functions are in progress when the disconnect occurs. ...more >>

Greetings, I want to add a blank row between groups of rows to separate them. select * from tbl1 where fname in ('Bill', 'tom', 'sam') returns Bill, 1, 2, 3 bill 4, 5, 6 tom, 1, 2, 3 tom, 4, 5, 6 sam, 1, 2, 3 sam, 4, 5, 6 I want to return this: Bill, 1, 2, 3 bill 4, 5, 6 ...more >>

Hello, I have an SQL Server 2000 stored procedure where I write to a file on disk using xp_cmdshell. SET @cmd = 'echo ' + isnull(@line1, '----------') + ' ' + isnull(@line2, '----------') + ' >> D:\Test\test.txt' EXEC master..xp_cmdshell @cmd I quickly have to convert this to SQL Se...more >>

Hi, I need to write a query that returns data if an employee has taken four or more consecutive holidays. I need to get the startdate and the employee name. The employee works either a 4X10..or an 8X5 shift... Also, the employee need not have an off only on Sat and Sun...Some of them work ...more >>

Hello! I'm picking up work from somebody that is no longer with the company. I see that in all of his scripts he first drops the index and then drops the table itself. Is it necessary, recommended, etc. to drop the index before dropping the table? I thought that dropping the table automaticall...more >>

me not at all a CQL guy. Have a file called SMTP Outbound Mail - Top 100 Senders by Count.rdl I want to make some changes to this file. How can I modify this rdl file? Advise Please. Thank you...more >>

Hi I have a table that inserts some of the values from master..sysprocesses and then this data is viewed from a .net app but a carriage return appears in the lastwaittype column. example table : create table mytable (lastwaittype varchar(64)) insert into mytable select lastwaittype from m...more >>

Hi, I have 2 jobs, the second one must begin only when the first one is finished. By using begin in sucess, it's look like the second job begin just after the start of the first one ! Thanks...more >>

In a table that has person forename and surname, is it possible to use soundex to identify for every row in the table what similar matches there are in the same table? ...more >>

Shall I manually shrink the database file after I purge a huge database table? After purging, there will be only 500,000 records left in the table (from 52 million records before the purging). If the answer is yes, after I manually shrink the database file, what if many records are inserted in...more >>

Hi all, I'm using SQL 2000 but I'm not a SQL professional so my knowledge is some kind of basic. Now I have the following problem: I have a table with several records for a single user. The records could have different priorities. I want to create a stored procedure which just returns the hi...more >>

hey all, I have the following tables: Departments ---------------- DeptID DeptName ParentDept Employees ---------------- EmpID DeptID Could someone please help me create my organizational chart? Do these tables look like the start of a good one? Am I missing any fields? Could...more >>

Hi! This is done on SQL 2000. I have a SQL code that is imbeded into one of my applications that selects bunch of field from user defined function( that function takes four parameters and returns a table.) CREATE FUNCTION [dbo].[udf_app_search] ( @AC varchar(5), @UserId int, ...more >>

Hi, When I use openrowset and opendatasource function in sql server 2000, i got following error. Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server. Have anybody know why it come and what is the solution. ...more >>

Hi, I'm new to CLR, so please excuse me if this is a stupid question. Is there somewhere a comparison table of the parameter data types to use? For example an "int" in C# works with an "int" in SQL, but when it comes to the more complex data types I'm still a bit weary, for example what ...more >>

Does SQL Server 2005 have 8060-byte row data limit? If I migrate SQL server 2000 to SQL 2005 would it be possioble to overcome that limit? Columns are VARCHAR TECH ...more >>

Dear All, My apologies for posting here but I cannot see anywhere to post this. We have had SQL Server 2000 for about 6 years now (I have been here 6 months) and between the 6 years we have had two changes of management and one take over. Consequently no one knows exactly what type of SQ...more >>

Hi, I have a problem with getting the right resultset for a query. I'm using SQL2000 and VB6. I have two main tables, one with users (PK is UserId) and one with messages (PK is MsgID). Each time a new message is added to the Message Table the user gets a messagebox (thru a VB6 program) t...more >>

Let say I have a query with the following structure: Select name, mdate, kdate from table1 a inner join ( select name, mdate from table2 b where a.id = b.id and mdate >= kdate union select name, mdate ...more >>

Hi, Sometimes I need to insert data into more than one related table at the same time. For example : Table X has an identity column named XX and this column is foreign key in table Y. So, if I want to insert some related data into both tables, first I should insert into X , get new identity...more >>

BACKGROUND I have an e-commerce site with separate web server and SQL server (SQL 2005 SP2). JPG images for products are saved in a table. Each night a stored procedure runs that uses XP_CMDSHELL to run a VBS script that extracts the images to files on the web server. The VBS script is locate...more >>

Hello there When i set output parameter i must (as far as i know) use it on calling the store procedure. Is there a way to run this store procedure and not use the output parameter? ...more >>

Hi, The scenario to which I am looking for a solution is as follows: I have a Table A, in which a record is inserted.The status for this record is say "Open" and there is a Quantity say 1000 associated with it, as one of the columns. script would be like: CREATE TABLE A( [Id] [uniq...more >>