hello, i am using "Microsoft.SqlServer.Management.Smo" to code a backup procedure, as the default backup location is "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup" directory, how can i change the backup location to another directory? For example, "c:\backup", thanks! ...more >>

I'm getting the following error message when I try to Drop a Primary Key index: Exception Details: System.Data.SqlClient.SqlException: An explicit DROP INDEX is not allowed on index 'primarykeyindexname'. It is being used for PRIMARY KEY constraint enforcement. How do I remove the Primar...more >>

select p.addressline1, p.addressline2, p.addressline3, p.municipality, c.candidateid from candidates c left join postaladdress p ON p.EntityID = c.CandidateID where entitytypeid = 1 I have an index on postaladdress.entityid and candidates.candidateid. I'm still getting index scans and 4...more >>

Hi, I am using SQL Server 2005 (SP1) on Windows Server 2003 EE (SP1). I get following error while accessing Excel or Text file data sources with OPENROWSET or OpenDataSource and Microsoft.Jet.OLEDB.4.0: From SQLAgent Job Log file: Cannot initialize the data source object of OLE DB provid...more >>

we have old data and we don't want to delete or show on the query. is there a feature in Sql that we flag them that they will not be scanned (will be ingnored) when we query? thanks ken ...more >>

Hey I'm abit stuck at this problem and could use some help. I'm trying to find 2 queries. (note that 2006-09-06 shouldnt be listed since its a range 2006-09-01 and 2006-09-05 in desired result) also note that C will not be displayed in either result since it has all dates matching the Date...more >>

I would like to store time data that has only minutes, seconds and hundreds, no hours, in this format 1:12.46 which would be 1 minute, 12 seconds and 46 hundredths. If I must attach a defualt date thats ok but it cannot hae hours thanks...more >>

hi i did : create table pp ( id int ) backup full insert into pp values ( 1 ) backup diff insert into pp values ( 2 ) backup log 1 insert into pp values ( 3 ) backup log 2 insert into pp values ( 4 ) backup log 3 -- tail i want to restore the rows 1, 2, and 3 ...more >>

I'd like to retrieve some records that have not been modified in the past 2 days using Query Analyzer. However the formats in two date fields for comparison are different. DateMod + 2 < Today's The DateMod is in yyyy-mm-dd format e.g. 2006-09-18 and the Today's format is mm/dd/yyyy...more >>

When write a sql select statement, for example: Statement 1: select 1 from t1 inner join t2 on t1.f1=t2.f1 and t1.status='A' statement 2 : select 1 from t1 inner join t2 on t1.f1=t2.f1 where t1.status='A' Is there any difference between statement1 and statement 2? Thanks. ...more >>

Hello: I've tried deleting Logins a couple of ways via DMO. My attempts fail. Code I've left out: 1- Creating the SQLServer2 object 2- Connecting to SQL Server These 2 steps are fine as I use the SQLServer object for other tasks. At this point I'm trying to delete a database th...more >>

Hello, I have to insrt rows into a table where the primary key consists of 6 columns. The source table contains 15000 rows which are all distinct, but 5 of the rows have a dupliacate primary key. I identify them like this: select col1 + col2 + col3...+ col6 from tblx Group by col1 + ......more >>

What is the best way to go about creating covering indexes for a query for instance that joins 6 tables together? Can you create a covering index for the entire query? Or do you have to break out each set of data being pulled form each table and create a covering index for each table. Then ...more >>

I want to pass parameters to a sql query. For example: WHERE ANS IN (" & MyString & ") So if MyString = 'This', 'That', 'The Other' It will populate the Where clause. But I also want to be able to pass an empty string and have it return all values for ANS. Any ideas? tod ...more >>

I have got a stored procedure that sends out E-mails in a loop as follows: WHILE ... BEGIN -- retrieve next subject and body here EXEC [master].[dbo].xp_sendmail @recipients = @SendTo, @copy_recipients = @CopyTo, @message = @MessageBody, @subject = @MessageS...more >>

Is there a way to generically refer to a SP name with a SP? For example, something similar to this: create procedure 'test' SELECT *, this.ProcedureName instead of SELECT *, 'test' I dont think there is a way to do this but I wanted to make sure -- JP ..NET Software Developer...more >>

Hello to all, I have been hitting my head against the wall on this problem. I have a client for some reason that requires a timestamp with millisecond precision. Since I am already using SQL to do other tasks I tought it would be good tosimply use the SQL CURRENT_TIMESTAMP and voila I get w...more >>

Which is better performace wise to execure dynamic SQL or even calling an SP within another SP? -- JP ..NET Software Developer...more >>

I have a simple query which returns me id (an integer), location_code (an integer), department_code (an integer), date_worked (a datatime) and hours_worked (a decimal): select ts.id, ts.location_code, ts.department_code, ts.date_worked, ts.hours_worked from time_sheet ts where ts.employee_i...more >>

Hello, I have numeric (not money) values in the format 5000000. I would like to convert it so that it contains thousand seperators as in 5,000,000 Ric...more >>

