H I create a stored Procedure with CREATE PROCEDURE statement and I want it to be recompiled without executing the stored procedure I want to recompile it while creating the sp or after that , not by executing it next time How can I do this Thanks...more >>

Hi, I need to pull the SP code from MS SQL Server 2000. Can any body tell me which system table contains this code ? is it encrypted? Thanks, Hassan H. ...more >>

I have a complicated sql2000 stored procedure. For testing, I want to return 2 hard-coded test rows/records instead of actual data. I know how to return one row, but I don't know how to return 2 or 3 test rows. For one, I do this: Select 123 as FieldA, 456 as FieldB But how do I d...more >>

Hi. This is a very basic question but I can't see what I'm doing wrong. I am trying to copy a table and all of its data from one database to another. Original Databse: db_sar Original Table : staff New Database: db_newuser New Table : staff My Query: SELECT * INTO db_newu...more >>

Hi, I have a Session table, with a field, session_close_type char(1) allow nulls I want to update it with a stored proc, but only if it is empty I made the following stored proc: ALTER PROCEDURE UpdateSessionLogout ( @SessionID INT, @SessionType char(1) ) AS BEGIN SE...more >>

Hi, If I run an select sql statement used 'exec',which type variable it will return. just like below: exec 'select * from table1' Then how do I get the return value? Appreciate for any answer! Thanks Jack ...more >>

I am writing a program (installer) that loads a bunch of SQL scripts from disk and runs them against an existing SQL database. The problem is I was planning on using ADO and found that I this method is not going to work because the SQL scripts a littered with GO statements and it would take for...more >>

Here are the two queries. select customer_survey_backup20040216.communications_id from customer_survey_backup20040216 left join customers on customer_survey_backup20040216.communications_id = customers.communications_id where customers.communications_id is null select customer_survey_backu...more >>

Is is possible to have foreign key constraints between tables in different databases? Bob ...more >>

hi is it ok to code triggers? do they have a very large performance overhead? like cursors do. thanx ...more >>

Hi group, Can sombody give me some pointers in how to convert visual basic code with recordsets to a stored procedures. For example: how should i implement the following. Set rs = CurrentDb.OpenRecordset("SELECT * FROM Table1 WHERE KeyField=" & mId) rs.Edit If rs!Field1 > 0 A...more >>

Hi guys, My SQL program trying to compete with flat file system Just to select one or more rows from table with 500 columns ( total length of brow is about 3K) . Flat file system is much faster and I think if I had possibility to send to client a buffer with selected rows instead of recordset i...more >>

What is the correct syntax for a datetime paramter in order for the debugger to work? When I pasted a datetime value from the database I get this error: [ODBC SQL Server Driver] Datetime field overflow Thanks, Doug Stiers ...more >>

Hi, From reading the BOL, I can see that you can specify a different owner when creating a default. How does one bind such a default to, for example, an UDT? -- With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL Server. Upscene ...more >>

Hi everybody, developing an application I have encountered this situation. Sometime insert triggers fire also if no row are inserted. This is how to reproduce what I've found: CREATE TABLE [dbo].[TMPTABLE] ( [field] [char] (10) NULL ) ON [PRIMARY] GO CREATE TRIGGER [AFTERINS] ON ...more >>

Hi, I would like to know if something like this is possible without defining my own iif function select stor_id, qty, (WHEN qty>10 THEN 1 ELSE 0 END) from sales thankx ...more >>

Hi all, I've got a table with data like this: Fred 3 Ed 2 From this I'd like to create a table that looks like this: Fred 1 Fred 2 Fred 3 Ed 1 Ed 2 Can I do this without a cursor? Thanks!...more >>

Hi All, I want to catch the next MSSQL error in my SQL code with following continue calculations Server: Msg 17, Level 16, State 1, Line 1 SQL Server does not exist or access denied. If REMOTE_SERVER_1 is inaccessible (as in (a) below) the executing of SQL will not continue with (b) ...more >>

