Questions tagged [sql-server-2012]
SQL Server 2012 (major build version 11.00.xxxx). Please also tag sql-server.
4,169
questions
0
votes
0answers
4 views
By using SQL Window Functions, how can I get the lead of the last row in a partition?
Considering the following simple example:
select lead(c) over (partition by a order by a)
from (values
(1,'1','date1'),
(1,'2','date1'),
(2,null,'date2')
) t(a,b,c)
The first two ...
1
vote
2answers
13 views
Incremental backup between now and backup taken one week ago
We do a full backup of our SQL Server 2012 database every night.
A new developer needs an incremental (or differential) backup of a backup we took one week ago. The reason is that for them, ...
1
vote
0answers
11 views
Informatica MD5 function vs SQL Hashbytes MD5 function
I am hoping someone has some crossover experience here. I am attempting to utilize the Hashbytes function within SQL to match a value that is being generated utilizing the MD5 function from ...
0
votes
0answers
15 views
TDE Encryption and “Recovery Pending” After Service Reebot
I've recently enable the TDE Encryption in a SQL Server 2012.
I've setup de Encrytion key and the certificate in Production and exported to the Test Environment in order to copy/update test environmet....
2
votes
1answer
31 views
Log Shipping and Daily Backup
My SQL Server 2012 DR setup is a mess. We've had a maintenance plan in place from the beginning that takes a daily full backup, with hourly log backups. Recently, we set up log shipping to an off-site ...
7
votes
2answers
291 views
See details of old sessions
Is there any way of seeing details of sessions from the past (I fully expect the answer to be "no"). I am trying to find out what server a certain application is running on, that application accessing ...
0
votes
1answer
24 views
Passing Variable in json_value in sql server 2016 [duplicate]
declare @id int
set @id = 1754
select
(JSON_VALUE(cast(ad.data_map as varchar(max)),'$.dataMap."1754".value')) CorrespondanceNumber,
(JSON_VALUE(cast(ad.data_map as varchar(max)),'$.dataMap.***"'+...
1
vote
1answer
35 views
What impact on the index storage size does overprovisioning of varchar types have?
Does over-provisioning varchar columns (as in using varchar(100) to store values from 1 to 10 characters long vs using varchar(10)) have any impact on the index storage size and if yes than why?
I ...
0
votes
1answer
26 views
Does Data Migration Advisor migrate system databases?
I would like to know if Data Migration Assistant copies system databases (master, model, msdb) into the new server.
0
votes
0answers
10 views
Report Service stopped unexpectedly
The SSRS service stopped unexpectedly and the workaround was to recycle the app pool and then restart the service, but no clue of what happened.
When looking at the error log below:
<Path>C:\...
0
votes
0answers
43 views
A Procedure Causes Some Blockings In SQL Server [on hold]
There is a stored procedure in my sql server database and it causes so much blockings for itself. Here is the code. How to solve this issue?
USE [FATURA]
GO
/****** Object: StoredProcedure [dbo]....
-1
votes
1answer
47 views
How can I achieve the below case in marketing cloud without using loop? [on hold]
I have a table like this
and in the table you can see
123 losing to 456, 456 losing to 789 and 789 losing to 101112 and in the same fashion for ID's 5 and 6.
In this case I want to replace winner ...
3
votes
1answer
131 views
How can I get rid of this Lazy Spool, or otherwise improve this query's performance?
Here is the plan: https://www.brentozar.com/pastetheplan/?id=rkM8d7ONS
I am mostly interested in how to get rid of the lazy spool?
This is the query:
SELECT DISTINCT
SM.Security_ID '...
-2
votes
0answers
21 views
What would be the suitable certification for SQL Data protection and privacy? [closed]
This posting is not related to any specific issue. I couldn't find suitable Certification code related to SQL Data Protection , Privacy , SOX ( Sections 302,404,408, 409 etc..)
What would be the ...
1
vote
1answer
31 views
When are statistics created?
I know that when we create an index then statistics are automatically created. My question is: Is it the only case when statistics are created? I remember that once I found on the internet that we ...
0
votes
2answers
122 views
How can I use SQL Profiler to capture a single stored procedure
Using SQL Server SQL Profiler, which configuration can be used to monitor a single stored procedure? I'd like to capture all EXEC sprocName to include the parameter list. I'd like to capture this ...
1
vote
1answer
64 views
Is it useful to index a column if it is already the first column in a composite index? [closed]
Suppose I have this table (simplified from my project):
create table BillTax
(
BillID int not null constraint FK_BillTax_Bill foreign key references Bill(BillID),
TaxCode varchar(20) not null,...
-1
votes
1answer
39 views
How to loop data month wise for the targeted dates [closed]
I need to load data from a primary table to an archive table by month or week.
As an example, I have a table called transactions with a column named return_datetime where I have 5 years or data.
I ...
0
votes
1answer
17 views
Implementing gMSA account for SQL FCI
I have a question on implementing gMSA account for managing authentication to SQL server.
Our goal is to use gMSA account to manage the auto password change activities for SQl server service and it's ...
-1
votes
1answer
51 views
restart sequence when the year changes
Is it possible to do a sequence with Start at 1 and INCREMENT 1, and when changing the year, restart the sequence?
-- code# Create a sequence
CREATE SEQUENCE SEQ_ALUNO
START WITH 1
INCREMENT BY ...
0
votes
0answers
14 views
Not able to migrate DTS package from SQL Server 2008 to 2012. Getting errored out
Requirement: We need to migrate SSIS/DTS package from SQL Server 2008 to another machine having SQL Server 2012 on it.
Steps performed so far:
1. First I exported the package from Source server to ...
0
votes
1answer
39 views
SQL Full Backup job is taking too long to complete
I am facing a strange issue with daily FULL backup. We have a total of 23 databases. We have 1.21 TB big database in this SQL server 2012 Standard Edition. We are taking daily FULL database backup for ...
4
votes
2answers
75 views
A table with all fields NULLable with important, sensitive data
I have a requirement for a database table to hold sensitive patient information about their lifestyle (alcohol/ drug use / injecting etc).
On screen the information is sectioned into tabs - "alcohol",...
-1
votes
2answers
75 views
Table with 80 foreign key
I have a table with 120 columns and most of them (around 80) are foreign key . We are refactor some of main table in our system . I was thinking of change this table to divide in small table because ...
0
votes
0answers
6 views
SSIS package || Scheduling in SQL Server 2012
We are tring first time to create SSIS package for export the Data from SQL table to Excel.
Using this tutorial in SQL Server 2012.
Now package has been created. But we are stucked in scheduling it....
9
votes
1answer
263 views
Why do MS SQL Server SEQUENCEs not have an ORDER parameter like Oracle?
In the documentation of CREATE SEQUENCE for T-SQL, you can see that the CREATE SEQUENCE command has no ORDER parameter.
For comparison, the Oracle docs for CREATE SEQUENCE show the ORDER/NOORDER ...
0
votes
1answer
32 views
I cannot get my data source to connect to an SQL Server named instance using SSRS Report Builder
I have a report in SSRS and I want to update the data source to use a named instance instead of the default instance. The server host will remain the same.
I thought this would be easy. I simply ...
2
votes
1answer
30 views
pivot on date and count values by hour
I hope this makes sense. I have a database which logs the day and time a row was inserted. I have a query which will select and count the number of insertions per hour.
I would like to pivot this so ...
1
vote
2answers
65 views
Get the matching condition in the SELECT list
Let's say my full text search query is below:
USE AdventureWorks2012;
GO
SELECT Name
FROM Production.Product
WHERE CONTAINS(Name, '"chain*" OR "full*"');
GO
I need one more column that ...
0
votes
0answers
44 views
MS SQL giving separate result on same parameters using application & query editiors
I have a SQL stored procedure which gives out result based upon the days passed in parameters.
I miss the weekend day (Saturday) from result if I call the stored procedure from my application but I ...
0
votes
3answers
93 views
Check if the value from one table exists in another tables in SQL Server
I have one table (tbl1) with column ID, the values can be duplicated. I have others tables (tbl2, tbl3...) with column ID , values are unique.
I want to write a trigger on insert row in tbl1 and check ...
1
vote
1answer
32 views
How can I configure a connection string in SSRS 2012 using named pipes?
I have SQL Sever 2012 and I want to create a data source that uses named pipes rather than TCP.
This is when I have tried:
Data Source=np:\\.\pipe\MSSQL$MSSQLSERVER02\sql\query;Initial Catalog=...
12
votes
1answer
154 views
Is this a symptom of an overloaded server?
I've been trying to diagnose slow-downs in an application. For this I've logged the SQL Server extended events.
For this question i'm looking at one particular stored procedure.
But there are a ...
0
votes
0answers
9 views
Different security levels for one SSRS report
I am trying to grant limited access for one report per user. For example, the report consist of multiple cities and each city has profit info within them but the users should only be able to see the ...
-1
votes
0answers
36 views
How to mask a column permanently in SQL Server? [duplicate]
I have to create an UAT testing environment of my production database and hand it over to developers, but I want to mask certain columns permanently, so they cannot view the actual data at all.
How ...
0
votes
2answers
43 views
How To Grant A User Just To Create A Table In Existing Database But Not To See Other Existing Tables In It
We have dealed with an outsource company to install an application for use of our accounting department. Now, they want to access our existing database to install their application but i don't want ...
0
votes
0answers
31 views
How to recalculate and adjust the entire row of time in and time out in sql server?
In SN = 1, I have an original schedule of 09:15:59 with my first time in is 09:50:57 so that I am late about 34 mins. In SN=2, 13:27:05 is my time out. In SN=4, I time in again of 15:40:13 that gives ...
4
votes
1answer
44 views
Delete unused indexes from Microsoft Dynamics CRM Application
So I found tons of unused indexes from our CRM application with this query from Pinal:
SELECT
o.name AS ObjectName
, i.name AS IndexName
, i.index_id AS IndexID
, dm_ius.user_seeks AS UserSeek
, ...
6
votes
3answers
1k views
Capture SQL Server queries without third-party tooling and without using deprecated features?
Is it possible to capture all queries sent to an MS SQL Server, without third-party tooling and without using deprecated features?
I'm looking for something similar to the "general query log" in ...
2
votes
2answers
148 views
Usage of NON Clustered Index in SQL Server
I have 2 Index:
CREATE NONCLUSTERED INDEX [IX_1] ON [dbo].[TableA]
(
[Column1] ASC,
[Column2] ASC
)
CREATE NONCLUSTERED INDEX [IX_2] ON [dbo].[TableA]
(
[Column3] asc
)
Now I have 2 ...
0
votes
1answer
55 views
How can I combine these three queries into one result?
I'm still pretty new at SQL, so I apologize if this is a silly question. I took existing queries for three data points, and I'm not sure how to streamline them into a single output.
Clunky query:
...
3
votes
1answer
111 views
Available space differs between sys.database_files and database properties dialog in SQL Server Management Studio
I am looking at this for a few minutes now and could not find a thread mentioning this.
I wanted to query currently used space and available space from a database on SQL Server.
The numbers I query ...
0
votes
1answer
27 views
Line breaks handled inconsistently by SSMS
Probably more of a SQL Management Studio question than SQL, per se, but when I run the following simple query:
SELECT results = 'Foo' + CHAR(13) + CHAR(10) + 'Bar'
I appear to get different results ...
0
votes
3answers
38 views
Add windows user to a particular database in SQL Server
I have a windows user who I want to add to just one database SalesDeals on the server. I added them in SalesDeals-> Security -> Users section of the Database and gave them db_datareader ...
0
votes
1answer
47 views
How to Query Which Table Is Inserted Mostly In SQL Server?
I have a database and there is not any trigger which follows the transactions in said database.
Now, I have to make a report that shows which table has mostly inserted so far. How can I do that?
1
vote
1answer
23 views
SQL Server 2016 - Disparity Between View Definition and View Design
I've run into an interesting issue with some databases I've inherited, and could use some help unpacking it all.
The Issue
After adding a column to a table, views selecting * from that table are now ...
0
votes
1answer
22 views
Defaulting a column to the 1st of next month
I'm trying to find the most elegant means of defaulting a column's value to the 1st of next month. The best I've been able to come up with is this:
ALTER TABLE Foo ADD
Bar datetimeoffset(0) NOT NULL ...
0
votes
1answer
64 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 ...
4
votes
1answer
74 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 ...
1
vote
1answer
57 views
Deny Delete to An Active Directory Account in Sql Server
I want to deny an AD account to delete any record in sql server. How can i do that?
DENY DELETE TO [usergorup\username] did not work. Is there another way?
