I am a long time database developer but not long using SQL Server. I am now working with a performance tuning group. One of the people on the group is instant upon not using a not equal (!=) in your where clause. He indicates that this forces SQL Server to use a table scan instead of indexes....more >>

Hi, I've got information stored in database B. However, I would like to retrieve this information by using a stored procedure placed in database A. Is this possible? Thanks! Bubbles...more >>

Does anyone know how to allow user to change sql login password within a custom application written in Delphi or VB? I have an application that each user have their own sql login and would like to allow them to change their password. I know there is a store procedure to call but it is in the m...more >>

Hi, I need to create a cursor, not from the result of a query, but from the result of an stored procedure that returns a result like a select command. I was thinking of inserting the result of SP into a temporary table and create cursor from that. But isn't there any better solution? Thanks in ...more >>

sp_tables returns a table with rows that has all the information about database objects. How would I select just those rows that contain user-created (not system) tables? Also, how would I return a single column just containing the name of each user-created table? Thanks! ...more >>

Does anyone know if you can write T-SQL that will turn columns into data in rows. This is the opposite flip of what CASE is used for. Example: Account Month1 Month2 Month3 101 100.00 100.00 100.00 102 200.00 200.00 200.00 ...more >>

Does anyone know if you can write T-SQL that will turn columns into data in rows. This is the opposite flip of what CASE is used for. Example: Account Month1 Month2 Month3 101 100.00 100.00 100.00 102 200.00 200.00 200.00 ...more >>

I don't know how to get SerialNo in multiuser environment so I write a simple test as followed, 1.create a test table as followed, create table test ( ID int identity(1, 1), SN int not null default 0, Nothing char(1) null ) go CREATE INDEX IDX_Test_SN ON Test(SN) go CREATE IN...more >>

Hi, When I: EXEC master.dbo.sp_reset_connection It gives me the error: Invalid object name 'sp_reset_connection' But the sp_reset_connection xp exists! Reason I want to do this, is I want to make sure that it is being called from PHP, and I'm not certain PHP is doing it properly.... ...more >>

I am creating a report (that will be displayed in a web page) that shows data of various activities in three groupings: week-to-date, month-to-date, and year-to-date. The report shows many activities, most residing in their own table, and so the same method is followed several times for the ...more >>

How does EM recreates columns without loosing values in the existing rows? Thanks, Costi PS: The only reference I found is in the online help for ALTER TABLE at section reffering to NULL | NOT NULL "Specifies whether the column can accept null values. Columns that do not allow null...more >>

I run the following script on Query Analyser window 1: Basically, I update a row in a table and run a loop to create a delay, within a transaction. ---------------------------------------------- begin declare @i int, @Name varchar(100) SET @i = 1000000 select @Name = Name from T...more >>

Hi I would like to create new table based on the existing table. Current Table: (3 columns) ============== ID Hist_ID HIstory_Notes -- ------- ---------------- 101 1 Open Position for JOB1 101 2 Paper Ad for JOB1 101 3 Invite candidate for JOB1 102 1 Open Position for ...more >>

At work we are attempting to optimize the query below. We have been working with our DBA and he is even out of ideas. As we are by far *not* SQL experts, am hoping someone out there can help us. Thanks in advance for any suggestions you may have.......Dena We recently upgraded our servers t...more >>

Hi Freinds, I need to know the databases on my server and the status of each database such as READONLY, DATA FILE, LOG FILE, INDEX FILE (if exist), database model and more. How can I do it ? Thanks in advance, Pat ...more >>

I have query where I display tableusage by month by table with a record count. SELECT SubString(TABLE_NAME,1,20) as TabNam,Max(KOUNT) as MaxCnt,DatePart(m,Date_Loaded) as Month FROM tbl_stats Where Kount > 5000 GROUP BY SubString(TABLE_NAME,1,20),DatePart(m,Date_Loaded) ORD...more >>

Hi Freinds, I have a SP which return 3 column I am creating table A with 3 column on begining of the script and want to save the result of Ps in that table. How cn I do it ? EXEC my_sp INTO my_table ???????? Thanks in advance, Pat ...more >>

Hello Experts, I have problem while making a SQL Query. For Example:- I have three tables Table1 Table2(TaskDetail) Table3(Results) Task1 Status Task1-ID-1 Task3-ID-1 Task2 ...more >>

I have a query that I would like to make into a view, but it won't save. The weird thing is that it will run and return data, but when I try to save it, it gives me this error, View definition includes no output columns or includes no items in the FROM clause. Here is my query (Jacco hel...more >>

I got a issue with xp_cmdshell I have only one hard drive. so when I run the following T-SQL, I expect to see file list in C:\: exec xp_cmdshell 'cd\' exec xp_cmdshell 'dir' how ever, it still give me file list under: C:\WINDOWS\system32. I just don't understand, it seems that 'CD' do...more >>

