Recently moved from another DB to MS SQL and slowly converting code and routines, I need to execute an external program based on a new entry into the database. I believe the most efficinet way is to create a trigger for insert then execute the program, I have what I belive is the trigger code bu...more >>

hi, In dos,unix how i created the batch file to call and execute my sotred procedure 'proc' can u gv answers???...more >>

Hi, I need to get today´s date + 1 (3.12.2004 -> 3.14.2004) by some functon in format m.d.yyyy I want to use it in query: SELECT * FROM table WHERE date <= m.d.yyyy where m.d.yyyy is today´s date + 1 and date is smalldatetime, for example 3.12.2004 19:54:00 Thank you Tomas ...more >>

Hi, How can I use query analyzer to debug my codes and stored procedures. I need something like debug tools of VB(watching line by line execution). Are such tools available for SQL server? Thanks, Amin ...more >>

Hi, I have created a view but I cannot create index on it. My view is like this: Create view v2 with schemabinding as select distinct count_big(orderid) as MyCount from dbo.orders group by orders.customerid and my index command is: create unique clustered index myidx2 on v2(MyCount) ...more >>

Hi, Where the result of indexed views are stored? Thanks, Amin ...more >>

I have the statement below but am having a hard time with the table variable. With the code below I get an error 'Server: Msg 137, Level 15, State 2, Line 19 Must declare the variable '@deletesconsumerid'.' A permanent table works, but I would prefer to use the table variable.. any help w...more >>

Ok, Can someone give me an example answer to how you are suppost to update 2 tables in a transaction without causeing blocking proc Begin Transactio Update table update table en proc begin transactio update table update table en This causes blocking, I need to do these things wit...more >>

hi, i want to know the standard sp writing if i have several select sql and update sql statement. how can write it with transaction and error handling? the following is mine - i have only one begin tran - i have the error handling module - i put return value 0 as successful and 1 as fail...more >>

Hi, We are having trouble to handle the string delimiter in the source csv file during the bulk insert/bcp. The source file looks like this: "CityABC, ST 99999", 1, 2, 3, "some text here", 4,5 the results should be end like: (comma was used for field delimiter) col1 = CityABC, ST 9...more >>

Using the code below, I tried to load some Information into the temp table so it can then be moved on after corrections CREATE TABLE #FRP077 [RLOB] [varchar](4) [RLIB] [varchar](4) [RLEDAT] [varchar](9) [RLRC] [varchar] (4) [RLGIH] [varchar](4) [RLEXH] [varchar](4) [RLCHG] [varchar]...more >>

I found something kinda quirky in enterprise manager today. Just wondering what others thought of it. Here's how to reproduce it -go to the design view of any table -click in any row (blank rows work best) -when the blinking cursor appears, click and hold, you'll see the cursor flicker very...more >>

I have two tables A and B, both have [id] field. I want to join them in a query on A.[id]=B.[id]. What is the way to get the least query execution time: inner join, right or left join? I mean in case A and B both have records for any of [id]s. Thanx for your help. ...more >>

Hi everyone, Is there any statement that can be used to update more than one row of data but with different values. For example you have Table 1 a 10 b 20 c 30 needs to be updated to a 56 b 89 c 2 Can you apply all of the updates at once without going throug...more >>

I have logged on to my SQL Server with sa and I am trying to execute the sp_change_users_login. I recieve the error message: Server: Msg 2812, Level 16, State 62, Line 1 Could not find stored procedure 'sp_change_user_login'. Why can't sa see this stored procedure? Thanks ...more >>

Even tho i know there is records in the fields I'm specified (i performed a number of response.write tests before the sql statement), I get an "Either BOF of EOF is true..." error when I do a response.write after the following sql statement ( all my recordset references are correct) as I am tryi...more >>

Hi! Think that you get 100 rows in result of a query. But i=20 don=B4t want to see all of them. I want to see the rows=20 between 20 and 40. Is it possible to do a query who get=20 the rows between 20 and 40??? Thank you for helping me Kind regards Mehran ...more >>

Hello I just installed a win2k server with sql2k and when i run datepart(wk,getdate()) it gives the american day of week, not european were monday =1 how do i fix that? can i set something in control panel? everywhere i look in regional settings I see it all set to swedish TIA /Lasse ...more >>

