Questions tagged [sql-server]

122989 questions
1

votes
0

answer
24

Views

SSIS - Write a log in existing package

I would like to include a log to check the DB source in existing package to track DB source details in different environments. This is existing package and I am totally new to SSIS. I am not sure how to implement in SSIS package. Is there any built in functionality available in SSIS 2008? It would...
Ask_SO
1

votes
2

answer
523

Views

running SSIS package as SQL agent job failed

I created SSIS an project, which exports some data to Excel. In Visual Studio 2017 it works fine, but when I run it using a SQL Server Agent job (MS SQL Server 2017), it throws this error: 'Executed as user: Administrator. Microsoft (R) SQL Server Execute Package Utility Version 13.0.1601.5 for 32...
WhiteB1
1

votes
1

answer
483

Views

Searching 13 million records using full text search with additional conditions

Performance issue while doing SQL Server full text search with additional conditions. (SQL Server 2012) I am trying to filter the data based on search filters list (table value parameter), which will return all the records for match filters and single record for the filter doesn't have any record fr...
K.Engineer
1

votes
1

answer
28

Views

How to get MS SQL Stored Procedure return value In PHP with PDO?

Simple example CREATE PROCEDURE [dbo].[InsertLoginInPHP] @login as nvarchar(50), @password as nvarchar(50), @resultOK as int, @resultnot as int AS BEGIN SET @resultOK = 10 SET @resultnot = 1 IF EXISTS (SELECT * FROM Users WHERE Login = @login) BEGIN SELECT @resultnot as return // 1 END ELSE BEGIN IN...
Afqan Haciyev
1

votes
1

answer
471

Views

create cte multiple times and drop cte [closed]

I am using a cte in stored procedure. I have to use it multiple times ie populate cte for different ids how can I drop or remove current cte thanks ;WITH PAYOUT_CTE(REGNKEY, REGNTPE) AS ( SELECT REG_KEY, 'Parent' FROM ML_MSTR_REGN A (NOLOCK) WHERE A.COMP_NO = @COMP_NO AND A.REG_KEY = @CUR_...
GL SOFT INDIA
1

votes
0

answer
62

Views

SSIS: Sequence container counts Warnings as Errors

I'm debugging an SSIS package that I've just developed. I've found the package failed because a sequence container is Red though all the tasks on it are green. I checked the process results and found there were 4 Warinigs (=exclamations) and no fatal errors. I want to have the container ignore Warni...
yobioo
1

votes
1

answer
39

Views

Vehicle Fitment Data Merge Overlapping Years

I have a large data set of vehicle fitment information for products, each on their own row. I am struggling to create a query to select only the minimum and maximum years for each overlapping entry. For example, I have data such as: fromyear toyear makename modelname submodelname wheelba...
user9147574
1

votes
1

answer
64

Views

SQL Query Execution time , SQL Server, Nested Query

I have a query as following: SELECT Brand,Id,Model FROM product Which takes time in order of seconds as Product table has more than 1 million records. But the query executes within no time. (less than even one second)) select count(*) as numberOfRows from (SELECT Brand,Id,Model FROM product) result...
Sahil Kumar Kansal
1

votes
1

answer
45

Views

I need to pull unique patients that meet certain criteria

I pulled person_nbrs that have never had an EventType1 before or after an EventType2. I need to pull person_nbrs that have not had an EventType1 prior to having an EventType2. If they had an EventType1 after an EventType2, than it is to be ignored. Here is my query that pulls person_nbrs that have...
zdsnide
1

votes
2

answer
222

Views

SQL adjacency list query

Trying to model dependencies on a service that is accessed by users. I have created a parent/child adjacency list style table that sets out that 'the service' relies on Component 1, which relies on Component 2, etc, etc. and the type of dependency e.g. full or partial This image shows the layout -...
Mo R
1

votes
3

answer
36

Views

SQL - Get Records within Date Range using 2 tables (Date Range/Hours given)

I have 2 tables: Table 1: Name, StartDate, EndDate Ex: Timmy, 9/12/17 08:00:00, 9/13/17 00:00:00 Timmy, 9/13/17 05:00:00, 9/13/17 07:00:00 Table 2: Name, StartHour, Data... Ex: Timmy, 9/13/17 06:00:00, Data1... Timmy, 9/13/17 04:00:00, Data2... Timmy, 9/13/17 07:00:00, Data3.....
Future44
1

votes
2

answer
139

Views

Inner join with three tables with different columns

I have three tables. Table 1 and Table 2, I can join with User name and Table 3, I can join Table 1's ip column. Here is the sample Sql Script I written. SELECT R.[First Time], L.[USER], R1.[Destination Port] , L1.[IP Address] AS 'L1 IP Address',L2.[IP Address] AS 'L2 IP Address' FROM [SAPA_SOCTem...
Jzl
1

votes
2

answer
74

Views

SSIS SCD Wizard with multiple incoming records

