I'm trying to have a DTS package execute by the SQL server agent (ie on a schedule). The package executes fine when I manually run it from Enterprise Manager but fails when scheduled. The latest schedule history is: "Executed as user: ATEA\SQLExec. ... OnStart: Drop table Results Step DTSR...more >>

Hi, Imagine I have 2 different tables, keeping a log of additions of items to some container, and another that keeps a log of when those containers were emptied. For example, simplified: CREATE TABLE additions ( logdatetime DATETIME NOT NULL, boxnumber INT NOT NULL ); CREATE TABLE...more >>

I need to show the 3 most-recent contact dates by our sales staff for each customer. I have a table that includes custid, custname, and contactdate - each row representing one contact with a customer. I need to generate a query to show: custid, custname, date1, date2, date3 where date1, ...more >>

I have 2 tables Table BOOKS with columns BOOK_ID(key), BOOK_NAME Table CHAPTERS with columns CHAPTER_ID(key), BOOK_ID, CHAPTER_NAME the BOOK_ID column in both tables are related my program assumes a new book has at most 1 chapter when I insert a new book I need to do 3 queries - ...more >>

I can connect and use the SQL Server database, but get errors when I attempt to backup to a unc path on the network. I can backup to a local folder. The SQL statement is: BACKUP DATABASE Mydatabase TO DISK='\\MyServer\MyShare\backup\dbbackup.bak' WITH DESCRIPTION='test' The error in the ...more >>

Hi; How can i call a store procedure within another store procedure with parameters? ...more >>

Hi, How can I log every activities from a table based on the colum level? For example, Employee table with columns: name, orgnization, salary and SSN, I don't care who access name and orgnization column, but I need to log who had accessed salary and SSN. Let's say we use windows authenti...more >>

Hi All, I've created a user-defined function as below: Create FUNCTION dbo.FnReportInstancesGet @AfterTimestamp timestamp = null output, @MailingNameFilter DTTypeNameLong = '%', @ReportEntityFilter varchar(50) = '%', @AccountIDFilter int = null, @StatusMessageFilter varchar(255) =...more >>

What are the advantages of using an Application Role instead of a single SQL Server login account? I'm new to the concept of Application Roles, but it seems to me like they would be functionally equivalent to having a single SQL login that my application uses to connect to the database. With an ...more >>

Is there a function in SQL Server that'll tell us what the local time in a certain time zone is? Basically, we have replication to London, and they want to modify the date columns to local UK time....more >>

Hi, I have a store procedure accept an datetime varible from ASP.NET. When user input an datetime value earlier than year 1753. I got an err: "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value." For example, user input 05/04/1001, which ...more >>

Hi, I have a store procedure accept an datetime varible from ASP.NET. When user input an datetime value earlier than year 1753. I got an err: "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value." For example, user input 05/04/1001, which i...more >>

I am in need to run a stored procedure on a nightly basis and create an ascii pipe delimited formatted file. I tried already xp_cmdshell to create the file from my queries but I get en error. for example if I write this format: SET @cmd = 'echo ' + @req_id + ',' + @patient_fname + ',' + @date...more >>

