FAQ Database Discussion Community


Return a single value depending on contents of a variably sized set of data

sql,sql-server,sql-server-2008-r2
I'm querying for user information from the database. I'm specifically interested in getting data from a field called CONTENTS, but only if there is a matching DESCRIPTION for a given USERID. Additionally, I do not want to return the CONTENTS, I instead want to parse through a set of them....

SQL Server Versions - 2012 vs 2014 [closed]

sql-server,oracle,sql-server-2008,sql-server-2008-r2,sql-server-2012
My team needs to get SQL Server as our backend database. I would like to find out if we should get 2014 or 2012 R2. The rest of the company is probably still SQL Server 2008 R2, Oracle and probably MySQL. I wonder if we get 2014 version which is...

CROSS JOIN with one empty table sql server

sql-server,sql-server-2008-r2
I have two tables say MyFull and MyEmpty. I need to do the combination of all records from both tables. Sometimes, MyEmpty table might have no records, in this scenario I need to return all records from MyFull. This is what I have tried: --Q1 >> returns no results SELECT...

aspnetdb.mdf file is not getting created?

visual-studio-2012,sql-server-2008-r2
I have VS2012 and I'm creating a website application in ASP.NET using C#. I want to use VS2012 built in membership and role tool to create users and roles. Problem is that ASPNETDB.mdf database is not getting created in App_Data folder when I launch ASP.net configuration utlility. Moreover there is...

Returning a column in a result set from merging results in another table

sql-server,sql-server-2008,tsql,sql-server-2008-r2
Please forgive the title, I wasn't sure how exactly to describe the situation below... I have 2 tables. One column in the first table has a set of comma delimited codes that are in a second table. So, the two tables look like this: Table1 RepID | RepDate | RepLocation...

Make a setting to connect to server in SQL Server 2008 R2 using C#

c#,sql-server-2008-r2
How can I make a setting so that I can connect to the server in SQL Server 2008 R2? I mean when my program runs I need to insert first the data source, initial catalog etc. so that I can connect to the server.

Unable to set full-text index to 'active' in table-designer

sql-server,sql-server-2008-r2,full-text-search
In SQL Server 2008 R2 Management Studio, I have set my database to be full-text indexed and ran the command EXEC sp_fulltext_database 'enable' along with creating a catalog. When I open the table design mode, and right click the Primary Key, then click Full-Text Indexes in the context menu, I...

sumifs on excel linked sql server query with date

sql-server-2008-r2,excel-formula
I'm having no luck being able to do sumifs or countifs on a cell range that is linked to an MS SQL server 2008R2 view when using the date column as a criteria. If it is done on a native table link they works fine but it appears that the...

SQL Different output result set for same query

sql-server-2008-r2,sql-server-2012
Why this SQL query produces different output result in SQL 2008R2 and 2012.It seems 2008R2 results for this query looks fine.Why the same sort order is missing in 2012? Declare @TempTable TABLE (Name VARCHAR(25), RankOrder INT) INSERT INTO @TempTable SELECT 'b', 2 UNION ALL SELECT 'd', 4 UNION ALL SELECT...

Cannot add new shared dataset to report

visual-studio-2013,reporting-services,sql-server-2008-r2,ssrs-2008-r2
I have a summary report with about 20 datasets, linking to 20 detail sub-reports. I have added datasets based on stored procedures for each of the detail reports. Now when I try to add another one I get an error message Could not create a list of fields for the...

How dynamically insert data in respective tables based upon xml in Sql server

sql-server,sql-server-2008,tsql,sql-server-2008-r2,sql-server-2012
I have this type of input XML parameter pass in stored procedure, which is passed from my .net application. Now, I need to insert records in each respective table: <root> <table1> <patid>123</patid> <name>gresh</name> <fname>kumar</name> </table1> <table2> <patid>123</patid> <Schoolname>12345</Schoolname> </table2> <tablen> <patid>123</patid> <nfield>12345</nfield> </tablen> <root> Suppose table1 will insert data...

combine row for each Unique value based on datetime

sql-server,sql-server-2008-r2
I have a table with three column TICKET_ID ASSIGN ASSIGN_DATE 5692 ASSIGN-5 2013-07-17 19:37:09.000 5740 ASSIGN-5 2013-07-17 19:37:09.000 5741 ASSIGN-5 2013-07-17 19:37:09.000 5742 ASSIGN-5 2013-07-17 10:40:15.000 5742 ASSIGN-4 2013-07-17 19:37:09.000 I need to combine ASSIGN row to one row for each TICKET_ID based on datetime ascending result should be like...

How to convince sql server 2008r2 to use clr v4.0 instead of v2.0?

.net,sql-server,sql-server-2008-r2,sqlclr
I have sql server 2008r2. According to my internet research it supports .net framework 4.0. I tried to install my assembly with sql clr functions and receved an error. CREATE ASSEMBLY for assembly 'MyAssembly' failed because the assembly is built for an unsupported version of the Common Language Runtime. Query...

