This totally weird, I have no clue why it's happening. As a part of a script which loops through some records and does stuff based on that, I have an update state ment like following: Declare @TMPVAR TABLE (myCol nvarchar(3)) /*Loop starts here, @TMPVAR is populated with a 3 character string...more >>

Hi I have a data field i a table where I want to set all values to null. How? update myTable set myDate = ? from myTable TIRislaa ...more >>

HI all, I have inhereted a table that has a bit datatype with 0 representing Male gender, and 1 Representing female gender Is there a way I can cast/convert this to have it display M ... or F in my select statement Thanks Robert ...more >>

HI all, Is there a way/how is it done, that one can update and or insert values on a cast column. i realise that one has to convert the datatype back again, but Im unsure of the process here Thanks Robert ...more >>

I've got a table I need to loop through comparing each row to some other outside data source. This is a VB app, but I figured the SQL gurus here may have an answer or idea. BV. ...more >>

I would like a UDF to return the value of a different field each time depending on a parameter ie something like Create GetFieldValue(@aFieldName varchar(30), .....) returns varchar(100) begin Declare @ss varchar(100) Declare s cursor FOR Select <<@aFieldName>> From Table Where ...... ...more >>

Hi, I am new to SQL Server. Can anyone please tell me if in SQL Server there is any equivalent of "Dual" table just like we have in Oracle ? Thanks SG ...more >>

I've inherited a crappy data structure on a project, see table below .... CREATE TABLE [REPL_COMPONENT] ( [ReplacementID] [int] IDENTITY (1, 1) NOT NULL , [RepairID] [int] NULL , [Repl1] [int] NULL , [ReplCost1] [int] NULL , [ReplType1] [int] NULL , [Repl2] [int] NULL , [ReplCost2] [int...more >>

Hi everyone, Is there a way to create a user defined function (udf), pass it an xml string, parse the xml string then return the results in a table-type value? example: CREATE FUNCTION MyTable(@xml Varchar(500)) RETURNS @tempTable TABLE (val1 varchar(50), val2 varchar(50)) AS BEGIN DECLA...more >>

I would like to have my stored procedure executed for each item returned by a Select query Ex. Declare @file_id varchar(5) Select @file_id = file_id from GEN where this_value <> '' execute sp_mystored_procedure @file_id go Currently it only works for one record How do I ge...more >>

I would like to have my stored procedure executed for each item returned by a Select query Ex. Declare @file_id varchar(5) Select @file_id = file_id from GEN where this_value <> '' execute sp_mystored_procedure @file_id go Currently it only works for one record How do I ge...more >>

I have a number of tables, each with many fields. I put together information by a view onto the relevant tables and fields. Now I'd like to monitor if data in my view has been updated or inserted. I don't care about the fields not included in the view. If something changes, the ID of the cor...more >>

I have a script that dumps data from several tables into one, where the source tables are not always the same data length. They are all varchar but some are 30 chars, some 255, etc. Below is my insert query, which errors because it won't truncate the 255 character data into 30. Is there a s...more >>

I need to pass to an SQL stored procedure a character parameter that exceeds the nvarchar limit, what would be the best way to accomplish this? Thank you. ...more >>

Using SQL server 2005 express, I am wish to delete data from my table. I cannot use TRUNCATE TABLE as some tables are referenced by a foreign key constraint. So I use DELETE FROM myTable This is fine, except that I would like my identification column, to start again at 1, rather than the las...more >>

Hi - I am writing a C# program using SQL Server. The form I have is collecting search criteria for a database. The main 3 fields are Category, Type, Author. Any combination of the 3 fields can be used. That is, All 3 fields can be used to search on, or just 2 or just 1. If the user selec...more >>

Hi, I have a stored procedure which cycles through a select statement and loads the results into a cursor. It then sends an email off for each result. I was wondering if it was possible to group all the results into one email? My stored procedure is below for reference:- OPEN surveilla...more >>

I'm using Query Analyzer to determine if a user is part of a Role. I'm using: SELECT IS_MEMBER('SomeRole') It returns a 0 if I am connected using my login with Windows Authentication but 1 if using SQL Server authentication with a test login. I'm absolutely sure that both logins are membe...more >>

I am attempting to retrieve specific task related data from a Project server environment for input to an Excel 2003 spreadsheet and have created the following SQL view using: SELECT dbo.MSP_PROJECTS.PROJ_NAME, dbo.MSP_TASKS.TASK_NAME, dbo.MSP_TASKS.TASK_IS_MILESTONE FROM dbo.MS...more >>

