Microsoft SQL Server

162K

Solutions

49K

Contributors

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Share tech news, updates, or what's on your mind.

Sign up to Post

I am trying to derive/predict a time/timestamp in SQL when our daily load might complete. Can someone help with this?

I have a datetime field from a table [Start].
I then have a dynamically generated [Cycle_Dur_AVG column which show the average time based on the last rolling three months,
CONVERT(VARCHAR(12), AVG(DATEDIFF_BIG(SECOND, [DDS_Dim_Start], [End])) / 60 / 60 / 24) + 'd :' + 
      CONVERT(VARCHAR(12), AVG(DATEDIFF_BIG(SECOND, [DDS_Dim_Start], [End])) / 60 / 60 % 24) + 'h :' + 
      CONVERT(VARCHAR(2), AVG(DATEDIFF_BIG(SECOND, [DDS_Dim_Start], [End])) / 60 % 60) + 'm :' + 
      CONVERT(VARCHAR(2), AVG(DATEDIFF_BIG(SECOND, [DDS_Dim_Start], [End])) % 60) + 's' AS [CYCLE_DUR_AVG]

Open in new window

which outputs something like '0 d 12 h 23 m 5 s'.

Is there a way I can add those average seconds to the Start column and output a timestamp/datetime field?

Thanks,
0
Big Business Goals? Which KPIs Will Help You
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

One of my clients is a single server environment, Windows server 2016 Essentials, running SQL Server (Express) 2014.  

They are running Thompson Reuters PracticeCS with the database hosted on this server.

Until about 4 weeks ago, things were running along normally.  Then, logins started failing until a variable time between 8:00 am and 8:30 am.  Once the logins start working, they work for everyone the reset of the day.  

Backups run at 12:00 am and finish within minutes.  The database is "Always on".

I have contacted the vendor and they claim that it is a SQL problem and refuse to help troubleshoot.

Any help is appreciated.  Below are the recent logs.  At 8:37, users could log in.

Date      Source      Severity      Message
9/18/2018 8:37      spid53      Unknown      Starting up database 'ReportServer$SQLEXPRESSTempDB'.
9/18/2018 8:31      spid20s      Unknown      Starting up database 'ReportServer$SQLEXPRESSTempDB'.
9/18/2018 8:27      spid60      Unknown      Starting up database 'ReportServer$SQLEXPRESSTempDB'.
9/18/2018 8:17      spid53      Unknown      Starting up database 'ReportServer$SQLEXPRESSTempDB'.
9/18/2018 8:15      spid51      Unknown      Starting up database 'ReportServer$SQLEXPRESSTempDB'.
9/18/2018 8:11      spid53      Unknown      Starting up database 'CSP_203449_DBSPC'.
9/18/2018 8:11      Logon      Unknown      Login failed for user 'CreativeSolutionsPracticeCsDatabaseOwner'. Reason: Failed to open the explicitly specified database 'CSP_203449_DBSPC'. [CLIENT: 10.0.0.62]
9/18/2018 8:11      Logon      Unknown      Error: 18456<c/> Severity: 14<c/> State: …
0
MSSQL count differences by a group of items.

I have a SQL trigger that captures adds, changes and deletes that happen for items in a ERP table. There is a primary item number (TCC) and a secondary partner number (TCC_SE).

In some cases the partner number has been changed and I need to find which item numbers have a partner number that has changed.

I have attached a spreadsheet that has two item numbers with multiple entries and within each item number there is a secondary partner number that has been changed.

At this point I simply want to see the item number if there is a secondary partner number that has changed. (i.e. in the attached spreadsheet there is a primate item number of M4/6D2 which also  a secondary partner number of M4/6D2 and M46D2)

Hope this make sense

Thanks
Book3.xlsx
0
TableProductXML(ProductID, XMLDATA)
 ------------------------
 21,
 <DataModel xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
   <ProductId>21</ProductId>
   <PType1>
     <WH>
       <WH_ID>1</WH_ID>
       <Name>Name1</Name>
       <WHL_ID>3</WHL_ID>
     </WH>
     <WH>
       <WH_ID>2</WH_ID>
       <Name>Name2</Name>
       <WHL_ID>2</WHL_ID>
     </WH>
     <WH>
       <WH_ID>3</WH_ID>
       <Name>Name3</Name>
       <WHL_ID>1</WHL_ID>
     </WH>
   </PType1>
 </DataModel>

 
How to write tsql select statement for XMLDATA value with case statement If (WH\Name = 'Name2') and (WH\ID = 2) then return True else return false
ProductID   , Result
21,     True
0
I need to create a query that calculate the car average mileage  per day. I have the query but it is calculating the average per ride not per day. Because you may have multiple rides per the same day and tat will be the sum not the average for that day


SELECT carkey , TotalMilesDriven , ISNULL([Monday],0) [Monday] ,
			ISNULL([Tuesday],0)[Tuesday],ISNULL([Wednesday],0)[Wednesday],ISNULL([Thursday],0)[Thursday],ISNULL([Friday],0)[Friday]
			,ISNULL([Saturday],0)[Saturday],ISNULL([Sunday],0) [Sunday],carnumber,model,make FROM 
			(
				SELECT DISTINCT model,make,carnumber,carkey, _WEEKDAY , AVG(milesDriven) OVER() TotalMilesDriven, AVG(milesDriven) OVER (PARTITION BY _WEEKDAY) MilesPerCarWeekDay
				FROM 
				(	
					select CD.carkey,(CD.endmileage - CD.startmileage) milesDriven,DATENAME(WEEKDAY,SU.sessionStart) _WEEKDAY,C.carnumber,C.model,C.make
					from car_details CD
					inner join Car C on C.carKey = CD.carKey
					inner join Users U on U.userKey = CD.updateuser
					inner join SessionUnit SU on SU.sessionKey = CD.sessionKey
					WHERE 1=1
					and CD.carKey = 32
					and SU.sessionStart BETWEEN '09/14/2018' AND '09/15/2018'
				)AS T
				WHERE T.milesDriven > 0
			)k
			PIVOT ( MAX(MilesPerCarWeekDay) FOR _WEEKDAY IN ([Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday],[Sunday]) )y1

Open in new window

0
Hi All,

My server get infected a virus. Please see the attached file.
Is there anyway to decrypted the infected virus ?

Thank you.
Read-Me.txt.id-E0D6296F.-ozzygepp-.gamma
Virus-Message.jpeg
1
Hi Experts,

I'm getting the following error when trying to import an Access table to SQL Server (2008).

- Copying to [dbo].[Skilled_Nursing_Visit_Note] (Error)
Messages
Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Invalid date format".
 (SQL Server Import and Export Wizard)
 
Error 0xc020901c: Data Flow Task 1: There was an error with input column "Date_Of_Birth" (382) on input "Destination Input" (284). The column status returned was: "Conversion failed because the data value overflowed the specified type.".
 (SQL Server Import and Export Wizard)
 
Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "input "Destination Input" (284)" failed because error code 0xC020907A occurred, and the error row disposition on "input "Destination Input" (284)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.
 (SQL Server Import and Export Wizard)
 
Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Destination - Skilled_Nursing_Visit_Note" (271) failed with error code 0xC0209029 while processing input "Destination Input" (284). The identified

Open in new window

0
Hi,

Many thanks for taking a look at my question.

Given the below SQL query that works

;with cte as
(
      select tr.tr_rowid_debtor as lp_rowdebtor_id, tr.tr_posted_date,
      (0-tr.tr_to_agency+tr.tr_to_client) as amount,tr.tr_account,
      ROW_NUMBER() over (partition by tr.tr_rowid_debtor order by tr.tr_posted_date desc) idx
      from de_transaction as tr
    where tr.tr_account = 16
)
Select d.de_number, lp_rowdebtor_id, tr_posted_date, amount, tr_account 
from cte 
inner join debtor as d on d.de_rowid  = lp_rowdebtor_id
where idx = 1

Open in new window


I need it to do exactly what it does above but also - 1 additional item I need, is that somehow I need a count of the each transaction it finds per by "tr.tr_rowid_debtor" and only give back a result for the files that have multiple instances of where tr.tr_account = 16.

many thanks!!
0
Trying to update an Iseries (as400) from SQL Server.

If I type at SQL Management Studio:
   update OPENQUERY(KRICOF8K,'select * FROM AMMOBJ78KF.PFUUIDR WHERE UUAKNB=71162') SET UUGGNB=104
I receive this error:
OLE DB provider "MSDASQL" for linked server "KRICOF8K" returned message "[IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL7008 - PFUUIDR de AMMOBJ78KF no válido para la operación.".
Msg 7343, Level 16, State 4, Line 1
The OLE DB provider "MSDASQL" for linked server "KRICOF8K" could not UPDATE table "[MSDASQL]".

Typing:
 UPDATE KRICOF8K.S10AF99T.AMMOBJ78KF.PFUUIDR SET UUGGNB=104 WHERE UUAKNB=71162
I receive:
OLE DB provider "MSDASQL" for linked server "KRICOF8K" returned message "[IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL7008 - PFUUIDR de AMMOBJ78KF no válido para la operación.".
Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "UPDATE "S10AF99T"."AMMOBJ78KF"."PFUUIDR" set "UUGGNB" = (104.)  WHERE "UUAKNB"=(71162.)" against OLE DB provider "MSDASQL" for linked server "KRICOF8K".

Of course, my query is more complex than that two examples, but I can't update my linked server with this two simple queries.

KRICOF8K es the linked server name and Works fine when I select rows, for example:
SELECT * FROM OPENQUERY(KRICOF8K,'select * FROM AMMOBJ78KF.PFUUIDR  WHERE UUAKNB=71162')
display a row with several columns with values.

What is the correct syntax and why am I receiveing that error?
Thanks in advance.
0
I have written a procedure to update SSRS subscription schedules. The procedure appears to work because the values are updated in ReportServer.dbo.Schedule. However when I view the schedule through SSRS Report Manager, the dates are not updated.

The code I am using is below -

declare 
--@SubscriptionID varchar(100) = 'E65E6D4A-A631-4C17-A8DB-001FB0AD0855'
@Path varchar(max) = '/QTS Internal/RTM/Service Disruption Report'
, @SPLC varchar(max) = '123456'
, @State varchar(max) = 'VA,NC'
, @Country varchar(max) = NULL
, @Carrier varchar(max) = NULL
, @Location varchar(max) = NULL
, @CustomerID varchar(max) = '1'
, @Disruption varchar(max) = 'Hurricane Florence'
, @StartDate datetime = '20180914 04:19:00'
, @EndDate datetime = '20181002'

create table #subs (SubscriptionID varchar(200), [Path] varchar(200))

insert into #subs

select s.SubscriptionID, c.Path

from Catalog c
join subscriptions s on c.ItemID = s.Report_OID
where c.Path = @Path

-----------------------------------------------------update schedule dates

UPDATE ReportServer.dbo.Schedule
SET StartDate = @StartDate, EndDate = @EndDate
from Catalog c
LEFT OUTER JOIN Subscriptions s WITH (NOLOCK) ON s.Report_OID = c.ItemID
LEFT OUTER JOIN ReportServer.dbo.[ReportSchedule] RS WITH (NOLOCK) ON s.[SubscriptionID] = RS.[SubscriptionID] 
LEFT OUTER JOIN ReportServer.dbo.[Schedule] SC WITH (NOLOCK) ON RS.[ScheduleID] = SC.[ScheduleID]
JOIN #subs subs on s.SubscriptionID = subs.SubscriptionID

Open in new window


Does anybody have experience with this?
0
Determine the Perfect Price for Your IT Services
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

I have dynamic sql that builds a select correctly

DECLARE @DIspo VARCHAR(MAX);
SET @DIspo = '
SELECT ''MAX(cte.'' + COLUMN_NAME + '') AS '' + COLUMN_NAME FROM [tempdb].INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE ''#table%'' AND COLUMN_NAME IN (SELECT CODE FROM dbo.DispositionTypes
WHERE DispoReport = 1)';
PRINT @DIspo;

