Dear T-SQL gurus, I can build a working running total for a table with one or more row columns (thanks a great Advanced T-SQL book I have), however, the running total will not work if the record structure is not fixed. If I have a record yesterday for Column A=1 and Column B =2 with value 10 in ...more >>

Hi all, I have a stored procedure which returns a vast number of record and i have to write the output into a csv file. I'm using BCP utility to do this and BCP utility is executed by xp_cmdshell from inside another stored procedure. Everything works fine from developement server but when t...more >>

Hi there. I need to populate a table with, hold on, 90.000.000 entries. They have to unique, starting with 10.000.000 and ending with 99.999.999. I reckon that i have to use a FOR-NEXT loop and maybe have to use some VBScript, but i rather that it was a SQL statement. Can anyone help me with th...more >>

Newbie question about T-SQL. Here is a my code. Basically I want to give user flexibility of inputting search criteria. If all parameters are empty, there will be no where stmt. I got syntax error on last where statement. Can anybody tell me is it going to work in stored- procedure. --...more >>

I must to implent a sp that checks data into a one to one relationed table and let me know if all data is available or not. To better understand (sorry for my poor english) imagine this. TABLE 1 ID DESCR QTA 1 aaa 1 2 bbb 1 TABLE 2 ID DE...more >>

Hi all, I have a path in a DTS-package that looks like: sFilename = "d:\path\*.PMS" & Right(Year(Now()), 2) sFilename = DTSGlobalVariables If I use the wildcard "*" I'll get: The filename.....is incorrect. If I use the full filename then it works! How can I get the wildcard to work...more >>

Hi everybody, First time I've posted in this group so go easy on me :-). Hopefully somebody can help. My website runs off an SQL database and I'm trying to write a query in Microsoft SQL Query Analyser. So, I've got a table called tbl_outgoing, and within it is a column called number. A typ...more >>

