I am going mad with this Query. I need to join 3 Tables. Their Formats are Vouchers [VoucherID] [uniqueidentifier] NOT NULL , [VoucherTypeID] [int] NOT NULL , [VoucherNo] [int] NULL , [VoucherDate] [datetime] NOT NULL , [VoucherNarration] [varchar] (255) CONSTRAINT [PK_Vo...more >>

I am going mad with this Query. I need to join 3 Tables. Their Formats are Vouchers [VoucherID] [uniqueidentifier] NOT NULL , [VoucherTypeID] [int] NOT NULL , [VoucherNo] [int] NULL , [VoucherDate] [datetime] NOT NULL , [VoucherNarration] [varchar] (255) CONSTRAINT [PK_Vouche...more >>

I have a table TABLE1. My company has 2 sites. This table contains employees with the amount of hours they worked on which project at which sites. [CODE] CREATE TABLE #TABLE1 ( Calldate varchar(10) NULL, Employee varchar(10) NULL, Project varchar(10) NULL, Hours decimal(10,4) NULL, Si...more >>

Hi all I have a tables like this.. fCode Number 1 10 2 -3 3 4 1 -2 2 8 I want to separate postivie and negative values for fCode. like this fCode PNum NNum 1 10 -2 2 ...more >>

I have a very simple .Net 1.1 app that I'm writing to upgrade our applications database for a new version. This app has a pretty tight loop where I'm using a SqlDataReader to walk through all records in a fairly large table, manipulate the data, then write one of the columns back out. The p...more >>

How can I do this?? <TableA.tableName> is my psuedo for the TABLE I want to JOIN to but I can't figure out how SELECT TableA.tableName, <TableA.tableName>.someData FROM TableA LEFT OUTER JOIN <Tabl...more >>

I'm planning to keep track of many [types of people], including patients, doctors, and referrals. Additional types will likely be added in the future (possibly "employees" or "staff") I plan to have a People table in which common attributes are stored, and then separate "type-specific" tabl...more >>

Hello, Does anyone know if it is possible to call a web service from a stored procedure? Failing that is it possible to use exterbal object? I'm thinking along the lines of: @obj = CreateObject("xyzzy.f") @obj.j = 1 call @obj Is this wishful thinking ? Many thanks... ...more >>

HI all, Hope I have the correct terminology. I have related tables, with cascading deletes off. In otherwords, you cannot delete a parent row if there are related child row. Now when this happens a correct error is produced. Is there any way to have a single return statement stipulation which c...more >>

Not sure if this is the right forum, so please correct me if I'm in error. I've written a Timeclock application in VB6 for our small chain of retail stores and storing the data in a SQL Server Express database. All is working well, but I want to perform a nighly backup of the database and there...more >>

I'm trying to setup a nightly backup to a remote computer using an SQL Server 2005 Maintenance Plan. It keeps giving an error. I've narrowed the cause down to an execute command... EXECUTE master.dbo.xp_create_subdir N'\\\\10.2.32.15\\Backup_SQL2005\\\\IOC' which is giving the error below...more >>

hi, how to find out which tables used a specific field in one database? Thanks. ...more >>

In sql server 2000 we used sp_databases for users to return a list of databases (the users were not admins and had no special permissions besides had rights to a database or two) but in 2005 the non admins when they execute sp_databases to get a list back, well get nothing back! what has cha...more >>

Here is a watered down DDL of my tables. CREATE TABLE Apps ( AppID smallint, AppName varchar(32), PRIMARY KEY (AppID) ) CREATE TABLE TestCases ( TestCaseID smallint, AppID smallint, TestCase varchar(32), PRIMARY KEY (TestCaseID) ) CREATE TABLE Reports ( ReportID smallint, ...more >>

I want to do something like this Select @MyText = LongDescription From MyTable Where id = inserted.[ID] I get an error message telling me that nText is invalid for a local variable how can I do this? ...more >>

I'm running out of ideas... Given the following example of code: set @partialname = 'u' SELECT col1, col2, col3 FROM tbl1 WHERE col1 LIKE @partialName + '%' ORDER BY col1 Why would two different sql servers, having exactly the same data, give different results? One server returns ...more >>

Hi. I have a table with 2 fields (personalName, companyName). I can insert person Names or company Names and one field tells me about what kind of field is. I want to retrieve the data, but ordered by person and company. Is there a way to do that??? Thanks ...more >>

Hi when I run this query SELECT * FROM OPENROWSET('MSDASQL', 'DRIVER={SQL Server};SERVER=test;UID=sa;PWD=amish;','select name, databasepropertyex(name,''status'') as status from master..sysdatabases') AS a Result is Name Status master 0x4F004E004C0049004E004500 tempdb 0...more >>

Hello, My detail table has a subID column and a CompanyName column. For each subID there needs to be a corresponding CompanyName. subID CompanName a-01 JoneCo a-01 JoneCo a-01 Null b-01 ShmoCo b-01 ShmoCo b-01 Null I need to update this table to replace the Null ...more >>

Hi, ..NET v1.x SP1 VS 2003 SQL Server 2000 SP3 Server 2000, XP, Server 2003 I would like to programmatically execute {possibly many} SQL Server batch scripts. Aka I have many scripts that drop/add stored procedure definitions, alter table definitions & constraints, etc... and I would li...more >>

Is there a system stored procedure or other method for mass exporting all users from one database to another on the same server?...more >>

I have a multi-statement table function that works great in SQL 2000, but in SQL 2005 it will work great for awhile then after you call that function a bunch of times it goes from taking 2 seconds to run to 60 seconds to run with the same exact paramenters. This only happens through ADO.NET ...more >>

Hi, I have a field in he db called LogDate. I have made it a timestamp type. Is there a formula or a default value i can set for this field in the table design view so that everytime a record is added, this field is automaticallly populated with teh current date? Thanks -- pmud...more >>

Can I use an application developed with DAO, ADO or RDO with MSSQL 2000? (I'm migrating the server) Are there compatibility issues? Greetings,...more >>

Hello, I copied a backup file of a production DB on a production server to another location on a Development server. When I try to restore this backup on the Dev server - from Enterprise Manager, I am getting an error message that states I need to use "With Move" to identify a valid locat...more >>

I have an ASP application running in a MS Windows Server 2003 computer joined to a Windows 2000 Active Directory domain. Different users have different roles, and the security in the SQL database is based on Active Directory security groups (SQL server is configured for Windows security and no...more >>

Hello Experts: I am a newbie to T-SQL, and I wonder if this is possible. I have the following possible phone number strings coming in: 1. 8882223333 2. 18882223333 3. +8882223333 4. 888 222 3333 What the expected end result is case #1: 8882223333. Is it efficient for T-SQL to proce...more >>

Hello I have written this stored procedure but I am getting following error. Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near '1'. Server: Msg 50000, Level 16, State 1, Procedure CopyDataArchive, Line 24 Error occured while copying data to December Here is the co...more >>

I guess I'll just post as much about my situation which I feel is pertinent, and hope I get the feedback I'm looking for. I'm not exactly sure what it is I am trying to figure out here, but I think I'll get some good suggestions... I'm working on a program in C# which takes files (either delim...more >>

Hello I have written this stored procedure but I am getting following error. Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near '1'. Server: Msg 50000, Level 16, State 1, Procedure CopyDataArchive, Line 24 Error occured while copying data to December Here is the co...more >>

I am a novice who hasn't mastered the SQL language yet. Your help will be appreciated. I need a query to search a table for records matching one or more of four conditions and then return them with the most matches first. For example, a table with name, birthdate, gender and zip code. S...more >>

Hi, I am looking for a tool to automatically create documentation for stored procedures. I am thinking of something similar to ndoc in .NET, which creates doc from the comments in the stored procedure. Does anybody know of such a tool? Kind regards, Dirk...more >>

Why does the first query work properly and return 1 for the IsMsShipped column when the second and third query do not? use Northwind go SELECT convert(varchar,name) as ObjectName, id, OBJECTPROPERTY(id, N'IsMSShipped') as IsMSShipped FROM sysobjects WHERE name='dt_adduserobject' use Mo...more >>

Hello, I want to compute an integral of values in a table that has 2 columns: Column A: Time-stamp (T) Column B: value (KWH) I need to compute the sum of (KWH*(T(n)-T(n-1))) Could you suggest a way to do it in T-SQL? thanks Ami ...more >>

Hi, This is one for the MVP’s. I am trying to optimize queries but I am finding un-expected results. I am using sql 2000. For example, I have read in several sites that referencing objects with qualified owner names is faster. The truth is that I am experiencing all the contrary. Am I ...more >>

Hi, I have a series of insert statements and if it fails he has to rollback the transaction, do logging and set the current row in status 99. So after each insert statement I check @@ERROR to see if it failed. If the statement failed I will do all the above things. This works fine when m...more >>

In a recent discussion a statement was made that stored procedures perform better than dynamic SQL statements execution. This assertion was challenged and it there has been a remark that in SQL Server 2000 that stored procs are not faster although it is not sure if ths holds for SQL Server 20...more >>

I have only this piece of code and its said that there is mistake, it's excercise, so i dont have full code. SQL wrote: > *declare @UserID uniqueidentifier > SET @UserID = @@ROWCOUNT > SELECT @UserID > > this will throw an error yes, assuming your @UserID is an int this > should work >...more >>

anybody know how many case expression can be in linked server query? when i have more then 10 "case" expression witjh linked server, sql query give this message : Server: Msg 8180, Level 16, State 1, Line 1 Statement(s) could not be prepared. Server: Msg 170, Level 15, State 1, Line 1 Line 8...more >>

I have a 5 million row table that gets truncated and new values get imported. The new values are obtained by values that have changed in other tables. Does anyone know the quickest way this can be acheived. I have tried this took around 34mins insert in attritable (attrivalue,attri_id,...more >>

which data type will be the best for the qty column? - R...more >>

Hello, in our project i have to drop an existing sql server database and immediately create a new (and empty) one with exactly the same name. MSDN says that in case of dropping a database also the physical files on the harddrive are deleted but this is not the case, as sql server complains ...more >>

Hi all, I bumped into this issue: If I run the following statement : "select sum(total) from sales where date>=dateadd(m,-14,getdate())" I get the result in 2-3 seconds, but if I run the following: "select sum(total) from sales where date>='2005-01-01 00:00'" it takes more than 40 seconds....more >>

This is a SqlServer 2000 question. In my Transact SQL-code (actually a procedure called proc2) I'm calling a procedure called proc1. proc1 is returning a resultset, typically 1-3 rows. There is only one column in the resultset. In my T-SQL code in proc2 I want to loop through the resultset retu...more >>

Hi, we have DTS packages which call other DTS packages, sometimes a few levels deep. In our error logs out DTS steps are labelled like "DTSStep_DTSExecutePackageTask_4" Is there a way to give our DTS steps custom labels so that it'll be easier to work out which dts and which step has failed...more >>

Hello I was wondering if you could show me the most basic transaction in code? And rollback if error :) ...more >>

Hello I was told by email someone got an backup error and I gotta reply something to him, what reasons can there be for this error? All services are running and disk isnt full SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan 'Backup All Databases'' (0xE09A5EC5A9A...more >>

Hi, From where can I get SpaceAvailableInMB and KB for a database for SQL 2000 and SQL 2005. sp_spaceused gives the Size of the Database, Data Space Used, Index Space Used. SQL-DMO gives it. Thanks Prasad ...more >>

can i use case in an update statement? UPDATE pn SET pn.payg_tax = pn.witholdingtax, pn.witholdingtax = 0 FROM @PaymentNotification pn where witholdingtax > 0 and ato_payment_id is null UPDATE pn SET pn.witholdingtax = pn.payg_tax, pn.payg_tax = 0 FROM @PaymentNotification pn where...more >>

Hi, Im using ASP and SQL Server. The processed values from ASP are stored in the SQL Server When there is concurrent users say about more than 10 then only 3/5 users only getting updated to SQL Server immediately and remaining users takes approx 30 min to update in the database. here i'm ...more >>