Hai can someone suggest me what is the problem with my code? 1. Actually i keep getting the error: Runtime error '2147217871 (80040e31)' Timeout Expired. I am using "cnPubs.ConnectionTimeout = 0" this command in my code so it should not give me a timeout error.But i keep getting the...more >>

I'm trying to do a query which breaks out sales summed by day and also grouped by week starting date. The starting table looks like this: Initial Table ID SaleDate Amount 1 11/4/2003 100 2 11/4/2003 90 3 11/7/2003 50 4 11/8/2003 25 5 11/10/2003 60 6 1...more >>

Hello All, I am putting forward a typical situation: User A does not have Insert privilege on TableA, but he has execute privileges for all Stored Procedures. Inside the stored Procedure 'ProcA': CREATE PROCEDURE ProcA as BEGIN EXECUTE('Insert into TableA....') END When User A ...more >>

Hi to all Wiz out there How can I import excel file into SQL using VB6 and export back to excel. Thanks a lot for your help RicT...more >>

Does any one know what this is about i think its something to do with running the xp_cmdshell [Microsoft][ODBC SQL Server Driver][SQL Server]xpsql.cpp: Error 87 from GetProxyAccount on line 604 thanks mic...more >>

From a trigger, how can I send a user an email in mssql 2000? The user's email is in a field from the record created in the trigger. thanks, raul Rego rrego@njpies.org ...more >>

