sql-server

How do I convert eg '22/03/2005' to a datetime in SQL Server

Its SQL Server 2000. I am starting with a character string in the format DD/MM/YYYY ...

Why insert TSQL statement block when transaction isolation level for another transaction is serializable with non-conflicting filter?

Serializable transaction isolation levels avoids the problem of phantom reads by blocking any inserts to a table in a transaction which are conflicting with any select statements in other transactions. I am trying to understand it with an example, but it blocks insert even if when the filter in the select statement is not conflicting. I ...

General network error after a night of inactivity

For some time now our flagship application has been having mysterious errors. The error message is the generic [DBNETLIB][ConnectionWrite (send()).]General network error. Check your network documentation. This is reliably reproduced by leaving the app open for the night and resuming work in the morning. Since it's a backend server...

In SQL Server, how can I compare rows using the column names from another table

I have two tables A and B, with dynamic columns where I have no idea what columns are key inside them, except from another table called C. The C table specifies which column/s is a key column in tables A and B. There can be 1 or more key columns. My question is, how would I generate such a query where I select all rows from A where the...

Which reporting tool is for me

Hey peeps I have a system implemented by numerous clients, system is built in asp.net, C# and uses MSSQL2008 as a database. New requirements are reporting functionality, this has to be presented in the browser. I know of two solutions: Crystal Reporting Services However, somehow i think their are more option out there. I am look...

wcf serialize linq results causes massive sql server load

Hello, I am trying to get serialized results from a WCF service from database using linq. The tables in the db are normalized to some extent and I'm returning some other data along with the on that I initially retrieve with a linq query using Data.Linq.DataLoadOptions, just like in Scott Landford's Blog: http://codeexperiment.com/post...

SQL Server: protect database from being changed directly

Our program ships with an SQL Server 2005 database and SQL Server 2005 Express. Usually it installs its own instance of SQL Server 2005 in the client's computer. Now I have to add some tables whose content should only be updated from within the program. I need to prevent these tables from being changed directly, by using Management Stud...

With DataSet create SQL Server dynamic tables with populating data

I have a DataSet with multiple tables (distinct schema). I want to generate table schema on SQL Server 2005 and insert data into the dynamically created table. Table name should be the name of DataTable (which is unique i.e. data0, data1, ...) Is there a way to insert it in to the SQL Server database without going through DataTable ro...

Disable PRINT in SQL Server

Hi, I have a script with many debug messages, which are printed by PRINT function. Is there any way to disable that messages? I have in mind something like SET NOCOUNT ON, but for user messages. ...

SQL performance: WHERE vs WHERE(ROW_NUMBER)

I want get n-th to m-th records in a table, what's best choice in 2 below solutions: Solution 1: SELECT * FROM Table WHERE ID >= n AND ID <= n Solution 2: SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS row FROM Table )a WHERE row >...

Question about SQL UPDATE with CASE statement

So I have this SQL table that I need to update. Now we use a tool to do the process so if we just renamed the column then it wouldn't work because our tool would just drop the old column and add a new column with the new name, so we have to do it manually and enter this code into the build and exclude the table from our build. The table...

How to store a tree in SQL database

Hi, I have to store a tree in a database, so what is the best way to do this? Show the method you use and name its pros and cons. (I am using SQL Server 2005) ...

Tweak SQL query help

I have a big query as following, someone please tweak this query? i need to avoid this big group by ... :-) SELECT COALESCE( SUM(total_retail), 0 ) total_retail, COALESCE( SUM(meterial_sub_total), 0 ) meterial_sub_total, COALESCE( MIN(po_template_group_by_code), 0 ) po_template_group_by_code, COALESCE( MIN(po_te...

Convert a few columns in the table to a resultset of name, value pair rows in SQL Server

I have a table like this Table1 Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8 1 2 3 4 5 6 7 8 2 2 3 4 5 16 17 18 3 2 3 4 5 26 27 28 4 2 3 4 5 36 37 38 I want the data to be converted in this format Col1 DerivedCol1 DerivedCol2 1 Col6 6 1 C...

How do I create/add a sql server compact edition db in vs2010?

I have vs2010 ultimate installed and sql server 2008 developer. I created a package using the openAuth Extension from the online gallery and can not find an option to add a sql server CE db to my project. I tried changing the target framework to 4.0 and still nothing. I see a type that says SQL Server Database, but I believed that was f...

T-Sql Select * Between 30% and 40%

Question How do I write a T-SQL Stored Procedure that lets me select percentages of rows between X% and Y%? So basically I would want to select the rows between 30 PERCENT and 40 PERCENT..... I know that you can do the following, but obviously that doesn't let met specify a set of rows between 2 percentages. SELECT TOP 50 PERCENT * ...

Administrate a SQL Server 2000 database from Windows and Change a hole column

Hello guys, i have some experience with MySQL but none with SQL Server, i have a computer who host a database (SQL Server 2000) and i have local access to it. The think is that one column of the table NomAntiguedad need to be change from a given date period; I need to multiply by 0.01 all the values of the column between the values of me...

Entity Framework newbie question. I select a table from the wizard, but nothing shows up.

Hi All, I create a new .edmx and the wizard allows me to choose the tables I want to expose after I pick my connection. I pick one table, and nothing shows on the design surface. The model explorer opens but nothing seems to have happened. Any ideas what's going here? There is a message on the design surface that says use the toolbox. I ...

How to increment in a select query

I've got a query I'm working on and I want to increment one of the fields and restart the counter when a key value is different. I know this code doesn't work. Programmatically this is what I want... declare @counter int, @id set @counter = 0 set @id = 0 select distinct id, counter = when id = @id then @counter +...

Querying SQL Server Database Results in "Invalid Object name" error, when it is definitely valid

I have a C# app that hits a couple of different databases to do some analysis. However, for some strange reason, I keep getting an "Invalid object name" error with one of my queries, when I know for a fact that the object exists and is being called properly. This is the query in question (Excuse the one line... it's generated dynamically...