Microsoft SQL Server 2008

49K

Solutions

17K

Contributors

Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the  Always On technologies and support for unstructured data types.

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

Sign up to Post

I looked online I cant seem to find the MS SQL 2016 backup command with "MAX" compression.

I have seen plenty statements with compression Keyword but none of them have the level specified 0-10 or 4 not even sure what the max is but thats what I am looking for right now.

Native SQL backup with max compression for SQL server 2016.
0
Expert Spotlight: Joe Anderson (DatabaseMX)
LVL 13
Expert Spotlight: Joe Anderson (DatabaseMX)

We’ve posted a new Expert Spotlight!  Joe Anderson (DatabaseMX) has been on Experts Exchange since 2006. Learn more about this database architect, guitar aficionado, and Microsoft MVP.

I'm looking for a way to "evaluate" an assembled string that is stored in a table.
The table has string data with many types of special characters such as nchar(160) or nchar(8204).  Many columns of it in many rows.
As a simple example let's take '>' + char(160) + '<' as the string data, and I want it to return "> <".

Looking for something that would be akin to SELECT EVALUATE_STRING(StringData) FROM Table.

DECLARE @TestTable TABLE(Seq INT IDENTITY, StringData nvarchar(100))
INSERT INTO @TestTable VALUES ( '''>'' + char(160) + ''<''' )  --non-breaking space

SELECT Seq, StringData 
	FROM @TestTable

--Shows the desired result when not selected from a table
SELECT '>' + char(160) + '<' AS 'Direct_Select__Desired_Results' 

--If I try a select statement, it doesn't work.
SELECT Seq, (SELECT StringData) AS 'Subqueried_Data' 
	FROM @TestTable

--How do I get the desired results when selecting from a table?  
SELECT Seq, /*??*/ StringData AS '??Evaluated_StringData??' 
	FROM @TestTable

Open in new window

Suggestions?  I hope it's simple.

Thanks.

SQL Server 2008
0
How can I merge all productcode from product detail tables column and display as below

Product table
ProductID Name
1     Abc
2.    BCD

Product detail table
ProductID ProductCode
1     P1
1.    P2
2.    S2
2.     S3

Select The result should be below

ProductID.  name  productcodes
1.     Abc.   P1, P2
2.     BCD.   S2, S3
0
Hi Experts!

firstly, much appreciated for taking a look at my question.

Basically my question is this:

I have these 2 queries:


select debtor.de_status ,count(de_number) as 'Number of A/Cs'
from debtor
join client on client.cl_rowid =  debtor.de_rowid_client
join debtor_status on debtor.de_status = debtor_status.ds_code
where de_active = 'A' and debtor.de_listed_date between '2019-02-01' and '2019-02-13'
group by de_status

select debtor.de_status ,count(de_number) as 'Number of A/Cs'
from debtor
join client on client.cl_rowid =  debtor.de_rowid_client
join debtor_status on debtor.de_status = debtor_status.ds_code
where de_active = 'C' and debtor.de_listed_date between '2019-01-01' and '2019-01-31'
group by de_status

Both of which work, but I need to know, is there a way to create a stored procedure that would run both of these in a loop back for X amount of months.

Any help you could give me to head me in the right direction would be greatly appreciated.
0
Hi,

I need to pull out the most recent record in the contact table only matching the details of the query - and then count them.

here is my current query to check the data being returned:
		SELECT debtor.de_number ,debtor.de_name ,client.cl_number ,debtor.de_owing ,contact.co_done_date ,contact.co_description
		FROM
			contact
		LEFT JOIN
			debtor ON de_rowid = co_rowid_debtor
		LEFT JOIN
			client ON cl_rowid = de_rowid_client
		WHERE
			contact.co_description = 'LOC'
			AND contact.co_done_date between '2019-02-01' and '2019-02-11'
			and contact.co_who = 'MNSW'

Open in new window


and this is my query I am trying to use to return the count:

		SELECT count(max(contact.co_done_date))
		/* debtor.de_number ,debtor.de_name ,client.cl_number ,debtor.de_owing ,contact.co_done_date ,contact.co_description */
		FROM
			contact
		LEFT JOIN
			debtor ON de_rowid = co_rowid_debtor
		LEFT JOIN
			client ON cl_rowid = de_rowid_client
		WHERE
			contact.co_description = 'LOC'
			AND contact.co_done_date between '2019-02-01' and '2019-02-11'
			and contact.co_who = 'MNSW'
		group by contact.co_done_date

Open in new window


and here are the tables:
Debtor Table:

de_number    de_name    debtor owing    de_rowid    de_rowid_client
1            Test Name     320.00        1    1
2            Test 2        132.00        2    2
3            Test 4        165.00        3    1
44           Test 6        178.23        4    1

Client Table

cl_rowid    cl_number
1           100
2           200
3           201

Contact Table:

co_rowid_debtor    co_description    co_done_date    co_who
1                  LOC               2019-02-01      MNSW
1                  LOC               2019-02-01      MVIC
1                  LOC               2019-02-03      MNSW
2                  LOC               2019-02-04      MNSW
3                  LOC               2019-02-04      MNSW
4                  LOC               2019-02-05      MNSW
2                  LOC               2019-02-02      MNSW
3                  LOC               2019-02-02      MNSW
1                  LOC               2019-02-013      MNSW

is it a matter of doing a grouping and a max on the co_done_date? Could someone possibly point me in the right direction?

many thanks!
0
Hello All:

The below query returns row count of cases that are created for every 30 min. The below query displays slots only where row count exist (query not displays slots where row count is 0).

I need to display all slots with row count - even if row count is 0 (from 00:00:10.000 to 23:59:10.000). Can you please suggest on which line and what logic i need to add in below query

WITH Data
AS ( SELECT T.* ,
            DATEADD(MINUTE, DATEDIFF(MINUTE, CAST(T.CreatedOn AS DATE), T.CreatedOn) / 30 * 30, CAST(CAST(T.CreatedOn AS DATE) AS DATETIME)) AS BucketStart,
            DATEADD(MINUTE, 30 + DATEDIFF(MINUTE, CAST(T.CreatedOn AS DATE), T.CreatedOn) / 30 * 30, CAST(CAST(T.CreatedOn AS DATE) AS DATETIME)) AS BucketEnd
     FROM   IB T )

SELECT   CONVERT(VARCHAR(20), D.BucketStart, 120) + ' - ' + CONVERT(VARCHAR(10), BucketEnd, 108) AS [DateTime],
         COUNT(*) As [Total Case Count]
FROM     Data D      
WHERE owneridname = 'MBO' 
AND CreatedOn between '2019-02-04 00:00:10.000' and '2019-02-04 23:59:10.000'
GROUP BY D.BucketStart, BucketEnd
ORDER BY D.BucketStart;

Open in new window

0
I have the following query that is way too slow

select distinct ipkCommanTrackingID,ImeiNumber,Name as vDeviceName,dGPSDateTime,vLongitude,vLatitude,vOdometer,bIsIgnitionOn,vVehicleSpeed,
	 ifk_TrackerType as iTrackerType, vpkDeviceID, vTextMessage,vReportID,cl.vEventName FROM tblCommonTrackingData  with (nolock)
	 inner join wlt_tblDevices A on ImeiNumber=vpkDeviceID  
	 inner join wlt_tblAssets B on B.Id=ifk_AssignedAssetId 
	 inner join wlt_tblEvents_CommonEvents_Lookup cl on ipkCommonEventLookupId=vReportID 
	 where  dGPSDateTime >=@dateFrom and  dGPSDateTime <=@dateTo
	 and ImeiNumber=isnull(@vpkDeviceID,vpkDeviceID) and A.iParent =@iParent 
	 
	 UNION 

	 select distinct ipkCommanTrackingID,ImeiNumber,Name as vDeviceName,dGPSDateTime,vLongitude,vLatitude,vOdometer,bIsIgnitionOn,vVehicleSpeed,
	 ifk_TrackerType as iTrackerType, vpkDeviceID, vTextMessage,vReportID,cl.vEventName FROM tblCommonTrackingData with (nolock)
	 inner join wlt_tblDevices A on ImeiNumber=vpkDeviceID  
	 inner join wlt_tblAssets B on B.Id=ifk_AssignedAssetId 
	 inner join wlt_tblEvents_CommonEvents_Lookup cl on ipkCommonEventLookupId=vReportID 
	 where  ipkCommanTrackingID>@top_ipkCommanTrackingID and ipkCommanTrackingID <= @extended_ipkCommanTrackingID
	 and ImeiNumber=isnull(@vpkDeviceID,vpkDeviceID) and A.iParent =@iParent
	 ORDER BY vpkDeviceID desc,dGPSDateTime DESC,ipkCommanTrackingID desc

Open in new window


i've tried using group by instead of distinct but it only works if i select one column, so maybe i need another solution?
any help on this?
1
Hello everyone,

