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

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
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
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
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
I have a winforms ERP software built using C# dotnet. Database is sql server. My issue is with installation at client side. It is taking huge time to install. Major time consuming is the installation of MSSQL server and its Management Studio. Is it necessary that I should install sql server at client side? Is there any option where I can copy the mdf and ldf files of the sql database to the system and connect with my application (like we do with access database)
1
Environment: Windows 2012 R2 Standard on VMWare VSphere
SQL Server 2016 Standard

We are getting this error
Msg 0, Level 11, State 0, Line 1
A severe error occurred on the current command.  The results, if any, should be discarded.

Open in new window

Everything works fine in SQL Server 2008 but when we moved it to SQL Server 2016 the complex queries are failing. Sample below is the query that works on 2008 but is failing in 2016.
SELECT        dbo.tblRevenueSubAccounts.Year, dbo.tblRevenueSubAccounts.ServiceTerritory, '$' + CONVERT(varchar, CAST(dbo.tblRevenueSubAccounts.Forecast AS money), 1) AS Forecast, '$' + CONVERT(varchar, 
                         CAST(dbo.tblRevenueSubAccounts.CurrentPlan AS money), 1) AS CurrentPlan, '$' + CONVERT(varchar, CAST(ISNULL(dbo.vwRFAM_YearCustYTDRev.YTD_Revenue, 0) AS money) 
                         + ISNULL(dbo.vwRFAM_YearCustOpenAVI.OpenAVI, 0), 1) AS [Shipped+Backlog], CONVERT(varchar(50), (CAST(ISNULL(dbo.vwRFAM_YearCustYTDRev.YTD_Revenue, 0) AS money) 
                         + ISNULL(dbo.vwRFAM_YearCustOpenAVI.OpenAVI, 0)) / CAST(dbo.tblRevenueSubAccounts.Forecast AS money) * 100) + '%' AS [%Forecast], CONVERT(varchar(50), 
                         (CAST(ISNULL(dbo.vwRFAM_YearCustYTDRev.YTD_Revenue, 0) AS money) + ISNULL(dbo.vwRFAM_YearCustOpenAVI.OpenAVI, 0)) / CAST(dbo.tblRevenueSubAccounts.CurrentPlan AS money) * 100) 
                         + '%' AS [%CurrentPlan]
FROM            dbo.tblRevenueSubAccounts LEFT OUTER 

Open in new window

0
Good evening,

We are having an issue with our SQL Server Express 2008R2 installation on our new server.  We currently have a server running SBS2011, along with SQL Server Express 2008R2.  Since support for SBS2011 is ending soon, we have purchased a new server, which currently has Server 2016 Standard and SQL Server Express 2008R2 installed.  I am currently testing the new server for functionality in a test environment, and we are unfortunately having an issue.  

We currently utilize an Access Database to track all of our patient information, along with SQL Server for authentication and reporting of the Access Database.  In order to move the database to the new server, I have backed up the database on the SBS2011 server.  I then installed SQL Server on new server (2016 Standard), and then restored the database.  When opening the Access Database file, it should query the SQL Server for permissions via Active Directory and SQL Server.  I have the authentication on the database set as mixed mode.  I have verified the credentials exist in SQL.  I am able to connect successfully from a workstation using the Microsoft Kerberos Configuration Manager for SQL Server tool as well.

I have attached screenshots of the errors that I am receiving and would greatly appreciate any input to assist in resolving this issue.

Thanks for the help.

Error 1Error 2Error 3
0
After updating a user account in Active Directory, to change the user name, SharePoint Foundation 2010 still shows the old user name. This version of SharePoint does not have the User  Profile synchronization tools that the regular SharePoint 2010 platform has. There is no option to change the name manually, either as an administrator or as a user on their own profile. I have tried some of the solutions for doing the update using PowerShell and other techniques, but I am not having luck with any of the workarounds. How is this normally done in SharePoint Foundation 2010?
0
I am looking for a software solution that will enable me to archive user data to another local location (NAS drive), i need to be able to specify that if data is over 2 years old it moves the data from live source A to Archive target B but retains NTFS Permissions on folders and sub-folders alike.

I have tried google but I am not seeing a suitable solution, if anyone has suggestions i would greatly appreciate it.

I know Robo-Copy is an option but my scripting skill are not great.
0
Hello Experts,
I am trying to display a table rows horizontally by TKT_Id.  I have two tables, TKT_DETAILS and FIELD_DETAILS.  Please see the details below.

TKT_DETAILS
TKT_Id
Field_Id
Field_Value

There are about 750 different fields numbered as 1 thru 750.  Each row contains only 1 field.  There are 750 rows for every single record vertically.

FIELD_DETAILS
Field_Id
Field_Name

This table contains Field Names of the every Field_Ids.

Because it is very difficult to read vertically, I am trying to create a view to show them all 750 fields in a single row as below.

Tkt_Id, Field_Name_1,  Field_Name_2,  Field_Name_3,  Field_Name_4,  Field_Name_5...  Field_Name_750
    1             001                     002                     003                     004                      005            ...             750

Plesae let me know how to do it without using PIVOT.  Thank you in advance for your time and help!
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.