I need to rebuild a View in SQL 2000. I made some modifications on one of the tables that this View uses for getting information, but for some reason all the rows that I modified are not showing on the View. I was looking some information about the subject and it seems that the View needs to be re...more >>

How can i make the Sql result like this. My table details are like this id name 1 a 2 b 3 c 4 d 5 e 6 f 7 g i want the result like this id name id name id name 1 a 2 b 3 c 4 d 5 e 6 f 7 g how can i do this. Any idea Thanks in advance ...more >>

I want to format data in sql transactional language like SELECT. For example, the raw data is: LogTime, Temperatur 1:01:03pm, 14.567 After SELECT ???? FROM ... it should become 13:01:03, 14.6 How can I do this? Thanks for any of your help. ...more >>

Script that was supposed to make a checkpoint of a existing table! but results in error.. 1. create a table named "mistable" 2. run this script. if NOT exists(select name from sysobjects (nolock) where name='MistableCheckpoint') begin select top 1 * into Mistabl...more >>

I have a simple SP that inserts records into a table. I want to check that the records were successefully inserted so I test the @@error value and when it's <> 0 I want to print a message. the problem is that I find that when ever an error accours (e.g. 8114 Error converting data type nvarchar ...more >>

Hello All In my database I have multiple tables. I use select information from each of these tables and insert it into one single table. I then index it up and run queries on it. The table is dropped each time and recreated and reindexed. THis process takes relatively less time. The reindexi...more >>

Hello All Can anyone tell me a little more about the execution plan. I am a little confused by the explanation on the SDK. I was analyzing the execution plan option to find out ways to speed up my queries which use cursors. The queries are being generated of tables that are properly indexed bu...more >>

I have an outer join query that returns results like: Col1 Col2 ---------- bob 1 bob 2 bob 3 I would like to make the query replace duplicated values from the first table with blanks like: Col1 Col2 ---------- bob 1 2 3 Is there a way to do this?...more >>

I have an application that uses single statement transactions meaning one insert or one update or one delete per commit. That being said we have noticed that using the WITH (NOLOCK) syntax speeds up large queries significantly (and minimized deadlocking). Can you see any problems with using WITH...more >>

I use one login name to connect my db to my application - lets call dbusr21. However I would like to keep track of the users who login in the application in my database. So I need to use the operating systems user since my database user in only one - the dbusr21. I know in Oracle I can use...more >>

I thought the session_user was the same loginname but when I run the SESSION_USER function in Query Analyzer connecting as a different user (not a dbo) I continue to see the session_user as the dbo (myself). Can anyone clarify this? Thanks in advance for your help. Olivia DECLARE @session...more >>

Hello, I'm migrating my C#.net application from a MS Access-database to SQL Server 2000. Now I found a problem. In my code I use a lot (and I mean a lot) of queries with multiple tables and some tables have columns with the same names ("id", "name" etc). For example: SELECT a.id, b.id FR...more >>

Hi all This code will create a table and populate it with a hierarchy of relations. create table tblRelations ( Id int identity primary key, ParentObjectId int, -- FK to object table ChildObjectId int -- FK to object table ) insert into tblRelations (ParentObjectId, ChildObjectId) v...more >>

My table has data in it that includes a flag value - AnsweredFlag. I'm having trouble when I want to group the data and report on how many in the group were ansered and how many were not. For example, if a group of data has only rows with AnsweredFLag = 0, then I get no returned row with a cou...more >>

Lo all, Ok - not really sure of the difference between the two, some info would be appreciated. I normally create any DTS's I'm making at work in the Meta Packages area, however, when I connected to a hosting companies SQL Server I was testing today I created my package which is saved in th...more >>

Can someone tell me if you can copy a table with only the structure? If so, can someone tell me how. Thanks, Chris ...more >>

I hope someone can help with this because it's been driving me up the wall! I have a table like this (simplified version): Proj_ID |StringValue |StringDescr --------|------------------|---------------- AB3445 |Business Analysis |Division AB3445 |Long-Term |Project Length A...more >>

