Go Premium for a chance to win a PS4. 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

I need a function I can wrap a column in to scrub out invalid xml characters

It is a varchar(1000) column



Select dbo.scrubhtml([myCol]) from tblPersonData
0
Prepare for your VMware VCP6-DCV exam.
LVL 1
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

hi experts
I have a server that is already making the backup and I want to save it somewhere else (Shared Path), it is understood that I should create a folder and give the permissions to (NT SERVICE \ MSSQLSERVER), but it does not take it, because that I must be on my own PC or server, in these cases as it is done, since it is understood that the backup of the database must not be on the same server.
0
Hi Experts,

I have this bit of code:
Declare @NotUsedDays as int 
Set @NotUsedDays = 120

SELECT Distinct 
Name,
Path,
Convert(Date, [LastUsedDate], 120) as LastUseDate,
NotUsedsince=DATEDIFF(DD,LastUsedDate,GETDATE()),
Statuskey

  FROM [LSV].[BISL_SSRSLog].catalog C

  JOIN (

                SELECT ReportID,LastUsedDate= MAX(timestart), StatusKey 

                     FROM [LSV].[BISL_SSRSLog].[ExecutionLog]

                       WHERE RegionName = 'Region that is in charge of SharePoint'

                       and ReportLocation = 'Location'

                       and Environment = 'Production'

                       and SPVersion = 2013

                       and (DateKey like '2016%' Or Datekey like '%2017%')
					    and [USERNAME] Not In ('bah1', 'bah2')


                 GROUP BY ReportID, StatusKey

          ) E

       --ON C.ItemID = E.ReportID

       ON C.ReportID = E.ReportID

 WHERE DATEDIFF(DD,LastUsedDate,GETDATE()) >= @NotUsedDays And StatusKey <> 34 -- 34 = Non existing reports??? 

 ORDER BY NotUsedsince desc

Open in new window


As you can see I am guessing that 34 means the reports are not available out on the share.  I am also guessing that column "Statuskey" is the correct column?

As a result I get reports that are no longer available (they have been deleted)

I want only reports that are available to come up in the query.

Thank You,
0
What SQL account will give me access to create?

•      bulk updates
•      stats monitoring
•      insert
•      VIEW SERVER STATE
0
Last weekend we moved our SQL Server from one server (Windows 2003, SQL 2005) to a new server (Windows 2012, SQL 2012) and copied over all the users and roles to the new server. The Migration went rather smoothly and the users are able to use the database without problem however all the notifications that come from that server are no longer sent. The accounts are still there the Jobs are still there and running without problem but the notifications are not going out. I have checked the SQL Agent and emailing is enabled and I can send out emails to my email from Database Email yet no notifications are sent.

What could be the issue? No firewall is on and the Exchange server it not rejecting the emails eventhough they are being sent from DB Mail. I Don't know where else to look.
0
Good morning

how can I make a query Sql where I pass a range of dates and returns me a column with the day and another column with the day in string.


date.                        day
04/11/2017         Saturday
05/11/2017           Sunday


Thank you
0
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
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
What does it mean to be "Always On"?
LVL 5
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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 have a query, when I try to run it commenting a logic defined below, it runs fine.

But I need to include this logic and run the query, the query is taking hell lot of time. 2 hours for 1 lakh record.

PLEASE HELP ME CHAMPIONS.


SELECT
--CASE WHEN (select distinct  1
--from TBL c            
--where EXISTS (SELECT 1 FROM TBL1 leave            
--               WHERE convert(date,c1.DT) = leave.DT
--                 ))
--IS NOT NULL THEN 1 ELSE 0 END AS LEAVE

FROM (select *,
case when test= 'A' then DATEADD(hour,8,Date)  
     when test=  'B' then DATEADD(hour,2,Date)  
     when  test= 'C' then DATEADD(hour,-4,Date)
     else Date end as DT

 from STAGING c
 ) c1
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
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
Free Tool: Port Scanner
LVL 11
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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
I have a table having varbinary column which is primary key.
Whle trying to retrieve the data as below gives 0 result:

Select count(*) from Trn_Account where cast(TransID as varchar(20)) = '3038D'

below query gives me 1 result:
Select * from Trn_Account where  TransID=  cast('3038D' as varbinary(20))

Below is my table structure:
Trn_Account:

TransID      varbinary      no      20                            no      no      no      NULL
TDate      datetime      no      8                            no      (n/a)      (n/a)      NULL
TrnsNo      numeric      no      9      18      0      no      (n/a)      (n/a)      NULL


Please help.
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.