I am creating a temp table in side a "IF" statement in a Steored Proc which looks like this . IF a = b begin create table #temp end else begin create table #temp end I am not able to compile the SP as the error being "The object #temp already e...more >>

Hi, I have the foll csv file 01,12111 02,14155 05,11011 10,00114 34,00001 03,11000 I have the following code that parse the text file and and import in sql = server table, it import perfectly except it is removing leading zeros so = 01 is returned as 1. Why? With Con1 ....more >>

I am think about using SQL Server as the database for my application. Would my end users have to own and have SQL Server installed? Acess databases will run without having Access installed. Do SQL databases work the same way? Thanks for the help....more >>

I'm quite new to SQLServer and I have a question about indexes. I understand the logic behind creating index and some of the thinking that should be used when decided upon which columns to index, however I don't understand where the existance of multiple indexes on the same table comes into ...more >>

Hi All, I want to know what character set used by SQLServer2000, so I run sp_helpsort and ServerProperty('Collation'), below is the result : sp_helpsort => ---------------------------- Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode D...more >>

What is your guess if we zip about 2GB database (backup file), what would be the size of the zipped .bak (.zip) file. Before that, is it possible to zip using Winzip or some other utility to zip such a big file. The reason I want to .zip because someone has to send me the datbase on CD...more >>

Hi, i'm working on a stored procedure for any single database which will list out the table name, column name, data type, column length, and description for each table and its rows. I follow from some other posts to add these separately: the INFORMATION_SCHEMA.COLUMNS and fn_listextendedproperty...more >>

Hi Sql Gurus. We get the montly reports by executing the Sql Queries, by copying the result set from query analyzer to excel sheet. Next task is we will format the excel sheet by removing the Nulls, set the font size, align the width of each cell bold the header etct etc. I know t...more >>

I am creating an Excel file from DTS. Problem is I need to add a password to the file after it is created - how can I do this? -- Jonathan Blitz AnyKey Limited Israel "When things seem bad Don't worry and shout Just count up the times Things have worked themselves out." ...more >>

Hi I have a whole series of databases with exactly the same schema all on the same SQL Server (2000 sp3a). I've written a cursor that gets all the database names from the master table. I iterate through the cursor's list of databases. I have a variable @sqlStatement AS NVARCHAR (4000) t...more >>

I have an SQL2000 Bill of Material table (dbo_BOM) that has the fields of Parent, Child, QuantityPer.) We have many multi-level products where the Parent number is also a child number in another record. There may be up to 6 levels deep of this relationship I would like to make sn SQL view t...more >>

Hi all, I have a WHILE loop that looks like this: WHILE @@FETCH_STATUS = 0 BEGIN SELECT @CampaignName, @CampaignID FETCH NEXT FROM CampaignList INTO @CampaignName, @CampaignID END How do I Select the data all into one Result Set? Each iteration of the while loop creates a ne...more >>

Hi, When I run the following query: Select vcname, convert(varbinary(16),vcname), len(vcname) From Temp1 I get the following result: DUMAS 0x44554D4153000000000000000000 14 DUMAS 0x44554D4153 5 Any idea how I can group these two records by ignoring the hidden characters...more >>

Hi, We are in the process of moving our development site to a production box. Today I tried testing it and my database connections fail. I get: Microsoft OLE DB Provider for SQL Server (0x80004005) Login failed for user ''. Reason: Not associated with a trusted SQL Server connection. n...more >>

i have a table with a smalldatetime field . how can i do a delect on the field to return all records with a recieveddate = getdate ie select * from table1 where Recieveddate - Getdate() Thanks...more >>

I'm trying to do a UNION qry but am getting a syntax error on the union. I'm not sure why but seems to be something due to the order by. I'm trying to union a list of the top 5 companies and their premium with the subtotal premium of all the other companies. Any ideas would be helpful. SELECT...more >>

Hello There! I am trying to find out what is the fastes way to load a text file containing 3,876,240 rows into a table that contains the same column definition. Before I did it table to table and it took 1hr 8 min, there is gotta be a faster way to do it. I am using DTS for these oper...more >>

I created a SP to delete unwanted data from a table. I need to execute the SP each nite. Can this be triggerd/executed automatically somehow? Mike...more >>

Using SS2000 and VB6. I'm getting this error when debugging the VB program: Parameter object is improperly defined. Inconsistent or incomplete information was provided. Here's part of my VB code: ' check for duplicates ' Set up a command object for the stored procedure. Set cmd...more >>

I created a stored procedure create proc getcontact @acno varchar(30) as select * from contact1 where accountno = @acno When I execute this sp in the Query Analayser like this getcontact '95112933591388844Gab' it works. But when I try to execute from my asp.net code, Dim Co...more >>

