I have a character field I am exporting from the database. This character field holds order numbers with leading 0s...so 0000345. I would like to know how I can export this field, but woith adding a single quote to the front of the field...so 0000345 is exported as '0000345. Many thx ...more >>

Hi, I am looking for some help with the following requirement I have come accross; Looking on the data in Table1 there are 2 logical groups. The first group is records 1 to 5 and the second group is records 13 to 15. The first row of each group is identified by the status column with a ...more >>

Hi, I am trying to catch a critical error. The code below should deliberately fail as it tries to insert an string into a table attribute. Sure enough the update fails but I can't seem to catch the error UPDATE IdentityCheck SET DateInserted='INVALIDDATE' if @@error<>0 BEGIN print 'An err...more >>

Hi, Why there is not object oriented stored procedure? ...more >>

Hey there,,, can anyone see why this statment is deleteing all records,, not just the ones returned in the select statment? the select stament selects the correct items,, but it deletes them all. DELETE FROM tbl_Staff_Allowed_Course WHERE EXISTS (SELECT tbl_Staff_Allowed_Course_1.staff_numb...more >>

I'm trying to optimise queries where IN (SELECT ...) has been used heavily and I would usually replace IN with EXISTS, and in 2000 I would invariably see an improvement in the query plan and in the execution time, but I'm not seeing thisin 2005, the plan is the same and the time is the same. H...more >>

In QA (using sql 2k), to change a primary key from clustered index to non clustered does it need to be dropped and then readded or is there a way to change the fact that an index is clustered or not without doing that? Keith ...more >>

i try using the dts TO TRANSFER database but on second time i just want copy the new data or update data.But DTS just allow me to replace existing data or append data and must drop the existing database? Any method can help me transfer the database from server to the other server by usin...more >>

I have a table that over a year or so will come to about a million rows. The primary key is INT and identity and clustered (because sql 2k makes it clustered by default, I didn't set that). I haven't messed around with clustered indexes yet. But some of the reading I've been doing makes me think...more >>

Hi, Will columns made as null take memory if there is no value in that column? I have a couple of columns of type uniqueidentifier and varchar which can be null. Thanks in advance. Cheers, venkat ...more >>

Please take away this limitation: "The query has exceeded the maximum number of result sets that can be displayed in the Execution Plan pane. Only the first 250 result sets are displayed in the Execution Plan pane." @@Version: Microsoft SQL Server 2005 - 9.00.2153.00 (Intel X86) Ma...more >>

I am trying to import some .dat files(exported from Sybase) into SQL Server 2005. The dates is formatted as such: Sep 4 2003 12:00:00:000AM Jul 31 2003 12:00:00:000AM etc. Anyways, in SQL Server 2000, this was easy to deal with using the DTS and the convert function. I cant seem to find ...more >>

This is hopefully a simple oversight on my part. I'm trying to use a parameter (@Locations) to pass in a list of integer values: -------------------------------- CREATE PROCEDURE spWhatever @DateRange_begin AS smalldatetime, @DateRange_end AS smalldatetime, @Locations AS varchar(1000)=...more >>

I don't think this is out there, but what the hey. I'm wondering if there is any simple way to invert the "for xml" function to perform an update. Like you take a record(s) and pass them as Xml to another procedure which then turns that xml into a set of rows in a temp table. I could do this wit...more >>

Hi, I have a table like taskid duplicatedID 1 null 2 1 3 2 when I specificy 1, it'll give me 2 and 3, even though 3 is indirectly related to 1. when I say 2, it'll just give me 3. I have implemented a recurisve store procedure, every interation, i did something ...more >>

I tried this: EXEC sp_addlinkedserver @server = 'MYremoteSERVER', @srvproduct = '' , @provider = 'SQLOLEDB', @provstr = 'DRIVER=SQL Server;SERVER=MYremoteSERVER;UID=sa;PWD=pass;' But this did not work. I looked in sysservers and saw myremoteserver listed, but myremoteserver...more >>

I have been asked if anyone on our team uses the extended stored procedure command shell. Is there a way to know if this proc is used without me having to ask each developer if they are using it? ...more >>

Hi Hope someone can help me out, I have the following problem. I have two tables one called tbDespatch and One Called tbOrder and what I want to do is create a report based on query which gets the summary of all products ordered grouped by product code and order number from the Order table. I ...more >>

When using JDBC to connect to SQL server 2005 express, I got "The TCP/IP connection to the host has failed. java.net.BindException: Address already in use" error. I saw such error when I were using a fast database machine (3 GH). I didn't see such error when I use the db on my local desktop...more >>

Hi all, I have a table: CREATE TABLE [Person].[Contact]( ... [LastName] [varchar](60) NOT NULL, [_SSN] [varchar](9) NULL, [SSN] AS ([Person].[fnSSN]([ID])), ... ) ON [PRIMARY] Abbreviated for brevity's sake. fnSSN is a function meant to hide the real data in certain user-role / a...more >>

I have a stored proc (most code below) that I want to return a new VendorID and it works. ALTER PROCEDURE [dbo].[fd_updCheckRequestOracle] ( @FileNumber int, @OracleID int, @InvoiceNo nvarchar(50), @InvoiceDate datetime, @SybronNumber nvarchar(6), @DatePaid datetime, @Oracle...more >>

Gurus, If I do this- SELECT * FROM foo WHERE ( foo.this like '%candy%' OR foo.that like '%cigarettes%' ) AND foo.boy not like '%Sally%' AND foo.girl not like '%Billy%' The result set does not return nulls in the boy or girl field. I end up having to do this: SELECT * FROM foo...more >>

Hi All I have the following 4 tables... CREATE TABLE ATFLogin.applications ( appid int identity primary key, appname varchar(50) ); CREATE TABLE ATFLogin.lobs ( lobid int identity primary key, lobname varchar(50), lobdesc varchar(100), appid int, foreign key (appid) referen...more >>

I'm new to SQL 2005 and it seems the wizard to create stored procedures is not included as it was in 2000. Can anyone suggest one that will allow me to select the tables and create the insert, update, delect, & select procs? Thanks....more >>

Hi, I created the following batch script to invoke OSQL and run a stored proc with an output file. SET SERVER_NAME=ABC SET DATABASE_NAME=master SET LOCATION=C:\"My Folder"\Temp SET Query="EXEC master..proc_runtask @dbname='master'" osql /E /S %SERVER_NAME% /d %DATABASE_NAME% /q %QUERY% >...more >>

Here is a fun one. Is there a way inside a SQL query to check to see if a linked server is working, (That there is some thing there and able to respond) and if not to either continue on its way check other linked servers with out blowing up? Thanks -Matt- ...more >>

I have a customer who wants to create ad-hoc Microsoft Access queries but the table structures in their SQL Server database are too complex for them to understand. So I created several SQL Server Views to simplify the data. The intent is to provide an Access database with links to the views. ...more >>

I need to return 0 if something is null and 1 if it's not. Here's what I'm doing: DECLARE @x INT, @y INT SET @x = 1232 SET @y = ISNULL(@x/@x, 0) The above returns 1. If I change the value of @x to NULL then the above returns 0. It works just fine. Is there a faster or better way to do t...more >>

Hello, I need to modify a trigger and want to selectively update fields in a table based on their changing or not Can I use an if statement in-line in my update statement. Here is a sample. update Loc_Info set CURRENT_YEAR = case when i.READY_TO_POST = 1 then i.ASSESS_YEAR ...more >>

We have a stored procedure that when called, runs just fine. However, when we call it from within Crystal Reports, it returns the following error - --Msg 512, Level 16, State 1, Line 12 --Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >=...more >>

I am finally getting a chance to use SQL Server 2005, but I am having problems importing for the first time. I am logged in as 'sa' and using the import data wizard. The table is 5 columns and 50 rows of data. I am getting the error: Error 0xc0202049: Data Flow Task: Failure inserting into...more >>

I am using SQL 2000. When using BeginTrans in VB/ADO, will it lock the database for other users while it is in between BeginTrans and CommitTrans or RollbackTrans ? I mean, once I issue a BeginTrans, will anybody else be able to use the database until I issue either CommitTrans or RollbackTrans ...more >>

I have a table with 15 million rows, running on a SQL8 test box, and a SQL7 production box. I need to convert an attribute from char(1) to integer. some of the rows have a space in the attribute, which I need to convert to null prior. I found that I get this message: starting insert to RS...more >>

I am storing WHERE conditions in a variable and applying it just ahead of a GROUP BY keyword. The transaction runs without error if I use the content of the variable (I used PRINT and copy/pasted the return to ensure I included all spaces etc) but when I declare and use the variable I get the ...more >>

I have a couple questions, and am a little bit confused. 1) Performace wise, is there any difference in using left outer join vs right outer join? 2) I have seen in some posts where people have used "SELECT TOP 0". to my understanding select TOP 0 would fetch nothing, so why would one want...more >>

Hello guys, I would to do a stored procedure that evaluate clustered indexes performance, but I have a little problem on that telling me that: Server: Msg 137, Level 15, State 2, Line 25 Must declare the variable '@mytable'. CREATE PROCEDURE sp_ClusteredIndexEval @mytable varchar(100) AS ...more >>

I have a table with Full Text index on it, the table has at the most 300 records. The database is on the live server and gets hit a lot. Every morning when i come to work the table is inaccessible, meaning any query that i issue to this table runs indefinitely, Select, update, Insert all j...more >>

Hi SQL 2005, transacionlog growing and don't free space when backed up. If I look for open trans I get a replication item but no spid... Also in sys tables it shows the db being replicated. Backed up of DB. log switching between recovery models nothing frees log space. Help would be app...more >>

I am having trouble with my attempt to pass in a parameter to a stored procedure in SQL Server 2005. The stored procedure contains a WHERE clause, with the IN keyword specified to use an input parameter, as follows: SELECT ...[snipped]... WHERE (device_id IN (@devices)) I am passing in...more >>

Hi All, We Use VB and SQL Server for our Application Development. I would like to what type pf Permissions do developers need in the Development ENvironment. Our DBA is dead aginst giving Developers DBOwner permssions. Typically our VB applications use a single Id which we call the Applicat...more >>

I have a stored procedure used by a program to get rows to print. The query needs to supply multiple copies of the row based on a count that is in one of the columns returned as part of the query (I can't change the program to use the count). Example, if the query returns a row wiith a quanity o...more >>

Hi, Here is scenario where I need to join two tables and get results as illustrated: I have two tables: where T1 is not normalised T1: Column1 Column2 ======= ======= 1 A 2 A, B, C 3 A, B 4 C 5 ...more >>

Hi, Here is scenario where I need to join two tables and get results as illustrated: I have two tables: where T1 is not normalised T1: Column1 Column2 ======= ======= 1 A 2 A, B, C 3 A, B 4 C 5 ...more >>

Hi All, Can you help me with this since I am new to Sql programming? I have a table for students of a school in the form of (nchar(20) name, int year) where each record (name,year) means student with that name was in school at that year. How can I find for each year the number of students in ...more >>

I've not needed to use grouping before so Im looking for guidence. My View ---------- Project_no ProjectName Week_no RawCost XXXXX1 ARK 2 20 XXXXX1 ARK 3 20 XXXXX1 ARK 4 2...more >>

hi, I am using Material Path method to build a very deep level (~100,000 levels) hierarchical tree table, the problem is the path column's size limit. I need to do paging on the hierarchy table, so can't use Stack Method. Moreover, fast record retrieval is important, it has to be preordere...more >>

I really thought I understood OUTER JOINs but obviously not! I would expect both of the following LEFT OUTER JOINs to return a single row when there are no matching JOINed records however only the first simplified one does. Can someone help me with what is wrong with the 2nd JOIN? I thoug...more >>