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.
0
votes
0answers
4 views
Having a backup of the master key, how can I restore a database that was encrypted by master key?
while setting up AlwaysOn - Availability Group on a test system,
I generated all the restores from live and restored them to this new server.
then I backup all these databases to a network share to ...
0
votes
0answers
8 views
Update thousand records with primary and foreign key
I am a beginner when it comes to SQL (SQL Server) and I hope you help me.
A warehouse database, where we have a tl_warehouse_items table for example, which contains over 50,000 different items and ...
0
votes
0answers
7 views
what is the relationship between product and stock?
My assumption is that it can be many to many as one product can be in many stocks and many product cane be in one stock but in the the original question is that there are 10 store locations where they ...
0
votes
0answers
22 views
sql query three tables with join
i have 5 tables in my database. that are connected with each other using joins.
department table vs course one to many
course table vs classes one to many
classes vs section many to many.
i want to ...
0
votes
0answers
34 views
Stored Procedure code
I am a beginner when it comes to SQL (SQL Server) and I hope you help me.
A warehouse database, where we have a tl_warehouse_items table for example, which contains over 50,000 different items and ...
0
votes
1answer
27 views
How to setup a SQL Cluster between 2 sites in active passive mode without shared storage
we need to install SQL at 2 sites, 1 primary and 1 DR. We don't to pay for license for the DR and need to know how will we setup a SQL cluster/replication/alwayson/bag or any other method that will ...
0
votes
0answers
31 views
Refactor SQL to left join on TVF requiring input variable
I would like to join two tables and a table-valued function. The TVF take a date as input variable.
The expected output is:
Date, LimitName, Exposure, ProductValueEligible, ProductValueNonEligible
...
0
votes
0answers
15 views
SQL Server availability groups and traditonal replication
Could you tell if it's generally a good idea to combine AG and traditional replication?
What kind of problems a traditional replication could create for AG?
1
vote
1answer
34 views
SQL Server Management Studio Doesn't do “Full” Backup
Very new to SQL Server. I'm trying to bring a backup from one our clients Windows 2012 Server Install to our Linux Install. We have the site working perfectly on Linux, but can't seem to do an ...
2
votes
0answers
33 views
70-761 Practice exam Question about @@Trancount final value
There is this question in the practice 70-761 exam that states after executing the following script, what is the value of @@Trancount?
Exam script:
CREATE PROCEDURE dbo.up_CreateSalesInvoice
(
@...
0
votes
0answers
30 views
Red gate SQL Compare error while running using SQL Agent Job
I created a SQL agent job to perform a SQL Compare using the below command line
powershell.exe -File D:\SQLCompareReports\DEVvsPRJ\DEVvsPRJ.ps1
This below given is the command line code in DEVvsPRJ....
0
votes
0answers
5 views
Migrating to SqlAzure - workaround for error “The element Certificate: [CertName] is not supported”
Within my database I use symmetric encryption on certain columns. I'm investigating migrating to SQL Azure, but when I try to migrate my database using the Wizard within SSMS, I get a couple of ...
0
votes
0answers
34 views
compare two logins on same SQL server and the output should be the difference between the first and the second
I want to compare two logins on same SQL server and the output should be the difference between the first and the second.
This requirement come up as there is migration of logins are happening from ...
4
votes
1answer
49 views
SQL query output on one line
I need to create a query in MS SQL Server to get db and db log size on one row. The application that reads the output can't view more than one row.
Something like this gives me the info I need. The ...
5
votes
0answers
50 views
CMEMTHREAD waits during Index Optimisation in Availability Group
In a 3 node Availability group, a secondary replica will often become subject to redo lag due to the causes covered in Microsoft's documentation:
Troubleshoot: Changes on the Primary Replica are not ...
0
votes
0answers
8 views
When I update end time of a tblSubTaskDetails, how to automatically update all tables with sum of ProjectID, TaskID and SubTaskID?
When I update tblSubTaskDetails, sum of particular ProjectID, TaskID, SubTaskID should be inserted in the tblSubTask table in ActualHours.
Like wise it should update tblSubTask with sum of particular ...
7
votes
2answers
77 views
Increase Max Memory setting on a SQL Server Production
I have a production box with 5 instances on.
Version: SQL Server 2014 SP3 Enterprise.
I discovered that even though the machine has ~400GB memory assigned, the combined MAX MEMORY settings on all 5 ...
1
vote
1answer
30 views
Security Risks with having Test Stored Procedures that have SQL Injection Vulnerability
I noticed that some of the stored procedures used in our integration tests for data generation have SQL injection vulnerabilities. These procedures are taking into string parameters to build a SQL ...
0
votes
0answers
35 views
How to find all values in one column that have two or more values in another column [on hold]
I have a table that has a column that can have multiple identical values. In another column, the values can be different. I'm looking for all the instances in the first column that have specific ...
0
votes
0answers
43 views
Can a SELECT statement block another SELECT on SQL Server?
So the basic question is simple, can a SELECT statement alone block another SELECT statement? My understanding was you needed at least an UPDATE or INSERT for a SELECT to start blocking something ...
1
vote
0answers
21 views
Searching for and parsing out specific text parts within the values of a column
I'm working on a CASE expression that I need to have in order to search for, and parse out, specific text values.
Sometimes the relevant column will contain a person's name, and sometimes it will ...
3
votes
1answer
33 views
Changing Database Owner Removed User Mapping
I ran an ALTER AUTHORIZATION to change a database owner from an AD account to sa and it seemed to cause the user mapping for a specific database to be removed. I’m not sure why this happened.
Is ...
1
vote
2answers
25 views
combining multiple rows with arbitrary number of columns
I am attempting to format a query but, I don't think I know all the required methods to do what I want currently I have these results
Source
------
item |PackQty|TotalUsage |JobDescription|...
2
votes
1answer
90 views
Understanding the differences in the storage space used by two tables
I have two tables in which, more or less, one contains a subset of data of the other one. There is some data I delete from the first table, which is inserted in the second one as historical data.
...
1
vote
0answers
35 views
Designing a database for a weekly time schedule - speed vs space
I need to "store" a weekly time schedule as compactly as possible. The schedules are in 15-minute increments. In a 24 hour period that would give me 96 time slots. Midnight is 00, 0600 is 24, 1800 ...
2
votes
1answer
21 views
Help with a pivot query
I've got data that goes sort of like this:
Time Name country
12 steve brazil
12 mary brazil
12 kate usa
12 john usa
13 paul canada
13 tim canada
...
0
votes
1answer
29 views
I gave 3 large Excel files need to be loaded in Single SQL table
I have 3 large Excel files each sized 56896 KB, I need to load all 3 files into same SQL table. what is better way to handle?
since My excel has more data, my excel hang up when I open and search any ...
0
votes
0answers
14 views
SSIS data flow gets stuck reading table with no errors
When I'm doing ETL in SSIS, the data flow task "pauses" reading from the source transactional Live IO DB (the one on the left). It stops reading the data and "pauses" on random rows and then does ...
1
vote
0answers
31 views
Different query plans for same In line Function [duplicate]
I am facing a potential parameter sniffing issue with an In Line function in sql server 2008.
Basically Given the below three queries (same function):
First Query:
DECLARE @SQL NVARCHAR(1000)
...
-1
votes
0answers
26 views
Lost the DB owner access by mistake
I was trying to delete a user from DB(AWS RDS) which was part of dbowner schema. While removing that user, I gave the ownership of dbowner schema to dbo role.
Now I have lost the access to that DB. I ...
1
vote
1answer
38 views
Query to see Index Fragmentation on all databases without using SP or temp tables
I am trying to compile a index fragmentation report on all databases, in a multi-server environment. The query will be executed via an openquery command, so I would like to make it as simple as ...
-1
votes
0answers
11 views
I am iserting the data in sqldatabase by csv import But This code insert only first row of csv can you help me [on hold]
here is my code for import the csv file
//code starts form here
public function import() {
$data = $this->request->data['csv'];
$file = $data['tmp_name'];
$handle = fopen($file, "...
1
vote
1answer
15 views
AlwaysOn Availability Groups and Encrypted Channels
If I want to implement both AlwaysOn Availability Groups and Encrypted Channels, how would I do this?
There is a desktop application (Visual Studio 2008 built) that interacts with this database, and ...
1
vote
1answer
22 views
Log Shipping Backup grey in Transaction Log Shipping Status
I have slow network that makes configure log shipping via GUI always timeout, so I generate script and run it in both primary server and secondary server.
The scripts work fine but the primary ...
3
votes
2answers
160 views
SQL table with same name as catalog view
When naming tables in SQL I try to stay away from SQL reserved keywords, but today a colleague questioned the use of Events as a table name. They said, anything that turns green in SSMS shouldn't be ...
0
votes
0answers
29 views
Optimize a query with unexpected results [on hold]
I don't post in this SE often, so if I'm not explaining the query properly please let me know and I will edit.
I have to obfuscate some of the work that I am doing so I will try to explain my issue ...
2
votes
0answers
22 views
Memory usage grows on SQL Server Analysis Services 2012 until cubes fail to process
We have a 64-bit SQL Server 2012 SP4 Enterprise Edition instance with Analysis Services (Analysis Services is running on the same VM as the SQL Server database engine) where the memory usage of the ...
2
votes
0answers
28 views
Extracting Data from SAP to SQL Server
I am using SSIS packages to extract data from SAP database tables into SQL Server tables. I am using OLEDB source/destination connections to achieve this.
The problem now is that a table in SAP has 5 ...
-2
votes
0answers
51 views
Database and Log Files not growing, and file dates remain unchanged [on hold]
My SQL Server 2012 Database and Log files have not changed their modified date in over a month.
How is my site and database working properly if these files are not updated?
I am running full backups ...
1
vote
2answers
39 views
Inline function Plan Properties in SQL Server 2008
Currently I use the below two queries to get some properties from the execution plan in stored procedures, the problem arises when I try to run it in a SQL Server 2008 version. Do you know if there is ...
0
votes
1answer
30 views
Create database in PostgreSQL from SQL Server [on hold]
An application is being made in .NET to register (inventory) databases (from SQL Server, PostgreSQL, MySQL and Oracle), users and credentials in SQL Server tables, what we want to do in addition is ...
0
votes
1answer
24 views
Mapping an MSSQL Login to the secondary replica in a High Availability Group
I'm trying to map a login to a user in a database on the secondary replica of a two node High Availability group. Obviously this fails since the replica is offline. Is there a way for me to create ...
1
vote
1answer
42 views
Using Window Function instead of Select sub-query for counting
I have to count the number of Orders a table, per Employee, for two criteria -
"Late Orders".
"Total Orders"
The query includes Orders table and Orders table. They are joined together via the ...
3
votes
1answer
123 views
Why isn't space deallocated after deleting all rows from a clustered table?
Today one of our queries has gone crazy. It was hanging for more than an hour before I killed the process.
UPDATE tc
SET tc.Status = 0
FROM #RC_Task_Calc AS tc
CROSS
APPLY ( SELECT TOP ...
4
votes
1answer
269 views
Forcing execution plan with local join hints
I have several encounters with bad execution plans in batch jobs during a week, and to avoid forcing plans I have moved on to adding local join hints (when these join types are the difference between ...
4
votes
1answer
209 views
Why does JSON_QUERY within CASE statement fail to remove escape characters?
It would seem that json_query fails to remove escape characters for the double quote character (") if the function is being applied within a case statement.
Example code below.
declare @data ...
1
vote
0answers
20 views
How do I grant permissions for a SQL Server Management Studio user to access system.views in an Azure Managed Instance?
I am connecting to an Azure SQL managed instance with Active Directory through SQL Server Management Studio.
I'm trying to access system views, like:
SELECT * FROM sys.dm_db_missing_index_details
...
1
vote
1answer
32 views
Do NDF files move with Alter Database automatically
I know you don't want to hear this but i am not a DBA, but I am responsible for a 2016 SQL server. From time to time I need to do some DBA work.
This time I need to move the tempdb (mdf, ndf, and ...
0
votes
0answers
25 views
Failed to initialize sqlcmd library with error number -2147467259 [on hold]
SP_db_sendmail fails with Failed to initialize sqlcmd library with error number -2147467259.
0
votes
1answer
44 views
Query Pivot Date column into Rows [on hold]
My problem is pivoting one single Date column into my row headings while all my current Column headings become rows.
My Column headings are [Date], which contains 50 years of days (from an awesome ...
