Hi SQL Gurus, When should I use Windows or SQL Collations ? What are the considerations ? Thank you, Trist ...more >>

On an MSDE server (eg no Maintenance Plan) is any other maintenance (other than the backup) needed than these? Is it best to do these before the backup? For each database: DBCC CHECKDB( 'DBName' ) WITH NO_INFOMSGS DBCC CHECKCATALOG( 'DBName' ) WITH NO_INFOMSGS For each table in each data...more >>

Hi, all: I have a form which lets users choose more than one value for each question. But how do I insert each value as a separate row in my table (instead of having the values submitted as a comma-delimited list)? Thanks for your help. J ...more >>

Hi We have just recently converted a MSACCESS 97/SQL Server 7.0 app. to MSACCESS 2002/SQL Server 2000. We have encounter some troublesome code errors and not sure the cause. Here is one of them. We have a table "tblMemberPaymentBatch" on SQL Server 2000 that has zero records. It has a clu...more >>

hi, i'm new to SQL server... is it possbile to create a table with sub tables inside? let's say i have a database named "payroll" it has a table named "assets" then inside the "asset" tables it contains tables namely "companya_asset", "companyb_asset", and "companyc_asset" is this possi...more >>

Hi SQL Gurus, Before starting a long and exclusive DML batch, I have to make sure that I am the only one that connect to the DB. (no matter how many connection but it can only be from my PC) The problem is : my front end app create two connection to the DB, different spid, different HostNam...more >>

Hi If I create a function called MyFunction and then create a table MyTable which has a computed column based on MyFunction and then try to alter the function I will get the following error. Server: Msg 3729, Level 16, State 3, Procedure MyFunction, Line 31 Cannot ALTER 'MyFunction' because...more >>

How do you call a procedure in TSQL passing a concatenated string e.g. Exec @rc=myproc 'a'+'b' Give syntax error Exec @rc=myproc 'a' Work okay Many Thanks Jon ...more >>

In master table the data type of Primary Key is UniqueIdentiFier, In Detail table what should be the DataType of the Referenced column to establish a Foreign Key Relationship? Int & BigInt are not Working, only UniqueIdentifier is working, Is this the Restriction? ...more >>

I would appreciate any suggestions for creating a stored procedure as follows: 1. A columns-to-be-selected table contains a list of one or more columns to be selected and can be updated to include more columns or fewer columns. Example: Columns-To-Be-Selected Table Columns --------...more >>

I would like to determine the dependencies that corresponds to each table, view, or stored procedure in a database. Please help me create a script that will determine these dependencies. Thanks, Mark...more >>

I have 6000 stored procedures that I need to determine if they can be complied for a database. I need to write a script that will write an error for each stored procedure that will not compile for a database. Please help me create this script. Thank You, Tim ...more >>

if Table T has a column named C, and C is a foreign key referenced to Table M and Column C, and the name of Foreign Key Constraint for T.C->M.C is ForeignKeyTC now I would like to drop column T.C, but it seems need to drop the foreign key constraint first, am I right? But how to drop the fo...more >>

What I design table and set data type in SQL enterprise manager, what's the major difference between varchar and char data type?? please help! ...more >>

I need to change a calculation in a view to being deterministic in sql 2000 (so I can create an index on it), but I am not sure why the current setup is not deterministic. Can someone point me in the right direction please? Any help would be appreciated. Thank you. Robert Taylor CREATE...more >>

Hello all, I'm trying to convert DBCS (double-byte character set) to UNICODE. My box chcp=437, font used in SQL Analyzer is Arial Unicode MS (Western) So far I came up with this code: DECLARE @ToDecode VARCHAR(8000) SET @ToDecode = '<Æ-±' DECLARE @Decoder TABLE (Decoded VARCHAR(8000) COLLA...more >>

I want toknow of the messages from sql server, such as Warning: The table 'Consulta_Final_51' has been created but its maximum row size (24886) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 byt...more >>

