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

Hi,
I need to know this date format is yyyy-mm-dd?
DECLARE @DueDate nvarchar(30)

SELECT RIGHT(DueDate,4)+'/'+SUBSTRING(DueDate,4,2)+'/'+LEFT(DueDate,2)
0
Introduction to R
LVL 13
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

I am installing a ClickOnce application that has included SQL Server 2008 Express in the list of Prerequisites.  It installs SQL Server 2008 Express and my application but the Windows User does not have access to the server.  I have verified the C:\Program Files (x86)\Microsoft SDKs\ClickOnce Bootstrapper\Packages\SqlExpress2008R2\en\Package.XML and it has included the arguments list as shown below.

Arguments='/q /hideconsole /action=Install /ADDCURRENTUSERASSQLADMIN /features=SQL /instancename=SQLEXPRESS /enableranu=1 /sqlsvcaccount="NT Authority\Network Service" /AddCurrentUserAsSqlAdmin /IAcceptSqlServerLicenseTerms /skiprules=RebootRequiredCheck'
     
Any ideas on how to resolve this issue?
0
Move a SQL database from one server to another server.  How to move a database from a SQL Server 2008 R2 Enterprise server to SQL server 2012. We know there are at least 3 ways to do it. Yet, what is the most fool proof, simple or generally used. is there a way to use MS SQL Data Wizard for managing your MS SQL database move? Backup/Restore? What would be the actual steps? Thanks
0
Procedure to convert the data from DATA TYPE " IMAGE " to  VARBINARY  in MS-SQL server 2008 (version 10.0.4000.0) ?
0
Facing issue while connecting to sql server
error message:-   Login failed for user 'sa'. Reason: Server is in script upgrade mode. Only administrator can connect at this time. (Microsoft SQL Server, Error: 18401)
how to solve this issue
0
hi,

please see attached file for error I am getting when I try to export to excel 2007. I have excel for office 365 installed on my computer.
any help will be appreciated.
thanks
sql2008r2exporterror.docx
0
I need to edit my query so that when two date parameters are passed to it, the query will accurately pull the correct data.
I would like to use a BETWEEN statement that looks at the dateBegin and dateEnd.


[EditHistoryPlacement] table
placementID	dateAdded	columnName
64291	2010-08-25 17:21:00.000	customText10
64291	2010-09-20 12:50:00.000	customText11
64291	2018-07-23 16:01:32.260	customText32
64291	2018-07-23 16:01:32.260	customText33
64291	2010-08-25 17:21:00.000	customText6
64291	2010-08-25 17:21:00.000	dateBegin
64291	2010-08-25 17:21:00.000	dateEnd
64291	2010-08-25 17:38:00.000	dateEnd
64291	2010-08-25 17:21:00.000	daysGuaranteed
64291	2017-12-13 16:31:01.653	employeeType
64291	2010-08-25 17:21:00.000	otExemption
64291	2010-08-25 17:21:00.000	reportTo
64291	2010-09-16 15:20:00.000	status
64291	2016-12-16 12:13:00.000	taxState
64291	2015-05-05 21:44:00.000	terminationReason
64291	2015-05-14 22:13:00.000	terminationReason

Open in new window



My  query:
SELECT DISTINCT
ehp.[placementID]
,(SELECT TOP 1 min([newValue]) as dateBegin FROM [AddisonDataMirror].[dbo].[EditHistoryPlacement] e1
		WHERE columnName = 'dateBegin' 
		and e1.placementID in (ehp.placementID) 
		GROUP BY [dateAdded]) as dateBegin

,(SELECT TOP 1 max([newValue]) FROM [AddisonDataMirror].[dbo].[EditHistoryPlacement] e2
		WHERE columnName = 'dateEnd' 
		and e2.placementID in (ehp.placementID)
		GROUP BY [dateAdded]) as dateEnd


FROM [EditHistoryPlacement] ehp

WHERE 
 ehp.placementID in (64291) 
and (columnName = 'dateBegin' or columnName = 'dateEnd') 
ORDER BY [placementID]

Open in new window


The results from my query

placementID	dateBegin	dateEnd
64291	2010-09-14 00:00:00.0	2013-09-14 00:00:00.0

Open in new window

0
Hi Experts,

I have written the following code  to get Working Days.
For example, you will enter Date  then You will enter total number of days. it will return the working date.
if you enter 2019-05-11 and enter 27 days
The function should be return 2019-04-04
But this function is returning
2019-04-09


