Hi, I am trying to use an ADODB.Command to execute a stored procedure with som parameters but gets the following error: ADOError Number:-2147217900 ADOError Description:[Microsoft][ODBC SQL Server Driver][SQL Server]Rad 1: Felaktig syntax nära 'lsc_getAllMatchesToLab'. ADOError Source:Micr...more >>

I'm learning SQL and I'd apprecite any links or references to books or articles that provide *introductory-level* SQL puzzles and answers. I googled this and found Celko's puzzles and answers, but that's way over my head. Any ideas? Thanks! ...more >>

hi there is it possible to have 2 conditions within a switch expression like the following =switch(Fields!ReferrerDept.Value = 56, "x Branch", (Fields!ReferrerDept.Value = 22 and Fields!ReferrerName.Value = 'name'), "Self Generated - Name", true, Nothing) Thanks...more >>

hi, most of a column values are like xx.00, how to find out the value have more than 2 decimal (xx.001 or xx.xxx...) Thanks. ...more >>

I need to write a DTS package that imports some records from a remote server, but only those records that actually already exist in the local database. How can I do that without importing every record from the remote server into a temp table and ignoring everything other than the records that a...more >>

hello, i am looking to use a trigger when a row is added to a table. however i need to kick of an external application. i can create it in vb, c, c++ etc... can triggers kick off an external program? thanks ...more >>

Can I use paremeters in Cursor within the SP as: DECLARE c_Stbls CURSOR FOR SELECT @columnparemeter FROM @tableparemeter where both @columnparemeter and @tableparemeter are input paremeters from the SP. How do I acomplish this within a SP ??? Thanks, Tomd...more >>

Hi, I have a table that consists of sales by employee by month and because there are more than one sales category, there might be multiple entries per employee per month. "Hours" represent the TOTAL number of hours worked for a particular month and it is the same for all sales categories w...more >>

Is it possible to have the value of a select stmt be passed directly to a stored procedure? Ex. exec sp_somePro @foo=(SELECT bar from aTable) i understand that you could create a variable, and initialize it before hand, but was curious if you could do it all in one step. in addition, i bel...more >>

I have would like to insert n rows where fieldA = 1 When I insert the new rows, I would like to set FieldA = [some value] I see in the docs that I can insert a range like this: <code> INSERT INTO Tbl_A SELECT * FROM Tbl_A WHERE FieldA = 1 </code> Is there anything I can do to change the v...more >>

I have an application which connects to a sql server database thru a SQL Server ODBC driver. In the application, I have a database componenet with its aliasname name set to the name of the ODBC driver. I have several databses on the server and I need to know if I can get the actual name of ...more >>

