I have two tables: one that is a temp table with a single day's data, one that contains all the records for all days. The design of these tables is identical, other than the table names. I want to use DTS to insert any records from the temp table into the final table if they don't already exis...more >>

A simple query select * from mytable with(nolock) when runing on my server,it is ok,but when running on my customers serever, pump an error: Error: 1 [Microsoft][ODBC SQL Server Driver][SQL Server]Conflicting locking hints specified feel very strange, my SQL server version: 2005 and W...more >>

I want to create a trigger that updates the column, 'TimePrinted', in table, 'DistributionPrintInstance', with column, 'UpdateDate', in table 'ReportInstance' -- The connection between 'TimePrinted' and 'UpdateDate' is established through INNER JOIN of several tables on the primary key columns o...more >>

A table has 4 columns - EID (IDENTITY int), FName (varchar(50)), LName (varchar(50)) & Salary (int). To find out the 4th highest salary, this is the query I framed: SELECT TOP 1 EID, FName, LName, Salary FROM ( SELECT TOP 4 EID, FName, LName, Salary FROM EmpTable ORDER BY Salary DESC ) E O...more >>

Hi, We have an app that's constantly reporting deadlocks on 2 tables. Table A is updated/inserted/deleted constantly and there are I/U/D triggers that essentially update Table B. These are the things we have tried so far 1) Checked the order of the tables involved in transactions. COuldn't...more >>

I'm totally new to this concept and I've read how to assign a picture to a field in my database. I created a field called MyPicture with data type "image" How do I assign a picture to this field? "C:\MyPic.jpg" I've read a bit about pointers and i'm a little lost... can you give me ...more >>

I'm wondering why we usually seed IDENTITY columns with 1 instead of the smallest negative number for the type? CREATE TABLE t ( id smallint NOT NULL IDENTITY(-1,1) PRIMARY KEY CLUSTERED, ch char(1) ) INSERT t VALUES ('a') -- id = -1 = 0xFFFF INSERT t VALUES ('b') -- id = 0 = 0x...more >>

I've recently upgraded a 2000 server to 2005. A view that calls a function with a CASE statement in it now returns this error: Msg 8621, Level 17, State 2, Line 1 The query processor ran out of stack space during query optimization. Please simplify the query. The part of the view that is f...more >>

I am trying to load a comma-delimited file from the drive C:\ to SQL Serve r2005 SP2. I looked at the BCP utility and tried to build up the script. I ran the following command in Query Analyzer bcp DB_NAME.schema_name.ACCT_Table in c:\acct1.DAT -c -t , /S ServerName -T. I got the following e...more >>

As per BOL, a subquery is defined as follows (under the topic Subquery Fundamentals): ------------------------------------------------------------- A subquery is a SELECT query that returns a single value and is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another su...more >>

In the following query, are "Col1", "Col2" & "Col3" identifiers as well as expressions? SELECT Col1, Col2, Col3 FROM MyTable If so, then what's the difference between identifiers & expressions? Thanks, Ron ...more >>

I've got this simple SSRS report I need to develop. My dataset source is currently a Oracle 9i table. I'd like to join in some data from a SQL Server 2005 database. Any creative way to do this within SSRS? from SQL Server? from Oracle? I know I can have two datasets in SSRS. Thanks for...more >>

Hi, I have an User table has fields of userID (primary), lastname and firstname, another table DeleteUser has fields of userID and deleteflag. Now I want to make a query to display an user info from User table with userID 1 that this userID is not in the DeleteUser table. Any ideas how ...more >>

How does one determine if a database is read intensive or write intensive?...more >>

Hi, We are developing a SQL server based asp.net application. As part of requirement we should allow users import/export some relational data through web user interface. We are investigation which file format would be the most efficient format to import export data. So far we came up wit...more >>

I've restored a database, and can't connect to it as myself. It turns out that the database is "DBO Use Only". "DBO Use Only" is displayed near the database name in the Enterprise Manager. How can I remove "DBO Use Only" limitation? Thanks! ...more >>

Hi, i have two sets of data: Employees query fields: EmployeeID Name PayRate ProjectID_FK and Hours query fields: EmployeeID HrsWorked What i wish to do is to join the two sets of data, however some records will not exist in both tables. For example, i will perform a SELECT * FR...more >>

Hi, I have a new request from my manager and really don't know how to go about getting the job done, Im kind of a newbie. What I need to do is take information from one system (which I get in a CSV) and create a new csv that meets the formatting requirements of another system so it can be i...more >>

