Hi, I'm not sure how to explain my problem, so I'll go ahead with an example. There are two tables: tblContact & tblContactAddress tblContactAddress has a FK ContactId. I want my SP to return something like this: ContactAddressId ContactId ... Position -------------------- ----...more >>

Hello: I am developing C# project, using ADO .NET 2.0 to connect to SQL Server 2005. I created a DataTable from scratch, and then use SqlBulkCopy to insert it into an empty table in a SQL Server 2005 table. The datatable I created from scratch matches exactly the empty SQL Server 2005 table. ...more >>

Hi let say I have record:s in two tables, namely tblTable1 and tblTable2 tblTable1: Year SerialA SerialB 1998 1 3 2000 3 2 1999 2 2 2001 5 3 1998 1 1 1999 ...more >>

A colleague was telling me the other day that in Sql Server 2005 there is a special type of query you can do, whereby it runs in the background and brings back results automatically, whenever they change. He referred to it as something called a "live" query, but I'm not sure that's correct ...more >>

Can a clr stored proc use a config file? ...more >>

How can I execute sql scripts saved in a text column of a table? Thanks, Peter...more >>

I have a stored procedure in a database on a server, and this stored procedure uses a linked server. The login I was given is only granted DBO permissions on this database. I am trying to set this up so that the above stored procedure runs once per month, so I naturally tried to set up a Job...more >>

I have what is probably a simple problem and I guess I'm just looking for whatever solutions you all can suggest... I have this query: Select columnA , sum(columnB) as VAL from myTable where columnA in ('01,'02') group by columnA which may return: columnA VAL ------------- ---------...more >>

