I'm trying to use 'RESTORE HEADERONLY ' to get the date when the backup I'm about to restore was made. The command: RESTORE HEADERONLY FROM DISK = '' works fine in Query Analyzer and gives a resultset with something like 50 columns. The problem is actually accessing this from code. I can get this into a temp table by declaring every single one of the 50:ish columns, inserting into it with exec and getting the value I want from there. The problem is that I really want to avoid having to declare the entire resultset as a temp table as it seems like a very brittle solution if they ever add columns to it in future versions. Is there any way to just get a single column out of this resultset without declaring all the columns?

I have migrated a SQL server from another server box that was decommissioned. They have the same name and also same IP address. However, when I connect to the server from an application using trusted connection, I get the "SSPI context not generated" error. What I have done: * I have used the setspn -X to confirm there is no duplicate SPN. * I have changed the order of protocol to follow the order: Shared Memory, Named Pipes, TCP/IP. * I have verified that when I restart SQL server, the service registers and deregisters. This was found in the SQL server log. * I have checked that on the SQL configuration the TCP/IP network protocol has the right IP and is active and enabled on both 32bit and 64bit. I am running out of ideas and I am still getting the same error. I can't find any log that point to Kerberos.

I've backed up, transferred to another location and restored a database. Is there anyway to check that the data is the exact same? Would I be best using the CHECKSUM and then the VERIFYONLY method? Thanks.

I have my machine and a database server on the same Windows domain. I also have a staging machine that is not on the domain. From my machine, I am able to perform any and all operations using FILESTREAM without trouble. From the staging machine, my application is able to use TSQL as it pleases, but the moment it tries to open a SqlFileStream, I receive 'access is denied.` The staging machine, the database server, and my machine all have a local user set up with an identical name and password so that the application can use integrated security to connect to SQL Server. Since FILESTREAM is the only thing not working for the non-domain machine, I am wondering if I am missing something in the FILESTREAM documentation or if the documentation fails to specify that only Active Directory will suffice for integrated security, or of course if there is something I can do to make my situation work.

I have a database on SQL 2008 R2 with compatibility level 80 and I want to change it to 90 or 100. I assumed that after changing, I may find a regression in our application's performance. Many backups (like full, differential, and log) will be taken while using the new compatibility level 90 or 100. This database is also configured with log shipping. My question is can I change compatibility level back to 80 safely without threats or must I take any considerations?

I have captured a trace file using the replay template. It includes all columns available, of course including ERROR. When I replay this trace file, the "trace configuration" window pops up, I declare the target server, save the result to a new trace file and run the trace. When I then load the new trace file, most of the columns are gone, including the error column. I was hoping to filter on ERROR > 0 in the resulted trace file of the replay action to find queries having problems on the replayed-against server. Why are the columns not included anymore - and why does the new trace file contain not all events anymore but instead includes these "Replay Result Row Events" (e.g. Name = blabla...) ? Edit: Meanwhile I followed a suggestion to look at the trace file using a sql query: SELECT * INTO mytracetable FROM ::fn_trace_gettable('c:\my_trace.trc', default) to see ALL columns. Unfortunately here the columns are displayed but contain all a NULL value where I had expected to see values e.g. for ERROR.

