Questions tagged [sql-server]
All versions of Microsoft SQL Server (not MySQL). Please also add a version-specific tag, like sql-server-2016, since it is often relevant to the question.
34,098
questions
3
votes
3
answers
2k
views
How to get the definition of a database trigger?
when I run the following script in one of my databases:
SELECT * FROM SYS.triggers
when I try to find out about sys.triggers
Contains a row for each object that is a trigger, with a type of TR ...
2
votes
1
answer
895
views
Database Mirroring failover with CNAME
I have SERVERA and SERVERB configured for Database Mirroring starting with A as Principal and B as Mirror. I created a CNAME for SERVERA and use that in my connection string. I am not using the ...
0
votes
1
answer
402
views
What would cause a transactional replication subscription to restart repeatedly with no errors?
I have set up a transactional replication publication/subscription to test a migration from an on-premise SQL 2016 Enterprise Edition installation to Azure SQL DB and I'm finding that when the ...
-1
votes
3
answers
633
views
admin: prefix before the servername while connecting to DB Engine Query in MS SQL Server
I am using SSMS to connect to DB Engine Query for DAC. I've seen some video tutorials where people use 'admin:' prefix before server name while connecting
When trying to connect with that prefix I ...
1
vote
0
answers
20k
views
AlwaysOn Error: The target database is participating in an availability group and is currently not accessible for queries
We have a 2 Node AlwaysOn Availability Group in SQL Server 2014 running on Hyper-V (VM).
Current setup:
SRV01: Role: PRIMARY READABLE SECONDARY: NO
SRV02: Role: SECONDARY READABLE SECONDARY: ...
2
votes
1
answer
179
views
Access is denied for SQLDumper.exe in MS SQL Server
I want to run SQLDumper.exe with process ID 6392 which is the ID of my Express edition. While running I get this error from the OS
access is denied
I've given full control to the SQL Express account ...
2
votes
1
answer
198
views
Vulnerability in Server from SQL Server 2008 R2
We scanned our systems and got a high vulnerability reported in a server.
One of the thing that got flagged was MS12-060, which is related to MS Office 2003 Web Components that gets installed along ...
3
votes
1
answer
8k
views
Generate Data Only Script without Identity SQL 2017
I am using the script generator in SQL 2017 to create an INSERT script.
When generated, it includes the Identity which I don't want.
I have set Script Foreign Keys and Script Primary Keys to false ...
2
votes
1
answer
284
views
Why does join syntax with stacked on clauses work? [duplicate]
I have three versions of the same select query (SQL Fiddle here):
CREATE TABLE MainTable(ID INT)
CREATE TABLE SubTable(ID INT, Type CHAR(2))
CREATE TABLE SubTableType(Type CHAR(2))
INSERT MainTable ...
1
vote
2
answers
116
views
Global only scoped trace flags in MS SQL Server
If the scope of the trace flag is global only does it mean I can't use it only for one connection while not affecting the performance of others?
3
votes
1
answer
3k
views
How to determine index needed to remove hash match from exec plan
Sql Server Database on Azure (Sql Server 2014/12.0.2000.8)
I have numerous stored procedures that all had the same basic select properties and joins, so I decided to generate a view that encompassed ...
0
votes
1
answer
120
views
What's the purpose of the cmd column in the sp_who stored procedure result set?
When executing sp_who I get this
I want to know what's the purpose of cmd column. What parameter does it represent?
Thanks for your time!
-1
votes
1
answer
1k
views
Maintenance Job not running but not Failing [closed]
I set up a maintenance plan that contains three sub-plans. One sub-plan for hourly log backups, one sub-plan for daily maintenance, one for weekly maintenance. These are custom plans set up using ...
10
votes
2
answers
37k
views
SQL Server Error while creating User
I tried creating a user with all privileges but I am getting this error:
Microsoft SQL Server Management Studio
Create failed for User 'Hru'. (Microsoft.SqlServer.Smo)
Additional ...
0
votes
2
answers
322
views
What does it mean that sp_who2 is not documented?
I am reading a book about SQL Server administration where I encountered this sentence
sp_who2 is undocumented stored procedure which gives you more verbose information about each processes (in ...
-1
votes
2
answers
902
views
TSQL Query to Remove Dates in Multiple Formats from Text Values [closed]
I have a table with list of text values that contain dates in any format types.
I need a TSQL query to remove the dates from each row in the table.
The date samples are below. What is the best way ...
5
votes
3
answers
13k
views
Login lacks connect endpoint permission in SQL Server - Error 18456, State: 149
I have a fresh database I've setup, which I can connect to and perform operations like normal via the SQL Server Management Studio. I have two accounts I created for my application to use, and both ...
5
votes
2
answers
189
views
How to teach the optimizer to use index instead of fts with join on data logging master/detail tables?
SQL Server 2012 optimizer does not get it right.
Test case, summary:
This is a simplified test scenario. DDL statements at the bottom.
I have two tables for data logging, A and B. There is a 1:n ...
0
votes
0
answers
518
views
Make the linked server ask for password
Till today i am using the linked server with the user of sql having same username and password in both the servers. For now on i can not keep the 2 servers password same for some reason and i still ...
0
votes
1
answer
209
views
Microsoft Key Storage Provider for Always Encrypted Master Key
Hoping someone can help-
I've been experimenting with Always Encrypted columns and have generated a certificate in the "Microsoft Software Key Storage Provider" -
How can I remove the "...
1
vote
4
answers
8k
views
Transfer data from Excel to table
How can I transfer information from an Excel file to a table in a SQL server database through SSMS?
Excel file Consists of five columns and a thousand rows.
I followed the steps as explained here,at ...
0
votes
1
answer
791
views
SSIS Transfer Login Task Error
I have created an SSIS Transfer Logins Task. I have selected the SourceConnection and DestinationConnection and both valid and active.
I have selected certain Windows group accounts to transfer ...
0
votes
1
answer
454
views
How to have folders in the SSMS 2017 project? [closed]
I am using SSMS 2017 and SQL server 2017, I have many companies and each company has many databases.
For example. I have five companies:
Company1, Company2, Company3, Company4, Company5
Every ...
1
vote
1
answer
435
views
Create DDL Trigger Dynamically
I'm trying to create a DDL Trigger dynamically so I can create a job that checks each database and creates the trigger if it's not already in place. To do this, I'm using sp_foreachdb. The code works ...
1
vote
1
answer
65
views
Dead Raid Card SQL Server Running in Memory
Wanted some opinions, on a predicament I’m currently facing. I just got thrown with this mess..
SQL Server database files saving to Raid volume, long story short raid card died so volume is not ...
3
votes
1
answer
387
views
Get notified when user with administrative privileges logs in
I have less than a handful of users that have sysadmin role membership. I would like to be notified (emailed) when one of them logs in. Is creating a trigger the best way or is there some kind of ...
3
votes
1
answer
704
views
SQL Log Shipping Read Only/Standby Different users
I have a primary OLTP server on Server A and Log Shipping in Standby/Readonly mode on Server B.
Log Shipping Server is refreshed every 4 hours, and utilized for BI Reporting Analysis, to reduce load ...
1
vote
2
answers
1k
views
INSERT Statement error message references column I am not including in query
I am using MSSQL 2008.
My INSERT statement is as follows:
INSERT INTO [DB1].[dbo].[SpotArtSong](ID)
SELECT SNG.S_ID
FROM [DB1].[dbo].[SpotArtSong] SPOT
INNER JOIN [DB2].[dbo].[nt-art] ART
ON ART....
3
votes
1
answer
2k
views
Updating statistics, what about existing execution plans
If you update the statistics on tables that have never been updated (don't ask why they never have), does SQL Server automatically refresh the execution plans based on the outdated statistics? If not, ...
4
votes
1
answer
261
views
Group together similar queries in sys.dm_exec_sql_text
I'm analyzing a 2008 Instance that supports a 3rd party app.
The app will generate the SQL Code then send it to the Database as an ad-hoc query.
I'm using this query (based on a Glenn Berry script):
...
1
vote
2
answers
13k
views
SSRS report ERROR "Could not load folder contents Something went wrong. Please try again later. "
One of our report servers is raising errors when trying to reach it from clients computers and works just fine from the actual server.
the error is:
I found that it was a bug in sql 2016 using this ...
-1
votes
1
answer
354
views
SQL Server Intellectual Property (Protecting DB Structure and Relations)
I would like to know is there any update about protecting DB Structure?
We deliver a licensed app that uses SQL as its main DBMS and we would like to lock the database structure (schema).
I know I ...
4
votes
1
answer
4k
views
Querying special character from SQL Server
When I saved special characters in a column of type nvarchar, I did not use the prefix 'N', so the characters got saved as ??.
Is there any way to retrieve the original special characters from these ...
1
vote
0
answers
115
views
Tracking log block movement using Extended Events
If we try to track the log block movement between 2 sync commit replicas, why do we need to track the immediate next Log block ID in extended events like hadr_send_harden_lsn_message on secondary ...
0
votes
1
answer
864
views
Email SSMS Log Shipping Status Report
We use Log Shipping to allow Business Analysis users to query data. I would like to email the Log Shipping Status report to users to see last time data is refreshed.
When I Right Click on Server--&...
0
votes
1
answer
393
views
How to find the erroneous code failing the SSDT publish?
Every time something fails the publish, assuming it passes the preview generation, the error line and executed script sample are completely useless at helping me trace the source of the error, since ...
0
votes
1
answer
2k
views
Why does SQL Server Profiler crash when its run against a DB machine from a client machine?
I have:
SQL Server 2014 (default instance )is on a Win Server 2012 machine.
SQL Server Management Studio (v17.7) is on a Win 7 machine.
Both machines are on the same domain.
I'm logged into the Win 7 ...
7
votes
1
answer
1k
views
T-SQL trigger to update columns
I've been working with SQL queries for a couple of weeks and I'm trying to learn and understand from my mistakes. I have a table with two columns IsInvisible (bit) and ShortName (string) and when one ...
3
votes
1
answer
3k
views
Begin transaction doesn't lock table
I have a table with this structure:
CREATE TABLE [dbo].[a](
[id] [INT] IDENTITY(1,1) NOT NULL,
[aa] [INT] NULL,
CONSTRAINT [PK_a] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF,...
1
vote
0
answers
149
views
Associate multiple tables with a single table
I have a RDBMS database and I need to associate a column in a new table to multiple other existing tables whose schema can't be changed.
The new table looks something like this,
UUID(pk) PartId(pk)...
2
votes
2
answers
437
views
Optionally exclude certain partitions using OLA's scripts
I have been using OLA's scripts for a long time and love them.
I have a very large database - 20+ TB with hundreds of partitioned tables.
I purge old data by dropping (merging the old partitions)...
0
votes
1
answer
308
views
Is it good practice to use non standard schema names in SQL Server? [duplicate]
If I have a database MyDB, is it considered good practice / bad practice to use schemas other than dbo?
For example:
MyDB.reports.YearlySales
MyDB.reports.TopSellers
MyDB.[data].ProviderX
MyDB.[data]...
0
votes
1
answer
474
views
Count of open sockets to the database
I want to check open socket connection count to the database.
Is there any way to get the number of open sockets to the database?
2
votes
1
answer
616
views
Replication from Amazon's RDS to Postgres via DMS errors out
I have a transactional replication from SQL Server 2008 to RDS SQL Server 2016 in 2014 compatibility mode. This works okay. I now want to replicate this RDS database via DMS to Postgresql. This errors ...
0
votes
2
answers
455
views
Automatically generate database creation script with default values for SIZE and FILEGROWTH
By using a mix of several PoSH scripts I managed to get a working script that generates, copies and remotely executes a script to create the basic structure for a given database with all its objects, ...
-1
votes
1
answer
68
views
Analysis of queries or stored procedures which ran on the database for last 15 days [duplicate]
We had a site slowdown between 11.57 am and 12:02 pm on 6th Aug 2018,
To trace back the queries which were ran our production database (SQL Server Standard Edition 2012) and which might have caused a ...
1
vote
1
answer
2k
views
How to know which instance is default in Configuration Manager if default port number is changed?
In MS SQL Server Configuration Manager I can see all the instances. I recognize the default one (by saying a default instance I mean the one that listens for 1433) by looking at TCP/IP's IP Addresses ...
1
vote
3
answers
1k
views
There is no copy database wizard
I'm trying to copy data from one table to another table in a different database. After right clicking I have got an option Tasks, under it there are only the two options:
Export Data...
Import Data......
2
votes
2
answers
4k
views
How to determine which one is my default instance in MS SQL Server?
I have two instances in my server and I want to know which one of them is the default. Can you, please, point out ways I can recognize it.
By saying a default instance I mean the one that listens for ...
2
votes
0
answers
505
views
Best practice for shut down and restart of Replicated SQL Servers
We have SQL Server 2008 R2 and SQL Server 2012 in production. We have transactional and snapshot replication implemented in our environment and unfortunately the subscribers are also the distributors. ...