Hello I hope anyone can help me here with a Problem between my Microsoft SQL Server 2000 SP 3 and an Oracle 8 Server. I have to copy records from a Oracle table (3.000.000 records by now) to a SQL Server table (8.000.000 records by now) daily (somthing like an incremental backup). The Oracl...more >>

I need to be able to construct a SELECT statement in a stored procedure and concatenate a CSV list of values passed as an input parm to the stored procedure. How can I do this without declaring a variable for the Select, then executing it. I need to use this with Reporting Services so unless...more >>

Hi Guys, I want to employee details in one table along with his photograph and resume. How can I achieve this? I'm fresher and I don't have any basic idea about this. Can somebody lead me to a worked out example. If appropriate how to use WRITETEXT/READTEXT to achieve this task? thanks in...more >>

Greetings, I am writing a program in VB6 which uses ADO to communicate with a SQL Server 2000 database. There are some fields in some of the tables that have columns of text data type. Inserts and updates work fine until the values start to get a bit larger. After reading many of these...more >>

Hi there, I have a peculiar situation where my Transaction log file is growing out of bound. I have too many deletes in the tables in a database and that log is growing in a big way. It has even grown to 10 GB in a day. So how do I handle the transaction log. I am unable to detach the databas...more >>

Hello, Can anybody explain me why is it showing rank as 0 always in the result? Table Data: LLBPSEPG This is test for the Search search test for heat mango apple banana eating banana is good for health man eats rice healthy big laod in the ship hot SPEG App LLBP - Next LLBP Lesson...more >>

Hi Folks I need to create a 'generic' class of triggers to be used in 1 of our applications (about 80% of the tables, with nothing 'funny' in terms of datatypes in the table columns). At 1st the request seemed trival (generate the XML of before/after values for fields that changed, like ...more >>

I have two tables one table T1 only have one result per date table 2 T2 can range from 0 to 8 results per date, I want to pull back all of the results in T2 and sum the totals, in my example it will be summing "Stafftime" and "items", in T1 I need to sum the "Contrctaed_Hours" and the Ove...more >>

I am importing some files using DTS on a new install of SQL 2000. It is randomly locking up. It will run x rows then stops. At that point, I cannot do anything with SQL Server. I cannot even stop and restart the service. 2k3 Small Business Server SQL 2000 SP3a 4GB Ram 2TB array (3war...more >>

After restore off a database the user under security logins was not created. The user under Database and users was created, but that user MUST bee the same user under security logins, if i try to make a new user with same name under logins, thats ok, but i cant connect to the database becuse t...more >>

Ok, so here's the scenario: We have an n-depth tree of a self-referencing table. (has to be this way) What we want to do is total the value of the same column all the way down the tree from the first parent. Value Value Value Value etc. We only use this in...more >>

Hello Thanks to all who have been helping me to fight a battel with tsql for rownumbering of my tables. I humbly admit defeat for today. I had to throw the towel in and whipped up an ADO loop to number some rows in a table. That took me all of 3 minutes, if that. I wrestled with tsql ...more >>

I have the following User_Log table: ID User Location Use_Date 1 1234 RR 1/5/05 2 1234 KT 1/10/05 3 1234 ZF 1/20/05 4 1259 ZF 1/13/05 5 ...more >>

Hello, What is the suggested way to store uploaded files? 1) IMAGE type data in an SQL Database table 2) As a file in the NTFS file system Thanks, Arsen ...more >>

Anyone know why stored procedures with Temp Tables in them take a REALLY long time to run the first time after SQL service is started? After running once in 35 seconds, I just ran my proc again with the same parameters and it took 2 seconds! Every time it takes 2 seconds, except the first time af...more >>

