Hi, We are uploading data from one table to another table. Table size is 430 million rows. We are using INSERT INTO .. for bulkloading. The problem we are facing is transaction log becomes too huge and unmanageable once we complete this operation. I want to know is there a way we could turn...more >>

Hi, In a sp I have multiple selects. How can I combine the result sets to one? Thanks! Arjen ...more >>

I have three tables: Process_Log, Process_A AND Process_B . The log table is used to create logs for data inserted in the other two tbls. The two tbls have the Process_Log ID as an FK and for each of the two a unique process file ID exists in the Log . I am using these update statements to upd...more >>

Hi, I have a server running 2005. I have a SP that dumps data into a table. When it runs, it adds 100K records (200Mb?) but the database grows to 19GB! but 18.5 is just space. When I shrink the DB I goes back down to 500mb? If I run the SP again, I get the same problem Thanks for the help! ...more >>

I'm getting the warning message: Warning: Null value is eliminated by an aggregate or other SET operation. When running the SP listed below. Count is returning a valid number greater than zero so I don't understand where the null value in the message is coming from. Is this anything I ...more >>

I am connecting to a remote SQL Server database from Visual Studio .NET 2005. I have an sql script located on my machine containing the sql code I want to use to create several tables, and want to execute it. How can I do this from Visual Studio .NET 2005? I do not like designing the database ...more >>

Hello! I remember reading a while ago that index on INT column could be used for bitwise operations efficiently. For example, Index on dwRights_Public could be useful: select dwPlayerIx from FTE_TABLE_SIT where (dwRights_Public&4) > 0 I created an index (which is highly selective) but Qu...more >>

Can you edit the result set from Studio Manager Sql Server 2005 like you could in EnterpriseManager sql server 2000? I loved being able to go directly into a table in sql server 2000 enterprise manager, return a result set and just edit inline right there. In sql server 2005, I always see a n...more >>

In a stored procedure if a variable supplied is '' eg @variable varchar(25) is supplied as '' how can i make that variable NULL for a query ? ...more >>

I have a table like OrderID Orderdate City SalepersonID 11 1/2/2005 NYC 205 12 2/5/2006 CHG 206 13 2/5/2003 NYC 207 14 3/5/2006 CHG 205 15 4/5/2006 NYC 206 There are only 2 cities I want to write a query to find out SalespersonID, count(orders by salespersonid), overall % of ord...more >>

Ladies / Gentlemen I am looking for software that will read in Old SQL / TSQL and will reformat it and make performance improvements to the generated SQL Output. Mark Moss ...more >>

I am trying to display only those records that are duplicates, based upon two columns. Example. I have a date field and a session field. I want to display only those records that have the same date and session listed more than once. Date Session 09/01/2006 ...more >>

I would like to use the ArrayList.Contains Method to determine whether an element is in the Arraylist. The elements in my list are User Defined Types (UDTs). Does this mean I have to override the Equals method in the code for the UDT with something like: public override bool Equ...more >>

Hi, Is there a way to format dates as m,dd,yyyy, e.g. 9/30/2006? I didn't see that style parametr in BOL for CONVERT. ...more >>

I have the following query as below select * from PJPROJ, pjprojem where pjproj.project = pjprojem.project and (pjprojem.employee = @parm1 or pjprojem.employee = '*') and pjproj.project like @parm2 and pjproj.status_pa = 'A' and pjproj.status_lb = 'A' order by pjproj.project; I want to m...more >>

Hello, I need to automate the replication from live server to test server (i.e weekly backup of a live server followed by restore to the test server). Any pointers would be greatly appreciated. Thanks in advance, Don Diego...more >>

Hi, How can I format a FLOAT or MONEY datatype as a money string, e.g. Format and convert 1234.56 float to '$1,234.56' string. I need to concatentate the '$1,234.56' with text, hence the need for the formatted string. I tried different combinations of CONVERT but couldn't get it. T...more >>

Hello. I have a nvarchar(500) field in a database table (SQL 2000). This field contains carriage returns, etc... When I view the output in Enterprise manager or Query Analyzer, these to not appear. The formatted results appear. How can I run a query to see what is actually stored in the fi...more >>