See the code below

ALTER FUNCTION sr_WeekDaysFc(@addDate AS DATE, @numDays AS INT)
RETURNS DATETIME
AS
BEGIN
    WHILE @numDays>=0
    BEGIN
       SET @addDate=DATEADD(d,-1,@addDate)
       IF DATENAME(DW,@addDate)='saturday' SET @addDate=DATEADD(d,-1,@addDate)
       IF DATENAME(DW,@addDate)='sunday' SET @addDate=DATEADD(d,-1,@addDate)
  
       SET @numDays=@numDays-1
    END
  
    RETURN CAST(@addDate AS DATETIME)
END
GO

Open in new window

Any idea appreciated
0
Hi;

How can I improve performance issue ? I created below stored procedure but if I check costly stored procedure by using sql query to detect them. I realized that below query needs improvement to fix performance issue.Can you help me please?

  

CREATE PROCEDURE [WMS].[GetSalesOrderSearch]  
 /*  
  EXEC WMS.[GetSalesOrderSearch] @CustomerId=1003,@PageNumber=1,@PageSize=1000,@SearchText='copy230',@SortDescending=0  
  */  
 (  
 @CustomerId INT  
 ,@PageNumber INT  
 ,@PageSize INT  
 ,@SearchText NVARCHAR(MAX) = NULL  
 ,@SortColumn NVARCHAR(255) = 'Id'  
 ,@SortDescending BIT = 0  
 ,@FilterListXml XML = NULL  
 ,@RowCount INT = NULL OUTPUT  
 )  
AS  
BEGIN  
 DECLARE @Query NVARCHAR(MAX) = '';  
 DECLARE @WhereClause NVARCHAR(MAX) = '';  
 DECLARE @SearchJoin NVARCHAR(MAX) = '';  
 DECLARE @FilterQuery NVARCHAR(MAX) = '(SO.CustomerId = @CustomerId AND SO.IsDeleted = 0) ';  
 DROP TABLE  
  
 IF EXISTS #FilterList  
  SELECT CAST(ROW_NUMBER() OVER (  
     ORDER BY n.value('(./AttributeId/text())[1]', 'INT')  
     ) AS VARCHAR(50)) Row  
   ,n.value('(./AttributeId/text())[1]', 'INT') AttributeId  
   ,n.value('(./PropertyName/text())[1]', 'VARCHAR(100)') PropertyName  
   ,n.value('(./AttributeType/text())[1]', 'VARCHAR(100)') AttributeType  
   ,n.value('(./AttributeEntityType/text())[1]', 'VARCHAR(100)') AttributeEntityType  
   ,n.value('(./BeginDate/text())[1]', 'DATETIME') BeginDate  
   ,n.value('(./BeginNumber/text())[1]', 'FLOAT') BeginNumber  
   

Open in new window

0
I have this job step to delete a flat file created by a SQL job: -
xp_cmdshell 'del Z:\AXDATA.txt'

It runs sucesssfully.

The actual file is AXData.txt with some lower case characters, is this the issue? I changed it to lower case in the job step but it just reverted back
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.

SQL Server 2008. This does not work

>= DATEFROMPARTS(YEAR(GETDATE()) - 2, 1, 1)

I'm trying to return data for the lat 2 year plus year-to-date. Anyone know what works in 2008?
0
I know what the problem is why its happening and whats going wrong, i have run out of ideas to correct it..

Setting The Scene...

i have a page where there are 3 dates, Creation Date, Expected Completion Date, and Actual Completion Date..

Not getting any problems with the first 2 as they are populated at the same time, and from then on their stat is a known quantity.

the third date is causing me no end of problems, the page can be updated numerous times but when the Actual Completion Date is filled in using a date picker. i need different correction code to enter it into the database. this is fine, but if the page gets updated again, it needs the same date function as the first 2 dates.

i need a way of telling what format a date is in and correct it accordingly, is this possible and how would i go about it..

Thank you in advance..
0
Hello am needing help on how to move SQL to another partition?
My C: partition is filling up and I have plenty of space for it on my D: partition
I am running MSSQL10_50.SQLExpressR2
server 2008R2
0
Is there any monitoring tool for sql server that capture and analyze system resource utilization?
0
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
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
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
Learn SQL Server Core 2016
LVL 13
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

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

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.