I am wondering if someone can solve this for me please. I am trying to do a join on two tables. One is a date calendar table with dates from the year 2000 until 2050. This table has column named Year_Month which I want to group by. The 2nd table is a transaction table containing dates and other details. What I want is to return all Year_Months with a count of the transaction records and a zero where data does not exist. My query is as follows select dd.YR_MONTH, a.dep_code, a.div_code, d.dep_name, actions_assigned = isnull(count(a.action_id),0) from DimDate dd Left outer join sr_assigned_to a on (assign_date =[DATE] or assign_date is null) Left outer join sr_dep_codes d on (d.dep_code = a.dep_code or d.dep_code is null) and (d.div_code = a.div_code or d.div_code is null) where [date] between DATEADD(m,-13,DATEADD(mm, DATEDIFF(m,0,GETDATE()), 0)) and DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) group by a.dep_code, a.div_code, d.dep_name, dd.YR_MONTH The results returned are missing some months (in this example Oct 2016 is missing) Where am I going wrong? **Table Schemas** CREATE TABLE [dbo].[sr_assigned_to]( [request_id] [decimal](8, 0) NOT NULL, [responsible_code] [varchar](16) NOT NULL, [assign_date] [datetime] NOT NULL, [reason_assigned] [varchar](10) NULL, [outcome_code] [varchar](10) NULL, [outcome_date] [datetime] NULL, [status_code] [varchar](10) NULL, [comments] [varchar](60) NULL, [client_notified] [varchar](1) NULL, [notified_via] [varchar](10) NULL, [date_notified] [datetime] NULL, [finalised_ind] [varchar](1) NULL, [assign_time] [datetime] NULL, [booked_date] [datetime] NULL, [booked_time] [datetime] NULL, [officer_notified] [varchar](1) NULL, [outcome_time] [datetime] NULL, [priority_code] [varchar](1) NULL, [action_format] [varchar](1) NULL, [collect_extras] [decimal](2, 0) NULL, [serial_no] [varchar](10) NULL, [position_no] [decimal](3, 0) NULL, [after_pos_no] [decimal](3, 0) NULL, [amount_held] [decimal](10, 2) NULL, [in_time_ind] [varchar](1) NULL, [escalated_ind] [varchar](1) NULL, [length_no] [decimal](6, 2) NULL, [width_no] [decimal](6, 2) NULL, [action_id] [decimal](8, 0) NOT NULL, [div_code] [varchar](10) NULL, [dep_code] [varchar](10) NULL, [taken_hrs] [decimal](8, 0) NULL, [over_hrs] [decimal](8, 0) NULL, [under_hrs] [decimal](8, 0) NULL, [posted_ind] [varchar](1) NULL, [change_booked_date] [varchar](1) NULL, [change_action_reqd] [varchar](1) NULL, [change_action_officer] [varchar](1) NULL, [change_priority] [varchar](1) NULL, [skip_time_taken] [varchar](1) NULL, [udf_ind] [varchar](1) NULL, [gen_wflow_code] [varchar](10) NULL, CONSTRAINT [pk_sr_assigned_to] PRIMARY KEY CLUSTERED ( [action_id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [dbo].[DimDate]( [DATE] [datetime] NOT NULL, [NEXT_DAY_DATE] [datetime] NOT NULL, [YEAR] [smallint] NOT NULL, [YEAR_QUARTER] [int] NOT NULL, [YEAR_MONTH] [int] NOT NULL, [ALT_YR_MONTH] [varchar](7) NOT NULL, [YEAR_DAY_OF_YEAR] [int] NOT NULL, [QUARTER] [tinyint] NOT NULL, [MONTH] [tinyint] NOT NULL, [DAY_OF_YEAR] [smallint] NOT NULL, [DAY_OF_MONTH] [smallint] NOT NULL, [DAY_OF_WEEK] [tinyint] NOT NULL, [YEAR_NAME] [varchar](4) NOT NULL, [YEAR_QUARTER_NAME] [varchar](7) NOT NULL, [YEAR_MONTH_NAME] [varchar](8) NOT NULL, [YEAR_MONTH_NAME_LONG] [varchar](14) NOT NULL, [QUARTER_NAME] [varchar](2) NOT NULL, [MONTH_NAME] [varchar](3) NOT NULL, [MONTH_NAME_LONG] [varchar](9) NOT NULL, [WEEKDAY_NAME] [varchar](3) NOT NULL, [WEEKDAY_NAME_LONG] [varchar](9) NOT NULL, [START_OF_YEAR_DATE] [datetime] NOT NULL, [END_OF_YEAR_DATE] [datetime] NOT NULL, [START_OF_QUARTER_DATE] [datetime] NOT NULL, [END_OF_QUARTER_DATE] [datetime] NOT NULL, [START_OF_MONTH_DATE] [datetime] NOT NULL, [END_OF_MONTH_DATE] [datetime] NOT NULL, [START_OF_WEEK_STARTING_SUN_DATE] [datetime] NOT NULL, [END_OF_WEEK_STARTING_SUN_DATE] [datetime] NOT NULL, [START_OF_WEEK_STARTING_MON_DATE] [datetime] NOT NULL, [END_OF_WEEK_STARTING_MON_DATE] [datetime] NOT NULL, [START_OF_WEEK_STARTING_TUE_DATE] [datetime] NOT NULL, [END_OF_WEEK_STARTING_TUE_DATE] [datetime] NOT NULL, [START_OF_WEEK_STARTING_WED_DATE] [datetime] NOT NULL, [END_OF_WEEK_STARTING_WED_DATE] [datetime] NOT NULL, [START_OF_WEEK_STARTING_THU_DATE] [datetime] NOT NULL, [END_OF_WEEK_STARTING_THU_DATE] [datetime] NOT NULL, [START_OF_WEEK_STARTING_FRI_DATE] [datetime] NOT NULL, [END_OF_WEEK_STARTING_FRI_DATE] [datetime] NOT NULL, [START_OF_WEEK_STARTING_SAT_DATE] [datetime] NOT NULL, [END_OF_WEEK_STARTING_SAT_DATE] [datetime] NOT NULL, [QUARTER_SEQ_NO] [int] NOT NULL, [MONTH_SEQ_NO] [int] NOT NULL, [WEEK_STARTING_SUN_SEQ_NO] [int] NOT NULL, [WEEK_STARTING_MON_SEQ_NO] [int] NOT NULL, [WEEK_STARTING_TUE_SEQ_NO] [int] NOT NULL, [WEEK_STARTING_WED_SEQ_NO] [int] NOT NULL, [WEEK_STARTING_THU_SEQ_NO] [int] NOT NULL, [WEEK_STARTING_FRI_SEQ_NO] [int] NOT NULL, [WEEK_STARTING_SAT_SEQ_NO] [int] NOT NULL, [JULIAN_DATE] [int] NOT NULL, [MODIFIED_JULIAN_DATE] [int] NOT NULL, [ISO_DATE] [varchar](10) NOT NULL, [ISO_YEAR_WEEK_NO] [int] NOT NULL, [ISO_WEEK_NO] [smallint] NOT NULL, [ISO_DAY_OF_WEEK] [tinyint] NOT NULL, [ISO_YEAR_WEEK_NAME] [varchar](8) NOT NULL, [ISO_YEAR_WEEK_DAY_OF_WEEK_NAME] [varchar](10) NOT NULL, [DATE_FORMAT_YYYY_MM_DD] [varchar](10) NOT NULL, [DATE_FORMAT_YYYY_M_D] [varchar](10) NOT NULL, [DATE_FORMAT_MM_DD_YYYY] [varchar](10) NOT NULL, [DATE_FORMAT_M_D_YYYY] [varchar](10) NOT NULL, [DATE_FORMAT_MMM_D_YYYY] [varchar](12) NOT NULL, [DATE_FORMAT_MMMMMMMMM_D_YYYY] [varchar](18) NOT NULL, [DATE_FORMAT_MM_DD_YY] [varchar](8) NOT NULL, [DATE_FORMAT_M_D_YY] [varchar](8) NOT NULL, PRIMARY KEY CLUSTERED ( [DATE] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY] ) ON [PRIMARY] GO I ran the following (joins commented out) select ALT_YR_MONTH as YR_MONTH --, a.dep_code,a.div_code,d.dep_name,actions_assigned = isnull(count(action_id),0) from DimDate --Left outer join sr_assigned_to a on (assign_date =[DATE] or assign_date is null) --Left outer join sr_dep_codes d on (d.dep_code = a.dep_code or d.dep_code is null) and (d.div_code = a.div_code or d.div_code is null) where [date] between DATEADD(m,-13,DATEADD(mm, DATEDIFF(m,0,GETDATE()), 0)) and DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) group by --a.dep_code,a.div_code,d.dep_name, ALT_YR_MONTH and it returns 2016-10 as one of the results But if I run the code uncommented select ALT_YR_MONTH as YR_MONTH, a.dep_code, a.div_code, d.dep_name, actions_assigned = isnull(count(action_id),0) from DimDate Left outer join sr_assigned_to a on (assign_date =[DATE] or assign_date is null) Left outer join sr_dep_codes d on (d.dep_code = a.dep_code or d.dep_code is null) and (d.div_code = a.div_code or d.div_code is null) where [date] between DATEADD(m,-13,DATEADD(mm, DATEDIFF(m,0,GETDATE()), 0)) and DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) group by a.dep_code, a.div_code, d.dep_name, ALT_YR_MONTH It drops out 2016-10 where there is no data for the year and month in sr_assigned_to

I need some help on SQL as this kind of selecting is beyond my knowledge. The result of the select should be in one row as it is shown in the picture. Can someone provide any ideas on how to achieve this? if col_name1 =AA -> add col_name2 as value col_name5 if col_name1 =BB -> add col_name3 value as col_name6 if col_name1 =CC -> add col_name4 value of first CC (c31) as col_name7 and col_name4 of second CC (c31) value as col_name8 original table might not have all 4 ids

I have multiple tables (12 for each month) that have 3 columns each ("code", "test_name", "value") Example: _______________________________ January code | test_name | value 0001 name1 17 0002 name2 45 0005 name5 12 ______________________________ February code | test_name | value 0001 name1 3 0002 name2 7 0004 name4 13 _______________________________ March code | test_name | value 0001 name1 6 0006 name6 32 0007 name7 41 _______________________________ How do I merge or insert or combine it in sql into one table that would look like this: Year code | test_name | January | February | March | ... 0001 name1 17 3 6 0002 name2 45 7 0 0003 name3 0 0 0 0004 name4 0 13 0 0005 name5 12 0 0 0006 name6 0 0 32 0007 name7 0 0 41

I have SQL Server 2008 R2 running on Windows Server 2008 R2 in a virtual environment (ESXI 5.5). Client has been advised to switch the existing E1000 adapter with a VMXNET 3 adapter because they were told it would perform better. Anyhow, I've done this several times on member servers as well as domain controllers. As I understand it, SQL Server is more concerned with the network layer so I don't believe this should be an issue. I am not a SQL guru so I figured I would ask here to be safe.

I'm looking into providing DB level change tracking within my system. I need to be able to track changes at an entity level, rather than just individual tables. Right now, we have triggers on each of the tables of interest, that write the tables PK into a Change_Event table. We can then query that table, and boil it down to the PK that represents the entity as a whole. We can then retrieve the data for that entity and perform the relevant actions upon it. As an example, consider the following (simplified) example: CREATE TABLE Employee ( Id INT IDENTITY PRIMARY KEY, Name VARCHAR(250) NOT NULL, Telephone VARCHAR(15) NOT Null ); CREATE Table Visit ( Id INT IDENTITY PRIMARY KEY, VisitDate DATETIME2 Not NULL, [Address] VARCHAR(500) NOT NULL, VisitorId INT NOT NULL, CONSTRAINT FK_Visit_VisitorId FOREIGN KEY (VisitorId) REFERENCES Employee (Id) ); INSERT into Employee (Name, Telephone) VALUES ('John Doe', '123456789'), ('Jane Smith', '999555333'); INSERT INTO Visit (VisitDate, Address, VisitorId) VALUES (SYSDATETIME(), '123 Fake St', 1), (GETDATE() + 5, '99 Test Av', 2); In this example, the Visit Entity is considered to be the following xml: SELECT Id, CAST(( SELECT Id, VisitDate, Address, ( SELECT E.Id, E.Name, E.Telephone FROM Employee E WHERE E.Id = V.VisitorId FOR XML RAW ('Visitor'), TYPE, ELEMENTS ) Visitor FROM Visit V WHERE V.Id = Visit.Id FOR XML RAW ('Visit'), ELEMENTS ) AS XML) AS Data FROM Visit I need to be able to know, when one of those entities has changed. If I were to change the telephone number of the Employee, I need to be able to see that that has changed the Visit entity so that I can reprocess it. ---- Right now, my change table records the Employee Id. I then run a query that gives me all the visits with that employee as the VisitorId. That sounds fine when you are only looking at two tables, but when you factor in several tables (and potentially several levels of abstraction between them) the performance can get pretty slow. I've looked into Change Data Capture, but that still seems to be capturing at a table level. Equally, I could add Modified date columns to all tables, and combine all the modifieds from the view to produce a single, max modified value - but considering I need to filter on that field, I can't imagine performance would be that great either. Is there a recommended method of dealing with this within SQL Server? As an added consideration - whilst the Instance is SQL 2008, the DB is still in 2000 compatibility mode. **UPDATE** I've been looking into [Change Tracking](https://msdn.microsoft.com/en-us/library/bb964713.aspx) , which works pretty well at a table level. Unfortunately it doesn't work too well with the select above. I even tried creating a schemabound view based on that select, and enabling change tracking on the view, but this appears to be an option that can't apply to views. I can make it work by using something like the below: WITH ChangedVisits AS ( SELECT DISTINCT OV.Id FROM dbo.Visit OV INNER JOIN Employee E ON OV.VisitorId = E.Id LEFT JOIN CHANGETABLE(CHANGES Visit, @LastSyncVersion) AS VCT ON OV.Id = VCT.Id LEFT JOIN CHANGETABLE(CHANGES Employee, @LastSyncVersion) AS ECT ON OV.VisitorId = ECT.Id WHERE VCT.Id IS NOT NULL OR ECT.Id IS NOT NULL ) SELECT Id, CAST( ( SELECT V.Id as [@Id], V.VisitDate, Address, ( SELECT E.Id, E.Name, E.Telephone FROM dbo.Employee E WHERE E.Id = V.VisitorId FOR XML PATH(''), TYPE, ELEMENTS ) Visitor FROM dbo.Visit V WHERE V.Id = CV.Id FOR XML PATH ('Visit'), ELEMENTS ) AS XML) AS Data FROM ChangedVisits CV But the drawback of that is the need to join on each of the tables and an associated CHANGETABLE object in order to work out if something has changed.

We have a linked PostgreSQL server and the remote login and password are parsed using the ODBC settings on the Windows server. Under the Security tab in Linked Server Properties within SQL, which connection setting should be used please? - Not be made - Be made without using a security context - Be made using the login's current security context - Be made using this security context If the last one, why would you want to pass the login credentials twice?

Currently running on SQL Server 2008 R2 I am attempting to increase performance of an UPDATE statement. I notice an Eager Spool operation in the showplan popping up. My understanding of spooling operations is pretty basic - they create temporary storage for the table during the update. I also know that, while they are preventing much worse execution times, eager spools are often indicative of underlying problems with table structure and/or query statements. My question is pretty simple: When you see an Eager Spool in your query plan, what problems do you first look to address? I will be analyzing _every_ part of our system to increase performance - I'm just looking for guidance as to where I should start.

I am deploying a new index maintenance jobs through MSX to about 100 servers, I am in the process of manually checking that each job has been created and I have already discovered that for some currently unknown reason specific servers are not receiving the new MSX job. Strangely, these servers have been able to successfully disable an older index maintenance job that already exists. I am wondering if someone can help me identify the reason for this. On my CMS I have the Server Agent assigned the TargetServerRole in MSDB. I have noticed in my agent error log, the above information. Edit: I have since identified that the servers needed resynchronisation, I defected the target (screen capturing existing MSX jobs before doing so), and then rejoined to the master and re-added the MSX jobs - this seemed to resolve the issue.

I am assessing the migration from SQL 2008R2 to SQL 2017 AlwaysOn where we have replication (publisher, subscriber, distributor) setup on SQL2008R2. There are various restrictions if one wants to setup Replication on top of AlwaysOn..For e.g below   1. Merge replication is not supported. 2. Transactional replication with immediate or queued updating subscriber is not supported. 3. Peer to peer replication is not supported. 4. Bidirectional transactional replication is not supported.   Does anybody have any simple select script which can confirm that my replication setup on SQL 2008R2 is anything out of the above type?  I tried checking "mspublication" table but that isn't providing any guidance to confirm whether my replication setup is out of the above 4 types. I don't have access on client's SQL instances so want to provide them simple select statement.

On a SQL Server 2008 R2 instance, a misconfigured credential used as a SSIS proxy for an SSA job was dropped before I could look at the system views to figure out when it was originally misconfigured. I restored a backup of the master DB under an alternate user DB name to see if I can extract the data but it appears the sys.credentials view is looking at the live master DB instead of the restored DB even with the DB context set to the restored DB. Is there an alternate method to retrieve the data from this and other system views from a backup without the usual DR methodology?

I have a 4 Tb SQL Server 2008 R2 database. I'm going to move this DB to a new server with a new release of SQL Server 2012. (the application does not support SQL Server > 2012, but that is not the subject of my question). Before that, I'm going to move almost all objects from the main datafile of 4To to multiple datafiles on the same filegroup so I can get back the free space on the 4Tb file. After moving objects, free space take like forever to be reclaimed with a dbcc shrink file with blocs of 256Mo. What's the fastest way to get that space back to the OS? Is there a way to use a backup restore to shrink the file?

I am facing some issues related to SSRS on SQL Server 2008R2, how can we have install below features: Reporting Services- SharePoint Reporting Services Add-in for SharePoint Products Any one could you please help me. Thanks

I am trying to migrate a database from an old SQL Server 2008 R2 to SQL Server 2017. The database was originally 2000 and thus has a compatibility level of 80. Initially I took a full database backup and tried to restore it to the new server, but got the following error: > Msg 9014, Level 16, State 2, Line 32 An error occurred while > processing the log for database 'nrf'. The log block version 2 is > unsupported. This server supports log version 3 to 5. I went back to the original server and restored the backup to a new name, nrf_temp, and then altered that database to compatibility level 100 (for 2008 R2). I took a new backup and again tried to restore to my new server, but got the same error again. Then I went back to the original server, deleted my new name database, nrf_temp, and did a restore from that last backup that was taken after I put the compatibility level to 100 - thinking maybe a recreation of the physical files might help. I took another backup and again tried the restore to my new server, but took the same error again. Is there any way to get the restore to work on the 2017 server or will I have to define the database completely new and import data? Note that I was able to successfully back up the other databases from that old server, that were already at compatibility 100, and restore those backups to my new 2017 build. I did back up to a completely new backup set & file for each backup that I took.

Would anyone know anything about Microsoft Dynamics Rms 2.0 (2012)? We are having trouble connecting our sql server to the client side cashier (windows 10 pro) this software sunset in 2016 I believe and Microsoft won’t help. (We used an emergency ticket) So the “cashier”, doesn’t see the database, however it will see the server it’s self. When ever using ole ob connection the server is seen but not able to be connected to. We have opened every port on both computers to test to see if we could establish a connection, but to no avail... I have spent 16 hours on this with several people and can’t figure it out. Here is the original question asked here https://www.facebook.com/groups/thisisanitsupportgroup/permalink/2698630683716128/ Covers a lot of what I have already done so