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.

-2
votes
2answers
54 views

How to convert a multi-row result set into a single-row text separated by commas

I'm using this query select Name, valueType from mytable where ID = 1 and getting this table: But I need to covert the result set into something like: 'idGasto int, noTicket string, fechaFact ...
-2
votes
1answer
1k views

Varchar Overflowed an Int Column [closed]

I am having an error with the following query: SELECT scp.phone, scp.extension, scf.fax, sce.email FROM supplier_contacts AS sc LEFT OUTER JOIN supplier_contact_phone AS scp ON sc....
-2
votes
1answer
90 views

Does statistics matter? [closed]

Colleagues, based on your experience, what is the maximum page count of a table when statistics on this table doesn't matter? We have several relatively small tables - around 5000 8K pages. But! ...
-2
votes
2answers
426 views

Update nvarchar value with GETDATE results

I would like to be able to update an nvarchar column with GETDATE(). e.g. if the value is currently 'ordered', I need to append the date. I did try : update order_line set comments = 'ordered'+ ...
-2
votes
1answer
2k views

Sql Server Optimize left Join [closed]

Hello i've these 2 tables : Table Contac_Group ID int Grp_ID int Grp_Name nvarchar(80) Is_Grp_Hidden Bit Table Contacts ID int Contact_Grp_ID int /* this values is from Contact_Group ...
-2
votes
2answers
2k views

How to make an OUTER JOIN return ZERO instead of NULL

I am trying to accomplish this on SQL Server. The simplest table structure with data is shown below. Table: Blog BlogID | Title -------+-------- 1 | FirstBlog 23 | Pizza Table: User ...
-2
votes
1answer
185 views

Column alias with Table alias [closed]

