sql server - how to multiple values to subquery that one xml field

declare @x xml;declare @DocHandle int;select @x=(select EmployeeAllwDedDetail1.EmpAllowanceDeduction from EmployeeAllwDedDetail1 where EmployeeKey in(select EmployeeKey from EmployeeMaster where EmployeeMaster.EmployeeID in(SELECT * FROM string_split('136,137', ',')) ) --and MONTH(EmployeeAllwDedDetail1.EffectiveDate) = 2 --and YEAR(EmployeeAllwDedDetail1.EffectiveDate) = 2018)EXEC sp_xml_preparedocument @DocHandle OUTPUT, @x -- Execute a SELECT statement using OPENXML rowset provider. declare @temptbl1 table(Name1 varchar(20),Amount d...Read more

sql server - How do I create a unique constraint that also allows nulls?

I want to have a unique constraint on a column which I am going to populate with GUIDs. However, my data contains null values for this columns. How do I create the constraint that allows multiple null values?Here's an example scenario. Consider this schema:CREATE TABLE People ( Id INT CONSTRAINT PK_MyTable PRIMARY KEY IDENTITY, Name NVARCHAR(250) NOT NULL, LibraryCardId UNIQUEIDENTIFIER NULL, CONSTRAINT UQ_People_LibraryCardId UNIQUE (LibraryCardId))Then see this code for what I'm trying to achieve:-- This works fine:INSERT INTO People (Nam...Read more

ssms - What is the best way to auto-generate INSERT statements for a SQL Server table?

We are writing a new application, and while testing, we will need a bunch of dummy data. I've added that data by using MS Access to dump excel files into the relevant tables.Every so often, we want to "refresh" the relevant tables, which means dropping them all, re-creating them, and running a saved MS Access append query.The first part (dropping & re-creating) is an easy sql script, but the last part makes me cringe. I want a single setup script that has a bunch of INSERTs to regenerate the dummy data.I have the data in the tables now. Wha...Read more

sql server - .ldf File Shrink or Reduce Size - Recommended Solution

I am confused about SQL Server t-Log file size growth (.ldf file). I analyzed various blogs/topics by DBA's some recommending Log file Shrink while some advised not to do so. Here is what I used to follow: Take Database in Full mode if its not already in it. Shrink Log files if not enough memory freed then move to next step. De-Attach Database. (sometimes it went in Single Mode and no matter what deadlock process I kill , it will never come in multi-user mode again. BIG PROBLEM!!! ) move .ldf file to other location and Re...Read more

sql server - Determine MDF and LDF file match

Is there a way to determine what MDF goes with what LDF file for SQL Server? We had a server crash and pull these files off and were only named with a random integer for the file name. So now we need to guess which MDF and LDF go together to get them up but what is the best way to do that?...Read more

Importing views from microsoft sql server 2003 fail

I'm trying to migrate from microsoft sql server 2003 to sql server 2016.Importing the data went well, using Microsoft SQL Management Studion 2016, but the views were not imported. And here is my request, how can I import the views?At this point I tried:Generate a creation script from the old database, but it seems back in 2003, this option didn't existedMake a query in the old SQL server to list all the views, copy them and recreate them manually in the new server, but no queries worked to retrieve all the viewsCopy the .MDF and.LDF file from t...Read more

sql server - Why is my LDF so large?

I have a Microsoft SQL Database with an LDF of 500MB. Usually, this is less than 1mb. Recovery Model is set to simple. This is SQL Server 2014 Standard x64. Any idea why the LDF would be 500mb if the recovery model is simple? I have tried shrinking it. it caught my eye because it was 22gb.This is a database for a Practice Management Software for Ortho. I have approximately 500 deployed and this is the only one that is larger than 1 MB when in simple recovery....Read more

Attach a database using MDF file that is Read-only and without LDF file in SQL Server

I want to attach a DB to SQL Server. I have the mdf files for it but those are in read-only state. Also the LDF file is not present. I use the following command to attach the DB. Create database TrackData on (Filename = N'F:\Data\Bill_Data.mdf') FOR ATTACH_REBUILD_LOG but i get the following error. Msg 3415, Level 16, State 3, Line 1Database 'Bill_Data' cannot be upgraded because it is read-only or has read-only files. Make the database or files writeable, and rerun recovery.Msg 1813, Level 16, State 2, Line 1Could not open new database 'Bill_D...Read more

replication - SQL Server Log File Is Huge

Currently my db logs for my production SQL Server 2008 R2 server is growing out of control:DATA file: D:\Data...\MyDB.mdf = 278859 MB on disk LOG file: L:\Logs...\MyDB_1.ldf = 394542 MB on diskThe server mentioned above has daily backups scheduled @1am & translog backups every 15 min. The database is replicated in full recovery model to a subscriber. Replciation is pushed from the node above (publisher). That same db log file on the subscriber is ~< 100 GB on disk.What I did to try and fix:Run a full backup of the db (takes 1h:47m)Run th...Read more

sql server - ldf in simple mode grew

The next .ldf grew to 50GB, and is eating all the disk… (it is in SIMPLE recovery model)I wanted to be able to roughly answer these questions:-what’s inside the .ldf? (can I say it is just temp tables?!)-which command or user caused this 50GB to fill up?-potential issues if I force to shrink the file to 10GB.I do not want this information to blame anyone, but to educate ourselves on the usage.Amazingly I get this result:...Read more

ldf - SQL Server: Why isn't the log file up-to-date with the database file on full recovery mode?

Using SQL Server 2012 on full recovery and looking at the directory:C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\I see .mdf and .ldf files.For a particular database, the modified times are very different? Why is this? I would have thought that as you modify a database your transaction would be recorded in the ldf file (regardless of the type of query)?...Read more