Get Numbers Range from Table in MSSQL

sql-server,sql-server-2008,sql-server-2008-r2,user-defined-functions
I have a table in MSSQL 2008R2: ID | PinAddress ------------------------------------- 1 | 1 1 | 2 1 | 3 1 | 4 1 | 5 1 | 6 1 | 16 1 | 31 2 | 55 2 | 56 2 | 57 2 | 81 2 | 82...

SQL display months when Count is 0 [duplicate]

sql,date,count,sql-server-2008-r2,datepart
This question already has an answer here: Include monthly counts including months where data doesn't exist 1 answer I was wondering if someone could help me... I have the following SQL query (have shortened this down as its a large union query) SELECT [ Month ], sum(total) from (select...

SQL Server 2008 R2: Only and only in condition

sql,sql-server,sql-server-2008-r2
I have the following table with two fields: create table teste_r ( colx varchar(10), coly varchar(10) ); Insertion of records: insert into teste_r values('3','A'),('3','B'),('3','C') ,('2','A'),('2','A'),('2','C') ,('1','A'),('1','D'); Note: Now I want to show the colx which is belongs to only and only A and C. So the according to the requirements...

SQL select where not in select statement

sql,sql-server-2008-r2
I'm trying to Select all the records in my database that don't exist in a subquery. For some reason it returns nothing even though the sub query returns 2000 or so rows on it's own and the main query returns over 5000. I need all the records that aren't contained...

sp_helptext return (server internal)

sql,sql-server,database,sql-server-2008,sql-server-2008-r2
I am try to show sp_xml_preparedocument by using the below code sp_helptext 'sp_xml_preparedocument' or sp_helptext 'sys.sp_xml_preparedocument' this returns : Text (server internal) This code work fine with another custom SP ...

Use a trigger in SQL Server 2008 R2 to change the value of one column based on the value of another column during update

sql-server,sql-server-2008-r2
I need to change the value of a column when a record is updated, based on the value of another column in the same table. The legacy application updating the table cannot be re-coded to handle this. The basic logic would be: If DateShipped is not null, set OrderLocation =...

Pivot table query using SQL Server 2008 R2

sql,sql-server,sql-server-2008-r2,pivot
I have the following table with two fields as shown below: I want to show the pivot table of the following data into verticle form. Table: test_10 create table test_10 ( col1 varchar(10), col2 varchar(10) ); Inserting records: insert into test_10 values('A','2015-01-01'),('A','2015-01-05'),('A','2015-01-10'),('A','2015-02-15'), ('B','2015-01-01'),('B','2015-01-05'),('B','2015-01-10'),('B','2015-02-15'), ('C','2015-02-02'),('C','2015-02-05'),('C','2015-02-08'),('C','2015-02-16'); Expected Result: ColX Jan Feb -------------------...

Return 0 in sum when no values to sum - sql server

sql,sql-server,sql-server-2008-r2,sum
Trying to return 0 if any of these columns have no values returned, in my particular case 'Past Due' has no values to total, so it is returned, so i get the return in the second snippet here. How can I return something if these are no values to count?...

Select Query giving output as ??????????? with different language text

sql-server-2008-r2
Why is this query giving me ???????? as output. Declare @Search NVARCHAR(MAX) = 'اختبار كتاب اللغة العربية' SET @Search = N''+ @Search select @Search Where as this one works fine :- Declare @Search NVARCHAR(MAX) = N'اختبار كتاب اللغة العربية' select @Search I want to make my query dynamic. Is there...

How can I convert split function to inline table valued udf in SQL server?

sql-server,sql-server-2008-r2,inline-table-function
Assuming I have this query ( pseudo) : Select T.a, T.b, (select top 1 element from fn_split(c,',') where element=T.element) From largeTable T Where fn_split runs for each row , I would like to use inline table valued udf so , that performance will be better. NB : fn_split just create...

SQL DDL to support migration and updates

sql,sql-server,sql-server-2008-r2,database-migration,ddl
I have a web app that uses a basic ms-sql DB only tables and constraints nothing special. Every time i need to upgrade the schema of my DB I delete the old one and recreate a new one using a standard DDL script with a lot of create table clauses....

Prevent concurrent selection of same next calculated value

c#,sql,sql-server-2008-r2
I have a table called SerialNumber in my database that looks like this: [Id] [bigint] IDENTITY(1,1) NOT NULL, [WorkOrderId] [int] NOT NULL, [SerialValue] [bigint] NOT NULL, [SerialStatusId] [int] NOT NULL Where in my application I always need to get the MIN SerialValuewith a SerialStatusId of 1 for a given Work...

Compare each records of same table in sql server and return duplicates

