FAQ Database Discussion Community


giving username sql server 2012 management studio

sql-server
I am installing SQL Server 2012 and I have selected to install Default instance. and authentication type is mixed but I don't want to add default username as "as" , I want give different username. Is is Possible?? Give me suggestion how to achieve it...

SQL Server 2012 & Polybase - 'Hadoop Connectivity' configuration option missing

sql-server,hadoop,sql-server-2012
As described in the title, I am using SQL Server 2012 Parallel Data Warehouse with Polybase feature to try to access a HDInisght Hadoop cluster. As a starting point for every connection to Hadoop from SQL Server, I find to execute the command sp_configure @configname = 'hadoop connectivity', @configvalue =...

Get items with 0 Counts after GroupBy in SQL

sql,sql-server
I want to get the total count of records for past 5 days, based on a boolean "IsInsert". The below query neglects all the days with zero Total. SELECT CAST(V.ChangeDate AS DATE) AS ChangeDate, COUNT(*) AS Total FROM CarrierRate.VendorBillUVBLog V WITH(NOLOCK) WHERE V.IsInsert = 1 And v.ChangeDate >= DATEADD(d,-4,GETDATE()) AND...

Syntax Error, Operator Expected

sql-server,prolog
I am trying to query the MS SQL Server database using Prolog. Prolog Code lemmas:- odbc_query('my_db', 'SELECT * ,case when ActualCost<EstimatedCost then 'true' else 'false' end as Value from Work_Order ' ). It gives an error that operator is expected after 'true' . Note: The Query works in SQL-Server but...

What is the execute command to use here

