Can someone explain exactly what this SQL below is doing? Thanks SELECT TOP 6 ProductName, ORD.UnitPrice, OrderDate, RequiredDate FROM ORDERS ORM JOIN [ORDER DETAILS] ORD ON ORM.ORDERID = ORM.ORDERID JOIN PRODUCTS PRD ON ORD.PRODUCTID = PRD.PRODUCTID WHE...more >>

> Dear all, > i had a table named saletable i had fields like > itemgroupid,itemgroupname,saleqty,saleamount and saledate. > i want to sum the saleqty and saleamount by every month and year.by every itemgoupname suppose that every year has january month and 31 days in january so i want that fo...more >>

hi Is there a way to determine the original value and the current value of a field within an update trigger? thanks in advance, Nir. ...more >>

How do you rename a column in SQL I have tried everything....more >>

Hi, I have a select statement from tb1 and tb2: select field1 from tb1 where field1 not in (select field1 from tb2) order by field1 I want to have the result inserted into tb3 using a SP. tb3 has only one filed, same type. How to do this? Thanks...more >>

Hi, I have the foll data in A COLUMN (col1) of my table col1 BALTIMORE MD 21224 FRIDAY HARBOR WA 98250-8109 Remember it's one column. I need to display the data as three columns City,state,zip so the result would be city state zip BALTIMORE MD 21224 How do...more >>

Hello - I have quite a few stored procedures that I have not yet put error handling code into. I intend to put most of my error code in the vb .Net code. What's the recommended error code I should include in my stored procedures under that circumstance? -- Sandy...more >>

OK, here's the table I am querying, is this enough info (aside from the specs of the query below)? CREATE TABLE [dbo].[tMasterSerialNumbers_Subscriptions] ( [iEntityID] [int] IDENTITY (1, 1) NOT NULL , [iProductID] [int] NOT NULL , [vchProductNumber] [char] (20) COLLATE SQL_Latin1_General...more >>

On SQL Server 2000 we are connecting to many AS/400 tables using Linked Servers. We are developing a process which runs solely on SQL using Stored Procedures. We are finding inconsistent results in the return of a query utilizing a linked table. The query: Insert into #tmpTableData(Control...more >>

We have a script that copies several backup files (F:\mssql\backup) to USB drive (L:) weekly. I would like to create a script that would check the backup files on the USB drive with the "restore filelistonly from disk" command. Output the logical and physical file names to see if there w...more >>

I posted this question in the DTS forum but haven't gotten an answer yet so I'm reposting it here. I get an error message exporing a SQL table to an Access database (97 ver). I'm joined to a workgroup so I supplied my Access acct and password in the DTS wizard access and I got this error...more >>

I am trying to join multiple tables using FROM joins. I want to pick up only OB/GYN Companies but am instead getting everything. Here's my SQL... SELECT COMPANY.[NAME], COMPANY.[ADDRESS], COMPANY.[CITY], COMPANY.[STATE], COMPANY.[ZIP], COMPANY.[MAIN_CONTACT], COMPANY.[MAINCONTCTPH...more >>

Hello All, I have a table with User_Id, Group_ID,Score, Date The data is as 1, a, 0,Jan 01 2005 1, a, 10,Jan 02 2005 1, a, 15,Jan 03 2005 1, b, 1,Jan 01 2005 1, b, 3,Jan 02 2005 2, a, 4,Jan 01 2005 I want the data as 1, a, 0,Jan 01 2005 1, a, 10,Jan 02 2005 1, a, 5,Jan 03 ...more >>

Hi, Is there any way to run multiple queries with each individual query as a separate attachment in xp_sendmail? I have 2 store procedure where it generates a report each in this email and can't seem to be able to get it to work by using xp_sendmail. ...more >>

I have someone who altered all of the data in our database [22 million lines] that added in a line feed and carriage return in one of the fields. It needs to be removed. How do I remove hex values OD OA from a database field?...more >>

hi, how to define the temp table to local, only the current use can see it: select * from into temp? ...more >>

hi, how to turn this off: .... (277 row(s) affected) (277 row(s) affected) ...more >>

background sql2k on win2k3 i started getting this error msg when accessing properties page of 'sa' and 'buildin/administrators' accounts in sql. Error 21776 [SQL-DMO]the name 'dbo' was not found in the Users Collection. If the name is qualified name, use [] to separate various parts of the ...more >>

Is it possible to do this? I am evaluating a bunch of possible solutions to a problem. Someone came up with this idea, but could not remember for certain if it possible. Thanks. ...more >>