How can I input a DATETIME value into the Parameter Value textbox of the "Debug Procedure" dialog box? I tried 1/1/2003, '1/1/2003', 2003/1/1 12:30 and '2003/1/1 12:30'. All of them yielded an error message: [Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification ...more >>

Hi All, I have columns with trailing ']' in all records. How can i delete the trailing ']' in all records? ex. Column C ----------- 312] 0987213] 87323] 564] 773238] 4234] should be like this Column C ----------- 312 0987213 87323 564 773238 4234 thanx! ...more >>

Hi guys, How can I get parent category_name from the following query? I've been experimenting with a subquery, but I can't get it work... Btw, can this query be optimized? Thanks for any kind of help. Jonah SELECT Programmes.emi_id, Programmes.start_date, Programmes.end_date, P...more >>

Hello All, I have an update trigger that runs if a customer's address is changed. The problem is if the new data is the exact same as the original data in the address field, I don't want the trigger to run. I've looked at Columns_Updated() already. The column is #70 is the table, so, d...more >>

Hello, In MySQL, this statement works: DROP TABLE IF EXISTS my_ado In SQL Server, the statement generates this error: Incorrect syntax near the keyword 'IF' What is the correct SQL Server statement to conditionally drop a table? -- Bill Swartz, PhD Voss Scientific ...more >>

This is a general design question for which i'd like some feedback, the following assumptions can be made: 1. Proprietary code is OK, this product will only run on SQL Server for the foreseeable future 2. This table is read from often, written to rarely We have a constants table that is...more >>

hwo to insert a union table to #tblfilenames? INSERT #tblfilenames SELECT ( SELECT image1 AS image1 FROM table1 UNION SELECT image1 AS image1 FROM table1 UNION SELECT image2 AS image1 FROM table1 ) AS temp ?? ...more >>

How can i run a job fron sqlserver on a command line or a query instruction. i have a job which name is "test_conti_04" , this job was created when i made a publisher replica... how can i syncronize with a command line or a query instrution? i know i can do it with Enterprise manager...more >>

Hi, I have tab-delimited, text file with damaged data in decimal field (like -19.-98). Usually, there are just a few damaged rows. I use BULK INSERT with parameter MAXERRORS = 10 (see full syntax below). Problem is it doesn't skip those damaged rows, but whole import fails. What is wrong here? ...more >>

Is ther any way to open a text file and read it within a stored procedure? Thanks, Fred...more >>

Hi all SQL server 2k Dev ed I want to sum up a column of numbers and if that is >10 then do something. I was wondering is it posible to do it WITHOUT use a variable ? with a variable i would do the following. eg SELECT @varName = SUM(fieldname) FROM TableName WHERE ID = 1 IF ...more >>

I am looking at the pros and cons of using Stored procedure or DTS script I want to select some records, Create a file, FTP the file. Run some dos commands Any reference to convince skeptics will be greatly apreciated ...more >>

I'm trying to find out the default database for a given user using the EnumUsers method. Below is my code: If DbName1 = "<default>" Then Dim usrs As DMO.QueryResults If Con1Auth = "WinAuth" Then Dim usr As WindowsIdentity ...more >>

Select convert(datetime,0x01C1CC57798F2590) Server: Msg 210, Level 16, State 1, Line 1 Syntax error converting datetime from binary/varbinary string. ...more >>

When I use a select statement such as the following: SELECT field1,count(*) from SomeTable where field2='SomeValue' group by field1 If this returns no rows I get a @@rowcount of 0. Which is correct. However, when I do this: SELECT field1,count(*) from SomeTable where field2='SomeValu...more >>

Thanks in advance for your help! I have a client who will be using Crystal Reports for enterprise reporting. They want to schedule reports to run through Crystal but do not want the end user to choose and pass in the parameters. Other than using CR .NET RDC to do it programatically they...more >>

