How do you save the output of a query/stored procedure to a text file? Thanks in advance...more >>

Hi - I have a table where the primary key is defined as an Identity field. I would like to copy a record and insert it back into the table. The statement I would like to use is as follows: INSERT Order SELECT * FROM Order WHERE OrderID = 1 I understand why this will not work because t...more >>

How do you get rid of the time part from the date returned by GETDATE function? Thanks in advance...more >>

How do you add a new column at a specific position (say at the beginning of the table or after the second column) using ALTER TABLE command? Thanks in advance...more >>

Hello I'm using ASP and connecting to a SQL Server 2000. I have noticed that in a few scripts a SELECT * from a Stored Procedure returns the colums, but it seems the content is not retrieved. I'll explain with an example: Stored procedure: CREATE PROCEDURE [content_edit] @contentid integ...more >>

How can I rewrite this query to eliminate the FROM clause? UPDATE ShowTime..s_Exhibitors SET SQLID=WE.ExhibitorID FROM ShowTime..s_Exhibitors SE INNER JOIN horseshowtime..WebExhibitors WE ON WE.LastName=SE.LastName AND WE.FirstName=SE.FirstName My unsuccessful attempt re...more >>

I am running SQL Server 2000 on Windows 2000 Server. OS is installed on C drive and the SQL server is installed on E drive. What should be the minimum space required in C to run SQL server properly. I got some error in SQL server during the transactional backup. Will ...more >>

Hi All, i am facing this problem... i have table "PLACEDATA" which has 2 columns ZIPCODE CITYNAME 56788 Seattle 98007 Bellevue 98009 Kirkland Is there any way that using ONE Query to get the following concatnate result like AllPlaces ---------- Seattle,Bellevue,Kirkland ...more >>

Is there a way to retrieve the current MAC Address into a View? T.I.A....more >>

How do you simulate a deadlock for testing purposes? Thanks in advance...more >>

I have a column where probably half the rows have a NULL value. How do I create a unique index on this column? Is this possible at all? Tosch...more >>