I have to filter cells of a table, i.e. replacing individual table cells with nulls. In a stored procedure I could use the following code snippet, where the filtering is done by SendThisRow() and BuildSqlDataRecordFromFilteredIDataRecord(). using(SqlConnection connection = new SqlConn...more >>

I want to convert a varchar(5) field to smalldatetime. I have a column that hold credit card expiry dates in mm/yy format e.g 10/06 I want to update a new column with a smalldatetime value derived form the column above. So I tried the following CREATE FUNCTION [dbo].[StringToDate] (@DATETE...more >>

I have a table with 9 code columns in it. I want a listing of every possible code in any of the 9 columns with a count of each. Is there a way to do this without creating a new table that has 9x the rows that the current table has or without 9 queries plus a sumation query? The two ways I...more >>

HI all, I'm adding a custom SQL message. How do I insert a new line, line feed. So that the message is split between a few lines Thanks Robert ...more >>

I have been trying to shrink or emptly a log file in one of my Databases but to no evail. When I try to shrink it only shrinks it by a little bit and grows to the original size within seconds. Is there a way to completely empty a log file. It about 80 GB and something is now right... T...more >>

Computer: 3.4 Ghz CPU, 1 GB RAM, 2003 Server database : MS SqlServer 2000 Enterprise. ~ 10 GB database file. Largest table in the database contains 11,000,000 records. Framework: .NET 2.0 I try to run a query against the database, selecting aggregated data from views based on the large ...more >>

I could swear that I once read that there were 12+ normal forms, with most of them theoretical. I can't seem to find the document and maybe I'm confused about what I'm remembering. So, can someone tell me how many normal forms have been somehow defined (or "proposed" if that even makes sens...more >>

Hello all, Originally, I had a query that looked like this: SELECT TOP 100 PERCENT co, id, salary, rate, ratePer, startDate, endDate FROM dbo.ERate WHERE (rateCode = 'Base') AND (startDate <= GETDATE()) AND (endDate >= GETDATE()) ORDER BY co, id now this works find and we...more >>

I have a .bat file that uses ftp -s:scriptname that works from a cmd window and works from running the .bat file but won't work from xp_cmdshell. The sql agent proxy is set to a system administrator domain account that is also an admin on the server. The folder where the files are is shared ...more >>

Hi, I am having what seems to be an obscure issue with SQL Server 2000. I posted about the problem in these groups about 2 days ago but did not receive any suggestions. I have never contacted MS paid support before, so I am not sure what to expect. How much would I need to pay for assistanc...more >>

I just found that the following code on my SQL Server 2000. And it works fine. what's { fn .... }? SELECT GETDATE() AS date, { fn SECOND(GETDATE()) } AS sec ...more >>

I have a table called sv_call_log The table contains columns row_id, card_num, start_date, end_date. This table logs calls. The start_date is when the call begins and end_date is when the call ends. They are both fromatted as follows EX. 2004-09-20 15:55:29.247 We currently have 4 lin...more >>

Within a SQL Server Job, I am using the following vbscript to connect to a FTP server and FTP a file: strLocalFolderName = "My Folder Name where we put the file to be FTPed" strFTPServerName = "FTP Server Name" strLoginID = "FTP Server Login ID" strPassword = "FTP Login ID Password" strF...more >>

I have some Records in my table like that 2006-06-16 14:15:01.607 2006-06-16 14:17:28.187 2006-06-15 14:17:28.000 2006-06-13 14:17:28.000 2006-06-17 14:17:28.000 2006-06-19 14:17:10.000 2006-06-17 14:17:28.000 I want to have count number of the records if they have same date. I dont ca...more >>

Hi: I would like an incrementing column in result set of a select query. Numbers begin with 1 and increment by 1 for each row in result set. Does same as counter column in table. How do you do this? Thanks, Charlie ...more >>

I have a long query and therefore want to avoid using it twice in an if else structure, while still be able to achieve excluding an id when @id=null(meaning all included) in the where clause or like. i.e. if @id=null theId<>555 Thanks, -- bic...more >>

I was under the impression that the DATETIME field was stored internally in SQL server as two integers? Any idea how to get correct ordering of a datetime field? I have two records containing a datetime field with these values and this is the order that order by displays them in when I say...more >>

I notice that SQL 2000 has a datatype of image. My question is after I setup the table and fields how do I populate the table with image data? ...more >>

Hi EveryOne, I have a table like this and wanna to distinct some columns so that only one record will show for this criteria (User1 <> User2 AND User1SampleNo <> User2SampleNo) User1 User1SampleNo User2 User2SampleNo EuclideanDistance ----------- ------------- ----------- -...more >>

Hi all, since seeing the post from Satya with regards to Alter time of proc, there's been a question bugging me for quite a long time now. Is there a way within SQLServer (2000) or 2005 where i can tell the last time a proc was executed? Immy ...more >>

I have two tables Expense_Addl and ExchangeRates These are the scripts CREATE TABLE Expense_Addl ( FileId int, HostAmt money, HostCurrencyCd char(4), BaseCurrencyCd Char(4), LockDt smalldatetime ) INSERT INTO Expense_addl values (100...more >>

I have two tables Expense_Addl and ExchangeRates These are the scripts CREATE TABLE Expense_Addl ( FileId int, HostAmt money, HostCurrencyCd char(4), BaseCurrencyCd Char(4), LockDt smalldatetime ) INSERT INTO Expense_addl values (100...more >>

In a table I have a date format: YYYYMMDD What do you do when instead of NULLs or Blank Dates there are 99999999? In Access I'd use an IIF statement. This case statement by itself doesn't work. Any ideas appreciated: SELECT CASE WHEN Col004 = '99999999' THEN IS NULL ELSE CONVERT(smalld...more >>

Hi all, Is it any way to query a running transaction to see the isolation level it is using? I'm debugging a mobile .net application that access a SQL 2K database and I need to verify if it is running transaction on the desired isolation level (read uncomitted). My idea is to run a test app...more >>

How can i write a simple procedure as below, call it from a job, and because @m = 2 it will fail the job step and so show me the red x beside the job name in ent mgr? create procedure marc1 as declare @m int set @m = 2 if @m = 1 begin print '2' end else begin print '2' return -...more >>

I downloaded some example code from MS and don't understand why this trigger is being used. Wouldn't a column default accomplish the same thing? Is there some reason a trigger is 'better' than a default? Is the trigger meant to ensure that someone doesn't insert a different value for [Las...more >>

Hello all, I would like a total (beginning balance) field in a query be the total for the year up to the month based on another field within the query, so that when the fiscal_date = '5/1/2006' that I would have a field contain totals from '1/1/2006' thru '4/30/2006'. I would like to do this ...more >>

How do you guys go about compare the efficiency of two queries? What I usually do is run the "Display estimated execution plan" in the query analyser and see each query cost (relative to the batch) and pick one that gives lower percentage. Is this a good way to compare queries? Am I missi...more >>

I want to SELECT data from one database table and INSERT it to another table on a second SQL Server in C# ADO.NET. Where can I find information on how to do this? ...more >>

How can the sql statement listed below be modified to test the UserProfiles_Number table that UserID field has only one record in the PhoneNumber table that corresponds to the UPID field? I would like to output the records from UserProfiles_Number that do not have single match between Use...more >>

Hello all, I have some data in a Varchar(15) column name datecolumn that I need to fix, should be date value. some of the data is like this 12/6/304 4/1/105 4/26/705 2/4/705 11/15/605 2/24/606 should be this: 12/06/2004 04/01/2005 04/26/2005 02/04/2005 11/15/2005 02/24/2006 ...more >>

Hi Group, I have 2 tables: x and y. this is my query select x.abc, (select y.def from y where y.id=123) as comment1, (select y.def from y where y.id=456) as comment2 from x where x.id=789 let's say, there are 1 million records in table x, how many times this sub-que...more >>

I wrote a custom data processing extension which works in preview mode, but when viewing it from the report server I get the error: ---- An error has occurred during report processing. (rsProcessingAborted) Cannot create command for data source 'MeridiumDataSource'. (rsErrorCreatingCommand) Th...more >>

Hi, I have a requirement, i need to bulk insert the data form the text file. we need to insert firt 2 values from text file and insert into first table. then retrive the primary key value of inserted values then with this value as Forign key insert next 4 value from the text file. is there...more >>

SQL Server 2000 on Windows 2003 SP1 and IIS6 I'm getting the following error from my ASP page: Microsoft OLE DB Provider for SQL Server error '80040e14' Formal parameter '@mname' was defined as OUTPUT but the actual parameter not declared OUTPUT. However, the formal parameter '@mname' ...more >>

I have two tables Expense_Addl and ExchangeRates These are the scripts CREATE TABLE Expense_Addl ( FileId int, HostAmt money, HostCurrencyCd char(4), LockDt smalldatetime ) INSERT INTO Expense_addl values (1001, 500, 'USD', '2006-06-15') INSERT INTO Expense_addl values (1001, 500...more >>

Hi all, Supose I have a view that combines fields from multiple tables. When I select something from this view, does the query optimizer know to 'use' just the fields specified in the SELECT statement and in the WHERE clause (if any)? Thanks, Sorin ...more >>

Hi Can somenone help me build a DTS in SQL Server 2000 that allows to schedule the execution of a executable file in a remote server without sql server?... [] Ricky ...more >>

Hi All, I want to see the dates on which a proc is altered in a database. Is there any way to get this information ? Regards Satya ...more >>

Hi all, Iam havin a rather complex query and need to add another column in the resultset. That new column is a COUNT aggregation and I need to use the GROUP BY clause. Below is the query that I tried. However, there is a problem with text, ntext or image columns being in the GROUP BY clause. ...more >>

Hi, Is there any way to make sure that perticular query uses specific index. I know that this can be accomplished using index hint. But client is arguing to work out on any other way to force the query to use perticular index except hints. Thanks for help, NJ ...more >>

Hi Everyone! We are using a cursor for paging results in SQL server, mainly due to the performance gains achieved when working with large results sets. We have found this to be of great benefit when working with SQL Server 2000, but have run into major problems when using it on SQL Server 200...more >>