Hi; I am using the following to get the data from Excel into a db table, but for some reason the Zip column (defined as nvarchar(64)) is coming in NULL for non-numeric Zip Codes.... Your insight is appreciated! insert into dbo.sys_load (FirstName, LastName, Address, City, State, Zip, Count...more >>

I have a multi step job that runs and one of the steps is a SELECT statement. The step after the SELECT statement depends on the output from SELECT statement. What is the amount of time that a select will run without return the values? Thanks Mark _______________________________...more >>

Is there a way to ignore the index on field that has index within a SQL statement? Within Oracle this can be completed. Example listed below. Index field Number: SELECT * FROM class WHERE number = 30; Ignore the Index SELCT * FROM class WHERE number+0 = 30; Please help me with ...more >>

In the following example, the t2 table doesn't even have that keyID field. The query still runs and give out results. Why? Is this a known bug? /* create table t1 (rowid int primary key, keyid int, rowname varchar(100)) create table t2 (rowid int primary key, rowname varchar(100)) ins...more >>

What is the default amount of time that a SELECT statement will wait before it errors without returning data? Can this wait time be specified in a SELECT statement? Does this wait time take into a count that a table is blocked? Thanks, Mike...more >>

Hi, I was wondering if anybody knows if it is possible to make http calls from a SQL Server 2000 stored procedure. Any info would be greatly appreciated. Thanks! Fred ...more >>

Hey all, I am running SQL 2k with Access 2k. I am using SQL Server with Access as the front end for the forms. I have code in Form_AfterUpdate that forces a Recordset.Requery when a record is added so that the record just added appears in the recordset with it's joins, and thus appear...more >>

I have a view that returns data from several tables I am trying to select the newest records from. Below are the fields; TransID TransDate ExpDate TransType CustID 1 01/01/2003 01/01/2004 4 1 2 01/01/2003 5 ...more >>

Background: I'm writing a stored procedure that parses out name components from an = email To, CC, BCC, and Author columns and then uses those name = components to determine which existing contact rows should be related = and then creates the relationship. I have the logic worked out for = e...more >>

Hi, I am currently backing up to my machine on my localbox of the database I want with enterprise manager. I would like to do an automatic backup of my database running on a schedule on a networked shared/remote machine? How do I do that? Thanks, :DHRUV...more >>

Is this possible ? I have db chaning turned on, and I can succesfully create views to tables that are in other database without having to setup permissions to the underlying tables. But, now I need some of the views to be created with SchemaBiding so I can create indexes on them. When ...more >>

Greetings, I am posting this message to both the SQL Server and C# news groups because this inquiry is more of a theoretical question that applies to both database and code naming conventions. I have currently been prefixing boolean object properties and database columns with Is .. for ex...more >>

How can I use group by to filter below rows and show below result? col1|col2 1|a 2|a 3|a 1|b 2|b result: col1|col2 3|a 2|b So, I need to see only max of col1. Thanks.....more >>

I am running a program and an error keeps coming up saying it encountered a deadlock and was declared the victom. I am running sql 2000 server. How can I determine what is causing the deadlock to cancel the transaction? Any suggestions would be appreciated? Thanks! ...more >>

I have a COM object that I instantiate from a stored procedure using the sp_OA* system procedures. The COM object establishes a connection to an AS400 system, invokes a process and returns the output from the AS400 process. Everything works as expected but I fear that there will be times, hopefu...more >>

I have a stored procedure which queries and inserts information on a local server and a linked server. When you use the query analyzer to EXEC SP### it works fine. When the programmers try to execute it from their application, the query runs for a long time and chews up resourses on th...more >>

I want to get the equivalent of NOW() within a stored procedure. I have not been able to find the correct syntax to do this. Can anyone point me to information on how to accomplish this? I looked through BOL but am not finding it. Wayne ...more >>

Hi there, Does anyone know the SQL Query analyser equivalent of VB's instr? VB sample code: sInput = "1,2,3" sOutput = InStr(1, s, ",") the result would be that sOutput would be filled with 2, indicating the position of the comma is 2. I need to do a similar thing with a SQL statement in SQL...more >>

I’m struggling with ”Case When” and can’t find a Workaround or other Select statement. Please give me suggestions of what to do! This works: SELECT dInvoice.nInvoiceNo, dInvoice.nSeason, dInvoice.cOurRef, dInvoice.cYourRef, dInvoice.cInvoiceText, dSupplierInvoiceHead.cVatNo, 'Vat1' = ...more >>

Hi there, I have the following code: Declare @pTest nvarchar(200) set @pTest = '1,2,3' SELECT Col1 FROM Table1 WHERE Col1 IN (@pTest) I get the following error: Syntax error converting the nvarchar value '1,2,3' to a column of data type int. Now Col1 is an integer but as soon as I d...more >>

Hello, I have a csv file: c:\tmp\myfile.csv which has thee column: lastName, firtsName, Age. Now I want to import this data nightly to a table: myTable which has the same columns in SQL Server. I am quite new in SQL server, can someone write me a stored procedure to accomplish this? Thank...more >>

guru, I have 200 tables. I plan to write a osql to add an additional column using the guid for primary key for each table. question: what is the SQL command to create additional column and using guid for primary key? Any help is much appreciated. Jenson ...more >>

Hi there, I'm totaly new to SQL so please bear with me. I have set up three identical tables (CoursesADC, CoursesLD and CoursesAll with field names CourseCode, CourseTitle, Medium, ExpectedHours etc) and I want to put a trigger(s) on the ADC & LD tables that update the All table whenever a record ...more >>

how well does the patindex function compare to a regex in terms of functionality/power?...more >>

Hi, i'm having some problems with partitioned views. When I create the following view there is no problem. CREATE VIEW dbo.Fact_SC_Orders_Vw AS SELECT Fact_Id, BC_ID, Cust_ID, ir_key, SC_Ord_H, SC_Ord_L, SC_Inv_H, SC_Inv_L, It_No, Line_Qty, LCUR_Line_Val, LCUR_Line_ListVal, LCUR_Line_...more >>

How do I get simple Queries (Insert, Update and Delete) to run automatic at a specific time (MS SQL 2000 server). If I put them all in Query Analyzer they work fine, all togehter. Must I have them in separate procedures? The SQL contains about 70 000 characters and consists of about 100 dif...more >>