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.

Filter by
Sorted by
Tagged with
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 ...
Marcello Miorelli's user avatar
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 ...
Henry Lee's user avatar
  • 1,246
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 ...
PaulM's user avatar
  • 95
-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 ...
igelr's user avatar
  • 2,132
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: ...
Joel's user avatar
  • 19
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 ...
igelr's user avatar
  • 2,132
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 ...
Vijaya's user avatar
  • 71
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 ...
Rob's user avatar
  • 33
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 ...
mathewb's user avatar
  • 1,144
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?
igelr's user avatar
  • 2,132
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 ...
ganders's user avatar
  • 143
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!
igelr's user avatar
  • 2,132
-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 ...
ivGeo's user avatar
  • 67
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 ...
Hiral's user avatar
  • 109
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 ...
igelr's user avatar
  • 2,132
-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 ...
Kevin's user avatar
  • 533
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 ...
Seb's user avatar
  • 161
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 ...
Moritz Both's user avatar
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 ...
IT researcher's user avatar
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 "...
m.t.bennett's user avatar
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 ...
Husam Ebish's user avatar
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 ...
ivGeo's user avatar
  • 67
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 ...
Husam Ebish's user avatar
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 ...
JonDBA5420's user avatar
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 ...
Arsalan's user avatar
  • 23
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 ...
Tomasz's user avatar
  • 186
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 ...
user avatar
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....
chris dorn's user avatar
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, ...
Mazhar's user avatar
  • 793
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): ...
pacreely's user avatar
  • 1,098
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 ...
sql_girl's user avatar
  • 345
-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 ...
KZD's user avatar
  • 61
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 ...
Kashinath's user avatar
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 ...
SQLequal's user avatar
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--&...
user avatar
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 ...
Elaskanator's user avatar
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 ...
VA systems engineer's user avatar
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 ...
LAffair's user avatar
  • 247
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,...
Amirhossein Yari's user avatar
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)...
Suresh's user avatar
  • 19
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)...
Kirby Richter's user avatar
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]...
Zach Smith's user avatar
  • 2,360
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?
Manish Kumar's user avatar
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 ...
Willem's user avatar
  • 66
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, ...
Yaroslav's user avatar
  • 2,826
-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 ...
Sayantani Nath's user avatar
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 ...
igelr's user avatar
  • 2,132
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......
Surya Adhikari's user avatar
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 ...
igelr's user avatar
  • 2,132
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. ...
NOOK's user avatar
  • 23

1
247 248
249
250 251
682