A client believes that "a stored procedure should never have more than about 50 lines of code in it." No rationalle behind that thought. When pressed - she stated, "I heard that in a class once a long time ago." I think this person is an idiot. Is there or was there ever a time when a ...more >>

Hello, I'm trying this question again; please excuse the repetition. I have written an extended stored procedure using Visual C++ .Net (unmanaged). The procedure is intended to send out a UDP multicast message when new data arrives. The message doesn't seem to be sent. I have verified...more >>

Hi Freinds, SQL2000 I am using sp_help_revlogin to create the script for account to recreate on new server. It is ok, but when I am trying to add a server role or add database access to new users on new servre , it is asking me the password for verification. But on the old server it is no...more >>

Hi, I have a query to build a returned record as and order by Name: ID, Name 5 A 1 B 3 C '??? 4 D 2 E Now, I want to add a parameter (for example ID=3) to the query (or somehow), so the returned record only return: ID, Name 4 D 2 E How to do ...more >>

On a WXP Pro SP2 system with SQL2K, when I try to debug a Stored Procedure, I'm getting the error message: Server: Msg 514, Level 16, State 1, Procedure sp_sdidebug, Line 1 [Microsoft][ODBC SQL Server Driver][SQL Server]Unable to communicate with debugger on xxx (Error = 0x80070005). Debuggi...more >>

We have a need to get a complete list of people and associated email addresses from the outlook address book into our mainframe system. We need this to be a scheduled download. Since SQL server can run scheduled jobs, could we use SQL server to do this. Does SQL server have the ability to read...more >>