We have a bunch of user Stored Procs (about 50) in the database. We recently purchased Visual Sourec Safe. Is there an easy way to move the copies of all the Stored Procs into Source Safe ? Thanks, S.Kumar ...more >>

The previous post wasn't posted in the correct format. Hi I have a table with name period month value ABC 2004 12 35 ABC 2005 12 30 ABC 2006 12 32 I am trying to write a select statement that w...more >>

Can you not pass the result of a scalar-type user defined function as a parameter of another user defined function??? e.x. select * from dbo.mytablereturningfunction(dbo.myscalarreturningfunction()) I get: Incorrect syntax near '.' ...more >>

Question: My master database contains a copy of all the tables that are in another one of my databases. I have 7 databases with the installation of SQL server. Of the other 6 DB's the master doesn't contain any tables from these DB's. Is the master suppose to contain other DB's tabl...more >>

Okay, I'm a newbie, so any help would be appreciated. I declare a cursor, then enter a loop. Inside the loop, I open and fetch, and everything works wonderfully until I close the cursor. When I re-open the cursor (I do this because I want it to re-query per the declaration), it opens at the ...more >>

How can I tell what default transaction mode sql 2000 is in Does it differ if you use stored proc or inline sql?...more >>

For ad rotation, we need to randomly select a single record from a table that has an IDENTITY primary key (some records may have been deleted, so no guarantee they are in numerical order) Any ideas? Jeff ...more >>

Hello: I'm having some difficulty changing the collation name associated with a SQL Server 2000 database. When sp_helpsort is executed, the following server information is returned: "Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data...more >>

Hi, I want to store the passwords in encrypted format in a SQL Server Database Table. These passwords should also be decrypted when needed ( when the user tries for authentication from web). Where do i start? basically this is a web based mail service where the user can register and acc...more >>

SQL 2000 I need to reinstall my sql server all over again. But I have a lot of jobs and DTS on server. How can I backup jobs and DTS and restore them after I redone my server? Thanks in advance, Patrick ...more >>

I need to count all those members who were continuously enrolled for all the 12 months starting from todays date going back 12 months. Thus, if someone had a gap for a month in between and was enrolled only for 11 months, I should not count him/her. Thus the maximum count of the number o...more >>

In Visual Studio 2003, I add a Server Connection. ...more >>

