Hi All I've written this stored procedure in SQL 2000. When I run it, I get an error that I've not declared a variable. Full error is: Inventory: Counting history, ID = 151 Server: Msg 137, Level 15, State 2, Line 1 Must declare the variable '@idHistory'. (I get this line for each t...more >>

Of what practical use are they? What problems do they solve? Do people actually implement them? Just curious. ...more >>

Here's some tricky SQL, looking for any guidance. I have inherited the support for 4 different MSSQL 2000 databases, each with a large number of tables, each table has 1-n columns that allow Nulls. I am tasked with getting all of the Null values out. I am comfortable with writing TSQL to ...more >>

hello , i have a table ARTICLES where are columns ID_ARTICLE and NAME, there is also a CONTRACTOR table with ID_CONTRACTOR and many tables with prices ( group price, individual price, article price) and i found a function that return resultset : netto_price and brutto_price, discount of article f...more >>

Hi All, One cool question. I have a table which is described below. TblCharaters Type Parent Description A Null CharA D A CharD H D CharH B H CharB If you look at this table you will find a pattern ...more >>

hello , i have a table ARTICLES where are columns ID_ARTICLE and NAME, there is also a CONTRACTOR table with ID_CONTRACTOR and many tables with prices ( group price, individual price, article price) and i found a function that return netto_price and brutto_price of article for one contractor, ...more >>

Hi, I have an installation of MSDE 7.0 (the version of MSDE that is equivilent to sql server 7.0) There are 3 production databases on this server. I would like to upgrade this to MSDE 2000 (the version of MSDE that is equivilent to sql server 2000) Can I upgrade by just installing MSDE 2...more >>

When running the following T-SQL, the error message below is displayed when the second statement I executed. I am trying to move a table that has image data from the primary filegroup to a new filegroup that has been created. ALTER TABLE [myTable] DROP CONSTRAINT [PK_myTable] GO ALT...more >>

What action(s) invalidate transaction log backups. SQL Server 2000 sp3. I use a home grown log shipping setup. Thanks Paul ...more >>

I have the following table: UserID int ProductID int Decile int (could be only 1,2 or 3) I need to write a query that will return the following when selecting 2 products. Number of Users found for each decile where both products are found. So if i have following records in the table: ...more >>

Hi, My query output is like: PolicyName MessageID Policy1 100 Policy1 400 Policy2 200 Policy2 500 POlicy2 700 Policy3 300 Policy3 600 Policy3 800 And I like to change the above output to someth...more >>

I have an invoice table that structured as follows: day month year customer invoice 2004-01-01 2004-01 2004 abc $100.00 .... 2004-12-01 2004-12 2004 abc $30...more >>

How can I tell if a cursor is open or exists in memory? I want to create an error handler that closes and deallocates the cursoe if it is open. ...more >>

hi, hi, I've created a replication on one of my sql server 2000 server. this is a replication with 'nosync' option. I got the following two errors from each of the distribution jobs/agents to each different subscribers: error1: 'Replication-Replication Distribution Subsystem: agent ...more >>

Hi. I have an application that send encrypted data over to the database with their keys. About 1% of the data is having problems with decrypting. Since all the data is encrypted, there is almost no way to find out what is wrong. However, our guess is that the encrypted data could conta...more >>

If I use this: select * from cpoProducts where izd_id in('I1569','I1570') I get 2 results. If I use: SET QUOTED_IDENTIFIER OFF declare @products varchar(200) set @products="'I1569','I1570'" select * from cpoProducts where izd_id in(@products) I get nothing. How can I solve that...more >>

Is there a way to set the application name for the current session from a procedure? My application must use a sql connection and set the application name to identify the window account for audit tables. Sometime, I will need to set the application name from a procedure. -->If the applicatio...more >>

I need to be able to create a new record in a table and return the AutoIncremented ID fields contents so I can be sure that no two people can try to use the same ID Number. Any suggestions, I have been looking for a while now, and I just do not seem to making any headway. ...more >>

Hi, all: I'm having trouble with something that probably has a simple solution. I have linking tables that can list a particular MemberID multiple times. Is there a way to run a query so that a specific ID will show up once? Here is an example of the tables I've set up -- MemberTabl...more >>

Is there any way to do something like this: SELECT * FROM (EXEC sp_Who) x WHERE HostName = 'PC5197' -- Peace & happy computing, Mike Labosh, MCSD "I have no choice but to believe in free will." ...more >>

Hi, I'm new to sql programming. I'm assigned a task to consolidate source data, the data like this: col#1 col#2 col#3 col#4 col#5 acc# name proditem prodgrp sales 2300 aa 5-aa aa 100 2300 aa 6-aa aa ...more >>

I know it's not possible to update multiple tables within one update statement but my question is what is the next best way to do it? I have an update query that updates the values from one table with the values from another based on a join. I also want to update the second table with values...more >>

I have a forgeign-key relationship in my table. I want to enforce the key for inserts and updates, but I also want to allow the field to be null. Is this possible? So in table Client, there is a field ShoeName, which has a relationship in table Shoe (pk field ShoeName). I want to allow Shoe...more >>

Hello, I need to evaluate several variables based on a single parameter passed into a stored procedure. I am able to do this using CASE statements, but am wondering if there is a way to combine multiple CASE statements to clean up the sp. Below is a portion of my sp (there are actually 8 mor...more >>

I'm trying to execute the following script using ExecuteWithResultsAndMessages. I get "Incorrect synthax near 'GO'" message every time. The script is generated using the script method of the Table object and I've been trying to replace the terminator GO with something else. Does anyone kno...more >>

In my testing of the following statement, Clause 1 has always executed before Clause 2. UPDATE keytable SET @retkey = nextkey, -- Clause 1 nextkey = nextkey + @inc -- Clause 2 WHERE blah blah Is it certain that those clauses will always execute in that order? If not, ho...more >>

Hi. I have a strange problem with connecting to an SQL server. The SQL server is on a windows domain, but my workstation is not. If I try to log on to the SQL server using sql-authentication, the login fails with a "server not exists or access denied" error message. If I however try to bro...more >>

Hi everyone, We have a contacts database on SQL. but also a large number of users storing contacts within Exchange. What I'd like to do is be able to extract Exchange Contact data, maybe on a nightly basis, into SQL. What would be the best way to achieve this? Could i use a linked server? I'...more >>

Friends, In MS-Access, if I want to eliminate duplicate records from tbl_A, I set up a mirror table (tbl_B), set a primary key on the field(s) in tbl_B that I want to eliminate duplicates from, and then append tbl_A to tbl_B. Access will eliminate duplicate records from the insert. How d...more >>

I created job in SQL server enterprise Manager - Managnent. I defined 2 steps for this job.I set for first step that on success quit with success and on failure Goto next step. But no matter if the first step failure or not, the second step is executed. Why? Simon ...more >>

I have it in my head that if you use the truncate table statement, it screws up your ability to have a database in Full Recovery Mode where you are backing up logs. I also have it in my head that truncate table statement doesn't log at all ... hence the reason it doesn't work with a transaction ...more >>

I know that the answer is to plug in the ADS_SEARCHPREF_SIZE_LIMIT but I have found no examples of use in a raw T-SQL statement. This workds for me (linked server setup completed) but only 1000 rows (I know I have more): SELECT * FROM Openquery( ADSI, 'SELECT name, adsPath, telephoneNumbe...more >>

I've got an app written in C# that runs on Pocket PC and connects to SQLServer. It is working very well except for one problem I cannot solve. On some machines it won't connect to SQLServer on that machine only. For example, I've got a laptop here that is wired to the network and the PDA is ...more >>

As I create the table,i want to set a field's indentity to Yes, and increment is 1, However, there are two option for 'Yes' Yes or Yes(Not For Replication) <-- what is the difference ? -- .. ...more >>

I am using SQL2K. 1.. Is it a database specific or instance (server) specific? 2.. How to implement this feature? Switch it on/off for every stored procedure at the start and end? Thanks, Alok ...more >>

I have a table called titles Titles DrawingNo nvarchar(50) TitleNo int ShowTitle bit What I want to be able to do is create a view call DrawingDescriptions DrawingDescriptions DrawingNo nvarchar(50) Description nText(?) Where the data in Description consists of each Title with a Sho...more >>

I want to create a function that can accept a variable number of parameters. Or failing that, for the parameters to default to some value if they are not passed. Is that possible? ...more >>

Hi, I've accomplished this with Access-only tables, and I'm wondering if I can do it with a SQL Linked view, or Access View to the SQL Linked tables. I have two tables, name and address. I have an address FK in Name, linking to the Identity column in Address, named ID. I have a view, d...more >>

hi I am really stuck up with this problem here is my problems I am inserting data in 3 tables in a stored procedure I have a table A with a auto generated id let ID and I have updated the table A with new record (with ID) now I have to make use of this id in the corresponding update in ta...more >>

I got a table Books, has columns TitleID, Title, Sold. Primary key is TitleID (Clustered), Non-Clustered on (Title, Sold). To execute a T-SQL: Select TitleID, Title, Sold From Books The execution plan shows the index scan on the non-clustered index but not the clustered index, why ? ...more >>

Hi, I don't seem to be very good at syntax when working with strings and dynamic sql. I've played around with this for about an hour and can't get it right. Could someone please show me what @sql would look like. thanks. declare @sql nvarchar(4000) declare @dv_name sysname --dec...more >>

I noticed something when i ran a query yesterday and want to know if this is a common item. i had a query that returned 1000 or so rows on two very large tables. When i used TOP 20000 the query ran faster. Has anyone else noticed this? thanks kes...more >>

Im trying to make a sql query but I dont know how. I need to make a select * from table1 inner join table2 (If the field1 is not null then table1.field1 = table2.field2 else field2 is not null then table1.field2 = table2.field2 ) from table1 where (field1 is not null and field2 is not null) ...more >>

Hi, this is an easy Question. I don't want to use Set dateformat DMy i n each procedure. instead let me know any solns to Set the default date \format as DMY regards Aneesh ...more >>

I'm working for a company as consultant. Sometimes I need to make some changes in some SP on their SQL Server. I do not have a remote access to their server. Is there any way (script, utility, my own program) to update SP without visiting this company? They are pretty far from me and I would lik...more >>

TIA for any help. I need to execute this statement to tune my indexes on this whole SQL Server DB (w/ SP3), but I get the following error (it's tripping up on the dash in the DB name, because it works fine with DB's that don't have a dash. I've tried using single or double quotes and brack...more >>

Where can I find a good example of how to create an append view? Thanks, Paul...more >>

I have a user I created in SQL server 2k which will be used by web services and I don't want to assign dbo permissions to this user, but I do want to assign exec permissions on all the stored procedures I created. Do I have to manually check each SP in the permissions for that user to assign ...more >>

I have a number of series of table I am joining to create a summary table. Below I have put together some tables in question and the summary table as well as the script I am using to build the summary table. Here is the data we are working with. An employeewith a HRMIS_ID=111 has two reco...more >>

SELECT COUNT(*) AS myTotalRec FROM myTable WHERE Col1 = 'test' SELECT COUNT(Col1) AS myTotalRec FROM myTable WHERE Col1 = 'test' Col1 has set null not allowed property. I am using SQL2K. ADO.Net and .Net Framework 1.1 1. Which query is the optimized when used with any SQL databas...more >>

Today, when I read articles about sql 2005, I started to think this question again. Usually I will use a idnentity interger as my tables' primary key, for exmample, I will use the auto-generated memid as primary key of my member table. But I remember it is not a good practise, using memid will ...more >>

I have a great need to locate the parameters in a stored procedure and to see if they have a default value assigned such as @MyID = NULL. I need to know which items have default values and which paramters do not have default values so that I know with parameters to require entry. I have ne...more >>

I posted the following message in the sqlserver.setup ng, but got no response, perhaps some one here can orient me in this matter... thank you Saga Post follows: Hello all! I am researching the viability of a project. It consists of building a front end in VB6 using ADO and DSNless c...more >>

I have a number of series of table I am joining to create a summary table. Below I have put together some tables in question and the summary table as well as the script I am using to build the summary table. Here is the data we are working with. An employeewith a HRMIS_ID=111 has two reco...more >>

Hi Folks Anyone know of a means to create and execute a 'FETCH NEXT' dynamically? I have no problem with the Cursor creation (doing it dynamically)... This all came up because the MS TXT/CSV driver has a MAX limit of 255 columns (and we have automotive input up to 1024 columns). (I had to...more >>

While I'm doing just a few updates in a one table I need at the same time retrieve data but I don't mind if they are dirty. I mean, which the best way and faster is? Thanks for any suggestion, Enric ...more >>

Hello, are the following equivalent? (A LOJ B on a=b) LOJ C on a.c and (A LOJ B on a.b) Join (A LOJ C on a.c) on a.a ? thanks, Vadim Rapp...more >>

I have an inner join query to pull all records that match a specific criteria. here is the query: select h.stkhstCsiSym, h.stkhstSym, f.fctmodIssue , h.stkhstDate from stkhst h inner join fctmod f on h.stkhstCsiSym = f.fctmodCsiSym where h.stkhstSym <> f.fctmodIssue and f.fctmodFunction =...more >>

How can a call a user defined function written by me.Which require two parameter.First will be value of a textbox which is on a table.I am calling this function in the column expression .can anybody help...more >>

Hi there - hoping someone can help me here! I have a database that has been underperforming on a number of queries recently - in a test environment they take only a few seconds, but on the live data they take up to a minute or so to run. This is using the same data. Every evening a copy of ...more >>

i have a very large table that contains banking header files for our clients. In the header file there is a count of the number of transactions. what i would like to do is get an average count of the number of transaction put through for september / october & Nov 2004. the table is to large t...more >>

Hi Can't figure this one out! I need to update a table so that all the 0's are updated to 1 and all the 1's are updated to 0 Thanks Angela *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it!...more >>

This (really simple) trigger keeps recompiling during use. Any suggestions why (99.9999% single [@rowcount=1] updates!)? CREATE trigger dbo.db2042_u on u_db2042 for update not for replication as declare @rowcount int set @rowcount = @@rowcount if update(u_ankomst) or update(u_avgang) ...more >>