Open in new window


When I copy the Printed @Dispo into a query window I get this

SELECT 'MAX(cte.' + COLUMN_NAME + ') AS ' + COLUMN_NAME FROM [tempdb].INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE '#table%' AND COLUMN_NAME IN (SELECT CODE FROM dbo.DispositionTypes
WHERE DispoReport = 1)

When I do run the select
I get three rows in a table

MAX(cte.DNC) AS DNC
MAX(cte.NI) AS NI
MAX(cte.DEALPUR) AS DEALPUR

What I need to do is in ONE process...
Get back a varchar string I can use elsewhere in a dynamic query process

MAX(cte.DNC) AS DNC, MAX(cte.NI) AS NI, MAX(cte.DEALPUR) AS DEALPUR
0
Hi,
How to convert an Integer value to TeXt Value
using DAX
0
WITH DATA 
AS (
select CAST(enc_timestamp AS DATE) as [Date of Drug Administration], p.person_nbr AS [Medical Record], enc_nbr AS [Encounter Number],
'OP' as [Patient Type], pp.service_item_id as [Charge Code], pp.service_item_desc AS [Charge Description], pp.units AS [Dispensed Quantity],
pm.payer_name, cob from patient_encounter pe
JOIN person p ON p.person_id = pe.person_id
JOIN encounter_payer ep ON pe.person_id = ep.person_id AND pe.enc_id = ep.enc_id
JOIN payer_mstr pm ON pm.payer_id = pe.cob1_payer_id
JOIN patient_procedure pp ON pp.enc_id = pe.enc_id
where CAST(enc_timestamp AS DATE) >= dateadd(day,datediff(day,365,GETDATE()),0) 
and pe.billable_ind = 'Y' and pe.clinical_ind = 'Y' and pe.practice_id = '0001'
and pp.service_item_id IN ( 'J0585','J0696','J0702','J1030','J1040','J1050','J1071','J1100','J1200','J1650','J1885','J2060','J2175','J2270','J2300',
							'J2405','J2550','J2791','J2930','J3030','J3301','J3420','J3430','J3490G','J3490TH','J7298','J7613','J7620'
							)
and pp.amount <> 0.00 and pp.delete_ind = 'N')
select * 
from DATA d
	Pivot (MAX(payer_name)
			FOR cob IN ([1], [2], [3] )) as P;

