How do I perform a case-sensitive search using LIKE?
Try using COLLATE Latin1_General_BIN rather than COLLATE Latin1_General_CS_AS
sql-server-2008
How can I get a SQL dump of a SQL Server 2008 database?
From SQL Server Management Studio you can use the Generate Scripts command in the Tasks item of the right-click menu on the target database. From here you generate scripts for the structure and data and SPs etc: make sure ‘Script Data’ is set to True in the scripting options to get the inserts.
Pasting from SQL Server Management Studio to Excel concatenates columns
This is an issue with Excel. After you paste the results into Excel, go to the “data” option and choose “Text to Columns”. Then click on “delimited” and be sure that “tab” is checked on the next window. This will convert the data into columns. And, Excel remembers the settings, so it will paste correctly … Read more
How to connect Sqlcmd to the server?
(This one is going to sounds silly) my problem was that I wasn’t using capital ‘S’ Eg: C:\> SQLCMD -S localhost\sqlexpress
How to select last one week data from today’s date
Yes, the syntax is accurate and it should be fine. Here is the SQL Fiddle Demo I created for your particular case create table sample2 ( id int primary key, created_date date, data varchar(10) ) insert into sample2 values (1,’2012-01-01′,’testing’); And here is how to select the data SELECT Created_Date FROM sample2 WHERE Created_Date >= … Read more
SQL error: ‘CREATE/ALTER PROCEDURE’ must be the first statement in a query batch
Run your statement in the following form: IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’myproc’) AND type in (N’P’, N’PC’)) DROP PROCEDURE [dbo].[myproc] GO create PROCEDURE [dbo].[myproc] AS BEGIN select * from mytable END GO Note the GO batch separator after DROP PROCEDURE
How to handle-escape both single and double quotes in an SQL-Update statement
You can escape the quotes with a backslash: “I asked my son’s teacher, \”How is my son doing now?\””
Using the IN operator to filter in reporting services
I had some troubles with this one as well. Microsofts own documentation states a simple comma delimited list of values in the Value box should work. This is confusing because IT DOESN’T. At least it didn’t for me and I am using SSRS 2012 with Visual Studio 2010. It turns out that when you are … Read more
Optional parameters in SQL Server stored procedure
You can declare it like this: CREATE PROCEDURE MyProcName @Parameter1 INT = 1, @Parameter2 VARCHAR (100) = ‘StringValue’, @Parameter3 VARCHAR (100) = NULL AS /* Check for the NULL / default value (indicating nothing was passed) */ if (@Parameter3 IS NULL) BEGIN /* Whatever code you desire for a missing parameter */ INSERT INTO …….. … Read more
How to Use Multiple Columns in Partition By And Ensure No Duplicate Row is Returned
Try this, It worked for me SELECT * FROM ( SELECT [Code], [Name], [CategoryCode], [CreatedDate], [ModifiedDate], [CreatedBy], [ModifiedBy], [IsActive], ROW_NUMBER() OVER(PARTITION BY [Code],[Name],[CategoryCode] ORDER BY ID DESC) rownumber FROM MasterTable ) a WHERE rownumber = 1