This is my first post, so sorry if anything is wrong. I work on a project where I create a report that is based on data from two different databases and local Excel tables. Both databases are from different third party software's. I can access one database via power query and I export Excel reports from the other one. My report consist of five different sheets, each sheet needs special formatting and calculations. I need over 20 different queries to get all necessary data. Many different queries access the same tables and the third party software is also blocking tables sometimes, therefore I can get the following error:

1004 [DataSource.Error] Microsoft SQL: Transaction (Process ID 302) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Open in new window


To get around these issues I programmed VBA code to refresh the queries in batches, that they don't interfere each other and I added an error handler to catch the error and rerun the code till it's successfully refreshed. I tested this code with two simple queries on a database table and it worked perfectly. It caught the errors and always refreshed everything. After I added one of my production queries, which consist of two database tables and two local Excel tables, I rerun the code and for a reason I can't figure out, the Datasource Error is not caught anymore and stops my program. See attached

The next day I tried a different …
0
Dear All


              I was trying to upgrade SQL Express 2008 sp2, but during the installation, I received 2 failed, any idea how to get this fix ?

1
0
I want create dynamic columns for 'Itg_Tiles_Grade' Rows data for production and column name like production + ' Itg_Tiles_Grade '  and set Quantity as per 'Itg_Tiles_Grade'.

now  also add one column for total of 'Itg_Tiles_Grade' as TotalProdction

then again CREATED dynamic columns for 'Itg_Tiles_Grade' Rows data for Quantity and column name like production + ' Itg_Tiles_Grade ' and data is calculated as per images
 
I have atttached table script as excel for data please just copy and paste in table.
Table Script :  Table-Script.sql

Table-Data.xlsx

Then also attched output file which i want.
output.xlsx
0
Why Diversity in Tech Matters
LVL 13
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

Hello

I have run into an issue I can not figure out. I am running a 2014 sql express server that is connected to and importing data from a 2008 R2 sql server.  Each server is Hosted within the same Virtual Machine host using the same data storage but on individual VMs. When trying an import the from the express machine the script runs well but hangs because of some type of temp disk space utilization that that fills the drive with over 40GB. On reboot the disk space returns to normal. The script is pulling only about 6000 records from the other data base and when complete the dtabase file is less than 50MB.

We copied the data from the remote server and placed it on the 2014 express server changed the script to look remotely and the import happened as it should.

My questions are why and how does the fail happen?  Why would a large temp file of some type be written when importing between VMs. How do I over come this?  

I will provide any other information you may need and thank you for your help.
0
Hello,

I have two SQL server which I need to determine their SQL versions.  Here is what I got using the sql@@version command.  

Can someone suggest a good weblink to look them up?


Microsoft SQL Server 2008 (SP3) - 10.0.5538.0 (X64)
                Apr  3 2015 14:50:02
                Copyright (c) 1988-2008 Microsoft Corporation
                Standard Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2) (VM)
 

Microsoft SQL Server 2012 (SP4-GDR) (KB4057116) - 11.0.7462.6 (X64)
                Jan  5 2018 22:11:56
                Copyright (c) Microsoft Corporation
                Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
0
Thank you in advance,
I'd like to know if it is possible to upgrade the MS SQL server independent of a Windows Server version.
I have a legacy client with Windows Server 2008 R2. They of course have their SQL embedded as a 2008 R2 version of SQL as well.
This is a difficult client, and just for grins I'd like to know if SQL can be upgraded separately from the OS.
Thank you for your help regarding this.
Sam
0
Dear EE,

I have one App and one DB server and need to disable TLS 1.0 and enable TLS 1.1 and TLS 1.2 on them.

App = windows 2008 r2
Db = windows 2008 r2 --- SQL 2008 R2

Please suggest,

Thanks
0
Hi There,
We use Macola 7.8.200 progression WorkFlow and Sql 2008 server (on Win 2012 r2)

We have auditors, and they want me to get a report of all the users who have access to macola and the modules they have access to and the access level( maintain, view etc).
I'm not sure if there is a table or from macola we can do a report to get this.
I checked the Screens database and looked through the table.

I see Syscomp_Sql table has the user and menu name, and menu level but not sure what the menu level indicates.I see 0 through 3 there.
I'm not sure which will be a good table or if we can run something from macola to get list of all users and their access level.
Otherwise we may have to go to Visual Menu builder and look at each user and do screen shot of each users access. That's a lot of work we have lot of users.
I will appreciate if anybody have a easier method
0
Why are my SQL Backups failing in my maintenance plan? SQL 2008 - The Job was invoked by Schedule 13

