Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x

Microsoft SQL Server 2005

71K

Solutions

25K

Contributors

Microsoft SQL Server 2005 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. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.

Share tech news, updates, or what's on your mind.

Sign up to Post

Hello

Need assistance writing a query which only returns instances where the Job_Id is the same, but the Product_Id and Type are different.
*See attachment.
Customer	JoB_ID	Product_Id	Type
ABC Movers	33225	1775		RE
ABC Movers	33225	1776		Non_RE
XYZ Ltd	33001	1775		RE
XYZ Ltd	33001	1775		RE

Open in new window

Thanks for your tremendous assistance.
Jobs_EX.xlsx
0
Technology Partners: We Want Your Opinion!
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

hi experts

i have this error
The module being executed is not trusted. Either the owner of the database of the module needs to be granted authenticate permission, or the module needs to be digitally signed.

the patch was installed but the problem persists.
www.png
0
Hi Experts,

I have this query:

Declare
 @REPORTTYPE as varchar(50),
 @POLICY as varchar(2000)

DECLARE @DB_REPORTTYPE as varchar(50)
SET @DB_REPORTTYPE = @REPORTTYPE
DECLARE @DB_POLICY as varchar(2000)
SET @DB_POLICY = @POLICY--'1 Draft VA SQL Server 2012 Policy (2)'

SELECT
	'Please fix to only one' as NeedBothAdded,
	--BOWN.[Branch Region],
	--BOWN.[Branch Network],
	--BOWN.Branch, 
	(Cast(SUM( Case when ActionableResult = 1 THEN 1 ELSE 0 END ) as int)) as [Fail],
	(Cast(SUM( Case when ActionableResult = 0 THEN 1 ELSE 0 END ) as int)) as [Pass],
	Count(*) as [All], 
	(Cast( SUM( Case when ActionableResult = 1 THEN 1 ELSE 0 END)  as float) /Count(*)) as PercentFailed,
	(Cast( SUM( Case when ActionableResult = 0 THEN 1 ELSE 0 END)  as float) /Count(*)) as PercentCompliant,
	Case when ActionableResult = 1 THEN 'No' ELSE 'Yes' END  as Success,
	fac.ComplianceAssetKey,
	fac.OwnershipGroupKey,
	fac.ScanPolicyGroupKey 
	--fac.AssetHostName
	--fac.[CheckName]

FROM [dw].[FactComplianceAssetCheck] as fac 
  inner join dw.brgComplianceScanPolicy as BCP on BCP.ScanPolicyGroupKey = fac.ScanPolicyGroupKey                                   
  inner join [dw].[brgOwnership] as BOWN on BOWN.[OwnershipGroupKey] = fac.OwnershipGroupKey
  inner Join dw.DimComplianceAsset CA On CA.ComplianceAssetKey = Fac.ComplianceAssetKey 
  WHERE fac.IsRowCurrent = 1 
  and Left(BCP.PolicyName, 72) = @DB_POLICY -- Note: Policy name had to be truncated by 72 for the dropdown otherwise way too 

Open in new window

0
Hello guys,

I am trying to do this, but MSSQL is complaining about my syntax, I need your help.

create function dbo.LeFormula(@codpro varchar(24), 
                              @codespec varchar(5),
							  @formula varchar(500), 
							  @CodcampoFormula varchar(100)) 
returns varchar(100)
as
begin 
declare @valor as varchar(100);
  
    select @valor= Exec sp_CALCULO_FORMULA_ESPEC @codpro, @codespec, '', '', '', '', '', @formula, @CodcampoFormula 

   return @valor
End

Open in new window


Thanks
Alex
0
Hi,

  I need to grant the 'SELECT' privileges to a user for a View that belongs to another database, but i do not want to grant 'SELECT' to any table of that database to the user, or even any of the tables that belongs to that view.    What happens is that I only want that user to be able to 'SELECT' to that view, for specific fields that belongs to that view.

  For example.., if the views is built using TABLE A  that has 8 fields, but in the view the display is only viewing  3 fields of that table, the user must be able to SELECT  the view for only to see the fields that belong to the view, but can not see the others fields of the table, or any other table of the database.
 
  Can i do it in MS SQL Server ?

Regards,
0
I'm using SSRS 2016 and facing rendering issue in IE 11 (one e.g. Drilldown button is missing when we browse reports via IE 11 ). It works fine with Chrome, but our end users preferred browser for  SSRS reports is IE 11. Is there anything can be done at server level to fix the rendering issue.

