Hi, I like to create a store procedure that takes a comma delimited varchar as parameter, and split them into an array and insert elements into a table. For instance, if the parameter input is 1,2,3,4 , there will be 4 records inserted into a table with respective value of 1,2, 3 and 4. How ...more >>

Hello. I have a sql server stored procedure (with input parms) that outputs rows via a select stmt. I am using vb 6 and need to place those rows in a local access table. Obviously I can get a recordset back, loop thru the individual rows and insert them one by one into the local access tabl...more >>

Here's the background: 1 One database was created and published for use - only data operation but without modify any object structure; 2 New functions are added and the database was changed with new created items and updated items; 3 Now we want update the published one with the updated ...more >>

Hi all The problem is that when I use a where clause with the format: where MyValue in (select Column from Table) and the subquery is erroneous (Column is not a column in Table), Query Analyzer does not report an error and evaluates the condition as true. for example, if RouteID IS ...more >>

Hi Freinds, What level of security an accounts needs to be able to view the jobs, and not be able to modify it. Thanks, Patrick ...more >>

Hello, I want to query a table called Referrals where the ReferallType is either a business or personal product type of Financial Services on a date range with no blank values in the R_BID field. The query is now this: {Referrals.R_ProdName} = "Financial Services (FNIC) Business" and {Refer...more >>

Hi all, I have a bunch of 20-30 XML-files, with the same structure, that downloads every day to a FTP-area. I want to load all these files, one at the time, to a table on SQL-server. I have a DTS script that looks like (part of it) ================================ Set objXMLDOM = CreateObj...more >>

Hi I have TABLE1 ID LINENUM THELINE 1 1 HELLO WORLD 1 2 HOW ARE YOU DOING 1 3 TODAY 2 1 ...more >>

Hi All i'm aware that emails can be sent from SQL using xp_SendMail, has anyone got any experience on the reliability of using this concept?? cheers ...more >>

I want to be able to use the bulk insert command to insert a flat file into a table with the following structure. col 1 char (9) col 2 char(12) col 3 char(1) col 4 char(12) col 5 char(10) col 6 char(8) I did see one article that created a formatfile. Is this the only way to do it? I a...more >>

I have a table of 3-4 million (currently) rows which will be constantly bulk loaded into and is also used for select queries. Note, the rows in this table are never updated or deleted, only selected from. Both insertion and retrieval times have proved problematic. A clustered index has iss...more >>

SQL 2000: I know that Enterprise Manager's "Display Dependencies" menu item doesn't give reliable information, such as after you rename a view. Does the sysdepends table always correctly reflect dependencies, such as which views are used by another view, or which fields of a view or a table ...more >>

Hi I have column CRName of varchar type. CRName ------ Mktg20040311 Fin20040310 Acct20040309 Cr20040308 I want to get get result set as FName ------ 20040311 20040310 20040309 20040308 I tried using different things but couldn't make it work. Plz. help T...more >>

I have a function I call after an insert to get the identity value. For the first record inserted into a table, this does not work. For subsequent records it works fine. Any ideas? Cheers Dave ...more >>

I am trying to use a UDF that returns a table in a where statement. But I would like to pass field names from the select as attributes to the UDF rather than a literal value. Is this possible? Regards ...more >>

I have a table that has 3 columns eventid datetime data I am to create an insert trigger on the table and everytime a records is inserted with eventid=1234, I am to print a crystal report to a printer. Can someone help me with the trigger code to instantiate the crystal report and print...more >>

Hi all. I have the following problem. In an MS SQL Server 2000 database I have as many as 75 tables which have references between each other. I want to delete some rows in one of the low-level tables. It mean that I have to delete rows in all the tables in the schema which means I have to analy...more >>

Hi All I have a really long CSV list which I am passing to a proc, unfortunately the proc parameter type is varchar and the CSV is way > 8000 bytes If I change the proc parameter to TEXT my function goes crazy since I am using the following routine DECLARE @FileNm VARCHAR(128), @Pos INT SE...more >>

I have 2 tables named Orders & OrderDetails. Orders is a master table and OrderDetails is a details table containing details for each Order record. The OrderDetails table has 2 columns: ShipElseWhere & ShipElseWhereAddress. I need to implement a way to allow users to select specific records...more >>

My client got Two branches in two different cities. Now, the database server will be placed in City A , the user will input the data in City B. they use the lease or VPN to connect it. I got a problem on 'version control' design, We need to keep each version of the docuement and saved them into ...more >>

Hello All, Can anyone tell me whats the benefit of having instances of SQL Server? - Irfan Zaidi ...more >>

Hi, Can anyone tell me "how to kill the process" as though I am not a member of sysadmin or anything else. I am trying to pull the text file into our staging tables. I keep getting the "tempdb is full. backup the transaction log". any ideas to add... Thank you....more >>

How do I debug a stored procedure? I'm using VS.NET 2003 Prof. + MSDE. I have tried simply right-clicking the SP name in Server Explorer and selecting 'step into', but I get the error: "Cannot debug stored procedures because the SQL Server database is not setup correctly or user does not have pe...more >>

Hello All, Can anyone tell me whats the benefit of having different instances of SQL server? - Irfan Zaidi ...more >>

I'm still a bit new to MSSQL so bear with me...... (and i am looking up the sql docs on line) Here is my question: I'm updating large groups of records that require me to use processed data from previous records to be part of the updated current record. The update is compilcated so it can no...more >>

So now I am cooking on my delete triggers; have them setup on about a dozen key tables and they are working like a champ! It would be really cool to know who has deleted the record and log that in the new Delxxxx table that I created (from the trigger). Or, is there another approach that is...more >>

I am getting an error when I try to change my SA password. error 21776. Something about dbo not being a valid username. I'm not sure whay it is referring to. I have tried doing sp_changedbowner [darren] on all databases, but it doesn't help ...more >>

How do I, from within the same stored procedure, use the result of a sql statement after executing it using the EXEC() command?...more >>

Hi folks -- Probably simple, but I just can't seem to get there. My achieved output (so far): Off# DbBals CrBals 1 210.0000 NULL 2 60.0000 NULL 3 100.0000 NULL My desired output: Off# DbBals CrBals 1 330.0000 ...more >>

I am working on a customer scoring application within a customer data warehouse. My database has ~350,000 customer records and ~25 million transaction records, with each customer having anywhere from 1 to 3000 transactions each. Performance is a big issue. My application needs to select a...more >>

Hi All, I know I can run a BCP comand line from within a SQL stored Proc. I have a twist to this now. I have a table that I run an SP on every month. In my Proc I have a few alter Statements that add new columns for the particular month. I need to now export this table after my proc fini...more >>

When I run the following SQL create table dest (col1 int ,col2 int ,col3 int ) go create table src (col1 int ,col2 int ,col3 int ) go print ('insert') insert into src values(1,2,3) go begin tran update src set col1 = col2 insert into dest select col1, col2, col3 from s...more >>

I'm evaluating a set of stored procedures that are already in use for the purpose of standardizing them. I've reviewed BOL and various articles and am still fuzzy on a couple of things. My questions regard the use of @@error and return values. I've seen it said that best practice is to alway...more >>

Hi all , Could anyone answer my questions please 1. how to drop a temp table in the stored procedure 2. I am getting the following error when I run the query. What is it mean?"Warning: The join order has been enforced because a local join hint is used." Thanks...more >>

Hi Been working with indexes lately trying to improve DB (and get more knowledge on indexing). When I create new idexes I keep on running into old indexes that's not used. So I decide to write dynamic script that drop all indexes.(I own the DB so not to worry) So I create this sele...more >>

Does anyone know how to get a datetime field into a varchar format of 'mmddyy'? None of the pre-defined formats under the CONVERT syntax seems to address this format. select <DATETIME_FIELD> from tblCalender . . . Thanks...more >>

Don't know if it is possible in SQL Server to have a join on a update ie my code UPDATE TEMPtblEntitlement INNER JOIN tblEmployee ON TEMPtblEntitlement.strLogonName = tblEmployee.strLogonName SET TEMPtblEntitlement.decYearAllowance = 28 WHERE (((TEMPtblEntitlement.decYearAllowance)<>28) AN...more >>

I asked a similar question yesterday but still not sure how to move on .. hence posting it agin: In a cursor, as I loop through, I create a dynamic SQL each time. My dynamic SQL looks something like this: SELECT @rOUT =[114x] FROM tblName WHERE eid=100 i.e, the above is assigned to a vari...more >>

Hello, i tried to write a function for my ms sql 2000 which should can: 1. gets a parameter country like "Germany" 2. searchs in a table country has col. NAME and col, MEMBER_OF_EU. 3. returns weather the country is EU member. i tried this code CREATE FUNCTION dbo.countryCode (@country nv...more >>

I would like to get hold of the current user name within a trigger to create a basic audit of who last updated a table. When I use the USER_NAME() function I always get 'dbo' as the user. Can anyone tell me how to get the real username which I can display using sp_who? ...more >>

I'm trying to add the following code into DTS INSERT INTO tblExtraDay ( strLogonName, intYear, intExtraDayReasonID, decExtraDays ) SELECT TEMPtblEntitlement.strLogonName, TEMPtblEntitlement.intYear, 7, [decYearAllowance]-28 FROM TEMPtblEntitlement INNER JOIN tblEmployee ON TEMPtblEntitlem...more >>

Hey there...I have a char(7) field I need to pad with leading 0's when bringing a number through here is my statemnt: Update Interface_File set ClientNumber= Replicate('0',7-Datalength(ClientNumber))+ClientNumber This sets all the values in the column to NULL. What am I missing snytaxic...more >>

Is there a way to use table fields from one database in a different database. I have a development database that I want to use for stored procedures and temp files that I don't want to clutter up the main database with. -- Thanks David Davis...more >>

Hi, Is is necessary to back up the log files of all three systems databases? Master, MSDB, MODEL? Thanks Ed...more >>

I have a table containng two datetime fields and i wand to select all records that are between 2 dates The dates are parmed in from a vb application on DD/MM/YYYY format ...more >>

Here is some code to create some test tables and data. CREATE TABLE [tblEntitlement] ( [strLogonName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [intYear] [int] NOT NULL , [decYearAllowance] [numeric](3, 1) NOT NULL CONSTRAINT [DF_tblEntitlement_decYearAllowance] DEFAU...more >>

Hi I have a small table with about 1 mil records, about 50 columns (no key) I've created a clustered index on PersonID and createddate column. I've tested this and I get the best performance on my queries with this index. Now... my question. Using an index that create datapages accord...more >>

I have a stored procedure wich takes the document type as input parametar and returns newly generated document number as OUTPUT parametar. So, if I wanted new invoice number, i'd say: exec ep_CreateNewNum 'INV', @docId OUTPUT and @docID would have 'INV1ZG1200400141', for instance. Now, si...more >>

I have a stored procedure wich takes the document type as input parametar and returns newly generated document number as OUTPUT parametar. So, if I wanted new invoice number, i'd say: exec ep_CreateNewNum 'INV', @docId OUTPUT and @docID would have 'INV1ZG1200400141', for instance. Now, ...more >>

Hi, A clustered index cannot be converted to a nonclustered index using the DROP_EXISTING clause, what can I do instead? My PK must become nonclustered and another field become clustered. when I try to drop its index using TSQL, it fails because PK is being referenced by a FK. I could do that ...more >>

hello Experts, this question might be a dump one but i will ask it anyway. I run a script like the following: If 'a' <> 'b' begin print 'not equal' return end if object_id('MyStoredProcedure') Is not null drop procedure MyStoredProcedure GO CREATE Procedure MyStoredPro...more >>