You are correct, it is a bug in SSMS. I’m using SQL Server Management Studio 2008 R2 and when I try to create a View using the built-in designer, I get the same error message as you: SQL Execution Error Error Source: .Net SqlClient Data Provider Error Message: Cannot call methods on date. As @Aaron … Read more
You can’t use variable names to bind columns or other system objects, you need dynamic sql DECLARE @value varchar(10) SET @value=”intStep” DECLARE @sqlText nvarchar(1000); SET @sqlText = N’SELECT ‘ + @value + ‘ FROM dbo.tblBatchDetail’ Exec (@sqlText)
You can use a ranking function and a common table expression. WITH e AS ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY ApplicationId ORDER BY CONVERT(datetime, [Date], 101) DESC, [Time] DESC ) AS Recency FROM [Event] ) SELECT * FROM e WHERE Recency = 1
I think you can hit CTRL + C and CTRL + V without any text selected and you can duplicate the line below it. Edit: works in SSMS18
A quick-and-dirty way of fixing these is to select the rows into a new physical table like so: SELECT * INTO dbo.MyNewTable FROM <the rest of the offending query goes here> …and then compare the schema of this table to the schema of the table into which the INSERT was previously going – and look … Read more
@tableName Table variables are alive for duration of the script running only i.e. they are only session level objects. To test this, open two query editor windows under sql server management studio, and create table variables with same name but different structures. You will get an idea. The @tableName object is thus temporary and used … Read more
I have managed to resolve the issue, I solved it by following this link’s suggestion: http://www.mattbutton.com/2011/06/01/sql-profiler-templates-missing/ The following comes from the link: If you are connecting to a SQL server with the SQL profiler and none of your templates are showing up, compare the versions of the SQL profiler you are running and the version … Read more
You can use the output clause to capture the modified/inserted rows to a table variable and use that with a delete statement after the merge. DECLARE @T TABLE(EmployeeID INT); MERGE Target1 AS T USING Source1 AS S ON (T.EmployeeID = S.EmployeeID) WHEN NOT MATCHED BY TARGET AND S.EmployeeName LIKE ‘S%’ THEN INSERT(EmployeeID, EmployeeName) VALUES(S.EmployeeID, S.EmployeeName) … Read more
Enclose the keywords in double quotes if you want to search exactly for control of SET @Keywords=””control of”” If you want to search for control and of, use the following: SET @Keywords=”control AND of” But server may consider of as a garbage or stop word, so in this case use the following: SET @Keywords=”control AND … Read more
When I had this problem, my database didn’t have an owner set properly. I had restored a database from another windows domain, right clicked the database -> properties and verified in the “general” tab that the owner was set correctly. However, in the “files” tab, owner was not set at all. As soon as I … Read more