Hi I want to get the following result from my table in sql server 2000,how is it possible? family (parentcode+childcode=PK) parentcode childcode description ---------- ----------- -------- 100101null 100102 100103 101200 101290 102288 102311 103207 200398 200700 3...more >>

Some one sent me some some files with the extension of ..sql, what are the and how are they used? Thanks Taccea ...more >>

I have three tables. Main table is tblAds. I want to count all records in my 2 related tables. My desired outcome is: AdID,Count of AdId in tblImpressions, Count of AdId in tblClicks 1,2,2 3,1,0 4,1,3 5,3,2 for example for AdId =1 there are 2 related records in tblImpressions and 2 r...more >>

I'm trying to construct a DTS package in SQL Server 2000, with some difficulty. The data source is a DB2 database with 56K tables in it, all with the same table owner/schema. This is an ERP package's database. The target database only has around 70 tables in it. The problem is when I cre...more >>

I'm finding that when filtering records using the Query options in a Word Merge it does not have a comparison operator for "contains"; it just has "equal to" among others. This means that if you have (for example) a merge to Outlook contacts and want to select only records where the Category ...more >>

I've got the following stored procedure... CREATE PROCEDURE [dbo].[a] (@CompanyID_1 [varchar](10)) AS SELECT CompanySerialNo, CompanyID, Company FROM [TEST].[dbo].[Companies] WHERE CompanyID = @CompanyID_1 IF @@ROWCOUNT = 0 BEGIN SELECT CompanySerialNo, Company...more >>

I am trying to wrie a query that I am stuck on. What I need to do is total up some hours worked by a persons bill rate. But, I need to be able to select the rate at the time the work is performed. So, lets say contractor A ia making $23 hr in june and worked 35 hours and $35 hr in July and wor...more >>

Why does is take a ridiculous amount of time to compare two tables based on what they dont have in common vs what they do have in common...Im running a query and when I switch the = to a <> it just runs and runs and runs..how can I get around this? thanks...more >>

Hello, I am new to SQL2000, how do I take a script someone creates for creating tables, and RUN it so it creates the phisical tables? Do I use the Enterprise Manger and typing in a stored procedure? Your help appreciated taccea ...more >>

Having a hard time on triggers on SQL Server 2000. I have two separate = triggers on a payment file for an invoicing system. Was getting a = "subquery returned more than 1 value" error when I tried "set @ID =3D = (select prnt_id from inserted)" to get the key. Here's my process: 1) The trigger...more >>

I'm trying to figure out how to design my database to store entities in their own tables and then allow the attributes of these entities to be compared to each other for querying. So, in the example below I have three entities, WaterBottle, CoffeeMug and a WaterBottleCap. In the three entit...more >>

hi i'd like to alter a table from script. as specified within books online ALTER TABLE doc_exc ADD column_b VARCHAR(20) NULL CONSTRAINT exb_unique UNIQUE how can i also include information for the description? ...more >>

I am trying delete some records in a table that uses two columns to create a relationship between records. Basically, the first column has a number which the second columns relates to. Here is and example: Col1 Col2 01 02 01 So, record two is a child of record 1. I am tryin...more >>

I am need to generate a report with the following lay out if possible based on Date,fromid, toid,body: Would like to display name not id. Layout: Date Name Name Body ------------------------------ date Mary Joe this is a test date Joe Mary yes it is ...more >>

Hello! Let's suppose I have a table MyTable (A INT PRIMARY KEY, B VARCHAR(20)). I send an UPDATE script to sql server. In this script I modify the A column /the primary key/. In an AFTER UPDATE TRIGGER I would like by analyzing "inserted" and "deleted" virtual tables to understand ...more >>

Im migrating over from MySQL to MS SQL. I'm somewhat stumpted on trying to format a Date field. In MySQL I used to be able to go like so: SELECT Date_Format(DateAdded,'%b %d, %Y %r') As DateAdded FROM... To get a date along the lines of: Nov 23, 2003 8:37:09 PM Rather then 11/23/2003 8:37:0...more >>

Hello, I need help with changing a column data type from Text to a nvarchar(4000). I tried the syntax below in QA and I got an error: ALTER TABLE table_name ALTER COLUMN column_name nvarchar(4000) GO Server: Msg 4928, Level 16, State 1, Line 1 Cannot alter column 'message' because it...more >>

hello, I need to upload at night 50,000 - 100,000 records into a staging table for data migration purposes via BULK INSERT. records will be nightly removed from the table and max of records should be 100,000. (there could be more staging tables later) I have one file and one filegroup....more >>

Hello all, I would like to write a single stored procedure to update any/all values in a table. I want most parameters to be optional, and if I don't pass a parameter then the value in that column should be *left alone* (not set to a default value). Is this possible? I know I can use opt...more >>

I am trying to build a table that has the following. id--length is 12, its the primary key, its auto incrementing company_id--256,string,null for now note_title--50,string null for now note_content--256,string,null for now note_date--date format for whatever the dat is I am using sql manag...more >>

