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

How to Write a sql query to retrieve the loan number, state and city, customer first name for loans that are in the states of CA,TX,FL,NV,NM but exclude the following cities (Dallas, SanFrancisco, Oakland) and only return loans where customer first name begins with John.
0
JavaScript Best Practices
LVL 19
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

How to identify slow running queries in SQL Server and how to resolved it
0
What would be the query (Script) to drop all synonyms in corresponding databases in entire instance as a bulk?

The report ( csv file) looks like this. I want to drop them all by base_object_name


DB_name               Database_id     object_id                    base_object_name

[ABSCorxxxOM]      39                         NULL                              [fwbsqerewe2].[ABSCorxxxOM].[dbo].[DealMaster]
[ABSCoyyyyOM]      35                         NULL                              [fwbsqerewe2].[ABSCoyyyyOM].[dbo].[TrancheMaster]
[A.ROM]                    40                         NULL                              [fwbsqerewe2].[A.ROM].[dbo].[Static_UserDirectory]
[As3.Ms.Import]      92                         NULL                              [sg-nt].[As3.Ms.Import].[dbo].[Static_InterfaceTransformSequence]

and ........
0
I have a very large table with 1 billion rows
One of the columns stores full address details (street, town, city, country)

Previously it was varchar(max) but since we moved to multilingual we have to change it to nvarchar(max) which doubles the size

Our database size is now growing by 10.5GB a day and I'm looking at ways to reduce this
Is there a way to compress this data and uncompress on the web app?

Open to ideas and suggestions

SQL Server 2017 enterprise
0
I have a large table with ID identity seed int (primary key)

Its currently at 1.8 billion rows and growing rapidly
When this hits 2.1 billion rows it will reach the max int size.
Therefore I need to change it to bigint with minimal down time

I've read a LOT of articles online about doing this
I have 3 indexes on this table which references the ID, so I know these will need to be dropped and recreated

My current theory is this
1. Create new column NewId Bigint
2. copy the primary key/int values to this new column so it matches
3. drop all indexes, and unset primary key
4. Drop old ID column
5. Rename "NewId" to "ID"
6. make the new ID column primary key, and identity seed (even though they already contain Id's ?)
7. re-create indexes

Is this solution plausible? what are the issues with the above?
Sql server 2017 enterprise.
0
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
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
Learn SQL Server Core 2016
LVL 19
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.

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
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
How to Generate Services Revenue the Easiest Way
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

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

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.