I use sql server 2008 R2 as a data store. Until now on the test machine I had the english version of the software and used to make queries formatting the datetime field as fromDate.ToString("MM/dd/yyyy"); now I have deployed the database on another server which is in the italian language. I shall change the format in my code to fro...
Hi I am using this script in the process of weekly maintenance, suggest best approach/scripts to do shrinklog. Currently am getting an error with the below script declare @s nvarchar(4000) set @s= ' if ''?'' not in (''tempdb'',''master'',''model'',''msdb'') begin use [?] Alter database [?] SET...
Hi, I have the following query: create proc [dbo].GetCustById as DECLARE @sql nvarchar(500) DECLARE @Param nvarchar(200) SET @sql = 'select @columnName from customer where custId = @custId' SET @Param = N'@columnName varchar(10), @custId int' EXEC sp_executesql @sql, @Param , @columnName = 'Address1', @custId = '42' But it always r...
I have a variable @csv which hold a comma separated value such as: -a -a,b -a,b,c I need to pass it in a query in my OLE DB source in a data flow to create a query such as: SELECT COUNT(1) FROM table WHERE col1 IN @csv So if @csv="a,b" then internally it should resolve into SELECT COUNT(1) FROM table WHERE col1 IN 'a','b' How can t...
Just curious as to how Skip & Take are supposed to work. I'm getting the results I want to see on the client side, but when I hook up the AnjLab SQL Profiler and look at the SQL that is being executed it looks as though it is querying for and returning the entire set of rows to the client. Is it really returning all the rows then sortin...
hi Is there any script (any batch file) to Start/Stop the SQL Server 2008 service? for example in Oracle i use this for open: net start OracleServiceORCL net start OracleOraDb10g_home1TNSListener net start OracleOraDb10g_home1iSQL*Plus net start OracleDBConsoleORCL how to do it in sql-server 2008 ? thank's in advance ...
Hello. Has anyone ever been able to get the Task List feature in SQL Server 2008 to work. Apparently you are meant to be able to use a comment tag and then TODO, HACK etc as a pointer to that bit of code from the task list. i.e. -- TODO: testing task list /* TODO: testing task list */ If anyone can tell me what I'm missing in ...
How do you look up a default global table locking hint? -- Questions Are there any DMV/DMF (Dynamic Management View/Function) that return such information? And also, is there a way to change the default lock hint? Currently I am adding nolock hint almost everywhere to prevent locks. I'd like to avoid doing so by changing the d...
I have a table called "changes" which contains a list of record numbers the user has modified on many other tables. I'd like to remove the record referenced by a row in changes (and the changes row itself) in one SQL query. WITH name AS ( SELECT tableName FROM [changes] WHERE id = 80 ), number AS ( SELECT changeRecnum FROM [changes]...
I'm trying to create a custom delete rule on a relationship between a parent and a child table. What I'd like to do is to apply cascade delete on the child if the child's column 'IsActive' is set to false and to apply 'NoAction' rule if this column is set to true. How can I do this? ...
I am using SQL Server 2008. I have tables on which there are duplicate indexes (basically indexes with same definition). I wanted to know if its possible to find out which queries are using these indexes? I don't know why the duplicate indexes were created in the first place. So before removing them, I want to identify any queries which ...
I have a view defined in SQL server 2008 that joins 4 tables together. Executing this view in SQL Server Management Studio takes roughly 3 seconds to run and returns about 45,000 records. My application is written in Java using hibernate to simply do a "from MyViewObject" query in HQL. When this is run, the execution time is consisten...
For a long time, we've wanted to create a case management system where no history is ever lost. When a change is made, we want to record that change, but have the ability to go back to any point in time and see what the record looked like. I wanted to pose this question to the Stack Overflow community to see what are some ways of doing t...
I inherited a fairly large C# codebase which is littered hundreds of times with the following way of doing DB operations: using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand command = new SqlCommand(queryString, connection); command.Connection.Open(); command.ExecuteNonQuery(); } However, due ...
HI I have made a maintenance package in that have used shrink database task for specific database, it ran successfully, found slight increase in previous db size. Initial size(129 gb) after running the package(130gb). I am expecting after shrinkning it should shrink? what might be happen? am sure package scheduled to run and check the...
Subquestioning [2] While copying Resource.mdf [1], I noticed that: 1) It is possible to copy Resource.mdf without stopping SQL Server instance (I attached one having copied from running instance and it works after attaching) . 1a) Should I understand that it as general possibility for all read-only databases or is it only in some v...
CREATE TABLE [dbo].[MembershipModule]( [Id] [uniqueidentifier] ROWGUIDCOL NOT NULL, [ParentId] [uniqueidentifier] NULL, [TargetId] [int] NULL, [WebContentId] [uniqueidentifier] NULL, [Name] [varchar](35) NOT NULL, [NameUpper] AS (isnull(upper([Name]),'')) PERSISTED NOT NULL, [UriPrefix] [varchar](max) NULL, [UriText] [varchar](...
Possible Duplicate: What's the difference between a temp table and table variable in SQL Server? What is the difference between table variable and temp table, actually I have two question on it. how to decide when to use what? which one is better in performance and WHY ? ...
Hi Guys, If i have the following entity: public class PocoWithDates { public string PocoName { get; set; } public DateTime CreatedOn { get; set; } public DateTime LastModified { get; set; } } Which corresponds to a SQL Server 2008 table with the same name/attributes... How can i automatically: Set the CreatedOn/LastModifi...
Yesterday I added some indexes on a view in my MSSQL 2008 db. After that it seems like all the store procedures need to run with QUOTED_IDENTIFIER set to ON, even those that don't use the view in question. Why is it so? Is this something I can configure on the db or do I have to update all my stored procedures to set the QUOTED_IDENTIFI...