sql,sql-server,sql-server-2008,tsql
protected void Page_Load(object sender, EventArgs e) { string sqlConnectionString = @"Data Source=phriz-webapp01;Initial Catalog=PFTracking;Integrated Security=True"; string script = "if not exists(select * from sys.servers where name=N'CNCTC-WEB01')begin exec sp_addlinkedserver @server='CNCTC-WEB01'exec sp_addlinkedsrvlogin 'CNCTC-WEB01','false',null,'svc_Phils',[email protected]' end INSERT INTO [PFTracking].[dbo].[TempTable] SELECT c.[pf_id],a.[RequestDate],c.[pf_carrierUsed],b.[PiecePrice] * b.[PartQuantity] as [Amount]...

Convert row to columns SQL dynamically [duplicate]

sql,sql-server,sql-server-2008,tsql
This question already has an answer here: Pivot Dynamic Columns, no Aggregation 1 answer Simple way to transpose columns and rows in Sql? 2 answers I have a table Dev with the data below YYYMMDD Atest BTest CTest 20150525 100 200 300 20150526 110 210 310 20150527 120 220...

SQL Subquery column equals operation

sql,sql-server,tsql
I am trying to do a SQL query for user with certain permission enabled flag. I know, I can do this: select u.ID, u.Name, (select p.Value from Permissions p where p.UserID = u.ID AND p.Key = 'CanEdit') as IsPermissionEnabled from Users u But it's not exactly what I need, can...

INSERT INTO fails due to incorrect conversion T-SQL

sql-server,tsql
My table looks like this: CREATE TABLE MyTable ( TableID INT IDENTITY NOT NULL, ForeignID INT NOT NULL, Value sql_variant NOT NULL, CodeOne VARCHAR(4) NOT NULL, CodeTwo VARCHAR(4) NOT NULL ) I'm trying to do a insert with the following code: INSERT INTO MyTable(ForeignID, Value, CodeOne, CodeTwo) VALUES ( 1,...

Dynamic Query Generation is not working with SQL

sql,sql-server
I am trying to execute following SQL statement : USE[Northwind] declare @MainQuery varchar(255), @WhereClause varchar(255) Select @WhereClause = 'where CustomerId =ALFKI' Select @MainQuery ='Select * from Customers [email protected] execute(@MainQuery) But getting the error , Msg 207, Level 16, State 1, Line 1 Invalid column name 'ALFKI'. What is wrong ?...

ONLY display certain rows from an inner joined table using a certain colum as a parameter from one of the inner joined tables

sql-server,join
I have two tables, one with various details such as Username, email id, user code and another table with columns which are: UserCode, supervisor email id, active_flag I required an output of a table which only displays the active users i.e. the users with active_flag (Y or N) column as...

SQL Server - notify 1 month from time (SQL Agent)

sql,sql-server
im trying to right an SQL Query that will do the following task: Example: SellDate = '2014-07-15' ExpirationDate = '2015-07-15' TodaysDate = '2015-06-15' --notify me by email An item is sold, (SellDate) and is set to expire in 12 months (ExpirationDate)... 1 month before, I want SQL Server to email...

Fastest way to add a grouping column which divides the result per 4 rows

sql,sql-server,tsql,sql-server-2012
If i have a resultset like this for example (just a list of numbers) : 1,2,3,4,5,6,7,8,9,10,11 and I would like to add a grouping column so i can group them per 4 like this : 1,1,1,1,2,2,2,2,3,3,3 (The last one in this examle does not have a forth element, so that...

Improving SQL Queries

php,mysql,sql-server
I have recently designed a website that used a lot of queries. During the time I was developing my website I came across an issues which was very time consuming and frustrating. So the problem was that at a certain point I wanted to add an additional feature to the...

SQL Server / C# : Filter for System.Date - results only entries at 00:00:00

c#,asp.net,sql-server,date,gridview-sorting
I have a connected SQL Server database in Visual Studio and am displaying its content in a grid. I created a dropdown menu with the column names as selectable options and a text field to filter for specific content, e.g., DropDown = "Start" - Textfield = 14.03.2015 = Filter Column...

Default the year based on month value

sql,sql-server
I have a query to display the year value from a table and default the current year. select distinct year(startdate) as syear, year(startdate) as eyear, 1 as sOrder from [TTS].[dbo].[Class] where year(startdate) <> year(getdate()) union all select year(getdate()) as syear, year(getdate()) as eyear, 0 as sOrder order by sOrder asc,...

How do you configure SQL Session State in ASP.NET 5.0 (vNext) MVC 6? [closed]

sql-server,configuration,session-state,asp.net-5,asp.net-mvc-6
In Visual Studio 2015 RC, ASP.NET 5 vNext, i am trying to use SQL Session State in MVC 6. I'm not sure how to go about it. Can anyone show me a sample or give me some tips? I'm just trying out Azure Load Balancer. I set up a load...

Select count Columns group by No

sql,sql-server
Emp_No Emp_Shift Emp_Date 500 AL 1/5/2015 600 S 2/5/2015 600 H 3/5/2015 500 S 4/5/2015 500 AL 5/5/2015 600 AL 6/5/2015 I need help on this issue , HOW TO RETURN count in 3 Columns >> EX: Emp_No Count Al Count S Count H 500 2 1 0 600 1...

Sort a LINQ with another LINQ in MVC

sql-server,linq,entity-framework,controller
Using SQL Server Management Using MVC VS 2013 for Web Being in a Controller Here materialnumb it's a LINQ query that always return only one value. Being the following... var materialnumb = (from r in db.MaterialNumber where r.MaterialNumber == 80254842 select r.MaterialNumber); I have another LINQ query from a...

How do I do this monthly calculation taking into account the correct number of months in SQL?

sql,sql-server,datediff
DECLARE @InputPeriodStart DATE = '1/1/2014' DECLARE @InputPeriodEnd DATE = '12/31/2014' ROUND(CONVERT(DECIMAL, DATEDIFF(dd, @InputPeriodStart, @InputPeriodEnd)) / 30, 1) AS DECIMAL(18, 2)) The issue here is that not every month has 30 days in it. So how can I make this calculation work properly? I would need to remove the ROUND() and...

Take thousand value in SQL

sql,sql-server
I have values like below I need to take only the thousand value in sql. 38,635.123 90,232.89 123,456.47888 I need to take result as below. 635 232 456...

Implement reference key in SQL Server

sql-server,sql-server-2008
I have a TestPack table that can have n no of Lines associated with it. But a Line may exist without a TestPack. Later on, a Line might be assigned a TestPack#. What this relationship is called and how do I implement this relationship in SQL Server 2008?...

Sql injected code is inserted to my database . How to remove it

sql-server
One of my sql table is injected with some html code. It is inserted such that the html tags are inserted after actual data. How to remove this from my table.

Problems With FOR XML AUTO

sql,asp.net,sql-server,subquery,sqlxml
i have the following Query: SELECT QuestionID_PK , QuestionTitle , ( SELECT dbo.Tags.TagID_PK , dbo.Tags.TagTitle , dbo.Tags.TagTitle_EN FROM dbo.Question_Tag_Rel INNER JOIN dbo.Questions ON dbo.Question_Tag_Rel.QuestionID_FK = dbo.Questions.QuestionID_PK INNER JOIN dbo.Tags ON dbo.Question_Tag_Rel.TagID_FK = dbo.Tags.TagID_PK AND dbo.Questions.QuestionID_PK = '2116' FOR XML PATH('') , TYPE , ELEMENTS ) AS Tags FROM Questions WHERE...

SQL Server: Issues with data type

sql,sql-server,toad
SELECT pa.[type], (SUM(pa.Actions_Logged)/SUM(pi.Impressions_Served)) AS ActionRates from Performance_Actions pa INNER JOIN Performance_Impressions pi ON pa.Alternative = Pi.Alternative GROUP BY pa.[type]; The above query generates an error: Lookup Error - SQL Server Database Error: Arithmetic overflow error converting expression to data type int. Both pa.Actions_Logged and pi.Impressions_Served are int and the result...

Group Count in T/SQL

sql,sql-server,sql-server-2012
Source: CREATE TABLE #TempTab (Value INT, Value1 varchar(10), Value2 varchar(10), GRP varchar(10)) INSERT INTO #TempTab SELECT 1,'One','One','One' UNION ALL SELECT 1,'One','One','One' UNION ALL sELECT 1,'One','One','Two' UNION ALL SELECT 2,'One','One','One' UNION ALL SELECT 2,'One','One','Two' UNION ALL SELECT 2,'One','One','Three' UNION ALL SELECT 3,'One','One','One' UNION ALL SELECT 3,'One','One','One' Current query effort: SELECT Value,...

Combine two subqueries to main query in SQL

sql-server
I am new to SQL and need assistance to combine (JOIN) two subqueries with a main query in SQL. Here is what I have. *Each query works independent of another. The end result would be that I would retrieve the # of accommodations for each resort, retrieve the lowest cost...

Newbie to linking SQL tables

sql,sql-server
I'm new to SQL, so forgive me if I am understanding this incorrectly. I have one table named Employees, where I have columns for: EmployeeID - This is Int Identity (1,1) FirstName LastName I have a second table named Calendar, with the calendar information populated for the next 50 years....

MS SQL JDBC connection with MS SQL Server 2012

sql-server,jdbc
I use to use MYSQL for my database needs. I switched to MSSQL because somebody wanted me to develop an application on it. I used MS SQL Server 2008 earlier and then upgraded to MS SQL Server 2012. In JDBC, I used the following: String connectionUrl="jdbc:sqlserver://"+sname+";DatabaseName="+dbname; where sname is my...

Get unique row by single column where duplicates exist

sql,sql-server
I have the following DB table called messages with columns: thread_id, sender_id, receiver_id, date Example: +-----------+------------+-------------+-------------+ | thread_id | sender_id | receiver_id | date | +----------------------+-----------+-----------------+ | 1 | 1 | 2 | 11/06/2015 | | 1 | 2 | 1 | 14/06/2015 | | 1 | 1 | 2...

Setting time limit in SQL Query

sql-server
I want a query to make which return 1 when the time is in between of 10:00 pm - 5:00 pm. Else it should return 0. Select * from table ...

Join SQL query Results and Get-ChildItem Results

sql-server,sql-server-2008,powershell
Background: I have a directory with a number of files that are imported to SQL server. Task: Creating a PowerShell script which will pick up files within this directory and use the filenames as in the SQL query. Ultimate objective: To display SQL results besides the filenames but the resultset...

Title search in SQL With replacement of noice words [on hold]

sql,sql-server,sql-server-2008
I have Two tables first us IMG_detail S.NO Title 1 women holding stack of gifts 2 Rear view of a man playing golf 3 Women holding gifts 4 Close-up of a golf ball on a tee 5 Businessman reading a newspaper and smiling and Second is tbl_NoiceWords SN Key 1...

How to incorporate wildcards with place holder variables in Java using SQL

java,sql,sql-server
Hello the following block of code represents one of the options a user can put in to perform a query search. As is, it successfully returns when the formation name is completely spelled out. However, I am trying to make it work for partial entries as well. if (!Strings.isNullOrEmpty(searchParameters.get("formation"))) {...

Purging Database - Count purged/not-purged tables

mysql,sql,sql-server,database,stored-procedures
So I am working with a database where I will be purging various tables that contain rows that are older than 30days. I have fairly limited knowledge of SQL and wanted to know if there was a certain way to select the row that will be purged and the rows...

TSQL - Error in stored procedure due to conversion failure

sql-server,sql-server-2008,tsql
I have this situation in a stored Procedure: SET @DATE_RELEASE_START = '2015-01-01'; SET @DATE_RELEASE_END = '2015-05-31' SELECT @statement = ' SELECT * FROM (SELECT AFCDENTE, M.ID_MODIFICATION_CODE, COUNT(*) AS Conteggio--, CAST((COUNT(*) * 100/ 15032) AS decimal(10,7)) AS Percentage FROM CIC_LOG_MODIFICHE AS L INNER JOIN ADM_MODIFICATION_CODE AS M ON L.CD_MODIFICATION_CODE = M.CD_MODIFICATION_CODE...

Connecting to database using Windows Athentication

sql-server,vb.net,authentication,connection-string
I would like to use window authentication in my program to connect to my sql server. users already have certain permissions on the SQL server and I would like to leverage that in my program. The way I currently connect to the server is using this connection string. Dim ConnectionString...

Optimize the execution of select

sql-server,indexing,query-optimization,clustered-index,non-clustered-index
I want optimize this select: Select Dane1, Dane5, Dane6, Dane7 FROM Test INNER JOIN Test2 ON Test.Id=Test2.IdTest WHERE Dane5 > 199850 My database has 2 tables test, test2: test design: Id int ->PRIMARY KEY, Dane1 int, Dane2 int, Dane3 int, Dane4 int, Dane5 int, test2 design: Id int ->PRIMARY KEY,...

How to select next row after select in SQL Server?

sql,sql-server
I have this issue, I need your help: EmpNo Shift_Date Shift1 shift2 stamp_Date stamp_Time stamp_Type 426 2015-04-12 A 2015-04-12 10:09:00.000 I 426 2015-04-15 B C 2015-04-15 23:46:00.000 I 426 2015-04-15 B C 2015-04-15 23:45:00.000 O 426 2015-04-16 OF 2015-04-16 07:02:00.000 O 426 2015-04-17 A 2015-04-17 07:34:00.000 I 426 2015-04-18 A...

How to select from a table with two columns but one of them is null

sql,sql-server
I have stuck at my SELECT statement. I am looking for an Item by code and definition. I have a select statement like below... select * from t_items where code ='#itemcode#' OR definition like '%#definition#%' the user can select the #itemcode# from a dropdown menu on the web screen, or...

Can someone explain to me how this statement is an exclude?

sql,sql-server,tsql
Can someone tell me how this is an exclude? Assuming that tableID is auto generated and in columnY there can be value of 0 or 1. This statement should exclude everything were columnY has a value of 1. SELECT * FROM [table].[dbo].[one] AS t1 LEFT JOIN [table].[dbo].[one] AS t2 ON...

More precision needed from SQL Server Money data type

sql,sql-server,asp-classic,sqldatatypes,money
I am working on this old SQL Server database which store numeric values in MONEY datatype. This has been good for years, now for some currency rate conversions we need up to 10 decimal places. We are exploring the possible conversion from MONEY datatype to DECIMAL. I see that a...

Not getting the correct SUM values in SQL Server 2012 when using a PIVOT

sql-server,sql-server-2012,pivot,distinct,aggregate-functions
I am trying to create a query that will pivot over some rows but will SUM some columns and then group them together. I've used the PIVOT function before but I am running into issues when my result set contains similar values. This is SQL Server 2012. Sample code: CREATE...

DELETE … OUTPUT COUNT(DELETED.*)

sql,sql-server,output-clause
I want to know how many rows were removed in a certain DELETE operation. I took the Microsoft example B which is DELETE Sales.ShoppingCartItem OUTPUT DELETED.* WHERE ShoppingCartID = 20621; and tried to modify it to return only the count of deleted records: DELETE FROM datacache OUTPUT COUNT(DELETED.*) WHERE [email protected]..

ASP.NET EF6 - 2 Tables & 1 Link Table

c#,sql-server,asp.net-mvc,entity-framework
I am not the most fluent with ASP.NET/EF, so I am having a bit of a hard time trying to find the best way to accomplish what I need to get done. I'm trying to create a CRUD with an already existing database. Instead of the Employees table having specific...

Update SQL Server table using XML data

sql-server,xml
From my ASP.Net application I am generating XML and pass it as input data to stored procedure as below, <Aprroval> <Approve> <is_nb_approved>false</is_nb_approved> <is_approved>true</is_approved> <is_submitted>true</is_submitted> <UserId>35</UserId> <ClientId>405</ClientId> <taskDate>2015-05-23T00:00:00</taskDate> </Approve> <Approve> <is_nb_approved>false</is_nb_approved> <is_approved>true</is_approved>...

Speeding up a SQL query with indexes

sql-server,database,performance
I have a table called Products. This table contains over 3 million entries. Every day there are approximately 5000 new entries. which only happens during the night in 2 minutes. But this table gets queried every night maybe over 20 000 times with this query. SELECT Price FROM Products WHERE...

Foreign key in C#

c#,sql,sql-server,database
I have two tables, A and B, in a dataset in SQL Server; I have created a connection to the dataset in a c# project in visual studio. How can I create a foreign key ( A is the parent) between my two tables ? I want to create the...

Latest two datetime in SQL Server

sql,sql-server,datetime
I have following table CREATE TABLE [dbo].[events] ( [event_id] [nvarchar](50) NULL, [event_timestamp] [datetime] NULL, [reading] [int] NULL ) ON [PRIMARY] with the following data and resutls Table: events event_id event_timestamp Reading --------------------------------- 1200 14-Jun-15 1500 1200 13-Jun-15 1430.7 1200 12-Jun-15 1400.9 1200 11-Jun-15 1333.2 1200 10-Jun-15 1233.7 1330 14-Jun-15 1490...

Merging two tables into new table by ID and date

sql,sql-server,phpmyadmin
Let' say I have two tables: table 1: col a | col b | col c 1 | 62215 | 21 1 | 62015 | 22 2 | 62215 | 23 2 | 51315 | 24 and table 2: col a | col b| col f 1 | 62015| z...

SQL Customized search with special characters

sql,sql-server,sql-server-2008
I am creating a key-wording module where I want to search data using the comma separated words.And the search is categorized into comma , and minus -. I know a relational database engine is designed from the principle that a cell holds a single value and obeying to this rule...

pivot not working in SSIS

sql,sql-server,ssis
My SQL query works fine when i execute it in SQL management studio but throw the error below when execute in SSIS package. Any advise? **When click on Build Query of the syntax - it shows The PIVOT SQL construct or statement is not supported. Case 1: SELECT listid, [1]...

SQL Server: checkident: “[S00014][2560] Parameter 3 is incorrect for this DBCC statement.”

sql-server,database
Using an SQL Server database. A lookup table is having 50 new items added to it using a database versioning tool so the same script will be run on all three databases. There is a Dev, QA, and Prod version of the database and because of political reasons prod can't...

Adding a padding 0 to a SQL column

sql,sql-server
With SQL Server 2014, I have a nvarchar(10) column with values like this 9998 03345 05567 6675 I need to add a left padded "0" to all the cells that are 4 digits long so that all values are 5 digits. How do I write an update that will take...

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...

Error connecting to MSSQL using PHP

php,sql-server,pdo,odbc,sqlsrv
I am receiving an error as below: PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[08001]: [Microsoft][ODBC Driver 11 for SQL Server]SQL Server Network Interfaces: Connection string is not valid [87]. ' My codes are as follow: $this->link = new PDO( "sqlsrv:server=$this->serverName:$this->port;Database=$this->db", "$this->uid", "$this->pwd" ); I wish someone can enlighten...

How to Retrieve value from a UDT

sql,sql-server
Goal: I want to reach and display the value of @data inside if [wwtest]. Problem: The UDT is from the database [test] and I retrieve an error message "The type name 'test.dbo.tvf_id' contains more than the maximum number of prefixes. The maximum is 1.". I don't know how to retrieve...

TSQL update value with subquery

sql-server,tsql,sql-update,compare
I have 2 tables and want to compare them and modify tableA (set NameMod = 1) if it has different rows. To compare tables I use: select Id, Name from tableB except select Id, Name from tableA And then I want to modify tableA: update tableA Set NameMod = 1...

Microsoft SQL Insert into subset of table columns fails [on hold]

sql-server,sql-server-2008
Can anyone help me find the column or columns in this table where data would be truncated? I am trying to execute the SQL insert below, but it is failing with the error: String or binary data would be truncated. Which I understand means a value is too big to...

Get work time in minutes based on shift schedule

sql,sql-server,sql-server-2008,ms-access,user-defined-functions
In production we have 3 shifts. Each Shift timing is described in table tbl_ShiftSched: WT - work time, PT - break time. ShiftTmID - schedule for 2 and for 3 shifts. I am looking for easy way to get work time in minutes having start and end time. For example,...

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?...

How do I check whether the input date's DAY is the last day of the given month in SQL?

sql,sql-server,datepart
DECLARE @Date DATE = '2/28/2014' I need to test whether the DAY in the DATE above is the LAST day of the month and it should work for all months in the year. If it's true, then I need to return a true, if not, then false. E.g. SET @Date...

Order by clause in update query in sql server [on hold]

sql-server
I need to update table using order by clause in sql server 2008 e.g Update tblTempChek Set TmpCheckIn='15:50:03' Where TempID IN ( Select TempID From tblTempChek Where convert(date, TmpDate)='2015-06-23' AND UserID='1' Order By TempID Desc ) but this gives error Msg 1033, Level 15, State 1, Line 3 The ORDER...

Select @field From table as parameter

asp.net,sql-server,parameter-passing
I have a database table with some columns like "col_a", "col_b" and I want to retrieve just one column of it. But the column is depended on some user selection. Some times could be the col_a, the col_b, c .... So my question is if I can select a field...

Conversion failed when converting date and/or time from character string in sql server

sql,sql-server
I have a function to convert a string to datetime (101) format. But it gives me an error when I convert this value. 2016-03-01 00:00:00.0000000 And the error is Msg 241, Level 16, State 1, Line 2 Conversion failed when converting date and/or time from character string. And my function...

Retrieving an XML node value with TSQL?

sql-server,xml,tsql,soap
What am I not getting here? I can't get any return except NULL... DECLARE @xml xml SELECT @xml = '<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <SOAP-ENV:Body> <webregdataResponse> <result>0</result> <regData /> <errorFlag>99</errorFlag> <errorResult>Not Processed</errorResult> </webregdataResponse> </SOAP-ENV:Body>...

Create a new column in my SQL View - Column must be two other columns combined

sql,sql-server,tsql,concatenation
I am trying to create a new view, but I also want this view to have a new column. The new column must be StkCode and Description_1 combined. For example: If my StkCode is DX11122 and Description_1 is Vanilla Powder; I want a field that will read as follows: "Vanilla...

SQL Multiple LIKE Statements

sql,sql-server,tsql,variables,like
I'm currently working on a report that shows me all post codes covered by our sales team. Each team covers over 100 post codes. What i would like to do is create a report that brings back the clients within the post code. Currently my code looks like this. SELECT...

Get Parent and grand parents of a particular child

sql-server,sql-server-2005,sql-cte
i have table like below tablename :ExampleTable ChildID ChildCommonID ParentID 1 2 0 2 3 0 3 4 1 4 5 3 5 6 4 The Problem is : i have a child id example :ChildID= 5 so i need to check wheather it has a parent or not if...

SQL Store queried data to one table (either temporary or not)

c#,sql-server
I am querying a data from two different servers and now I want to store it on another table so that I can use it as my reference table in my program.. (I am using ASP.NET in programming) Have a look on my command and please advise what to do....

Improving work with SQL DataTime

sql,sql-server,database,tsql
I have a business in my Date. I need change date below for each date before 25 of each month I want get 25 of month before and for each date after 25 of each month I want get 25 of the same month. Example : for 2015/06/24 (or 2015/06/06)...

Recursive Lag Column Calculation in SQL

sql,sql-server,recursion
I am trying to write a procedure that inserts calculated table data into another table. The problem I have is that I need each row's calculated column to be influenced by the result of the previous row's calculated column. I tried to lag the calculation itself but this does not...

want to generate coupon code 5 digit number [duplicate]

sql-server,sqlite
This question already has an answer here: TSQL Generate 5 character length string, all digits [0-9] that doesn't already exist in database 6 answers i want to create a coupon code generator by using SQL database but i don't know how to generate 1000 of random number without repeating...

How can I use IF statement in sql stored-procedure

sql,sql-server,stored-procedures
I want to transform an SQL Query to a procedure in SQL server, but I have an issue in the IF statement. This is the logic I want to implement: //If the patientStatus equals "all" there will be no condition patientStatus.ToString() == "all" ? "" : ( patientStatus.ToString() == "out"...

how to make xml values comma seperated using XPath, XQuery in Sql Server

sql-server,xml,xpath,xquery
I have an xml column with sample values as <error> <errorno>BL04002055</errorno> <description>Smart Rule PROJECT_COUNTRYCODE_VAL Violated</description> <description2>Country Code is required</description2> <correction /> </error> <error> <errorno>BL01001973</errorno> <description /> <description2>Error While Saving the Project info</description2> <correction /> </error> <error> <errorno>Unable to Create Custom...

SQL Recursive Query - Sum Rows with Lesser Rank

sql-server,rank
Using MS SQL - 2012 I am trying to run a SQL Query that ranks rows by product id's. Then calculates a sum that represents the rows ranked less than or equal to it. I set up a formula to do this and it works but is slow and resource...

Update where column has highest value

sql,sql-server
I'm trying to write a simple "undo"-system for a table with the following structure: id element position start_date end_date 1 1 23 01/01/2015 05/01/2015 2 2 36 01/01/2015 NULL 3 1 17 05/01/2015 NULL So, when a new action is added for an existing element, it adds a new row...

Using SqlQuery in Entity Framework

sql-server,entity-framework
Is this really true that order of sql parameters in sql stored procedure must match the order of sql parameters used in SqlSquery: var Parameter = new List<SqlParameter>(); Parameter.Add(new SqlParameter("@ParamOne", 1)); Parameter.Add(new SqlParameter("@ParamTwo", 2)); Parameter.Add(new SqlParameter("@ParamThree", 3)); context.Database.SqlQuery<myEntityType>("exec sp_Stored_Procedure @ParamOne, @ParamTwo, @ParamThree", Parameter.ToArray()).ToList<myEntityType>(); And in case sql developer decides to...

SQL Server - Substitute for OR when JOINing on non-indexed field

sql-server,join,query-performance
Background: One of our clients has a system where they have recordings of phone conversations between their agents and their customers, who they create various contracts with. The recordings are stored on a server with their locations saved in the Recordings table in the database. The agents can then "attach"...

SQL Server Query Performance with Timestamp and variable

sql-server,performance,timestamp
I have a simple SQL query to count the number of telemetry records by clients within the last 24 hours. With an index on TimeStamp, the following query runs in less than 1 seconds for about 10k rows select MachineName,count(Message) from Telemetry where TimeStamp between DATEADD(HOUR,-24, getutcdate()) and getutcdate() group...

Retrieve data from one table and insert into another table

sql,asp.net,sql-server
I am trying to retrieve data from one table and then insert it into another table. This is a a sample of the first table in which there is the following data. tb1 is the table which consists of data. The two columns Manager and TeamLeader basically means for example...

copy table and drop it

sql,sql-server,sql-server-2008,tsql,stored-procedures
I know we can simply create a new table and copy the old table by doing select * into tbl2 from tbl1 i would like to check if table tbl2 exists, if it does then copy all the rows, if it doesn't then create a new one without having to...

Concatenated string as parameter argument value does not give output in stored procedure

sql-server,sql-server-2008
I am using SQL SERVER 2008 management studio. I have a stored procedure which accepts several parameters. One parameter name is @recvemail with default value as 'B'. If the value of @recvemail is 'Y' then all the users who opted for email news letter should be displayed in the output....

How do I convert this tSQL statement to LINQ using group by in a sub query

c#,sql-server,linq,tsql
I have the following MSSQL query I am trying to convert to LINQ. I am using entity framework with the following syntax to get at the data. var rv = (from i in DC.TableA select i).ToList(); This is the sql I want to write a C# LINQ query for but...

No column name was specified for column 1 of 'tbl'

sql-server
When am executing the below query in sql server 208 r2 I am getting error message No column name was specified for column 1 of 'tbl'. SELECT rollno, classid, t_class.classname FROM t_class LEFT JOIN(SELECT Count(classname), classname FROM t_class GROUP BY groupname HAVING Count(classname) > 1)tbl ON tbl.classname = t_class.classname ...

How to insert excel formula to cell in Report Builder 3.0

sql-server,excel,reporting-services,excel-formula,ssrs-2008-r2
There is RDL report template for SQL Server Reporting Services. I need to set value for cell in table in the report template which must be calculated from other values in the report. When the report is exported to Excel file I need to see the Excel formula in that...

How to find all customers who didn't place an order?

sql,sql-server
I am new to stackoverflow, I recently went through an interview as graduate developer, and I was asked a question regarding SQL. Given two table customers and orders, how to find all cusomers who didn't place an order. Can someone tell me what would be a query to achieve this?...

Multiple column values into a single column as comma separated value

sql,sql-server
I have a table CommentsTable with columns like, CommentA, CommentB, CommentC, CommentD, CommentE. All comments columns are VARCHAR (200), by default all columns are NULL also. The data looks like: CommentId CommentA CommentB CommentC CommentD CommentE --------------------------------------------------------------------- 12345 NULL C 001 C 002 NULL C 003 45678 C 005 NULL...

LINQ to Entities Retrieving related Entities or T-SQL

c#,sql-server,linq
I have these (simplified) models: class Vendor { public int Id { get; set; } public IEnumerable<Product> Products { get; set; } public IEnumerable<Booth> Booths { get; set; } } class Show { public int Id { get; set; } public IEnumerable<Booth> Booths { get; set; } } class Booth...

converting varchar to Int SQL SERVER

sql,sql-server,casting
I needed help with something I am not entirely sure how to resolve. This is my code : SELECT DISTINCT Nr_of_Times_Cust_No_Appears=CASE WHEN CAST(a.TV_Code AS Int)-CAST(BB_Code AS Int)=0 THEN COUNT(*) OVER (PARTITION BY BB_Code) ELSE 'Not same' END FROM table Basically, the above code is meant to make sure that the...

Combine multiple rows using SUM that share a same column value but has different other column values

sql,sql-server
I thought this would be a very simple query but for some reason, I can't seem to get the results I'm looking for. I have a table that has this structure. I just want a single entry for each account while summing the charges. I don't really care which date...

Executing dynamically created SQL Query and storing the Query results as a temporary table

sql,sql-server
I am creating a SQL Query dynamically. After it's been created I want to execute it and store it as a temporary table. WITH [VALIDACCOUNTS] AS( EXEC (@sqlQuery)) ...

The column name “FirstName” specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument

sql,sql-server,sql-server-2008
I am getting the following error message when I am trying to do replace null to zero. The column name "jan" specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument. Query below: select * from(select isnull(jan,0),isnull(feb,0),sum(data) as amount )as p pivot( sum(amount) for month...

left join table, find both null and match value

sql,sql-server,join
I have two 2 table t1(years int, numOfppl int), t2(years int, numOfppl int). t1 contains years between 2001 and 2010 , t2 contains years between 2003-2005 and 2007-2010 I want to query a result like that t1.years t1.numOfppl t2.yeras t2.numOfppl 2001 7 null null 2002 6 null null 2003 4...

How to use OFFSET and Fetch without Order by in SQL Server

sql-server,sql-server-2012,sql-order-by,fetch,offset
I want use OFFSET and Fetch in my SQL server 2012 query.But without any order by.I can not use order by.Because my sort order will be lost. How can I use OFFSET and Fetch without order by and row number and where in my query? My 2 select tables have...

How to Implement Dependent Dropdownlist in MVC4 Razor and using SQL server also

sql-server,asp.net-mvc-4,razor
I've 2 Dropdownlist called States and Country. but only the States DDL(DropdownList) will be enable the country DDL will be diable. when I select a option in States which is already stored in SQL. The Country DDL Should Show the Selected state's Country automatically from DB. Please help Me..... Ex:If...

how can I update a list of records in a table with the new value depending on the old value

sql-server,sql-update
I have a table called acc1152 with the field accno. depending on what the value of this field is, i need to replace it with a new value. these are the values i need to update old value new value 7007 4007 7008 4008 4008 7 7009 4009 7011 4011...

Looping distinct values from one table through another without a join

sql,sql-server,tsql,while-loop
I know looping is not ideal in SQL, but I couldn't think of another way of doing this. I want each distinct row from this Table 1 to have each distinct date and hour produced on Table 2. In other words, Table 2 has the dates between 05/01/2014 through 04/30/2015,...

Adding values of different sizes in SQL

sql,sql-server
Good day everyone, I am hoping someone could help me with an issue I am having. I have a database that stores the amount of data that was reviewed on a hard drive. The values can either be Megabytes or Gigabytes. Example: Hard Drive 1 100MB Hard Drive 2 2.5...

SQL Server Stored procedure declaring variable in different way

sql-server,tsql,stored-procedures
I'm just started using MSSQL and need to understand. While I was reading few sample code that I might use in near future found CREATE PROCEDURE [dbo].[SP_Set_LMS_BY_ERW] @UPA nvarchar(30) = NULL Question is what's the difference between @UPA above Declare @count nvarchar(30)? Thank you...

Cannot Browse in sql to backup bak file

sql-server
As the title says I cannot browse and cannot see backup folder. is there any other way to restore bak file ? or how can I fix this ? ...