Hello, Have a table with web site info: website_id, url, name, description, date went live, ... and about 20 other fields Each site can belong to one client. Have a table with client info: client_id, client name, client address, client other stuff, ... and about 20 other fields ...more >>

Hi All, We have a client server application where a lot of business logic is executed in stored procedures in SQL Server. Our procedures use a lot of complex joins between 3-4 tables or more. While I dont have specific examples to show, I was hoping for some general pointers on how joins ...more >>

SQL 2000 CREATE TABLE [dbo].[Tbl1] ( [Username] VARCHAR(32) NOT NULL, [PkgName] VARCHAR(32) NOT NULL, [SessionID] INT NOT NULL, ); TABL1 Username PkgName SessionID ----------------------------------- jsmit...more >>

Hello, Is there a way to output a string/msg from storeproc when it's executed and the msg can be viewed (with Sql Profiler for example)? This will help me trace many issues with store procs by adding instruments to store procedures. Thanks! ...more >>

Hi, I have this sql statement to update some fields in Comet_Header table. Update Comet_Header Set CSC = iif(isEmpty(csc_code),'',csc_code + ' - ') + csc_desc where orderNo ='00012333' here, CSC, CSC_code and CSC_desc are the fields in this table. When I run this statement, I got "'IsE...more >>

I am running SQL 2000. I have the sp below I want it to do nothing if any of the insert statements fail. It isn't rolling back the transaction if the third insert fails from the loop. Any help is greatly appreciated Mike Stored procedure: CREATE PROCEDURE dbo.osp_insert_Instrument_Sc...more >>

Hi, I have created automated partitoned tables. When the DTS package runs a table is created automtaically with week# as extension. For example customers43 this week. And next week when the package runs customers44 is created. Now I want to insert the data in the partiotned table weekly....more >>

I have a query which randomly selects a name or two from the database, the only problem is that currently I have 10 queries for one ASP page, here is what the query looks like, SELECT TOP 1 EmpFName + ' ' + EmpLName AS FullName, D.DeptName FROM tblEmployee E INNER JOIN tblPosition P ON E.Pos...more >>

Hello, I have a temporary table that I would like to output in Excel format. I thought perhaps the BCP utility could do this, but I can't find the switch options, could anyone provide me with them or give me a better solution. By the way we cannot use DTS. ...more >>

If I make a PK on a column which is INT IDENTITY and then make FK references to it, it seems useless to be to use ON UPDATE CASCADE since you can't update an identity column. Is this true? Should I only use ON UPDATE NO ACTION? ...more >>

I've been on the Microsoft site and I can't find how to log a fault :-( I've create a really simple SP that basically has one if statement in it - The if statement never gets run as when I run the trace it sees it as part of the comment line above - If I change the way that I comment the line o...more >>

If from a DTS package, you need to combine 2 files to make one, which is the better, more secure way to do it from the following 3 methods, and why? 1) Have an Sql Task that performs the following: exec master.dbo.xp_cmdshell 'copy c:\temp\file1.dat+c:\temp\file2.dat c:\temp\final.dat', NO_OUT...more >>

I've created a stored procedure that runs great in query analyzer, but I keep getting errors when I try to run it from my Access front end. The specific error message I'm getting states "One of your parameters is invalid". Here's the procedure definition. Note that @SID is expecting a string,...more >>

Hi there We're rewritting and redesigned our core database but have a slieght delima with whats the best practice with regard to importing / utilising external data. Basically we use external product list from our suppliers which is automated into our existing database. say tbl_MSDProductLi...more >>

Dear All, We have a database with a Surname and Forename column. We have a second database where the we have a "FullName" column, this column is in either the format of Forename/Surname or Surname/Forename. I would like to loop through everything in the Second Database to see if it exists...more >>

I have a situation in which I need two stored procedures that do basically the same thing, but takes in a different parameter value and return less of the same data. I wonder is it best to do two different stored procedures or just one to accomplish my goal? The first sp take in the @email...more >>

Hi I'm in trouble... I'm trying to get some data out of a few tables, but my SQL doesn't do what I want. The tables contain some data about manuscripts and processes: maunscript table has manuscripts process table contains all the processes a manuscript has had assigned to it. So a m...more >>

Hi I have written an extended stored procedure DLL, thanks to the people who helped me on this. however i wonder if anyone could shed any light on my deployment issues that I'm having with it. Basically, I built it on my XP development PC with VC7.1. I can deploy it on this PC's local defau...more >>

When trying to navigate all rows of a cursor and update the rows using "update...where current of", the row is updated correctly, but when I do the next fetch, I get the same row back again? whats going on? Please help, See script below. PS: I know this is not generally something you wo...more >>

I get thiserror :error 137: must declare the variable '@Tablename'. But @Tablename is just there. My procedure is this: create procedure dbo.sql_Admin @Tablename varchar(30) as begin declare @field varchar(30) set @field='personID' select * from @Tablename where @field=1 go ...more >>

...more >>