I'm looking to return an object hierarchy from a .net clr function/procedure call, i.e. parent classs with children classes (different class type). I am aware of the following options for doing this: - Serialize to xml and use sql xquery to work with it - De-normalise into a flat structure an...more >>

Hi, my query is declare @startdate datetime declare @enddate datetime select @startdate = '2006-09-09 14:06:49.347' select @enddate = '2006-09-09 14:07:26.020' select datediff(n,@startdate,@enddate) select datediff(s,@startdate,@enddate) it's returning 1 37 the difference be...more >>

We are purchasing a Dell 2950 for the backend of a write intensive vendor application running SQL Server 2005. 2 fixed disks will be Raid 1 and remaining 6 disks will be Raid 5. OS will be on RAID 1. Data Files will be on Raid 5. Should the log files go on the Raid 1 or Raid 5 drives? I h...more >>

I have tuned a stored procedure. Now before I apply the changes to the production, I want to compare the execution time of tuned SP on dev machine and non tuned SP on the production server. I am seeing how much time a query takes to run on the status bar of query analyser. But the time display...more >>

Let's say I have a Product table like this: ProductName ProductRank Alpha 1 Bravo NULL Charlie 5 Delta 2 Echo NULL Foxtrot 4 Golf 3 I want to write a query to return the data like this: ProductName ProductRank Alpha 1 Delta 2 Golf 3 Foxtrot 4 Charlie 5 Bravo NULL...more >>

We are installing SQL 2005 on a server that has windows regional settings defined as Spanish (Chile). All the clients will also use Spanish (Chile) regional settings. We don't want to change the collation of the db (it is set as SQL_Latin1_General_CP1_CI_AS throughout the database at the column ...more >>

I am trying to write a stored procedure that could create a table called [B]tblManagerHierarchy[/B]. It is a table that contains recursive data. The data is coming from tblEmployee where it contains fields called EmpNum and ManagerEmpNum. [B]tblEmployee[/B] (EmpNum, ManagerEmpNum) The t...more >>

Has anyone tried implementing the Nested Sets representation of hierarchies? I'm curious to hear of other's experiences, other than Celko's of course! The biggest question I have is how do you handle the DML? The article's I've read want to limit INSERTs to one at a time, say, via PROCEDURE,...more >>

Hello all, I haven't done SQL in years and I can't see what I'm doing wrong. I have two tables. A primary and a secondary. I am trying to pull items monthly quantities from primary but check a second table and if item has a qty for it in that month, use that qty instead. I've simplified it d...more >>

I have job which runs a stored procedure which (among other things) sends information including the duration of the backups of each database. The backups are done through maintenance plans we have created. In SQL Server 2000, this was easy to get at through the duration column in the sysdbm...more >>

Any recommendations on a hosting company for a dedicated Windows Server that is MANAGED and would allow one to install SQL Server on the box. ...more >>

I am using vb.net 2005 and SQL server 2000. In my table I have a date field of type "smalldatetime". In my vb application, the user may or may not enter a date value into the appropriate text box. I then want to pass the value of this text box as a datetime variable to my stored procedure whic...more >>

Hello... Are you aware of any case where a MS SQL 2005 Server can't run a command writen for a MS SQL 2000 Server? Thanks..! ...more >>

Hello, In SQL server, how can produce First day of last month and last day of last month in oracle date format from the system time ? for example 01-Jun-2007 and 30-Jun-2007 Thanks for any help or information. ...more >>

I am taking a sql2k profile, using the replay template of a few simple db operations. All local, I have all access, etc. I replay the trace against a backup of the pretrace db, with all the same user names, pwds, roles, default dbs, etc. The first few trace statements execute ok. After hitt...more >>

Is there a way to create an application wide connection object in C# so I don’t have to create a new one for every page? My issue is that while I can do this within the scope of a single aspx page. It can really drag down performance because each portion of the page is made up of user contro...more >>

I want to be able to change the value of two tables where the key in one table is a key in another table. There is a check constraint set on the main table so that you can not change the value in both table. However for one user, I want to change the value of the key from one number to an...more >>

Hi... I was experimenting with getting dates in and out of Sql Server 2005 and working with them programmatically, and I was running into behaviors I didn't expect. From what I've seen in the docs, the last line should produce the same output, but it doesn't. Basically I'm trying to go ...more >>

Hi, I have a special user extended Stored Proc in master db. UDFunction in each database to call that XP. I need to create a log table to monitor users every time they execute that XP either by UDF or by calling XP directly. I need to know username hostmachine datetime and sql statement ......more >>

Hi, This is probably a simple question, at least I hope so. I would like to use a variable in place of a list of items used in the IN() command. But I can't quite figure it out, here's what I've tried... Select * From mytable Where acctperiod IN ('2006_11', '2006_12') -- This works fi...more >>

I am looking in BOL at the ROW_NUMBER function and it doesnt specify what data type the returned number is in, is it integer, bigint? any ideas? thanks! ...more >>