I have the following table for example: id colA colB colC 1 test1 test2 15 2 test4 test5 13 3 test6 test7 15 I basically want to return the whole thing, except that there cannot be any duplicate colC entries....the colC corresponding to the id with the max...more >>

When I execute the following Stored Procedure with a parameter (EXEC MyProc 'MyTab'); it creates a table ##MyTempTable. What happened to the parameter @MyTempTable that was passed in the SQL statement? I meant to create the temp table named MyTab as passed to the SP from the statement. CRE...more >>

C# VS 2003 ..Net Framework V1.1 SP1 SQL Server 2000 SP3 Enterprise Library June 2005 I'm working with some code {not of my creation} that performs the following sequence of actions: - Open SqlConnection using SQL authentication with UID & PWD in connect string - BeginTransaction - Ca...more >>

The following statement SHOULD generate a script of the specified stored procedure. Instead, it returns NULL. Please tell me what I'm doign wrong: DECLARE @oServer int DECLARE @method varchar(300) DECLARE @TSQL varchar(4000) DECLARE @ScriptType int EXEC sp_OACreate 'SQLDMO.SQLServer', @oS...more >>

Hi there, We are using SQL server 2005 and I am trying to create an assembly with the permission set EXTERNAL_ACCESS as below CREATE ASSEMBLY Permissions FROM 'C:\SQL Labs\User Projects\Permissions\bin\Permissions.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS But it gives me the below err...more >>

Hi, I am trying to write a C# user defined function in SQL Server 2005. From there, I have to access a dll that was written in unmanaged C++. I do not think that I can use dllimport since I need to create objects from that dll. So I created a C# wrapper for the work that I need to do with...more >>

So, I've read some past articles that indciate there's no easy way of knowing within a CLR trigger what table the trigger is acting against?! I read there are 2 possible solutions: 1) Add a String argument to the CLR Trigger to pass the table name. Then Create a user defined function for...more >>

Hi all, I am updating an application that has a routine to get the next sequential number. This number is used as a client number, so everytime that a client is created this routine gets the next available number. The algorithm that gets the client number goes some thing like: get n...more >>

I have a simple trigger that writes out records to another table when a user deletes a record. I am looking for a way to capture the Network Address value that is associated with the process. Is that possible ? Here is the trigger. I want to insert the Network Address value into the Lastuse...more >>

Hello, Has anyone run into a situation where using SET FORCEPLAN ON changes the results set of a stored procedure? I've got a large, complicated procedure that spits out a large hierarchical table of aggregated values. I noticed that some of the data was coming out incorrectly, even though ...more >>

