Using exec() function when inserting to a table

When you have 2 tables table like this CREATE TABLE #BranchType ( [External_BranchTypeID] [uniqueidentifier] DEFAULT newsequentialid() NOT NULL, [BranchTypeID] [smallint] identity(1,1) , [BranchTypeDescription] [nvarchar](20) NOT NULL, [DateCreated] [datetime] NOT NULL, [UserCreated] [nvarchar](20) NOT NULL, [DateModified] [datetime] NULL, [UserModified] [nvarchar](20) NULL, [IsDeleted] [bit] NOT NULL, ) CREATE TABLE BranchSubType ( [External_BranchSubTypeID] [uniqueidentifier] DEFAULT newsequentialid() NOT NULL, […]

Adding WHERE adds 20+ seconds to SQL Azure query

I’m looking for some advice on speeding up queries in SQL Azure. This is an example of the two queries we’re running, when we add a WHERE clause on there, the queries grind to a halt. Both columns, theTime and orderType are indexed. Can anyone suggest how to make these run faster, or things to […]

How to select multi record depending on some column's condition?

Say there is a SQL Server table which contain 2 columns: ID, Value The sample data looks like this: ID value —————— 1 30 1 30 2 50 2 50 3 50 When I run this query: select ID, NEWID(), value from table1 order by ID The result looks like this: 1 30 E152AD19-9920-4567-87FF-C4822FD9E485 1 […]

Sql Sum of Distinct

Is it possible to sum two value that comes from COUNT(DISTINCT [something]) ? I have a table that have near 400 million records in it. This table increases based on user visits and now I want to get COUNT(DISTINCT [SpecialUser]) bases on normal query it takes about 5-6 minutes to give the query answer. Now […]

Creating Autoincrement on AlphaNumeric in Stored Procedure

My Table: CREATE TABLE USER_DATA ([ID] NVARCHAR(10),[NAME] NVARCHAR(50)) My Stored Procedure: CREATE PROC SP_INSERT_USER @NAME VARCHAR(50) AS BEGIN DECLARE @ID NVARCHAR(50) DECLARE @IDTEMP INT SELECT @IDTEMP=MAX(CAST(RIGHT([ID],3) AS INT) ) FROM USER_DATA SELECT @IDTEMP=CASE WHEN @IDTEMP IS NULL THEN 1 ELSE @IDTEMP+1 END SELECT @ID=’RP’+RIGHT((‘000’+CAST(@IDTEMP AS VARCHAR(10))),3) INSERT INTO USER_DATA VALUES (@ID,@NAME) END Input: SP_INSERT_USER ‘Ajit’ […]

SQL Server : returning the related xml with value, nodes methods

I have a the below sample XML schema that I am querying from in SQL Server 2012. I have a table with a XML column. I want to include the snippet of xml when each row is returned. Below is the existing query and more detailed explanation. <dev:Doc xmlns:dev=”http://www.w3.org/2001/XMLSchema” Number=”0″ SchemaVersion=”0.1″ Settings=”Testing” Title=”Ordering”> <dev:Forms FormId=”A1″> […]

Need help to insert row into table by using fluent nhibernate

I am trying to create my first nhibernate application. when i run program command window opens and database tables are created. however, transaction to insert new row to Team table fails with error “Invalid Cast (check your mapping for property type mismatches);” Error occurs in program.cs file in line session.Save(Team);. Could anyone help to find […]

How to read Arabic characters from varchar datatype?

I have an old system that uses varchar datatype in its database to store Arabic names, now the names appear in the database like this: “ãíÓÇÁ ÇáãÈíÖíä” Now I am building a new system using VB.NET, how can I read these names to appear in Arabic characters? Also I need to point out here that […]

SQL Insert, Update Trigger – Can you update the inserted table?

I have an SQL Trigger FOR INSERT, UPDATE I created which basically does the following: Gets a LineID (PrimaryID for the table) and RegionID From the Inserted table and stores this in INT variables. It then does a check on joining tables to find what the RegionID should be and if the RegionID is not […]

Project relational data to XML column – is it possible?

I have 2 tables of the following structure: People Id | LastName | FirstName | Other columns… The second table has an XML column: Id | MyXmlCol | Other columns… MyXmlCol stores the following XML: <myData> <block> <person id=”1″ /> …other nodes </block> …other blocks </myData> The id attribute points actually to the Id column […]

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