Hi, I've come across this oracle code that I need to convert into T-SQL: GROUP BY f.folio_no, ft.room, decode(ft.from_resv_id,null,''(Group)'',rn.guest_last_name||decode(rn.guest_first_name,null,'''','', ''||rn.guest_first_name)), decode(ft.from_resv_id,null,ft.room,rde.room) I've conve...more >>

I am trying to find out the days since the last application(purchase) by a client and have a few views which will tell me the last application a client has done but i need to find out the days between apps, here is my view select LastApplication.ClientId as ClientId, LastApp...more >>

Hello everyone, I have the following problem I am not sure how to solve: I am using sql 2005. I have a database that has a product table (already with around 2000 products). The product table has a price column. I need to add another column called discount. The discount is by default 5% of ...more >>

I don't have accesss to DTSRUN. Can someone tell me if its /L (log_file_name) argument will accept a UNC path?...more >>

I never got a response, so I thought I'd repost: I’ve got two 4-way 2000 servers. I am wondering how a migration into Clustering would occur. Here are some of my questions: 1. I’d like to end up with two active/active 4-way servers, call them “ServerA” and “ServerB”, accessi...more >>

Hi I have the following table CREATE TABLE [Sales] ( [StoreID] [int] NOT NULL , [CatName] [varchar] (100) COLLATE Latin1_General_CI_AS NOT NULL , [WEDate] [smalldatetime] NOT NULL , [Best_Sales] [real] NOT NULL , [Corrected_Sales] [real] NOT NULL , [Total_Waste] [real] NOT NULL , [A...more >>

Hello, I am trying to figure out if there is a way to call web service from sql server 2000 via extended stored proc. I am getting indications that you can. Is there anybody that has done this before? At this point, we cannot upgrade to sql 2005, hence the need to call from sql 2000. Th...more >>

What is the best approach to use to monitor database performance? Can you run specific system sp's to analyze load on the SQL server, number of users / Stored procedures that are currently executing, what load each of the stored procedures are putting on the server, etc?? Thanks! DotNetNow...more >>

Hi All, It appears that when using a correlated subquery with a group by clause in the outer query that any of the fields referenced by the inner query that are in the outer query need to be part of the outer query's group by clause. Is this the case? Is there a workaround? I am using SQL 2...more >>

Hi all, I have a table "Models" which has a column name (field) "model_islive". Table contains models about "titles" that are either archived (model_islive = 0) or live (model_islive = 1) I managed to build a query that get the price field of the most recent non live model, but now I have a...more >>

Hi I am creating a new SQL function to call in a future script, but I am having trouble getting it to sum the correct amounts based on dates to generate Month to Date and Year to Date totals. The function needs to take a single date, work out the month start date and then sum the total f...more >>

Hi, I know that sp_fkeys will tell you for a given table what other tables are foreign keying off of it, but is there something that tells the reverse? For example if table A has a foreign key to table B, is there a system stored proc I can use passing in table A's name to see the reference ...more >>

Hi, I'm trying to run a UNION query from tables in different databases with different collations. SELECT * FROM dbo.SALESDESCENDINGYEAR3 UNION SELECT * FROM dbo.SALESDESCENDINGYEAR4 (SALESDESCENDINGYEAR3 AND SALESDESCENDINGYEAR4 are views which selects data f...more >>

Probably really simple but I can't seem to get it. I want to compare 2 fields in the where but one of the fields (UserID) has garbage in the first character so I want to delete the first character in one of my where clauses. SELECT DELTEK.USER_ID.USER_ID, DELTEK.EMPL.LAST_NAME FROM ...more >>

I have a table with part records, quantities and cost. what I would like to do is insert the item into another table and duplicate it x number of times (x being the quantity) but divide the cost by the quantity. e.g. Part Qty Cost 001 2 £500 002 3 £300 in...more >>

Hi, I know that sp_fkeys will tell you for a given table what other tables are foreign keying off of it, but is there something that tells the reverse? For example if table A has a foreign key to table B, is there a system stored proc I can use passing in table A's name to see the reference ...more >>

Hi All, I am new to SQLCLR programming and I am attempting to write a stored procedure that iterates through a result set and sends an email for each record. I would like to access the System.Web.Mail namespace and a custom assembly that is registered in the GAC. Are either of these possi...more >>

I have a view that uses Union to join data from our "production" and Archive environments. Both have a noclustered index on the SSN field and a Primarykey on a noncontiguous field. The production database has a large volume of activity, select and inserts, while the Archive is read. When I execu...more >>

I have 200 databases with identical schema from a customer. I need to take the data from a specific table in each database and append it to a single, identical table in a "master" database. It is a one time project. I'm not looking forward to running a script 200 times. Can anyone give me s...more >>

Hi, I have a select statement that selects data out of three tables, like this. SELECT DISTINCT * FROM table1, table2, table3 WHERE table1.name = 'x' OR table2.name = 'x' OR table3.name = 'x' Well, this is just an example. The real statement is bigger. When I run this query with two tables...more >>

Hallo All, I have the following table with fields/columns and test data) called tblExport03 ID FirstName LastName BusinessEmail PrivateEmail NoMail 1 Bob Bobson bob@bobsom.com bob@priovate.com o 2 Jim Last last@jim.com jim@mail.com ...more >>

Hi all, I have a table "Models" which has a column name (field) "model_islive". Table contains models about "titles" that are either archived (model_islive = 0) or live (model_islive = 1) I managed to build a query that get the price field of the most recent non live model, but now I have a...more >>

I am wondering if it is possible to insert the results of a stored proc in a temp table as fiollows : INSERT INTO #temp sp_configure 'Remote Access' In fact I need to check if the server allow using remote acess to continue lmy batch thanks...more >>

Hi all, I have a table "Models" which has a column name (field) "model_islive". Table contains models about "titles" that are either archived (model_islive = 0) or live (model_islive = 1) I managed to build a query that get the price field of the most recent non live model, but now I have a...more >>

Hi all, I have a table "Models" which has a column name (field) "model_islive". Table contains models about "titles" that are either archived (model_islive = 0) or live (model_islive = 1) I managed to build a query that get the price field of the most recent non live model, but now I have a...more >>

Hi, We're trying to investigate a performance issue with our application which uses SQL Server 2000 as the backend. The performance has become an issue in a live customer environment and we are trying to determine what is acquiring and holding locks for a "long" time. We've been monitoring ...more >>

I have SQLServer 2000 and when I run the following code I got an error message: DECLARE @object int DECLARE @hr int DECLARE @src varchar(255), @desc varchar(255) EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT IF @hr <> 0 BEGIN EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT ...more >>

Hallo All, I have the following table with fields/columns and test data) called tblExport03 ID FirstName LastName BusinessEmail PrivateEmail NoMail 1 Bob Bobson bob@bobsom.com bob@priovate.com o 2 Jim Last last@jim.com jim@mail.com ...more >>

Hi, I'm trying to do an update on a table, but I'm confronted to a difficult query. My tables: [Flag] { FlagID, Description } [FlagData] { FlagDataID, FlagID, DataDesc, DataValueStr} An example : Flag : FlagID = 104 Description = 'Martin Smyth' FlagData FlagDataID = 208...more >>

Hi, I'm trying to do an update on a table, but I'm confronted to a difficult query. My tables: [Flag] { FlagID, Description } [FlagData] { FlagDataID, FlagID, DataDesc, DataValueStr} An example : Flag : FlagID = 104 Description = 'Martin Smyth' FlagData FlagDataID = 208...more >>

Hello, I want to create a stored procedure that will take a list of Bigint numbers to use in the where clause of a select query. The problem is that I am not sure which datatype to use for the parameter because declaring one parameter with bigint datatype only allows me to pass one number,...more >>

Hi does anyone know a way of enumerating through the built-in functions in SQL Server 2005? I'm trying to create an expression builder similar to the one in Access 2003. Thanks Chris ...more >>

I have a big trouble on one of my databases in a Microsoft SQL Server 2000. Only in one of them, if I want to create a query by the design mode and not with a script, the console put an alias for each table that I include in the view. It's impossible to erase the alias because each time tha...more >>

Hi, We have an externally supplied database running under SQL Server 2000 that is accessed through a thrid party front end application. Users have been experiencing problems where they can access info they are not supposed to and updating one record actually updates another record. I have...more >>

Hi, I know I should probably know the answer to this, but I'n gonna ask anyway. In the simple case of an integer being returned to a calling procedure/trigger, does it make any difference whether you use an output parameter or a return value? My own preference is for an output parameter, but ...more >>

Hi All, i've table name Commet with one column, names "Description" the values in table is in the following convention" INSERT INTO Comments (Description) VALUES('Feedback::Alert:very bad !!!!!') INSERT INTO Comments (Description) VALUES('Feedback::Possitive:test test test ') INSERT INT...more >>

Hi, I have this table where one field is set to Identity type. I want to know the next valid ID tha will be generated before inserting a new record! @@Identity doesnt help as it gives the id that was used in the last insert whereas I want to know the next id before inserting! Sinex ...more >>

Hi All, Our servers are getting frequent deadlocks and this is one trace which we got to troubleshoot the deadlock. But I cant understand this trace file and particulary the facts that the two selects which involve in the deadlock are for two seperate tables. Deadlock encountered .... Pri...more >>

Hello I have a stored procedure in oracle that does (simplified) select name from table where enddate=NULL LOOP --read the name in each recordset and append to one single string that is returned from the SP END LOOP In sql server I would rather not use stored procedures, is it possi...more >>