I need to understand what could cause two almost identical queries to run in a vastly different amount of time. The basic query is the same, it is just selecting different employees when calculating schedules. The number of selected employees in the slow query is 39. The number in the quick is 6...more >>

Hi All! I'm setting up a game that involves pari-mutuel betting. For example - Total Pool is 1, 213. Take total amount and divide into bet. Bet Odds would be: Calculation First- 314 4 - 1 (3.86 - ...more >>

Is there any Sql Cluase to say insert if the record not found in a single statement i.e : INSERT INTO STUDENTSVALUES('RKNET') where not exists Name='RKNET' thnaks in advance. RKNET...more >>

Hi, Does anybody knows how the Table Size can be greater than the Database size ? I performed the following set of operations 1) Insert a lot of Data 2) Delete quite a bit of Data. 3) Here the size of the database was around 3.14 GB and that of Transaction Log around (5...more >>

Hi all, Is it possible to include optional columns in a query. Basically what i am wanting is to return a particular column/s if a given variable/s is not null.. IE: SELECT col1, (select col2 if @var2 is not null), (select col3 if @var3 is not null) FROM my table Any guidance on t...more >>

Hi, I have a lot monthly tables, in order to query some data for last few month, I have to check a few tables. And the table names are determined by today's date. If I find the record, I don't need to query other tables anymore. how can I do it in store procedure? Thanks...more >>

We have a text file in which the first row has four fields and it needs to get BCP'ed into a table with four columns. All remaining rows are much longer with 75 fields and get BCP'ed into a table with 75 columns. I am doing this second BCP execution with the /F2 option to indicate that the...more >>

How do I get the return code of a stored procedure, Declare @rtn as integer set @rtn = Execute myProcedure does not work. Is there an @@...... value that I can check for the return code Which is set when I execute a Return 1 in my code I would like to use it like a function as I am ...more >>

I have a Stored Procedure on my Sql Server 2000 that is not working correctly. When I copy and paste it to Query Analyser, and change my paramaters to declares with the same data that my SP is using, it works the way I would expect. Stored Procedure ***************************************...more >>

Hi All How can i create a stored procedure with a parameter that accepts a list of values? Below is my example: Table:Tb1 Account Amount 2a 3 3b 2 4c 5 5a 1 I want to create a stored procedue sp1 that accepts a parameter which can be a list of Account values and sum their amoun...more >>

What is the best way to made update OLEDB Destination. I would like to UPDATE row in case INSERT fails because of duplicate key. Do I have to insert my rows into a temporary table and then update it in transaction, or can I do it in line in data flow task by any component. Thanks. ...more >>

Using ms sql 2000. I have a table that has a crap load of scan results that is inserted using a stored procedure. The first time a PC's scan is placed into the table it uses an Insert. Any scan receievd after the first is an update. However the problem is that what if I have more data the sec...more >>

I have a cursor within a cursor which is like Declare vendor_cursor cursor for select distinct top 10 vendor_name from event_feed_view Where vendor_id = @vendor_id Open vendor_cursor Fetch Next from vendor_Cursor into @vendor_name WHILE @@FETCH_STATUS = 0 ...more >>

given an input variable @ColumnsToSelect, how should the syntax go in the following? Thank you, -Greg Declare @ColToSelect varchar(4000) Select @ColToSelect = 'COALESCE(@ColToSelect + ', ', '') + CAST(Column_Name As varchar(50)) From ' + @ColumnsToSelect + ' Where ...more >>

Is this the correct NG for a backup Q? Right now I'm wondering if the only way to copy a backup file from one server to another is by DTS(?) 1. I'd like to schedule a back from one server to another recieving server to be used in an emergency. 2. I'd like to be able to automate the restor...more >>

I'm importing data, via DTS, into SQL Server 2005. The destination database has its recovery model set to Simple. After running for a period of time the import fails with 9002 transaction log full error (autogrow for the Tx Log is off). This didn't make sense as the recovery model was set to s...more >>

Hi All I have a stored procedure sp1. 1. Is it possible to select certain columns from the resultset after executing the sp1. e.g select * from exec sp1 2. Also is it possible to insert the resultset from sp1 into a temp table. e.g create table #tb1 (col1 char(10), col2 varchar (20)...more >>

Hi, I have the following problem In a DB exist this inf: (I can use many tables... and/or many columns) --> 1 2 3 4 5 6 7 8 9 a b c d e f g <-- In need write a select sentence that move in "circle" all info, for example 5 1 2 3 9 a 6 4 d b 7 8 e ...more >>

If I have a varialbe that contains the name of a Parameter in my stored procedure is it possible to get the value of that parameter Example: CREATE PROCEDURE test @myParam1 varchar(50) AS DECLARE @ParamName varchar(50) @ParamName = '@myParam1' EXEC ('SELECT ' + @ParamName) ...more >>

help please. I have sql statement that cast two date range into decimal and then devive by one another. When I compile this code I get error: Source: Microsoft OLE DB provider for sql Error desc: Devide by zero error encountered. How would I resolve this issue?...more >>

I have this table below: I want SUM the Amount Column where Codeid is beginning with letter A and if PayType is 1 then Credit amount... For example: record 1 and 2 will be -$12 and -$11.55 ResolID CustID Amount CodeID PayType 1 1235 12 A1234 1 2 1235 11.55 A1000 1 3 1235 22.9 A123...more >>

Hi, I installed SQL Server Express and SQL Server Management Studio. I have observed that everytime I start Management studio and am typing a query,it automatically shifts focus to the Management studio taskbar after every few seconds and I have to click my mouse to bring back the focus to t...more >>

Perhaps in SQL Server 2005 this may now be possible to do with the XML column type: I would like to log row changes from many tables within a database to a single audit/log table. (Instead of creating an individual log table for each data table that needs to have each row change (transacti...more >>

I thought this would be easy, but it's now seeming way more complex - so I'm hoping someone has a shortcut. How can you figure out if a User_Name is allowed in a specific role? Ok, sounds easy at first, but what roles that that role is a sub-role of? That's where it's gotten more complex t...more >>

Using SQL 2000 Developer, Windows XP, and DotNet 2.0 I get this error message when trying to UPDATE a very large table: "ExecuteNonQuery requires an open and available Connection. The connection's current state is closed." The UPDATE command affects all the rows in the column. The confusing ...more >>

Given; CREATE TABLE [dbo].[ColumnsToSelect]( [ID] [int] IDENTITY(1,1) NOT NULL, [COLUMN_NAME] varchar(50) NULL, [Include_in_Report] [bit] NULL) insert into ColumnsToSelect (Column_Name,Include_in_Report) Values ('Column_Number',1) insert into ColumnsToSelect (Column_Name,Include_in_Report...more >>

Hi All I am trying to execute a stored procedure that does a very simple lookup and returns a text field. However, when I try to execute it, I am getting a rather strange error that I can't seem to fix! There is defiantely information coming back as I have tested this in Query Analyzer. Th...more >>

In SQL Server 2000's Enterprise Manager, there is a way to create a copy of a table by right-clicking on the table name and selecting "Export Data >> DTS Import/Export Wizard >> Specify Table Copy or Query >> Copy table(s) and view(s) from the source database". Is there a way to use to T-SQL to ...more >>

Hi, I've got three tables: [Article]{ArticleID, Title} [Journalist]{JournalistID, Name} [ArticleJournalist]{JournalistID, ArticleID} One article can be associated to one or more journalist. Here's my request to get all of the records: SELECT A.Title, A.ArticleID, J.JournalistID, J.N...more >>

I am using access as front end and using sql pass through query to get data.. is thier any way to call parametrized store procedure using vba not directly but using pass query method mean first pass the data to tat query and then execute that query through vba..... Am i rite or any other good ...more >>

Hi all, I have several columns which store currency values (typically up to 4 integer values, plus two decimal places) Using Enterprise Manager I can set a column as decimal type, but it doesn't allow me to specify precision) and any values show as the integer amount plus ..00 (ie 123.45...more >>

Hi all. I have sql server 2000, and am trying to do a job (administration/sql server agent/jobs) with sql server enterprise manager, but the job exceeds 3200 bytes and it tells me that it will truncate the program. Is there a way to increase the maximum job size? ...more >>

hi guys. this is probably really easy but i'm afraid i really don't know a lot about SQL scripts. the script below does the following; 1) checks for the existence of a 'group type' named 'site' 2) removes the groupe type if already present 3) creates the new group type called 'site'...more >>

I have the following trigger: CREATE TRIGGER [trRISK_HOLD_ALL] ON [dbo].[transactions] FOR UPDATE AS IF EXISTS (SELECT * FROM INSERTED INNER JOIN TRANSACTIONS ON INSERTED.TRANSACTION_ID = TRANSACTIONS.TRANSACTION_ID INNER JOIN ADMIN ON INSERTED.USER_NAME = ADMIN.USER_NAME ...more >>

Hi i have table which has a a unique key call myKey which is actually consist of three field in that table like mykey=A+B+C i want to create a store procedure or trigger or any thing which solve my probblem question is, i have external data like in text file and excel file so i i import ...more >>

Hi Using 2000 I am writing a .cmd file for Bulk copying data(about 25 tables of 1 million rows each). I need your help and advice on this 1)is dts faster than BCP converting to flatfiles and again copying to destination tables. 2) if we write cmdshell and use BCP in that instead of direc...more >>

