What is LINQ to SQL template? What is the advantage of it?
What is LINQ to SQL template? What is the advantages of it? ...
sql-server
Selecting a null columns with conditons
ID Date Day Status 001 23/02/2009 Monday Appear 001 24/02/2009 Tuesday Appear 001 25/02/2009 Wednesday Appear 001 26/02/2009 Thursday Appear 001 27/02/2009 Friday null 001 28/02/2009 Saturday Appear 001 29/02/2009 Sunday null 002 ..., Query Select ID, Date, Day, ISNull(Status, 'Holiday') from table1 The above query is appearing Sta...
How to separate programming logic and data in MS SQL Server 2005?
I am developing a data driven website and quite a lot of programming logic resides in database stored procedures and database functions. I found myself changing the stored proc/functions quite a lot in order to fix bugs or add new functionality. The data (tables) have remained mostly untouched. The issue I am having is keeping track of...
"Round half up" on floating point values
We are stuck with a database that (unfortunately) uses floats instead of decimal values. This makes rounding a bit difficult. Consider the following example (SQL Server T-SQL): SELECT ROUND(6.925e0, 2) --> returns 6.92 ROUND does round half up, but since floating point numbers cannot accurately represent decimal numbers, the "wrong"...
Saving file from SQL Server into Memory Stream in C#
I've method which saves the file saved in db into file on disk. How would I modify it so method returns MemoryStream? public static void databaseFileRead(string varID, string varPathToNewLocation) { using (var varConnection = Locale.sqlConnectOneTime(Locale.sqlDataConnectionDetailsDZP)) using (var sqlQuery = new SqlC...
C# DataSource Class and Thread Safety
What would be a good way to writein C# a .NET3.5 Thread Safe DataSource Class. The class would connect to SQL Server and every method would execute a Stored Procedure. When the code worked single threaded. I had a Singleton DataSource Class with a private SqlConnection member. Each method opened and closed that connection. When running...
SQL Index Question: Why does SQL Server prefer this NONCLUSTERED index to a CLUSTERED one?
I have the following query: SELECT COUNT(*) FROM FirstTable ft INNER JOIN SecondTable st ON ft.STID = st.STID As you can guess, "STID" is the primary key on "SecondTable"... and "FirstTable" will have a pointer to that second table. Here are the indexes that I have: FirstTable: NONCLUSTERED INDEX on column "STID" Sec...
SQL Server Express 2005: How to import fixed width data using BCP
I’m trying to import fixed width data from text file into SQL Server Express using BCP. I have read some Microsoft’s web pages about this matter, but I haven’t got far with them. Can please give some examples or point to some good tutorials about this subject? ...
Composite Primary keys and Foreign key constraint error
Hi, I have an SQL table defined as below: CREATE TABLE [TestComposite] ( ID int, SiteUrl nvarchar(255), Name nvarchar(max) NOT NULL, ParentID int NULL, PRIMARY KEY (ID, SiteUrl) ); Items and folders are stored inside the same table, if an item is inside a folder, the ParentID column is the ID of the ...
How to append EOF to file using Perl or Python?
I’m trying to bulk insert data to SQL server express database. When doing bcp from Windows XP command prompt, I get the following error: C:\temp>bcp in -T -f -S Starting copy... SQLState = S1000, NativeError = 0 Error = [Microsoft][SQL Native Client]Unexpected EOF encountered in BCP data-file 0 rows copied. Network packet size (by...
SQL Server 2008: produce table of unique entries
Hi all I have the following problem. I have a table with a few hundred thousand records, which has the following identifiers (for simplicity) MemberID SchemeName BenefitID BenefitAmount 10 ABC 1 10000 10 ABC 1 2000 10 ABC ...
Testing EF SQL Server based application with in-memory SQLite?
Hi I am using SQL Server with Entity Framework for a development of web app in .NET 4 with VS2010 RC. I would like to prepare testing database with sample data. Should I prepare a copy of the real database (say another SQL Server database) for testing, or can I use SQLite in memory for better performance? If using SQLite, can I use th...
Monitoring a calculated data (A person's age) in SQL Server
I have a table, tblClient, which stored a client's date of birth in a field of type datetime, DOB. The goal here is that, when a client reaches 65 years old (need to be calculated thru DOB), I need to insert a new record into another table. But since the age of a client does not change due to a database transaction (INSERT,UPDATE,DELET...
scheduled SSIS package creating excel file with date as filename
Hi, I need some feedback on what is the best way to do what I want to do, allow me to situate. I have a huge database which receives new inputs constantly. Now I need to have an excel file which shows all the columns of inputs of the past day. So I thought the best way to do this is to use SSIS packages. I never really used SSIS that m...
SQL Query improvement - Select with max and groupby
Problem Given the following two tables, I'd like to select all Ids for Posts that have their most recent (i.e. last) comment made in the given time span (e.g. Feb 2010). The result of the query should only return Post ID 1, since the most recent comment for Post ID 2 is outside the range of the time span filter. Question I've c...
Enumerated types in SQL Server 2008?
Is there some kind of mechanism in SQL Server to allow Enumerated type like functionality? For example, if I have a column Called "UpdateStatus" it usually gets setup with single letter values like so: D X U I This could equate to a lot of things. That leads to confusion. The alternative is to have it be a string column like this:...
Is it possible to get sqlalchemy to create a composite primary key with an integer part without making it an IDENTITY type?
I'm using sqlalchemy 6.0. The SQL Server T-SQL dialect seems to want to make any integer that's part of my primary key into an identity. That may be ok if the integer field were the primary key, but mine is a composite and this isn't going to work for me. Is there a way to suppress this behavior? Here's a demonstration of the problem: ...
Retrieving the most recent revision information for specified dates (SQL)
In the above FldID 52 = Description and FldID 54 = HistoryDetail For any given date the output should be the last entry for that date. Also the columns need to be become rows. User will provide 2 dates. Say March 2, 2010 and March 3, 2010. So output in above case should be Since Rev 6 does not have an entry for FldID 52...
SQL Server "Dry Run" mode? Load data buffers, without holding locks or changing data
i'm going to running some queries against an SQL Server database, followed by a delete. Ideally all this happens inside a transaction (i.e. atomic). But practically, because the data has long since been purged from the buffers, SQL Server will have to perform a lot of physical IO in order to complete the transacted T-SQL. This can be a ...
Why is using OPENQUERY on a local server bad?
I'm writing a script that is supposed to run around a bunch of servers and select a bunch of data out of them, including the local server. The SQL needed to SELECT the data I need is pretty complicated, so I'm writing sort of an ad-hoc view, and using an OPENQUERY statement to get the data, so ultimately I end up looping over a statement...