Your help is highly appreciated.
0
IF OBJECT_ID('tempdb..#Test') IS NOT NULL
             DROP TABLE #Test

DECLARE @TextSearch NVARCHAR(1000) = 'India,,'
Select Value into #test  FROM dbo.udf_Split(@TextSearch, ',')

BEGIN
IF @TextSearch IS NOT NULL
    Select CountryRegionCode,Name  from (
    select  DISTINCT CountryRegionCode,Name  from [Person].[CountryRegion] C     
   
    )A
    INNER JOIN #test t on A.Name  like '%' + t.value + '%' and a.name <> ''

ELSE 

    Select CountryRegionCode,Name  from (
    select  DISTINCT CountryRegionCode,Name  from [Person].[CountryRegion] C 
   
    )A

END

Open in new window

0
I have SQL Server 2005 express. Somehow, the program itself was deleted and I need to reinstall it. The .mdf, .ldf files still exist. For the moment, I have renamed the Data folder to Data_Save to avoid overwriting.

How can I go about reconnecting these file after re-installing Express? Should I rename the Data folder back to its correct name before re-installing?
0
Hi Experts,

I have this:
=(Sum(Fields!Resolved_Vulnerabilities_in_30.Value) / Sum(Fields!Total_Vulnerabilities.Value)) * 100

Open in new window


as expression.

But I get this:
2/368,421 = 0.00

5.428

Please help and thanks
0
Hi Experts,

I have this part of a calculation:
Cast(ISNULL(Sum(ResolvedVul60)/NULLIF(Sum(TotalVul), 0) * 100, 0) as Decimal(18,2)) as [Percent 60]

Open in new window


but the outcome Example:
TotalVul = 64
ResolvedVul60 = 5

I get:
0.00


Please help and thanks...
0
Independent Software Vendors: We Want Your Opinion
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

For a subject of leasing will be made renovation of servers, among them the commercial database.

I had mapped the following:
1) Installation of new operating system.
2) Installation of SQL Server software, here would configure the collation to use for my BD, the memory that I will assign to SQL (I think it will be a 64GB of RAM server).
3) Migrate my Logins to the new engine via script.
4) Restore my FULL backup from my old server database.
5) Check the users and their BDs by default, check orphaned users.
6) Migrate DB Link's from my old database.
7) Set up my replica since this server is a subscriber of another server that has the accounting data that would become my publisher.
8) Script the job creation of the old server.
9) Recreate indexes, update statistics, pagination.


With this, keeping the same IP and leaving offline the old server should work everything without problems.
The work will be done at dawn, I have a window of 24 hours.

Anything else you should consider to do this base migration to the new server?

Maybe a checklist for a successful migration.
0
Hi Team,
   I am not able to install SQL server 2008 R2 in windows 8 machines.If I install the setup file after few seconds automatically closed.Not running What should i do?I was tried 32 bit & 64 bit setup file for installation.Both are not able to install
0
Step : 1  - I am doing bulk  insert    -- This works

step 2 : I need to retrieve BillId


CREATE TABLE #T (IntCol int, XmlCol xml); 

INSERT INTO #T(XmlCol)  
SELECT * FROM OPENROWSET(  
   BULK 'c:\Testing\Test.xml',  
   SINGLE_BLOB) AS x;  
   
      select * from #t   

<filename>
<Transaction>
<Billid>100</BillId>
</Transaction>
<Transaction>
<Billid>200</BillId>
</Transaction>
</filename>

Open in new window

0
how do I join two queries, join them HORIZONTALLY, i.e. extra columns, second columns query 2 to right of first query



--query 1 OUTPUTS
SELECT a.timeStampKey, t.timeStamp,MAX(CASE WHEN a.PIE2_O_ID = 1 THEN value END) AS 'K_DESIGN_SG_A_AVERAGE_A', MAX(CASE WHEN a.PIE2_O_ID = 2 THEN value END) AS 'K_DESIGN_SG_B_AVERAGE_B'FROM   tblOutputs as a INNER JOIN       tblTimeStamp as t ON a.timeStampKey = t.timeStampKey GROUP BY a.timeStampKey, t.timeStamp
--query 2 INPUTS
SELECT a.timeStampKey, t.timeStamp,MAX(CASE WHEN a.PIE2_I_ID = 10104 THEN value END) AS Flow, MAX(CASE WHEN a.PIE2_I_ID = 10006 THEN value END) AS Head FROM   tblInputs as a INNER JOIN       tblTimeStamp as t ON a.timeStampKey = t.timeStampKey GROUP BY a.timeStampKey, t.timeStamp
0
Permission issue but i can't figure it out
0
I have the following query, it returns the from dates, works great.

