[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

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

Scenario:

Table : Supp
Columns:
AppUser
SQLUser
DateCreated
DateModified
SuppCode
SuppDesc
Address
SuppType

Whenever application changing 'SuppDesc' column data in 'Supp' Table, we need details include hostname (user modifying application from his desktop) through email alert.

Version : SQL Server 2008 R2.

Please help me with solution.

Thanks,
Chandra
0
Protecting & Securing Your Critical Data
LVL 1
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Hi,
  Does anyone have experience in HP ALM Quality Center version 11?  The previous tech left so there is little info. about this setup with no support contract.
 Currently everything for Quality Center (JBOSS, Microsoft SQL Server 2008 R2, Win 2008 R2 Ent. ) is installed in one VM and it's one serves this function.

Any recommendations on a backup and restore plan should anything goes wrong with this server or on the HP ALM Quality Center version 11 application?
qc.jpg
0
we have sccm and scom 2012 r2 running on windows 2008 r2 as the OS. database on a different server running windows 2008 r2 and sql 2008.

we want to upgrade the OS of sccm, scom and sql servers

we want to upgrade sccm and scom to system center cb 1802 or later.

it is best to install fresh deployments of all and try and migrate data.

or look at the option of inplace upgrade.
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
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
I have an emergency need to compare object definition across a few servers -- procedures, tables and views.   I've got all object definition loaded into a bunch of different tables, but I have one problem.
 
 The production objects are from a 'PROD' server, and there are explicit references to 'PROD'.
 The uat objects are from a 'UAT' server, and there are explicit references to 'UAT'.

When I compare he object definition, I want to exclude the server name references, such that I am only comparing the rest of the procedure definition.  I've written the piece below, thinking my REPLACE would do it --- but it does not appear to be.  I say that because I looked at one of the variances it returned, and I do not see any differences aside from the server names.

A third party tool is not an option.  I need to compare the objects across two servers ASAP, and identify which ones are different.  It is SQL Server v2014.


SELECT a.server_name,a.schema_name +'.'+ a.name [procedure],a.definition [prod],b.definition [uat]
FROM dbo.Procedures a JOIN dbo.Procedures b
  ON a.schema_name +'.'+ a.name = b.schema_name +'.'+ b.name  
  AND a.type = b.type
WHERE a.type = 'P'
AND REPLACE(a.definition,'PROD','') <> REPLACE(b.definition,'UAT','')
AND a.server_name = 'PROD'
AND b.server_name = 'UAT'
ORDER BY server_name,[procedure];

Open in new window

0
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
10 Tips to Protect Your Business from Ransomware
LVL 1
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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
Hello Experts,

I’m trying to filter a drop down list based on another dropdown list using SQL/vbscript/hta. I have it working sort of thanks to Experts Exchange where I can populate the dropdowns from the database.

Now, I need to do two things:

1. Filter the dropdown lists
2. Send the values to a text file

The query in the code below shows the data like this:

ARA_PERSON_ID               ARA_TYPE_DESC                     INVOICE_TERM_DESC
0000000                                Student - Credit                       2004 Fall Reporting Term
0000000                                Student - Credit                       2004 Spring Reporting Term
0000000                                Student - Credit                       2005 Intersession Reporting
0000000                                Student - Credit                       2009 Fall Reporting
0000000                                Student - Credit                       2012 Fall Reporting
0000000                                Student - Credit                       2013 Fall Reporting
0000000                                Student - Credit                       2013 Spring Reporting
0000000                                Student - Credit                       2016 Spring Reporting
0000000                                Student - Credit Free               2003 Fall Reporting Term
0000000                                Student - Credit Free               2004 Spring Reporting Term
0000000                                Student - Credit Free               2006 Fall Reporting Term


 But I need it to show like this:
0
Hi Experts,
I have used following sql query for Month start date and End date
Month start date

DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)

Month End date

convert(varchar,Dateadd(dd,-1, Convert( char(8) , Current_timestamp, 112 )), 120)
My questions is , every 1 st of month no transaction is avilable, users need to
see Last month transaction when it's come 1 st of month,
But 2nd day need to change form start month to end month.
Any idea how to do that

PS: this is doing in the View
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
Hello Experts,
Currently I have a view as below (look Current).  I am trying to make it more dynamic, and trying to convert it to as below (look Future).  Please let me know how can we make it possible.  Thank you in advance.

Thank you!



Current
------------

USE ITN
SELECT DISTINCT            
   TKT_ID       
  ,CAST(MAX( CASE FIELD_ID WHEN 1  THEN FIELD_VALUE END  ) AS VARCHAR(100) ) FIELD_1
  ,CAST(MAX( CASE FIELD_ID WHEN 2  THEN FIELD_VALUE END  ) AS VARCHAR(100) ) FIELD_2
  ,CAST(MAX( CASE FIELD_ID WHEN 3  THEN FIELD_VALUE END  ) AS VARCHAR(100) ) FIELD_3
  ,CAST(MAX( CASE FIELD_ID WHEN 4  THEN FIELD_VALUE END  ) AS VARCHAR(100) ) FIELD_4
  ,CAST(MAX( CASE FIELD_ID WHEN 5  THEN FIELD_VALUE END  ) AS VARCHAR(100) ) FIELD_5
  ,CAST(MAX( CASE FIELD_ID WHEN 6  THEN FIELD_VALUE END  ) AS VARCHAR(100) ) FIELD_6
  ,CAST(MAX( CASE FIELD_ID WHEN 7  THEN FIELD_VALUE END  ) AS VARCHAR(100) ) FIELD_7
  ,CAST(MAX( CASE FIELD_ID WHEN 8  THEN FIELD_VALUE END  ) AS VARCHAR(100) ) FIELD_8
FROM TKT_DETAILS (nolock)