sql-server,sql-server-2008,sql-server-2008-r2
I have table like below.I wanted to get the duplicate records.Here the condition if date2 and date4 having same date OR dates within less than or equals to 10 days of each other then records are duplicate. I have around 2000 records in the DB.Showing here few sample example. Date1...

Grouping and checking for conditions using a query

sql,sql-server,sql-server-2008-r2
I have been asked to compile a SQL query for the following case: ID | PARAMETER | VALID | VALUE ------------------------------ 1 | Event1_Val | 1 | 0 1 | Event2_Val | 0 | 50.00 1 | Event2_Avg | 0 | 55.00 1 | Event1_Avg | 0 | 66.67 2...

SQL Server Synchronize data between two database

sql-server,database,sql-server-2008-r2
I am using SQL Server 2008 R2. I want to synchronize data between two databases. The primary database is where all insert, update and delete operations take place & it is kept on a different server on the network. The other database is that reflects the state of primary database...

SQL Server 2008R2: Evaluating three columns case statements or coalesce?

sql-server-2008-r2,nested-queries
I have been working on figuring out how to accomplish the following for a few days now: I have one table which has alternate addresses, but I only need to pull the city. However the city might be in field5,field4, or field3. What I would like to do, and have...

Why do i get network error when i tried to access a database file(.mdf)?

