spot7.org logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML Categories
  Home » SQL SERVER » Page 1
Dynamic SQL declaration executed by batch file
I don't think you need a dynamic SQL or any variables here. Just an Insert from select should work INSERT INTO PriceData SELECT TOP 1 P.ID, Cast(Getdate() AS DATE), Value, Getdate() FROM PriceData PD JOIN Price P ON P.ID = PD.PriceID WHERE ID = '123456' ORDER BY PD.UpdatedOn DESC

Categories : Sql Server

Dividing by number of rows as matched by ID
Try this. Use Aggregate count with Window function to get count of sum1 in each ID and use it to divide the sum1 CREATE TABLE #sum ( ID INT, Sum1 NUMERIC(4, 2) ) INSERT #sum VALUES (1,10.00 ), (2,15.00 ), (3,20.00 ), (3,20.00 ), (4,30.00 ), (4,30.00 ), (4,30.00 ) SELECT *, Sum1 / Count(id) OVER (

Categories : Sql Server

How to convert VS2010 SQL Server Unit test project to VS 2012?
I work on the product team, thanks for raising this. After a quick investigation, this looks like it is a regression introduced in the July release of SQL Server database tooling in Visual Studio. Could you please file a connect bug for this issue at https://connect.microsoft.com/SQLServer/feedback/CreateFeedback.aspx and use the category "Developer Tools(SSDT, BIDS, etc.)". We will treat this

Categories : Sql Server

SQL Server Namespace
Well, if you use WITH XMLNAMESPACES, then you either define a default XML namespace that gets applied without using an explicit prefix, or you define a XML namespace with a prefix that you need to use in your XPath expressions. To use the default XML namespace, use this statement: ;WITH XMLNAMESPACES (DEFAULT 'http://schemas.datacontract.org/2004/07/LiveUpdateWS') and then your existing XPath

Categories : Sql Server

Conversion Failed when converting the varchar value 'Wired' to data type int
Problem is in the case statement of both the queries In T1 there is case statement which is CASE RO.Inventory_Type WHEN 'WIRELESS' THEN 1 ELSE 0 END AS InventoryType here InventoryType will be either 0or 1 based on RO.Inventory_Type. So here the InventoryType column datatype will be INT In T2 there is case statement CASE WHEN InventoryType = 1 THEN 'WIRELESS' ELSE 'WIRED' END AS Inven

Categories : Sql Server

Multiple Session State databases on Database Instance
I've seen a site or two that recommend having different ASPState databases for different applications and have not yet found any that say this is a bad practice, so I am going ahead with this plan. Created the new ASPState with this command Aspnet_regsql.exe -S -E -ssadd -sstype c -d ASPState_ (Aspnet_regsql.exe is in the C:WindowsMicrosoft.NETFramework64v4.0.30319 directory for me) All looks

Categories : Sql Server

Increasing size of SQL Server MDF file
The recommended approach is to NEVER grow it outside maintenance periods. As any sensibly large and busy sql database lives on dedicated storage ANYWAY - you can pre-allocate all the available space during a maintenance window.

Categories : Sql Server

Connecting Laravel 4 (on OSX) to MSSQL
I have had issues connecting also. Here is what I did. I'm assuming that freeTDS is installed properly. In database.php, when using a local installation make sure to use a comma instead of a colon between ip_number and port_number. If server installation use colon. 'mssql' => array( 'driver' => 'sqlsrv', 'host' => 'ip_number,port_number', // Requires comma instead of

Categories : Sql Server