Hi Folks, I need to migrate data from SQL Server 2005 to a UniData database. I've setup a linked server using an IBM OLE DB provider for UniData. This driver does not support transactions. Initially, I setup a trigger that looped through the inserted table, grabbed the values I needed and t...more >>

When I do this in a stored procedure: if @@FETCH_STATUS < 0 begin set @TmpSQL = 'select * into ' + @TmpTableName + ' from ##TMP_ROLLBACK_INFOS where 1 = 0' exec (@TmpSQL) end set @TmpSQL = 'set IDENTITY_INSERT ' + @TmpTableName + ' ON' exec (@TmpSQL) ...more >>

Hi All,. I have a quick question about the performance cost of adding columns to a SQL table. Scenario\Background: ================ I have an insert trigger and an update trigger for one of the SQL Server 2005 tables in our application, say for example lets call this table as Table1. When...more >>

Hi Folks, I have a CLR trigger that needs to call a web service. I want to grab a primary key from the inserted table and pass it as a parameter to a web service method. However, when I try to pass a value from the SqlDataReader, I get an exception. See CLR trigger code below: public sta...more >>

Anybody have any luck with this one?...more >>

Anyone ever seen something like this or can tell me what the heck it is? Looks like a stored proc to me but I can't find it. Pulled this out of profiler trace. Developer says it's .... a mystery to him. -- burt_king@yahoo.com...more >>

I am trying to find a way to stop or abort a query in Query Analyzer for troubleshooting purposes. Is there a function in SQL that would allow me to do this? If not, has anyone seen a udf that they would be willing to share?...more >>

I tried doing a convert at the concatenation string, But it still is doing the same thing My part of the code that is likely causing it is as follows *Bulk copy the data from table Trax_audits to the data file */ select @filepath = '\\Filename\volume\Track\' select @filename = 'Audits' /...more >>

Hi, I have atable with following two columns Month yearQuarter 1 2005/Q1 2 2005/Q1 3 2005/Q1 4 2005/Q2 1 2006/Q1 2 2006/Q1 3 2006/Q1 4 2006/Q2 I want to create a col in query that will give me output like:...more >>

How to convert DATE from one format to another. datatype: datetime original format: 2006-09-25 10:48:19.000 I need to convert to 2006-09-25 any TSQL function? I want to do this in a single SQL query. The above date value can be NULL as well. If it is null, It should no do anything. ...more >>

Or "counts" if it's not possible? I want to see which tables occupied most of the hard disk space....more >>

Hello, I wish to create a trigger to execute an external command. I plan to use the xp_cmdshell procedure to do this. I wish to pass as the argument string the table that the trigger is being called against. Can anyone tell me how to do this in the T-sql statement. Many thanks ...more >>

my purpose is to check the two database in two different server if there has any different. And synchronize these two database continusly. if one database had change, the other will also change ...more >>

Hi, I have 2 databases in SQL and in Access also. One is used for developement in Access and in SQL. and other is online for clients. I want to update Developement server by the clinet's databse server. So is there any script or store procedure to acquire the solution of my problem? ...more >>

Hi, I have installed MS sql server management Studio Express. Downloaded the AdventureWorks_Data.mdf Now would like to have this as a database to use. Right clicked on databases, Attach, in database to Attach added the path to the .mdf, then clicked OK. The error message is: Unable to open ...more >>

Hi all, I've got some problems deploying a CLR stored procedure which calls a webservice. It is written in C# by using the VS 2005 . This procedure is invoked by some params (strings, decimals, ints). When I deploy this procedure to the SQL Server 2005, the params having the data type 'decima...more >>

Hi i have two databases on two different server say DatabaseA on server1 and DatabaseB on Server2 i have store procedure which get data from databaseA and insert into DatabaseB which is on server2. on one server is easy like reference DatabaseA.dbo.table1 and DatabaseB.dbo.table2 but i dont k...more >>

Dear All Any performance differernt between follow two codes? SELECT * FROM TableA INNER JOIN TableB ON TableA.FieldPK = TableB.FieldPK WHERE TableA.NormalField = @SomeValue SELECT * FROM TableA INNER JOIN TableB ON TableA.FieldPK = TableB.FieldPK AND TableA.NormalField = ...more >>

Hi im running a MS sql database behind my Cold fusion site. I have a question on like queries that someone here will probably be able to help me I have a query that uses the following code name like '#search#%' The word Danger Sign appears as a name in my database. When I do a...more >>

Hi all, I want to know if it is possible to deadlocks to occur when executing SELECT quarries only. I don't have any transactions activated at this time. What I have gathered so far is that deadlocks will occur when we use INSET and UPDATE statements mostly. Recently we have face...more >>

Hi, I have a table defined like the following example, Col1 Col2 Col3 AA 1 102 AA 2 101 AA 3 103 AA 4 102 AA 5 101 AA 6 104 AA 7 ...more >>

hello, i am developing a web-based application with ASP.NET and SQL Server 2005 in Windows 2003, i have added the "network service" account as "db_owner" role to a database called "appdb" , i know "db_owner" has has no retore permission, how can i grant the right to the "network service" acc...more >>