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
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
I Need to alter an extended stored procedure in SQL 2008R2.  Pretty much exactly like here: https://www.experts-exchange.com/questions/21081340/Microsoft-SQL-System-Extendend-Stored-Procedure-Overwrite-sp-executesql.html 
Though that ended up without a solution.

I need to use the same name as an Existing stored procedure though.

I want to be able to test for a parameter to the Stored procedure and Exit if it contains a keyword. If it does not, then I want to execute the original SP as usual.

So something like:

sys.sp_executesql  -> sp_origexecutesql

New sys.sp_executesql  
      if @P1 = "Select * from NoNoTable" return NULL
      Else exec sp_origexecutesql @P1

I have SysAdmin Access to the SQL Server, so I can make any changes there. I can Create my Own DLL Code if needed too. I just don't have access to all of the Code that is running against the SQL Server and I want to Intercept some of the sp_executesql Commands.

Thanks,
0
I had to change my login in active directory.  After doing that all of my SSRS (2008 R2) subscriptions wouldn't run because the previous AD entry didn't exist.  I had a tool to make those changes, but I still have the same issue when I create a new subscription.  It says my AD login isn't recognized.  How do I change that?  I've googled it and found some hits, but nothing quite explains how to fix the issue for new subscriptions.  Can I just go to the users table and change the old login to the new login?
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
Database Migration from HP Storage to DELL EMC:
After renaming of SQL Server 2008 R2 Default instance, We are unable to connect Management Studio from remotely for Windows Authentication users. It is working fine for SQL Server Authentication users.

Error: The target principal name is incorrect. Cannot generate SSPI context.

Meantime for SQL Server 2014 it is working fine.

Scenario 1:
Server : SQLCL (SQL Server 2008 R2) - We have build new server with the name as SQL2 then migrated the databases, added alias name as SQLCL. In this scenario, we faced same mentioned above error.
Server:  DBCL (SQL Server 2014): We have build new server with the name as SQL1 then migrated the databases, added alias name as DBCL. In this scenario, we have no issues.

Scenario 2:
Again We have renamed the server from SQL2 to SQLCL and SQL1 to DBCL but Still we are getting same error for SQL Server 2008 R2 version installed.

But we have no issues jobs and every thing fine. Please help us.

Thanks,
Chandra
0
How would one pull out the numeric text from within a varchar field?  For example, I need to write a formula to determine overall dosage a patient could take in a given day.  The instructions for the medication is stored as one long string like;

take 1 tablet by oral route  every 4 hours as needed

Open in new window


I'd like to pull out the first numeric entry from the left and place it into a column named 'Quantity' and the first numeric entry from the right and put it in a column named 'Frequency'.
I'd then multiply this by the dose to determine the overall usage of a medication.  Here is my current query;

select distinct p.person_id as [PID], p.last_name as [Last Name], p.first_name as [First Name], CAST(p.date_of_birth as DATE) as [Date of Birth], 
pm.medication_name as [Medication],pm.sig_desc as [Sig],  pm.start_date as [Start Date] from patient_medication pm
JOIN person p ON p.person_id = pm.person_id
JOIN fdb_medication f ON f.medid = pm.medid
where medication_name  like '%hydromorphone%' and p.expired_ind <> 'Y' and  (pm.date_stopped > GETDATE() or pm.date_stopped = '')
order by p.person_id 

Open in new window


Thanks for any help!
0
Hello,
How to set a column which is [Ref] [int] IDENTITY(1,1) NOT NULL to 1 . I tried to delete all rows in the table but still
when I insert a row in the table , theRef value is 33.

Regards
0
hi experts

i have two query's
--query UNO
SELECT [ERDAT     ]
      ,A.[TRDAT     ]
	  ,[Tipo usuario contractual     ]
      ,[Texto       ]
FROM [SMI].[dbo].[01102018_01_USR02] A 
		JOIN [SMI].[dbo].[01102018_USMM] B	ON A.[BNAME       ] = B.[Usuarios    ]
		JOIN [dbo].[02102018_USR05] C ON B.[Usuarios    ] = C.[BNAME       ]
WHERE  [UFLAG] NOT IN (192,64 ) AND
		[PARID                 ] like '%BUK%'
		and [Texto       ] = 'Diálogo     '
		AND try_convert(datetime, [ERDAT     ], 103) > try_convert(datetime, '31.10.2017', 103)
order by try_convert(datetime, [ERDAT     ], 103) 

--QUERY DOS
SELECT BNAME, [BUK], [BZPNR]
FROM (
		SELECT * FROM [dbo].[02102018_USR05]
	 ) AS Data
PIVOT
(
	MAX(PARVA)
	FOR PARID IN ([BUK], [BZPNR])
) AS PVT_Data

Open in new window


How can I join the queries to have a single result?
0
Help with SQL SERVER 2008R2 view or stored procedure.

I have a view (CT_Work_Order_Warehouse_Headers_Pass_02) that the output looks like this:
BASE		LOT		SPLIT		SUB		SEQ		WAREHOUSE
CC181137.3		21		0		0		5		HOUMA
CC181137.3		21		0		0		10		TBD
CC181137.3		21		0		0		20		TBD
CC181137.3		21		0		0		30		PORT_ALLEN
CC181137.3		21		0		0		40		TBD
CC181137.3		21		0		0		50		HOUMA
CC181137.3		21		0		0		60		TBD
CC189999.3		1		0		1		10		PORT_ALLEN
CC189999.3		1		0		1		20		TBD
CC189999.3		1		0		1		30		TBD
CC189999.3		1		0		1		40		HOUMA
CC189999.3		1		0		1		50		TBD
CC181234		1		0		1		5		PORT_ALLEN
CC181234		1		0		1		10		TBD
CC181234		1		0		1		20		TBD