The place where I'm working now (SS2000SP4) has the audit mode turned on so that it logs every Sql Server connection into the Sql Server log. One thing I noticed is that the Agent - I'm assuming it's the Agent because it is using the domain Sql Server service account - opens a connection ever...more >>

Hi I have been playing around with a simple query along the lines of; select category, count(id), sum(cost) from items where date >= '01-jan-2006' and date < '01-jan-2007 group by category This query take about 30 seconds to complete and according to query analyser, only uses the index...more >>

I need to update table A with an average amount from table B based on matching keys on both tables. The query below updated all records in table A with the avrage of all records in table B, which is not what I wanted. I want only the average of records match the criteria stated on the ON clau...more >>

hey all, is it at all possible that the alias contain a <br /> tag? select getdate() as 'row1<br/>row2' this is another one where i hope the answer is no. thanks, rodchar...more >>

Hello, I try to find the best solution for a database design. I have the following case: 2 or more items (let's go with 2 for now), defined each in its own table (Item1, Item2). The Item2 has a foreign key referencing Item1 primary key ("master-child" design with enforced cascade). Both...more >>

Hi! I have a table like this: column1 column2 father child1 father child2 mother child1 father child3 aunt child4 So, child1 has two parents and child2 has only one. This probably happens somewhat frequently: what I w...more >>

Hi all, I am trying to export data from CSV file to sql server 2000 datatable using sqlbulkcopy I am getting follwoing error 'Received invalid column length from bcp client.' I searched on net but everywhere i got help with respect to sql server 2005 and not with respect to sql server ...more >>

SQL 2005 Enterprise, 9.00.3042.00 I'm trying to change my log file option from "restricted" to "unrestricted". Whether I modify this option through Management Studio Database Properties or ALTER DATABASE statement, it switches back to "restricted" immediately. Please help... Leon...more >>

I need to be able to put Russian characters into a text column. Is this possible? If so, what do I need to do? Stephanie...more >>

hey all, are there any major drawbacks to using sp_executesql? thanks, rodchar...more >>

I created a temp table to try increase the speed, but I think something is wrong. I created the following procedure: ALTER PROCEDURE P3_Alloc AS /* SET NOCOUNT ON */ DECLARE @STR INT SET @STR=dbo.iCrint() --xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx IF @STR=1 --Delete ...more >>

This is a smple data in table1 sector RefDate price pharm 22 august 2007 100.21 gap 15 august 2007 10.32 pharm 21 august 2007 99.99 pharm 9 oct 2007 100.99 pharm 2 oct 2007 98.34 pharm 8 oct 2007 96.34 .... I would like to have the result as follows: sector Re...more >>

I have following stored procedure Create PROCEDURE [dbo].[SPName] ( @xmlText ntext ) AS --Set NOCOUNT ON EXEC sp_xml_preparedocument @hDoc OUTPUT, @xmlText begin Insert into Table1( Field1, Field2) Select Field1, Field2 From OpenXML(@hDoc, '/Node', 2) With (Field1 varchar...more >>

Hi, I am trying to include an order by clause in the below code. When i add in an order clause i get the following message The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified. I do...more >>

Hi I'm just moving across to SQL Server 2005 Express version from SQL Server 2000. I've written an app which creates temporary tables on the fly. When I was debugging my app (with SQL Server 2000), I used to be able to see and check the contents of the tables. Now I cannot see anything i...more >>

I'm working with a developer who is calling a stored procedure in a C# web app. He is getting an error stating can't convert the varchar value to an int. When profiling the call I see that when SQL is parsing out the parameters it is setting all the @p variables to int even though some of ...more >>

Thanks ...more >>

We have set up a SQL Server 2005 for developer who will use .NET VB to create application. We would like to know is it necessary to give him access to SQL Server 2005 Client or we should create the database according to his requirement and just tell him the Server Name / Instance Name / use...more >>

if possible could i create a view that EXECs other stored procedures in order to create a table of rowcounts ? (so i have a lookup table of current rowcounts in particular SPs) eg exec SP_1 SELECT SP_1'= @@Rowcount final results would be something like SP_1 5 SP_2 10 SP_3 16 then i ...more >>

Hi we are developing an pplication that is using the value returned from Select @@rowcount to perform other actions. If 0 no record returns. If 1 return a record. At present all tables have no Triggers, but bearing in mind the history of the company and throughput of staff, this may ch...more >>

Hi we are developing an application that is using the value returned from Select @@rowcount to perform other actions. Such as If 0 no record returns. If 1 return a record. At present all tables have no Triggers, but bearing in mind the history of the company and throughput of s...more >>

some store procedures are running when i exec sp_who2, the status of that sp_id is still "sleeping" what's wrong of the sql server 2k? ...more >>