Hi All, Whenever I open the Microsoft SQl server 2005 SP1 I can't login to the database I am getting this error: " An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) "...more >>

I have some SQL with the following line in it; WHERE CONVERT(varchar(8), dbo.tblAppointments.fldAppointmentDate, 112) BETWEEN CONVERT(varchar(8), CONVERT(Datetime, '4/2/2007'), 112) AND CONVERT(varchar(8), CONVERT(Datetime, '4/3/2007'), 112) When I leave this as a where statement I get not...more >>

Database consists of the following 4 tables with respective attributes: CUSTOMER(C#, CUSTOMER NAME, ADDRESS), the key is [C#] ITEM(I#, ITEM NAME, MANUFACTURER, YEAR), the key is [I#] BOUGHT(C#, I#, DATE, QUANTITY), the key is [C#, I#,DATE] PREFER(I#, C#), the key is [I#, C#] I'm trying to ...more >>

Hello, I am learning T-SQL (SQL Server 2005). I have written such loop: DECLARE Received CURSOR FORWARD_ONLY READ_ONLY FOR SELECT ... FROM ...; OPEN Received; FETCH FIRST FROM Received; WHILE @@FETCH_STATUS = 0 // here error BEGIN ... FETCH NEXT FROM Received; END; CL...more >>

I have some fieldnames I would like to do a search and replace and it needs to scan all tables within the db. Does anyone have any code that could perform that function. I would like it first to list the fields before changing. Thanks. ...more >>

Hello, I am learning T-SQL (SQL Server 2005). I have written such loop: DECLARE Received CURSOR FORWARD_ONLY READ_ONLY FOR SELECT ... FROM ...; OPEN Received; FETCH FIRST FROM Received; WHILE @@FETCH_STATUS = 0 // here error BEGIN ... FETCH NEXT FROM Received; END; CL...more >>

DBCC showfilestats reports both Total and Reserved extents; but for the CURRENT db. I need to report on ALL databases on the server. any ideas hwo? Thanks ...more >>

Is there any way in Trasact-SQL to change a column datatype from ntext to sql_varient with out it bombing? ...more >>

Hello all, My company has a webbased application that we deploy to our customers. As a back end database, we recommend either Sql Server 2000 or 2005... I've discovered one slight rub with this, we maintain a sql file which is meant to sync up customer database schema's with the changes we ...more >>

Hello, I am working on a multi table join insert that will do a left outer join, and I was wondering if there is a way to use only the rows that have a right side join. DB Example: 1> select * from testtableA 2> go time id val ----------- ----------- ----------- ...more >>

This Update Sproc frequently times out in my vb.NET program. When I step through it in SQL2000 QA, it performs the 1st "SELECT 1..." query instantly, but hangs on the UPDATE. Both sections use the same fields "case_number" & "county" in the WHERE clause. "case_number" is a clustered index; ...more >>

Has anyone ever developed any SQL code for creating a merge-purge key??? Typical like ripping out vowels of addresses, using zip codes...stuff like that. We have to merge-purge a SQL Server Table and I'm just looking for some ideas right now. Any suggestions are appreciated. Thanks ...more >>

Does anyone have a script that can provide me with a list of permissions for all objects in my database for all roles that have access to the database....more >>

Considering this sql below: INSERT INTO Notes select id, (SELECT MAX(NoteID) +1 FROM Notes) I am getting this result: id NoteID 1 22551 2 22551 3 22551 But how can I get his result: id NoteID 1 22551 2 22552 3 22553 The problem is that the system ...more >>

How can I return this format : "mm/dd/yyyy hh:mm:ss PM" from a datetime field in sql server 2005 I tried: select convert(varchar(30), myDateTime, 109) but I'm getting the milliseconds (Apr 13 2007 4:27:07:963PM) after the second, which I don't want thanks...more >>

We get some dates from an external data source. In stock trades, the "settlement date" is generally 3 business days after the trade date. Some of the data comes in with the two dates reversed. (For certain administrative types of transactions, the dates are the same.) Will the following ...more >>

I am trying to fire a trigger off on updates but I am having a tough time when more than one value is updated. I have the following code : IF UPDATE(Ratio) BEGIN SELECT @NEW_Ratio = isnull((SELECT Ratio FROM INSERTED) , 0) SELECT @OLD_Ratio = isnull((SELECT del.Ratio FROM DELETED) , 0)...more >>

Is there a way to do a select statement into a variable instead of returning a recordset? Let's say that I want to Select fld1 from table2 where fld1=@xyz and have the value of fld1 returned into a variable, and no recordset. ...more >>

Hello All I have a select statement that returns a 1 or 0 bit if a record exists or not. I would like for it to return an actual field value instead of this bit. Here is the query statement along with DDLs and test data. SELECT GroupID, PrimKey, CAST(CASE WHEN EXISTS (SELECT * FRO...more >>

I'm trying to write a SQL Server function, which will be called by stored procedures or programs. I'm about halfway through and need some help, I think. First of all, I have to take a date that is passed, and first determine if it's on a Monday thru Friday. If not, then I'm to subtract a day ...more >>

Using the "Dummy" tables that I have listed below, here is what I trying to do... I need to group all the entries in TableA by ID1 and ID2 and they must be grouped by ID1 and then ID2, which is not the problem. The issue for me is that I need to look at the entire TableB for each specific ...more >>

I'm currently stuck using Sharepoint 2003, which lets you connect to a table view or function but not calla SP directly. I was wondering how I'd script a SP inside a view, or should I just use a function?...more >>

I can say SELECT "HELLO" and it returns HELLO But say I want to return a table HELLO GOODBYE Is there a select statement I can write? ...more >>

I have been trying to find a way to map out which tables and columns are used by all of my stored procs. Well, after some research, posts here and elsewhere, Googling, and more, I have discovered what Erland Sommarskog, SQL Server MVP, wrote about in his article "The Curse and Blessings of Dy...more >>

Our servers are all SS 2000 SP4 sitting on top of Win 2000 SP4. The other day I was in a meeting with our JDE administrator and he made the comment that when we go to Windows Server 2003, we'll get a "30% performance improvement". Of course, that's a vague number to say the least. But I am ...more >>

Hi, I tried to create a DDL Trigger as follow: Create TRIGGER [ddl_trig_login] ON ALL SERVER FOR Create_Login AS SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') Then I run the following Create login [xxx\xxxxx] from windows The re...more >>

Is there an easy way to query to find out what users have what roles in what databases? Ex: Login smith is dbreader and dbwriter in db1 smith is dbreader in db2 etc.. Thanks ...more >>

So I would like to do something in T-SQL that would make a lot of sense, but I dont think it exists... Mock Code: DECLARE @InsertedWhen AS DATETIME insert into MyTable(MyValue, InsertedWhen) values ('MyValue', @InsertedWhen = GETDATE()) Now the goal of this is to have the exact time t...more >>

I have a situation where I have a table that has an integer Id and an IsActive bit. There can be many entries in the table with the same Id. I want a constraint that will allow any number of these entries but will enforce that only 1 can have IsActive set to 1. I don't want to leave it to ...more >>

Can you guys point me to any books, papers or case studies on how to properly manage 3 tiers (development, quality, production) for many programmers (12) and over 100 databases? Information on security, migrating data into updated versions moving up from D to Q to P so data on P does not get l...more >>

I have a field 'TASK' that has the following data: TEST.SITE TEST.CABLE01 TEST.FACILITY TEST.KIT02 I would like to set a flag 'NA' in a field created by a CASE statement when the last two characters of the field data are less than 99. I tried using the following case statement but incur...more >>

I have three tables in my database. Each is linked to the other by a one to many relationship. As follows Traveler ---->Traveler_Steps---->Step_Data One traveler can have many steps, each step can can many step_data, but each traveler_step can only have one traveler. I am trying to "clo...more >>

Hello, I am trying to get to grips with SQL .. its a very long time since I did any sort of SQL ... (Pick Databasic and Oracle 6) The question .... I have two tables ... Job Table and Customer table. I am mainly working on the job table which holds all the data about jobs carried out for...more >>

Hi, I am trying this with no success. Declare mycursor for EXEC @StoredProcedureName That is instead of simple select statement in my case there is exec stored procedure name.I know this is possible.Can somebody help me out Also if i run exec @StoredProcedureName then it is returning data. T...more >>

Hi. Is there a way to access linked servers from Enterprise Manager? Recently I tried this: SELECT * FROM [ABC-DEF.GHIJKL.MNO,1234].dbname.dbo.tblname It changed to: SELECT * FROM [ABC-DEF].[GHIJKL.MNO,1234.dbname].dbo.tblname tblname_1 I am looking for a general solution because i...more >>

Hi, I want insert data into multiple tables. In a single query using to collect data from more than one table .In that same method , insert values to multiple tables using a single query. Please send solution. Thanks -- Raj...more >>

In a SQL 2005 stored procedure that currently output's using the print method, is it possible to modify this so it sends its output to an email? ...more >>

Hi, I am trying to change the deployment of a pre-existing set of data warehousing databases, which are distinguished by different quarters, by placing databases for previous quarters on a different server. This is mainly for reasons of space on the server that is earmared to contain the mo...more >>

Hi, I have created this simple SP in SQL Server 2005: ------------------------------------ use northwind go create proc sp1 @cid nchar(5) as select * from customers where customerid=@cid go ------------------------------------ Then created a new trace in profiler and selected all events...more >>

For a long time, I have wanted to use SQL Server to perform some calculations which are based on running values (which imply an order). Of course, using SQL Server for this is not a good idea, since all operations are set-based. However, with the introduction of ranking functions i...more >>

Hi, In my SQL Server 2000, I wrote the following scripts. And all the Tables which has name like 'test1' will be updated! But it is not what I want. declare @sql nvarchar(4000) set @sql=' declare @vvc nvarchar(50) set @vvc=''ooooo'' SELECT N''UPDATE '' + QUOTENAME(TABLE_SCHE...more >>

I have trigger which delete some records from other table. What is better performance: use exists or without exsists statement: if existst(Select * from deleted d INNER JOIN table i ON...) DELETE i FROM deleted d INNER JOIN table i ON.... OR without exists statement: DELETE i FROM del...more >>

Hi. is there a way to convert and concat 5 integer fields so i can use it as a one single string field on my where caluse? i have 5 integer fields... CTA,SCTA,SSCTA,SSSCTA,SSSSCTA i need a result like " 1101 1 1 2 0"..... how can i do this? TIA ...more >>

Hi all, I'm backing up a database and then restoring it with a different name. On the restore part if the database is all ready there is it possiable to keep 2 tables and the data that is in them and 3 Stored Procedures that are not in the original backup database? USE master BACKUP D...more >>

My SQL server got over 30 databases. I want to list out the table sizes . How can i do that? Thanks ...more >>