WITH Ordered
AS ( SELECT 
               twt.ClientCode,
			   twt.Word ,
			    
             
              
              ROW_NUMBER() OVER ( PARTITION BY twt.ClientCode
                                  ORDER BY tw.WordPosition ASC
                                ) AS RNF ,
              ROW_NUMBER() OVER ( PARTITION BY twt.ClientCode
                                  ORDER BY tw.WordPosition DESC
                                ) AS RNL
     FROM     Dictionary.[dbo].[TblWords] twt LEFT JOIN Dictionary.[dbo].[TblWords] tw2 ON twt.ClientCodeWordPosition=tw2.ClientCodeWordPosition
              LEFT JOIN Dictionary.[dbo].[TblWords] tw ON twt.ClientCodeWordPosition = tw.ClientCodeWordPosition
      WHERE ISNUMERIC(twt.word)=1 AND LEN(tw2.word)=4 AND TW2.StrFull like '%[1-2][901][0-9][0-9] to [1-2][901][0-9][0-9]%' AND tw2.Word LIKE '[1-2][901][0-9][0-9]'
   )
SELECT *
FROM   Ordered O
WHERE  O.RNF = 1;

Open in new window


I would like to insert the date into another table

INSERT INTO TblDataExtraction (Clientcode, DatefromExtract)

Open in new window


The client code  must be
twt.ClientCode+'datefrom'+twt.word

Open in new window

The datefrom extract is from field twt.word

i dont need to put rnf or rnl into the other table

I dont know how to do the insert when using this query structure.

ex
0
Hi Experts,

This there a better way to write:
cast(sum (case when (nn1.iplong is null and nn1.plugin is null) and (sa.plugin is not null and sa.iplong is not null) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 30],

Open in new window

I have several CASE When statements that are similar.

something like:
Cast(Sum(ISNULL(nn1.iplong  and nn1.plugin) and IS NOT NULL(sa.plugin and sa.iplong ) then 1 else 0 end ) as Decimal(18,2)) / cast(count(sa.scandate) as Decimal(18,2))* 100 as [Percent 30],

Open in new window

[/code]

Is this correct?  Seems this would make better performance if several of these Case when statements could be replaced?

Thank You for helping me...
0
Hi,

I am looking for SQL Server Interview Questions and Answers links and material, additionaly looking for SQL Server Certification

Thanks
Chandra
0
Hi

I want to express the conditions for a column in a SQL query in the Select part rather than the Where part of the SQL

So I am using

Select Case When [ID] = 234 Then [Quantity] End

Instead of

Select [Quanity] Where [ID] = 234

Is this a good way of doing it?
0
Important Lessons on Recovering from Petya
LVL 10
Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

The first query needs an additional column that shows the blocking text...but I eliminate that from the where clause...so am thinking I need a union...or some other outer query.. please help.  I have put a 2nd query below that will return the blocking test.
I need to unify this logic into one output.

select sp.spid,sp.blocked, sp.last_batch,sp.waittime,sp.waitresource,sp.lastwaittype,sp.cmd, DB_NAME(sp.dbid),
st.[text],sp.loginame,sp.hostname,sp.cpu
from sys.sysprocesses sp
cross apply sys.dm_exec_sql_text(sp.sql_handle) st
where sp.spid>50 and sp.spid<>sp.blocked and sp.blocked<>0 and datediff(mi,last_batch,GETDATE())>2
---final touch for sending mail notification when blocking duration goes above 2 Minutes

Open in new window



2nd query that does get the blocking text I am after...but  I need it an aditonal column in the above query.

select qt.text as [blocking text]

FROM master.dbo.sysprocesses sp
LEFT JOIN sys.dm_exec_requests er
    ON er.session_id = sp.spid
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)
AND blocked = 0

Open in new window

0
Hi Experts,

 I am trying to create an Expression:
=IIf(IsNothing(Fields!Field1.Value/Fields!Field2.Value),"#ERROR",Fields!Field1.Value/Fields!Field2.Value)

Open in new window


I am getting:
NaN in the field.