Is it a good practice to assign user permissions inside the stored proc code? I have a bad feeling about this. How to address this issue in terms of best practices? TIA... CREATE PROCEDURE dbo.myproc AS SELECT <columns_list> FROM mytable GO GRANT EXECUTE ON [dbo...more >>

I need to be able to read from tables while a transaction is running. This transaction could take 20-30 minutes. I don't want to be able to read uncommitted data that has been written in this transaction though. I looked at NOLOCK but that says it reads uncommitted data. Any suggestions? ...more >>

I ran out of fingers... How can I count the number of stored procedures for which the name starts with "XYZ_" Thanks! ...more >>

Hello. Does anyone know where there is an example of emails that are automatically generated based on query results and have certain variables from the query populate the email? More Detail - A query runs daily that identifies sales in certain locations. For each location (row) an ema...more >>

in the SQL Query Analyzer I can use select {fn UPDATELINEAGE(0x0, 1)} and get result set like 0x0100000001000000FF back from SQL server, no document describes what the syntax is for {fn UPDATELINEAGE(0x0, 1)}. Does any one know this? Thank you Xinmin zhou ...more >>

I always write and test my SQL code using QueryAnalyzer (or Enterprise Manager). I'm now wanting to write hierarchical (shaped) commands, but QA and EM appear not to support this. I can of course still test the individual queries that make up the shaped command, but not as a whole. Ar...more >>

Hi all, I have a question about how to write a select statement, I have a table of data that looks like :- Months Mileage RVDiff RNDiff 24 60 115.3 119.2 24 60 97.3 18.5 36 80 158.6 111.01 What I want to...more >>

Hi All, Below is my initial query, that returns totals for each week within the month specified. I hard coded the Week Columns to make it easier to work with. This works fine. /*---------------------------------------*/ Select T1.DataSource AS [Service Line], COUNT(T1.PoNumber) AS [Tot...more >>

hello, i have a sql script/batch that i run against a db every day on my workstation w/o a problem. recently i tried running it on my laptop (replication of usual db) i get an error on one select statement. the statement is a join b/t two tables, on a field of type varchar(256) . i've...more >>

Hi, If I run DBCC SHOWCONTIG WITH FAST, TABLERESULTS, ALL_INDEXES on our DB I get info back our indexes. As I understand it, an indexid = 0 is the index representing the heap of a table with no clustered index, so we don't need to worry about the scandensity or fragmentation of these for db...more >>

I have a DTS that will cycle though records coming from a FTP file. As it inserts some of the records into the database, it will need a call a web service to get photo information for each record it inserts to also write as a blob to the database. How/Can I have a DTS call a WS to get addi...more >>

Hai all, Can anyone help me in this following query. There are totally 16 cases like T1_1, T1_2, T1_3, T1_4, T1_5, T1_6,T1_7 T2_1, T2_2, T2_3, T2_4, T2_5, T2_6,T2_7, T2_8 and T3 A patient can be in any of the cases or all cases. This is maintained in a table as follows Patient...more >>

Hi there! I really need some help, well, that's besides the point, but I _am_ seeking information about how to generate TSQL script for granting database access to logins. I'm working on a program to generate database, table, view, etc. scripts automatically in a way to produce the same scri...more >>

I have a table where no keys are currently defined, so we have dups....kind of. In this table the account number with be the primary key, and we also have a date field. There are records in there that have the same account number, but a different date. I want to find the duplicates, which i...more >>

I would like to change all rows in a table that have the date range between June 1, 2005 to June 30, 2005 to the month of May. I haven't been able to figure out the SET code that would update the rows to the the month of May instead of June. The day and year have to remain the same. Any sug...more >>

Hi, I am trying to write a stored proc that will return data for use on a webpage. I would like the SP to return nothing if the ROWCOUNT > 200. How do I just return an error and no data? TIA...more >>

Hello, How can I check an existing index on a table? I have found this: IF EXISTS (SELECT name FROM sysindexes WHERE name = 'my_ind_name') DROP INDEX my_table_name.my_ind_name My Problem is following: 1. What happens when this index is on an other table (e.g 'my_other_table...more >>

How can I select distinct values from a table which has column datatype as Text. data sholud n't be truncated. -- Regards, Kassim....more >>

Hi, I have a query to be tuned. Do we have a wizard for doing this? the query is: SELECT mmt.transaction_id Transaction_ID, mtt.transaction_type_name Transaction_Type, msib.segment1 Item_Number, mut.serial_number Serial_Number, ccg.co...more >>

Hello all! I have got a problem, when I use this query it returns the expected results select * from tbl_list where keyword like '%' + 'abc' + '%' but when I use the same in stored procedure, it returns unexpected results: my stored procedure is this Create proc sp_getlist @keyword v...more >>

Hi all, I'm just started to learn about SQL Server 2000. I have a stored procedure called 'sp_UpdNewPassword' and I run the stored procedure in SQL Query Analyzer: exec sp_UpdNewPassword '001234','abc123' I got error the following message: "Server: Msg 7202, Level 11, State 2, Proc...more >>

Hi, I have to import Unicode data from a txt file to a sql server table. I'd like to use BULK INSERT with the FORMAT FILE option. I 've tryed with Datafiletype = WIDECHAR but without success. The error is "Cannot perform bulk insert. Invalid collation name for source column xx in format fi...more >>

Hi All, I use the DTSRUN utility with /L option to provide the log filename with path for logging. I think in Package property "Error File" option is same as the above but there the NAME will be given in design time. Also there is an option to log to SQL Server - MSDB and also to eventlog...more >>

Hi, I have a hierarchy of tables, like "Company" on top, with "Offices" underneath and "People" underneath those and "Contact info" under those, etc. Now, a record gets deleted in the "Company" table. This means the Offices and People attached to them get deleted also. I could do this v...more >>

Hi All, I have DTS which uses global variables and dynamic mapping to map to the variables as per the parameters that i send from the Stored procedure that used to call / execute the DTS. My DTS has to export data from a SQL Server (Join Based Query) to a TARGET ..DBF file. problem 1: Ho...more >>

Hi, I have this (syntax not verified): CREATE TABLE Colors ( Id INT PRIMARY KEY, Color VARCHAR(20) NOT NULL ) GO INSERT INTO Colors VALUES (1, 'red') GO INSERT INTO Colors VALUES (2, 'green') GO INSERT INTO Colors VALUES (3, 'blue') GO CREATE TABLE Candy ( Id INT PRIM...more >>

I have created a script that returns every column and row that is queried and some of the fields are blank. I want to only return the fields that are populated. Below is the script. Any advice would be appreciated: SELECT Relation.xparent_prov_id, Relation.Parent, Provider.DataSource_ID...more >>

Suppose i have function: CREATE FUNCTION MyFunc (@myParam int) RETURNS TABLE AS RETURN(SELECT * FROM MyTable t WHERE t.Col = @myParam ) i use it : Select t.col1, ... from dbo.MyFunc(123) my question is does it unnecessary to use function in these case , i perform SELE...more >>

Hi, I have a client PC program which fetches certain records out of a database. To ensure that changes made to this record are not overwritten by someone else, the record needs to be locked, so other clients can not open it, or open it in 'read only', until changes have been made by the firs...more >>

Hi All the below query returns the totals just fine; but I need the averages instead. Can someone please help me. I can't seem to use the AVG function here; probably because I don't understand how :) Select T1.DataSource AS [Service Line], COUNT(T1.PoNumber) AS [Total Of PONumber], ...more >>

Hi, Whenever I test duration of a SP through SQL Profiler, Why It was showing different execution time though the Database same ? Thanks ...more >>

Hi and Thanks to all in advance for help, I have 2 tables. What we want to do is grab the columns listed and then if the PRODUCTGROUP(int) column is larger than 4999 we only want to grab items that have ONHAND(int) in the other table (STOCK) is greater than 0. SELECT PRODUCTGROUPNUMBER, ...more >>

Hi, Is that possible to open a text file in stored procedure and read it through line by line and process it accordingly?? Thanks in advance Tony ...more >>

Hi All, My DTS Imports data from a Comma Delimited TEXT from to SQL Server table. Thsi works fine 99% of the time. But Fails some time by giving the below ERROR - that I Log in a text file. Step 'DTSStep_DTSDataPumpTask_1' failed Step Error Source: Microsoft Data Transformation Services F...more >>

I have a table that I am inserting records to via VB6 using an ADO command. If I try to insert a duplicate record I get the error message -2147217873 Access Primary Key violation etc This error doesn't jump to my error handler so how can I catch it? is there something I can do from with SQ...more >>

Hi I have changed mycomputername from test1 to test2 I can connect to sql server 2000 in query analyzer using test2 name , but when I give query select @@servername is gives old name test1 why this happens? Thanks ...more >>

Hi, Can I restrict INSERT, DELETE, UPDATE, INTO queries in ADO? I get the query as input from user. Run it using ADO connection. Display the result in grid. However I want to allow only SELECT queries. Is there a property on ADO which allows this? Otherwise I will have to do the parsing mys...more >>

Is it good idea to call to the function (that return int) inside the IF...ELSE e.g. DECLARE @param int .. .. SET @param = some int .. .. IF(@param = dbo.MyFunc()) ... ..... or better to use DECLARE @res int EXEC @res = dbo.MyFunc IF(@param = @res) ... .... What can u...more >>

Hello I have a problem with show all values from my database into my asp page! Example of my database: Namn: test.mdb Table: tblTest Columns: col1, col2, col3 The thing I want is to search the database for "2005-04-27" in the columns col1 and col3 but I can only get values from col1 !!! ...more >>

Hi, I need to format a data in the given format. AAA-AAA-AAA Where AAA stands for any alphanumerice. For eg. I am looking for something like this. FORMAT('AAA-AAA-AAA','ABCDEFGHI') Should return ABC-DEF-GHI I can get the above info using the below given sql, Select SUBSTRING(Field...more >>

Hi, My system is suffering long time blocking by one process (Here for simple, I name it Process A), I want to know what was blocked, but only saw 'sp_executesql;1' in the blocked process's (Process B) command window. And there are lots of processes blocked by second process (Process B). Th...more >>

Hi If I try to run a simple select statement "select * from table1" from the Query Analyzer , its executing fine. But when I tried to run the query by joining tables from the Query Analyzer I got the following error "[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData...more >>

I have a question on usage of statistic for non-indexed columns. I have a query as follows: SELECT DISTINCT Code, Desc FROM DocumentDim WITH (NOLOCK) WHERE Code IS NOT NULL ORDER BY Code, Desc Both column 'Code' and 'Desc' are non-indexed. I could see that sql is creat...more >>

Hi all, I have a short question regarding UDT's (SQL Server 2000 / Windows Server 2003). I specified a UDT named MyUDT (I am sure there are lots of such UDT's *LG* It is based on a 4 bit INT and should obey to the following rule: @value IN (1000, 2000, 3000, 4000, 5000, 10000) Wh...more >>

Hi, my tb1 already have a primary key, how to drop it and recreate a new one is auto id primary? Please help... ...more >>

Hello there I runed store procedure with transaction on it It had critical error Server: Msg 207, Level 16, State 3, Procedure stoUpdateExcelClient, Line 210 Invalid column name 'EmployerNo'. As a result of that i got an error: Server: Msg 266, Level 16, State 1, Procedure stoUpdateExc...more >>

Hi, Can i use 'Contains' with a ColumnName like SELECT * FROM <Table_Name> WHERE CONTAINS ( <ColumnName>, (SELECT <AnotherColumn> FROM <anotherTable> )) regards lara ...more >>