Is it possible in T-SQL to directly send query results to a text file without the use of command line utilities and command shell procedure calls? (The only reason I ask is because a friend of mine insists he once used something like a 'SET OUTPUT <filename>' command with SELECT. I think he's...more >>

Here's a stumper: I have two queries, on two different tables, that return a set tuple, ie, select distinct a, b from table_1 select distinct c, d from table_2 How do I combine the queries to return the set difference of (a, b) - (c, d)?? The example to perform a set d...more >>

All, Could anybody remind me a string to connect to a remote MS SQL Server exposed on the Internet using IP/Port#/Login/Database/Table? Thanks, Dmitri. ...more >>

Hi all, I would like to ask how to update a value of each row from a table using "FETCH". For example, I would like to insert a key like 1 to 10000 in a table with 10000 records. How can I do that? Should I use "FETCH"? Thanks a lot, Ray ...more >>

Hi All, I have a stored procedure that is called by C# code to start a SQL agent job. The stored procedure simply call the MS SQL system procedure msdb..sp_start_job to start the job. The problem is that after the job started, the message "Job XXX started successfully." is raised and the ...more >>

I'm trying to archive data in a live database with very large tables and am using bcp to copy data to files, but I need a good method to delete all the records that I just copied, without blocking access to the tables. The problem is that the database has many active connections throughout the day a...more >>

Hi I am trying to import some text files into MS SQL 2000. I want to import them into a table with the first column being the filename (excluding the extension) and the second colunm being the contents of the textfile, I want the entire textifle to fit into the one cell (ignoring any commas, ...more >>

Hi How would I go about importing a multiline text file into a single cell in SQL? And what dataformat should I use to store multiline text data. On importing I want to ignore any commas, etc and interprit it including the CRs into one cell. Thanks Dan ...more >>

I do the following: SELECT * FROM Item_treeOptions() And get back ~3000 rows. But when I try the following: SELECT COUNT(*) FROM Item_treeOptions() I get a count of >6000. Looking inside the rowset returned, it seems the data has somehow been truncated, as if subsequent rows have overw...more >>

I get "Arithmetic overflow error converting expression to data type int" when I run a simple query .. Select id , SUM (transtime) from table1 group by id The "SUM (transtime) " throws this error which tries to sum up very large value . the SQL DB is SQL 7.0 How do I get rid of this err...more >>

Hi, I know when you run a query the first time, it cache the execution plan and syscacheobjects table, and second time or third time when you run the same query, it will reuse the execution plan, therefore it 's faster than first time. but what if a query is inside a trigger, and this query...more >>

Hello: I'm using the following script to prepare a select statement into a variable and then in executing that select statement: --start of script declare @dbname varchar(30) declare @tbname varchar(30) declare @last_bkup_file varchar(255) declare @sql varchar(500) set @dbname='CCQ' ...more >>

How can I find list of columns that are not Primary Key from table I tried : select distinct s1.name as 'table name', s2.name as 'column name', s3.status, s3.colid as 'cons col id', s2.colid as 'col col id' FROM dbo.sysobjects s1, dbo.syscolumns s2, dbo.sysconstraints s3 where ...more >>

Hi, I have three fields which I want to concatenate into one, but I want the last one to be zero padded on the left to a specific width: Fields and example values (all are integers): Company Type Account 11 4 456 12 3 3342 ...more >>

Hi all! The text below is from a view I'm trying to create via Enterprise Manager. The columns eMonth, eDay, eYear, etc. are all NCHAR. I want the view to create a real date and time. Then I can actually do some calculations on counting records in certain 24 hour periods. But I keep getting...more >>

I have a query that joins and displays several values as one, converting them to a string, but if any of those fields are null, it returns null. For instance, if all fields have values, I get "abc", but if b is null, I get null returned instead of "ac" Select ColA + " - " + ColB + " - " + Co...more >>

Hi, I'm trying to update a row in a table that has 87 columns. I'm doing this update because an insert has failed, which means that only some of the columns need to be updated. An insert only fails in my program if there is already an entry for the current row that matches (i.e. each row has a un...more >>

Hi there, I've created a new (utf-8) web-application that works with unicode, as a new update from my old ASCII application. How can I convert and export the varchar data to nvarchar. Will something like this work? INSERT INTO dbo.NewDB.Table1 (id, title, description) SELECT id, N'titl...more >>

Hi, I've a problem with the following SQL Statement: DELETE AK FROM OPENDATASOURCE('SQLOLEDB','Data Source=myserver;Initial Catalog=mycatalog;User ID=myuser;Password=mypw;').mydatabase.mytableowner.mytable AK left outer join localtable lt on lt.field1 = AK.field1 where AK.field2 = 0 Whe...more >>

I don't know where to ask this question, I am hoping someone will clarify this for me. We are evaluating several ERP systems, but when it comes to choosing the database. All the software vendors say that they have to pay approx. 10% Royalty to Microsoft to develop their applications on SQL...more >>

Hi All, I'm developing a (Client-Server)database application in VB inorder to work in wireless environment (CDMA or WLAN). The problem i'm facing is, though the wireless network are not stable as compared to wired networks between Client & server; I'm getting an SQL timeout error. Does...more >>

I have a property table indexed on an autonumber primary key, with a subsidiary one-to-many contacts table. I have to add additional properties for many condos and businesses with unit numbers, duplicating the property info, and the contact records, just adding in the unit number. Does anyone kn...more >>

Hi, As part of something larger in scope, with the following script, what I'd like to do, is to pass one variable to another (as seen in the EXECUTE statement): -- start of script declare @dbname varchar(30) declare @tbname varchar(30) declare @last_bkup_file varchar(255) set @dbn...more >>

If you're in Enterprise Manager, and you right click a table and select return all rows, the results are displayed in a Grid. Does anyone know what type of control this grid is? Is this a grid that is available for distribution / programming / reuse? The reason I am asking is becuase that co...more >>

We need to create a lookup structure that can contain a flexible amount of attributes. We have discussed different design solutions for lookup-functionality, and come up with this suggestion: The "lookup" structure will be defined by three tables. The "Element" table, the "Attribute" tabl...more >>

Hi, Still having the same problem (as follows), I had posted it before but haven't managed to resolve the issue. Sorry for reposting it. I am trying to import some text files into MS SQL 2000. I want to import them into a table with the first column being the filename (excluding the ext...more >>

I am having am getting the following error message when I attempt to updated a table that fires a trigger that calls a stored proc on a linked server. Server: Msg 7391, Level 16, State 1, Procedure tr_CreatSilkIssue, Line 45 The operation could not be performed because the OLE DB provider 'S...more >>

It's there anyone expert can teach me whats difference between using "Not in" and "Not Exists" in my SQL? Because I found out that "Not Exists" is faster than "Not in". However, my question is when I will to use "Not in" instead of "Not Exists"? Thanks ;) ...more >>

Hello, How do I change the sender name when using xp_sendmail? For example...the sender in my mapi profile is "michael" So when I use xp_sendmail, it will show: From: michael To: Robert Subject: test Message: testing EXEC xp_sendmail @recipients = 'robert', ...more >>

I have just upsized an Access database, that was at 2G limit to a SQL Server backend and I am having a problem with one of my columns. I have a column [EquipID] this field was unique and you cannot enter a duplicate ID. This function is not working now that I have upsized I need it to w...more >>

How do I capture an SQL statement error in a stored procedure? I have tried using @@error but the stoped procedure "dies' before the check on the flag. What am I doing wrong? -- Jonathan Blitz AnyKey Limited Israel "When things seem bad Don't worry and shout Just count up the times ...more >>

I would like to show my own messages and display it to the user in an output parameter using a sproc. But it seems that whenever there is an error Sql Server's builtin messages will always be displayed instead of my friendlier messages. For example when there is a violation of the foreign key err...more >>

Start in the middle of a large record set... ..moveNext Now, assuming the cursor is sitting on record n... ..delete Compared to the *original* record set where is the cursor now? n (it was deleted but no .update) n + 1 n - 1 ...more >>

Is there any way to share an identity column between two tables so that they both use unique values? For instance, these would be the identity values for successive inserts to table 1 and table 2. Table 1: 1 2 Table 2: 3 Table 1: 4 In Oracle I could use a sequence to accomplish ...more >>

PLEASE REPLY TO GROUP SO SOMEONE ELSE CAN LEARN TOO. How can I calculate RushAvg below. Basically I am trying to use the Aliases of columns within the same SELECT to compute another column: ________________ Select into MyFlatFile ( Select TeamMemeberID, (RushingYards/RushNum) as RushAvg...more >>

Hi All, Can someone help me figure out the proper functions to update a column with part of another column? Basically I have a column with phone numbers. I created a new column specifically to hold Area Codes. I need to get the area code from the phone number and update the AreaCode column. ...more >>

Hi, I have one table with 14 million records. I am trying to update 3 million records in that table. Update query is taking more than 20 mins to update 3 million records. I am using all possible where conditions. Can anybody suggest some tips to tune the update query. Already i have po...more >>

How can I use T-SQL to remove CR/LF from data I need to migrate into MS CRM? I am bringing data in from Outlook and it apparantly has some sort of line breaks that are causing individual records to be imported as 2 or more records. How do I write T-SQL to remove these breaks? IOW, how do I...more >>

The core problem... we have a calculated production number for January and another number for February, but when we calculate the value for "the year through February" it doesn't match January Result + February Result. These values come from multiple records per month. I've been researc...more >>

I'm trying to write a stored procedure and I get the following error when I try to save the procedure: Heterogeneous queries requires the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. Enable these options and reissue your query. I thought I set these options by righ...more >>

I have two table, Header Table containing Header records for Client files, and Breakdown Table containg breakdowns of the Header records Both tables have a varchar (20) called LedgerKey, which links the records together. The ledgerKey is Unique in the headers table If a record in the...more >>

Hi, I have one table with 14 million records. I am trying to update 3 million records in that table. Update query is taking more than 20 mins to update 3 million records. I am using all possible where conditions. Can anybody suggest some tips to tune the update query. Thanks in advance...more >>

Hi All, I have a work table from which i need to insert the data to the master tables. This iam doing it using a cursor (for i need the info on what records have gone in successfully and what aren't.) But say incase in the middle, if the 5th rec is going to fail --> because of primary key v...more >>

Hi, I am still working on this cross tab query, I started by using Access, created a query and called it "jen", this is the query: SELECT Atts.UserId, Users.MailStr, Users.MaidenNm, Cases.Id, Cases.CaseId FROM (Atts INNER JOIN Cases ON Atts.CaseId=Cases.Id) INNER JOIN Users ON Atts.UserId=U...more >>

Hi, When I execute my code for the first time, the data remains untouched, but on executing it the second time, i get the desired results. Why? SET NOCOUNT ON DECLARE cnames_cursor CURSOR FOR SELECT CUST_NAME FROM dbo.T_CUSTOMERS FOR UPDATE OPEN cnames_cursor DECLARE @custna...more >>

is it possible use an optional parameter with a user defined function in SQL server? Thanks...more >>

Hi, I've a problem with a delete statement containing an Opendatasource table and a local table, which are joined. SQL: DELETE AK FROM OPENDATASOURCE('SQLOLEDB','Data Source=myServer; Initial Catalog=myDataBase; User ID=myUser;Password=myPassword;').myDatabase.myDBUser.myTable AK LE...more >>

I was asked by another software engineer where the "main table" of a relational database was. I have not worked with relational databases extensively, but never really heard this term applied to one. What is really meant here? Thanks ...more >>

Hi In our system I have discovered that a stored procedure, changed (but apparently not well tested) by some of my colleagues to use a table variable @TaskOrg instead of an earlier temporary table #TaskOrg. It works fine most of the time but in the code part shown here it goes wrong be...more >>

I'm trying SQL Server 2005 beta 2 "Yukon" because i'm interested in some functionalities, such as using OLAP Services, XQuery and XPath over XML files and the metadata management. Unfortunately, I'm just able to load my database, but cannot use OLAP services (with Intelligent Business Studio) be...more >>

I'm getting the following error even after installing SP3 Server: Msg 7607, Level 17, State 1, Line 1 Search on full-text catalog 'egpl_kb_index_on_attachment' for database ID 8, table ID 1694629080 with search condition '"want"' failed with unknown result (80040406). Please help me in this r...more >>