My question is, what type of column alias it is? SELECT serial_no = ROW_NUMBER() OVER (ORDER BY Number),Item FROM (SELECT Number, Item = LTRIM(RTRIM(SUBSTRING('Anaemia|Anorexia|Cachexia', Number, ...
-2
votes
1answer
217 views

How Do You Set a SQL Backup to URL Blob Retention Period Greater than 30 Days?

How can a retention period be configured to delete SQL backup blobs older than some custom duration, such as 180 days? The existing command only permits a max of 30 days: use msdb; GO EXEC ...
-2
votes
1answer
1k views

why unable to execute table-valued function?

Here is table-value function I have declared like: CREATE FUNCTION fn_GetMedian(@List TypeMedian READONLY) RETURNS INT AS BEGIN RETURN ( Select ( ( ...
-2
votes
2answers
5k views

Select query having count and variable

I have a table like the one below. I need to form a select query. I'm going to pass a RefID like SELECT SNo FROM Register WHERE RefID = 1 And beside the SNo in the Statement, I need to have ...
-2
votes
1answer
41 views

Query syntax error [closed]

I tried a lot but can't find what is wrong with this query, It gave me this error: Incorrect syntax near ')' WITH cte AS ( SELECT * FROM ( ...
-2
votes
2answers
77 views

Concatenating Dynamic SQL commands with UNION returns inconsistent results

I'm testing a report that I've written in T-SQL and I'm having some issues with the results that are being returned. Here is the section of the code that I'm having issues with; CREATE TABLE #Tables ...
-2
votes
1answer
55 views

What do I do about blocked processes? [closed]

I am getting alerts stating: The SQL Server Performance counter 'Process Blocked' (Instance N/A) of object 'General Statistics' is now above the above threshold of 40 (the current Value is 41)" ...
-2
votes
1answer
42 views

Style bodytext while sending email using sp_send_dbmail [closed]

I am working on an email alert in trigger where body text has to be styled like this: Counterparty: a Previous Status: (Any status should be red) New Status: TRDNG OK(Green colored and Bold Upper)...
-2
votes
2answers
66 views

Are lost updates completely handled in SQL Server or is manual intervention still required? [closed]

As lost updates occur when 2 different transaction try to update the same thing at the same time - different sessions update the same row; the last one overwrites the changes made by the first. We ...
-2
votes
1answer
49 views

Looking for help with this shipping report! The Case Syntax is throwing me for a loop

So, the goal of this report is to get all orders that have been shipped in a month (October here). I was initially just using the 'PickedUpOn' field but soon found that there were instances where this ...
-2
votes
1answer
91 views

Is there any Performance Issue joining Row based Table to Clustered Columnstore Index Table [closed]

Is there any Performance Degradation joining Row based Table to Clustered Columnstore Index Table? Does SQL Server have any guidance for or against this? We have a table that is 2 million rows, and ...
-2
votes
2answers
441 views

Performance problems after setting ALLOW_SNAPSHOT_ISOLATION on

I'm running a 15GB database on SQL Server 12.0.5207. Server is a virtualized Windows Server 2012 R2 with 16GB RAM and 4 CPUs. Yesterday, we set ALLOW_SNAPSHOT_ISOLATION to ON, to check if it would ...
-2
votes
1answer
182 views

RID and Key lock on same object by a Select and Delete query

To be specific how did the select query (spid 128, on the left) is able to acquire a S (shared) lock on Index but not on the row? Also, how did Delete query (SPID 121, on the right) is able to get X ...
-2
votes
1answer
104 views

a RIGHT JOIN is not working as expected

Question UPDATED: Yes I know the tables are not normalized and yes I know there are no primary keys. Semester table Semester, Year, City 1|2017|Washington 2|2017|Paris 3|2017|London 1|2018|Paris 2|...
-2
votes
2answers
3k views

Connecting to Database using powershell [closed]

I'm trying to connect to my database using PowerShell but it's not find it even though I can see it using SSMS. I'm trying to follow the example MS provides here
-2
votes
1answer
455 views

Is it possible to disable locks for a specific table?

Is it possible to disable all locks for a specific table in any version of SQL Server? I have a handful number of tables that are populated with data at deployment time. For example, one of the ...
-2
votes
1answer
23k views

Scripting out database user level permissions [duplicate]

I have tried to search this and was able to find few but could not find exactly the way to script out the database level permissions for a database. I am migrating this database whose logins I can ...
-2
votes
1answer
2k views

write stored procedure to delete records

There are two tables job run and account interface, as per requirement to write code to prune associated JobRun records (JobRun.AccountId = AccountInterface.AccountID AND JobRun.InterfaceID = ...
-2
votes
1answer
658 views

Automatic Date Format Conversion

I have a column of data type date in SQL Server 2012. All the dates currently in this column have format yyyy-mm-dd. When I attempt to update a row explicitly: Update Table set column = 'mm/dd/yyyy' ...
-2
votes
2answers
245 views

Database engine with counting b-trees for efficient paging support [closed]

Today I realized another fascinating thing about sql databases: They don't do paging well at all. By paging I mean selecting a slice of data defined by a starting record number and a record count ...
-2
votes
1answer
4k views

Assign foreign key using one of two tables

Question 1: There are 3 Tables Direct Customers, Agent Customers, Sales Primary keys are marked in red underline: How to set foreign key for sales table using other two tables. I want foreign key ...
-2
votes
1answer
567 views

Script to check CPU, memory, server type

Can someone please help me with the script to get CPU, total physical memory, server type(i.e. physical/VM), maxdop and max server memory details? I need it to run for 50 servers. All the servers are ...
-2
votes
1answer
2k views

Which is faster Select Column names or Select *? [closed]

I am writing a Stored Procedure which is joining 3 tables, each table have two Columns. Query 1 : SELECT * FROM [dbo].[PersonInfo] P join [dbo].[Receipt] R ON P.Id = R....
-2
votes
1answer
1k views

can exec work with while loop of cursor? [duplicate]

Declare @var varchar(200) set @var='select max(val) from floattable' declare c1 cursor for select id from floattable declare @val int Fetch next from c1 into @val open c1 while @@Fetch_Status=0 ...
-2
votes
1answer
294 views

Maximum dataset size for a well-performing SQL Server 2012? [closed]

We're planning a project using SQL Server 2012 for storing about a billion records over less than 50 fields. Assuming good hardware, is SQL Server up to that task? Roughly how many request/second can ...
-2
votes
1answer
539 views

Errors keeping stored procedures through restore from backup [closed]

We are running SQL Server 2008 R2 on two identical servers. One, called LIVE, is our production environment. The other, called QA, is our dev enivornment. In order to check backup integrity and run ...
-2
votes
2answers
4k views

Export complete database to a remote server including (Tables, Stored Procedures, Triggers)

How do we "export" a complete Microsoft SQL server database from one server to a "remote" server (including all the "stored procedures" and "triggers"). The export facility provided in the management ...
-2
votes
1answer
3k views

How to run 2 separate sql server engines on the same server?

I have a Windows server, and on this server there are many websites running and most of them connect to the installed SQL Server, some of them connect to SQL Server Express and some to local. My ...
-2
votes
1answer
84 views

Who is the original author/creator that took the time to fill/create Northwind SQL server sample database? [closed]

I've used it before, but I never really seen any documentation in SQL server pointing to who actually created it. Is it a person, a group of people? Did Microsoft create it? Who??
-2
votes
1answer
43 views

How can I find out who's logged on to database?

I'm trying to rename a database in SSMS and I get this error: The database could not be exclusively locked to perform the operation So how can I find out who or what is using this database? I don't ...
-2
votes
1answer
52 views

SQL Server performance with FREEPROCCACHE [closed]

I watched a lesson on Youtube about SQL Server performance tuning. They demonstrated on a query "Wait Time per Core Per Sec". They use an update script as below: DBCC TRACEON(4199,-1); DBCC ...
-2
votes
1answer
44 views

Column name or number of supplied values does not match table definition. What shoul I do? [closed]

create table employee_tbl ( EmpID int primary key, FirstName varchar(50), LastName varchar(50), Salary float, MobileNo varchar(15) ) create table employeeaudit_tbl ( EmpID int primary key, AuditData ...
-2
votes
1answer
51 views

SQL Server Database flatten records

Sorry for the confusion. I have below attached table sql server database table holding continuous and non continuous records. When i say continuous that means EP of first record if Beginning Point(BP)....
-2
votes
1answer
81 views

Centralized Logs (error, deadlocks, slow queries) For SQL Servers

I want to establish a centralized log for many SQL Servers, similar to the products Graylog or elk stack. What is the best and performant way to send these kind of logs to a log collector? I ...
-2
votes
1answer
98 views

Datawarehouse Staging Tables: Null or Not Null

We are loading data from OLTP into Staging Tables with SSIS, then into Kimball data warehouse with ETL process. We are inquiring about staging tables, if they should have null/not null constraints. ...
-2
votes
1answer
76 views

What difference between stored procedure and query in application [closed]

What difference between stored procedure and query in application. Performance , network load, server load and speed Thank you very much
-2
votes
1answer
42 views

Query for max CPU, Memory and System Queue length

I am looking for a query to find maximum CPU, Memory and System Queue length for last month. Any help would be greatly appreciated.
-2
votes
2answers
53 views

Problem in formatting result set in desired format

I have devices which are installed in a different location with different area ID I am returning the device activity status of different areas on an hourly basis in the following format. AreaId | ...
-2
votes
2answers
442 views

Find maximum number of concurrent logged in distinct users

I am looking out for a query providing month and year and show 4 (or 5) weeks of the selected month, and for each week it should show the maximum number of concurrently logged in users during this ...
-2
votes
1answer
46 views

Some one please help with this script [closed]

DELETE FROM Sql.dbo.Data DD INNER JOIN ( SELECT TOP 100000 D.CLIENT, D.REFERENCE FROM Sql.dbo.Data D INNER JOIN Sql.dbo.Log P ON D.CLIENT = P.CLIENT AND D.REFERENCE = P.REF WHERE P....
-2
votes
1answer
2k views

Find the max(date) from two tables in the most efficient manner [closed]

I have two large tables containing a common value ContactReference, and two DateTime fields (amongst a lot of other fields). One table for Contact and one for ContactAddress. I am trying to find an ...
-2
votes
2answers
169 views

RAID 5 configuration with two drives [closed]

I am new into RAID implementation. I have researched on RAID and learned about RAID levels. I have to install SQL Server into one of the server(server have two drives currently). I am told by ...
-2
votes
1answer
429 views

Error RUNNING scalar function “Invalid Object Name TestFunction” [closed]

My problem is similar to this one, but with a further twist: I can't call the function without getting an error. Msg 208, Level 16, State 3, Line 5 Invalid object name 'TestFunction'. I can live ...
-2
votes
1answer
42 views

What is Mirroring in SQL Server ? When and how it is used? [closed]

Hi i am new to SQL Server and wanted to understand what is mirroring, different types if any and when or what scenario we used mirroring in SQL Server.