PlEASE FORGIVE. I am very desperately looking how to ask this eloquently. Not doing to good here. In SSIS, if you have an incoming dataset that has multiple records for the same PK (like intraday records in a daily processing method), how do I tell the SCD Wizard to expect that? in my case, even th...
arcee123
1

votes
0

answer
429

Views

Sync NoSQL with SQL

Is there a way to sync NoSQL and SQL databases? My problem is: We have a software that uses MSSQL. We also have a mobile application that uses MongoDB. We want to sync data (on create/update) between those databases. Mostly from MongoDB to MSSQL. It is not a problem for us (if we have to) to use di...
italomarca
1

votes
1

answer
334

Views

Snapshot agent not starting SQL Server error 15404 error code 0x54b

I have run into this issue and I am stuck with it for hours. I have 2 databases one which is publisher and distributor and second which is subscriber I am going to use them as snapshot replication. Installation went without issues but when I look in Replication monitor publication status is ok but a...
Macieyerk
1

votes
1

answer
507

Views

Combine ASP.NET MVC and Web API projects using identity

I am new to ASP.NET and I have to create an ASP.NET MVC project using the identity framework for authentication and authorization, but the access to the database (SQL Server) must be done in an independent web service project so I created a Web API project with also identity containing all services...
sam
1

votes
1

answer
383

Views

Writing contents of XML Variable to File

The code below creates me an XML file that shows up as a link as shown in image1 DECLARE @xmlentire AS XML = ( select (select @xmlheader as returnXml), (select @xmlbody as returnXml) for xml path ('IndirectSalesMessage')) select @xmlentire SELECT @loop = @loop + 1 the contents that are returned to m...
jp3nyc
1

votes
0

answer
46

Views

Neither recordset or SP approach to validating salted hashbyte fields working

UPDATE: ASP page still always returns a '1', even when correct password is supplied, but when stored proc is run in SSMS, it works properly. I added SELECT statements for the fields being tested and modified the running of the stored proc to return a recordset when executing the command. Set rs2 =...
Jan Paul
0

votes
1

answer
11

Views

Why does `NOT IN` cte not work but `NOT IN` subquery works?

