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
0
votes
0answers
2 views

Prevent updating table using procedure in different database

This is my scenario: Windows User Gabe has db_owner access of database DB1. Windows User Gabe has db_reader on database DB2. When the user executes UPDATE DB2.dbo.Tbl1 SET X = 1, they get "The ...
5
votes
1answer
32 views

Hash aggregate bailout

A question that arose in a chat discussion: I know hash join bailout switches internally to a sort of nested loops thing. What does SQL Server do for a hash aggregate bailout (if it can happen at ...
0
votes
0answers
31 views

Login connection data and time

I wonder if there is a way to query SQL Server to find out dates and times a login has connected to a particular server?
5
votes
2answers
48 views

Using SPID in DB Tables (instead of Table Variable)

Transactional database used for booking things... Our vendor was asked to replace #temptables with @tablevariables (because of heavy compile locks) but instead they replaced with an actual table ...
0
votes
3answers
39 views

Must declare the scalar variable question

That is my first post here, need helps: in a stored procedure I had some following code CREATE PROCEDURE [dbo].[SP_getAvg] ( @projectId INT ,@carrierId INT ,@fetchType VARCHAR(20) ) ...
0
votes
0answers
42 views

slow join performance SQL Server

I am struggling a bit to improve performance on one of my queries. What i had is basically all of those 4 tables for reporting without any indexes,pks etc. I was thinking that if i copy them (even on ...
1
vote
2answers
22 views

SQL Server - Update with Output not working in Parallel environment

I know that the output parameter does not guarantees parallelism logic. And the logic I want to implement is failing in a multi thread environment. To test this, just open 2 windows and execute them ...
0
votes
1answer
22 views

Copy Data from Two different databases with FK relation

So the situation is as follow: I have production DB called DB1 which has huge data, around 250 GB. I have Test DB called DB2 on my local machine which has the same schema as the production DB but ...
0
votes
2answers
28 views

SQL Server: Join all tables from a table listing their names [duplicate]

I have a table that contains a list of metrics and their IDs in the format: L6Metrics | ID | Name | -------------------------- | 734 | L6A_Velocity | | 736 | L6B_Velocity | | 738 ...
2
votes
2answers
22 views

Restore DIfferential backup on azure managed instance?

I am migrating my database from on-premise to Azure managed instance. In order to move the database I'm using the organization weekly full backup, and I am asking them to take a diff after they take ...
0
votes
0answers
50 views

SQL Server Query Timeout with NOT IN clause but IN works fine

So were using SQL Server 2016 Enterprise and suddenly have an issue with a query on the Production server. Works fine on Test and Dev and if we backup and restore the production DB to either of ...
1
vote
0answers
29 views

Which permission is required for OBJECT_ID to return non-null value

Firstly, yes, I know I've tagged this 2008, but unfortunately that is a requirement under this particular project I'm working on. This is a rather complex setup, so please bear with me... there are ...
1
vote
1answer
17 views

Does AE certificate include both master and encryption keys together?

After setting up Always Encrypted, I can see a certificate in my Current User when selecting select * from sys.column_master_keys under the key_path I see this location, but there is no location ...
0
votes
2answers
35 views

Get value from one field, and paste it in to a new column

I'm relatively new to Microsoft Access, and just a beginner with SQL programming. I have a table in MS Access. It is an import from a .txt file. In the first column There is a String with a Number or ...
1
vote
1answer
18 views

Facing permission error while trying to execute a stored procedure published through CLR

I've created a stored procedure using SQL CLR c# stored procedure in Visual Studio 2017 and I've published it in MSSQL Server. Now I can see my stored procedure, but I face this error when I want to ...
0
votes
2answers
18 views

Get matching part from conditional pattern

I'm having a table like this: |id|key |value | ------------------------ | 0|name |Homer | | 1|surname|Simpson | | 2|town |Springfield| I'm running this query: SELECT * FROM ...
1
vote
2answers
36 views

How to verify the restored MS SQL DB from Backup matches with the source?

Once you restore the backup, how do you verify that the source and destination are in sync (schema, datatypes, number of records etc)? assuming that the source has not change. Appreciate your insights....
1
vote
1answer
55 views

Run a stored procedure only the last 3 days in a month

How can I run a stored procedure only the last 3 days of a month using a T-SQL script?
0
votes
1answer
54 views

how to retrieve MAX ID from inner join SQL Query?

I am working on a search stored procedure which, the outcome of the procedure retrieving search rows in increments of 60. The values that this stored procedure is receiving is the search value, ...
0
votes
1answer
50 views

SQL Server Agent Job - Unable to open Step output file. The step succeeded

I have seen many posts with "Unable to open Step output file.", but can't find any references where the step succeeded. I have my output file location set to: $(ESCAPE_SQUOTE(SQLLOGDIR))\$(...
1
vote
0answers
50 views

Availability groups multiple instance, multiple listeners

In my work environment, I have two database machines: DB01 DB02 Each database machine has three SQL Server instances: MSSQLSERVER FBG Central I've managed to set up availability groups with SQL ...
2
votes
3answers
239 views

How do I query for system views in a SQL Server database?

I have some queries that run against Query Store, the view sys.query_store_wait_stats is not in SQL Server 2016, but it is in SQL Server 2017. I want my queries to work in SQL Server 2016 and SQL ...
0
votes
1answer
54 views

Mssql query problem. Column '' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

There is a problem I have experienced in mssql. ( but I don't have this problem in mysql. ) They know two different engines. but I could not find a solution. Do you think I can fix this error? My ...
1
vote
1answer
52 views

Inserting few millions of rows: Does indexing happens along with the insert operation

I am inserting 50 million records to an existing table with a primary key , and a non clustered key. My question is whether indexing of those will existing indexes will happen along with the insert ...
1
vote
1answer
25 views

Master Key vs Password Certificate Encryption in SQL Server

We can encrypt certificates in SQL both by the database master key, and the other option is by password. I want to know the advantages and disadvantages of DMK encryption. Does password usage make the ...
2
votes
2answers
59 views

Backup a single table

I need to Truncate a table in Sql server which containing around 250GB data but before truncating i need to take backup of this,it may to csv file or other mode. What is best way to do it.
2
votes
1answer
66 views

How to check if backups are reliable?

I generally have to restore databases to different servers after important data imports or other important and delicate database operations. I need to be sure that my backups up to that point in time ...
-4
votes
0answers
26 views

SQL Server Disk Usage Top Tables Green & Yellow Rows Reporting Screen [on hold]

What is the meaning of the yellow and green rows in this report image from https://ibb.co/L69Knc5?
1
vote
1answer
28 views

Difference in location of transaction isolation level declaration - inside/ouside a transaction?

I have been looking though some stored procedured on our system today when I saw the following structure: (pseudocode indicative of actual SP, server is using the default isolation level): CREATE ...
0
votes
1answer
56 views

SQL don't display ORDER BY column

I'm quite new to SQL programming, so i apologize in Advance for all the irrelevant Information and stupid Questions :) I have a database in Microsoft SQL Server Management Studio. I select data from ...
3
votes
1answer
52 views

Checkpoints on secondary replica AlwaysOn AG

Setup 3 Node Alwayson cluster - 1 sync and 1 async secondary replica - SQL Server 2012 Situation We are witnessing PageIOLatches when reading from the asynchronous secondary replica. This is ...
2
votes
1answer
44 views

When is Service Master Key Exactly generated?

As it is created automatically, on Microsoft official source it says during setup. but in the 70-764 official exam prep book page 18, also there is another non official blog which say Any time ...
0
votes
2answers
35 views

How to select top 3 vendors without duplicate entries?

Here is my tables I want to select Top 3 lowest quoted rate vendors without duplicate Entry. Expected output will be V1, V4, V3 without using while loop. is this possible? Table details CREATE ...
1
vote
0answers
21 views

This failure occured while attempting to connect to the Principle server

One of our clients recently came to us with a problem he's facing while working with our application. The error in the log file is the next: System.Data.SqlClient.SqlException (0x80131904): Timeout ...
0
votes
0answers
26 views

How to reuse index space that was not created

My database is 34Gb in size and I created a clustered index which I believe was not successfully created due to some error and I can't find the index anywhere. During running of creation of Index, i ...
0
votes
1answer
50 views

Lock Escalation happening while inserting few million records into a table in production

I want to insert few million records into a table in my production database. I am not updating any existing rows.I am just doing INSERT INTO TAB1 SELECT COL1,COL2,COL3 FROM TAB2 This table has an ...
0
votes
1answer
24 views

Proc runinng slow once in a while [duplicate]

I am trying to troubleshoot the performance issue on one of the SP which executes throughout the day daily every 20 mins and on avg completes under a min. For past couple of weeks it happens once in ...
2
votes
1answer
61 views

How to send mail from a restricted SQL Server

I have created an DB refresh job on a pre-prod SQL server, with latest successful Prod backup to refresh pre-prod DB every monday morning. However, i want to send a status mail post refresh. Due to ...
2
votes
1answer
39 views

How to use grouping sets in this query that shows database sizes - adding the last time it was updated

I have the following query that shows me the database sizes, including log dna data files. SELECT [Database Name] = DB_NAME(s.database_id), [Type] = CASE WHEN s.Type_Desc = 'ROWS' THEN 'Data ...
2
votes
3answers
56 views

Copy table from one server to another including keys and indexes

I have been working on a small project for about two weeks. We want to consolidate a few tables from several remote server databases in order to run queries and reports on separate from the live ...
12
votes
4answers
861 views

Index Uniqueness Overhead

I've been having an ongoing debate with various developers in my office on the cost of an index, and whether or not uniqueness is beneficial or costly (probably both). The crux of the issue is our ...
-1
votes
0answers
31 views

Stored procedure in my production server causing blocking [on hold]

I have a MS SQL server production server. We have a stored procedure, which is consumed by multiple clients like website and mobile app. We are noticing a steep increase in the blocks with the ...
2
votes
0answers
63 views

Why is my view running extremely slow in comparison to the regular query?

I have a sql server view that has a number of joins in there. These joins are pulling and comparing records of 60,000+ when aggregating all three tables. The problem is the view takes 1.5 hours to run ...
0
votes
0answers
24 views

Restoring database created with Ola's tools from Azure blob

I'm exploring using Ola Hallengren's excellent backup tools to backup to an azure blob. Is there a simple way to do a restore of the latest backups on a fresh MSSQL 2017 database directly from azure ...
1
vote
1answer
48 views

How to change the FROM clause of multiple views at once

I have multiple views that are the sources for a collection of scripts I use for loading data from NAV into our DWH, meaning the view collects fields from multiple tables in our NAV database and the ...
0
votes
2answers
56 views

alwayson read only routing

if i'm change connection string to applicationintent:readonly then i setup read only routing list in my SQL server 2016 , does that mean my application will route all write request to primary and then ...
0
votes
2answers
57 views

Implications of adding a trigger on production database

I am trying to add a trigger on a production database table which includes millions of rows and is used very heavily. There are around 70k-100k inserts per day. When I try to add a trigger to this ...
1
vote
2answers
50 views

Adding a database with Service Broker to an existing Availability Group on a new replica

Server 1 - SQL Server 2016 (Primary in existing AG) Server 2 - SQL Server 2016 (Secondary in existing AG) Server 3 - SQL Server 2016 (Offsite Secondary in existing AG) Server 4 - SQL Server 2017 (New ...
-3
votes
1answer
80 views

Sql Server consumes CPU but no one is using the server

In my server I have a SQL process that consumes CPU (~10%) but no one is using the server. In query select * from master.dbo.sysprocesses order by cpu desc I see at least one suspect process: spid: 9 ...
0
votes
0answers
40 views

how i can solve this problem in sql server management studio 2016 [on hold]

Copying to [dbo].[Sheet1$] Error Messages Error 0xc020901c: Data Flow Task 1: There was an error with Source - Sheet1$.Outputs[Excel Source Output].Columns[AA_change] on Source - Sheet1$.Outputs[...