I know there must be a better way..... What I have done in the past is to make each column a subselect ,ca.ondate = (select max(ondate) from ..... ,cs.ondate = (select max(ondate from.... This appears to be a poor way and performance would suffer. So, onto a new way, but it's not workin...more >>

I have a stored procedure this is called multiple times from within a transaction. The sp inserts rows in a table and also reads data from the same table. I need the reads to see data that has been written to the table within the same transaction. I've tried the SET TRANSACTION ISOLATION LEVEL...more >>

Where in the system tables I can find the text description of the selected column? Thanks in advance, *** Sent via Developersdex http://www.developersdex.com ***...more >>

Hi, I am using SQLServer 2000 I currently have a table with just one field in it of type (ntext), the reason for this is there is a very large amount of data in it of an XML format. The data looks something like below althought this is only a small sample there is actually a lot more i...more >>

Hello All, I am in the midst of designing a database, and I have a very general (pseudo-newbie) question. Should all tables have a Primary Key defined? As I understand it, there are two reasons to have a primary key: - It is used in conjunction with a Foreign Key on a table, and - It ...more >>

I have a Stored Procedure serving an web application that returns data from a number of tables. This dat resides on three seperate tables, two of which I join in a single select statement, the third I use to lookup data dependent on some data retrieved from the original select statement. O...more >>

In other words, from whenever the calendar begins, a serial number representing the day. if 1/1/1904 is 1, then 1/1/1905 is 366, and so on. is there a function in tsql to get this number from a datetime field value? ...more >>

Hi Can anybody explain why this select returns different results in SQL 200 and SQL 2005? ========= if exists (select * from sysobjects where name = 'test') begin drop table test end GO if exists(select * from sysobjects where name = 'vw') begin drop view vw end GO create table t...more >>

I got a table SITUATION with a userID, a date and a situation. Unique number UserID SitDate Situation Int I Need to get the last situation of the UserID I'm stuck there with that query and it come out with the last for each situation I only need the last situation. Query: SELECT DI...more >>

I've got the following code (C# code) but when I execute the Create Trigger statement I get the error "Could not find Type 'InheritanceTriggers' in assembly 'CLRTriggers' I believe I'm satisfying all the rules: the class is public, the method is public, and the method is static. Anythou...more >>

Hello, This request return an error: SELECT MAX(date_loading), n_station FROM Load_station WHERE n_load=238 AND date_unloading IS NULL In this request, I would like to know the value of the column n_station of the record with the biggest date_loading and where n_load=238 and date_unloa...more >>

I have a C# windows service that uses MSMQ, DTC Transactions, SQL server 2005 and Notification services. I am doing the following in the service: OnStart Create the queue set the Peek Complete event call Begin Peek PeekComplete WaitHandle[] waitHandle = new WaitHandle[] { new...more >>

Can anyone please give me the difference between a clustered index and non-clustered index? Thanks!...more >>

Hello all, I have an application which was written in VB6. I used DSNless connection for the odbc connection to access sql 8.0. The application works fine on one machine and when I try to run the appl. on another machine I got this error. Any idea why?? Here is my connection strin...more >>

I have a program that inserts a row to a parent table and before it commits then calls another program to insert rows to the child table. This is causing a deadlock. When I looked at it, the first program has an X lock on the primary key of the parent table and the second program is trying to ge...more >>

Hi, Is anybody aware of any problems with inserting unicode chars via osql. I've got some unicode text and when it gets inserted it inserts not the character I am expecting. It works fine from query analyser etc. My collation is set to the generic collation no problems there. Thanks. Pan...more >>

Hi guys n gals, I'll confess now that I only know enough sql to get me by when web developing, so I really need a hand with this one... I've basically setup a Scheduling system within our office where people chuck in projects and they get scheduled... Having been asked to setup some recurri...more >>

I've automated defragging my indexes with a stored procedure, but can't capture the results of the defrag into a table. How can I go about this? I've tried: Declare @sql varchar(1000) Select @sql = 'DBCC INDEXDEFRAG(''SOSManager'',''tmpKeys'',''PK_tmpKeys'')' Insert Into maint_IXDEFRAG (Pag...more >>

Hi, I would like to know about the internal information regarding sql backup operation. I want to monitor the Backup operation in all version of sql server. I am able to get all the information from the msdb database but i'll not find any information regarding backup fails. Suppose i wan...more >>

hello, i have 2 tables which have 2 fields. Common in the 2 tables is the id, the other field is a varchar(256) example Table1 Id UserInfo 1 Pc A.Julien-3400 2 Soft V.Noris-2800 3 Liz Barbara -2345 Table2 Id Username 1 Julien 2 Jack 3 Barbara I want to get the id value where...more >>

I have a 540 million row table; in it, there is a column whose datatype that I am attempting to alter using the following command: alter table tb_History alter column browser INT NULL go In running the above stmt for the first time, it filled up the tran log, so I expanded it to over 130...more >>

Dear All, I have a requirement in which I have to display a row from one table & the corresponding row from the another table. e.g. say there are 2 tables T1 & T2. Suppose there is a record in T1 say R1 & the corresponding record in T2 as R1' then the display would come as R1 /* ...more >>

Hello, 1) Does SQL Server 2005 fully support UTF-16 character types, rather than UCS-2? According to the following link, it does not: http://msdn2.microsoft.com/en-us/library/ms186939.aspx Our concern regarding UCS-2 vs. UTF-16 support relates to support for surrogate pairs. As I und...more >>

I'm using sql2000 and Access 2002. I have a stored procedure that sends an e-mail when a command button is clicked. I have an Access Report "rptMaintWo" that I want to send as an attachment. How would I add the attachement to the e-mail? Thanks for the help, Paul...more >>

Hi all, I am having trouble trying to get an efficient way of checking if a value pair exists in a table, and if not inserting the value pair and returning the new id in the identity column. The old method I have used has been fine for some time but the table has grown to a very large size an...more >>

Hi, I have few questions about table variable: 1. Which is better, using table variable or sub-query select statement? For example: Select * from table1 where [col1] in (select [col1] from table2 where [col1]='david' or [col1]='harry') Or: Declare tableVar tabl...more >>

Hi If i use select * from table...i get the following 10 Energy Energy Energy Equipment & Services Oil & Gas Drilling 10101010 10 Energy Energy Energy Equipment & Services Oil & Gas Equipment & Svcs 10101020 10 Energy Energy Oil ...more >>

Hi, I have 2 tables ''Data' and 'LatestData' with same fields: - ID, - TruckNo, - DateTime, - Comment. My application adds data to the Data table. I would like to have a trigger which automatically updates or adds (if not exists) newly added records into LatestData table. This trigger ...more >>

Hi, I have some questions about table variables: 1. What is better to use table variable or sub-query? For example: select * from table1 where [col1] in (select [col1] from table2 where [col1]='david' or [col1]='harry') or: declare tabale tableVar ([col1] varchar(100)) insert tableV...more >>

I am currently creating a DTS that will carry out a select from a table and insert the results into a spreadsheet. Each sheet has a formula at the bottom that calculates the sum of column A. In the DTS the first step that I do is to remove all data that was inserted into the sheet last t...more >>

I've got an Oracle table with 140 columns. I'd like to know which are the most perfect and optimizable mappings between Oracle and Sql2000k DW001H_FEEFEC NUMBER(8) NULL, DW001H_INSEGRIE NUMBER(1) NULL, DW001H_INOPVENT NUMBER(1) ...more >>

Hi guys Just setup a new system running Win2k3, SQL Server 2005, etc (all msdn). I've got a web app (.net 2) which is running perfectly well, most of the time, and then there's one page where I'm getting the following error... Server Error in '/' Application. A connection was successfully...more >>

Hi guys I have a table containing serial numbers, some of which occur more than once. What I want to do is to only count serial number that only occur more than once. How would I go about doing this? Many thanks ...more >>

ive been trying very unsucessfully to try and get the last date of the previous month, ie if i run the query today i want the result to be 31/01/2006 ...more >>

Dear all, I've got a table with a datetime field and I would like to delete all those rows which are >= "2006-02-01 09:54:22.863" How do I such thing? Any help would be very appreciated. Thanks a lot, ...more >>

Hi folks, this is not a technical problem at all. I just wonder if you could recommend a truly great SQL/Transact-SQL book to read I'm not interested in Henderson (I have it already) , or tips and tricks, I want a solid well written intro to have SQL works from the ground up, not just a list...more >>

Hi, we sometimes have statements which seem to block the whole server. now i use this to get information of running processes: SELECT spid, kpid, status, hostname , USER_NAME(uid) , program_name , DB_NAME(dbid) , memusage,* FROM master.dbo.sys...more >>

Hi, I need to get information from a backup file. For example the logical name of database file (data/log) that exist in the backup set. I know that RESTORE FILELISTONLY and RESTORE HEADERONLY provide this information, but I need to get them in variables, or something that makes me able to u...more >>

this procedure below is for use in a web application, I could not figure out how to get the bottom table itemShares to join with the top select union portion. Is there a more efficient/faster/viable way to do this? ALTER PROCEDURE dbo.itemsharedata ( @uid int, @tid int ) AS c...more >>

Does anyone know of any code to produce a crosstab table or temp table? I found several examples of stored procs to do this, however they just produce results. I need to produce a table to use to update another table. Thanks much, Marc Miller ...more >>

Hi all, Please have a look at a script below: USE tempdb DECLARE @t TABLE (c1 uniqueidentifier, c2 uniqueidentifier) INSERT @t (c1, c2) SELECT y.id, NULL FROM ( SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 ) x (id) CROSS JOIN ( SELECT NEWID() UN...more >>

please help me solve this problem i have with my database! i have 2 tables : table A --------- id name table B --------- id tablea_id title now, what i need is this: return ALL joined rows from table B and A but it should only return maximum 2 rows with the same tablea_id. ...more >>

Hi guys, please create a table in tempdb running following USE tempdb CREATE TABLE delete_me (c1 int, c2 int ) INSERT delete_me (c1, c2) SELECT 1, 1 UNION SELECT 2, 2 UNION SELECT 3, 3 UNION SELECT 4, 4 Then running the script below you can get (I do) the error message : Server: Msg 2...more >>

Hi, When I want to restore my database, there might be some users browsing the web site and therefore they have connection to SQL Server. I need a quick way to kill all of users and perform the restoration. I think I must use KILL command but instead of writing an script and a cursor, I thoug...more >>

Hi all. Is there a way I can have my resultset saved to a file directly from a SP using SQL2000. E.g. use northwind CREATE PROCEDURE ToTxt AS SELECT * FROM Customers /* I want something like*/ /* SELECT * FROM Customers TO c:\Cust.txt */ /* Then I can run the following to creat...more >>