Dear All, Is it possible to declare a cursor using output of stored procedure rather then select list ? Thanx in Advance for any help....more >>

Details: SQL Server 2000, SP3, NT 4.0 Server Application (VB6 ADO 2.6, 2.7) The application I've written works as expected when using Windows Integrated security from an Administrator's logon. ANY IDEAS? Database read/write to individual tables/columns etc., access to SPs and views work fin...more >>

when i execute bulk insert using a format file it is reading the data including the "" (the file contents are like "56452","534","43563563"........) is there anyway we can read only data avoiding double quotes.??...more >>

I'm trying to pass a field name - value pair from a webform to a stored procedure that updates a table. I don't get an error message and nothing gets updated. My stored procedure looks like this: CREATE PROCEDURE update_car @id varchar(50),@glue_id varchar(50),@field_1 varchar(10...more >>

I have the following table ActionID aDate 1 01/01/2004 00:03:11 2 01/01/2004 00:01:05 3 01/01/2004 01:02:35 etc (currently 200,000 rows but growing quickly) What I want is to get the ActionID that occurred just before a selected ID. eg. ActionID ...more >>

How to enable and disable trigger ??...more >>

Hi, I have some problems with my data on dataset coz when I'm in a multi user environment and I open my program on 2 separate pc and change the same record that is open on the other pc and saved my changes the other pc would not know about the changes because its disconnected from the sql se...more >>

IsNumeric() and IsDate() are "leftovers" from the VB6 days ... looking forward perhaps it would be better not to use them .... does anyone have suggestions on replacements ? ...more >>

I have a data within a field. That data is c:\artfiles\2004\southern.emb. How can I write a SELECT statement to retrieve just SOUTHERN.EMB? I want to return JUST the filename. Thanks in Advance. My job depends on this...jk. Jeffrey M. D'Entremont ...more >>

In Access, we have a function NZ() that returns "0" if a value is NULL. How can I achieve the same in SQL? Below is a sniplet of my code that sometimes is NULL, how can I force SQL to return a "0" if it's a NULL value? CONVERT(decimal(10, 6), DATEDIFF(s, tblDowntime.StartDateTime, tblDown...more >>

Using SQL2000 enterprise on 100+GB databases. We are constantly fighting the SQL server optimizer in that it will often choose a hash join, when a nested loop is supported by indexes. If specified as a hint [option (loop join)], the query runs multiples faster. Some examples would be 10 ...more >>

Sorry for the basic question ,but brainlock has apparently set in. When I run this: SET NOCOUNT ON UPDATE patients SET EMPI = 100100658 WHERE patient_id = 1033 AND EMPI IS NULL SET NOCOUNT OFF SELECT @@ROWCOUNT AS rows_updated ....the record gets updated, but rows_updated is 0. Same re...more >>

I have 2 identical tables: if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[t1] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1...more >>

I have table data: Grade Score Name 1st 90 Adam 1st 85 Bill 2nd 70 Corey 3rd 75 Dave 2nd 95 Eric I want to get name of highest scroe in each grade group: 1st 90 Adam 2nd 95 Eric 3rd 75 Dave What i...more >>

Is it possible to pass a table name as a parameter to a user defined function? What I want to do is create a table when the user logs into the application, and pass that table name to all udfs that need to use it. Thank you. -- At least one sentence in this post is wrong, but it could be t...more >>

Dear Group I'm having a very weird problem. Any hints are greatly appreciated. I'm returning two values from a MS SQL Server 2000 stored procedure to my ASP.NET Webapplication and store them in sessions. Like This: prm4 = cmd1.CreateParameter With prm4 ..ParameterName = "@Sec_ProgUser_G...more >>

hi al I am using dynamic sql to do a query with differents 'order' sentences and/or 'where' sentences depending on a variable I pass to the s ex create proc e @orden varchar(100 @criterio varchar(100 a declare consulta varchar(4000 set consulta=N'select pais from paises where '+@criteri...more >>

I have a field d_Date that is set to 'datetime' and defaults to GetDate(). How can I add 2 hours to GetDate() as a default? thanks ...more >>

I have two update queries, like so: update tmpEmployeeData set EmployeeID1 = @defaultEmployeeID where EmployeeID1 = 0 update tmpEmployeeData set EmployeeID2 = @defaultEmployeeID where EmployeeID2 = 0 I'm just curious if there's a way to consolidate those two queries into one? -...more >>

Reading the article in MSDN magazine on Yukon XML it says the XML data type must be converted to and from a CLOB (varchar(max) or nvarchar(max)). Doesn't this place a somewhat severe limitation on the size of the XML data that can be stored as a XML data type? I haven't found anything that say...more >>

question about inserting varchar data into a column let's say I have a table with a column to hold user_id info and it's type is varchar 15 If I try to send a string of length 16 into the insert statement, what happens when I atempt to do the insert (assume that I have not written any special ...more >>

I just got a request to change the collation on a column in our DB. Apparently the front end (Powerbuilder) does not sort the same way as the collation we're using, so they figure we can just change the column's collation. I've never really dealt with this before so I'm not sure what the pros ...more >>

I am using the following union: SELECT H.ORDNUMBER, H.CUSTOMER, H.PONUMBER, H.TERRITORY, H.ORDDATE, H.INVDATE, D.ITEM, D.UNITPRICE, D.UNITPRICE * D.QTYSHIPPED AS TotalInv, D.QTYORDERED, D.EXTINVMISC FROM intcom.dbo.OEINVH h INNER JOIN ...more >>

Hi, Can you explain to me what the bottom select query means? What is the "fn concat" use for? I caught this query while doing a daily trace... SELECT "employeeid","firstname" ,"lastname" FROM "northwind"."dbo"."Employees" ORDER BY ({fn concat(({fn concat("lastname" ,', ' )}) ,"firstname" ...more >>

Can any one suggest me the best way to connect to a remote SQL server using the internet. The option that I know is: Use of remote IP address to connect. Is there any other options availabe to access the SQL server like using ASP pages or some thing else. ? I want to ensure that the connect...more >>

Table Customer is as below: Fname Lname Age Ash Knight 25 Doug Mutt 35 select Fname +' '+ Lname as CustName ,Age from Customer where Custname='Ash Knight' I know above query will not work. One way I found is select * from (select Fname +' '+ Lname as Cu...more >>

Hey, what type of lock does an update/inser/delete set on a table? Thanks, Mike...more >>

Hi all, Please help me to understand this: -- begin script create table test (field1 varchar(50), field2 varchar(50)) go insert into test values ('a','b') insert into test values ('c','d') insert into test values ('e','f') go -- create view vtest as select * from test -- this is a...more >>

I am trying to cleanup an eligibility database that contains a personID, Begindate, and enddate fields. For a single person I can have multiple timespans soem that are independent of one another, and some that are whole or partially contained within another timespan for the person. An example is as ...more >>

Hello. I have a situation where I need to add criteria to an SQL Select query which is built within an API (so I can only modify things after the WHERE statement. What I need is to return the records which match the MAX Order_No for one particular customer. The query below works only if the customer...more >>

Hi I'm trying to run queries across two DB's over two different Servers, and I get the following message: Server: Msg 18456, Level 14, State 1, Line 1 Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. The Query that I'm running is : SELECT * FROM Server1.DB1.dbo.TableName WHERE...more >>

Hello, I've been using the following script to send ZIP files from one server to another for a while with no problems: **************************** BEGIN **************************** declare @command varchar(200) set @command = 'xcopy c:\<LOCAL_PATH>\*.ZIP \\<SERVERNAME>\<SHARE_NAME>\ /D /...more >>

Good Day! :O) let's say i run the following script that create a temporary table with some constraints on it.. : ------------------------------------------------------------- CREATE TABLE [dbo].[#KitDes] ( [KitDes_Ref] [int] NOT NULL , [Header_No] [int] NULL , [Kit_From_Module] [smal...more >>

The code below gives me an Error 156: Incorrect Syntax near the keyword 'SELECT' Any assistance is greatly appreciated Thank you INSERT INTO [program_management].[dbo].[ecr_evaluations ( [ecr_id], [user_id], [affected_item], [state], [user_id1], [time_stamp]) VALU...more >>

I inherited a database that has a significant number of tables written = to by stored procedures which have no primary keys. I want to correct = this situation, in the long term I want to place the appropriate keys on = the tables, in the short term, in the interest of a replication project = f...more >>

This is the table I have __________________________ startDate datetime setupTime int (in milliseconds) runTime int (in milliseconds) qty int I need to calculate the endDate, by using this formula: startDate + setupTime + (a.qty*a.unitRunTime) = endDate (readable format such...more >>

This seems simple, but it keeps tricking me. Could not get the proper result. Thanks in advance! TABLE1 App_ID Business_Unit_ID PROFILE_ID 1 001 AA1 3 001 AA1 TABLE2 - SUPERSET App_ID Business_unit 1 001 1 002 1 003 1 007 2 002 2 003 3 001 ...more >>

Just an example. I don't think that you need DDL to solve. This is part of an SP. If @compID = 0 then I want rows for all companies, if not, just the specified company. This does not work. I get an error message that (select compID from companies) is returning more then 1 value. What's wro...more >>

Win2K/SQL7/Access frontend I need some advise on how to either store pictures or link them to particular records. Thanks, Tony ...more >>

I would like to restore my SQL Server TP database that was backup on a remote server nightly. I use the XP_CMDSHELL command to copy the file to the restore SQL Server. Next, I have a route the searches and kills all connections to the database. Then I try to place the database in singl...more >>

SELECT ps.F1, (SELECT ISNULL(SUM(tbl1.F2), 0) FROM tbl1 WHERE ID = ps.ID) AS Result1 FROM ps WHERE (ps.F1= 7) GROUP BY ps.F1 When this SQL is ran, I got the following error: Column 'ps.ID' is invalid in the select list because it is not contained in either an aggregate function or the grou...more >>

In the REPLACE command, can one use a column name as the first argument?...more >>

I'm troubleshooting an issue where we are using a 3rd party software to connect to a SQL Server database. When doing a trace we see the following statements repeated many times: SET NO_BROWSETABLE ON SET NO_BROWSETABLE OFF IF @@TRANCOUNT > 0 COMMIT TRAN These statements do not appear on oth...more >>

Hello, I need your advice, I want to create a user that should not be able to cause any data change to the database. How do I do that? I have tried to put these on the user: - public: ON - Db_denydatawriter: ON - Db_datareader: ON With those settings, the user cannot execute any store p...more >>

Hi, Can multiple tirggers(same) execute on same tabe at same time ? (or triggers are queued and executed one by one) What I need to implement ? Tables: material_Sales (contains sales rows) material_Info (contains material info, calculated qty in stock) I need to update material qt...more >>

Hi, I'm trying to find out how to schedule a stored procedure to run at a particular time interval. I know I can create a Job to run T-SQL through SQL Server Agent, but is there anything that can be done in the database to have it run a Stored Procedure at a scheduled interval? I'm running...more >>

Hi, I'm trying to use a 'between' condition within a CASE statement, but I get an error. I don't want to have to repeat the CASE expression for each 'When' because it's pretty long. Anyway, here's what I want to do: CASE myVeryComplexSubqueryThatReturnsAnInteger WHEN BETWEEN 1 AND 5 ...more >>

Hello I have a db that is 5mb large and a logfile that is 110mb, ive done lots of inserts, updates and such when building it. the recovery model is full and it backs up every day. how can i shrink the logfile without loosing any data? shrinking data feels unsafe for me but i guess i need to...more >>

Hi, What is the easiest way to show decimal number with thousands separator in email produced by xp_sendmail? Example 123456789.12 = 123,456,789.12. I'm using SS2000 Enterprise edition SP3 on Win2000 Advanced Server SP4 Thanks in advance Nikola Milic ...more >>

I get an "invalid use of insert in function" error. Please explain. CREATE FUNCTION [dbo].GetNextTrackingNum() RETURNS int AS BEGIN DECLARE @trackingID int DECLARE @dummyval char(10) DECLARE @exists int SELECT @exists = 0 SELECT @dummyval = 'dummy' INSERT INTO CONTROLID_SEQ (num) ...more >>

Hi sqlmasters.. So ther is a data type which has setting allow null not checked. And now how can I assume that this is really something ? When I give shot like that: declare @a MyTypeWhichoutNulls select @a Great wizard SQL give me.. NULL - hym.... Hot this option is used in SQL 2000 ??...more >>

Hello I have the following: Teams: Team A B C D E RoundPoints: Team RoundPoints RndNo A 4 1 B 3 1 C 10 1 D 5 1 A 6 2 B ...more >>

Hi All, I have a complexed query that pulling data from five different tables and post it to Intranet Application. Because of its complexity I splitted it up into 2 different queries. For best practice, Should I attempt to combine it into 1 query? Thanks in Advance for your suggestion,...more >>

Is it possible to run a dts package from a stored procedure how thanks....more >>

Hi, I'm fairly new at this I'm converting an existing company spreadsheet used for licensing, into a mssql driven web application. In an existing row of the spreadsheet, there exists information for 3 different companies. The same information is kept for each company, so I decided to make a sepe...more >>

Hi, i have several triggers which under certain circumstances i dont want to run. I would like to be able to set a variable against the sql connection which i can test for within my trigger and then prevent running if certain conditions apply. Is this possible? without using connection spec...more >>

Problem: I need to be able to adjust pricing on an entire order as each line is entered. The method I am currently evaluating is too slow. Can someone give me a "best practice" poke in the right direction? I tried to use a computed column which uses a user defined function to return the corre...more >>

Can anyone help I am trying to perform a select query from within VB connecting to a SQL Server 2000 database. I am trying to select and in the where clause i want to search for a date. The field 'MyField' in the database is set up as a datetime data type. See code below. --- dim rs as ...more >>

I have a view that joins two tables (1-to-1) using a left outer join. In Enterprise Manager, I am opening the view (return all rows) and attempting to insert data into the right side of the join. When I try to save, I get the following error message: Another user has modified the contents...more >>

Dear Experts, I've got an ASP script firing a procedure to insert the value into sqlserver. The error coming out of the database is dbExecute 22/01/2004 12:38:55 Line 1: Incorrect syntax near 'other'. Sql Statment: exec AddToCertAuditTable '0','322', 'CF Output', 'Batch205', 'Response...more >>

I have declared the following parameters for my sp @StartDate datetime @EndDate datetim I would like to have the parameters as dates instead, so that when I create a crystal report off of this sp, the parameters come in as dates. When the users prints reports, they have a date field and a time ...more >>

I have a table with following fields: key datetime value 1 2003-01-01 23 1 2003-01-02 24 2 2003-01-01 27 2 2003-01-02 28 ...... I need max value and corresponding key and datetime, so result would be 2 for key, 2003-01-02 for datetime and 28 for...more >>

I am trying to udate a group of records that have a 'newline' character within the column fields. i can use REPLACE('string',N'\n',N'') but i must specify the string value. Since i have multiple records that need this correction, how can i query the entire column instead of each de...more >>

I have a need to create an sp where inside that sp I make a call to another sp passing in the appropriate parameters then have that result set be used in a cursor set that I can then walk in the calling sp. Does anyone have any sample code I can see? Thanks!! -Richard K ...more >>

I need to drop multiple tables meeting certian criteria. I have a query that generates a results table which contains a list of tables meeting the criteria. How can I use the data in the results table to drop the target tables? Thanks,...more >>

Is there anyway of increasing the timeout for a query over a linked server? Sometimes my update query timesout. I wondered if there was any way of upping the timeout limit on the linked server in order to prevent this?...more >>

Hi I'm new to using cursors and am having a problem which might be a simple fix. If I run the SP it returns the dataset as required but if I run it again it returns no records. It appears that the second time around it's not looping through the cursor, the @@FETCH_STATUS doesn't seem to set back t...more >>