Tag: sql-server-2012

Conversion fails when converting date average to date in sql

I have calculated a date average from several dates and I’m trying to get the result of the average date, but I’m getting the following error: Conversion failed when converting date and/or time from character string. Here’s the SQL query I tried: select CONVERT(DATE, LEFT(convert(varchar(10), AVG(convert(int, convert(varchar(10), OnMarkDate, 112)))), 8)) from @tbl_XMLResult Here are the […]

How do I grant sql permission to an object in a different schema as dbo?

This GRANT EXECUTE ON OBJECT::[test].[someTable] TO [MyUser] AS [dbo]; fails with error Msg 15151, Level 16, State 1, Line 1 Cannot find the object ‘someTable’, because it does not exist or you do not have permission. If I remove the AS [dbo] part it works fine. Can someone explain why this is and the implication […]

Create a group where either value from two columns for a row matches either value from two columns for a different row

I am trying to create a Group ID where either value from two columns in a table matches the either value from the two columns for a different row. For example: Table: +———+——————-+ | ogr_fid | comapared_ogr_fid | +———+——————-+ | 8 | 10 | | 6 | 8 | | 10 | 4 | | […]

System.Data.SqlClient.SqlException (0x80131904): Login failed for user

This question may has been already put, but I do this for the first time and I don’t know where I’m wrong. I want to connect my asp.net web page to SQL Server 2012. My connection string is: static private SqlConnection connection = new SqlConnection(@”Data Source=DESKTOP-U8B6J4S\SQLEXPRESS; Initial Catalog=DBSera; User ID=DESKTOP-U8B6J4S\PROGRAMARE; Password=”); When I connect SQL: […]

How to add XML text node to an XML fragment queried by attribute value

I’m working on a system that maps data between a new schema and a legacy schema. In certain circumstances I want to update an xml field by targeting an element node based on an attribute value and performing an insert or update on the text node. I’ve worked out how to handle update cases but […]

Using RETURN in dynamic SQL to exit stored procedure

I am trying to use something like below in my stored procedure to exit when a certain condition is met, but the code after the return gets executed. Is there something wrong my script? declare @DBName sysname set @DBName =’Test_DB’ SET @sql=’IF OBJECT_ID(”’+@DBName +”’,”U”) is null’ + CHAR(10)+ ‘RETURN;’ EXEC (@sql)

How does the SQL Server query optimizer choose non clustered indexes from available indexes for particular table?

I have following non clustered indexes for serviceregistration table. Index Name —————+ Column Name —————– + Index Type IX_SERVICEREGISTRATION_I | SERREGNO | NONCLUSTERED IX_SERVICEREGISTRATION_II | SERREGNO, SERCUSTID | NONCLUSTERED IX_SERVICEREGISTRATION_III| PLACEID, JOBID, SERREGNO | NONCLUSTERED Query 1: Select CustomerName, Place From dbo.ServiceRegistration Where SERREGNO = ‘JAN1234’ And PLACEID = 1 Query 2: Select CustomerName, Place […]

continue with next iteration if foreach loop takes too long

I have a for each loop that pulls queries out of a table with queries. The loop then executes each query and writes the results to a table. This works like a charm. I even implemented error-handling for when a query could not be executed (if it for example does have syntax-errors). But sometimes executing […]

why sql server increment the Identity specification?

I am using sql server 2012, in my database I have set primaykey on userid also I have set the Identity specification Yes,Is Identity Yes,Identity Increment 1 and Identity Seed 1. I just insert 5 users and userid is 1,2,3,4,5. I am sure after that I haven’t did any insert and no other sp or […]

SSIS Custom Data Flow Component – Redirect error rows

I have created a SSIS custom data flow component that performs the simple task of converting dates from a COBOL mainframe date types, that are in the format of CYYMMDD, to a SQL Server supported format of YYYYMMDD. There are error rows because the incoming COBOL formatted dates can be invalid dates (i.e., 2-29-2015, 4-31-2016, […]

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