Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [sql-server]

All versions of Microsoft SQL Server (not MySQL). Please also add a version-specific tag like sql-server-2016 if that is relevant to the question.

4
votes
2answers
113 views

Datetime conversion fails when filtered index is placed on partition column

I've got a mysterious error of char data conversion at the moment of insertion a new row into a table. Troubleshooting reveals that a filtered index is the source of the problem. The index's filter ...
0
votes
0answers
7 views

Always Encrypted returning Timeout error

I am trying to encrypt a number of tables (1 column in multiple tables) in two different databases. On Database A I manage to encrypt these, while on database B it fails with an error: 'Unable to ...
1
vote
2answers
64 views

SQL Server mistaken update rows without backup

I did a mistake UPDATE query yesterday. Database engine is MSSQL and the version is 2017. I have not any backups. All I have is the database itself. Do you know any ways to redo my modifications to ...
1
vote
0answers
17 views

MSSQL: Optimize multiple JOINs with large data

I have 3 tables with this structure: +-----------+ +-----------+ +-----------+ | Files | | Programs | | Channels | |-----------| |-----------| |-----------| | ID | | ID ...
3
votes
2answers
3k views

how to check who has killed the job which was running ?if logs are not generated

Unspecified error occurred on SQL Server. Connection may have been terminated by the server. This is error I am getting.(when the job is executed) [SQLSTATE HY000] (Error 0) Cannot continue the ...
0
votes
1answer
58 views

Quorum Drive needed?

Do I need a quorum drive for configuring Always On on SQL Server 2014? I am sure it is not mandatory as the 'last man standing' concept is based on node and disk majority. But I need a little more ...
0
votes
1answer
43 views

SQL Server Execute Permission Denied

I have a small app which calls a stored procedure over ODBC using a sysadmin user with login and password. The problem is that some (Windows Domain) users get an error saying execute permissions were ...
3
votes
3answers
390 views

Difference Between a Staging and the Production DWH

I have a general question on a Data Warehouse Architecture. I have heard a lot of a Staging and a Production, so that data should be loaded via SSIS first into a staging dwh and then via SSIS to the ...
6
votes
1answer
828 views

Error starting SQL Server 2017 service. Error Code 3417

I have SQL Server 2017 installed on my computer. This is what SELECT @@VERSION returns: Microsoft SQL Server 2017 (RTM-GDR) (KB4293803) - 14.0.2002.14 (X64) Jul 21 2018 07:47:45 Copyright (C) ...
1
vote
1answer
652 views

Most efficient way to aggregate data to new table