c#,linq,linq-to-sql,sql-server-2008-r2
i have used following simple LINQ2SQL query to access an .mdf database file on hard drive and display certain rows class Program { static void Main(string[] args) { string str = @"D:\SE\MS\LINQ\LinqInAction\Data\NORTHWND.mdf"; DataContext db = new DataContext(str); Table<Contact> contacts = db.GetTable<Contact>(); var x = from y in contacts select y;...

SQL Server: Sum of Unique values for multiple columns

sql,sql-server,sql-server-2008,sql-server-2008-r2,sql-server-2012
Using SQL Server, I have a table as shown in the sample table below. I need to have sum of all the unique values per the columns "BookOrder, StationaryOrder, and Printing Order". SAMPLE TABLE: KeyIDCustomer BooksOrder StationaryOrder PrintingOrder 29945843 1070756 1891514 198876 29945843 1070756 1893827 198876 29945843 1070758 1891514 198876...

Stored procedure part optimization

sql-server,stored-procedures,sql-server-2008-r2,query-optimization
I have a stored procedure CREATE PROCEDURE [dbo].[MyProcedure] -- Add the parameters for the stored procedure here @xml XML AS BEGIN SET FMTONLY OFF declare @idoc INT; -- table(CentreId bigint, LanguageId int) EXEC sp_xml_preparedocument @idoc OUTPUT, @xml --declare @LangCentre table (CentreId bigint, LanguageId int) CREATE TABLE #LangCentre ( CentreId bigint,...

Count multiple column without zero SQL

sql,sql-server,sql-server-2008-r2
I want to have the following result: -------------------------------------------------------- V1 V2 V3 Result -------------------------------------------------------- 5 10 15 10 4 0 10 7 2 2 0 2 My table: -------------------- V1 V2 V3 -------------------- 5 10 15 4 0 10 2 2 0 What I want to do is, average the column...

One identifier for set of values

sql,sql-server,sql-server-2008,sql-server-2008-r2
I have this two columns. One is filled with some data, second is null. col1|col2 --------- null| 72 null| 72 null| 72 null| 33 null| 33 null| 12 null| 12 null| 55 null| 72 I want to generate values for col1 that will gather and group values from col2. So...

Find intervals when flag was active

sql,sql-server,tsql,sql-server-2008-r2
I need to find out from a table below when flag inserted was active (inserted = 1). I need to show interval date_from to date_to when inserted was active. Here is the table: no dcr action deleted inserted --------------- ----------------------- ------ ----------- ----------- 191005040000806 2014-04-17 10:39:59.790 U 1 0 191005040000806...

Delete with double FROM clause

sql-server,sql-server-2008-r2
I'm surprised this works. I'm not sure I understand why. create table #tempt(something int) DELETE FROM #tempt -- works fine FROM #tempt EM I'd expect to need to use the alias in the DELETE and I'd especially expect to not need that first FROM. Why can that first FROM exist?...

Unable to configure TFS backup using Backup wizard

sql-server-2008-r2,tfs2010,database-backups
When trying to configure the TFS 2010 backup using the TFS Power Tools I kept running into teh following error message: Account TFS\tfsadmin failed to create backups using path \\tfs-xxxxxxx.local\TFSBackups The strange thin is that TFS\TFSAdmin has full permissions on both share and file system and that the share path...

SQL Server optimal query based on value distribution

sql-server,sql-server-2008-r2,query-performance,table-statistics
I have a table that stores dynamic user data in a key-value pair format. Something like this: UserId | Key | Value --------------------------------- 1 | gender | male 1 | country | Australia 2 | gender | male 2 | country | US 3 | gender | female 3 |...

Access denied SSIS w/ Parameters via xp_cmdshell

ssis,sql-server-2008-r2,xp-cmdshell
I have searched up and down for a solution and cannot find anything that helps. Everything that I have tried doesnt seem to work. I have two database. I cannot modify Database A by adding a table or anything like that. I cannot enable cross-database access. I believe I cant...

I want to sync my local database with the one online [closed]

sql,database,sql-server-2008-r2,synchronization,replication
I want to sync my local database with the one online after a certain time I am using sql server Management 2008 r2 the purpose is to open reports online so that the client can check it from anywhere any suggestions to do this. I want to sync the local...

Dynamic SQL string returns NULL

sql-server,sql-server-2008-r2,dynamic-sql
I am trying to construct a dynamic SQL statement and for some reason it is not returning the expected results. This is my query DECLARE @user_script AS VARCHAR(MAX); SELECT @user_script += 'IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = ' + SPACE(1) + '''' + [name] + ''''...

Why do my local temporary tables display as invalid object names in SQL Server 2008 but not in R2

sql-server,sql-server-2008,sql-server-2008-r2
I am moving some stored procedures from a database in SQL Server 2008 R2 to an identical database in SQL Server 2008 (not R2). One of them makes use of temporary tables. While the create script generated from the R2 database executed fine, the temporary tables and fields display as...

Write a query to count different kind of records in single line result

sql,sql-server,sql-server-2008-r2,group-by,sql-server-2012
I have a table with 2 columns TimeStamp and ID in MS SQLSERVER TimeStamp ID 2015-05-20 1 2015-05-20 2 2015-05-20 1 2015-05-21 1 2015-05-21 2 2015-05-21 2 2015-05-21 1 My requirement is to calculate number of records for every Id according to date. Requirement: Date No of records for Id=1...

How to install ssas AdventureWorks DW with SQL Server 2008 R2

sql-server,sql-server-2008,sql-server-2008-r2,ssas,adventureworks
I am doing a proof of concept to explain the team using cubes improves the application's query performance. I am allowed to use VS 2008 BI tools. I am in need of the AdventureWorks2008R2 DW to be installed to write some mdx queries to query the cubes. I have landed...

How to convert int to time (e.g. “1930” to “19:30”) and get difference between start and end time in hours and minutes (HH:MM)?

tsql,sql-server-2008-r2
I have 2 columns on my table that contains time in and out. I need to subtract them and get the spent hours even converting them to time or any other way. CREATE TABLE [dbo].[FELData]( [RCIN1] [numeric](4, 0) NOT NULL, [RCOUT1] [numeric](4, 0) NOT NULL ) ON [PRIMARY] RCIN1 RCOUT1...

Constraint with multiple tables. UDF?

sql-server,sql-server-2008-r2,constraints,user-defined-functions
I have two tables: table A and table B. These two tables are linked with a primary key in A and a foreign key in B. Table A: CREATE TABLE [BIO].[table_A]( [table_A_id] [int] IDENTITY(1,1) NOT NULL, [type_id] [nvarchar](2) NOT NULL CONSTRAINT [PK_table_A] PRIMARY KEY CLUSTERED ( [table_A_id] ASC )) Table...

Recover master database [closed]

sql-server,sql-server-2008-r2
I got the followng error 2015-05-13 12:04:31.00 Server Using locked pages for buffer pool. 2015-05-13 12:04:31.10 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required. 2015-05-13 12:04:36.14 Server Node configuration:...

Save view with variable in sql server

sql,sql-server-2008-r2
I've designed a complex view in sql server 2008 R2. Which is similar as follows- SELECT column1,column1 FROM Table WHERE [email protected] I would like to save it. But I'm getting following message- Must declare the scalar variable "@VarName". Any help?...

Selecting distinct consecutive rows amongst duplicates

sql,sql-server,sql-server-2008-r2
I have a table with the structure below: create table roster ( date date not null, first nvarchar(20) not null, second nvarchar(20) not null, third nvarchar(20) not null, fourth nvarchar(20) not null, ) go And that has the following data inserted: insert into roster values ('2015-06-10 12:45:34', 'e', 'm', 'a',...

Correct way to write a SQL Query to combine two result sets with a condition, giving a distinct list back

sql,tsql,sql-server-2008-r2
I have 3 tables like the following: tbl_risk (risk_ID, Title) tbl_approval_history (approval_ID, risk_ID, status_ID, approver_ID) tbl_users (user_ID, username) A risk can have many approval history entries with different statuses. I need a query which will return a distinct list of risks with an approved by column, so output is risk_ID,...

Stored procedure - Error converting data type varchar to numeric

sql,sql-server,stored-procedures,sql-server-2008-r2
When running a simple stored procedure, I'm getting this error and frankly I don't know why. Msg 8114, Level 16, State 5, Procedure PROC_TEST, Line 15 Error converting data type varchar to numeric. Columns: SI_ID is NUMERIC(19,0) SI_COMPANYID is NUMERIC(19,0) SI_LOGINID is VARCHAR(100) Any idea why? This is the stored...

Ambiguous column when update with remote server in SQL server?

sql-server,sql-server-2008-r2
I have 2 SQL servers . dstest\mssql2008 <--I'm currently at this instance ( server) dstrn <-- another server Both servers has the same table called : EServices_Pages_Content Goal : I need to update data on dstest from dstrn On the current server (dstest) I have : I can(!) access dstrn...

Embedding image in email with SQL [sp_send_dbmail]

sql,sql-server-2008-r2,html-email,email-attachments,sp-send-dbmail
I want to embed an image into an HTML email with SQL server. The emails are getting sent but the images do not show. My code is as follows: DECLARE @body_custom VARCHAR(MAX) SET @body_custom = '<head> <title> Embedded Logo Example</title> <meta name="Generator" content="EditPlus"> <meta name="Author" content=""> <meta name="Keywords" content=""> <meta...

INSERT single quoted value for all column types in SQL Server

sql-server,sql-server-2008,sql-server-2008-r2,sql-server-2012
Say you have a column of int (also bit, etc.) CREATE TABLE TEST (a int) It seems it doesn't matter if you insert value with '1' or 2 INSERT INTO TEST VALUES ('1') INSERT INTO TEST VALUES (2) Is there any side-effect if I insert/update values by single-quoting them (e.g.'value')...

Select a specific date for the current year

sql,tsql,sql-server-2008-r2
I have a query that needs to select {current year}-05-31 00:00:000. Is there a way I can do it without just concatenating the year on to that string? Here's my current query: SELECT DATEADD(dd, 30, DATEADD(mm, 4, DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))) ...

Unable connect to SQL Server 2008 R2

c#,sql-server,sql-server-2008-r2
I have a C# project with SQL Server 2008 R2 and the project runs with no problem on my local machine and when I publish it to the server Win2008 R2. But the project doesn't work and can't connect to the server with no error. All the database setting check...

How to use case in sql query

sql,sql-server,sql-server-2008,sql-server-2008-r2
My Query is below SELECT *, CASE WHEN SUBSTRING(controlId,1,1) = 'M' THEN (select fullname from master as controler where Regular.controlId = Master.MasterId) WHEN SUBSTRING(controlId,1,1) = 'A' THEN (select fullname from Admin as controler where Regular.controlId = Master.AdminId) END AS controler FROM Regular INNER JOIN Master ON Regular.ControlId = Master.MasterId INNER...

how to get a percentage depending on a column value?

sql,tsql,sql-server-2008-r2
Update 05/18/15 Having a column with customers names listed on it. How can I get a percentage based in one customer per date? For example CustomerName Date Sam 04/29/15 Joy 04/29/15 Tom 04/29/15 Sam 04/29/15 Oly 04/29/15 Joy 04/29/15 04/29/15 Sam 04/29/15 04/29/15 Sam 04/29/15 Oly 04/29/15 Sam 04/29/15 Oly...

unusual sql server query result

sql,sql-server,database,sql-server-2008-r2
Let's say I have a table called nameAge: ID Name Age 1 X 12 2 Y 12 3 null null 4 Z 12 and when I run a query like: select * from nameAge where Age <> 12 it returns me an empty result set while I have row with...

Why doesn't my Full text search yields results?

sql-server,sql-server-2008-r2,full-text-search
I have a simple View: Let's select top 5 rows : (the view doesn't have top 5 - it's just for clarity here) SELECT TOP 5 [CustomerId] ,[EngLastFirst] ,[EngFirstLast] ,[LocalLastFirst] ,[LocalFirstLast] FROM [WebERP].[dbo].[View_CustomersNames] Result : Let's create a Full Text Index (Right click on the view): OK. so now we...

sql query date formating (Hours:Min)

sql,sql-server-2008-r2,date-formatting,qsqlquery
How to display values as hours:Minutes format (eg 77 , Result: 1:17) info : select SUM([Travel Time] + [Total Productive Time]) from vAction_Reg where incident_id = '10064068' Result: 77.00 Need to get result in below format (1:17) Changed Query: SELECT(SELECT ISNULL(SUM(action.service_time), 0) AS Expr1 FROM dbo.act_reg AS action INNER JOIN...

ssis swap some values in a data flow if they match a lookup table

ssis,sql-server-2008-r2,transform,lookup
Here's my problem - Midstream in my data flow, we have some values in one column that we want to swap for other values based on a lookup table. For example, if I had a rowset like this: Key Value 1 A 2 B 3 A 4 C 5 D...

Get value of a specific column of a row without primary key

sql,sql-server,sql-server-2008-r2
This is purely out of curiosity. create table test (ename varchar(50)) insert into test values ('abcd') insert into test values ('pqrs') insert into test values ('lmno') insert into test values ('xxxx') insert into test values ('tops') I want the value of 3rd row from this table in a variable. i.e...

join tables that are on different DB instance inside a stored procedure

sql,sql-server,stored-procedures,sql-server-2008-r2
I have created a stored procedure inside sql server 2008 r2, where i use to reference tables from 2 different Database that are defined inside the same sql instance as follow:- [DB1].[dbo].[Technology] [DB2].[dbo].[SystemInfo] currently we moved the DB2 to be on separate DB instance but at the same database server,...

Trigger after insert on two tables

tsql,triggers,sql-server-2008-r2
I have the following trigger right now but I now have the requirement to only kick off the trigger if ANOTHER table Facility has a record inserted into it with a LocationID = 'ER'- As per the comment I've updated the trigger - ALTER TRIGGER [dbo].[VoceraOeOrders] ON [dbo].[OeOrders] FOR INSERT...

Does JOOQ Support SQL Server 2005 or SQL Server 2008 R2 in Code Generator?

sql-server,sql-server-2008,sql-server-2005,sql-server-2008-r2,jooq
I've been trying to auto-generate artefacts for my SQL Server Database. Its originally from SQL Server 2005, and then I didnt find in the documentation of JOOQ, so i imported it on a 2008 R2. But then its still says the same thing: 1428 [main] WARN org.jooq.util.AbstractDatabase - No schemata...

DateDiff to show Minutes and Seconds

sql-server-2008-r2,datediff
I need to calculate the diff and have my answer show minutes and seconds. Here is sample Declare @clockin datetime, @clockout datetime, @total decimal(18,4) Set @clockin = '2015-01-03 08:15:19.000' Set @clockout = '2015-01-03 12:02:42.000' Set @total = DateDiff(minute, @clockin, @clockout) But this returns a whole number I need it to...

Is there a quick way to view the size of an existing column when writing a query?

sql,sql-server,sql-server-2008-r2,sql-function
I'm in the process of transferring lots of embedded SQL in some SSRS reports to functions. The process generally involves taking the current select query, adding an INSERT INTO part and returning a results table. Something like this: CREATE FUNCTION [dbo].[MyReportFunction] ( @userid varchar(255), @location varchar(255), more params here... )...

Prepare single where clause

sql,sql-server,postgresql,sql-server-2008-r2
I have the following table with two fields namely a and b as shown below: create table employe ( empID varchar(10), department varchar(10) ); Inserting some records: insert into employe values('A101','Z'),('A101','X'),('A101','Y'),('A102','Z'),('A102','X'), ('A103','Z'),('A103','Y'),('A104','X'),('A104','Y'),('A105','Z'),('A106','X'); select * from employe; empID department ------------------ A101 Z A101 X A101 Y A102 Z A102 X A103...

Does Persisted Computed Column need to update all rows or just affected rows?

sql-server,performance,sql-server-2008-r2,calculated-columns
In this Stackoverflow article, I understand what Persist mean and their advantages and disadvantages. However, I tried to research deeper but cannot find about the "update" one. Here is my scenario (TL;DR version below): I have Order table and OrderDetail table, as in this diagram: The OrderDetail.Subtotal is a computed...

Get records that match all corresponding records on conditional join

sql,sql-server,sql-server-2008-r2
I have a business problem that I formulated to the following example for easier communication. Say I have three tables Employee, Project, EmpWorkProj. EmpWorkProj is used to link employees and the projects they worked on (or link table between Employee and Project). This is an example of the table data:...

How to get Duplicate records from single table depends on column value?

sql-server,tsql,sql-server-2008-r2
I got table like this: declare @t1 table(Pat_Ref int,Fname VARCHAR(20), Sname VARCHAR(20),minor VARCHAR(1),SourceSys VARCHAR(40),regdt datetime) insert into @t1 values (111,'John', 'Wayne','N','ick','2015-06-09 21:31:09.253') ,(111,'John', 'Wayne','N','ick','2014-05-09 21:31:09.253') ,(111,'John', 'Wayne',null,'hpk','2015-04-09 21:31:09.253') ,(112,'Jill', 'Smith','N','ick','2015-01-08 21:31:09.253') ,(112,'Jill', 'Smith',null,'hpk','2015-05-01 21:31:09.253') ,(113,'Bill', 'Peyton','N','ick','2015-06-09 21:31:09.253') ,(114,'Gill',...

how to get the unique records with min and max for each user

sql,sql-server,sql-server-2008,sql-server-2008-r2,sql-server-2012
I have the following table: id gender age highest weight lowest weight abc a f 30 90 70 1.3 a f 30 90 65 null a f 30 null null 1.3 b m 40 100 86 2.5 b m 40 null 80 2.5 c f 50 105 95 6.4 I...

Can a SSIS 2012 package be deployed to SQL Server 2008 R2?

ssis,sql-server-2008-r2
I built a SSIS package in VS2013 and can't find any deployment instructions. One person mentioned he thought you can't deploy backwards. So before I rebuild the package in BIDS 2008, I want to make sure there is no way to deploy.

How can I increase the performace of Stored Procedure

sql,sql-server,sql-server-2008,sql-server-2008-r2
I have stored procedure which is taking around 2 minutes to get executed, I have used few temp tables in that and while loop , am not able to figure out the best way to increase the speed of stored procedure. My stored procedure is as follows ALter procedure _sp_Get_PatentAssignment_Mail_Test...

Cannot run Invoke-Sqlcmd in a PS1 file but can on the console

sql-server,powershell,sql-server-2008-r2,sqlcmd
I'm currently on a machine with SQL server 2008 and according to this question (How to execute .sql file using powershell?), I've installed the powershell snapins to interact with SQL server. The problem is that now I have it installed, I can run Invoke-Sqlcmd from the command line like this....

Sub query in t sql select statement

sql,tsql,stored-procedures,sql-server-2008-r2
I have a query which lists users but it requires a sub query to get whether they are available today. I need the fourth column 'Availability' to iterate through each user and display if they have availability or display a null. I've tried everything I can think of sub queries,...

Trying to get a column value of an ancestor when using HIERARCHYID

sql,sql-server,sql-server-2008,sql-server-2008-r2,sql-server-2012
I'm evaluating the HIERARCHYID data type to see if it'll meet my needs for a project. I was hoping to use it to get the manager of a given employee ID. I'm looking at tables in the AdventureWorks DB (the 2012 version), specifically the Person.Person and HumanResources.Employee tables. My query...

Will installing full text search on live SQL server 2008r2 database server cause downtime?

sql-server,sql-server-2008-r2,full-text-search
Will adding the SQL full text component an existing live SQL Server 2008R2 Database Server cause the server to be restarted/reset?

right to left string in SQL server

sql,sql-server-2008-r2
Consider below query containing both Persian(a right to left language) and English(a left to right language): SELECT 'نرم افزار SQL سرور' the required result is this string : سرور SQL نرم افزار Is there any function or any other way to converting string from ltr to rtl??...

Group by custom function in T-SQL

sql-server,tsql,sql-server-2008-r2,group-by
I have a table of people in which there may be duplicates. My goal is to return a list of possible duplicates so that we can combine them into a new person. I want to group by first_name and last_name, obviously. However, if both person records have a defined birth_date...

How can I execute/call a MySQL stored procedure using SQL Server 2008 R2

tsql,sql-server-2008-r2
How can I execute/call a MySQL stored procedure using SQL Server linked server? Here is the SQL I have so far. select * from openquery(linked_server_name, 'call sproc_name()') ...

Stored Procedure in SQL 2008 Not Working in 2005

stored-procedures,sql-server-2005,sql-server-2008-r2
Hi I'm Trying to Create Stored Procedured in SQL2005 below is my syntax CREATE PROCEDURE [dbo].[POBalance] @PONumber nvarchar(50) AS BEGIN declare @status bit = (Select status from tblPOHeader where PONo = @PONumber ) if @status = 'False' Select A.Description, C.qty as POqty, B.QtyDelivered as PDQty, case when A.partialflag ='false' then...

Get information from table based in conditions

sql,sql-server-2008-r2
I am having some trouble trying to figure out a way to get some information from a table based in some conditions. What I am trying to do: Case the CATEGORY column is in A, B, C or D the I would like to get the date from the DATEX...

delete sql rows that have same value in other rows

sql-server-2008-r2,delete,rows
I've a table having data as: C1 || C2 ----------------- a || 1 b || 1 c || 1 a || 0 b || 0 c || 0 d || 0 I've to delete row 4,5,6. All rows with C2 = 0, that have same C1 and C2 = 1....

SQL Server Using TableDiff on large tables

sql-server,sql-server-2008-r2,batch-processing
We have a process which uses uses SQL Server's amazing tableDiff via: Microsoft SQL Server\100\COM\Tablediff.exe It's SQL Server 2008 R2. It connects from one instance to another identical instance. It works very well! I have a situation where a table which now has 10767594 records is taking 2.5 hours to...

How to use SQL Server 2008R2 for working purpose as part of personal use [closed]

sql-server-2008,sql-server-2008-r2,remote-desktop
SQL server 2008R2 was installed on my personal laptop just for my working data, all the logins used were setup by our system admin which was connected too remotely from home. I need to use the same SQL Server for Doing some tutorials and adding my own data apart from...

SQL Server 2008 R2 - store top result and use in SSRS

tsql,reporting-services,sql-server-2008-r2,temp-tables
I have a SSRS Report that displays the total number of days lapsed since a complaint was received. This SQL Query is the difference between today's date and date of the last received complaint. SELECT DATEDIFF(day, MAX(complaints.ComplaintReceived1Date),CURRENT_TIMESTAMP) as total FROM complaints WITH (nolock) If for example this is set to...

Transaction log for database 'tempdb' is full: but Table created anyway?

sql,sql-server,sql-server-2008-r2
I have a query that creates a temporary table and uses this table in later portions of the query. I ran the first part of the query which just creates the table to see the results. I obtained the following result: Query Completed with errors: (166166381 row(s) affected) Msg 9002,...

Issue running stored procedure from .net

asp.net,sql-server,visual-studio-2012,stored-procedures,sql-server-2008-r2
I have this stored procedure : CREATE PROCEDURE [dbo].[GetTransactionFeeType_Pager] @PageIndex INT = 1 ,@PageSize INT = 10 ,@ProgramName VARCHAR = '' ,@RecordCount INT OUTPUT AS BEGIN SET NOCOUNT ON; SELECT ROW_NUMBER() OVER ( ORDER BY ID ASC )AS RowNumber ,FeeType,TransactionNumber,Amount,TransactionDate,ResponseDescription,PaidUsing INTO #Results FROM TransactionLog where [email protected] SELECT @RecordCount = COUNT(*)...

Get a count of a number of records with same create date

sql,sql-server-2008-r2
I have a table containing records all with a create date. I want to Select all the records created in January 2014 and have a count of how many were created each day. I've gotten as far as Selecting all the records created that month but I'm unsure how to...

Process/SPID is being blocked by itself, how to clear/kill without restarting Sql Server

sql-server,sql-server-2008-r2,locking,spid
We have a process that was running for 4 hours. Because it was running so long, it was causing other issues in the database, so it was decided to kill the process. Now, the process is in a suspended state. It also states that it's being blocked by itself after...

SQL Agent Job runtime alert

sql-server-2008-r2,sql-agent-job,sql-job,sql-agent
I was hoping i could get some help on how i can setup an e-mail alert for a specific agent job, such that it sends an e-mail alert when the run duration exceeds 30 minutes. Would it be easier to add this step in the job itself? Are there any...

2008R2 How do I average the total scores summed?

sql-server-2008-r2
How do I do the Average in this coding? Sum(qa.greeting1+qa.greeting2+qa.greeting3+qa.greeting5+qa.greeting6) as AverageTotalCareScore Thank you!...

Pass subset of User Defined Table to a Stored Procedure

sql-server,stored-procedures,sql-server-2008-r2,user-defined-types
I have a User Defined Table that I am passing into a stored procedure from within a stored procedure. DECLARE @tmpInput MyTableType; --Table is populated from an INPUT XML exec ValidateInputXML SELECT * FROM @tmpInput TI WHERE TI.EntryType = 'Attribute'; Now this isn't giving me an error, but when I...

Converting string date to ODBC canonical date

sql,sql-server,sql-server-2008-r2
I'm working with SQL 2008 R2. We have a third party software that is passing a string to a stored proc. The string is a date in the format of: 2015-05-27 11:59pm I have no access to this formatting and cannot change it. I need to convert this string to...

MERGE using a table variable without an obvious matching criteria

sql,tsql,merge,sql-server-2008-r2
I have an InfoPath form that users fill in. It gets assigned a unique report number, and on the report they can select zero or more affected parts each of which have a bunch of columns. This list of affected parts can change as the report is processed. Submitting or...

multiple joins in t-sql

sql-server,tsql,sql-server-2008-r2
I got some problem in querying. I got 2 tables: declare @t1 table(pat_ref varchar(10),pas_id varchar(10), is_mnor char(1)) declare @t2 table (prev_pat_ref varchar(10), pat_ref varchar(10)) insert into @t1 values ('1','111','y') ,('5','115','y') ,('6','116','y') ,('2','112','n') ,('3','113','y') ,('4','114','n') insert into @t2 values ('1','2') ,('5','1') ,('6','5') ,('3','4') Now I want: pat_ref pas_id is_mnor major_ref 1...

union all with #temptable exist condition in sql server?

sql-server,tsql,sql-server-2008-r2
I got INSERT INTO TABLE3 SELECT * FROM table1 UNION ALL if object_id('tempdb..#temp') is not null drop table tempdb..#temp select * into #temp from (SELECT * FROM table2) then original SELECT for UNION but I am getting syntax error? any help would be appreciated. Thanks ...

Problems with restoring a database from a backup on local computer

sql,sql-server,sql-server-2008-r2
I downloaded backup of the base from the winhost server and I try to make restore on the local computer. I use Microsoft SQL Server Management Studio (SQL Server 2008 R2). As much as I try, nothing comes out. Please help (I'm actually in the database are not strong). So,...