Future
-------------


USE ITN
SELECT DISTINCT            
   TKT_ID       
  ,CAST(MAX( CASE FIELD_ID WHEN 1  THEN FIELD_VALUE END  ) AS VARCHAR(100) ) SELECT FIELD_1 FROM FIELD_DETAILS
  ,CAST(MAX( CASE FIELD_ID WHEN 2  THEN FIELD_VALUE END  ) AS VARCHAR(100) ) SELECT FIELD_2 FROM FIELD_DETAILS
  ,CAST(MAX( CASE FIELD_ID WHEN 3  THEN FIELD_VALUE END  ) AS VARCHAR(100) ) SELECT FIELD_3 FROM FIELD_DETAILS
  ,CAST(MAX( CASE FIELD_ID WHEN 4  THEN …
0
I have 100 MSSQL database servers. I wan to do mass installation on these 100 servers b y network. The MSSQL server I want to install is MSSQL 2016 enterprise.  Is there a way to do that automatically? and how?
0
Thank you all in advance for taking a look!

The sql below sends an email update in the form of a table. Each row in the table provides detail for a particular transaction. I would like to include a count of the rows in the body.

Any suggestions would be much appreciated!



DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
Declare @email NVARchar(4000)





SET @xml = CAST(( SELECT email AS 'td','', Created as 'td','', LOC AS 'td'
FROM  
stbl_Provider_test_6_25
Where Created > ' 2018-4-1'
and email = 'joe@aol.com'
order by created desc






FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))



SET @body ='<html><body><H3>
Email Update
</H3>
<table border = 1> 
<tr>
<th> email </th> <th> Created </th> <th> Location </th></tr>'    

 
SET @body = @body + @xml +'</table></body></html>'


EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'ACE DB Admin', -- replace with your SQL Database Mail Profile 
@body = @body,
@body_format ='HTML',
@recipients = 'joe@aol.com', -- replace with your email address
@subject = 'E-mail in Tabular Format';

Open in new window

0
Acronis True Image 2019 just released!
LVL 1
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

SELECT   COUNT(*) as total_items, "Category" =  
      CASE  
         WHEN NR_R < 1645 THEN 'less costly'
         WHEN NR_R > 1645 AND PKG_NR_R < 1700 THEN 'ok costly'  
         WHEN NR_R > 1800 THEN 'high costly'          
         ELSE 'not listed to sell'  
      END  
   
FROM SCHEMA1.TABLE_1
GROUP BY  NR_R
GO

above query working fine.

But i am trying to transpose data to look in column view where each category shows in the below column format.



less costly  ok costly  high costly
23                 50             77

SELECT   SUM (CASE WHEN NR_R < 1645 THEN 1 ELSE 0) as "less costly"
         SUM (CASE WHEN NR_R > 1645 and WHEN PKG_NR_R < 1700 THEN 1 ELSE 0) as "more costly"
         SUM (CASE WHEN NR_R > 1800 THEN 1 ELSE 0) as "most costly"
         ELSE 'not listed to sell'  
           
FROM SCHEMA1.TABLE_1

GROUP BY  NR_R
GO
i tried as above says incorrect syntax with )

Please advise
0
Created an SSRS 2014 report by joining 2 table ,related by CS_ID filtered by year . Notes column is in 2nd table and for each year it is unique . I am able to display data in a tablix. Issue I am facing is ,when some of the rows  are having notes (value of which is same for all rows). How to display that note on  bottom of a report on  a textbox  ? Currently I use another dataset to do this .  I want to do away with this. But I feel there is  way to set a report variable on expression of a column ( I use status column) of the row which has that  note column value. SetValue() does not seem to work . Ensured that report variable is not ready only.
0
Hi Everyone,

How to setup software upgrade group (SUGs)? Can you show me how to configure in SCCM console or GPO push down the windows updates & security to the clients.

How to retrieve full software report for SCCM

Tks.

Lcuky
0
I set my sql server to sql server mixed mode authentication

Question
I add this Windows account, for example domain name\johnsmith to SQL server login
If I want to log into SQL server by this Windows account like domain name\johnsmith on SQL authentication, not on Windows authentication
Can I do that?

My SQL server is SQL 2012
0
Logon Login failed for user . Reason: Attempting to use an NT account name with SQL Server Authentication.

I have a Windows domain account like gooled\solarexpert that is set as sysadmin on mssql database, connect to Solarwind application.  The application connection is not successful. I check the database log, it is "....Reason: Attempting to use an NT account name with SQL Server Authentication."

I use this account gooled\solarexpert  on other database with same setting, it's no issue to connect. Most are nnncet5ed successfully, only few of them is with this error message.

Any advice?

I have tried almost all suggestions posted from web about this error, it still doesn't work.

My server is mssql 2012
0
As part of our failover process in SQL Server 2008 R2 we need to change the registry on our sharepoint servers so they are pointed to the right SQL box on the mirror. This has worked fine until we created 2 vm sharepoint boxes.  The step that changes the registry is in a text doc for each sharepoint box and is accessed using  Operating System (CmdExec) with 'regini -m \\SHP01 d:\scripts\SwitchToAppN1_SHP01.txt '  . With the VMs  the command is regini -m \\VMPRDWEBAPP01 d:\scripts\SwitchToAppN1_VMPRDWEBAPP01.txt. Error is
Executed as user: ########. REGINI: SetValueKey (CLSQLN1\Applications) failed (5)  REGINI: Failed to load from file 'd:\scripts\SwitchToAppN1_VMPRDWEBAPP01.txt' (5).  Process Exit Code 1.  The step failed.

Searches said this has to The problem was w ith permissions on the remote server.  HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurePipeServers\winreg so we have added permission to the reg key for the user/svc-account but are still getting the error.

Any help would be appreciated.
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.