I am facing a problem : i have created a stored procedure : Create Procedure CreateProc @Str Varchar(8000) As Begin exec(@Str) End Through Query analyser, i am executing stored procedure : CreateProc 'Create Procedure dbo.Stmt as Select * From Dept Go Create Procedure ...more >>

I want to know nested transactions should be reference counted? i.e. If there are 3 BEGIN TRANSACTION, then there should be 3 COMMIT TRANSACTION? thanks! ...more >>

Given the object model, I want to design the database tables. The object model is that school has a list of courses. The following are my 2 approaches. My question is which one is better? Should table COURSE has the foreign key of table SCHOOL? Or table SCHOOL has the foreign key of table COU...more >>

I want to know if VARCHAR2 data type is not valid in MS-SQL. In SQL Query Analyzer, it is successful to parse the following query. But when I execute it, it has the error messages "Column or parameter #1: Cannot find data type varchar2." CREATE TABLE STUDENT ( NAME varchar2(50) not null,...more >>

I want to be able to monitor an Exchange mailbox for specifically formatted messages with a stored procedure, and when they arrive save an attached file to the file system. I don't think there is any way to do that with the SQL MAIL stored procedures. I've tried using the SP_OA.. procedures and...more >>

Hi, This might be an easy one for you. I have an sql which counts number of records, each statement returns one value, and I need to add the 2 values: SELECT COUNT(*) AS TOTALI94s FROM users WHERE FirmId = 1 AND archivedcont = 0 AND I94DateD BETWEEN getdate() and (getdate()+30) SELECT...more >>

Hi, I'm have trouble with a query i want to make. I hope someone can help. OtherUsers(userID, roleID) ClassUsers(userID, classID, roleID) Above you can see the three tables and their columns. I want to a userID to be specified and then have a column with the roles he/she is assigned to. Ob...more >>

Hello All. I am using an SQL Server 2000 db and have an address table as follows:- CREATE TABLE [Address] ( [AddID] [int] IDENTITY (1, 1) NOT NULL , [Street1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Street2] [varchar] (5...more >>

Hi All Wonder if you could shed some light on a really weird problem. Basically we use a standard client side app that comms with 2 x SQL 2000 db. This SQL 2000 db sits on a Win2003 Server quite happily. The client apps sit on WinXP or Mac OS 9 quite happily. In SQL Enterprise Manager...more >>

I need to get the count() of rows that match two condition but I need to get in diferents columns, one for each count. :S Something like this... (this is a pseudo code) Select Count(*) as Counter1, Count(*) as Counter2 from myTable WHERE Case When (Column1 = 'value1' and Column2='value2) Th...more >>

Hi, How do I create a Crosstab view like the crosstab queries in Access? I understand crosstab as a table of numeric values with labels in X-axis and Y-axis. Thanks ...more >>

Hi there, The idea is that I need to be able to search two tables for a certain name using LIKE. I created SQL that generates an aggregate of first_name and last_name with the alias full_name, but I can't seem to be able to use it in the where clause. Here's what I've done so far: table_1 ...more >>

In Our application there is a method which contain a series ( around 7 ) of functions ( we can think it as sql queries). Earlier we use to create one table for each fuction out put and use it as the input to next function. ( we use simple recovery model) Things were working at that time , but to...more >>

When I do the following.... -- Create a backup device for the subcontractor database USE subcontractor EXEC sp_dropdevice 'myOLDSCDB_1', DELFILE EXEC sp_addumpdevice 'disk', 'MyOLDSCDB_1', 'd:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MyOLDSCDB_1.dat' -- Back up the full subcontract...more >>

1) Can anyone explain in simple the effect of SCROLLABLE and SENSITIVITY on the different types of cursors. 2) I am a newbie. i want to practice Procedures, cursors, triggers and joins.Can anybody tell me where can i find exercise to practice these ? Thanks Panks ...more >>

Is there any generic way to get the names of the columns that were changed after an update statement? Must i compare each colunn or is there some soft of sql server mechanism for getting the list of fields that changed after an update statement? ...more >>

Is there any generic way to get the names of the columns that were changed after an update statement? Must i compare each colunn or is there some soft of sql server mechanism for getting the list of fields that changed after an update statement? ...more >>

Hello all, I have several tables, each of them has a Delete trigger like the following: ---------------------------------------- Create TRIGGER tr_t1_Delete ON dbo.Table1 After Delete AS Set NoCount on Delete rr from ResourceRating rr inner join deleted on rr.GUID = deleted.GUID D...more >>

Hi, is there a way to change the collation of *all* char/varchar fields in every table in a given database? I can do it like 'alter table XYZ alter column Afield char(50) collate Latin1_General_CI_AS' for every field but that's a hell of a job... Groeten, Jeroen...more >>

hi. I am trying to create a trigger on update and in the process i want to capture the machine login information (which may be different from the credentials used to login to SQL). I looked at using "select suser_sname()" but this returns the SQL login info. Please assist. Thanks....more >>

Hello, I have two tables to join here is a simplified view of them Master table rec_id, company, name... 1, xyz, jack 2, yxy, sam detail table rec_id, contacted_on, contacted_by,line_id 1,'01/01/04',kevin,1 1,'05/01/04'kevin,2 2,'01/01/04',joe,1 ...more >>

Now that I got the duplicate rows(using the codes below), I need to select rows that have same value in cloumn 'inovice_number' but different 'trip_ticket'. How can I do that? SELECT tblInvoice.invoice_number, print_date, tdate, trip_ticket FROM tblInvoice JOIN (SELECT tblInvoice...more >>

I have a table which includes some floating point information. I need extract it to a file that stores the values in binary format. If the floaing point value is 33 and you: run: convert(varbinary, val)) - you get - 0x4040800000000000 or run: convert(varchar, convert(varbinary, val)) - you...more >>

Anyone know a way to drop a column from a table with a non-default constraint? ex. ALTER TABLE TEST ADD TEST_DATA1 smallint NOT NULL DEFAULT 0 GO ALTER TABLE TEST DROP COLUMN TEST_DATA1 GO Fails because a constraint on the column. The column can be dropped via SQL Server manager GUI, but I ...more >>

I need some advice, so here is a case scenario for anyone that has some: I have two separate databases that are identical with the exception that some fields in the first database are varchar and those same fields in the second database are nvarchar. Each database has a TON of data in them. I...more >>

Running the DTS export wizard in SQL Server Enterprise Manager in SQL Server 2000 (build 2600.xpclient.010817-1148), I selected copy objects, and chose 21 sprocs and 5 tables to move. I selected extended properties. The transfer dropped and created the 5 tables I had selected, and 3 tables t...more >>

background: sql2k (with sp2) on nt5 I have encountered the "cxpacket wait type" when running queries that used parallel processing. and my reports based on those queries are very slow. Looking at the various articles on the web, there does not appear to be a definiative answer on how to correc...more >>

I am trying this sql: select distinct gbl_no from track.v_latestpntdtts order by gbl_no I also want to get sereral other fields in those rows to, like... select distinct gbl_no,TCN,TRAC_NO,CAT,SRC,HAZ from track.v_latestpntdtts order by gbl_no I only want gbl_no to be distinct Dave ...more >>

Hi, I need get the last 12 Month do my table. How i do this. I try: SELECT GETDATE() - MONTH(12) But this command decrement the Days and not Months complete sql: SELECT * FROM tblPrivateLabelVenda WHERE PrivateLabelVendaPrivateLabelNumero = 1001706752717617 AND PrivateLabelVendaDataDaVen...more >>

I have one SP with two variables @Var1 int @Var2 int When I execute the nested SP from other SP, passing output parameter.. like this Execute myOtherSp @Var1, @Var2 <-- Both param are marked with the "out" property The @Var1 and @Var2 never come back with value, the result for both is...more >>

Hello All, I have 2 SQL 2000 servers linked , but when I run the following stored procedure, it will wait forever without any error messages. However, when I take ou the begin tran, it works fine. begin distributed tran INSERT INTO [LinkedServer].dbName.dbo.table(field001) VALUES ...more >>

If I have something like below this in a stored proc, does it work if @Value is null? A simple test shows that it does but in a more complicated stored proc it doesn't. @ID always comes out null and so it is inserting duplicates. Does it only work in some situations? Thanks Michael Culley ...more >>

Sorry about the cross post, but I need an answer by the end of the week. I am trying to migrate a DTS from a file into the server. I dont know what user name or password will be on the target PC, so I cant specify them, but the integrated security gives the user ability to connect. On my dev...more >>

I have a single table. Key Group Date ---- ------- ------ 1 1 1/1/2004 2 2 1/1/1999 <---- eliminate 3 2 1/7/2004 4 3 1/3/2004 <---- eliminate 5 3 ...more >>

HELLO, HERE IS AN EXAMPLE. CONVERT (CHAR(8), DATE, 10) NOW IS THE RESULT OF THAT A CHAR OR DATE? IF SO CAN I COPNVERT THAT FORMAT BACK TO A DATE? THANKS. ...more >>

Has anyone seen the SQL Server error: "tempdb is skipped. You cannot run a query that requires tempdb"? We're running a .Net web application with a SQL Server 2000 backend, and we get the error intermittently. Restarting the SQL Server service seems to fix it, as it causes tempdb to b...more >>

For some reason, this week we started to get "SQL Server does not exist or access denied" error messages when attempting to connect to our SQL Server databases. This only affects people who dial into our network. But this seems to be a rather complicated situation, so let me explain. We have a...more >>

hello, is thier an easy way to creat a temp column in a select statement that will number the rows? ...more >>

background: sql2k on nt5 can any one tell me if nvarchar(4000) the same as varchar(8000)? thank you. ...more >>

Is there a way to return fields in the same order they been passed to where clause by UserID? Example SELECT User.* FROM User WHERE UserID IN (2002,1001,958,2453,3454) Any Ideas? Thanks a lot, Greg ...more >>

Another theoretical SQL development question: Our development team has had several heated discussions over what a primary key should be in SQL tables. Some say that every table should have an auto increment int ID field for the primary key allowing for ease of use in development and ...more >>

Hi, This is a catch 22: Iam trying to create index in views. I take ownerhip of the dependencies, and run: CREATE UNIQUE CLUSTERED INDEX IV1 ON dbo.BMRUNSTEPSPREADING (RECID, DATAAREAID) GO Then I get the error: Server: Msg 1939, Level 16, State 1, Line 1 Cannot create index on view ...more >>

How can I backup and restore a SQLServer DB from a .net VB program? Thanks, G ...more >>

CREATE DATABASE permission denied in database 'master'. Could not attach database 'IBS_DBSAuditMergeComplete' to file 'C:\Documents and Settings\ALT\My Documents\My Stuff\fNew Format\IBSConsolidated\IBS_DBSAuditMergeComplete.mdf'. I'm trying to connect to a sql server database using sqlDataAda...more >>

This is just a general question....trying to collect some ideas on how to approach this issue: I have a web app that has users across the country. Since they login, I can make their profiles have a time zone field, so that's no big deal. My data in question is stored in SQL 2000 in a dateti...more >>

Below you'll find the DDL for this problem. I'm trying to create the fact table that will go into a cube. The basic question I'm trying to answer is "for each sales_id, how many unique clients had activity in the last 6 months", activity being defined as a client having a joborder created. ...more >>

Any help would be appreciated. I have an ASPX code that outputs a ListBox containing our venue dates. However some of the entries in our Database are missing and therefore in the results this shows up as blank. Here is the code... Dim strSQLforListBox As String = "SELECT VenueDate, VenueI...more >>

I have inherited a database with several dozen stored procedures that are simply: SELECT * FROM (view name) Is this a good idea? Wouldn't it be better just to put the view's SQL directly into the stored procedure? I don't believe these views are being used other than by the stored procs t...more >>

We are currently looking at ERP software solutions for our business and I am getting rather mixed messages from multiple vendors regarding what defines efficient use of SQLServer (or any other RDBMS) given todays technologies. On one side, we have companies offering multiple RDBMS deployments (Or...more >>

Just wondering what the options are for protecting, hiding, or somehow obfuscating source code of stored procedures in a hosted environment. Thanks. ...more >>

Are there any ramifications to creating extremely long stored procedure names? Like 70+ characters long... Thanks in advance, Chris...more >>

I'm sure this has been asked but my searchs haven't given me any fruit for my labor so here I am. Two exmaples below is what I want help on. I know they don't work which is why I'm here but they get the point across of what I'm trying to do. --I'd like this in a cursor Declare cCursor Cursor...more >>

Can someone tell me what is wrong with this statement: USE master GO ALTER DATABASE gms SET SINGLE_USER It passes syntax check, but when I run it, it just sits there and does nothing. I let it run for 4 min before stopping the query. Any help is appreciated as always. Thanks, Gera...more >>

I'm attempting the following SQL query:- SELECT table1.id from table1 left outer join table2 on table1.id = table2.id where ISNULL(table1.number,0) <> ISNULL(table2.number,0) But the damn thing won't work when either of the tables contain NULL values. How do i go about comparing two va...more >>

Hi, I want the equivelent of a pre-processor #define in my osql script. I want to include a script with :r, but I've got the directory defined in a @localvar. This doesn't work because the localvar is not evaluated before the :r (understandably). How to get round this? Thanks Martin ...more >>

Doesn't seem to work. (get syntax error) How can I get round this? I'm using osql to string a load of sql scripts together that can each run on different dbs. Thanks Martin ...more >>

I had to add a log file to a online database in an emergency last week. Now I want to either chane the logical file name (i.e. Test to test.ldf) I forgot the ldf extension, without restarting sql server. Or I need to remove the file. I reied to empty the file first, then remove it, but I h...more >>

HI all, I have a problem with the following query. It works fine on Sql Server 6.5 but in Sql Server 2000 it doesn't work good, it give me out more rows than using 6.5 (that gives the right result). I think the problem is related to the use of left outer join and some setting related to ...more >>

Hello, I'd like to know if it's possible to issue a kill command passing a varible instead a simple value. I tried the folling with no success: DECLARE @ID INT SELECT @ID=3D58 KILL @ID =BFAny idea on How to accomplish this? Thanks in advance. ...more >>

I'm working on SQL Server 2000 and I would to know the open DB name into a stored procedure. Any suggestion? Fluido...more >>

I have a situation where views and sps cannot compile on an external sql = server. The error message is unrecognized object: FullName. FullName = is user defined function that compiled just fine. In the views and sps I use FullName by using "dbo.FullName". This has = always worked. I di...more >>

I have been reading the newsgroups about ranking queries and trying to read SQL Queries for Mear Mortals to get the basic structure right. Its easy for me to list STATION_NAME and then the SONG_NAME by TIMES_PLAYED DESC, but ONLY the TOP 10 for EACH Station is a twist for me. This is how a p...more >>

Hi, I have such a data Name, Value 1, Value 2 John, 2, 7 Ana, 4, 5 Luke, 1, 20 I would like to receive a report based on Value2-Value1 showing how many records is beetwee 0 and 3, 4 and 6, and more then 7, for eg. col1, col2 'between 0 and 3', 1 'between 4 and 6, 1 'more than 7, 1 ...more >>

Here's the problem. I've got a large database of records (93 million). In trying to update a column, the transaction log filled up the remaining space on the hard drive before completing, thus the query didn't update. According to this knowledge base article: http://support.microsoft.com...more >>

I have some very long running (20 mins) SQL server stored procedures. I would very much like to give the client (a VB program) some idea of how the procedure is getting on - ideally a progress bar. I can easily calculate the percentage complete in the stored procedure, but how do I communicate...more >>

Hi there, I have two tables - "tblTutors" which holds contact details and includes the field "TutorName" (the Primary key) the other table is "tblClasses" which which also has a field called "TutorName". Is it possible for all the names from tblTutors to apear as a drop down list on tblClasses or ...more >>

Hi, I am trying to build a stored procedure that will display data that meets certain conditions, the procedure as it stands below produces the correct results in that it only displays records where the RENTAL and CIL_BIL_FLAG fields are equal to Y, but what I want to do is then dig further a...more >>

I have a very basic level question. I want to make comparison over date portion. Select * from MyTable where MyTable.Datefield='2003-09-21' does not return me correct result because data in MyTable.Datefield has time infomation as well. I have come up with the following SQL Select * from M...more >>

Hello Experts, I come up with my issue again: I want to update one or more columns of a table using the values of "trigger table" and when values are different. I don't think that this is the right way write the statement because when I update only one column of the four, then the "sta...more >>

I have a procedure below that I am using to update / insert based on the @directive parameter passed in, some of the parameters I wish to suppress from the update but when I try to of course the stored procedure tells me that it's expecting that parameter. How can I suppress some of the paramete...more >>