sql-server

Retrieving Content from a Sub Content

I'm using SQL Server 2k8 and have a table that needs to be able to pull dynamic content. So, I was thinking of having 2 columns which would act like a Parent and Child. ContentId - SubContentId - ContentInfo 100 100 data here 101 100 data here 102 100 data here 103 100 ...

I need to pull large backup files from my clients' servers to my server every month

I have clients out there running an SQL Server Express 2005 and each of these needs a backup each month and that backup needs to be moved to our server in case they lose their backup. Our software automatically backs up the database each month but we have to manually go in and copy it across. Is there any way to automate the copying of f...

Problems with string parameter insertion into prepared statement

Hi, I have a database running on an MS SQL Server. My application communicates via JDBC and ODBC with it. Now I try to use prepared statements. When I insert a numeric (Long) parameter everything works fine. When I insert a string parameter it does not work. There is no error message, but an empty result set. WHERE column LIKE ('%' ...

How to get identity value after calling exec(@Sql)

I am trying to find the identity value of an inserted record inserted by exec(@Sql), but it seems that exec() excutes in a different scope. /* create table [dbo].[__Test]( [id] [int] IDENTITY(1,1) NOT NULL, [description] [varchar](100) NULL ) ON [PRIMARY] GO */ declare @Sql varchar(512) set @Sql = 'insert into [dbo].[__Test] ([...

How can I make "month" columns in Sql?

I've got a set of data that looks something like this (VERY simplified): productId Qty dateOrdered --------- --- ----------- 1 2 10/10/2008 1 1 11/10/2008 1 2 10/10/2009 2 3 10/12/2009 1 1 10/15/2009 2 2 11/15/2009 Out of this, we...

Automate installation SqlJobs SqlServer

Is there a way to install SqlJobs on SqlServer by a script? I would like the sqljobs to be installed when setting up a Database remotely at a customer. Is this possible? ...

Can I Select and Update at the same time?

This is an over-simplified explanation of what I'm working on. I have a table with status column. Multiple instances of the application will pull the contents of the first row with a status of 'NEW', update the status to 'WORKING' and then go to work on the contents. It's easy enough to do this with two database calls; first the SELECT ...

Count of Distinct Rows Without Using Subquery

Say I have Table1 which has duplicate rows (forget the fact that it has no primary key...) Is it possible to rewrite the following without using a JOIN, subquery or CTE and also without having to spell out the columns in something like a GROUP BY? SELECT COUNT(*) FROM ( SELECT DISTINCT * FROM Table1 ) T1 ...

Thoughts On Extended Stored Procedures

I am looking to insert and update records in a database using functions and logic that are not available in SQL Server or any other RDBMS for that matter. After Googling around a bit this morning, I have come across the concept of Extended Stored Procedures. As far as I can tell, I should be able to compile my desired functionality int...

SQL Server 2005 (Express) - Login vs User

I'm quite new to Microsoft SQL Server. I have some experience with MySQL, and there you have a user with priviliges, if I understand things right; these priviliges decide which databases you have access to on the MySQL server. However now I am in the situation where I have to restore a database on my SQL Server 2005 Express, and this da...

Change a Primary Key from Nonclustered to Clustered

Suppose I have an SQL Server 2005 table, TableX, with 2 indexes on it: PK_TableX = PRIMARY KEY NONCLUSTERED on FieldA IX_TableX_FieldB = CLUSTERED on FieldB I want to switch the PK to be CLUSTERED, and the other index to be NONCLUSTERED. I have to assume that the database will be in use at the moment I try to change the indexes round...

how to start/proceed for implimenting database layer for C#.net

Its first time for me to start with something like this. In applications with decent size DLL (Data Logic Layer), BLL (Business Logic Layer) is common to implement. But with no great experience around this area i am struggling regarding where to start. Most of the time i did simple application where to open connect, get data, close con...

SQL caching strategy

I'm working on an interactive contact search page (contacts are returned via ajax as you type or select criteria). I want this page to be very responsive. There is a complex set of rules to determine which contact records a given contact can see; these rules are rolled up into a user-defined function, DirectoryContactsByContact(@Contac...

Is there a better way to keep Stored procedures in sync across databases that is also easy and can be done within Visual Studio?

The code is in sync because I am in charge of that aspect (its just xcopying to the different servers when I need to) The DB people are incharge of keeping the SPs in sync and right now that is asking them to manually do a ALTER PROCEDURE on a specific DB. Is there some easy way to do this in Visusal Studio? Id love to have a DB proj...

SQL Server Performance with Key/Pair Table vs XML Field and XPath

I've seen a few questions on this topic already but I'm looking for some insight on the performance differences between these two techniques. For example, lets say I am recording a log of events which will come into the system with a dictionary set of key/value pairs for the specific event. I will record an entry in an Events table with...

SQL Server 2005/2008 - Import a fixed width text file via the command line?

In MySQL I am able to create a table with fixed column widths and then can use the load data infile command to import a fixed width file. For example: Fixed width text file = JOHN 1234 Imports into table: Username - CHAR(8) Password - ChAR(4) The beauty of this approach is that the file is 'chopped' up based on the column size...

SQL - how to count groups of rows and display the top/bottom 3

I realise this is likey to be an easy one, but my SQL is basic at best. Lets say I have a table containing a list of orders, with 'item_id' being one of the columns. I need to display the 3 least (or 3 most) popular orders of item. I know that I need to group the orders using item_id and then count them. Then I need to display the bott...

Built-in database role in SQL Server 2005 to permit execution of stored procedures?

In SQL Server 2005, there are built in roles: db_reader db_writer etc. Is there any role that lets a user execute an stored proc? I don't want to use db_owner, because that will permit deletion and updates, which I don't need. The only permissions I need are: SELECT EXECUTE ...

Easy way to inspect BCP .dat file?

I'm getting BCP error "Unexpected EOF encountered in BCP data-file" during import, which is probably misleading. I strongly suspect that some field has been added to the table or there's some offending character is in the file. How would I go about inspecting the contents of .dat file visually? Are there any good hex viewers where I ca...

Location Based Search (Lat,Long) TSQL MS Server 2003 R2

Group, I'm looking for a query that can do a location search in radians with in a binding box. With a table structure like ProductID,latitude,longitude,timestampGMT. Any helpful suggestions. Chad ...