Hi ... I got a job using Web Publishing to generate report based on the data returned from store procedure. Lately, I got some funny problem which never happen before. ************* Error Message ************* SQL Web Assistant: Could not execute the SQL statement. [SQLSTATE 42000] (Erro...more >>

which is better to use? simplified example: I have a table T with fields: id (int key) fSelected (bit) not null I want to validate that fSelected=1 for at most one row. There are 2 ways to do this that I can think of. I want to know which is the best one. Method 1: create a view: sel...more >>

I have two tables, one containing a list of words that I want to match and another containing a full list of words and a link field into another table. I am trying to write a query that will select all the rows from table #Words where the LinkID contains all the words in #SearchWords, for examp...more >>

hi folks, I have a table like this.... Col1 Col2 Col3 Col4 =20 This NULL NULL NULL =20 NULL is NULL NULL =20 NULL NULL line NULL =20 NULL NULL NULL one ...more >>

I have a user table which is to store user's demographics. Every time a user signs up, one row will be created. When this table grows (say, 50M rows now), it is taking more and more time to generate reports from that table (if that is the only way). I am thinking a work-around for this but I a...more >>

Is there currently a way to cleanup the tempdb? Tried dbcc shrinkfile , but no luck. Can't restart the server ,because it's on production. What makes tempDB big ? and tempDB is in Simple mode , but not truncating it . Thanks ...more >>

DateTime data type according to SQL Server Books on line can store dates between 1753 and 9999 years. SmallDateTime even narrower. What Data Type should I use If I need to store dates before January 1st of 1753? Thank you Vlad ...more >>

Hi All, I have an Excel sheet which I recieve every week. Its not too much data and the present process is to manually load that data into a SQL Server 2000 EE table. But I would like to automate this process. I have tried using the DTS, but it doesn't work. What I wud basically need is tha...more >>

I have the following table: Name Austin, Frank Cusp, Jack Ross, Mike Smith, Dave Sears, Edward Thompson, Peter Is there a way to write a query that will return the following result set? LastName FirstName Austin Frank Cusp Jack Ross M...more >>

Is it possible to get source code for system UDFs? Specifically, I would like to get the code for fn_trace_gettable. Thanks. ...more >>

Hello! We have a Lotus Notes server. I have set up a linked server on the Sql server that connects to the 'table' in lotus notes. From there, I have created a stored procedure that pulls data from that table. All that is running great. But now, I want to have a view that joins the content...more >>

Hello, I was wondering how to create a function withing a trigger (Sql Server 2000). I would like to put the invalid email characters in a function so it is not replicated (see the code snippet below) An example would be great! Thanks in advanced! CREATE TRIGGER dbo.trg_d_EmailSend_I...more >>

Hi I have the following tables TranDetails (3rd Party) with 4 fields making up primary key Supplier char 7 GRN char 7 Journal decimal JournalEntry decimal detail1 detail1 detail3 InvMove Journal (PK) decimal JournalEntry (PK) decimal Stockcode char Supplier char Date date GR...more >>

Hi folks. Thanks in advance for you help. We're in the midst of a wholesale revision of our Access database to SQL so that orders can be directly entered from our website. We offer several products that are handled differently depending upon what sort of product it is. So I want to limit what ...more >>

Are there any functions in SQL Server to find the index of a char in a string i.e. declare @index int set @index = ????('This is my string', 's') Thanks Mike ...more >>

I have a script that I run that executes a series of dynamic 'select into' queries. Sometimes the script generates a "Server: Msg 8630, Level 16, State 77, Line 2 Internal Query Processor Error: The query processor encountered an unexpected error during execution." and sometimes it execute...more >>

when i type the following query in db1 i get the error mentioned below select * from atsymcatransmittals where chvTransmittalNo in (249161, 249162, 249163, 249164) order by guiuniqueid they produce the following error when run in SqlTes03.yrs: Server: Msg 245, Level 16, State 1, Line 1...more >>

Hi all .... let's say i have a table called Registered users which updates everyday and has only a TOTALUSERS column so if i want to check how many new users i have from today and yesterday i just find the difference between today and yesterday.....but how can i do this for a date range..... ...more >>

is this possible within a stored procedure? if ( 50- (if (@var1>4) select 0 else select 10 ) )>=0 select @var1 ...more >>

I have a script that I run that executes a series of dynamic 'select into' queries. Sometimes the script generates a "Server: Msg 8630, Level 16, State 77, Line 2 Internal Query Processor Error: The query processor encountered an unexpected error during execution." and sometimes it execute...more >>

Hello All & Greetings. Can someone pl tell me in a hurry how I can format a currency item in 2 decimal places. thanks in advance. RK ...more >>

Hi. I have a stored procedure that among other things returns a column with values that will be used to compose a temporary table. The number of records returned by the procedure can vary and I need to read the values of this specific column and use them as column names in my temporary table. ...more >>

I send and SQL command to generate a repport on customer=20 site. Sometime its work fine, sometime not. What could cause this error message? My SQL request was not using NOLOCK keyword. Thanks in advance for your help. Mario Boss=E9, inf Product analyst Exact Modus...more >>

I have 3 tables: table countryPrice: productID countryId price 1 Italy 90 1 England 110 Table country: countryId countryName 1 Italy 2 England 3 Germa...more >>

what is the correct syntax for adding additional single quotes around a string in sql? the problem is with AAA Corp, which is a variable. "$selected_genre=' + rtrim(@borrower2) + '"> this is the result needed "$selected_genre='AAA Corp'" tia, dk ...more >>

Hi! How can I get yesterday's date? or function that will get only the date yesterday in 'mm/dd/yyyy' format? joel ...more >>

Does anyone know how to shrink a database and or log files when the log file are like 10 gb. I am running the wizard and dbcc shrinkfile\dbcc shrinkdb but nothing seems to make the actual file size shrink???? Any clues. thanks in advance.. ...more >>

Hi guys, How can one provide a user and/or role access to truncate selective tables. For example, how can I give user 'appnet' access to truncate only 'customer_base' table? Thanks, Ramandeep ...more >>

Hi, I have a product table which defines all the attribute for a product, some attribute is not for all products so I have another table for it, ie, promote table, some attributes can have multiple values so I have another table too, like, schedules, pay method, so I have schules table ...more >>

Hi all, I have got two tables:- a) students ======== std_id Numeric std_name Varchar(50) std_grade Varchar(10) b) attendance ========== atd_id Numeric std_id Numeric atd_date DateTime Every day, records get inserted in attendance table only of those students who...more >>