I have two tables. One is about 400 records from another SQL server The other is a telemarketing programs main data table What I need to do is, import to table 2(the telemarking software's table on a one to one basis). But, we want each new record to go though a dup check. First, phone i...more >>

Dear all, As I know, Cross Join returns all the left rows mutiplied with all the rows in Right table. And Left Outer Join also retuns all the rows in left table with all the rows in right table. So, what is the difference between Cross Join and Left Outer Join? When should I use them? ...more >>

Hi, I am looking for the complete specification of Tabular Data Stream (TDS) 8.0 specifications (packet structure, offset, flags, ...) . I have not found any reference on Microsoft sites dedicated to SQL server. Thanks. -- Nothing Ventured, something lost!...more >>

Hello, Is there a sort of switch statement in SQL? I want to do this: Record 1; value x Record 2; value y Now I want to change/switch the values, I know the record Id's. Is there a function for this? Thanks! ...more >>

Hi, is there a way I can increase the max row size in my database from 8060 to a higher number and how? thanks ...more >>

Does anyone out there have a utility program (stored procedure or UDF, perhaps) that generates COBOL copybooks (DCLGEN's) for a specified table in a SQL Server database? -- Joe Palm Senior Technical Developer Madison, WI...more >>

Hi Freinds, I find out, by running sql profiler, that there some statemnts running by specific users on server. Is there any way I be able to block those statments from running on my server? Like : I want to block the : create table ##tmp_global_table_for_trik (col1 int) which is runnni...more >>

sorry for my stupid question. My coding is update invinfo set paidinvamt = honour_account.dbo.arinvheader.paidamount where invinfo.invno = (select honour_account.dbo.arinvheader.number from honour_account.dbo.arinvheader) invinfo is the table which belongs to DATABASE_A arinvheader is the tab...more >>

I would like to be able to use a parameter to specify which physical SQL Server database a query reads the data from. So, instead of: SELECT CustomerName FROM CompanyDB..Customers I would like: SELECT CustomerName FROM @database..Customers So @database would be set t...more >>

Hi, in a time-critical system we've chosen to use SET LOC_TIMEOUT with at short value to avoid waiting for update of certain records. Though, we see that timeout-exception occurs more often than expected. There might be two reason for this: a) The record is being updated by somebody else b) O...more >>

Hi, I am having some major issues with a bulk insert operation. Here is a summary. I have a table with 28 fields. many of the fields are nullable. the row terminator is a new line/carriage return and the field terminator is a | (pipe). the statement is as follows: BULK INSERT dbo.tbl_link...more >>

How can i debug code in sotred procedure ? ...more >>

Hi, We got some wired characters spread all over the place in few tables which are screwing up the xml we built out of that. We can't even see them. They appear like space in the database. Is any body aware of any generic algorithm that can find out these type of characters so we could repla...more >>

I aplogize for this but I will likely have a slu of silly, very basic, questions for the next several weeks or months... I am completely new to TSQL programming. My date field saves data like so: dd/mm/yyyy hh:mm:ss am/pm. Which I don't want to change but I want to display it like so: dd/mm/yy...more >>

I have a coworker who is working on an application that currently interacts with SQL server. He would like to be able to port the application to other databases as well. One of the key functions is for this thing to be able to generate tables and corresponding audit trail tables. Currently...more >>

Hi, I'm a newbie so any help is appreciated!!! I have two tables: When one table is updated (RapLog) in the "DateCompleted" column, I need it to insert a row into another table (DateVisit) that selects some information from the RapLog table, and inserts it into the DateVisit table, as well a...more >>

Hi all, I have SQL Server 2000 locally and remotely - I need to setup a nightly job to copy the entire database (content at least) from the remote server to the local server, once here I then need to backup to a couple of seperate drives. I can do the second part myself, as I already have t...more >>

I need to limit the number of character returned in a few fields. For example the field may contain short description data and I only want to retrieve the first 40 characters. I could retrieve the whole field and have the app portion (vb.net with asp.net in this case) to the trimming before disp...more >>

Hi All, I need to export a table to a delimited file. One of the fields is defiend as text data type. There are carriage returns in the field and when I export it's splitting into multiple lines. I just like to strip carriage return "char(13)" but I can't seem to do it in text field. I can...more >>

Have a theory question: Which is FASTER a single select statment with 8 WHEN clauses or 8 update statements. I assume the case statement but I am not sure. Are there circumstances when one might be faster than the other? -- Rex ...more >>

My error: Microsoft OLE DB Provider for SQL Server error '80004005' Login failed for user 'UserName'. Reason: Not associated with a trusted SQL Server connection. I copied a website to my own server machine along with a database... added a username to security\logins and assigned the ...more >>

I'm trying to do something like this but I don't think that I can evaluate the expression.... case lower(@strFilterCase) WHEN 'region' THEN vwRegion.[Region ID] = Cast(@strFilterValue as int) WHEN 'state' THEN vwFacility.[phys state...more >>

I need argument about why not divide objects into different databases. Any argument appreciated. Background: My company has a lot of databases on various platforms. Developed under a great length of time.Since the quality of these databases vary a lot a suggestion has come up. Let's move ever...more >>

Can I default the datetime field in 'blank' but not null ?? Thanks a lot ...more >>

Hello, I'm trying to find an example in vb.net of how I can have a user select a MS SQL database stored locally created using MSDE. TIA ...more >>

Hello, I tried to generate a script of a database in Enterprise Manager 8 so I could recreate it on another machine for testing but it didn't script the tables. Can someone provide instructions on how to create a script of a complete database so I can rebuild it without data? Thanks! ...more >>

Thanks Dan, The assumptions are correct ie -This conversion assumes the integer contains the number of days since '1900-01-01'. If your integer contains a date value such as 20041102, you'll need to first convert it to character and then to datetime. For example: CAST(CAST(tblSiteVisit.intDate...more >>

Does anyone has any recommendation in analyzing PSSDIAG output to break down Memory, Disk I/O, CPU bottlenecks. Some sample scripts, some tools, web articles etc. ...more >>

Hello all! I have created a trigger insert in a table. But I need to insert a row at a time. So every time the trigger fires, it only affects the next row, 1, 2, 3, ect. I know i can specify a a row, with a key id. But I need to make it so it goes to the next row automaticly. Any suggestions ...more >>

We have a Web application that uses a common ID to connect to the DB and then we have secutiy implemented based on the user's logged on Id. When we check for sp_who, it always shows the same server name as the HOST in sp_who which ofcourse is right. Is there anyway I can get into more depth as to...more >>

I have a table called 'Test' and the below structure check_no int Primary assoc_no int Check_no assoc_no 113271 0 113272 113271 113274 113271 The table holds a check number. If the check needed to be reopened or modified a new check number is created and the or...more >>

I have a View with some calculated fields SUM(Field1) - SUM(Field2) - SUM(Field3) as Total When I see the result in the view it has no decimals eg. 9000 but when I retrive the value (via SQLDataReader bind to a DataGrid) it shows the number with decimals eg. 9000.00000 and I don´t want...more >>

Here's my problem, There's a problem with an insert into a temp table. The sql code is supposed to enter a header line, followed by data, followed by a footer line. It is currently being accomplished like this: insert into #exampleTable select 'header_literal_1' as column1, 'header_literal_...more >>

I want to use a store procedure to select records based on a date range but the date range can be in one of two fields depending on the data in another field.. So I want to select records with an ETA (@FindDate) within a range (@StartDate and @EndDate) if the record is an 'Import' (stored in ...more >>

I need to create a TempDB that that takes this table... BRAID RATEDEFID BRADATE RATECODE PRODNAME 614 14 12/1/2002 Rack Beach Tower Terrace 615 14 12/2/2002 Rack Beach Tower Terrace 616 14 12/3/...more >>

Hi, I have an insert trigger that modifies the rows that have just been inserted. The (simplified) trigger looks like: CREATE trigger Member_Insert ON Member For Insert AS DECLARE @transactionId AS INT; set @transactionId=(select dbo.AxGetTransactionId()); update Member set Tr...more >>

I didn't know of this, perfect. But what if I want to use this result as a column in a view, I know I should have mention this in prev post, but I forgot. Thanx Kjell B "avnrao" <avn@newsgroups.com> wrote in message news:ecbCcEYwEHA.536@TK2MSFTNGP11.phx.gbl... > not sure if you know thi...more >>

I have a fiscal calendar from my client, basically Q1 starts Feb 1 and weeks are from Sunday to Saturday. I have several (75-100) queries that need to be re-written to go by this fiscal calendar. I have imported a table with a date and the corresponding fiscal info and was going to join that...more >>

Where the heck can I find the description of a column using a query?...more >>

I'm trying to get information about indexes from the INFORMATION_SCHEMA.xxx views. I can find primary keys and constraints, but not indexes created with e.g. CREATE INDEX [IDX1_Table1] ON [dbo].[Table1]([Col1], [Col2]) ON [PRIMARY] Am I missing something? I know I can use sp_helpindexes...more >>

Hello: I have some connections opened in SQL from .NET, the application didn't close them. I need to know if there is a way to close all these connections from SQL withour restart SQLSERVER or the server itself, Thanks ...more >>

Hello: I have a WEB application written in ADO.NET. From this application I open connections to AS/400 database and my SQL Database, my ADO.NET application and the databas share the same server, the question is what is more efficient to open , uses and then close a connection for every stored...more >>

Is there anyway one can set up a password protection policy in SQL Server such as: 1. Password shouldn't be one of the 5 previous ones. 2. It should be a combination of numeral and alphabets. 3. Mixture of upper case and lower case etc.... I have SQL 2k SP3 with ID in SQL only. It's a mixe...more >>

We have just added [DeliveryRank] to this table and need help coming up with an UPDATE statement to populate it. CREATE TABLE [dbo].[DeliverySchedule] ( <other columns here> [DeliveryRank] [int] NULL , [BoxSequence] [int] NOT NULL , [SortHelper] [int] NOT NULL , [Sequenc...more >>

Hi all, I am working with Reporting Services, and they don't like #temp_table, so I can't use it. But that's no point. Now my stored procedure looks like this: ----- start of procedure ----- CREATE Procedure GEGE_anal_2 @ord int as SET NOCOUNT ON CREATE TABLE #Tbl_val ( [table ...more >>

Hi all, I need to do following.......... Current table Layout ************************** NAME ADDRESS CON1Num CON1Name CON2Num CON2Name CON3Num CON3Name CON4Num CON4Name CON5Num CON5Name ---------------------------------------------------------------------------------------------------------...more >>

I've got a stored procedure that essentially consists of about a half dozen CURSORs (one after the other, not nested), each of which processes some data. Each CURSOR has a source that is a SELECT with at least one INNER JOIN (which I cannot change). I'm trying to run this stored procedure as...more >>

Hi,there, Some suggestions or links ? Thanks ...more >>

Hi, I have following data sturcture thats needs to be changed as as desired structure, please help! Current Layout ************************** NAME ADDRESS CON1Num CON1Name CON2Num CON2Name CON3Num CON3Name CON4Num CON4Name CON5Num CON5Name ---------------------------------------------------...more >>

Hello Sir/Madam, I have not used cursors a whole lot. I have a cursor that inserts a bunch of rows into a bunch of tables with the help of a loop. My question is that if say I am inserting a 1000 rows and at row 100 I get an error of some kind, I would somehow need to rollback the transaction...more >>

Hello Was wondering if you guys had a neat solution to these 2 "puzzles". Thing is to calculate "D" from A,B,C 1) A B C D 0 1 1 1 0 0 0 0 1 0 0 1 1 0 1 1 0 0 1 1 2) A ...more >>

Hello Sir/Madam, I have not used cursors a whole lot. I have a cursor that inserts a bunch of rows into a bunch of tables with the help of a loop. My question is that if say I am inserting a 1000 rows and at row 100 I get an error of some kind, I would somehow need to rollback the transaction...more >>

I have a doozy of an SQL statement question... Not sure if it can be done one SQL statement or not... I have two tables... A customers table and a Notes table. They are related in a one->many relationship based on the customer #. There are many notes for one customer record. For the sake ...more >>

Is there a simple way to do a "select columnname from tablename" which returns 10 rows getting this rows into a commaseparated string. ex. above select would return: columnname 1 2 3 4 5 6 .. .. .. .. and so on..... I would like it in this form 1, 2, 3, 4,......... I know UDF...more >>

Hi, I need to know how to get some user connection attributes. I know that I can get the current user with the function “CURRENT_USER”. They are functions or store procedures to get the following attributes: Application that launch the database connection Computer name that launch the...more >>

Hi to free up some more space in memory,Is it neccesary to shrink db in periodical time? can you tell me the best values for parameter needed for shrinking? Is there another way to free up some more space in related drive,(cause by working with db ,the size of 2 files will grow too fast)? T...more >>

Hi all, I have the dubious please of 2 separate databases (the structures of which I cannot change), that I want to join for a simple query. select fields from table a inner join table b on a.pk = b.pk However, the collation sequences on table a & b are different. Annoyingly, the data ...more >>

I am trying to write a stored procedure for removing all relationships for a particular table. So far I have been able to get the names of all the relationships but I cannot remove them because the syntax for the "ALTER TABLE ..." line is incorrect: DECLARE @var1 int SELECT @var1 = id FRO...more >>

Searching for this problem I found lot's of people with the same problem. Non however are solved without using a dynamic sql solution. This is not an option for me so does anyone have an other solution? thnx Marius...more >>

Do I have to go through install/setup again to change the default dateformat over for the whole server? I know I can change per session (SET DATEFORMAT) but I can't find a way to do this for the server. Thanks ...more >>

I currently have a group of linked tables and I need to update several columns in on table and one column in another table! Table Structure ...::tblPage pageID fldName fldText ...::tblOfficePage pageID officeID ...::tblOffice OfficeID fldName fldDept ...::SQL FOR JUST ONE TABL...more >>

How to create a text file with the data from the table in a stotred procedure. Thanks in advance....more >>

Hi all, The command is as follows: BCP asdos+_reports..StageCONTACTINFO OUT C:\16E8.tmp -w -b10000 -t"|" -r\n -Sblrkec28791d\sqlserver -Uadmin -Padmin -eC:\ERRCONTACTINFO.txt Here, since the first argument(database name) has a special char '+' in it, the command gives a syntax error....more >>

I am using a cursor in a TSQL Script and would like to find out if it is possible to do the following: I would like to reference the contents of the cursor for comparison purposes and understand that it can be done using variables. My Cursor will contain over 60 columns so I would like to r...more >>

How do you reset or reseed the IDENTITY column? ...more >>

Hi, I was wondering if you would be able to help me with a problem I am having. I am designing a web form for the intranet at my work. The web form will be linked to a sql database which holds company files. The web form will allow users to enquire, edit, insert and delete files. I am having...more >>

Hello About six months ago, I created a stored proceedure thatfills a table. There is one parameter that is passed to the procedure. The SQL that fills this table has four unions and a quite a few cases. The problem is that this proceedure takes about one minute. If I take the T-SQL and ...more >>

I've a problem on an access sub-form. All modification of values on any field of the sub-form generates this error message : "This record has been modified by another user while you were woking on it. Do you want to copy the changes to the clipbaord?". No one is using my sql tables and there are n...more >>

Hello To All Gurus out there, Hoping you could give me some ideas as to how to work around this problem I'm having. Basically I need to create this stored procedure (Let's call it MY_SP) to call my client's stored procedure (Let's call it Client_SP) and do some grouping and filtering on th...more >>

Hello, I'm a bit of a beginner and I was wondering if anyone could tell me what the syntax error is in this UPDATE statement. I can't spot it and it's really annoying me now! The statement is declared as a variable in VBscript for ASP and has variables concatenated with it. The NoOfUses fie...more >>

Hi all, How can i get the MDAC Version which is on my system thank you Satya...more >>