Hi I'm trying to write a cursor with a dynamic column name in the SQL Select : DECLARE @varcol char(50) SET @varcol = 'column2' DECLARE myCursor CURSOR FOR SELECT @varcol FROM strat_tbl WHERE @varcol IS NOT NULL OPEN myCursor .... But this does not provide the expected result since ...more >>

Hi, when I change number of columns in table I lost dependency to (for ex.) stored procesure. I have to recompile every stored procedure for update sysdepends table. Please Can somebody help me? I want to rebuild sysdepends automaticaly or I can run any job but recompile every stored proc...more >>

I have a table in my_db that has credit card information in one of the columns. I would like to replace this credit card data with some values that are not real. The only requirement is that new data has to be the same lenght as the real data. What is the easiest way to acomplish that ? ...more >>

I have detail table that contains attributes or properties for each parent record. These properties can have 1 to n attributes. Since the number of attributes per parent can be variable AND there can two of the same attribute per parent but with different decriptive values, I denormalized the pare...more >>

Hello:) I am trying to count records for each unique address within each member. The TSQL is already done to select (using coalesce between 2 tables). The primary key is member and group (address is not a part of the primary key therefore I can't just count unique index); SELECT COALESCE...more >>

I am having a problem with Distinct. My statement looks like this: SELECT Distinct Field1, Field2, Field3 FROM MyDatabase GROUP BY Field3, Field2, Field1 In the database, there are records that have the same info in Field1. I want only the first record with each unique Field1. How...more >>

According to SQL Server documentation: I can use any expression in a SELECT clause. An expression can consist of two constants separated by a comparison operator. Yet when I try this: SELECT EmployeeID, 4>3 As Value FROM Northwind.dbo.Employees I get the error: Line 1: Incorrect ...more >>

I am facing a problem when try to update and insert thai wording into my SQL 2000 server database. I can type the thai language word in query analyzer, but when i execute the insert and update statement, it show successful update. but when i select the row of record, it show me those funny...more >>

I have a SQL 2000 database that is accessed via a VB6 application by the users. The users are given a Search Screen comprised of about 25 different fields they can enter criteria for. The different field types available for searching are integer, date, and text. Their criteria is then s...more >>

A strange problem has cropped up as follows: select isnumeric('.')=1 select isnumeric('1')=1 but select isnumeric('1.1')=0 I have a web page that calls a stored proc using isnumeric to either calculate a price or return 0. Today, all prices are coming up as 0. However, sometimes if I refr...more >>

Hi, hope someone can help with this - it's got me completely stumped! I've a table like this: (use a fixed-width font to view) Proj_ID |StringValue |StringDescr --------|------------------|---------------- AB3445 |Business Analysis |Division Name AB3445 |Long-Term |Project Du...more >>

Hello All I want to pass an Excel range data to a SQL Server stored procedure. I am trying to pass the data in a Excel range in form of some VB array to a stored procedure. Can you kindly provide a simple sample code in VBA and in T-SQL stored proc how I can do this. Thanks Belinda...more >>

Hello From SQL server I have opened a rowset to insert data into an Access table which works fine. INSERT OPENROWSET ('Microsoft.Jet.OLEDB.4.0','\\servername\sharename\databasename.mdb';'admin';' ',data) (LEVEL) SELECT TID FROM XYZ Is it possible to rename this table called data to data2? ...more >>

Hi, i have this in a stored procedure.. Declare @test varchar(10 set @test='a-b 1)its possible to make an split of the variable test something like... @newvar=split(@test, '-' to obtain an array with two elements, the first @newvar(0)=a and the second @newvar(1)= and to know what number ...more >>

I switched on implicit transactions for QA. I created some SPs in QA, didn't think to commit, executed them a number of times in QA, etc. No problems. Later, I was receiving ASP.NET errors, so I checked SEM and found my SPs were not there. So ... am I correct in assuming that creating a ...more >>