I received the following email alert about my Maintenance Plan backups:

JOB RUN:            '4DCVR_BackupPlan.4DCVR_Subplan_Tran' was run on 27/11/2018 at 14:00:00

DURATION:        0 hours, 1 minutes, 46 seconds

STATUS:               Failed

MESSAGES:        The job failed.  The Job was invoked by Schedule 13 (4DCVR_BackupPlan.Subplan_2).  The last step to run was step 1 (4DCVR_Subplan_Tran).



I attempt to manually run a Full Backup on the database which is set to Full Recovery mode, which appears to be backing up as the file appears in the backup folder, but still the  backup fails. The transaction logs are apparently failing according to the email, but they are still visible in the backup folder (although may not be complete).

I have maintnenance plan backups setup exactly the same way for other databases on different instances that are successfully working,

Can anyone tell me what may be going on and how to rectify it?

The backups from the maintenance plan have stopped working for way over a couple of weeks.
0
I'm having an issue with a DAX formula in SSAS/Tabular.

I have a calculated measure (rtn_qty_adj) which provides the values as expected, however when I reference that formula in another calculated measure (rtn_qty_avg_adj), it gives incorrect values.
It appears that "rtn_qty_avg_adj" is ignoring or filtering-out the logic of the calculated measure "rtn_qty_adj".

Basically the dataset consists of returns quantities over a (monthly) timeframe.
Where there are big peaks/excursions in the data (ie; return qty. in a month is greater than uper control limit) , then in the measure  "rtn_qty_adj" I reset these peaks to the Average return qty.
So then I want to perform another averaging on the adjusted returns qtys. where the peaks are "smoothed-out" (ie; equated to the average)
Most of the calculations work just fine, except this one which is causing me a headache.

Appreciate all input/help!!

Formulae
Here's the formulae for the adjusted return qty and adjusted return qty. average
Rtn_qty_adj:=
VAR rtn_avg =
CALCULATE(
		(AVERAGEX(
			SUMMARIZE( drct_excg, dates[incr_base_date], "rtn_avg",  [Rtn_qty] )
		,[rtn_avg] ) )
, ALL(dates))
RETURN
IF( [Rtn_qty_ucl_1sd_otlr] = 1, 
	IF(COUNTROWS(VALUES(dates[incr_base_date])) >1,
		SUMX(VALUES(dates[incr_base_date]),
			IF( [Rtn_qty_ucl_1sd_otlr] = 1, rtn_avg , [Rtn_qty] )  )
	,
	rtn_avg
),
[Rtn_qty]
)

