How do I set a SQL server connection to readonly? I tried Googling and all I found was File Mode=Read Only, but it didn’t work (File Mode keyword not supported). The reference looked SQL CE specific. No luck with SQLite Read Only=True either. -edit- My connection string is below. I have no clue when it …
Tag: sql-server
Avoiding Nested Queries
How Important is it to avoid nested queries. I have always learnt to avoid them like a plague. But they are the most natural thing to me. When I am designing a query, the first thing I write is a nested query. Then I convert it to joins, which sometimes takes a lot of time to get right. And rarely
is it possible to select EXISTS directly as a bit?
I was wondering if it’s possible to do something like this (which doesn’t work): select cast( (exists(select * from theTable where theColumn like ‘theValue%’) as bit) Seems like it should be doable, but lots of things that should work in SQL don’t 😉 I’ve seen workarounds fo…
Swap values for two rows in the same table in SQL Server
I want to swap the values from two rows in a table. I have the rows IDs of the two rows. Is there any query to do that? Here is an example. Before the query I have this: row1 : 1,2,3 row2 : 5,6,7 After the swap I want this: row1 : 5,6,7 row2 : 1,2,3 Answer If you want
Why do I get “Procedure expects parameter ‘@statement’ of type ‘ntext/nchar/nvarchar’.” when I try to use sp_executesql?
Why do I get this error Procedure expects parameter ‘@statement’ of type ‘ntext/nchar/nvarchar’. when I try to use sp_executesql? Answer Sounds like you’re calling sp_executesql with a VARCHAR statement, when it needs to be NVARCHAR. e.g. This will give the error because @SQL nee…
SQL How to correctly set a date variable value and use it?
I have the following query which uses a date variable, which is generated inside the stored procedure: The problem is that the @sp_Date value appears to be being ignored and I am wondering why? Have I defined or used it incorrectly? Answer Your syntax is fine, it will return rows where LastAdDate lies within …
How to find Expr#### in Execution Plan
When looking at the actual execution plan for a query in SQL Server Management Studio (SSMS), how do I determine what an expression such as Expr1052 represents? When I identify the costly parts of the query and look at the properties of that operation, there are often references only to these Expressions, or …
Unable to drop constraint in SQL server 2005, “Could not drop constraint. See previous errors”
I’m trying to drop a constraint on a DB table, something like: But the execution just runs and runs. If I stop it I see: Web search throws up various pages but note that the constraint is properly named and I am trying to remove it using the correct name Answer Found a way to sort this, although I don&#…
Run all SQL files in a directory
I have a number of .sql files which I have to run in order to apply changes made by other developers on an SQL Server 2005 database. The files are named according to the following pattern: Is there a way to run all of them in one go? Answer Create a .BAT file with the following command: If you need
SQL server query to get the list of columns in a table along with Data types, NOT NULL, and PRIMARY KEY constraints
I need to write a query on SQL server to get the list of columns in a particular table, its associated data types (with length) and if they are not null. And I have managed to do this much. But now i also need to get, in the same table, against a column – TRUE if that column is a