Hi... i want to alter table using query analyser, and i want to change the data type from numeric to numeric identity. after this command - "alter table new_khasra_details alter column khasra_id numeric IDENTITY " it gives the following error--- Server: Msg 156, Level 15, State 1, Line ...more >>

I found that if you do not include any effective SQL statement (excluding comment), SQL Server 2000 Query Analyzer will think it is an error: Server: Msg 156, Level 15, State 1, Line 6 Incorrect syntax near the keyword 'end'. if 1=1 select getdate() else begin --select 'ok' end Is th...more >>

Hi, I have a select query that usesjoins, union and order by, the query uses only selects no inserts/updates nor deletes. when run as an SQL batch it produces results for about 40 secs. When I put the same query into a stored procedure and I run the procedure ( source is the query and nothin...more >>

During the installation of our project we want that the end-users to be able to rename the database. We generated the sql scripts for the default database schema. But for renaming the database object we want to use parameter values instead of the hardcoded database name in the scripts. Is it som...more >>

Hello all, I have a question about using the DISCTINCT Keyword. Does anyone know which record it takes... the first occurence? If so, does it still exhibit his behavior over a sorted table? For example, if one finds him/herself in a situation where one must select one record from the many s...more >>

I get the following error when I run the query pasted below it. As you can see, I'm not referencing anything called 'Col1047'. I'm thinking this is a linked server issue but I'm really lost as to how to resolve it as the query isn't particularly complicated. Just as an aside. This error is ...more >>

Hi, Is it possible to use a select statement to return, for example, a userId and a single field containing a delimited string of all records that match in a second table. For example if we have Companies and People, is it possible to select coId and a sinlge field containing a single strin...more >>

At some point after running this report the "Wholesale Price" column data type in the excel spread sheet changes from Currency to General... It seems to ba around the 300th or so row but varies... This problem only occurs if the client is running Office XP.. And only in our production environment...more >>

Hi I've got exel xls file with a lot of data and i have to import it to Sqlserver. I've used DTS and ok I have a table with data but there is one column PASS/LOGIN and in that column i have both passwords and logins separeted with sign "/", i need separate them and export to another table ...more >>

this works ------BEGIN SQL------- CREATE PROCEDURE [tmpBug] AS DECLARE @SQL varchar(1000) SET @SQL = ' test ' SELECT @SQL ------END SQL------- this gives the error message Error 105: Unclosed quatation mark before the character string ''. Line 5: Incorrect syntax near ''. ------B...more >>

I need to write a script that a dbo can run against a database and it needs to do the following... Create a new Role 'ABC' Create another new Role 'DEF' Grant execute permissions to Role 'ABC' to all stored procs that begin with ABC. Grant execute permissions to Role 'DEF' to all store...more >>

Greetings! I am trying to pinpoint an issue a developer ran into. We have two different SQL Servers both running version 7.00.1063. They also both run on Windows NT 4. The problem we're having is that we have NT authenticated accounts on both servers. On the production server, the ...more >>

Hi, Further to my previous question on syntax, I have a question regarding the syntax of a trigger. This question is probably more to do with general TSQL syntax than triggers but please bare with me. I realise that this probably isn't the best design in the world but I figure its a good le...more >>

Hi, Why this query works fine with sp_who, but it doesn't work with sp_help_job ? SELECT * into #Tmp FROM OPENQUERY(<server>, 'msdb..sp_help_job ') It returns error: Server: Msg 7357, Level 16, State 2, Line 1 Could not process object 'msdb..sp_help_job '. The OLE DB provider 'SQLOL...more >>

I am writing a simple sql statement to filter records based on a input date. The date is currently stored in a database as a varchar field, and changing this field to a datetime is not (currently) an option. This sql statement works fine without the WHERE clause, but with the where clause it beg...more >>

Gents, Is there any way this would work, without using the EXEC() command? DECLARE @String varchar(100) SET @String = 'RoleID = 1' SELECT * FROM dbo.deldlrEmployeeRole WHERE @String Thanks in advance, Jaap Terlouw...more >>

I have an application that uses MSDE as base of data. I already know as installing MSDE in my customer, however I am not getting to execute the command correctly to register the database in MSDE, without the aid of Access. I already read everything that had at the Forum, but even so I didn't get...more >>

Friends, please, Could you help me? I have the next storeprocedure create proc miProc @sWhere varchar(50) select a.code, b.description from table1 a, table2 b where a.code=b.code + @sWhere When I run this with exec miProc " and a.code=321 " the results it's (0 row(s) affected), Pl...more >>

I prefer to keep raw SQL out of my code and in stored procedures, but some built-in SQL functionality ("create procedure", etc) only works in the 'current' database. Unfortunately, executing dynamically generated SQL to change the database with USE doesn't work within stored procedures, so I woul...more >>

One of my developers has used the T-SQL command SET XACT_ABORT ON (which I confess I was unfamiliar with) in his stored procedures. I have reason to believe that the transaction is failing, for one reason or another, to store the data. In looking at his code he had a statement like this: BEGIN...more >>

How do you get the top N rows from a query where N is any number? I know I can do something like this: Select Top 100 ColA From Table1 but is there any way to do: Select Top N ColA From Table1 and assign N at runtime in some kind of stored procedure?...more >>

I need to generate a incremental numeric value for a given user during its session. My system has a global operation identifier that is saved in each new record, update or delete operation on audit tables that is currently generated by inserting a value in a table with an identity column. I...more >>

I thought I remembered back in the 6.5 days when you could backup just a table. Is that still possible? We have a database over 150 gigs and would like to run a dbcc reindex on some of our larger tables. Last time we did it in production, it blew out our logs and messed up our log shipping. We...more >>

Hi, I would like to store Word templates in a image field in one of my tables. Are there any perfomance issues (speed etc on indexing) when including an image field in a main table, rather than putting it in a separate link table. Thanks H. ...more >>

Can we create index on a table variable? Thanks. ...more >>

i have three tables: products,orders and orderlines and I want to make a procedure which will give the quantity of each product ordered under a given order number ( I still want the product to show even if there is no quantity). I need to first eliminate all the inappropriate rows from the order...more >>

Hello, im trying to build a variables string for a stored procedure like this: .. .. .. SELECT count(*) AS ObjSNR FROM SD_Objekte WHERE SD_Objekte.ObjTyp IN (17,21,20) <<<<<< HERE IS THE Problem!!!!! AND SD_Objekte.ObjLSNr = @Liegenschaft AND SD_Objekte.ObjLage IN (1,...more >>

CREATE TABLE XYZ.TABLE1 (col1, col2); CREATE PROCEDURE dbo.PROC1 AS SELECT * FROM XYZ.TABLE1; CREATE PROCEDURE dbo.PROC2 AS EXEC sp_excutesql N'select * from XYZ.TABLE1'; IF the login user X has rights to dbo.Proc1 & dbo.Proc2 and no rights on XYZ.TABLE1 then X can execute dbo.Proc...more >>

In my table I have defined an insert trigger which updates a column (say columnX) after the records are inserted as while inserts ColumnX is left Null. So I have to grab certain values from other tables concatenate them and update that columnX from inserted table. I also have defined an ...more >>

I am having problems with a stored procedure not returning the @@identity to the ASP Page that called the stored procedure. Any ideas? The stored procedure is inserting the records, but I just cannot seem to get the identity. Here is the ASP and Stored Procedure. ASP: Set conn = Server.Cr...more >>

I want to create a SP that is passed a View Name - but I can't use this variable as an object name within a Select statement. My question: Can I create a View object by querying the View Collection and use that object within the Select Statement?...more >>

How can I obtain a "cascading referential integrity constraints" in SQL server 7.0? (delete rows with same foreign key) Thanks! ...more >>

What are the differences between using IN and EXISTS? They seem to be functionally equivalent so why have both? Are there any instances when you would want to use one instead of the other?...more >>

Hi! I need help in upgrading SQL 7.0 (standard edition) to SQL 2000 (enterprise edition). The problem I am having is during installation the "upgrade" option is grayed out and if you proceed, it creates a new instance of SQL Server 2000. Is there any other way to do it? If not, then I underst...more >>

I know this is obvious but what is a merge join and how does it differ from a normal join? Is this just jargon I have never come across before. And how does sorting take place in the two....more >>

Hi all, i'm using in some stored procedures a # table that contains a discreet number of records (about 70000). I choose to add a primary key to encrease performances ad that it happens. The code i used is the below: alter table #tmptree ADD constraint [pk_tmptree] primary key ( cNodeID, ...more >>

I all In my case, I use a DTS to import text data into Table and I have a trigger on this table to update a another table When i Test the trigger by a insert manualy, the trigger fire, but when the dts run , the trigger don't fire. Any ideas Thxs...more >>