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 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
Angular Fundamentals
LVL 12
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

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 dynamic sql that builds a select correctly

DECLARE @DIspo VARCHAR(MAX);
SET @DIspo = '
SELECT ''MAX(cte.'' + COLUMN_NAME + '') AS '' + COLUMN_NAME FROM [tempdb].INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE ''#table%'' AND COLUMN_NAME IN (SELECT CODE FROM dbo.DispositionTypes
WHERE DispoReport = 1)';
PRINT @DIspo;

Open in new window


When I copy the Printed @Dispo into a query window I get this

SELECT 'MAX(cte.' + COLUMN_NAME + ') AS ' + COLUMN_NAME FROM [tempdb].INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE '#table%' AND COLUMN_NAME IN (SELECT CODE FROM dbo.DispositionTypes
WHERE DispoReport = 1)

When I do run the select
I get three rows in a table

MAX(cte.DNC) AS DNC
MAX(cte.NI) AS NI
MAX(cte.DEALPUR) AS DEALPUR

What I need to do is in ONE process...
Get back a varchar string I can use elsewhere in a dynamic query process

MAX(cte.DNC) AS DNC, MAX(cte.NI) AS NI, MAX(cte.DEALPUR) AS DEALPUR
0
I want to pass a string in procedure containing single quote and/or double quote
Ex:
My data entered in textbox is "I as 'as'  and as "India"

1/ How to pass this as parameter
2/ How to create querystring inside procedure


declare @qs nvarchar(max)
set qs=?
0
hi experts

can you share scripts for administration about block SQL SERVER 2016
0
Restore Stored Procedure in SQL Server
In this article, we will discuss how to deal with a situation wherein you face an issue with a mandatory stored procedure in your critical database.
0
SQL Server 2012 -

I have a challenging query... trying to best optimize it.   Would love some help!

I have two tables (called BEFORE and AFTER) with the same structure; two columns, a GROUP_ID and a VALUE.
I need to compare the VALUES for each GROUP_ID and if they are DIFFERENT, add the GROUP_ID to another table.

So given the BEFORE and AFTER tables below.
GROUP ID = 1 is different between the two tables because value 11 and 12 are not in AFTER
GROUP ID = 2 is different between the two tables because value 23 is not in BEFORE
GROUP ID = 3 is the same in both tables

Therefore, the RESULT table should contain  GROUP ID  with 1 and 2

BEFORE
Group ID     Value
    1                 10
    1                 11
    1                 12
    1                 13
    2                 21
    2                 22
    2                 24
    2                 25
    3                 30
    3                 31
    3                 32


AFTER
Group ID     Value
    1                 10
    1                 13
    2                 21
    2                 22
    2                 23
    2                 24
    2                 25
    3                 30
    3                 31
    3                 32

RESULT table
GROUP ID   (unique key, no duplicates)
     1
     2  


SO basically, it would have to be something like this...


INSERT INTO RESULTS (GROUP_ID)
select distinct GROUP_ID  
  from BEFORE
    ( join ) AFTER …
0
I recently received some excellent help from another Expert with a decimal manipulation that you see in this ticket.  Basically, I needed to control the decimal output to 2 digits or 3, and the Expert gave me great advice:   https://www.experts-exchange.com/questions/29114876/VARCHAR-to-DECIMAL-string-manipulation.html#a42663296

But, I am having a problem with the data in one of the subsequent manipulations which is in the code below.  I've input a lot of numbers into a temp table using the FORMAT in the above question, where the columns are VARCHAR(255), and the values in the column  are 1.00, .50, 5.49, .17, etc.  The problem is that I need to reference that character value numerically within the CASE below, and every attempt to do so fails with the error in my subject line.

I tried to correct it myself with this:  CONVERT(INT,CONVERT(DECIMAL(19,2),LPW))
But the end result was changed ---  .01 becomes 0, 2.30 becomes 2, etc.   So my attempt to convert the char value so that I can perform the CASE statement CHANGES the data, and thereby invalidates it.