Open in new window



This is working---however the payer name is the same in 1, 2 or 3 (depending on the patient's number of insurances).  For example, if a patient has Medicare and a supplement--it will at least recognize there are two entries (cob 1 and cob 2)--but instead of the supplemental name, it is supplying the same insurance name (Medicare) for both entries.  If the patient has 3 insurnaces, then it would have 3 Medicare names across 1, 2 and 3.
0
I have Write the DAX Function
See below
TotalDays = DATEDIFF([ChangeDate],TODAY(),DAY)
But same date giving 2 differents values
See attached Screenshot and see example
Change Date   TotalDays
12/09/2018     10
12/09/2018      2
Any idea appriciated
DAX-Issue.JPG
0
Hi, I have a MS SQL Server running in a data centre and I would like to move it into a new SQL Instance running on AWS EC2. I know i can take a backup of the DB and restore it to the AWS Instance but i was wondering if there were any other options for migrating the database to the new server. I know AWS have a database migration tool, just wondering if anyone had had experience using it ?
0
I have been tasked with creating some reports out of VS and SSRS from an Oracle 12c database.  I am an Oracle developer but I don't know much about VS or SSRS.  I have both VS 2015 (14.0.25431.01 Update 3) and 2017 (15.7.5) installed and I have installed ODTwithODAC for 12c.  I have also installed SSDT for both VS 2015 and VS 2017.  

Using VS 2017, i have created a new project but I am unable to create a shared data source.  As soon as I select Oracle Database in the Type drop-down, the Edit button next to the connection string becomes grayed out.  

Using VS 2015, I have cloned an existing project that works fine for my colleague.  However, when I first tried to load the project I received a message that the version of the report server project is not supported and the project must be upgraded.  If I say no, then the project won't load.  If I say yes, the project loads and everything seems to be ok. However, when I try to Preview an existing report from the project, I receive the following error:
"An error has occurred during local report processing.  An error has occurred during report processing.  An attempt has been been made to use a data extension 'ORACLE' that is either not registered for this report server or is not supported in this edition of Reporting Services."  There is nothing in the Error List when this happens.  

The only difference between my colleague's setup and mine is that he has SQL Server Reporting Services 14.0.806.134 and I have …
1
Please help with writing a query to get aggregated OrderAmount for each SalesPerson for the last month (last 28-31 days). For example, if today is 09/13/2018, we need sum from 08/13/2018 to 09/12/2018.

TableOrders
----------------
SalesPerson
OrderAmount
OrderDate


Thank you in advance.
0
Hi,

How can convert date to yyyy-mm-dd, i tried some options in converter and nothing:

The following query works but i think the format is mm-dd-yyyy
select cod,ISNULL(datep,CONVERT(datetime, '03/09/2018 18:06:53',120)) as datep from Refmb
this code not work get error when converting, changing 03/09 to 13/09
select cod,ISNULL(datep,CONVERT(datetime, '13/09/2018 18:06:53',120)) as datep from Refmb

best regards
0
I developed software recently for a company where the software I developed is using C# to write to/read from a database attached to a SQL Server Express instance installed on the local machine while the customer is connecting to that instance as a linked server to push/pull information to and from it.  

Well, it seems that after the customer pushed some data to the SQL Server Express instance database while the software was open and connected, I was no longer able to access the data correctly until I close the software down and restarted it (thereby closing the connection and re-opening it).  

I don't know the exact sequence of steps preceding this event and this was the first connection to the local sql server database through the linked server and there had been no issues prior.  So, is there anything that anyone can think of that would disrupt a connection this way?  Anything that might be missing in my connection string, for example?  

Thanks.
0
Newly released Acronis True Image 2019
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Im using sql server 5 and I want a query to loop through a table adding a value in each row to a counter and break when i reach a max value - eg TotalQty

eg table
'records'
name qty
lou 1
paul 2
alan 1
jess 3
.. loop through this table and stop when adding qty in each row = TotalQty

eg TotalQty = 4

should return counter = 3
 as it would have looped through rows 1 to 3
if eg TotalQty = 2 it should return counter = 2

pseudocode eg
select * from records
counter  = 0
recordcounter = 0

while counter <  TotalQty
 counter = counter + table.qty
recordcounter = recordcounter  + 1
wend

any ideas welcome
0
Hello guys,

I am having a problem to get this issue.
I would like to add the @location variable on the query of ics using the sql server.
here is my code:

Thank you!


 DECLARE @location NVARCHAR(MAX)
 SET @location = 'Philippines'

EXEC msdb.dbo.sp_send_dbmail
  @recipients ='sample@sample.com'   
  ,@profile_name='Golegalcase' 
  ,@subject = 'invite'    
  ,@body = 'invitation from Me'
, @query           = 
    '

    --

    SET NOCOUNT ON;

    --

 SELECT ''BEGIN:VCALENDAR'' + char(13) + char(10)

+ ''PRODID:SomeIDDoesitmatter'' + char(13) + char(10)

+ ''VERSION:2.0'' + char(13) + char(10)

+   ''CALSCALE:GREGORIAN''  + char(13) + char(10)

+ ''METHOD:REQUEST'' + char(13) + char(10)

+ ''BEGIN:VEVENT'' + char(13) + char(10)

+ ''DTSTART:20170419T040000Z'' + char(13) + char(10)

+ ''DTEND:20170419T050000Z''+ char(13) + char(10)

+ ''DTSTAMP:20170419T155247Z'' + char(13) + char(10)

+ ''ORGANIZER:MAILTO:sample@sample.com'' + char(13) + char(10)
	
+ ''UID:ABCD1234'' + char(13) + char(10)

+ ''ATTENDEE;CUTYPE=INDIVIDUAL;ROLE=REQ-PARTICIPANT;PARTSTAT=NEEDS-ACTION;RSVP=TRUE;CN=sample@sample.com;X-NUM-GUESTS=0:mailto:sample@sample.com'' + char(13) + char(10)
    
+ ''CREATED:20170419T023104Z'' + char(13) + char(10)
+ ''DESCRIPTION:This is a description''+ char(13) + char(10)
+ ''TRANSP:OPAQUE'' + char(13) + char(10)
+ ''LOCATION: @Location'' -- This is the query that I'd like to pass the variable @Location and also, the DTSTART and DTEND above.
+ char(13) + 

Open in new window

0
I am develop application using VB.Net with SQL Server, and i use dataset to bind data to GridView, but my Field is stored Unicode Character (Khmer Unicode) with collation 'Khmer_100_Bin". To select data from this field correctly i need to use prefix N' , But my concern is i am using dataset with variable @a so i don't know how to use prefix N with that variable, anyone can help me about this?

Note:: My dataset Query is in picture attached.

Thanks in advance.

0
I have a SQL Maintenance plan scheduled for Sunday at 4pm (SQL Server 2012 running on W2K12 R2).  Turns out it has been failing every Sunday. When I ran it by hand yesterday (Tuesday), it worked just fine. I changed nothing during the test run.  The job consistently runs for 10-11 minutes before failing.

The SQL error is

      Date            9/9/2018 4:00:00 PM
      Log            Job History (Maintenance.Weekly Maintenance Tasks)
      
      Step ID            1
      Server            VRNS-SQL-PROD
      Job Name            Maintenance.Weekly Maintenance Tasks
      Step Name            Weekly Maintenance Tasks
      Duration            00:11:56
      Sql Severity      0
      Sql Message ID      0
      Operator Emailed      
      Operator Net sent      
      Operator Paged      
      Retries Attempted      0
      
      Message

Executed as user: NT Service\SQLSERVERAGENT.

The application event log entries that coincide with these failures come in a pair (one warning, one error):

      Log Name:      Application
      Source:        SQLISPackage110
      Date:          9/9/2018 4:11:55 PM
      Event ID:      12291
      Task Category: None
      Level:         Error
      Keywords:      Classic
      User:          NT SERVICE\SQLSERVERAGENT
      Computer:     xxxxxxxxx
      Description:
      Package "Maintenance" failed.
      Event Xml:
      
      Log Name:      Application
      Source:        SQLSERVERAGENT
      Date:          9/9/2018 4:11:57 PM
      Event ID:      208
      Task Category: Job Engine
      Level:         Warning
      Keywords:      Classic
      User:          N/A
      Computer:      xxxxxxxxxx
      Description:
      SQL Server Scheduled Job 'Maintenance.Weekly Maintenance …
0
I am trying to migrate SQL maintenance plans from one server to another. The current server is Windows 2012 R2 Standard with SQL 2014 (12.0.4213.0) Standard. The new server is Windows 2016 Standard with SQL 2017 Standard (14.0.1000.169).
I have used SSIS to export a maintenance plan, modified the connection string in the DTSX file, then imported it into the new server. The import process succeeds and when I execute it (it is a backup job with a UNC path as the target), it completes successfully in 1 second but it does not do anything. The job history shows no entry.
Is there a better way to do this as I have over 50 maintenance plans to migrate? If I have to do this one by one, then what else do I need to do to make this work on the target server? Both servers are in the same network in the same domain with different names and IP's.
Thanks in advance.
0
Dear experts

I would like to export the result below to a html format is this possible? Would like to know how. Thanks alot

select A.*, DataFileSizeMB + LogFileSizeMB as TotalSize
from (
SELECT
    DB.name,
    SUM(CASE WHEN type = 0 THEN MF.size * 8 / 1024 ELSE 0 END) AS DataFileSizeMB,
    SUM(CASE WHEN type = 1 THEN MF.size * 8 / 1024 ELSE 0 END) AS LogFileSizeMB
FROM
    sys.master_files MF
    JOIN sys.databases DB ON DB.database_id = MF.database_id
GROUP BY DB.name
) as A
ORDER BY DataFileSizeMB DESC

Open in new window

0

Microsoft SQL Server

162K

Solutions

49K

Contributors

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.