Hi all If I execute the query---" select * from categories for xml auto" It will return a column name with XMLsomething and the value as xml representation of the above executed result. I have to insert the xml value into another table which has only one column of datatype varchar(8000...more >>

how i can get SPID of my connection? ...more >>

Hi, What is the simplest way to prevent updating existing rows in particular table, but preserve possibility to insert and delete rows? Nick...more >>

Here is some sample data from Table A KY ID INITIAL NAME SEX HIREDATE STRTDATE STA ACT REA ENDDATE 100 45 AA SMITH M 19680513 19680513 20030115 101 45 AA SMITH M 19680513 20030115 A 20040328 102 45 AA SMITH M 19...more >>

I have a table to which I have added a calculated field. It is calculated by a function in the database, and is useful in GROUP BY clauses in several queries I want to run. That part works fine. Unfortunately, since adding that field, whenever I try to change any value in any field, the cha...more >>

Hi, I have an integer field which is neither a key or an autoincremented column. I would like to write a stored procedure to return the first available value for that column. No assumptions can be made on the order the values have been taken. I.e.: I could have this situation: 000 001 0...more >>

Hi all, In my Transaction table , there is a column : 'ApprovalDate' DATETIME NULLable. The problem is : in my front End application there is requirement to display the records sorted by ApprovalDate. So I create Index on ApprovalDate. For this requirement, should I put ApprovalDate column...more >>

I have a Store Procedure that returns records based on parameters that I send it (date range). Then I also have two different views that return records. How do I create a new Stored Procedure that puts information from all three objects into one recordset? Example sp_EmployeeHours is a s...more >>

I need to do a very large update. I do not want to overburden my internet connection with a 50,000 + record replication. I am using merge replication and usally only replicate 100 to 1200 items at once. So I need to do a mass data update now. It does not need to all be done at once so I would lik...more >>

Hi, Let's say I have a table with fields date, credit and debit. User is entering date range. I need to calculate balance amount on every day(balance is not stored in the table, it needs to be calculated). Output should be: date net change balance =====================================...more >>

I have select which returns: SELECT hour,adv_ID, sum(quantity)as quantity,sum(sale)as sale,sum(procent)/count(*) as procent FROM table GROUP by hour,adv_ID hour adv_id quantity sale procent ------------------------------------------------ 8 1 4 ...more >>

Hi, I want to use the format hh:mm:ss in for field "myTime" which has got datatype "DateTime". If I change the date to my preferred format manually in the database, I get the correct results back by doing a SELECT * from myTable ORDER BY DATE, myTime DESC Now I want to add new records...more >>

Hi, Env: SQL Server 2000 We need to store application configuration settings in SQL. The settings are initially of three types - boolean, integer and string. e.g. stuff like: Number of threads: 25 Export filename: MyExports Export to XML: True I'm thinking that the va...more >>

I need to pass in a [database...table] name to a stored procedure How can I use a variable name as the table name used by SQL Statements? I tried this and it as a model for what I'm wanting and it doesn't work: DECLARE @DBName1 varchar(50) SET @DBName1 = 'SomeDatabaseName.DBO.SomeTableName...more >>

Hi, I would like to get the highest Total Calls for a day within the 24 hours time frame. I need to accomplish this without a temp file or a view. Could somebody help me out? I did get the desired solution by creating a view or temp file, but I need the same result without creating a few or ...more >>

I'm working with data from two databases in a lengthy stored procedure. Because two databases are involved the inclusion of the database name/owner is required in the WHERE clauses. Example: SELECT * FROM SomeDatabaseName.DBO.SomeTableName I understand an arbitrary alias can be specified - ...more >>

I have a CREATE TABLE dbo.Customer ( idCust INTEGER IDENTITY(1,1) PRIMARY KEY, status VARCHAR(1), dateCreated DATETIME, dateCreatedInt VARCHAR(25), title VARCHAR(20), name VARCHAR(100), lastName VARCHAR(100), customerCompany VARCHAR(100), phone VARCHAR(30), email VARCHAR(100), password...more >>

I'm trying to drop a SQL2K database through SQL. I'm using the following command: USE Master DROP DATABASE '<DB Name>' However, when I attempt to do this, I get the following error: Error: Cannot drop the database '<DB Name>' because it is currently in use. How can I drop a databa...more >>

Hi all, quick question. Any one know of a way to send SMTP mail through SQL Server 2000 (clustered) that supports the type Text in the message or body portion? I've tried a few extended stored procs and they all work fine except the message/body only supports varchar(8000). Any Ideas? Thanks ...more >>

Hi folks, I have the following cross-reference table that stores TypeId and TransactionId. CREATE TABLE [Table1] ( [Id] [int] NOT NULL , [TypeId] [int] NOT NULL , [TransactionId] [int] NOT NULL , [Code] [nvarchar] NOT NULL, CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED ( [Id] ) ...more >>

Is there a way to search the text of all stored procedures in a database for a piece of text such as a table name? thanks! ...more >>

Hi all. I think this is a correct place to ask this: I'm trying to address possible transaction deadlock(s) when I use ADO.NET. I followed all sql-side recomendations but I expect deadlocks anyway - app is too complex. Would it be the right way to do it if I write something like this: ...more >>

Can you not use ISNULL with dates? It seems to be ignored if used with a date. For example, if I use a statement: select Application from jobApplicant where PositionID = (select PositionID from Position where ReferenceCode = 'MD102465M') I get: 2004-11-16 00:00:00.000 2004-11-22 00...more >>

I have a 4000 row ascii file with duplicate employee data that I must select only the last entry for a given employee. The approach I've used imports the records into a table (##keep) using... INSERT into ##keep SELECT * from txtsrv...kte_data#dat alter table ##keep add importID int i...more >>

I have a client who is running SQL Server 7. I have an application which calls a stored procedure that executes master.dbo.xp_sendmail. This has been working fine for several months. Last week, Microsoft patch KB870669 (Microsoft Data Access Components - Disable ADODB.Stream object from Int...more >>

I see this has been discussed a lot, but I can't see that anyone has worked with this particular situation: I have an "instead on insert trigger" on a view which has an identity column. I need to use the know the identity value that SQL has assigned (or will assign), because I need to use it ...more >>

Hi Freinds, SQL 2000 I need to have this as a function. ----------------------------- declare @dr nvarchar(100) set @dr = 'c:\compaq\' create table dirlist (filename varchar(8000)) declare @qq nvarchar(1000) set @qq = 'insert into dirlist(filename) exec master..xp_cmdshell ''dir '+@d...more >>

Found it in a DTS package that bombs, but I can't find it in BOL: INSERT BULK (Column List) WITH (Check_Constraints) Could someone point me at a BOL topic? -- Peace & happy computing, Mike Labosh, MCSD "I have no choice but to believe in free will." ...more >>

See the following link: http://support.microsoft.com/kb/q175313/ This is an article from the knowledge base about a "confirmed" bug in the Microsoft SQL Server driver. It concerns a timing conflict of SQL statements from multiple threads. Although the article does provide some work around...more >>

I am trying to create the following view, it executes and gives me results but when I try to save it I get an error message that says: "View definition includes no output columns or includes no items in the FROM clause" Again, I get this message even though i can execute and get results prior...more >>

I need to backup a database including the stored procedures.When i right click on the database,it gives me the option to backup database.There is also another option to generate an sql script.Which one am i supposed to choose?...more >>

Hi. I need to be able to export data in a Sql Server 2000 table to an Access 2003 table. The following insert statements works when I do not select the datetime, and I remove the datetime field from the Access 2003 table. INSERT OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source="C:\tes...more >>

Hi, I want the database to invoke my application on occurence of an event(insert/update/delete). I dont want my application to poll the database all the time. can i implement this feature in SQL Server? If yes, then how to do it? Thanks, Rajeev...more >>

I have a table which has been altered.How do i generate an sql script so that it just updates the table instead of dropping the table and creating a new one?...more >>

Hello, I have one Table where i store my all emails to be sent(e.g. OutBox Table).After emails have been successfully sent i move emails from that table to history table (same schema as above table eg. OutBoxHistory table). My problem is after moving into the history table same records aga...more >>

I have restored a backed up database from a SQL 7.0 SP2 to a SQL Server 2000 SP3. In the new SQL 2000 database, I have a request that doesn't work because it can not convert the int (123537) to varchar(30), which is the datatype of the field con_id in the table. It works on the origina...more >>

I have a table with two smallint columns: Month and Year. I want to convert those two columns to a datetime with the day being 01. Is there anyway to do this with normal SQL functions when creating a view? Thanks, T ...more >>

Hello Everyone, I have a database table with two fields, it was created from an excel spreadsheet and one of the fields has numeric data in it, but I am trying to write a query on this field and I get the error cannot convert nvarchar to numeric, is there any workarounds on this? -- J....more >>

Hi all. Say I have procedure and it has something like this: begin tran update SomeTable set SomeColumn = 'Some Value' where SomeID = @SomeParam insert into OtherTable (Col1,Col2) values (@Param1,@Param2) commit tran And let's say I call it from web app (.NET or anything) without creatin...more >>

I know this is a simple question, but i still don't get it. ok i'm trying to set rows to inactive where records from another table exist here is my sql: update csistk set csistkActive = 0 where csistkCsiSym = (select fctmodCsiSym from fctmod where fctmodFunction = 'D') i don't what to use...more >>

Hello, how can i convert the timestamp: "Mon, 06 Dec 2004 00:54:21 +0100" into a standard datetime like: "06.12.2004 00:54:21" ?? Thanks Sebastian ...more >>

I have an XML string that contains international characters (for ex: GB pound symbol). When I update the a table in SQL server (SP using OPENXML), the characters don't get transferred. For ex: instead of GB pound symbol, its stored in the DB as b#. I am passing in the XML string as a NVar...more >>

Hi peeps, we are going to have to upgrade O/S from NT server to possibly 2003 server and I was wondering if there are there any known issues with 2003 server and SQL server 2000? Cheers. Geo ...more >>

Hi, this is one of those questions on dates. I'm completely stumped so any help would be great. I'm querying the server with - SET DATEFORMAT dmy SELECT project.id AS pid,* FROM project_date,project WHERE project_date.cms_id=project.cms_id AND project_date.dfrom<'1/6/2005' AND project_date...more >>

hi, In Access I can use the NZ() function to convert null fields to zero in a SQL select statement. I put it in a SQL view and I get an unknown function error. How do I do it in SQL and can you give me an example? Thanks! Joe ...more >>

I would like to created a query that display only two rows for each problem id where an inner join returns only the two most recent notes. A query with all notes might look like the following: SELECT p.id, n.note, FROM problems p JOIN tblNotes n ON p.id = n.id I would like to select the tw...more >>

I have following code that should run a dynamic SQL based on DBName and some number. When I print out @SQL at the bottom, the variables @Number and @DBname are not replaced with the variables assigned value at the top. Is there something I am missing?? Thanks in advance for your help. D...more >>

Hi, I've two SQL Server 2000 installed on windows 2000 server with the lastet windows/sql updates. I've created a linked server on S1 pointing to S2. When I create a stored procedure using query analyzer with Ansi_null and ansi_warnings on with the following sql statement select * from...more >>

Hi guys, Does anyone know of a development tool to unit test stored procedures? At the moment we are using nunit but it requires a lot of work to set up and then remove the test data for the next run. There must be something easier than this. Cheers, Andy....more >>

I have inhearited a third party SQL 2000 database, and im trying to create queries for different reports. One of the problems is that there are over 30 tables with no documentation, so it a tedious task trying to find matching data to build quires with. To this end, im looking to see if any on...more >>

Say I have a table with a column of data type image (it contains compressed files), how can I effectively run a query to give me the size of the image data? If I do len(cast(imagedata as varbinary)) it gives me 30, when I know for a fact that some are more than 30. Also is the actual si...more >>

Hi all of us, I need change the name of one our databases, but I wondering how? Any help will be welcomed. Thanks a lot ,...more >>

What is the minimum permissions I need to give a user in order to execute DBCC SHRINKDATABASE ? or what role would they need to be in ? how would I give the user those permissions in T-SQL? ...more >>

Greetings I am trying to do an update statement by retrieving the column to be updated from another table. Is this select possible ? UPDATE DNA_SalesBudgetAnalysis SET (SELECT COLUMN_NAME FROM SysproCompanyN.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'DNA_SalesBudgetAnalysis' A...more >>

I have a simple stored procedure that is based on a simple SELECT statement. Several fields are calculated fields, which I am trying to use again later in the statment. I get invalid column name errors on those fields. For example: SELECT Field1, Field2, Field3, Field 1* Field2 AS TOTAL, Fi...more >>