Hi everybody, Is there any way to backup DTS and DB diagrams? Thanks for your help. LJ...more >>

If I ask query analyser to give me an estimated query plan, how does it work this out since my temp table will only be populated when I fire the query? I suppose this "estimated" plan is only worth so much... Now, what happens at run time, if I create a temp table and populate it with 1 milli...more >>

I'm an Access programmer making the migration to SQL Server, and I want to delete all the records from one table with matching records in another table, but am getting a syntax error: Incorrect syntax near the keyword 'INNER'. The second table starts out as a string of comma separated text vari...more >>

What risks are there in running the 1204 trace flag on a production server? Thanks Bob Castleman SuccessWare Software ...more >>

I'm having a excessive amount of lock timeout and I discover that every time when users execute store procedures, these procedures are compile before execution. Anyone has an idea about this behaviour ? thank you for your help.... ...more >>

this is a modification of a previous post. Thanks Jacco,Tom,Anith and Steve for your help. The solution to my last post was this.. /***************** update tablex set [sequence] = ( select count(*) from tablex t where t.stulink = tablex.stulink and t.enterdate >= tablex.e...more >>

I'm using SQL Server 7. I've got two versions of an trigger that just keeps an audit record of when a table was updated. One uses a cursor, and the other is set-based. I've read that you should try to keep cursors out of triggers, because they are slower. However, when I look at the execu...more >>

I am attempting to create a VB call to a stored procedure that uses xp_cmdshell and textcopy to store word documents [rtf] in a table. I have used an article by Alexander Chigrik to create a successfully proc using Query Analyzer but when I call it from VB it always fails. I've finally been ab...more >>

Hi, I exported data from a table that has column with datatype as 'nvarchar' with length 31 and has NOT NULL constraint with collation as "database default". All the data for this column has '' as value. I exported this table to a .csv file using command line utility "BCP". The query...more >>

Hi I have a client application that is currently connecting to MSAccess via ODBC. It uses the IIF and Format function in SQL-Statements. I want to migrate to SQL-Server. What equivalent functions do I have in T-SQL? thanks...more >>

Hi, Can I set the maximum I/O and memory usage when executing an transaction. The queries I'm executing are taking all the Power it needs leaving nothing to our business system. I have several SQL statements which I would like to run during working hours not during night. I have looked ...more >>

Hello, I've included a datediff function in my stored procedure, and it works correctly, but I want to format it to return only the Month and the Date. I know the Format Function does not work in an SP is there any function that I can use to quickly format the returned value? Thank you...more >>

Hi, folks! In my application I am compressing a large string using zlib (in VB6), and want to store this in a MSSQL2000 database. When storing this compressed string in a field with the datatype "text", some of the bytes "disappear", probably because of escape-characters or something like tha...more >>

I have a client that deleted thier 6.5 DataBase. All I have is a .DAT file. The restore tab is greyed out, not sure why. Is there any way to recover the DB from the .DAT file I have remaining?? I wrote a program to read the .DAT file, but it does not look like a database file. Any assista...more >>

I need to enforce a simple validation rule on my table. The solution I came up works, but seems complex. Im hopping there is a better way to do this (reduced to the simplest case): I have table T, with fields: id (int key) fSelected (bit) //note fSelected is indexed I want to enforce th...more >>

-- Anthony B ------------------------ My RAM may be gone, but my floppy remembers "Anthony" <antgoodlife@nospam.comcast.net> wrote in message news:... > I've been using sql statements for a few weeks and recently realized there > are a whole host of commands I don't understand.. and can...more >>