Hi, I posted this code earlier. However, it was a different issue which got resolved. Here I am using a Access form to update record in sql server pubs databse using parameterized stored procedure. However, the update is not taking place. I appreciate any help on this. Thanks CODE: Private ...more >>

I have inadvertently came across a way to delete every record from my table by using invalid stored procedure code. Basically by adding a subquery which is invalid to the stored proc, it passes validation and is created on the database. Later executing causes all table data to be deleted. B...more >>

Is there some easy function that would allow me to ignore the time portion of a datetime field when running a query? I don't care if it sets the time to 12:00:00. I only want to know that something happened on a certain day....more >>

The following query truncates my data to the first 8000 bytes. Any suggestions on how I should accomplish this? UPDATE TARGET_DB.stix.MedChart SET ChartNote = (CONVERT(VARCHAR(8000),b.Notes) + CONVERT(VARCHAR(8000),b.Notes2) + CONVERT(VARCHAR(8000),b.Notes3...more >>

I am trying to created federated partitioned views (for update), my ideas is to split a table to two servers and create a single view based on the split local table and a table on the linked server. Customer Id (Integer) is PK for the table (see DDLs below) , to spread the load equally I ...more >>

HI All, I'm trying to produce a report, but I'm having trouble with a field which contains numbers and semicolon separators the filed looks something like this... 38100 4400 4430 4400;4430;4490;45000;4520;45510;4511 3841 4430 48400 4400 I need to find every occurrence 4430 and ...more >>

Hi, How do I get a query to show a range of dates from today to 120 days ahead. I want to show the results of an event between today and anything in the next 120 days. This is what I have for a where statement but does not seem to be working: WHERE (dbo.MediaDetail.EventDate BETWE...more >>

Hi, I have the following code which I am testing to check the process of passing paramter to sql server stored procedure from an access form. However, the code throws me the above error. I am running this on my local sql server setup and I am sure that the userid and the password is correct. ...more >>

I originally created a table with option ansi nulls off. now how do I turn ansi_nulls on for this table without drop/create? ...more >>

Hi All I have a table that I want to pivot as illustarted below and I am unsure how to go about doing this in SQL Server 2005. Any assistance would be appreciated. CREATE TABLE Sales ( col001 VARCHAR(30), col002 VARCHAR(20), col003 VARCHAR(20), col004 VARCHAR(20), col005 VA...more >>

Hi all. How do I define "Use double quote as Text identifier" using BSP file export from a SP (SQL2005). I am moving for old DTS to directly export to .txt files..... EXEC master..xp_cmdshell 'BCP multicase..vekRptItegraFilExport out K:\MultiCase\Filexport\SQL2005Eksport\ProduktFile.txt...more >>

Hi all, Now I inherited a new sql2k instance (itanium 64 cluster) and some fantastic things, that I've never seen. sp_helpdb did not work. I found out that the owner of some user db's did not match to logins in master. I used sp_changedbowner for them and everything is ok with them. Remain...more >>

There is a folder which contains several different xml files. Question for each xml file, how can I get the contents of the xml file and then pass it to a Stored Proc? Is this to do with a sql function that takes the file path of the xml file, i.e. openxml or something similar? Thank...more >>

Guys Quite a tough question. We are using .net 2 and the new transactionscope class to execute a distributed transaction across two SQL2005 servers from a webserver running windows 2003. The code in test simply calls a stored proc on both sql servers that just inserts a row into a test table ...more >>

Hello! I have a problem with specific query. I don't know how to write it... :/ My table looks like that: Time_stop | Time_Start | Name 2007-07-10 11:00:00 | 2007-07-10 11:45:00 | A 2007-07-10 12:00:00 | 2007-07-10 13:00:00 | B As result I need to get 1:45 (total sum of difference betwee...more >>

I have a stored procedure that only allows for one input at a time, how can I change this procedure to allow more then one? I want to pass several items to this procedure such as this name1,name2,name3,name4, instead of having to calling over and over again ...more >>

Hi, I want to store some global settings for all of my TSQL stored procedures and SQL statements. This is something similar to what we have with app.config in C# programming. So far I am storing all of my settings in a table and a function returns the values. Something like this: pri...more >>

I run into problems after I updated MSDE to SQL Server 2005 Express. The following script used to work with MSDE. Now with 2005 Express the @@ROWCOUNT returns always a zero value and the last restore operation fails! RESTORE DATABASE Test FROM TestDev WITH NORECOVERY DECLARE @Backup...more >>

Hi again, I need another good practise advice. Everybody (100 persons) in our compagny manages one or more projects. Each projects needs two tables: table 'project' (general information), table 'projectdetails' (details of projects) and one or more result tables (one per project) like ...more >>

What is the syntax to update a column in one database using a value from a different database/server. I either get this error, if I leave out dbo: The column prefix 'Comp-102.dev.tblCentre' does not match with a table name or alias name used in the query. or this eeror if i put dbo in: The...more >>

As I need to do some tasks when Begin Transaction and Commit Transaction raised, How can I do to like OnBeginTransaction Event() And OnCommitTransaction() on SQL Server? ...more >>