Hi, I want to output a count in a SELECT query of the number of records which match the following criteria: Field_A <= Field_B AND Field_A >= Field_C I gather I can use something like: SUM(CONVERT(smallint,Field_A <= Field_B AND Field_A >= Field_C) AS MyCount Is this correct? H...more >>

Hi, would anyone be kindly to help me ? Here is my scenario, hope that you don't mind seeing it. How do I be able to convert this using stored parameter as suggested or anything that might works ? Thanks. On my form: frmJobCostReport, I have a drop down selection where I select the JobNumbe...more >>

I want to know how to return a recordset from a stored procedure within more than one Select statement. The following will return a recordset easily at the IF statement, but, how can do return a recordset from more than one select statement at the ELSE statement SET QUOTED_IDENTIFIER ON G...more >>

Hi, I hope somebody can help me with my problem, I have a table with users, containing userID, username, loginDateTime etc... I want to create a stored procedure to delete users from the table where the datediff is more than 1 hour. I have this code: SELECT DATEDIFF(h, loginDateTi...more >>

I have attached an as400 from SQL2000. Can I run an update query on the attached 400? I have a stored procedure that updates and returns a recordset, can I run a stored procedure on the 400 from MS Sql? I have a stored procedure that just returns a recordset. Can I run that from MS SQL? ...more >>

How can I do the following?.... INSERT INTO FirstTable ( someColumn1, customUniqueIDcolumn ) SELECT someColumn1, (exec sp_mystoredprocedure) FROM OtherTable In other words, I need to copy 1 table to another, but during the copy, I want to access an SP for each and every row that I insert. ...more >>

I am trying to create a user function that utilizes Rand to return random values. In testing it works like a champ, and you can configure parameters for it, but when I attempt to create a function with the script I get the following error, and I can't figure out what's wrong. Server: Msg 443, ...more >>

Is there a way to determine dynamically if a database is in use in a SQL Script? I would like to rename a database in a SQL script, but only if it is not in use. What I am trying to do looks somewhat like this: while DATABASE_IS_IN_USE('mydatabase') begin waitfor delay '00:01:00' end e...more >>

Is there a way to determine dynamically if a database is in use in a SQL Script? I would like to rename a database in a SQL script, but only if it is not in use. What I am trying to do looks somewhat like this: while DATABASE_IS_IN_USE('mydatabase') begin end begin exec sp_rename 'mydata...more >>

I am attempting to join a SQL table to a pervasive one. Here are my tables. CREATE TABLE [sql] ( [EmployeeID] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL , .... ) The other table is through a odbc pervasive linked server: Other table employid varchar(15)...more >>

Hi, I want to pass array parameter to stored proc.But I can't find the array data type in Sql server 2000 online doc.Please tell me whether the data type exists.If it doesn't exist,how should I implement it. Thanks for advice Jack ...more >>

All, I am trying to consolidate several stored procs into one. Basically, they all perform the same tasks; just 4 possible values may change. I want to dynamically create query strings and then execute them. Here is an example: SET @SQLString = 'SELECT o.SurName, o.GivenName, ' + ...more >>

Hello, I am trying to create a table for month end information that is created dynamically as part of a daily process, problem is I cannot work out how to specify a variable for the table name. I am trying: SET @TableName = 'dbo.' + CAST(Month(Getdate()) as varchar) + CAST(Year(Getdate()...more >>

Hey I would like a Script as can tell my all locks in <db>.<owner>.<table> I have made a simple script telling me all locks on a DB, can I do this on objects??? Please help me! Med venlig hilsen Klaus Ladegaard Jensen TECHOTEL ...more >>

Can a user defined function developed in T-SQL (Yukon) access (read and modify) temporary tables? If so, will this be implemented in the next BETA release? BETA 1 doesn't implement this functionality. Craig ...more >>

I'm trying to have trailing spaces removed from columns. I've done the following: ALTER DATABASE NAMEOFDB SET ANSI_PADDING OFF I verified that the setting was off using DATABASEPROPERTYEX I then created a new table. (I know that the change will only impact new columns). When I add sp...more >>

Hi, I have one "master" table and one "child" table. Each record of the master table is connected to mutiple records of the child table by field "masterid". Question is - is the query with user-defined function faster than without it? 1. select m.*, (select count(*) from child c1 where ...more >>

Language: Visual Basic 6 SP5 Data Access: MDAC 2.8 OS: Windows 2000 Professional SP4 Database: SQL Server 2000 Standard Edition SP3a (which is running on the same computer as the application) The attached code runs every hour based on a timer. It runs fine for several days (around 2 weeks), ...more >>

Hi All, does anybody know why this sql statement gives me this output - 1461.5119999999999 Select Cast('1461.512' as Float) From TableName The result I need is 1461.512 of datatype float. How do I fix it? Thanks much...more >>

Hi, I have a SQL Server 2000 db on which I would like to perform querys and have pretty reports printed. Can I use MS Access (for the pretty reports) to connect to the SQL Server 2000 db? If yes, can you please tell me how to set up the connection? Thanks, Rogue Petunia ...more >>

Hi all, I developed a stored-procedure that returns a resultset of 8 columns. Is there a way to parameterize the sorting of the data? In example, when I want to sort on the 5th column, I pass 5 to this procedure and data should be sorted on the 5th column. Another solution would be nice too...more >>

Hi, from a newbie - working hard to ramp up on SQL ... I'm trying to create a query which ... looks as the value of a column and returns a boolean value based on its content. In Access SQL I have: SELECT Transaction, ([Module]=1 Or [Module]=5) AS Jump FROM tT; So the Jump column in t...more >>

Hi, I have a table with 4 cols already have data in it. The last cols is datetime. I want to import a CSV files with 3 fields into this table but also put in the 4th field with datetime. It doesn't matter if its DTS or bulk insert or bcp. Can someone help me? Thanks ...more >>

Given the following view definition, running=20 select * from vwLostFuelRebatesDECS where memberid =3D '05060129'=20 takes just over a minute to return the 41 rows.=20 CREATE view vwLostFuelRebatesDECS as --Used for Lotus Notes DECS select ISNULL(VG.Transactiondate, MS.Transactiondate) ...more >>

Hi all Please help with the following I have a file witl a id/sequence number i would like to resequence these numbers this is as close as i have gotten set rowcount 1 update temp_Reports set identifier_id = (select max(identifier_id)+1 from temp_Reports) where identifier_id = (sele...more >>

Hi, i'm working on an asp project that will work similar to enterprise manager for remotely managing some aspects of a sql database. What i'm stuck on is how (in vbscript or with SQL commands) can i find out what the default value (if any) is for a given column of a given table? I know there a...more >>

Hello, Someone knows why i am getting this error when I try to apply DTS...external File from my Enterprise manager in SQL Server 7 "Failed to transfer 1 Table(s) Double Click on the error row to get a detailed description of the error:" So, did that: "Error at source for row number...more >>

Hi! I've just recently migrated a VB system from Microsoft Access to SQL Server 2000, and a few pieces of code loop through a recordset like the following example: do while not rsRecordset.eof if ...... then rsRecordset.delete else .... (add a new record, or edit ...more >>

I'll admit.. I'm a SP n00b, so be gentle. I'm using DTS to import a text file into a table on a somewhat regular schedule (hourly). After the file is imported, I need to do a couple of different things to it. The piece I'm stuck on is, how do I Loop through TableA and insert records into T...more >>

Say you a table that looks like this... PK FK DTE 1 1 1/20/2003 2 2 1/20/2001 3 1 1/21/2003 4 2 1/20/2003* 5 1 1/20/2003 6 3 1/20/2003 The Goal is to get the row with max or last date based on the fk Select * from testTable as t1 where t1.fk=2 and pk = (select PK from test...more >>

Hi all I want to get a list of current Active Directory groups in a particular OU in a particular domain ("ou=testing groups paul,ou=47 Clarence", "dc=cips,dc=gc,dc=ca"). More to the point, I want to ensure that the one I'm trying to grant a login to has been replicated when I do the sp_grantlogin...more >>

Good Morning All, I have a query from a user that I have been asked to take a look at. I have narrowed down the issue to one of the parameters in the query. When the query covers 2002 thru 2003 one execution plan is issued when the query covers only a portion of 2003 a second execution plan...more >>

This is a follow up post to the "Are Table Scans Always Bad?" post I posted last week. I am in the process of re-indexing some tables, and I have been cleaning things up so execution plans show no table scans. Performance seems to be helped not hurt, so I was just curious, is it safe to say: A...more >>

Hello everyone, hope someone can help me with this. I have a SQL stored procedure that inserts a record into a table, creates a cursor to fetch the last record that was added to get the unique key that was created and then writes that and other info to a separate table. This procedure was w...more >>

Have a field that I need to Pad the Left with 0's. 1234 -> 0001234 123456 -> 0123456 Please help. Lynn....more >>

I have a varchar column. How can i validate the values in it are of smallint without causing arithmetic overflow error. thanks...more >>

I have two tables: Company & Users_Allowed, which holds user ID's and a company ID to determine if that particular user can administer that particular company. What i need is a record set that alway returns the entire list of companies from the company table, but have an additional column in i...more >>

Hi, I have a stored procedure which works perfectly when in the WHERE statement I reference 'UPRN' or 'POSTCODE' but when I try and reference 'ADD1' it tells me that it is an invalid column name. Any ideas on whi this is happening?! Wuld appreciate the help guys & gals! Here is my code: ...more >>

Is there any tool that comes with SQL Server that you can Debug (step through) a SP. I know it is different from typical code in the way the execution path is set up , but there must be a way. I know you can do it with .NET but we use VB 6 here at the office, and our MIS director resists...more >>

Hi I have found this in a datadictionary. Does anyone know what it means? DataType: decimal(15,4) I have tried to give my field the "Decimal" but it does not let me type 15. Please help. ...more >>

I have a parent and a Child table. I want to select the first child record for each Parent record How do I do this? TIA Craig ...more >>

Hello, I need to get a list of roles the current user(windows authentication) belongs to in a stored procedure. I can get close using a combination of sp_helpuser and suser_sname...somtheing like this: CREATE PROCEDURE GetRolesForUser AS DECLARE @UserName varchar(50) SET @...more >>

I need to used a stored procedure in a select statement. Ie. using the results of the stored procedure like a table. I would also like to create a view based on the results of a stored procedure....more >>

I want to perform multifactor regression analysis on data contained in SQL. I then want to store the results of the regressions (e.g. the coefficients and t-stats) in a SQL table. Any tips on how to approach this and/or leads on 3rd party products are appreciated. Thanks....more >>

I am migrating SQL from VB to T-SQL in a SP for SQL Server. Everything is fine, but I can't make, or use an Escape Character ('\', '%' for example.) I am building strings in the SP and NEED a Escape. I follow the documentation and it does not work. Any Thoughts?? P.S. Thank you Linda...more >>

Hi all, I'm missing something, probably stupid, on connecting to a SQL Server database from an aspx file. I haven't really done much w/ SQL Server and suspect that it's a problem on that side. I've installed the SQL Server developer edition locally and do have a pubs database. The connec...more >>

I would like to output the results from the statement EXEC ("sp_depends " + @TABLE_NAME) to a table for future analysis. The cursor is listed below. Please help me with this problem. Thank You, Dean DECLARE @TABLE_NAME sysname DECLARE @SAVE_STRING_TABLE VARCHAR(255) DECLARE ...more >>

I have a set of answer in a table from a survey that I want to parse out. Below is an example of some of the questions in a survey. In comparison to other analysis tools, please rate our tools on the following? The Best Better Than Most Average Need Improvements ...more >>

IS the beginning of a new day in Date Time format in SQL server 2000: A) 12:00:00 AM OR B) 00:00:00 AM ? C) Makes no Difference I have heard that A is actually 12 Noon a nanosecond before the PM cycle since PM is actually 12:00:01 Thanks for the Reply...more >>

I am trying to execute a dynamic sql query and get the following problems: when i run the code as a sp on another server it works ok.However when running it on the query analyser i get the following error message Server: Msg 207, Level 16, State 3, Line 6 Invalid column name 'exec master..xp_...more >>

I have a set of answer from survey which I need to have it parse out. It is currently in a table like this: TheBestBetterThanMostAverageNeedsImprovementUnsatisfied How do I get the answer to be like this? The Best Better Than Most Average Needs Improvement Unsatisfied Below is the ...more >>

I am trying to run this code on the query analyser and i get the error Server: Msg 207, Level 16, State 3, Line 8 Invalid column name 'exec master..xp_cmdshell 'copy '. Server: Msg 207, Level 16, State 1, Line 17 Invalid column name ''' However the same runs fine on another server.... Any ...more >>

We created the sp_getkey store procedure that outputs maximum value to the next record. We have blocking problems with this procedure listed below. Please help me resolve this issue. Thank You, Carl Create Procedure sp_getkey @vc_TbleNme varchar(128), @i_Ky int Output, @i...more >>

I have changed database from Sybase to Microsoft SQL. And now I got a problem: when Dataset is set to the GridControl and I am trying to update datas - the message is coming: DataSet has no unique row identifiers... Shall I set for every primary key also rowID? Any idea?...more >>

How's about ye, I am interested in getting an explanation for the following (simple) sql query: select * from ey_ftrn where ftrn_prtd <> 'T' The field ftrn_prtd is a normal varchar field. In our application, the value is only ever set to T, so the only values that are in the database for...more >>

How could I change only one data field in an INSTEAD OF INSERT trigger? In my application the table structure can be modified dinamically (adding/modifying/deleting fields). Therefore, I can not hardcode all the fields in my INSTEAD OF INSERT trigger. At the same time, I have to change one datafi...more >>

Hi, does someone know how to get the user's private data from the structure SRV_PROC passed to a extended stored procedure in a DLL? there is a definition on the SRV_PROC structure in the (srvstruc.h) but no documentation on how to use it: void * userdata; // pointer to user's p...more >>

I am writing a set of reports on a Workflow system. I have a main table which is basically an Audit Trail of every move an entity makes. What I need to do, is for every record that is generated, I want to join it to the previous for that entity in order to obtain certain fields which have a differen...more >>

Hi SQL Gurus, Using Sql2000, I have a table to maintain stock availibility, the requirement is qty_in_orders must be <= qty_on_hand, so I use CHECK constraint like below : CREATE TABLE StockAvailibility (prod_code CHAR(5) NOT NULL PRIMARY KEY, qty_on_hand INTEGER NOT NULL, qty_in_orders I...more >>

Hi! I want to return a generated text with a function, but with nvarchar it is only possible to a size of 4000 ! I cant use text or ntext in a function. How to return a text greater than 4000 characters ? Thanks, Daniel Rakojevic ...more >>

Hi, I have created the following function in MS-Access as follows. Can anyone tell mwe how this could be translated into an SQL stored procedure. ********************************************************** Function setKPIPassCriteria(strRefNo As String) As Boolean Dim rsDefn, rsMths ...more >>

i have created a trigger as follows CREATE TRIGGER TRIG_CLEARRECORDS ON Table1 FOR DELETE AS DELETE Table2 WHERE LedgerKey in (SELECT LedgerKey FROM Table1 WHERE RecievedDate = '02/12/2003') Print ' Triggered ' If i then do a Delete from Table1 where R...more >>

Hi I have few questions about the Client Side Cursor: What I know so far about Client Side Cursor is that the table content (recordset) is created on the client’s computer and not on the server. Because of that, the network traffic is increased... but it puts less pressure on the server. Quest...more >>