Hi All I found an error ( run time error 3669. Execution cancelled) when ran the following query via VB6 SP5. select crc,month,sum(v) as v from ( select crc,v, case when fadate between '1/1/2003' and '1/31/2003' then 1 else 2 end as month from...more >>

Hello all, My school at a large university just finished developing a SQL Server 2000 Web based application. We are a mixex desktop/server environment (Windows, Linux, Mac, Novell). To meet everyones needs we developed a web based application. We choose Cold Fusion as the middleware. The w...more >>

Hi I am executing this statement , the final output which i get is number 5. I want to capture this value in a variable , how do i do it Remember, i am executing a SQL Statement and not a procedure declare @TSQL varchar(8000) set @TSQL = 'select count(*) from person' exec (@TSQL) ...more >>

Hi NG, I am developing an application that now needs to append a (constant) text to a ntext column. Unfortunately my preferred solution via UPDATE doesn't work on ntext columns. SQLServer Books Online refer to UPDATETEXT, but I have some newbie-questions regarding this: How can I start s...more >>

Hi, I have a rather strange problem with some linked servers that we use to read data in from some Excel spreadsheets. Everything works fine for a few weeks (or months) at which point all linked servers to Excel stop working and return that (oh so useful) error "OLE DB Provider reported ...more >>

In the long query below I need to ORDER BY the Pts column DESC (which I have separated by some blanks lines to see it easier), can anyone please help?? Thanks ahead. SELECT Teams.TeamDescription, Divisions.DivisionDescription, (SELECT (SUM(IIf(Games.HomeTeam = Teams.TeamDescription, ...more >>

Hi All, I have table with a binary data type coulumn(length 8 ), This column is used to store dates,I need to convert the contents of this column to datetime when I query this table,Please let me know the easiest way to do this. Thanks in Advance Ganez...more >>

Hy Guys, I need a Data Type that armazenate text with length = 500 What is this Data Type??? ...more >>

hi to all, i tried to enter the function name as parameter to SP like this: CREATE PROCEDURE PROCEDURE_NAME @function_name VARCHAR(50) AS return (execute(@function_name)) but in time of run i received message (Incorrect syntax) Does anybody know about this problem? a lot of thanks, Mike...more >>

I have a query with employee names and a list of dates I want to return in one field. There could be any number of dates returned. (It is the start dates of leave of absents). example: I have the code in this format: Employee Date JSmith Oct 3, 2003 TBub Jan 5 , 20...more >>

Hello all, Does anyone know if there is a way to use a variable to point to a table? I may not be saying this the right way, so let me explain what I am trying to do: I am trying to write a stored procedure that will point to different databases and tables on the same server based on v...more >>

i have a db where all objects are owned by dbo. two performance questions: 1. does prefixing objects (tables/views) with owner in stored procedures has any measurable impact on the system performance? 2. does schemabinding functions has any effect whatsoever on performance? i'll be intersted...more >>

Hi, I it possible to run a stored procedure from one db on the same server and get output from a different db on that same server? Would there be a performance hit if so. Thanks Ron...more >>

Hi there. I'm having a little trouble with some SQL syntax.... Specifically, I have a stored procedure which returns a results set (say 5 columns named 'rsValue1' - 'rsValue5'). I would ilke to insert this results set into a table which is 1 column wider that the results and I would lik...more >>

I need to obtain the phisical size for each table at one DB, how can I obtain this? Any information will be appreciated. Thanks...more >>

Hi All, In Oracle, you can compile all the packages, procedures and functions using COMPILE_SCHEMA package. Is there any tool to compile it or is there any possiblity to write the coding to compile it?. Any suggestion would be greately appreciated. -- Thank you, With Regards...more >>

Is there a limit on the number of unions that can be created in a view? If not what are the practical performance considerations. Server is a Compaq Proliant ML 370, 733 Mhz. Current load is less than 10% usage....more >>

i have a file like d:\folder\myFile.txt i want to see if the file has any contents, if it does i want to email the data out of it to myself. how do i look at the file size? in vb i can do something like len("c:\folder\myfile.txt") can i do this in tsql? also does anyone know how ...more >>

Dear All, We have a job that runs nightly. However occasionally we need to run the same thing during the day. To save me going into Enterprise Manager, is there a way of starting a job through a sp ? Thanks J...more >>

What is an easy way to lock a table. I wrote a littel proc which will run the sp_who procedure and if ther are mover then "x" jobs blocked at the time it runs, then it creates rows in a table and sends out a reports. So I was wanting to lock the table and then submit a few jobs which wou...more >>

I have a summary update process that must occur each time a record id added (or certain fields are updated) in a particular table. The process takes about 7 seconds with the current database. This is a little too long to ask the user to wait after making an update. If I put the process ...more >>

admittedly, this SP is probably a mess given that I am not a TSQL pro. its purpose is to, based on the arguments, do 1) paging or 2) return prev/next ids for a given record. it does the job, but at the cost of several seconds. i was hoping someone could give me pointers on how to optimize it...more >>

What's the benefit when using NOLOCK in UPDATE and INSERT commands ? Can this solve record lock issue ? Thanks. ...more >>

Hello: I have the following table: CREATE TABLE TranRules_sa.ELEM_GROUP ( Parent_Elem_ID_N int NOT NULL, Elem_ID_N int NOT NULL, Order_N tinyint NOT NULL, Occurs_Min_N tinyint NULL, Offset_N int NULL, ...more >>

Why when I rename a default in Ent Manager then run sp_helptext does it still return the old default name...more >>

Hello, when I run the osql procedure to create an output file of the sp_who2, the output file wraps the line. I would appear that the output defaults to 80 charactes for the Lrecl. Is ther anyway I can control this so that my lines do not wrap? Thanks inadvance for your assistance!...more >>

Hi all: I have been grappling with this problem for sometime now. I have a patch process that picks up a sql file, adds wrapper code to it and creates a patch file. Eg: Generate patchid If (patchid NOT EXISTS) copy the sql from the file here This file is then applied during our ...more >>

Hello I am upgrading a database from SQL Server 6.5 to 7.0 and am now having problems with blocking. An application written in MS Access using DAO is causing the probem when trying to open a querydef recordset. The application just freezes until it times out and then comes back with an ODBC erro...more >>

I need my query to return a MIN and MAX in ASCII order. Specifically, strings starting with { | } ~ should follow regular characters (ie in ascii table order). My collation setting for the column is SQL_Latin1_General_CP1_CS_AS. The database setting is SQL_Latin1_General_CP1_CI_AS. I ...more >>

In t-SQL you first declare a cursor and then open it. I wonder if it is possible to declare de cursor with a parameter that is resolved when opening the cursor. The following sequence would be possible: declare mycursor cursor for select * from mytable where fieldx = @par .... set @pa...more >>

Hi, I'm running SQL Server 2000 Service Pack 3 and am having problems debugging a stored procedure. I've ensured debugging is enabled, by running the "exec sp_sdidebug 'legacy_on'" as per Knowledge Base article Q328151. I've encountered this problem before and by simply running the ab...more >>

Hi all, I'm working with MS SQL Server 6.5. In my application I perform a database transfer with the DMO DatabaseObject and TransferObject. This works fine. My only question is where to set the directory for the transferfiles (e.g. *.BCP). At the moment these files are written to the di...more >>

Why won't the following code work? I am trying to reference a table name within table1 and insert some data from table3 where the period in referenced in temp table, #table2 Thanks Dave INSERT INTO (SELECT Version FROM table1) SELECT * FROM table3 WHERE PERIOD IN (SELECT PERIOD...more >>

Hello All what I would like to know is how do I embed and run my SQL queries in a Visual Basic enviroment.I want to be able to access my SQL database through VB. I really want this to work.. Thanks ...more >>

I am reposting this since my earlier post did not appear even after a considerable time. Hello All, I have a query like this: SELECT id, empl, fromdate, todate, type, value FROM tblEmployeeValue ORDER BY Type, fromdate This query which returns following output id empl From...more >>

Hello All, I have a query like this: SELECT id, empl, fromdate, todate, type, value FROM tblEmployeeValue ORDER BY Type, fromdate This query returns following output: id empl FromDate ToDate Type Value 8614 665 2003-01-27 NULL 1 36 8615 665 2003-01-27 2003-04-2...more >>

I know a few algorithms to perform MEDIAN calculations. However, all seem to assume that you want to only calculate one median value. What I want to do is do something similar to an AVG with a GROUP BY, but with a MEDIAN instead. The data is in a temp table. Some sample data might help explain: ...more >>