Dynamic SQL sp_executesql problem

I have the following query: DECLARE @sync_table_name nvarchar(500) SET @sync_table_name = ‘ContactTypes’ DECLARE @sync_batch_size bigint SET @sync_batch_size = 2 DECLARE @sync_last_received_anchor timestamp SET @sync_last_received_anchor = 1 DECLARE @sync_max_received_anchor timestamp SET @sync_max_received_anchor = 18732866 DECLARE @sync_new_received_anchor timestamp DECLARE @sync_batch_count int DECLARE @sql NVARCHAR(500) SET @sql = ‘SELECT cast([version] as bigint) as [version], ROW_NUMBER() OVER(ORDER BY CAST([version] […]

Data in a XML node into a table

edited: <people> <person> <name>John Doe</name> <age>21</age> </person> <person> <name>Jane Smith</name> <age>24</age> </person> </people> actualy what i want to know is how to get both name and age at the same time using xpath. i can retrieve name by using string fileName = “testinfo.xml”; XPathDocument doc = new XPathDocument(fileName); XPathNavigator nav = doc.CreateNavigator(); XPathExpression expr; expr […]

How to revert update query in sql server?

I am using SQL Server for my database. now there is table which has following columns. PK_ID FK_ID Name Description 1 5 ABC ABCDE 2 7 EFG EFT 3 8 XUZ Xyz 4 11 TEF TEF Now by mistake i update some Fk_ID field with null value. Now I want to get back my all […]

Loop to insert multiple values into SQL Server

I am trying to insert a lot of values into SQL Server in one SQL insert statement. So for example.. Insert Into someDataBase Values(value1, value2, value3,…, value120, value121) All these values will be in the correct order on a sheet in Excel. I already created a loop to go through all the 121 values but […]

SQL Subquery to get actors in more than 30 films

I need to find all the names of actors that have participated in more than 30 films. This below isn’t quite doing it. What am I doing wrong here? SELECT first_name, last_name FROM actor A WHERE 30 > (SELECT COUNT(actor_id) FROM film_actor); Tables involved: actor: actor_id first_name last_name last_update film: film_id title description release_year language_id […]

Simple SELECT script giving error in Sql Server

Below are two scripts select * select count(*) 1st one gives error but 2nd one doesn’t give error. Below is the error given by sql server Msg 263, Level 16, State 1, Line 1 Must specify table to select from. Can you please tell the reason? Note: No table name specified. Thanks in advance.

Return AutoIncremeting ID upon insert

I have a stored procedure that inserts into a table that has two values, id and username. The id field is autoincremeting so my stored procedure looks like: CREATE PROCEDURE [dbo].[sp_test] @username varchar(50) AS BEGIN INSERT INTO dbo.testtable(username) SELECT @username FROM tbl.test2 WHERE username IS NOT NULL How can I return the id even when […]

How to substring a phrase in SQL?

I have a Phrase like this: DECLARE @helloworld NVARCHAR(100),@partHello NVARCHAR(10) SET @helloworld=’HelloWorld=10&HelloSQL=20′ I want to take the number between ‘=’ and ‘&’ , I used this statement but it didn’t work … it returns: 10& here is the code I used: DECLARE @helloworld NVARCHAR(100),@partHello NVARCHAR(10) SET @helloworld=’HelloWorld=10&HelloSQL=20′ SET @partHello = SUBSTRING(@helloworld,CHARINDEX(‘=’,@helloworld,0),CHARINDEX(‘&’,@helloworld,0)-CHARINDEX(‘=’,@helloworld,0)) PRINT @partHello I want […]

Converting string 'yyyy-mm-dd' to date

I want to select from table where date column is equal to specific date which I sending as a string in format ‘yyyy-mm-dd’. I need to convert that string and than to compare if I have that date in my table. For now I am doing this: select * FROM table where CONVERT(char(10), date_column,126) = […]

Is it possible to convert nvarchar to int?

I have tried something like this: select PREPRO = case when (isnumeric(PREPRO) = 1 and PREPRO in (‘0′,’1′,’-1′)) or convert(varchar, PREPRO) in (‘True’,’False’) then convert(bit, convert(integer, PREPRO)) else ‘No’ end from dbo.TI10SE The PREPRO contains all “False”. I get this error: Conversion failed when converting the nvarchar value ‘False’ to data type int. Does it […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.