I need help with the sp below. I have to get data this way, I cannot use dataset. CREATE Procedure dbo.sp_select_image ( @iNo int OUTPUT, @iName nvarchar(50) OUTPUT, @iSize int OUTPUT, @iType nvarchar(50) OUTPUT, @iByteData image OUTPUT, @iDescription nvarchar(100) OUTPUT, @iUplo...more >>

Hello, we have a table on our SQL 2000 server as follows CREATE TABLE [dbo].[Products_Tmp] ( [Product_Id] [uniqueidentifier] NOT NULL , [Product_Group_Id] [uniqueidentifier] NULL , [Product_Id_Client] [decimal](18, 0) NULL , [Client_Id] [uniqueidentifier] NULL , [My_Name] [nvarchar] ...more >>

Hi, Can anyone please guide me as to what the different values in the Status column of the Sysobjects table mean. I found the following values in my SQL 2000 server installation. 1610612736 -536870912 16 1610612736 1610612993 1610612736 0 The same procedure behave different if it ...more >>

Hello, I would like to achieve the following within an stored procedure. SELECT * FROM TableX WHERE ID = 1 OR SELECT * FROM TableX WHERE ID IS NOT NULL How can I solve this by using a condition in my WHERE clause? eg. ////// CREATE PROCEDURE TestID @ID INT AS SELECT * FROM...more >>

Hi we are using SQL polling from ASP.NET application. I know that we are having some problems with closing connections, and we run out of connection in our pool. When we take a look at SQL server we can see there are a lot of connections in the sleep mode. Is it possible from SQL side to remove t...more >>

Hi all, I have the following table id (autonumber) category1 (int) category2 (int) booking_month (int) booking_year (int) I have records in the table for booking_year = 2004 and booking_year = 2005, for example id, category1, category2, booking_month, booking_year 1, 20, 30, 4, 2004 ...more >>

Hello, All I have a trigger on a table, which when called, creates a job to run in n minutes. The trigger runs fine and the job is created, however with a small issue. The username that is used as the Job Owner, ends up to be the name of the user that triggered the trigger eventhough the co...more >>

I am trying to build a query based on Expanded Hierarchies in Boooks online. My table structure is changed by assigning an ID to both parent and child element. Please help me in building the query as similar in BOL. Many thanks in advance ParentID ParentName ChildID ChildName 8 ...more >>

The code that i want to fix and where im stuck at Declare @server varchar(100) DECLARE Server_Cursor CURSOR FOR SELECT srvname from master..sysservers OPEN Server_Cursor FETCH NEXT FROM Server_Cursor into @server WHILE @@FETCH_STATUS = 0 BEGIN DECLARE Employee_Cursor CURSOR FOR SELECT...more >>

create table t (colA int, colB char(1), colC int) insert into t(colB) Values('C') insert into t(colB) Values('C') insert into t(colB) Values('C') insert into t(colB) Values('C') insert into t(colB) Values('A') insert into t(colB) Values('A') insert into t(colB) Values('A') insert into t(co...more >>

I have a table that I use an INSERT command to add a new record. The primary key is an int field and I am calculating the highest # on table and incrementing by 1 just before INSERT. On rare occasions, 2 people get the same number and I get SQL error about duplicate. Is there a way to trap ...more >>

Hi, I am doing some project with SQL Server 2000 and C#. I am using a SELECT statement to get a column and to bind it to a dropdown list. The column is a datetime format. I don't need time portion of it. Before I tried any CONVERT functions my query looked like this: SELECT DISTINCT DateEnter...more >>

declare @var varchar(50) select @var='ServerA' select name from @var.master.dbo.sysobjects -- Need help here Right now its just one server but eventually i want to probably use a group of servers and kinda use a cursor. ...more >>

Hello, I get the error There is already an object named 'PTallow2' in the database on the TRUNCATE statement, when I do: First: SELECT * INTO PTAllow2 FROM PTAllow Then: TRUNCATE TABLE PTAllow2 Of course there is an object PTAllow2 ;-) I tried: - wrapping the statement in a explicit ...more >>

Hi, I have an application written in .Net and my database is SQLserver. In my application i am using SqlAdapters to get the values from the database. But sometimes, values are not returned properly to these adapters and there are no exceptions also. At this time, all database queries wi...more >>

I need to change many IDENTITY columns in many tables so that they show as NOT FOR REPLICATION. This way when I replicate, it doesn't trash my identity values. I can do it with Enterprise Manager without any problems but I need to code it (using t-sql) so I can repeat the process and undo it l...more >>

Is it possible to use VB or VB.NET in a stored procedure? I know that I can call a stored procedure using ADO or ADO.NET from VB or VB.NET, but if I click on Create New Stored Procedure within Enterprise Manager is it possible to use VB(ADO) or VB.NET(ADO.NET) to make a connection to different ...more >>

PRB: "use database" not working after "create database" Please help, I have the following query: set XACT_ABORT on begin transaction create database MY_DB use MY_DB commit transaction The "use" statement fails saying the database does not exists, but I get no error on the "create"....more >>

a very basic situation, here's table definition: create table table1 (xid int identity, f varchar(20), f_std varchar(20)) go alter table table2 with no check add constraint pk_test2 primary key ( xid ) create table table2 (f varchar(20), f_std varchar(20)) go alter table table2 with no che...more >>

I have two tables (table1 and table2) in a database of sqlserver 2000. I want to add those fields of table1 who doesn't exist in table2, to table2. How can I do that? Thanks, Hugo ...more >>

Hi, How do I return the identity of the row of data I've just inserted from the sp? Thanks ...more >>

I have to pull data from a SQL app for a WIN-Form app in .NET, where data is stored in FoxPro. Seems that the bit data type is not casting properly through the ODBC driver back to FoxPro?? Any way to cast or convert a bit column? When I did case when IsPrint = 0 then ' 0' else '1' end IsPrin...more >>

HOWTO programmtically code 'use' in T-SQL Please help, How can one programmticaly code the 'use' statement in T-SQL so that from both Query Analyzer and a typical ADO/ODBC query one can change whatever DB a TX is "pointed" to to "point" to the DB they really want to work work. ========...more >>

How can I copy fields (not records) from one table to the field structure of another table (in the same database on a sql server 2000)? So that I have not to re-type the name and properties of the fields and to be sure the name and properties of the fields are the same in both tables. Thank...more >>

I am trying to backup a database which is on the server and restore it on my local server. Can anyone please tell me how to do this? Thanks in advance....more >>

What does database integration mean? I assume it means "integrating databases" but what does that translate as in a real life example. I'm hoping someone can give a helpful definition and an explanation of what that means in the real world. Does it mean merging 2 different kinds of database...more >>

How can I leave the table name in a create statement as a variable? DECLARE @tname varchar(32) SET @tname = 'ralph' CREATE TABLE @tname... I need to create tables/temp tables for multiple ado.net users and I hope to gain some performance improvements over dynamic sql. thx md *** Sent...more >>

Hi, I am working on a SQL server and the "Maximum worker threads" option is set to zero. That seems strange to me. What does that means? Does zero have special meaning for "Maximum worker threads" option? Thank you, Alan ...more >>

Can someone tell me why I get this behavior? Whenever I build a new stored procedure into my database I specifically set the ansi nulls and quoted identifier on like so SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE PROCEDURE xxxx AS It compiles fine, but when I go back to E...more >>

SQL Server 2000, MSDE I have a query as follows SELECT SysID FROM Customers WHERE CustomerName = 'Tim's Computers' which fails because of the ' in the customer name. How can I prevent this type of problem short of not allowing ' in names. TIA Tim Morrison ...more >>

Hello every one, I have a table from which I am going to select 75% of the records with a query. Once I get this query, I will need to go through every record in this query and update another table with the content of each record. The keys to link the query and the table to be updated ar...more >>

I am reading the index keys through a query and not using sp_helpindex because I need to make a join on the query. How can I know if the index column is in descending order the query i am using is the following: select sysobjects.name as TableName , sysindexes.name as IndexName , s...more >>

create table t (colA int, colB char(1), colC int) insert into t(colB, colC) Values('C', 3) insert into t(colB, colC) Values('C', 1) insert into t(colB, colC) Values('C', 4) insert into t(colB, colC) Values('C', 2) insert into t(colB, colC) Values('A', 4) insert into t(colB, colC) Values('A', 1...more >>

I have a list of approximately 2000 objects that need to create a sql script to generate these objects. The script will be applied to another datbase to generate these objects. The 2000 objects name have been loaded into a Table A. Please help put together a program that will read the ...more >>

I have a purchase table and a sales table. I used a union query to join them and using CASE statement, I have been able to extract purchase and sales amounts. Now, I can now generate the following report using a matrix: JAN FEB Purchase product...more >>

Hi All, Does anyone know the code to transfer tables from an Access database to a SQL database thru a Stored Procedure? I know the code to run a BCP from a text file, but I do not know how to get into an MDB. TIA, Joe ...more >>

hello, I have a Question Paper table Create Table QPaper (QPID, QPName,SubjectSLNo,Type, NoOfQues) Data is like Insert Into QPaper Values (1, 'UNIT1', 1,'Objective', 10) Insert Into QPaper Values (2, 'UNIT1', 1,'Descriptive', 5) Insert Into QPaper Values (3, 'UNIT2', 1,'Objective', 2...more >>

Hi. I have a SQL backend and an Access data project front end. Each user has a local copy of the front end on their computer. I have a function set up to read data off a CD and import it to the system -- this function works on all computers except one. On the problematic machine, I keep gettin...more >>

I'm attempting to sort using the following conditions: If the score is greater or equal to passing score, sort by score descending, test taken date descending. If score is less than passing score, sort by test taken date descending, score descending. Expected results: 3 95 2005-01-02 00:00:00...more >>

Hi All, I have a few columns that are of a type Money. My problem is that the data has four decimal places to the right all the time e.g... 1032.0000 ..0000 12.1200 100.100 etc... How can I set the columns of type Money to only provide two decimal places. I would think that this woul...more >>

I have a SP which is having an odd problem. There is an int which I am using to to return several different queries in the SP. The first query gets the value of the int based on a date, @minsess. @minsess is then used in multiple queries in the SP. For some reason if I replace @minsess with ...more >>

Hi, I have to find out where an old stored procedure A is being used in our SQL Server database. Is there any command which can tell which other stored procedures are calling this proc A, I tried sp_depends but it does not work Thanks dev...more >>

is there a way to select all users who have db_datareader privledges using sql server code? tia, dk...more >>

Here is my current query Use Winpayment GO SELECT convert(char(11), retrieval_reference_number) RR, message_type, authorization_identification, convert(char(8), card_acceptor_identification) SN, convert(char(25), transaction_name) TransactionName, ...more >>

Currently, this isn't working and I need to know how I can get it to work. The problem is with the list of sites. I have no problem when sending in only one number like "17" but when I add more than one, it breaks. I have tried setting it as integer instead of varchar... doesn't help. Was ...more >>

I have a SQL 2000 Standard server setup and I have a table in one of my databases to store files in. I am using asp code to retrieve the files from the database. I can download txt, bmp files just fine but when it comes to pdf documents it just hangs at the downloading the file screen and th...more >>

I have a simple query with group by. and I want to get the topX rows from each group. how to do that ? for example declare @myTable table ( id int identity(1,1), comment int, someDate datetime ) select comment, someDate from @myTale group by comment ...more >>

I've been chasing a performance issue on and off for several weeks. A stored procedure in a custom application will run in under 30 seconds or it may take as long as 6 hours to run. I've got a stack of execution plans on this procedure and sometimes the procedure uses a good plan complete wit...more >>

Hi everyone I have something interesting (annoying) going on with my trigger on SQL2K, I've noticed several threads on this in the past, but no resolution for the posters. I have a simple insert statement which puts a few fields into a table on a linked server. Code that works in SQL QA:...more >>

Hi everyone I have something interesting (annoying) going on with my trigger on SQL2K, I've noticed several threads on this in the past, but no resolution for the posters. I have a simple insert statement which puts a few fields into a table on a linked server. Code that works in SQL QA:...more >>

I have a table that looks like this: Acct Reason 1 MA01 1 MA18 1 MA19 2 MA01 2 MA03 3 MA01 3 MA03 3 MA07 4 MA01 4 MA03 In the above example, if an acco...more >>

Hi - is there a SQL statement which will remove text between tags? eg. in my database, people have copied and pasted from Word, into the HTML editor - this has carried across a lot of markup, which I don't want - I only want text. So I could have something like: <span mso:normal.............more >>

I need to transform some data from table format ID | Apr | May | Jun | Jul 1 30 40 50 60 to ID | Month | Value 1 4 30 1 5 40 1 6 50 1 7 60 Whats the easiest way to do this. Transfering from on etable to another....more >>

I ran a bulk update on a SQL database for a range between dateswhere there were trigger and possibly foreign keys. the parent updated fine but it changed multiple child records although they were not related...more >>

Hi, I have SQL Server 2000 standard edition from MSDN installed on what will now become a production server. I have since purchased a 1 processor licence. My question is there any reason not to continue using the MSDN version in production? Or should I uninstall it and use the purchased ver...more >>

I am trying to import some data from excel into SQL Server table - some of the data has a leading 0 ie 0897 - when I import it, it imports these records as NULLS. How do I import the record so it is 897. Thanks in advance...more >>

i have a procedure on sql 6.5 that runs the command dbcc checktable(syslogs). I am migrating this to sql 2000 and am not sure how to replace this. I know that it is checking the integrity of the log file but what is the equivalent command in 2000 ? Thanks in advance....more >>

hi, do you have to be a local admin on a windows 2000 box in order to view and execute any NT scheduled tasks? I have delelopers that shouldn't be local admin on our production sql server, yet, they need to view and execute some NT scheduled tasks on the server. I've granted their dom...more >>

I can use a UDL file to build my connection string to connect to an SQL Server database. Version 1 of connectionstring Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=Northwind;Data Source=JANDY When I use an SQLClient.SQLConnection in Visual Basic.NET and buil...more >>