Hi all I have some confusion amoung the joins in SQL Server I have two tables TableOne - having 2 records , id as primary key (1, 2) TableTwo - having three records, id as foreign key (1, 2 ,3) Query one -------------- select d.* from tableone m inner join tabletwo d on m.id = d.id ...more >>

I did a bad, bad thing. I set my default database to some database, then I detached it. Now I can no longer log in to SQL Server to reattach it. What do I do now? ...more >>

Hello Im trying to find the Peak hour of a day from the following table (keep in mind that this table will have my days, so i will need the peak hour for eacvh day in the table. DateTime Amount 1/1/05 1:00:00 1000 1/1/05 2:00:00 1000 1/1/05 3:00:00 ...more >>

Does anyone know how to create a column in a Select statement that will list a value in one column and a running total column in another. I need a result like the following from Table A ( Text, Amount) ... Text Amount Running Total Apple 15.40 15.40 Banana 20.00 ...more >>

Hi All, I have three tables (for simplicity, I'll include only mock data): Table A: PTNO PTCL QTY A000 Black 500 B000 White 1000 C000 Red 2000 D000 Blue 5000 Table B: PTNO PTCL Q...more >>

We have a web app that is demonstrating bizarre performance issues, but most of the work is all in stored procedures. The Web App, when the user clicks a button, pretty much just calls a stored procedure. So it sounds like it's the stored procedure that's being slow. Yet we can run the st...more >>

Why do I get an error when trying to make this function? It seems to me that I'm asking for something perfectly reasonable. BOL doesn't seem very helpful in this case. CREATE FUNCTION MyFunc (@dt AS datetime) RETURNS datetime BEGIN IF @dt IS NULL SET @dt = getdate() -- That line gets...more >>

'08/02/2005' might mean Auguest 2 2005 or February 8 2005, depending on some setting somewhere. Does SQL Server recognize any format as an unambiguous date string? I'm looking for something that does not depend on any localization setting. ...more >>

It is absolutely necessary to have SQL Mail running in order to send an email using a trigger? I had to rebuild my db a couple weeks ago due to a virus attack on the server. Prior to the rebuild it was sending emails famously by using a trigger, but I forgot to check on all of that before ...more >>

If I open my DTS pkg in Designer, and then save it as a .bas file to modify it, how can I bring it back in to DTS "Local Packages" in Enterprise manager? Thanks. ...more >>

Hello I have a question about finding the peak hour of the day. Here is an example of my table DateTime Amount 1/1/05 1:00:00 1000 1/1/05 2:00:00 1000 1/1/05 3:00:00 1000 1/1/05 4:00:00 1000 1/1/05 5:00:00 25000 1/1/0...more >>

Hi all, thanks for all the help with the previous problem. Now I have the proper results and everything is working perfectly. But now I have a new problem based on the results. I have a stored procedure that accepts a state, county and start/end date ranges. It queries the Coverage_Ranges ...more >>

I have a linked server setup on SQL2K SP3a with DB2 Connect 8.1 fixpack 9 to get the DB2CLI.dll; v8.01.09.700. Using OPENQUERY syntax a SELECT statement works fine. However, I am trying to insert a new row into the DB2 table with this statement: INSERT INTO OPENQUERY(MYLINKEDSERVER, 'SE...more >>

How to write a .sql script that runs on SQL-Server machine 1 but acts on a database located on SQL-Server machine 2 ? ...more >>

I have restore various dbs from one server to another server, this to include the msdb, model and master dbs. I wanted to stop the jobs from run on my new server, and rename the originating_server name using script below: use msdb UPDATE sysjobs SET originating_server = N...more >>

Hi All, I'm using windows authenitication for all my sql servers. I have a lot of NT groups that contain a lot of uers and sometime I don't remember who are members of a certain NT group. Is there any undocemented SP or any script out there that I can use to list all members of a certaint ...more >>

Hi I , am facing very typical problem in SQL Server 2000 while creating cursor. I am executing the below script, sp_BeginTrace starts a server side trace and returns the trace id. declare @TraceID int exec sp_BeginTrace @TraceID output declare Trace_Cursor CURSOR FOR select * from openr...more >>

FACTOR-JACO is an Informix linked server -------- Create View vw_FA_dailySales as SELECT * from FACTOR_JACO..ssfactor.sales100 go Got this error during execution: Server: Msg 7354, Level 16, State 1, Procedure vw_FA_dailySales, Line 3 OLE DB provider 'MSDASQL' supplied invalid metadat...more >>

I am doing a global search and replace on a text field using this query I found (see below) It works great but I have multiple search and replaces to do on each record. In some cases up to 30 seperate values to replace. Im trying to avoid looping 30 times over a list to replace each item but...more >>

Hi, I have a problem that I can't solve Table 1 idProduct, labelProduct Table 2 idProduct, purchaseDate, purchasePrice case n° 1 : a function returns numéric CREATE FUNCTION f_averagePrice(@idProduct int ) RETURNS numeric(18,5) BEGIN declare... select ... different operati...more >>

I have the following stored procedure how can i update more than just one at a time. Example if I have 2 users 2344 and 3424. Create Procedure SP_UserTURNON (@Usercode varchar(4)) AS Begin Update Agent Set User = 'Y' Where Agencycode = @Usercode END GO To run it I use sp_UserTU...more >>

Hi, I run a big query in my query analyser, from the execution plan, I see some tables are on "Clustered Index Seek" while somes are on "Clustered Index Scan". I am not very clear what is the difference between them? And what is the performance comparison? How can I make query to run on "...more >>

I have 150+ DBs on a single instance. Rahter than me going through each server's properties to find out its recovery mode, is there any easier way to find that information out using T-SQL? Thanks....more >>

Hello All, I have come across an issue that I am struggling to find a solution for. I will do my best to explain the situation below: I have 2 tables as illustraited below: TABLE 1 ItemKey [int] PK ItemKeySub [tinyint] PK ItemBase [int] PK ItemCount ...more >>

Is there a way to find out if a SQL job was completed successfully using SQL DMO? I want to use VBScript to do this. Thanks!...more >>

Hi guys, What is the best way to access the SQL Server thru dial-up ? by retrieving data as FOR XML will it be faster than the normal data retrieval process or is there any other best methods? Thanks a lot for your time, -Aruna ...more >>

I am building an asp.net website which uses a SQL Server database. I want to do data validation in the stored procedures and triggers, and raise SQL Server errors there when a user enters invalid data. The message that is displayed to the user (like "ABC is an invalid Product Code") should be ...more >>

Someone once told me how to get the Enterprise Manager to display the commands it's executing when you tell it to do something via a menu or similar command. For instance, if you use the Enterprise Manager to add a field to a table, you can get it to display something like: ALTER TABLE myta...more >>

Hi, I have a table with 21 columns, and have a couple of update triggers. One trigger updates two columns (lets call them Tom and Jerry) which are not updated by any other means. The other trigger acts on other columns. How can I determine within the second trigger that during an update, only...more >>

Hi. Can anyone suggest how I could create 1 column of results from 2 fields of a table? What I am trying to do is create 1 dropdown list that will contain information from 2 fields in a table. Please advise. Any help would be appreciated....more >>

Hello: I'd like to store the result of a execute function to a local variable, as shown in the code extract below: ::::::::::::::: OPEN cr_dbname FETCH NEXT FROM cr_dbname into @dbname WHILE @@FETCH_STATUS = 0 BEGIN set @fcmd=('use '+@dbname+' select name from sysfiles where filename l...more >>

Hi, I have a table to city names which I need to do a select and return rows which contains values which is almost like a provided value, i.e a search parameter. I have tried the SOUNDEX function and it worked, however it does not return a good enough match collection. For example, if y...more >>

Hello: Is there a way to store the result of an execute function to a local variable? From an extract of my code below, I'd like to store the filename being returned with the 'exec (@fcmd)' command to a local variable. Any idea how I may be able to do this? Is it possible? If not, any alte...more >>

I want to select the distinct customer ID records, yet include all the other fields... Is this the correct syntax? "SELECT DISTINCT(customerID),fname,lname,address FROM CUSTOMERTABLE" I am just not certain whether the distinct is trying to apply to all fields or only the one I have encl...more >>

I am trying to create a string that will be usind in an IN clause. The string can become very large, easily outgrowing the size of a varchar(8000). I decided to change to using text, since it has a much larger capacity. I am trying to test the code be building the string and then passing it...more >>

Hi all, Are there any standard locations for descriptive information about a database and about a table that are easily accessible in EM? For example, just as a field in the table designer has a place to enter a Description, it would be nice if the table itself had a readily visible place ...more >>

I am having problems opening a recordset in my vb6 program from the following procedure i get "operation is not allowed when the object is closed" I have another copy of the recordset with the while loop and cursor removed and it runs and creates a recordset in my program, I have tried it in a...more >>

Is their any way in SQL 2K to specify a condition for allowing cascade deletes on a foeign key constraint. For example based on the value of a field in parent or child tables prevent the deletes. thanks ...more >>

Hey all, >From a stored proc, what syntax/command would I use to count the number of processes running on a SQL Server (the whole server, not just one specific database)? Thanks much, Roy ...more >>

/* Is there a cleaner/better/faster way to do this? Do I need to use Table Variables? I am cleaning up an SQL 2000 database, it has Inventory Items which are orphaned in ARDT, PODetail, RCDT, SHDT, SHLD, SNDetail tables. So I need to find all orphaned Inventory items (invID), not in I2IX....more >>

I am having problems with an SP to update records. Here's the process. I have a table which contains a whole bunch of default values. The first time a user opens a screen, the default values are read, then inserted into a similar table against the user. This all works fine. However, once t...more >>

I have created a table variable and i want to use it in a Dynamic Sql Statement.(Before I created a temporary table.but becase of performance issues I dropped that approach.) the code is Declare @AccountQry varchar(4000),@QueryText varchar(4000) Declare @ApplicableAccounts Table (AccountNum...more >>

Hi, I am trying to update the data in one field with that of another field in the same table, I think I am going about it in a long wined way. Both fields are the same datatype. I am first of all creating another table: SELECT U_T_COMMITTED.* INTO U_T_COMMITTEDALL FROM U_T_committed...more >>

Hi, Thinking of stopping the use of Tempory tables and starting to use Table variables instead. Where are the object created and logging done ? Still to the Temp DB or the database that contains the Stored Procedures ? Or the database that the Storted Procedure is called from ? Do anyone...more >>

Hi anyone/everyone I desperately need help. I have created a stored procedure to build all the dates between two dates. i.e. the procedure picks up the min an dmax dates from a table (e.g. 2005-01-01 and 2005-01-03) then builds a record set containing all dates in the range linked to produ...more >>

hi, I'm using bcp utility in order to copy data from SQL table into an excel file & txt file. It works fine and copy the data, however I also need a first row with columns names. any idea how to do it? or is there another way (not via bcp) to copy data with columns (and not via DTS cause m...more >>

Note: this is for my use, not a project I am doing for someone else. I am not trying to get the SQL Community to solve my consulting problems. I have a table I use to keep track of sports gambling transactions. I use various tout services' recommendations. I would like to be able to generat...more >>