I need to use a list of characters in my where clause. I only need to compare the first 3 characters of the number. I keep getting 0 results when using the below sql statement. I tried like but it only wants to use one of the numbers in the list. Select '06/01/2006' , 'Active' , Count(A...more >>

I am using the SQLXMLBulkLoad.SQLXMLBulkload.3.0 object to load XML into SQL2000 tables. The tables have GUID keys. I am populating parent-child relationships and need to get a GUID while populating the catalog table and use it to populate the CatalogID FK on the Product table. I found ...more >>

Hello, I need to join two tables on a text field that is an unlimited amount of characters in one table and truncated to 10 characters in the other table. Should I use the RTRIM function? e.g.: where (b.crtd_user = rtrim(d.[user_id]) or b.crtd_user = d.employee) Thanks!...more >>

I'm using "sa" authentication on my servers. Is there anywhere that tells me best practices for security. Like, should I rename the sa account? thanks ...more >>

Hello all - One of my views is constantly timing out. I have tried various fixes, some that were recommended on the net, and none have resolved my problem. Here is my code for the view, not sure where/why it is timing out - this has worked for over 2 years now without any concerns SELE...more >>

I can add extended property for database users. But when I try to add extended property for a login like this: exec sp_addExtendedProperty 'MS_DESCRIPTION', 'test desc', 'login', SomeUser I got some error message. Does anybody know how to add extended property for a login? Thanks a lot...more >>

I'm using SQL Server 2005 Pro and I've been poking around Management Studio trying to figure out how to change the deafult location in which new databases are created (.MDF and .LDF files). How can I do that? Thanks! ...more >>

I need to list the names of all columns with triggers defined in a given database. I'm poking around the new Catalog views in 2005, and can find the names of the tables with triggers and the names of the triggers easy enough: SELECT t.name AS Table_Name, tr.name AS Trigger_Name, te.ty...more >>

Is there an easy way to backup the entire database so restore will take into account dependencies in the proper order? I want one strategy that gets the db and data and a second strategy that does not get that data, only the structure. Thanks ...more >>

This query displays data in a crosstab table by joining itself. It works fine. My question is, what do I need to do to make it dynamic. How do I change the query so that the columns for C02A, C02B, E010, C101 and etc. will display based on what is available instead of hardcoding. SELECT LN_NR...more >>

HelloWorld_CLR is the sample provided by Microsoft with SQL Server 2005. There is a method called SayHello in HelloWorldService. I could not find any reference to it. But if i write some code in it it gets executed. Any idea from where this method is getting called??? Mana ...more >>

Hi The input variable values in the following code (@TABLE_NAME ,@S_ID_VAL) is not getting set for the query. I get an error select @min_val2=min(id),@max_val2=max(id) from @TABLE_NAME where schema_id = @S_ID_VAL and status = 0 Msg 1087, Level 15, State 2, Line 1 Must declare the table ...more >>

Hi I am suppose to get the maximum and the minimum values in the variables @max_val2 and @min_val2 but instead i am not getting any values printed except the query(SQL_TEXT) DECLARE @SQL_TEXT nvarchar(2000), @TABLE_NAME nvarchar(200), @min_val2 numeric(10), @max_val2 numeric(10), SET...more >>

for each record returned in this select statement I want to update a column x in table tOrderHeader to 'SL' SELECT tOrderDetail.ID, COUNT(DISTINCT tOrderDetail.PCN) AS cnt FROM tOrderHeader INNER JOIN tOrderDetail ON dbo.tOrderDetail.ID = dbo.tOrderHeader.hKey WHERE ...more >>

Hi, Thank you for taking the time to look at this problem :o) I have been tasked with the job of automating some of our monitoring/survailence jobs and have run into some troubled waters, hopefully someone can supply me with the piece of information I need to either scrap it all or get o...more >>

Hi, Most of the trigger samples I have seen only have insert. I have the following 2 tables TABLE1 COL1 COL2 1 5 2 4 TABLE2 COL1 COL2 COL3 1 5 TEST 2 4 TEST2 How can I create a trigger on table 1 so that when ...more >>

Hi, If you type "select * from orders" in QA and only parse it (while Orders table does not exist) using ctrl+F5, you get no error. But this way: set parseonly on select * from orders set parseonly off Error occurs indicating that Orders is invalid object! I expected to get no error even ...more >>

Hi, If you type "select * from orders" in QA and only parse it (while Orders table does not exist), you get no error. But this way: set parseonly on select * from orders set parseonly off Error occurs indicating that Orders is invalid object! I need some clarifications. Thanks in advanc...more >>

Hi, Can anyone give me a sample deployment script /********************************************************************* HEADER - DESCRIPTION AND COMMENTS *********************************************************************/ /* QA REQUEST : CREATED BY CREATED DATE : SCRIPTE...more >>

One of my field is having 3 values: H,F,Y If I say in a query that : 1st case: field = 'H' or Field='F' 2nd case : field <> 'Y' Which of the above case is more efficeient and why? ...more >>

i would like to know if there is a way how to query a table and add some fields with data filled in without really adding those fields into the table structure? for example: <prodCode> <prodDesc> <prodPrice> <prodDateMfg> <prodDateExp> 01AB MyProduct1 5.25 200601...more >>

Hello, I would like to know if it's possible to make both application and SQL server synchronize. I develop: In a normal application, you make the connexion with the database, use data from database but when you want to update your data, you must ask another time the data from...more >>

I am try to write an query to retrieve several records from the database and run on QueryAnalyst However, when I Save the result, or copy the result, from the Query Analyst, and pasted in Excel The character '0' is missed if it occurred in the first character of string... Any solution? ...more >>