TSQL Query Inner Selects from Outer Where Clause
How about we also join to those two subordinate tables so we can narrow the result set based on your c.localeID: SELECT a.[trainingEventID], a.[teTitle] , a.[teSource] , a.[teType] , a.[teMedium] , a.[teFlag] , a.[teCreator] , a.[teCreated] , a.[tePOC] , a.[teDirector] , a.[teTeammateImpact] , a.[teCustomerIm

Categories : Sql Server

SQL IDENTITY autoincrement when 0 but still allow explicit inserts
If you tell EF the primary key is database generated then it will not pass the id to the insert sql. You need to pass the ID so go with DatabaseGenerated.None. But you want it to be an IDENTITY, so make it one in a migration script. You could change the CREATETABLE statement, adding identity: true to the column specification, or you can modify the table by running sql using the Sql() method Now

Categories : Sql Server

Checking update in database with ESQL without loop
If you don't want to use the loop then enter the query something like SELECT id FROM TABLE_NAME WHERE First_Name='John' if the record returns any data then stop the message there. Otherwise continue.

Categories : Sql Server

Testing sp_send_dbmail
If you're just looking to verify what will be sent, you can query the msdb.dbo.sysmail_mailitems table: SELECT * FROM msdb.dbo.sysmail_mailitems This table holds the recipients, subject, body, format, query, sent_date, from_address, reply_to, and some other fields. I also suspect, though I haven't test, that if you updated that table to a new recipient email address that you could send the

Categories : Sql Server

Transpose Rows to Columns for the same ID
You can try this select Unit_ID, max(case when seqnum = 1 then SensorSerial end) as Sensor1, max(case when seqnum = 2 then SensorSerial end) as Sensor2, max(case when seqnum = 3 then SensorSerial end) as Sensor3 from (select equipment.*, row_number() over (partition by Unit_ID order by SensorSerial) as seqnum from equipment ) equipment group by

Categories : Sql Server

SQL Server 2008 R2: Dynamic Pivot table query performance
Here's an alternate way, it avoids the DISTINCTs and uses a sum instead of the pivot function: SELECT orig.cola , colas.counter , SUM(CASE orig.colc WHEN 'A1' THEN 1 ELSE 0 END) AS A1 , SUM(CASE orig.colc WHEN 'A2' THEN 1 ELSE 0 END) AS A2 , SUM(CASE orig.colc WHEN 'A3' THEN 1 ELSE 0 END) AS A3 FROM tft orig INNER JOIN ( SELECT cola ,

Categories : Sql Server

Want cumulative sum by raw & column in SQL Server
Try the updated query (last query). The output logic in my understanding is: Year | C1: Fall + previous_year_total | C2: Fall + summer + previous count | C3: TOTAL create table #test(Year int, fall_unit int, summer_unit int, spring_unit int) insert #test (Year, fall_unit, summer_unit, spring_unit) select 2010 as Year,4 fall_unit,5 summer_unit,6 spring_unit union select 2011 as Year,7 fall_unit,

Categories : Sql Server

Add a trigger on a column when I update its value very quickly
Below is a trigger example that joins to the inserted table to identify the updated rows and further qualifies the rows to be updated. I wouldn't expect performance to be a concern due to joining on the primary key, which provides a unique index. CREATE TRIGGER TR_CODE_UPDATE ON dbo.CODE FOR UPDATE AS UPDATE c SET FLAG = 1 FROM dbo.CODE AS c JOIN inserted AS i ON i.CODE = c.CODE AND i.L

Categories : Sql Server

Xamarin and SQL Server
The best approach is to wrap your SQL calls in a web service then you can invoke them in your Xamarin application. Here is a guide http://developer.xamarin.com/guides/cross-platform/Application_Fundamentals/Web_Services/

Categories : Sql Server

CRM 2011 Email in Workflow
I have seen the exact same issue working with CRM 2011 on IE 11 - compatibility mode didn't make any difference. Try using an older version of IE (I used IE 8 which was installed on the server). Once you make the edits, the WF will work even though it doesn't show on the IE 11 machines.

Categories : Sql Server

LIKE and Equals not working as expected
Citing the documentation forLIKE: Using Wildcard Characters As Literals You can use the wildcard pattern matching characters as literal characters. To use a wildcard character as a literal character, enclose the wildcard character in brackets. So do this: SELECT * FROM @Temp WHERE UserID LIKE '%[[]%' if you want to match all rows with a left bracket somewhere in the UserID.

Categories : Sql Server

Conflict between sql server 2008 r2 and 2012
Open your SQL Server Configuration Manager, Under SQL Server Network Configuration you will find Protocols for and all the instances that is installed will be there. MSSQLSERVER is the default instance. Find your instance name there. There other option is to run regedit Go to HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL Server You will find keys like. MSSQL10_50.MSSQLSERVER and MSSQL11.SQLEX

Categories : Sql Server

create sql view from a sql function
Try to use dynamic sql .I didnt check it but maybe this is the way to do it... RETURNS @retContactInformation varchar(max) -- not table . . DECLARE @sql nvarchar(max) DECLARE @sqlx nvarchar(max)=dbo.ufnGetContactInformation() set @sql='CREATE VIEW [aaa] AS '+ @sqlx+'' EXECUTE sp_executesql @SQL instead of cursor try while loop CREATE TABLE #tempo (id INT IDENTITY (1,1),c1nr varchar(

Categories : Sql Server

Create one trigger for multiple operations
Nevermind, I got it : Create trigger [dbo].[TR_AUDIT_TESTAUDIT] ON [dbo].[testaudit] AFTER INSERT, UPDATE, DELETE AS BEGIN SET NOCOUNT ON; declare @action nvarchar(1) set @action = 'I' -- always I if exists(select top 1 1 from deleted) and not exists(select top 1 1 from inserted) set @action = 'D' if exists(select top 1 1 from deleted) and exi

Categories : Sql Server

Unpivot Period Table
This may help you: This assumes that LC and UN are fixed SAMPLE DATA create table temp( ID int, Name varchar(10), MTH201401UN int, MTH201401LC int, MTH201312UN int, MTH201312LC int, MTH201311UN int, MTH201311LC int ) insert into temp select 1, 'A', 200, 300, 400, 500, 600, 700 SOLUTION USING DYNAMIC SQL declare @sql varchar(4000) = '' select @sql = @sql + ' se

Categories : Sql Server

How to fix this error: Conversion failed when converting the nvarchar value 'String or binary data would be truncated.' to data type int
The error is pretty self explanatory. Try replacing N.value('(ControlID)[1]', 'NVarchar(MAX)'), N.value('(MaxLength)[1]', 'NVarchar(MAX)'), with N.value('(ControlID)[1]', 'int'), N.value('(MaxLength)[1]', 'int'), EDIT: Check source table: IPAddress varchar(1) - in the actual insert I notice an IPAddress of varchar(20). You should alter table and increase this column width.

Categories : Sql Server

Detect Client's Local IP address
Yes, it's possible. Provided query returns IP address of current client connection. select max(client_net_address) client_net_address from sys.dm_exec_connections where session_id=@@SPID

Categories : Sql Server

SQL Server Concatenation & Display in VB
To concatenate int and varchar , First you should convert int to varchar convert(varchar,anyint)+' '+'varchar string'; it will remove your : Conversion failed ERROR

Categories : Sql Server

SSRS enterprise edition installation
Install it from Microsoft website (Microsoft SQL Server Data Tools) http://www.microsoft.com/en-us/download/details.aspx?id=36843

Categories : Sql Server

How to count same numbers and return muximum counted numbers value from each column
Try this one: CREATE SAMPLE DATA use tempdb create table temp( id int, c1 int, c2 int, c3 int ) insert into temp select 1, 5, 8, 3 union all select 2, 7, 4, 7 union all select 3, 5, 8, 7 union all select 4, 8, 2, 7 SOLUTION ;with cte as( select c1 as val, 'c1' as col from temp union all select c2 as val, 'c2' as col from temp union all select c3 as val, 'c3' as col

Categories : Sql Server

Parsing string either regular expression or by comma separated in MsSql
This is how I would do it if the number of columns is unknown. I changed your ufn to have an identity columns which I can later use to PIVOT the resultset. This has to happen in dynamic SQL if the columns are unknown. alter FUNCTION [dbo].[ufn_Parserstring] ( @StringInput VARCHAR(max), @separators varchar(255)) RETURNS @OutputTable TABLE ( id int identity, [String] VARCHAR(max) ) AS BEGI

Categories : Sql Server

sub query in select clause vs Apply operator performance
Did you look at at query plan Select max(T2.LineNum), T1.Docentry, T1.Docdate, T1.CardCode FROM ORDR T1 join RDR1 t2 on T1.Docentry = T2.DocEntry group by T1.Docentry, T1.Docdate, T1.CardCode

Categories : Sql Server

SQL Server Self Join and more
You could also use a common table expression if you'd like It may be easier. For example, to find all parents with students in a particular class, you would do this: --Variables for testing DECLARE @classid VARCHAR(10) SET @classid='2a' --Common table expression ;WITH students AS ( SELECT [USERID] FROM USERS WHERE [CLASS]=@classid ) ,parents AS ( SELECT u.[USERID] FROM USERS u INNER JOIN stu

Categories : Sql Server

SQL Server Replication Overrides Data at Subscriber
The solution to the Central Subscriber Model is horizontal partitioning, using static row filters, and setting the Action if name is in use article property to Delete data. If article has a row filter, delete only data that matches the filter. Horizontal partitioning Ideally, published tables in a Central Subscriber topology will be horizontally partitioned. In order to horizontally partition t

Categories : Sql Server

Merging two columns with values from another table
To update the table do update t1 set t1.sales = t2.sales from table1 t1 join table2 t2 on t1.customername = t2.customername and as a select use select t1.customername, coalesce(t1.sales, t2.sales) as sales, t1.date, t1.variable1 from table1 t1 left join table2 t2 on t1.customername = t2.customername

Categories : Sql Server

A Report: Complex SQL Query from multiple tables
I am only mentioning the relevant columns of below tables so that it will be sort and understandable. << Then we need the keys and constraints; that means DDL of some kind. Is the “item_code” like a UPC, EAN or other industry standard code? We have no idea. But that sequential “item_id” looks useless; did someone accidentally put an IDENTITY column on the table? We need

Categories : Sql Server

Possible To Improve On This TSQL Query?
--try this, variant using temp tables --remove calculation from subquery to temp table -------------------------------------------------------------------------------- IF OBJECT_ID('tempdb..#History') IS NOT NULL DROP TABLE #History SELECT History.AlertID , MAX(History.CreatedBy) AS CreatedBy INTO #History FROM [ISG_SOI ].[dbo].[AlertHistory] AS History INNER JOIN [ISG

Categories : Sql Server

option strict on disallows implicit conversions between 'boolean?' to boolean in SQL statement
If it is related to nullable type, this might help. Function(s) ((s.PdId.HasValue AndAlso s.PdId.value = ProjDivId) And (s.selected.HasValue AndAlso s.selected.value = True))

Categories : Sql Server

Using SqlBulkCopy with a partitioned view in SQL Server
Given that SQL Server has no support for partitioned views - partitioned tables are something different - likely the view is read only and you msut write to the underlying correct table. Simple like that. Possibly also that there is an instead of trigger on the view that is not triggered by bulk copy. That said, it is pretty bad to sql bulk copy to a table (sql builk copy is written by someone wh

Categories : Sql Server

sql insert operation in stored procedure
try it like this - it looks like you want t do update ALTER PROCEDURE setstaffsalary @staffid int = 0 , @amount int = 0 AS BEGIN UPDATE accstaff SET totalsalary = @amount WHERE fk_staffid = @staffid; END;

Categories : Sql Server

Relationship between Isolation Level and Locks
Well, locks are partly an implementation detail. They are used (in SQL Server) to give you the guarantees that the isolation levels give you according to the standard. For example, SERIALIZABLE gives you as-if single-threaded access to the database. This is done by S-locking all data you have read in order to freeze it. Locks are not purely a detail because the very details of a locking scheme le

Categories : Sql Server



Recently Add
Multiple SQL Server Reporting Services machines using same database instance
Dynamic SQL declaration executed by batch file
Dividing by number of rows as matched by ID
How to convert VS2010 SQL Server Unit test project to VS 2012?
SQL Server Namespace
Conversion Failed when converting the varchar value 'Wired' to data type int
Multiple Session State databases on Database Instance
Increasing size of SQL Server MDF file
Connecting Laravel 4 (on OSX) to MSSQL
TSQL Query Inner Selects from Outer Where Clause
SQL IDENTITY autoincrement when 0 but still allow explicit inserts
Checking update in database with ESQL without loop
Testing sp_send_dbmail
Transpose Rows to Columns for the same ID
SQL Server 2008 R2: Dynamic Pivot table query performance
Want cumulative sum by raw & column in SQL Server
Add a trigger on a column when I update its value very quickly
Xamarin and SQL Server
CRM 2011 Email in Workflow
LIKE and Equals not working as expected
Conflict between sql server 2008 r2 and 2012
create sql view from a sql function
Create one trigger for multiple operations
Unpivot Period Table
How to fix this error: Conversion failed when converting the nvarchar value 'String or binary data would be truncated.' to data type int
Detect Client's Local IP address
SQL Server Concatenation & Display in VB
SSRS enterprise edition installation
How to count same numbers and return muximum counted numbers value from each column
Parsing string either regular expression or by comma separated in MsSql
© Copyright 2017 spot7.org Publishing Limited. All rights reserved.