sql-server

Strategy for storing supplemental data alongside imported data

I have ran into this problem a few times and I am wondering what other people are doing. When I am creating a database, sometimes I have to import data into a table on a regular basis, let's say daily. What I normally do is delete all of the records and reimport every record from the external data source. Many times I will have to stor...

SQL Server Date format Issue

I have this query to display records based on date Select * From orders Where CONVERT(VARCHAR(50), Orderdate, 101) = CONVERT(VARCHAR(50),'1/21/2010', 101) Though there are orders for today, the query is failing because the date passed is not 01/21/2010 How can I fix this issue in the query itself as the date passed by the other ...

Get date of last successful job run?

I have a single step job that executes a stored procedure. I would like get the date of the last successful job execution time so that I can just update a delta instead of the whole set of data. Right now I have the job setup to run once every day, so I have a default parameter that if it's null I set it to GETDATE() - 1 so I'm still u...

Designing a conditional database relationship in SQL Server

I have three basic types of entities: People, Businesses, and Assets. Each Asset can be owned by one and only one Person or Business. Each Person and Business can own from 0 to many Assets. What would be the best practice for storing this type of conditional relationship in Microsoft SQL Server? My initial plan is to have two nullabl...

Calling stored procedures, passing NULL values, dealing with boolean values

When calling a stored procedure I am concatenating values together, my question is how do you call a stored procedure but send a 'NULL' value in one of the params? Lets say that AID = null, but if I pass that to my query I get an error?! QueryConn.Execute("Search_Res " & Count & "," & AccessList("InvoiceLevel") & "," & AID) Ok, so my...

TSQL - Help with UNPIVOT

I am transforming data from this legacy table: Phones(ID int, PhoneNumber, IsCell bit, IsDeskPhone bit, IsPager bit, IsFax bit) These bit fields are not nullables and, potentially, all four bit fields can be 1. How can I unpivot this thing so that I end up with a separate row for each bit field = 1. For instance, if the original table...

SQL injection on Classic ASP pages with parameterized queries: text fields

I've parameterized my queries in my Classic ASP app, but am unsure whether I need to sanitize or scrub free text fields or if the parameterization is sufficient to prevent injection. ...

What is the alternative of bigint in c#

Hi, what I am suppose to use when handling a value in c#, which is bigint SQLSERVER DB ? Thanks ...

String processing during table creation and inserting from another table

I have a table that contains file paths, like so: -------------------- |Files | -------------------- |path nvarchar(500)| -------------------- I want to split it into two tables, one containing unique directories and one containing filenames: --------------------------- |Files | ------------------------...

Quickly generate ascii text sql scripts based off of sql 2008 database

This is starting to frustrate me, mostly because it should be easy to do but I can't find anything. I am trying to version control my database. The method I have decided upon is to generate scripts at each check-in that creates a sql script for every object in my database. Most of my edits are done via the visual designer in SSMS and ...

How to get value instead of fieldname in Unpivot query

Hi This is my query Declare @SampleUNPivot Table(ID int ,Name varchar(50),a int,b int,c int,d int) insert into @SampleUNPivot values(1,'name1',1,2,3,4) insert into @SampleUNPivot values(2,'name2',10,20,30,40) insert into @SampleUNPivot values(3,'name3',11,21,31,41) insert into @SampleUNPivot values(4,'name4',14,24,34,44) Select ID,Na...

Drop database only if Backup is successful

Hi, This might be an easy one for some one but I haven't found a simple solution yet. I'm automating a larger process at the moment, and one step is to back up then drop the database, before recreating it from scratch. I've got a script that will do the back up and drop as follows: Use [Master] BACKUP DATABASE [databaseName] TO DI...

How do I backup the data in SQL Server 2008 on a third party host?

Hi, I have a SQL Server 2008 database that is hosted by a third party host (heart internet). How would I go about backing this up? I used SQL Server Management Studio Express 2008 to create the tables within the database, but the backup options within this app seem to be only of use if you have direct access to the server machine (whic...

sql query disregarding all rows that have all null columns

I have an SQL Server query that needs to count the number of rows returned, but I need to disregard rows where all the column values are NULL. Some rows have NULL values for some of the columns, but that is ok. I just need to filter out the ones that have ALL NULL values. Right now I am returning all rows and using a SqlDataReader itera...

SQL Server database creation scripts in Visual Studio

As part of a ASP.NET MVC project I'm working on, I'll be working with SQL Server 2008 Express. I'm not sure how I should go about having a version-controllable (I'm using Mercurial) way of recreating the database. Ideally it should be run every time I perform a build in Visual Studio 2008. EDIT 1: In response to couple of the answers,...

SQL [Conversion to bool]

C++Builder ADOQuery SQLServer I'm using a stored procedure with this select SELECT Name, COALESCE( ( SELECT TOP 1 0 FROM TbUserParam WHERE TbUserParam.ID_User = @ID_User AND TbUserParam.ID_Param = CfgListParIzm.ID_ListParIzm ), 1) Visi FROM CfgListParIzm WHERE ...

SQL Server - Better Data type to store large string value

we have a database table which has around 200,000 records. which includes 3 ntext columns, which hold string data with length vary from 4000-70000. but a mere selection on the table takes more than 1 minute to return data. and even using where condition, and indexes to select 12000 records for a condition it takes 40 sec. so we decided...

Is there a SqlFileStream like class that works with Sql Server 2005?

For the background to this question, see “How to I serialize a large graph of .NET object into a SQL Server BLOB without creating a large buffer?” that now has a large bounty on it. SqlFileStream gives you an IoStream that sits on top of a blob (varbinary) value that is stored in the database. However due to limitations ...

Execution Plan reuse

Consider the following "code" define stmt1 = 'insert into T(a, b) values(1, 1); define stmt2 = 'select * from T'; MSSqlCommand.Execute( stmt1;stmt2 ); MSSqlCommand.Execute( stmt2 ); Investigating the cached query-plans using: SELECT [cp].[refcounts] , [cp].[usecounts] , [cp].[objtype] , [st].[dbid] , [st].[objectid] , [st].[tex...

SQL WHERE clause for column all capitalized

table_beatles contains the following data in column name. John PAUL george RINGO In MS-SQL is there anyway to get any item which is all caps? eg SELECT * FROM table_beatles where name is (AllCaps SYNTAX HERE) to return PAUL and RINGO. ...