Say I have a large table A around 151 million rows: TableA [Id] [bigint] IDENTITY(1,1) NOT NULL, [MetaDataProp1] [int] NULL, [MetaDataProp2] [int] NULL, [MetaDataProp3] [int] NULL, [MetaDataProp4] [...
1
vote
5answers
799 views

Document database structure for auditors

I am DBA for a Microsoft SQL Server 2008r2. Due to an audition, I need to provide data structure of my database, to people without an explicit IT background. I guess they would like the traditional ...
0
votes
0answers
40 views

Can we find who killed a SQL Server process? [duplicate]

I am using SQL Server 2012 Enterprise Edition. We are running SSIS package which usually take more than 5 hours to complete execution. Some time we find that the transaction is being killed by someone ...
11
votes
8answers
3k views

Identity Columns or UDF that explicitly generates a unique id?

I am in middle of a debate about whether it is better to make a PRIMARY KEY out of an Identity Columns, our out of a UDF that explicitly generates a unique id. I am arguing for the Identity Column. ...
0
votes
0answers
40 views

Kimball Data Warehouse: Denormalize Hierarchy Attributes

I have many different flat files. We have Property Owners, who own Properties, which are under a Tax Assessment (Tag Code). Property Owners flat file does not have SSN/Id, so sometimes same property ...
1
vote
2answers
56 views

Empty closing XML tag instead of self-closing tag

My query is USE ... SELECT @JNo=jour_no from WB_PMS..tblJournal WHERE jour_scode=@Jcode IF EXISTS (SELECT 1 FROM WB_PMS..tblArticle WHERE jour_no=@JNo AND art_no=@ArtNo) BEGIN SET @XML= ...
1
vote
2answers
221 views

SQL Server: Pass table name into table valued function as a parameter

All of my tables have those two columns: [Id] [uniqueidentifier] NOT NULL [Revision] [bigint] IDENTITY(1, 1) NOT NULL Every other time I write a query, I want to eliminate some of the rows. The ...
5
votes
1answer
205 views

Pre-staging data causes execution plan cost to skyrocket

I have a troublesome query that we are trying to tune. One of our first thoughts was to take a portion of a larger execution plan and store those results to an intermediate temp table and then ...
0
votes
2answers
56 views

Restoring more than one .bak files in a TSQL query

Can any one share a script that will allow me to restore all the .bak files in my one folder into SQL Server using a TSQL script?
0
votes
2answers
275 views

Determine when subscription became inactive in transactional replication

When I try to send a tracer token on some of my publications to get the latency I get the following error - No active subscriptions were found. The publication must have active subscriptions in ...
4
votes
3answers
75k views

Update table using values from another table in SQL Server

I have 2 table in my database. Table #1 ------------------------------------------------------------------------- | name | family | phone | email | gender | phone2 | address | birthdate | -----------...
2
votes
0answers
46 views

Can't import flat file into SqlServer

I've got this tab-delimited flat file with fifty columns and two million rows. I am trying to import it into Sql Server 2017 Express. (I first tried working with it in Excel... hopeless.) I see two ...
6
votes
2answers
80 views

Fastest way to split/store a long string for charindex function

I have a 1 TB string of digits. Given a 12-character sequence of digits I want to get the start-position of this sequence in the original string (charindex function). I have tested this with a 1GB ...
1
vote
1answer
35 views

Should SSIS packages and SQL database be on same server?

I'm a bit new to SSIS. I have a lot of 'dev', building, query, tuning performance on SQL Server but am not the master of admin/ overall performance modeling. So here's my question --- Should SSIS ...
1
vote
1answer
50 views

Privileges on SQL Server

I come from Oracle Database and now I am trying learn SQL Server and I don't undestand why may I viewer others databases, like the master for example, with a common user without privileges in this ...
-3
votes
0answers
24 views

How to write a query for ATM transactions

Mr. Ali and Mr. Adnan singed a deal and by which Mr. Ali has to pay 50,000 to Mr. Adnan and he wanted to transfer by ATM machine, you are required to write a procedure for ATM machine which make a ...
3
votes
1answer
155 views

Can low memory cause Table Spool operators in plan

I was trying to optimize a query and found that the plan had table spool(eager) in it. But when I ran it on another server which had much more memory(256GB compared to 64GB) than the other one the ...
0
votes
1answer
32 views

How To get value of column when it exist in content value of column by sql server 2016

I want To get value of column when it exist in content value of column by sql server 2016 For Example: Table1: Title1 ---- Jack Benz Volvoo Table2: Description Names ---- ...
1
vote
1answer
148 views

Inserting values into a column when Table1 column A value matches Table 2 Column B Value

I have two tables ImportData and PurchaseInfo. I am trying to import [Supplier Contact Name] from ImportData to PurchaseInfo, when *[SupplierName] in ImportData = *[SupplierName] in PurchaseInfo This ...
0
votes
0answers
27 views

A transport-level error has occurred when receiving results from the server. Error 10054

I have been using SQL Server & Azure for almost two years with Zero problems. Now for some reason I am getting the following error when I try to connect to my Azure database from SSMS. I have ...
0
votes
1answer
152 views

Restore a database backup on another server without sysadmin account

I need to restore my own database backup on another server. On both servers I have the same user account name with limited privileges (eg WebUser). On the target server I can't use a sysadmin ...
47
votes
3answers
27k views

Performance difference for COALESCE versus ISNULL?

I've seen a lot of people use the COALESCE function in place of ISNULL. From internet searches, I've found that COALESCE is ANSI standard, so there is an advantage that we know what to expect when ...
-1
votes
0answers
23 views

Ola Hallengren backup Linux default location [on hold]

The documentation for Ola Hallengren's backup scripts states that a @Directory of NULL will use the default backup location. I keep getting this error under SQL 2017 running on RHEL Linux: The ...
2
votes
1answer
147 views

How to combine a parent with all children in the same row

SQL Statement Question: I have two tables PARENT and STUDENTS. PARENT Table ParentID | FName | LName | StudentID ---------+-------+-------+---------- 1 | John | Doe | 11 1 | ...
0
votes
2answers
148 views

Transaction Log file size for Index reorg

I have a DB that is 100GB. It's fixed this way on purpose. It won't exceed this. I have two indices. One with a single value. One with three values. I'm not a DBA. I'm a guy who's been tasked with ...
0
votes
1answer
147 views

how to install mssql on to ubuntu

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Can't open lib '/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.2.so.0.1' : file not found. pugazhl@pugazhl-LIFEBOOK-LH532:~$ # sqlcmd -S ...
1
vote
1answer
41 views

AlwaysOn Cluster

I'm currently setting up AlwaysOn SQL Server cluster. We have 3 DR sites: London, Amsterdam, and Amazon, with London and Amsterdam synchronous, and Amazon asynchronous. I have a couple of questions: ...
1
vote
1answer
1k views

Powershell to dump database schema to text file

I want to be able to dump a database schema to a sql text file using Powershell. I want this because our DBAs want to be able to do a diff on the file that is committed to SVN. They won't execute it ...
3
votes
1answer
8k views

Permanent count of waiting tasks (2) in Activity monitor

Now I'm observing in Activity Monitor permanent count of waiting tasks: and at "Resource waits" tab I see next: so, my question is: How to find and resolve this wait tasks? I suspect that it ...
0
votes
1answer
153 views

Backup is getting failed with following error

Msg 3202, Level 16, State 1, Line 1 Write on "\w-mum-fp\SQLBKP\Weekly-FL\Interactions_26_01_2014.bak" failed: 1130 (Not enough server storage is available to process this command.) Msg 3013, Level ...
1
vote
1answer
72 views

How to clear temp database content in SQL Server?

As the title implies, I am going to clear the content of my temp database in SQL Server. SolarWinds is generating a critical alert and I want to clear the tempdb workfiles and worktables. How can I ...
0
votes
1answer
43 views

Different manifestations of the same logic yield very different execution times [on hold]

Assume that the widgets_population table records every widget which has rolled off the assembly line, while the widgets_tested table is the subset that have been tested. For every widget in the ...
1
vote
1answer
106 views

SQL Server - redirect synonym without downtime

I have multiple application databases that use synonyms point to a shared database with immutable data that needs to be updated periodically by restoring an external backup. We now have a new ...
45
votes
12answers
27k views

Why aren't primary key / foreign key matches used for joins?

As far as I could find out many DBMSs (e.g. mysql, postgres, mssql) use fk and pk combinations only to constrain changes to data, but they are rarely natively used to automatically select columns to ...
1
vote
1answer
165 views

Restore Read Only FileGroups from File

I have a database DB in simple recovery mode with filegroups PRIMARY, A, B, and C. Each filegroup has a single file, PRIMARY.MDF, A.NDF, B.NDF, and C.NDF, respectively. First, I run: ALTER DATABASE [...
1
vote
1answer
145 views

when can AG primary replica be a different host to WFC primary?

To set up an Availability Group with two servers, the servers both participate in a Windows Failover Cluster. I've seen cases after certain failover modes where the primary IP address in the cluster (...
0
votes
1answer
189 views

Cannot start sql server in antergos linux: Unit mssql-server.service has a bad unit file setting

I can no longer start sql-server on antergos linux. This is what I tried so far (following this link): $ sudo systemctl status mssql-server.service ● mssql-server.service Loaded: bad-setting (...
1
vote
1answer
146 views

Avoiding a full re-initialization when making changes to transactional replication

When I use the GUI to make any changes to transactional replication on SQL server, I live in fear of the "the changes you have made require a reinitialization of all articles" dialog. I have had ...
1
vote
1answer
53 views

Clarifications on Transparent Data Encryption

This is the first time we are applying TDE on production databases that are critical and I have the questions below. While TDE is in progress: Can we still access the application that is working on ...
0
votes
1answer
49 views

Where clause in an inner join in SQL Server

I have the below query SELECT b.CreateDate, * FROM ConsolidatedData_DealSheet AS a INNER JOIN Deals AS b ON a.DealNo = b.DealNo Result Set 2007-11-13 15:40:15.627 ...
1
vote
1answer
26 views

SSAS Build in TFS

How do I build an SSAS Cube Project in TFS? For 1) DatabaseProjects: it is MSBuild 2) SSIS: Devenv.exe with SSIS Project directory How would I build in Team Foundation Server 2015 for SSAS cube? ...