Open in new window


I need a view or stored procedure to make the data look like this:
BASE		LOT		SPLIT		SUB		SEQ		WAREHOUSE
CC181137.3		21		0		0		5		HOUMA
CC181137.3		21		0		0		10		HOUMA
CC181137.3		21		0		0		20		HOUMA
CC181137.3		21		0		0		30		PORT_ALLEN
CC181137.3		21		0		0		40		PORT_ALLEN
CC181137.3		21		0		0		50		HOUMA
CC181137.3		21		0		0		60		HOUMA
CC189999.3		1		0		1		10		PORT_ALLEN
CC189999.3		1		0		1		20		PORT_ALLEN
CC189999.3		1		0		1		30		PORT_ALLEN
CC189999.3		1		0		1		40		HOUMA
CC189999.3		1		0		1		50		HOUMA
CC181234		1		0		1		5		PORT_ALLEN
CC181234		1		0		1		10		PORT_ALLEN
CC181234		1		0		1		20		PORT_ALLEN

Open in new window


Note: Each starting SEQ will have a non 'TBD' warehouse for each different Base/Lot/Split/Sub
I know i need to go get the WAREHOUSE from the SEQ <= the current SEQ where it is not = 'TBD'

any help would be greatly appreciated.
0
hi experts

i have this query
SELECT
    [BNAME       ]
,      [PARID                 ]
      ,[PARVA                                   ]
  FROM [SMI].[dbo].[02102018_USR05]
  WHERE [BNAME       ] LIKE '%RLARA  %'

this is the results
BNAME             PARID                       PARVA                                  
RLARA             BUK                         1000                                    
RLARA             BZPNR                       13271                                  
RLARA             FIT_ALV_AR                  /CLIENTE EXT                            
RLARA             LE_SHP_DEL_MON_LISTT        HC                                      
RLARA             MOL                         PE                                      
RLARA             UGR                         PE                                      

but i need this
BNAME             BUK                         BZPNR                
RLARA             1000                      13271
0
In this app data is passed to and from the SQL server and parsed either in the DB to insert or the client for display.

The query passed to the Cursor is:

SELECT PropertyName, Property_ID, CrewChief FROM HTS.PropertyMaintenance WHERE DateOfActivity = 10-29-2018 ORDER BY  Document_ID ASC

It returns

Central Park             M3428       RC
Bryant Park              M83822      TJ
Prospect Park          B36482      WF

The string to built from this set:

'Central Park, M3428, RC, Bryant Park, M83822, TJ, Prospect Park, B36482, WF,'
(trailing comma is correct)

This SP returned a single column string successfully. When modified to return 3 column set it returns a null string? Can anyone see the error. I'm blind to it.
Thank you for any help.

CREATE PROCEDURE [HTS].[PropertyMaintenance_SELECT_DocumentsByDate]
(
      @QryByDate      varchar(300)
)
AS


declare @objcursor as cursor
 
declare
    @vsql       as nvarchar(max)
    ,@id        as int
    ,@value     as varchar(50)
   

DECLARE @list varchar(1000)
DECLARE @listTemp varchar(100)
DECLARE @description varchar(300)
DECLARE @description2 varchar(300)
DECLARE @description3 varchar(300)


set @vsql = 'set @cursor = cursor forward_only static for ' + @QryByDate + ' open @cursor; '
 
exec sys.sp_executesql
    @vsql
    ,N'@cursor cursor output'
    ,@objcursor output

fetch next from @objcursor into @description, @description2, @description3

while (@@fetch_status = 0)
begin

SET @listTemp =  …
0
In the SQL Selects below the first one will get you an example of my data

The select unions is what I need as output

The Question Types can be literally anything and may be from 3 to 5 types
(IndividualTypeID is tied to an origin so everyone within that origin will have the same origin)

Not all Individuals will have all the Question Types
But they will all be constrained within a "set"
In this case... my example below... 2.
Marital Status and age


DECLARE @table TABLE
(
    IndividualID VARCHAR(100),
    QuestionType VARCHAR(100),
    AnswerText VARCHAR(100)
);
INSERT INTO @table
(
    IndividualID,
    QuestionType,
    AnswerText
)
VALUES
(4819540, 'MaritalStatus', 'Single'),
(4819540, 'Age', '25-75'),
(4819546, 'Age', '18-24'),
(4819599, 'MaritalStatus', 'Married');

SELECT * FROM @table



SELECT 4819540 IndividualID, 'Single' MaritalStatus, '25-75' Age
UNION ALL
SELECT 4819546, '', '18-24'
UNION ALL
SELECT 4819599, 'Married', ''

Open in new window

0
I need to set a table lock in  Microsoft SQL Server 2008 for the duration of an update process.

The end user has multiple independent applications that utilise the database all from independent vendors.

I have experimented with executing a query incorporating "WITH TABLOCKX" but this results in a hang in one of the other independent applications indicating the lock is applied but not trapped.  Attached (query1.jpg) is the result of a query on sys.dm_tran_locks that I run to test for existing locks in order to determine if my process can run.
What I need is a query to apply the locks in a way that is trapped by the other independent applications.T_SQL query on sys.dm_tran_locks
0
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
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
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

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.