I have a 2 tables (emp & NoBonus). I would like to update the bonus column of emp (SET bonus = 5000) such that emp.empid <> Nobonus.empID, how can I do this? Basically, all empId's listed under NoBonus should not be updated emp ---- empid empName dept bonus NOBonus ---------- emp...more >>

Hi Guys/Girls... I'm using SQL 2005 integration services to copy data from an existing access database. The access datbase contains a field, with the type of NTEXT. I am trying to write the corresponding data to a table with a nvarchar(8000) field. I'm also trying to use the 'Data Conver...more >>

I'm getting "Invalid Column" error with below code. Can anyone Help? CODE: USE [Northwind] GO declare @SQL varchar(1000), @debug int declare @sTable Char(40), @sField Char(40), @sField2 Char(40), @employeeID int set @sTable="Orders" set @sField="OrderDate" set @sField2="emp...more >>

Instead of having many columns in a single table I have two separate tables: a base entity table and an attributes table. For example, I have a car table that has an ID for each car and an attributes table that contains discriptive information about the car. The attribtributes table has a ...more >>

Is there an easy way to evaluate a string of SQL syntax to execute in line like so: Assuming @A = '3,4,5,6,7,8,9,10' SELECT a FROM b WHERE c IN (EVAL(@A)) I know you can do this: EXEC ('SELECT a FROM b WHERE c IN (' + @A + ')') but this is not a good solution when you have an SP tha...more >>

How does sql server 2000 distinguish between explicit transactions for a user who is logged into the server with the same userid more than once? What I'm trying to find out is if a user is logged into the server twice and begins 2 distinct non-nested transactions, how does sql server distingu...more >>

Since "The Release" 4 weeks ago I've been monitoring the various Visual Studio/.NET NGs to get a sampling of the issues people are running into. In this SQL Server NG I don't see many questions at all about SQL Server 2005 - issues, migration, etc... very little of anything 2005-specific... wh...more >>

I am trying to generate update statements for my table, this way I can send the script to the client. for e.g. I have 2 tables a) employee with the columns empid,name,dept,phone b) lock with 1 column empid I have some data in lock and employee tables employee ----------- 1 John 1 ...more >>

I've a problem in Job on SQL Server 2000... I need a SQL Job can be executed from query by users (more than one user) who doesn't have system administrator privileges.. At the moment it can't be done cause of that limited privilege.. Please, help me... Your solution is very expected Thank...more >>

I am hoping someone can help me with an example of a user function that will return just the letters of a string. For example, sending 1RMB23 will return RMB. Thank you....more >>

Hi All, I want that by default SQL Server automatically starts a transaction for me and I must commit every action manualy to really change something in the database. I am using SQL Server 2000. Regards Chakravarti Mukesh ...more >>

I have a script that runs fine in SQL Query Analyzer but has a syntax error using osql. It restores several databases and log files. One of the databases has a space in the database name. Similar to RESTORE DATABASE "XXXX XXXX" FROM DISK (BLAH). When run using osql all the databases restore co...more >>

I' have two Questions.. 1- Am I have to add inex to my foringnKey column say clustered index to optimize performace or it's not necessary and forign key is indexed by default?? 2- I have Table with ID Primarykey.. I have another table With ParentID,ChildID both are referanced to Table1.ID....more >>

I have a situation where I need to 1. Backup a template database 2. Create a brand new database from that backup with new unique logical filenames Example: Existing Database: TestExistingDatabase Logical Files TestExistingDatabase.mdf TestExistingDatabase_log.ldf ------- ...more >>

I need to extract data from a csv file, validate it, and populate other tables with that data for a multi user web application. I am importing a csv file via linked servers as follows: EXEC('SELECT * into ##temptbl FROM '+@linked_server + '...['+@file + '#' + @extension + ']') Once data...more >>

I have 2 tables joined in a query. One is a simple static list of available processes, the other is a dynamic dataset of process audit failures. Currently the query counts the number of process audit failures per process per day, and omits any process with zero failures. I included the proce...more >>

I have a table that currently contains 22 columns that contain redundant data. We need to normalize this table by moving the 22 columns to a new table and put a foreign key in the main table. Ex: Table structure before normalization: Table1(Pk1, Pk2, F1, F2, F2...F22, V1, V2) Table str...more >>

