SQL Server sp_ExecuteSQL and Execution Plans
I have a query which is superfast in SQL Server Management STudio and super slow when run under sp_ExecuteSQL. Is this to do with caching of execution plans not happening when run under spExecuteSQL? ...
sql-server-2005
SQL show records that don't exist in my table variable
I have a table variable that holds orderID, UnitID and OrderServiceId (it is already populated via a query with insert statement). I then have a query under this that returns 15 columns which also include the OrderId, UnitId, OrderServiceId I need to only return the rows from this query where the same combination of OrderId, UnitId, an...
Fastest way to update 120 Million records
I need to initialize a new field with the value -1 in a 120 Million record table. Update table set int_field = -1; I let it run for 5 hours before canceling it. I tried running it with transaction level set to read uncommitted with the same results. Recovery Model = Simple. MS SQL Server 2005 Any advice on getting this done faster?...
Access function return value in joining table column
The partial select statement is: SELECT t1.Column1, t1.Column2, **"Column3"** = dbo.FunctionName(Column1, Column2) FROM Table1 t1 Now I wanted to left join Table2 in above query but join needs to be "Column3" = Table2.Column3; something like as below query which does not work and how to achieve that. SELECT t1.Column1, t1.Column2, ...
Recursively grab all data based on a parent id
We have a table where rows recursively link to another row. I want to pull data associated with a given parentId and all it's children. Where parentId is one from the root row. I thought I have seen or done something like that before, but I am unable to find it now. Can this be done in SQL or is it better to do this in code? I want the...
<column> IS NULL vs <column> = NULL in LINQ to SQL generated SQL
in ASP.NET MVC 2.0 I'm doing something like the below (specifically, matching a record based on some values as well as some NULL values). var result = dataContext.Inventory .SingleOrDefault(x => (x.part_id == model.Part.id && x.location_id == transaction.to_location_id && x.bin_id == tran...
SQL one-to-many relationship, how to select parent containing unique set of children
I have a routine one-to-many schema, similar to this simple example: PERSON person_id (PK) PERSON_TRAIT person_id (FK) trait_id (FK) quantity TRAIT trait_id (PK) name //other attributes Given a set of traits ("friendly, funny"), how would I return the associated person_id and quantity recordset. At first glance I was tempted to use ...
Connecting to SQL server via client through another machine
I am trying to connect to my SQL server from a client and both have the different gateways, when i try to connect with the server in the same subnet, it connects, but as said above, when i try to connect it through a client machine available in other subnet, it gives an error.... Connection failed: SQL State: '01000' SQL Server Error...
Strange Problem With Left Join vs Subquery in Select
I have a T-SQL query that is causing performance issues. Its a chunky one but the part that seems to be causing an issue is a simple LEFT JOIN. This can be resolved by removing the left join and using a subquery in the select, however this seems unsatisfactory to me as I can't see why one works quickly and not the other. Theres not a ...
What are the consequences of the sys.sql_dependencies Catalog View Not Being Updated
If I have two trivial stored procedures, created like so: create procedure root as select 1 go create procedure dependant as exec root go (Where dependant depends on root). When I check the sys.sql_dependencies table for the second procedure, I see an entry (as i would expect). If, however, I add the dependant procedure first, I ...
Multi-part Identifier could not be bound - Update Query SQL Server 2005
Hi, SQL Server novice here. UPDATE dbo.ObjectivesApproved SET dbo.ObjectivesApproved.VAP = 'Y' WHERE ((dbo.Approved.Cri_Group In ('X01' ,'X02' ,'X03' ,'X04' ,'X05' ,'X07' ,'X08' ,'X09' ,'X10' ,'X11' ,'X12' ,'X13' ,'X14'))) gives the following error The multi-part identifier "dbo.Approved.Cri_Group" could not be bound. What's caus...
Server hardware requirements for SSRS
As the title says, i'm trying to figure out how much RAM is needed to generate and export to excel a large report using SQL Server Reporting Services on Windows Server 2003. It is not an option to upgrade it to SS2008 and also not an option to export to CSV. Strictly from a hardware point of view what is a good configuration for a high...
ADO.Net entity framework SQL loginPermissions
I am fairly new to entity framework, and i am planning to use it in my next project. what permissions should web give to the sql server user login which entity framework uses? since i read entity framework does DML operations do i need to provide datawriter role to login? ...
merging rows from sql query result
What would it be the simplest way to merge a result like this from a sql query to be displayed in an asp.net gridview? NULL Tarde Fer W. Lunes Mañana NULL Fer W. Lunes I need the result to look like this Mañana Tarde Fer W. Lunes ...
Percentage row makes up of total where each row determine by group by clause
I'm sure this is a stupid n00b question, but I have the following results table (which is created through a long-winded query I only half understand) and what I'd like is to be able to add in two columns that work out the row percentage relative to the total: Sample results table: CREATE TABLE #temp (category varchar(30) ,count_people ...
SQL Server & ASP .NET encoding issue
Hi, my page has utf-8 meta element added + sql server encoding is also utf. However when I create record and try to issue SELECT statement with condition that contains POLISH characters like 'ń' , I see no results. Any ideas what am I missing? ALSO Sql management studio shows result with POLISH characters , but I don't trust it.... I ...
TSQL Set theory
Here is the question You are to label the dice sides with numbers. Each dice has 6 sides. You have two dice. You must label so that you can display (not sum or product) the numbers 0 to 31. Complete output: 00 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 I tried (select 0 as ...
c# get current connection in TransactionScope transaction
Hi, I want to make transactional a series of sp call (sql server 2005) in a .net 2.0 project surrounding some piece of business logic with a using(TransactionScope...) Unluckily, I inherited the DAL from another project and I don't want to make many changes there..the problem is that every method that calls a stored procedure opens...
How to use secure encryption of an existing column in SQL Server 2005
I want to encrypt an existing column in SQL Server 2005, using an UPDATE statement, moving the old content into the new encrypted column. So I have 2 choices: Symmetric and Asymmetric. The issue I have is that with a symmetric key, I have to embed the password into an SP in order to read the column like: -- Create key (at some earlier...
Do you send automatic weekly reports about status of your server?
I am working on redoing a weekly report, I send, to anyone on our development team. The previous version just sent sql server based scheduled jobs status, backup status, and database size. But for this new version, will have more information from every part of our server. Coldfusion: Verify Main Datasource List Scheduled Tasks Serve...