In the SP:
Select
NULL as [Field1]
NULL as [Field2]

Please help and thanks
0
how to write a query to genate a target column?

can any one help me
0
Hello Experts,
can someone help,
I need to group it by VehciletypeID

SELECT   'Montreal' AS City ,FO.OrderDate,
(Select Description from VehicleTypes where VehicleTypes.VehicleTypeID = FO.VehicleTypeID) AS [Vehicle],
(Select Name From Drivers where Drivers.DriverNumber = FO.PickupDriver) AS [PickupDriver],
(Select Name From Drivers where Drivers.DriverNumber = FO.DeliveryDriver) AS [DeliveryDriver],
(Select Name From Drivers where Drivers.DriverNumber = FO.Driver3) AS [Driver3],
(Select Name From Drivers where Drivers.DriverNumber = FO.Driver4) AS [Driver4],
(Select Name From Drivers where Drivers.DriverNumber = FO.Driver5) AS [Driver5],
DATEADD(wk, DATEDIFF(wk, 7, GETDATE()), 0) As Monday,
DATEADD(wk, DATEDIFF(wk, 7, GETDATE()), 1) As Tuesday,
DATEADD(wk, DATEDIFF(wk, 7, GETDATE()), 2) As Wednesday,
DATEADD(wk, DATEDIFF(wk, 7, GETDATE()), 3) As Thursday,
DATEADD(wk, DATEDIFF(wk, 7, GETDATE()), 4) As Friday
FROM     Finalizedorders FO
WHERE    FO.orderdate BETWEEN DATEADD(wk, DATEDIFF(wk, 7, GETDATE()), 0) AND DATEADD(wk, DATEDIFF(wk, 7, GETDATE()), 6)
AND NOT EXISTS (SELECT * FROM  Clients C WHERE  C.AccountNumber = FO.AccountNumber
AND (AccountCode LIKE 'K%' OR AccountCode LIKE 'A%' '))

Attached is a sample of the output, and how I would like to group it.

thanks for your help,
Sample.xlsx
0
Hello,
Can you please help,
I need to add the case statement in a Croos Apply

SELECT FO.Orderno , FO.Orderdate , FO.Accountnumber,LEFT(FON0.NoteID , LEN(FON0.NoteID)-1) NoteID,
LEFT(FON.reason , LEN(FON.reason)-1) reason
FROM FinalizedOrders FO
CROSS APPLY (SELECT CONVERT(varchar(10), FON0.OrderNoteTypeID)  + '__' FROM FinalizedOrderNotes FON0 where FO.[Orderno] = FON0.[Orderno] AND (FON0.OrderNoteTypeID in (9,10,11,12,13,14,15,16,17,18,19,20,21,22,23)) FOR XML PATH('')) FON0 (NoteID)
(CASE       WHEN FON0.OrderNoteTypeID = 1 THEN 'A'
      WHEN FON0.OrderNoteTypeID = 2 THEN 'D'
      WHEN FON0.OrderNoteTypeID = 3 THEN 'Y'
      WHEN FON0.OrderNoteTypeID = 4 THEN 'X') END
CROSS APPLY (SELECT FON.NoteText + '__' FROM FinalizedOrderNotes FON where FO.[Orderno] = FON.[Orderno] AND (FON.OrderNoteTypeID in (9,10,11,12,13,14,15,16,17,18,19,20,21,22,23)) FOR XML PATH('')) FON (reason)
WHERE xxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Is this possible?
Thanks,
0
In my select below...
I need to find duplicate values in the list

In other words...
IndividualID (12345)
May have 6 rows of data
In that 6 rows
I need to know id=f any two rows are duplicates

SELECT   i.IndividualID ,
         i.FirstName ,
         i.LastName ,
         p.PaymentID ,
         p.DateAdded ,
         p.Amount ,
         p.AmountRefunded ,
         p.UserID ,
         u.FirstName ,
         u.LastName ,
         ROW_NUMBER() OVER ( PARTITION BY i.IndividualID
                             ORDER BY p.PaymentID DESC
                           ) rn
FROM     dbo.ewPayments p
         JOIN Individuals i ON p.IndividualID = i.IndividualID
         JOIN Users u ON p.UserID = u.UserID
ORDER BY i.LastName ,
         i.IndividualID;

Open in new window

0

Microsoft SQL Server 2005

71K

Solutions

25K

Contributors

Microsoft SQL Server 2005 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. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.