I am running a query that will return 1 field from 1 row. What is the syntax to assign that to a variable. Samplecode: Declare @DataFilePath varchar(200) Declare @DBName varchar(200) Set @DBName = 'TestDB' @DataFilePath = select FileName From master.dbo.sysaltfiles WHERE name = ...more >>

The update trigger included in the sample below does not work and I can't figure out why. It's almost a straight copy from stuff explained for w2k in an article by Itzik Ben-Gan on maintaining hierarchies. I scripted a sample table definitions and the two triggers. The Insert one seems to work...more >>

Hi all, this may seem like a dumb question, but I am trying to understand the concepts and benefits of indexes. Lets say I have a table with about a dozen indexes defined on different columns. When I execute a query how does the system determine which index to use? What if it uses the wrong on...more >>

Hi, I am trying to troubleshoot an issue that came up suddenly on a SQL Server 7 that I support. There is a sp that runs every five minutes and one of the functions in the sp uses the sp_oaCreate to create and object which returns some free disk space information. This sp has been working fi...more >>

How do you convert the following Access query to SQL Server ? Format([tblA].[PRICE],"0.0000") AS Price Thanks. ...more >>

Need to Run a search on SDESC from Inventory Table using all the short descriptions related to OrderDetailItems for a certain Customer Return search results of I.IDESC1, OD.STOCKNUMBER, OD.QTY, O.CARTNAME Tables: 1) Inventory as I StockNumber | IDESC1 | SDESC 2) OrderDetail as OD ...more >>

Hi, When I want to deploy CLR assemblies on a SL Server Production Server. What is the best pratice to define where to store theses assemblies? In the case of deploying with VS 2005, where the tool put the assemblies on the server? Thanks in adavance, Laurent ...more >>

We have a table with an update trigger that we seem to be having unintended deadlock issues on. The trigger, among other things, updates the same row that was updated to spawn the trigger. In our examples where we are encountering the deadlocks we are always doing single row updates on a uniqu...more >>

Hi, All There are two tables A and B. the structure like these. Table A ID , Name, etc... Table B ID,Title,ForigenKeyToA, etc... And one person may have multi-titles, if I join A and B, I will get A.ID, A.Name,B.title 1, John, 'AAA' 1, John,'BBB', 2, Bill, 'AAA', 2, Bill, 'CCC' 3, Jo...more >>

Hi, I would like to do a subsquery to compute my previous year amount. i.e Pol No UW_Year Renewal Premium Expiring Premium 0001 2005 1000 900 (this amount should get from year 2004) SELECT Policy_Number, UW_Year, RENEWAL_P...more >>

Can anyone kindly let me know what the N'' of the statements below mean - N'NULL' and N'AdventureWorks2000' EXEC [dbo].[sp_addtype] N'AccountNumber', N'nvarchar (15)', N'NULL' IF EXISTS (SELECT [name] FROM [master].[dbo].[sysdatabases] WHERE [name] = N'AdventureWorks2000') DROP DATA...more >>

I need to be able to consider time-zones, including daylight savings, within stored procedures. Can anyone point me to a resource/code base which will either allow me to procure or build my own code? I need to do things like determine local time based on a customer's state, zip code, city, o...more >>

I want to create a copy of a table with a new name, what is the best way to do this. I did notice I could copy a table, but there is no paste !?! ...more >>

I need to check the current value of xp_cmdshell in SQL server, how do I get that? Thanks, RSH ...more >>

I am just starting to look into CLR integration with SQL Server 2005. What I would like to do is: Pass a string array or object array parameter into a CLR stored procedure Interate through the elements and for each element update some tables or call a TSQL stored procedure I have compil...more >>

Is there a SQL Server Transact SQL statement that is simular to Oracle's describe command DESC? DESC [table name] I am interested in a column's name and definition. Ultimately to copy from query analyzer and into a document. Thanks ...more >>

Hi, this one has me stumped. Any help would be appreciated. Client 1 has the same postal address as client 3 Client 2 has the same email address as client 3 So even though clients 1 and 2 have different postal and email addresses we know they are dupes of each other. So in my table clie...more >>

