hi, I have a table of tasks with time: select taskid, startTime, endTime from Tasks when no task was running ? many thanks ...more >>

Hi How can I use Table Datatype in Procedure as an input variable. The problem is that I have different triggers on different transactional tables. These triggers are used to log the changes via insert in some other table (this table is static in all cases). Here I need that trigger passes the v...more >>

I need to convert the datetime into YYYYMMDD. BOL says using style value 12 or 112 should put it in the correct format, but that is not what its returning SELECT CONVERT(DATETIME, GETDATE(), 112 gives me 2003-12-30 01:17:26.75 When I want 2003123 Does anyone know what I am doing wrong ...more >>

The following simple queries on information_schema are not working reliably from my ASP 3.0 web page. 1. query to get list of non-system databases SELECT CATALOG_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE CATALOG_NAME NOT IN ('master','tempdb','model','msdb') 2. query to verify a select...more >>

I have a procedure, say procedure A which calls Procedures B and proc. B calls procedure C , I have started a tran in Proc A with <begin tran mytran > do I need to start more tran for procedures B and C. Currently it is like this create proc a as begin begin tran t1 <do some update tas...more >>

Can anybody tell me the dates (may ve appox.) sql server 6.5 sql server 7.0 sql server 2000 were realesed to production TIA ...more >>

Hi there, How can I use BCP utility to BCP OUT data with first column is column name. I am using SQL Server 7. Thanks in advance. Allan ...more >>

I have two databases data1 and data2, I would like to copy the contents of data2 into data1. Both databases have the same structure. How can I do this in Server 2000? Thanks Mustapha ...more >>

Hi, I used sp_depends to find out what objects contain inside an object such as store procedure. sp_depends 'store_procedure_1' but I only get objects from current database, can I get objects from outside of database? is there a function in sql server 2000 allow me to find out t...more >>

I'm doing an export of a file from SQL Server via BCP. On a field defined as numeric 9(10,0), I take the field and translate it into a SQLCHAR in my format file. If the number is less than 10 positions, the result is left justified with trailing blanks. Is there any way to convert the blanks ...more >>

I have a system that uses a stored procedure called from a VB application using a series of custom classes. Each call to the SP uses different arguments to attain the specific result set. We recently had a requirement that essentially equates to a union of the two of the data sets. Is there an...more >>

Can a word document be saved in a SQL Server column using c#?...more >>

Windows 2000 Office 2000 MSSQL 2000 (seeing a pattern yet?) Trying to query an Excel file from Query Analyzer using OpenRowset. It works fine except with columns whose data contains a mix of alpha-numeric characters. A column of: 1234 2345 3456 4567 FOO 5678 6789 BAR imports as...more >>

Hi, I have an online application that access data from a view. Unfortunately it is extremely slow. How can I fine tune the code below to run faster. SELECT acct, final_decision,( SELECT COUNT(DISTINCT final_decision) FROM sale t1 WHERE t1.acct = t2.acct) FROM sale t2 Thanks, Cula...more >>

Hi, this query works before, but now it gets: Server: Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to numeric. here is the simplified query SELECT top 10 * FROM table1, table2, table3 WHERE table1.id = table2.id AND table2.type_id = table3.type...more >>

Hi I am using an application (Robocopy) to copy a series of database back-ups (*.bak) from one machine to another. What I then want to do is to go through the folder containing these back-up files and restore databases from them. However, I don't know in advance the names of the files, bu...more >>

Found the article... http://support.microsoft.com/?id=194124 -- -oj http://www.rac4sql.net "oj" <nospam_ojngo@home.com> wrote in message news:... > This is a limitation of Excel ISAM driver. The determined/sampled datatype is > based on the majority. Once this is selected, the rest w...more >>

I have a perfectly functioning linked server. Created in SQL 2000 to Oracle 9i. The SQL openQuery function works fine. But I dont know how to run a proc that lives on the Oracle db and requires an input param. I've tried using "Exec OracleSrv..Scott.TEST1 1736". Test1 is my proc, 1736 is the numb...more >>

I am working on a stored procedure (see below) that creates a cursor with var_id and var_desc fields from a linked server. It needs to loop through the cursor and try an insert (via another stored procedure call) for each row into a table in a database on the local server. If the row already e...more >>

I read about how to use sp_configure with "max text repl size" to set the max text size for replication but how do I retrieve the current max size for replicating text? Thanks! ...more >>

I have a question about database design. I have inherited a system that archives data generated from an older financial system. The problem I am facing is that this system does not have the ability to increase its invoice number size over 999999 and we are turning over invoice numbers. I am curio...more >>

I have been trying to change a column value using the following statement: ALTER TABLE tablename ALTER COLUMN column_name varchar(30) The response that I get is: Server: Msg 170, Level 15, State 1, Line 2 Line 2: Incorrect syntax near 'COLUMN'. The column was originally a varchar(10) ...more >>

I am using stored procedures to fetch data from database, and I want to control how many rows I fetch. How to use a variable in select command with top parametar something like this ? CREATE PROCEDURE abc (@n varchar(2)) as select top @n * from bbb go thnx ...more >>

I have a table with all the header information for an order. All that is not included in this table is information pertaiining to order lines of the order. The tables are relational so the header table has all numbers in it. I need to export this information for a non relational databas...more >>

Gang, Having a little problem, and I'm hoping that wiser heads than mine can point me in the right direction. I'm trying to use a stored procedure to execute a query that is doing partial substring seaches of a keyword table. I was using the CONTAINS sql method in SQL Server, but run into...more >>

Hi, I have a stored procedure that accepts an image parameter from a VB app. In VB, i declare the parameter as "adBinary" and in the stored procedure, it's declared as "Image". When I try to execute the insert statement, it fails. If I comment out the image field of the Insert statement, it w...more >>

Hi all: Hope somebody could give me some good practices to go on. Supose the following tables: create table ClientTypes ( lClientTypeID numeric identity not null, sDescription VARCHAR(100) not null, iActive smallint not null ) on [PRIMARY] create table Clients ( lClien...more >>

I have an application that is misbehaving -- the application writes to the database without locking parent tables. The problem I have is that a routine I run changes the primary keys in the parent table before the application has written to all child tables, causing the application to f...more >>

-- I'm using SQL Server 2000, Is there any way Tran-SQL , return 'abxxxfghiCDE' -- instead of 'abxxxfghixxx' using the following statement?. Thank you in advance. SELECT REPLACE('abcdefghiCDE','cde','xxx') GO abxxxfghixxx ...more >>

Lately my application has started to deadlock on a stored procedure that until now did not. I'm about to dig in and figure out the cause. The transaction does effect a lot of tables and I'm sure SQL is doing a table lock where a row or page lock would suffice. My question is how can I get my app...more >>

declare @startDate as datetime declare @endDate as datetime set @startDate='20031210 7:01:03' set @endDate='20031210 7:01:05' if exists( select * from cpoTimeSlot where (ts_startDate > @startDate AND ts_startDate < @endDate) OR (ts_endDate >@startDate AND ts_endDate < @endDate) ) select ...more >>

We need to start collecting the source IP for an application. What would be the best datatype to store that info in? Thanks in advance for your help Dan...more >>

Is there a local version of @@Error like there is for @@Identity. For @@Identity you use the function SCOPE_IDENTITY(). Does @@Error need somehting like this? ...more >>

I have a query that i'm not sure how to compose there are 3 tables Table1 VendorID ProductID Table2 ProductID (various other fields) Table3 VendorID ProductUD Basically Table1 will have the majority of the records. IF Table2 has productID that is Table1 THEN there has to be a ...more >>

I have a quote table and a quote detail table. I need to delete all quotes that belong to the contact that I want to delete, and I also need to delete the quote detail records that are linked to the quote table. So each record in the quote table has a contactID field and the quote and quoteDetail...more >>

We are in the middle of an upgrade here. We are going from a straight Access database with VBA front end, to SQL Server 2000 with a VB front end using ADO. We are mid- way. Right now we are at the point of having all the VBA code in Access using SQL Server and ADO. I am connecting to SQ...more >>

Is there a way to reference the database name using a variable? Something like this??? sp_DeleteJobItem @NewJobID, @DatabaseName CREATE PROCEDURE sp_DeleteJobItem @NewJobID int, @DatabaseName as DELETE @DataBaseName.dbo.Job_Item WHERE Job_ID = @NewJobID...more >>

Hi I'm seeing a very weird (and new) phenomena on two of my IIS servers. Suddenly, queries are very very slow. The weird part is that if i run the query from Query Analyzer it works great (0 seconds) but if i run it from ASP i get a very slow response and sometimes even timeout. any ...more >>

hi, I have a table of tasks with time: select taskid, startTime, endTime from Tasks when no task was running ? ( i need it by ranges of time ) many thanks ...more >>

So how can I send the results from a system-SP to a table or a file? I wish to run the procedure sp_publication_validation each night, because during the day it would probably be too much of a strain on an already busy DBserver. How can I capture the output in a file opr a table? Tnx, Hans ...more >>

Hi, all! When I'm trying to import data from Excel using DTS object in VB one of the params specified are: oCustomTask1.SourceSQLStatement = "select `F1` from `'Sheet1$'`" What is F1? Actually what I need is to specify a range of cells in the Excel to import into SQL tables. This could be a ...more >>

Can someone help me with a query? example create table #test_order (ordernumber smallint, order_value numeric(8,2)) create table #test_orderrow (ordernumber int, row smallint, itemnumber varchar(20) ) insert into #test_order values (1, 1023.50) insert into #test_order values (2, ...more >>

Hi All, I have a serious problem with my database. Even after periodic backup of my transaction log, the size of the log file is increasing significantly. I was told that periodic transaction log backup prevents the increase of transaction log space. My backup plan is like thi...more >>

Hi programmers, Please, how can this be coded in Microsoft SQL Server CREATE TABLE EMPLOYEE ( FNAME VARCHAR(15) NOT NULL, MINIT CHAR, LNAME VARCHAR(15) NOT NULL, SSN CHAR(9) NOT NULL, BDATE DATE, ADDRESS VARCHAR(30), SEX CHAR, SALARY DECIMA...more >>

Hello All, When I queried sysprocesses, following was the output. spid waittime lastwaittype waitresource 1 407 SLEEP 2 23907 RESOURCE_QUEUE 3 3782 RESOURCE_QUEUE 4 0 PAGEIOLATCH_SH 1:1:1095 5 0 WRITELOG 6 3873657 RESOURCE_QUEUE 7 43000 RESOURCE_QUEUE 8 0 MISCELLANEOUS 9 0 WRIT...more >>

How to get notified when the SQL server agent stops ? Gary ...more >>