Can somebody assist?

I can cast it as float, but then I lose my decimals -- 1.00 becomes 1, 0.50 becomes 0.5... etc.  I need a result that retains the data as I have stored it in the temp table.

    SELECT tmpTable.MATNR,
           tmpTable.itemNumber,
           CAST(tmpTable.WPL AS VARCHAR(20)) + ' oz./ft.',
           CASE
               WHEN tmpTable.LPW >= 2 THEN
                   CAST(tmpTable.LPW

Open in new window

0
This is concerning index rebuild's impact on transaction replication.
During an index rebuilds with full recovery mode, transaction log entries will be created (normal).  
If I switch to bulk logged recovery, will:
- less data be replicated ?
- less mining of the transaction log occur ?

What I'm seeing after an index maintenance where some large table indexes are rebuild, replication will report "Approximately 5000000 records have been scanned in pass #4, 0 of which were marked for replication".

This concerns me in that "real" data changes are being slowed down in getting to the subscriber.

Thank You
Bruno
0
Hi,
Any suggestion on how to make this  query look better.
Thanks in advance


ALTER PROCEDURE  [dbo].[sp123]

(
    
	 @DocIDType int = 0
	
)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;  	
	
	declare @SQL varchar(500) = ''
	declare @GroupBy varchar(500) = ''
	
		 SET @SQL = 'SELECT DISTINCT ApprovalDocDisplay ' 

	IF @DocIdtype = 1 
		BEGIN


			
			SET @SQL = @SQL + '   FROM  [vvApp]    ' 
		    SET @SQL = @SQL + ' WHERE  Email = '+ '''' + ''  +''''	
		END


	IF @DocIdtype = 0
		BEGIN
			SET @SQL = @SQL + '   FROM  [vvApp]    ' 
		    SET @SQL = @SQL + ' WHERE  Email <> '+ '''' + ''  +''''	
		END
		  
	SET @SQL = @SQL  + ISNULL(@GroupBy,'')
      
	EXEC(@SQL)


END

Open in new window

0
Build an E-Commerce Site with Angular 5
LVL 12
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

Hello,

Need to have a new column depending on the text in an column.
Please find the attached example.

Thanks in advance.
example4.xlsx
0
Hello guys

I made this function to get the percentual from two value, but sometimes it works fine and sometimes it doesn't.

could you help me to ajust it?

Thanks a lot

alter Function  fn_PercentualDesconto (@Fullvalue float, @EndValue float)
  Returns Float
Begin 
	declare @v01 float;
	declare @v02 float;
	declare @v03 float;

        Set @v01=@Fullvalue 
	Set @v02=@EndValue 
	Set @v03=(@v01-@v02)
	Return 100- round((@v03 / @v01 ) * 100 , 2) 
End

Open in new window

0
In the sql below it is reading about 2000 records.
Is there any way to read those columns from left to right
Basically I want to choose column 1 if its > 0
If not then column 2
and so on

Just seems like I have a mess here



SELECT RollaPoll_IndividualID ,
            CASE WHEN [1] > 0 THEN [1]
                 WHEN [2] > 0 THEN [2]
                 WHEN [3] > 0 THEN [3]
                 WHEN [4] > 0 THEN [4]
                 WHEN [5] > 0 THEN [5]
                 WHEN [6] > 0 THEN [6]
                 WHEN [7] > 0 THEN [7] ELSE [8] END IndividualID
     FROM   #Results r

Open in new window

0
Hi,
I’ve made some changes to some SQL Server objects, tables,views, stored procedures, functions etc in my test environment which I now want to move to the production environment.
I want to make backups of the existing objects in production, eg Production_Table_1 as Production_Table_1_backUp.
I’m looking for the easiest way to make a copy of the table in its existing database, and the copy to include all associated indexes,constraints, data etc…
I looked at Transfer SQL Server Object Tasks in SSIS but it doesn’t seem to allow altering the object name.
I’m now looking at using  the “Script Table as” option in Management studio but it doesn’t seem to copy indexes.
Any guidance appreciated.
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
I need some help with an incredibly ugly DECIMAL string manipulation.  The datatype for all values used in these calculations is varchar.  (I know.  There's nothing I can do about it.)  I have two different groups of calculations -- I need one to always come back with 3 decimals, and I need the other to always come back with 2 decimals.  By itself, that is not too bad... I thought I could use something like this --   CONVERT(varchar(20), CAST(SUM(value) as MONEY),1)    ---  where CONVERT( varchar, m, 1 ) gives me the value into a string with commas, but it's just not coming together for me.

Again, the two groups of calculations below -- I need the top one to always return 3 decimals with ROUND, and I need the bottom one to always return 2 decimals --- whether the values are whole or not.

Is anyone able to help?



Always display 3 decimal points, whether it is a whole number or not -- ie., 200.000, .015 -- ROUNDing up for anything 4 and greater.  .0539 becomes .054
1.        CAST(ROUND((mmmm / oooo), 2) AS FLOAT)
2.        CAST(LWength AS VARCHAR(20)) + ' oz" x 1" piece'
3.        CAST(WPLength AS VARCHAR(20)) + ' oz/' + MinWidth   + '" x 1" piece'

For these, I just need to always display 2 decimals, whether the value is a whole number or not -- ie., 1.00, .50
1.        MinWidth + ' x ' + CAST(LPWWeight AS VARCHAR(20))  + '"/oz'
2.        CAST(LPWeight AS VARCHAR(20))+ ' sq in/dwt'
3.        MinWidth + ' x ' + CAST(LPWeight AS VARCHAR(20)) + '"/oz'
4.  …
0
Hi,
How do I change the column number in a serial way
example:
1,2,3
excluding which have a ref number :  0

example
Refno
1
2
3
0
5
0
6

The expected result:


Refno
1
2
3
0
4
0
5

Thanks in advance
0
What is the syntax to restore a SQL Server .mdf file from the Command Prompt?
0
10 Tips to Protect Your Business from Ransomware
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.

Hi,

I am populating 4 dropdownlists with distinct values from columns from my DB table. I would like to ask is it more efficient to have for datsources for each dropdownlist or somehow create 1 datasource that contains the unique values from each column?

I have several other datasources so would like to limit how many are used on the page.

Manager	PMList	ClientList	ProductList
Name1	PM2	ClientA	ProductA
Name2	PM1	ClientA	ProductC
Name3	PM2	ClientA	ProductD
Name4	PM1	ClientA	ProductF
Name5	PM1	ClientA	ProductF
Name6	PM1	ClientA	ProductC
Name7	PM1	ClientB	ProductC
Name8	PM2	ClientB	ProductA
Name9	PM2	ClientB	ProductF
Name10	PM1	ClientB	ProductF
Name11	PM1	ClientB	ProductC

Open in new window


Thanks for any help.
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
I have a project for our shipping department - I need to created in SQL query that will provide the following results;
1. group by style & color (example: style:123R12, color: BLK) and create an alpha sequence (A) which 123R12BLK = A

2. if style is the same but color is different (example: style:123R12, color: BLUE) it should be sequence (B) which 123R12BLUE = B and so on.

3. Sequence could go as far A thru ZZZ
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
what are the cautions we need take in SQL Server 2012 std( windows cluster 2012) and  Node1, Node2 are in cluster

as sql server 2012 sp4 patch upgrade we unable to install because of some installer files are missing, we tired to fix, but it is not working and  we are decided to go for SQL server Repair option on Node1

Pls let me know, what steps do i need to do..with a caution..on Node1

Pls note, once if it successful patched on sp4 on node2, after repair, then we will plan on node2.

Pls suggest. Thanks
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.