Here is my problem: Table Event (ID,EventID,PersonID,... , Date) Table GPS (ID,PersonID, x, y,... , Date) For Table Event, say I have eventID=1, personID=1 was logged at Date='12/05/2005 10:45:33' Table GPS randomly records where PersonID is on 12/05/2005 at these times: 10:44:21 10:44...more >>

Hi, I want two of my insert statements work in a transaction. If the first one encountered an error, all of it can be rolled back. I wrote my code like this: -------------------- USE TempDB GO CREATE TABLE T1( C1 INT CHECK (C1<>1) ) GO BEGIN TRAN INSERT T1 VALUES(1) INSERT T1 VALU...more >>

Hi, I want two of my insert statements work in a transaction. If the first one encountered an error, all of it can be rolled back. I wrote my code like this: -------------------- USE TempDB GO CREATE TABLE T1( C1 INT CHECK (C1<>1) ) GO BEGIN TRAN INSERT T1 VALUES(1) INSERT T1 VALU...more >>

hi guys i need the sql server Service manager in yukon. any ideas why it is not around thanks. -- thanks, ------------------------------------ Jose de Jesus Jr. Mcp,Mcdba Data Architect Sykes Asia (Manila philippines) MCP #2324787...more >>

Hey everyone... Im a new programming to SQL Server (MSDE actually). I have the statement INSERT INTO kwh ('tstamp','value') VALUES (CONVERT(DATETIME, '2005-10-19 00:00:00', 102), '1890') I have an ODBC connection open and I am trying to execute this statement. I keep on getting an error a...more >>

Hi, I want to know about the unused indexes. Is sql server provide any direct api or services to give information about the unused indexes. My problem is to find indexes which are not used in certain period of time and if yes then delete that indexes. Please help me by providing some sugge...more >>

Considering the following which is most efficient? and why? option 1: set @var = (select aFld from aTable) select aFld from bTable where bFld = @var option 2: select aFld from bTable where bFld = (select aFld from aTable) ...more >>

Hi Hoping someone may know this. I am connecting to sql server via OleDbConnection from C#. Is the instance of @@ERROR that is set in db per connection? What I was going to do was, after connecting and opening a data reader on one connection, I wanted to open a 2nd connection and read @@ERROR. ...more >>

hi, when i run my job from the Server Agent JOB - it failes , with message JOB RUN: 'Job_Run_Sales_Cube_Process' was run on 06/12/2005 at 11:18:01 DURATION: 0 hours, 18 minutes, 4 seconds STATUS: Failed MESSAGES: The job failed. The Job was invoked by User MYDOM\gilon. The last step to...more >>

Hi! Guys i have problem with order by clause. i have table some T1 with two columns say Itemname and Status and data like this ItemName Status T1 G T2 D T3 US T4 S T5 G T6 NULL T7 ...more >>

Hi I have a query as below: select [transaction].[M_GIFTCARDNO], [T_GIFTCARDS].[M_SERIALNO], [T_GIFTCARDS].[M_CARDNUMBER], [transaction].[req_login_time] from [T_GIFTCARDS], [transaction] where req_login_time between '5 October,2005' and '6 December, 2005' and [T_GIFTCARDS].[M_CARDNUMBER]...more >>

Hi, How to find out the total number of lines in a stored proc programmatically? Regards Pradeep...more >>

can anyone help... i have two databases on the server I wish to pull data from in a query, what command do I use? dbo.databasename.table.column??? - is that right much appreciated Ivo...more >>

Hi NG, I have a query that takes pretty long and changes rarely. It would be fine to cache the query result, so I could get the result in the moment, without calculating 20 minutes. Is this possible in SQL Server 2005? If yes: what if the base data changes? Thank you very much Rudi ...more >>

Hi All, I have created a well structured cross relationship db.. To extract all the details out is not a problem but i am trying to now include a simple search to filter results.. Where is the best place to build the query, within my .Net web app or within a sp itself.. My basic setup is a ...more >>

Dear all, I need to do some analysis on particular records. For that I have one Table named EVENT in which I have a field named CODE. From that field value I can get many entries with same CODE value. Is there a way to extract the value of CODE field which occurs more often in a table ? ...more >>