I'm trying to solve the Trips and Users SQL Leetcode problem. If I'm reading this correctly, NOT IN cte does not work but NOT IN subquery does work. Why? The following code works. select request_at as Day, cast(sum(iif(status like 'cancelled%', 1.0, 0.0))/count(status) as decimal(4,2)) as [Cancell...
vivian
-3

votes
0

answer
14

Views

How to log output result of SQL scalar valued function?

SQL DB is used as back end DB in part of PHP web application. Is there a way to write to some table result that function sends to front end? I have to use function (instead of sp) because of some limitation in front end framework. I made SP to write all data but if I try to execute it from function...
hoggar
0

votes
0

answer
12

Views

Full Text Search Using Multiple Partial Words

I have a sql server database that has medical descriptions in it. I've created a full text index on it, but I'm still figuring out how this works. The easiest example to give is if there is a description of Hypertensive heart disease Now they would like to be able to type hyp hea as a search term a...
Jhorra
0

votes
0

answer
4

Views

Printing Dynamic Sql with Parameter Values

I want to print the SQL Query which is used in the below stored procedure with parameter values.Currently it is printing the SQL Query without parameter values substitution while executing the stored procedure.It is printing the the parameters as it is(for example AND TP.F_LANGUAGE = @LANGUAGE) CRE...
Ram
1

votes
1

answer
50

Views

LINQ to SQL query succeeds or fails based on using foreign key versus using a built in navigation property

I have a query against a SQL server database that throws an Error converting data type nvarchar to numeric. exception. I was trying to use Convert.ToDecimal on a varchar field, however I'll eyeballed the data as best I could and couldn't find an invalid value. The query is filtering the table by a...
Terry
1

votes
0

answer
31

Views

SQL Server deadlock condition not being reported back to client

We recently converted an app from OLEDB to ODBC for security reasons. As of the conversion, however, a deadlock condition easily observed in SQL Trace is not being reported back to the client. I observe the call to SQLExecute returning NO_DATA instead. There is an insert trigger involved in the t...
BobTheBuilder
1

votes
0

answer
1.5k

Views

Setup URL redirection in SSRS without using IIS

We're using SSRS 2016 & SQL SERVER 2016 in our environment and don't want to use IIS. We've a requirement to re-direct report manager URL to Target URL (Similar to URL rewrite function of IIS). For example: http://ssrs01/pages/redirect.aspx?redirectnewurl When someone access above URL they should be...
Vino
1

votes
2

answer
258

Views

Query is very slow when we put a where clause on the total selected data by query

I am running a query which is selecting data on the basis of joins between 6-7 tables. When I execute the query it is taking 3-4 seconds to complete. But when I put a where clause on the fetched data it's taking more than one minute to execute. My query is fetching large amounts of data so I can't w...
Kshitij
1

votes
1

answer
53

Views

Transaction Level Delayed Durability

We are looking to deploy Transaction Level delayed durability to our databases. BEGIN TRANSACTION ----- COMMIT with (DELAYED DURABILITY = ON) Many of our client SQL servers are 2014 so should be fine but we still have some clients using 2010 - problem is we deploy the same code to all clients If i...
andyc209
1

votes
2

answer
43

Views

Group value via range and value sql query

I have two table . Table1 have following fields. From To id ---- ---- ---- 0 0 1 1 5 2 5 10 3 10 15 4 Table 2: Table 1 ID Value --------- ------- 1 10 2 10 3 15 4 10 current output...
ila
1

votes
1

answer
73

Views

Calculating quantity of an item in each transaction in another table

I am working in a company in stock department Consider two table of Export and Stock which Export have following attributes: Export{ ExportDate Date not null, StockID int not null, Quantity decimal(10, 2), }; and Stock table has the following attributes: Stock{ ItemID int primary key, ItemName nvarc...
Ibrahim
1

votes
2

answer
94

Views

Calculate total experience in SQL in terms of months - SQL Server

My table is like: id | prev_experience_in_month | current_company_experience 1 | 13 | 1.4 2 | 15 | .5 3 | 20 | 3.0 current_company_experience is calculated using the following formula, in the form year.mont...
Anu Antony
1

votes
0

answer
45

Views

How to catch raiserror from tigger in yii2

I'm use yii2 to catch raiserror in my sqlserver This my controller : try{ $model->save(); }catch(Exception $e){ print $e->getMessage(); } But the result is allways General SQL Server error: Check messages from the SQL Server [3903] (severity 16) [(null)] This my sql code in my trigger : BEGIN if exi...
Andhika Trickster
1

votes
0

answer
34

Views

Can upgrading Windows Server R2 Standard to Windows Server R2 Enterprise affect configurations of installed SQL Server 2008 Management Studio?

I try searching anywhere in other sites and I can't find a good answer to my question maybe I just search here and find the right answer. In my company that I employed with, we use Windows Server R2 Standard and it was live and functional. Our team wants to upgrade to Windows R2 Enterprise My quest...
1

votes
0

answer
34

Views

Error Msg 208 while merge multiple rows in a single record

A query in SQL Server fetches around 30 records with 15 columns. I want to merge it and convert it into one record with 30*15 = 450 columns in a single records. Here is the sample code: DECLARE @SqlText NVARCHAR(MAX) = 'select * from ((SELECT ''abc'' AS ID, SUBSTRING(col1,0,10) ID_Mark, CASE WH...
Shahin P
1

votes
0

answer
31

Views

Find modified project file by Visual Studio (SSDT) that is not saved yet

We are facing situation when some developers modify the project file (*.sqlproj) for example by adding or removing objects, then they do not save all the files in the Visual Studio and try to commit/push changes using command line. In that case the *.sqlproj file is not saved yet and it is not caugh...
Dmitrij Kultasev
1

votes
1

answer
906

Views

Cannot select target in SQL Server Schema Compare, Visual Studio 2017

I'm having an odd problem wich I haven't been able to find a solution for. Suddenly I am not able to select a Target or Source in Schema Compare in VS2017. When I click the Combobox nothing happens. It is still possible to do a Data Compare and SQL Queries. I've updated SSDT to version 15.5.1 but w...
jhe
1

votes
0

answer
47

Views

Using SUM and Count function

I would like to group sum and count according to months and my query as below SELECT terminal_name, sum(case when month(sales_timestamp) = 1 then nc_deposit else 0 end) as jan_amount, count(case when month(sales_timestamp) = 1 then nc_deposit else 0 end) as jan_count FROM dbfastshosted.dbo.fh_mf_...
nur wahidah
1

votes
0

answer
112

Views

SSIS package failed to execute ssrs report from schedule job

i have a problem with ssis. when I run it manually, the process runs smoothly and its reports are formed. but when I run it with a schedule job in the sql server, the job is successful but the report is not formed. in the task script settings (task to generate ssrs) I have set to save the credential...
irfan kusuma
1

votes
1

answer
237

Views

WSUS SQL Query - Sort by ArrivalDate

I was given a SQL script to run by our Global IT group to generate the monthly WSUS reports they want. However, it returns everything in WSUS, not just the past months information. I am looking for assistance with modifying the script I was given to filter out all of the previous dates. This report...
Stephen Parker
1

votes
0

answer
83

Views

CLR Assembly in SQL Server Error “Timeout Exceeded” when executed in stored procedure, runs fine in new query window

I have this stored procedure, we'll call 'wrapper', that calls another procedure to build an XML message then executes a CLR Assembly to send that XML message to a web service. The web service should then return a message saying success or failure. The issue is, the assembly is exceeding its timeout...
John Donahue
1

votes
1

answer
78

Views

When inputting data into my T-SQL table from C#, I get an error

I am creating a register like program using C# and a local SQL Server database. I have managed to create a query that inputs the data into the table called UserAdd. Code: ALTER PROCEDURE UserAdd @Name VARCHAR(30), @Organisation VARCHAR(20), @DOB DATETIME, @Contact VARCHAR(50), @Address VARCHAR(250...
WMTS

View additional questions