Hi Currently I'm stuck between the options A - Using several(20) small 3-5 columns tables to store my slow changing dimensions. OR B - Use 1 big table 5 columns and "!" blank columns not used and use an identifier column to get the dimension name to identify the specific dimension in o...more >>

Could Stored Procedure or Trigger choose another database that is on difference engine? For example, I have database A on engine or instance name Server1 that has a stored procedure Stored_Proc_1 and in this stored procedure I want to access some table on database B on engine Server2. I have use...more >>

I've been studying how SQL server 2k creates, manages and uses statistics on indexes and columns, and I was wondering if someone could shed some light on the following: - why would I create statistical information on a non-indexed column? What good would it do the query optimizer to know th...more >>

Hi I use ASPNET application using Sql Ser 2000 database. There are a lot of queries using "like" statement. I suffer bad performance of that application and I noticed in SQL Profiler that those "like" queries takes a lot of time. I heard about full text queries and I wonder if it could boos...more >>

Hello all, I have found the new problem (bug?). I store the generic list of simple objects(string, int, etc..) in IDTSCustomProperty90 I design time I can retrieve and save this List without problem. For example: List<object> fncProps = new List<object>(); fncProps.Add(25); fncProps.A...more >>

Hi, I'm writing a ASP.NET application, and I would like to export data from SQL Server 2005 to MS-Excel. I know that the Integration Service in SQL 2005 has replaced the DTS in SQL2K. I'm wondering how I can execute the Integration package from either ASP.NET or by means of Stored Procedu...more >>