Rtn_qty_avg_adj:=
CALCULATE(
	AVERAGEX(
		SUMMARIZE( drct_excg, dates[incr_base_date] ,"rtn_avg", 

Open in new window

0
My SQL server is 2016 SQL. The SQL server has replication service running. I installed below two KBs
SQLServer2016-KB4458871-x64
SQLServer2016-KB4464106-x64

Both KBs seem not installed successful because it doesn't show "successful" But, instead showing the image attached. But, after applying the KBs, the SQL service is not auto started. Even I manually started it, it failed with the image attached.

So, I have to use the following command to start it.

NET START MSSQL$INSTANCENAME /T902

I don't want to start SQL service every time I have to run this command, but instead using SQL service

Do you know why the SQL service is not started auto. How to fix this issue? I know uninstalled is very complicated even not possible because they have installed into the registry
CaptureSQLPatchingProvisioningdbissu.PNG
CaptureSQLPatchingProvisioningdbissu.PNG
0
I'm looking for reasons why a SELECT query does not return all rows that satisfy the selection criteria.

 I have created such a query and incorporated it in a stored procedure that runs periodically at 15 minute intervals.  Most of the time it returns all of the expected rows.  At other times a row is missing even though the data in the tables being queried remains unchanged as does the selection criteria for three successive executions of the query.  

The routine processes 20 to 30 transactions a day correctly, but every couple of days it misses a transaction.

I suspect this might be due to locking, but it seems improvable that records could remain locked for a 30 minute period.  

Am I overlooking any other possible reason or reasons for this behavior.  

Thanks in advance for your time and consideration.
0
Learn Ruby Fundamentals
LVL 13
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

I have a view that calls a function which takes a long time to complete.
Can anyone suggest how can I convert this cursor to a set based approach or CTE for better performance?

the view without the call to the function runs in seconds,  adding the call to the function kills the performance.

ALTER FUNCTION [dbo].[CT_OpsBetween] (@TYPE nchar(1), @BASE nvarchar(30), @LOT nvarchar(3), @SPLIT nvarchar(3), @SUB nvarchar(3), @eSeq smallint)
RETURNS nvarchar(255) 
AS
BEGIN

DECLARE @OpsBetween nvarchar(255);

DECLARE @MyCursor CURSOR;
DECLARE @SEQUENCE_NO smallint;
DECLARE @RESOURCE_ID nvarchar(15);
DECLARE @OPERATION_TYPE nvarchar(15);
DECLARE @SERVICE_ID nvarchar(15);

SET @OpsBetween = ''
SET @MyCursor = CURSOR FOR
     SELECT SEQUENCE_NO, RESOURCE_ID, OPERATION_TYPE, SERVICE_ID
     from dbo.OPERATION
     where TYPE = @TYPE AND BASE_ID = @BASE AND LOT_ID = @LOT and SPLIT_ID = @SPLIT AND SUB_ID = @SUB AND SEQUENCE_NO  < @eSeq
     ORDER BY SEQUENCE_NO
 
OPEN @MyCursor
FETCH NEXT FROM @MyCursor INTO @SEQUENCE_NO, @RESOURCE_ID, @OPERATION_TYPE, @SERVICE_ID
 
WHILE @@FETCH_STATUS = 0
   BEGIN
      IF @OpsBetween <> ''
         BEGIN
             set @OpsBetween = @OpsBetween + ', '
         END
      SET @OpsBetween = @OpsBetween + Cast(@SEQUENCE_NO as varchar(10)) + '-' + CASE WHEN @OPERATION_TYPE IS NOT NULL THEN @OPERATION_TYPE ELSE CASE WHEN @SERVICE_ID IS NOT NULL THEN @SERVICE_ID ELSE @RESOURCE_ID END END;
      
      FETCH NEXT FROM @MyCursor INTO 

Open in new window

0
You receive an error message when you try to create a differential database backup in SQL Server 2008

Cannot perform a differential backup for database namedb, because a
current database backup does not exist. Perform a full database backup by
reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option.
BACKUP DATABASE is terminating abnormally.

We use TSM,  TDP for SQL to do the backups.
0
SQL Server 2008 R2 Developer Edition

Hello Sir,
My DBCC OPENTRAN Displays

Oldest active transaction:
    SPID (server process ID): 67s
    UID (user ID) : -1
    Name          : DELETE
    LSN           : (286474:1220:6)
    Start time    : Oct  2 2018  1:10:17:007AM
    SID           : 0x0105000000000005150000005080301bd0cce941ef8d2526df040000

How can i kill it
0
Hi,

I have a DTS job that uploads some CSV data into a SQL table.

I wanted to schedual the DTS using an SQL job.

I have setup the job in SQL but when it is executed through the SQL job it fails (see error message below).

If I run the DTS directly it works ok.

I think it is something to do with the user permissions but I don't know why it fails as SQL user I have setup (web_admin) has SA rights.

Any ideas?

DTS fail
0
Hello,

I am getting error;
Date cannot be converted to string

Please find the query below:
INSERT INTO TABLE1

SELECT IET.[In1]
   
         , ''
         , getdate()
         , ''
         , IET.[Name of Trt:]
       ,NULLIF(RTRIM(IET.[FROM]), '')      
        ,NULLIF(RTRIM(IET.[TO]), '') 
         , IET.[Trion] 
 FROM TABLE2 IET


WHERE [InvemGUID] NOT IN 
    (SELECT [InvemGUID] FROM TABLE1 )
 

Open in new window


The error is caused by
  ,NULLIF(RTRIM(IET.[FROM]), '')      
        ,NULLIF(RTRIM(IET.[TO]), '') 

Open in new window


Any suggestions?
Thanks
0
Hi,
 Please find attached Image.
My query
I have Created Data warehouse. For example
Dimension Table : DimSite, DimCustomer,DimSalesperson
FactTable : FactinvoiceDetails

DimSite is Include

SiteID    Sitename

1            United Kigdom
2           USA
3           France

DimCustomer
SiteID       CustomerID CustomerName
1                R001             Ryan
2                R001             Bryan
3                R001            Stephen

DimSalesperson
SiteID       SalespersonID      SalespersonName
1                S001                       Mark
2                S001                       Stacy
3                S001                       Franck


My question is

I have to connect DimCustomer and DimSalesPerson to DimSite Table also
FactTables.png
0

Microsoft SQL Server 2008

49K

Solutions

17K

Contributors

Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the  Always On technologies and support for unstructured data types.