Hello, What is the syntax to pass a table name to a stored procedure? I know how to pass other variables, but can't get this to work.. Right now I have (and it doesn't work): CREATE PROCEDURE storedProc1((@rateID int, @table varchar(20)) AS SELECT * FROM @table --this is t...more >>

Aliquot table contains columns PatientID, AliquotTypeID, LocationCode, AliquotNumber. The query is to get the locationCode with minimum AliquotNumber in a (PatientID and AliquotTypeID) Group. select locationCode from aliquot a where a.patientID = 1 and aliquotTypeID = 1 and a.aliquotNumbe...more >>

I am trying to conver a binary column in the source table to a varchar as shown select convert(varchar(32),0x00000000000003F3) it retunrns a null hoe do i go about doing it? Thanks ...more >>

Hi, Is there any way to accomplish this (SQL Server 2000): Client-App sends an Update to an SP. The SP has to perform various actions, amongst one setting a FK. Normally there will only be One FK possible per Update. BUT It can happens there are multiple FK's possible for One Update. Unde...more >>

I have written a stored procedure which contains a Select that returns a recordset, and returns a pair of OUTPUT values. The Recordset returned is correct, but I cannot access the OUTPUT args until I close the recordset. I've seen reference to this in SQL 7, but not for SQL 2000. I need th...more >>

Hi, experts I have ~ 50 table with the same structure in 4 databases. These tables are union'ed in partition view. Insert into partition view works perfect. I would like to load data into PV with bulk insert from text file. But there is the restriction of PV - I cannot do the bulk load into P...more >>

Hello. I was wondering what is the best way to kick off multiple procs trapping the ones that had an error. Here's an example of what I came up with. alter procedure dbo.testError @problem int = 0 OUTPUT AS set nocount on print 'start' Declare @error_msg int set @error_msg = 0 set @er...more >>

Hi, I have a simple update query as update tblApplication set TotalSworn = MaleSworn + FemaleSworn, TotalCivilian = MaleCivilian + FemaleCivilian, GrandTotal = MaleSworn + FemaleSworn + MaleCivilian + FemaleCivilian However, I need to build a stored procedure out of the above with the fac...more >>

I have a project to update one of our program's database from one version to another. Additionally, I will be migrating the data from one database to another. Here's an example: mydb7.1 mydb8.0 Both databases exist in the same database server. So mydb7.1 contains production data for...more >>

In a stored procedure I need to test a parameter to see if it is numeric or not, can someone tell me how to do this? CREATE PROCEDURE [dbo].[Stuff] @rd as varchar(50) AS DECLARE @QRY AS VARCHAR If IsNumeric(@rd) = 1 BEGIN SET @QRY = "select f1, f2 from tblRD where id = @rd" ...more >>

I have two databases, one for DATA and another for GIS data. Each customer has a table which stores a 3 letter code. This code is different for all customers. For each customer the GIS db is labeled like so (<3 letter code>_GIS). I would like to create views that are stored in the DATA db, but...more >>

I have a table that have 4 fields of interest, A, B, C and D (plus others that are not of interest). I am not interested in completely unique rows (based on the fields A,B,C & D). What I am interested in rows where the A, B & C fields are identical, but within these identical "groups" th...more >>

I have SELECT query which include 2 UNION operators. SELECT ..... UNION (select..... UNION selec....)as t1... If I execute this query in query analyzer, I get 7 records as a result. If I create new SP and copy the same query into this new STORED PROCEDURE and execute this procedure, ...more >>

Sorry for dup. Please ignore the previous one. Thanks. Hi all, How can I return the results below. Any help would greatly appreciate. The business rule is show below. IF OBJECT_ID('Tempdb.dbo.#Policy_nb', 'u') IS NOT NULL DROP TABLE #Policy_nb GO CREATE TABLE #Policy_nb ( ...more >>

How can I concatenate a column of data that will be grouped together on a separate column? Thanks, Tony...more >>

Hello, friends, Is there a way that we can select records from a table whose name can be determined at runtime? For example, CREATE PROCEDURE dbo.sp_GetAllRecords @fieldName VARCHAR(50) @tableName VARCHAR(50) AS BEGIN SELECT @fieldName FROM @tableName END Of cour...more >>

Hi, I have a query that needs to filter on certain data only if needed. eg. (Assume tblStock and tblFilter are 1-to-1 on StockID) DECLARE @Min TINYINT , @Max TINYINT SELECT @Min = 5 , @Max = 10 SELECT StockID FROM tblStock INNER JOIN tblFilter ON tblStock.StockID = tbl...more >>

In a stored procedure that performs a search on a Client table, one condition in my WHERE clause is: WHERE (ISNULL(ClientPhone, '%%') LIKE '%' + COALESCE(@ClientPhone, ClientPhone, '') + '%') This allows me to search on the ClientPhone column depending on whether or not the @ClientPhone p...more >>

Server: Msg 296, Level 16, State 3, Line 4 The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value. I'm doing a simple select, one of the values is the concatention of a CASE statement and two other fields. I've got no date specification o...more >>

Hello all - I am trying to create a view that will calculate a category field value based on several conditions regarding another field. i.e. having a table PayCodes (employeeID, paycode) employeeID paycode ---------------------------- 1 01 1 02 ...more >>

I need to run a given SP (@chvSpName) with a given parameter list (@chvSpParm) and get the return value. The problem is that @chvSpParm can contain any number of parameters depending on @chvSpName. I get "Incorrect syntax near '+' with the following: exec @intStatusCode = @chvSpName + ' ' + @c...more >>

Hi, I would be grateful if someone could tell me if there is any performance difference between the IN and OR statements when searching for multiple values in the same field. For example type = 'DVD' Or type = 'CD' Or type = 'VIDEO' and type in ( 'DVD' , 'CD' , 'VIDEO'...more >>

Here is the beginning of my trigger: CREATE TRIGGER CloneStatusUpdate ON nxscuste FOR UPDATE AS DECLARE @d_num int, @status char(16) SELECT @d_num = d_num, @status = status, FROM updated I want @d_num to equal the value of the 'd_num' field of the row that is being updated. I though s...more >>

Is there some way to bypass a transaction, when doing a insert. Meaning when i'm inside a BEGIN TRANS and wants to do a INSERT that will not be removed if a ROLLBACK is done, is there any way to do that??? Example: BEGIN TRANSACTION INSERT log_table values ( 'Survive' ) ROLLBACK TRANSA...more >>

I need to ask what is exxactly meant by length of certain field in SQL Server database design.Let us say i want to have a user registration system on my website, now in suuch a scenario there would be trillions and zillions of users from all over the world.Now what data type and length i must ...more >>

In the old days..with DbaseIII, if two tables were indexed on an ID field, at the top of the program, I would "open" this relationship by using the right syntax and "boom" ..it's done.. the record pointers are at the top of each table waiting for a command. In SQL, it appears the that JOIN ...more >>

Can someone explain why this sql statement does NOT work. I want to use @test for both: a column and table paremeters: USE pubs DECLARE @test varchar(30) SELECT @test = 'TEST' SELECT @test, count(*) from @test Assuming that table TEST is in the pubs database and have 10 rows. I should ...more >>

I have a table with about 100 fields in it. I want to write a stored procedure that will take an xml document and insert rows into the table. The format of the xml document is 1 node per column in the table and the node name matches the column name of the table. I have written sql to take t...more >>

Hi I am looking for a script that will generate all the foreign keys, defaults and check constraints only from all the tables in my database. Can anyone point me in the right direction?...more >>

Hi All, Wonder if anyone can help me with this problem. I am an MS Access programmer recently moving to SQL Server. I have inherited a database with a table I need to add an Insert trigger to. This trigger needs to manipulate the data that was just entered. However the table has no unique i...more >>

I want to validate the correct sintaxis of a free-text in a form (supposedly a valid SQL instruction written by the user). I'm planning to use sp_executeSQL, evaluating @@ERROR. This is correct, or there is a better alternative? Thanks in advance...more >>

Hi! We were running a long "archival" job in SQL server for a database. The job does alot of DELETES with a single transaction. The job was running for a very long time so we canceled it. Now it seems that when anyone accesses the database, they are getting spiratic errors. We've have tr...more >>

Hi, I was an oracle programmer recently moved to MSSql. So I'm trying to learn the right way. Oracle was very cursor oriented & MSSQl seems to seriously discourage using them at all wherever possible. In this situation here's what I roughly written, with cursors - it'll give you the...more >>

Hi, I am retrieving records from the sql server database. It basically returns 4 records as follows Col1 Col2 ... Coln xxx xxx D xxx xxx M xxx xxx P xxx xxx V ...more >>

Hi, I am retrieving records from the sql server database. It basically returns 4 records as follows Col1 Col2 ... Coln xxx xxx D xxx xxx M xxx xxx P xxx xxx V ...more >>

I have a linked server setup in a SQL Server 2000 environment that links to an Oracle database. I then have a Trigger on a SQL Server table, that on insert, inserts a row of data into the Oracle table via the linked server. All of this is driven by a front-end application written in VB.NET. ...more >>

I am importing from a text file a field that needs to end up in a sql table as decimal type. The field has numbers and then a trailing sign (+ or -) to indicate pos or neg.. How can I convert that filed to numeric with the sign in front of the number? e.g.: 000032897+ should be 328.97 ...more >>

We have been recommended by our database designers that 20million rows is the maximum number of rows a data warehouse should be on SQLServer. What is the opinion of you guys on this benchmark? Have you seen tables bigger than that? Did you notice any performance impact. Our table is at 14m...more >>

Hello all: The following snippet will produce: keyvalue1,keyvalue2,keyvalue3,...keyvalueN that is, a comma-delimited list, which is what I want, but what I'm not quite getting is, WHY does this work? The snippet (which I did not create, and would credit if I could recall): DECLARE @...more >>

through no fault of my own, there exists a table with varchar(50) column, the values of which are comma separated integers. something like so: create table foo ( fookey int primary key not null, foointegerlist1 varchar(50) null, foointegerlist2 varchar(50) null, fooprice money null, f...more >>

hi all, we are having an asp app that is fetching a big resultset to display in a list screen. The screen has 4 or 5 columns which have the click sort option (ie., on clicking the column hdr, the list needs to be sorted out). Currently, we are using a procedure which would dynamically const...more >>

I have a DVD database. I have several categories (three) that a DVD may be in. I put all three possible topic fields in the main table that holds the title, etc. of the DVD. For example, I have: title, when_made, running_time, genre, subject, specific_subject the last three are the th...more >>

Hello all, im trying to write a script to update several SQL servers and ive encountered a problem. When im doing if(select count(*) ... ) >0 begin update table set column1 = value1 end the update will always be executed, even if the count = 0. Is this a formiliar problem with SQL ...more >>

Hi All I want to have all ideas about the use of HAVING and GROUP BY When either of them can be used? Can HAVING be used without GROUP BY? HOW? WHY? Can HAVING be used without an aggregate function? HOW? WHY? Guys, you will certainly be doing me a favour and all other newbie who access this g...more >>

Hi champs! I am trying to make a short INSERT INTO -command With an subquery SELECT statement, and it seems that it is not possible; or is it? INSERT INTO [PeopleInGroups]([PeopleREF], [GroupREF]) VALUES ((SELECT [PeopleID] FROM [People] WHERE initials = 'SSS' ) ,20) This query goes wrong....more >>

I am having a database in SQL which is of size 6GB. My application will be logging the data into database, which makes increases the size of database.(currently it is growing by 10%). I am running a query againest the database, which refers many tables, which is taking around 5 Minutes. I a...more >>

I a having a database in SQl which is about 6GB which is having around 90 tables .My application always keeps logging data into the database which makes the size of database increased .(currently it is growing by 10%). I am running a query which referes many tables and it takes around 5 minut...more >>

Hi friends, From where do I get the sql server error log. Example I execute the script in the Query analyzer and if i get a error. From where do i retrieve all these errors? thanks vanitha...more >>

CREATE TABLE [INTIMINGS] ( [EMPID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [INTIME] [datetime] NOT NULL , [EFFECTIVE_DATE] [smalldatetime] NULL , [END_DATE] [smalldatetime] NULL , [ENTERED_BY] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , CONSTRAINT [P...more >>

I have the following query and just cannot seem to remove the table scan on Table2. I have posted the indices from all tables in the join. I think Ive covered everything according to rule. but is it overkill? select count(a.Vh_SummVeh_id) from Table1 a (nolock), Table2 b (nolock), ...more >>

table1: In timings history EMPID pk INTIME EFFECTIVE_DATE END_DATE ENTERED_BY tabl2 intiming EMPID pk INTIME EFFECTIVE_DATE END_DATE enteredby ta3 emptimings ENTRYTIME pk EMPID TIMETYPE INOUT IP_ADDRESS i want to calculate emp salary after deducting leaves ...more >>

programmers are writing a loop in .net and passing empid,date to server there are 700 employees so the round trips are increasing ----------queries select '8/5/2005' as daysabsent,e.empid as empid,intime,type,cast('8/5/2005' as datetime) as absentdays,'A' from ccsmay26.dbo.employee as e le...more >>

Hi all, My table structure is CREATE TABLE [dbo].[TestforDisplay] ( [Col1] [int] NOT NULL , [Col2] [int] NOT NULL , [Col3] [tinyint] NOT NULL ) ON [PRIMARY] The data in the table is Col1 Col2 Col3 ----------- ----------- ---- 1 5 0 1 ...more >>

Hi ppl, I need to create a view which contains both SALES and CANCELLATION/RETURN data. Is it possible for a VIEW to store data from 2 sources? Eg. Create VIEW MyView (RefNo, Date, Data1, Data2, etc...) As Select (sales_table.SalesNo, sales_table.SalesDate, ..) From sales_table Where ....more >>

SQL Server 2000 Is there any way using system tables/functions to pass the name of an existing view and determine whether that view meets criteria to be updatable? For instance, I was hoping for an OBJECTPROPERTY parameter such as: objectproperty(object_id('someview'), 'viewisupdatable'...more >>

Hello I got this table (for testing).... I'm struggeling to create a view that displays the number of "entries" each person has for each month. Maybe you guys could show a proper way of dealing with this. CREATE TABLE #Test ( SomePk int identity(1,1) NOT NULL, Person char(1) NOT NULL, ...more >>

I have a table Named UserTab Where I keep all the details of a User with the following Structure (UserID int Identity(1,1),FullName varchar(96), Pass varchar(32), DOB smalldatetime, address varchar(64),IsMultiSignUp bit) Also i have another table names MultipleSignUpInfo ( UserID int, UserCount ...more >>

Hi, I'm getting the following error message for the dynamic SQL code below: " Syntax error converting the varchar value 'SET ' to a column of data type int. " Is it possible to execute this statement without using SP_EXECUTESQL ??? Your help would be greatly appreciated. DECLARE @BdC...more >>

I have developed a .NET Stored Procedure in C# (SQL 2005 sept CTP, VS 2005 RC1). 1. I'm passing an T-SQL output variable into the .NET function. For example @TSQL varchar(max) 2. Inside the .NET procedure the variable is of type string (for example: ref string SQLText). 3. The .NET procedure...more >>

Hi Where can i find the information regarding Optimizing SQL Code by Execution plan regards Lara ...more >>

What's the hands-down, no-questions-asked, BEST VCS and/or change management systems for SQL Server 2000??? TIA! Robert ...more >>