I'm trying to clean up the result of a data import gone wrong (I didn't = do it, honest!) and I needed to run a query to determine a list of = duplicate records in a particular table. The only way to determine a = duplicate was by using a combination of 3 columns (stamp1, item_code, = and suffi...more >>

I was wondering if anyone can help me come up with a creative trigger/stored procedure that enables us to work around the shortcomings of a 3rd party tool we are using. I definately think this is doable but since I am a newbie I'm not sure how to even begin to accomplish this. A summary in pse...more >>

Hi, I want to do paging data. I found a very good sample from http://www.aspfaq.com/show.asp?id=2120 But I modify little just add a 'if' statement and a variable. When I try to save it, Error message show: ADO error: There is already an object named '#CDList' in the database I use sql serv...more >>

I have a select statement that works on all of our customer sites except one customer on one table. The statement is select * from dba.case_incident where case_incident_key not in (select case_incident_key from dba.report) I have copied their database and it works fine on my server....more >>

Hello all, A newbie, so please be gentle. I have a table with the following structure col1 int col2 int col3 int In the table, please imagine the data looks like... 100 20 30 100 21 72 200 23 34 200 34 45 200 44 67 I would like to have a sequential number indentifying col1...more >>

Hello, I am trying to run a VBScript that will add a local user to a computer outside a domain using xp_CmdShell in MS SQL 7. When I run the script without xp_CmdShell (Double clicking or command line 'CScript c:\AddLocalUser.vbs') it works great. If I run the script against a computer in...more >>

Is it possible, in Sql Server, to call a SQL script stored in a SQL file from another SQL script ? Example - content of file C:\dbtest\OverallScript.sql: <Call> C:\dbtest\File1.sql <Call>C:\dbtest\File2.sql <Call>C:\dbtest\File3.sql .... "<Call>" is the command I am looking for. Is it p...more >>

Hi. I have a few (4,000+) .TXT files that I need to import into a SQL table. 123.TXT 456.TXT 789.TXT I have a routine that gets the list of .TXT files and puts into a table. Select * Into #TempProFiles From OpenRowset('Microsoft.Jet.OLEDB.4.0', 'Text;HDR=NO;Database=\\Ms00...more >>

I know that SQL 2000 does not provide this Row Level Triggers, but is there a workaround During a record insert or update, I need to insure that several columns do not contain NULL. We have a trigger in Oracle that will change the NULL to space (and this works fine) We are converting to SQL Serv...more >>

Right now i am gettin the values from an inserted table like this declare @colname int set @colname = (select colname from inserted) for each column... is there a better way to do this? If you have tables with 60+ columns that gets vert tedious.... thanks! ...more >>

The message that I got was: [Microsoft][ODBC SQL Server Driver][SQL Server Login] is aliased or mapped to a user in one or more databases. Drop the user or alias before dropping the login. I am trying to create all new accounts for a new database. Is this referring to the SA accou...more >>

I have two user defined functions. One is to format datetime as mm/dd/yy, the other is to return a table of entries that match today's date using getdate. When I try to compile the 2nd function in QA, I get an error pointing to the use of getdate. --- sample code --- create function dbo.fnFin...more >>

I need some script to convert sql result to the horizontal table. So each row from the SQL result should be the column in the new table. In fact,generally that should be convertion from "vertical" table structure to the "horizontal" table structure. I remember I saw that kind of SQL script ...more >>

Hi, I am wrintitg the result of stored procedure to a text file using osql. This osql is excecuted through xp_cmdshell in another procedure.When the proc executed by osql returns any error it is simply written to the text file.I need to capture this error in the called procedure to include...more >>

I'm having troubke figuring out how to perform a query for a word that contains an apostrophe. I've been trying to use the "ESCAPE" keyword but no success so far. Here's an example word to search for and the SQL statement I'm trying to make work. Gene'ric SELECT * FROM TableName WHERE Colum...more >>

Thinking about creating a table with a PK of uniqueidentifier default NEWID(). Can I be assured that the value will always be unique? (Within reason, I'm sure statistically there must be some point that I run out of values Thinking space and efficiency - If I choose to go with a GUID as my PK is ...more >>

Hi I create a table called f_sales_actual and f_sales_history I created a check constraint in each table saying what are the years that can be insert in each table, f_sales_actual - CHECK CONSTRAINT ( year(dat_refer) >= year(getdate()-1) - F_sales_history ( year(dat_refer) < year(getdate()-2), after...more >>

im running a query Select b.BTYear_Licence,b.BTYear_DestSortCode, b.BTYear_DestAccountNumber, b.BTYear_DestBankRef MAX(a.BHYear_RecievedDate from dbo.BacsTrnYear as Left JOIN dbo.BacsHdrYearly as ON b.BTYear_LedgerKey = a.BHYear_LedgerKe where b.BTYear_DestBankRef = '317460546CL2...more >>

Good day I'm trying to find a good test data generator tool. But most of them (turbodata, dbgen, ems) give me an error about identy_insert being off when i want to insert data to the table with the identity column (Cannot insert explicit value for identity column in table 'X_ROLE' when IDENTITY_INS...more >>

Hi I have just started to use SQL Server 2000 procedures with an access project, I have created a procedure that returns a number of rows when a invoice number is input, the procedure runs quickly if the invoice number is hard coded, but slowly if a parameter is used for the invoice number. See exa...more >>

Given identical environments, which query will perform faster (or will they be the same) 1. (Query run on a table with one million records of which one record's isActive=1, there is a clustered index on person_ID SELECT person_ID FROM PERSON WHERE isActive= 2. (Query run on a table with one rec...more >>

I want to do a drill down that groups the results by the month IE jan, feb ............ This is what i have allready. SELECT DateRecive,CruShip FROM dbo.TBLCase Group by DateRecive,CruShip So i assume that i need to do something with the DateRecive part of the GROUP BY line or is ...more >>

We have SQL Server7 (therefore don't have the luxury of SQL2000 with the indexed views) - does anyone know how to order records in a view since one cannot use the ORDER BY in a view? thks!...more >>