Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

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

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
Concerto's Cloud Advisory Services
LVL 4
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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
My question is in the following, I have a profile in my databaseMail (it's another server) this has been working fine, but in the last week many emails are glued and no longer sends anything, I have to stop the database mail service and start it again. This is how it works all morning and falls back down. I've searched all forums and what I find is restart the service.
Or what solution recommends
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
Back Up Your Microsoft Windows Server®
LVL 4
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

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
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
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
Nothing ever in the clear!
LVL 1
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

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
I'm looking at examples and I need to insert the result of this CTE into another table.

I created a temp table to insert the results into but I don't know where the insert statement goes.

I tried it right after the CTE query , before it but it didn't work.

How can I do this? This is SQL 2016

;with cte as
(
SELECT  n.c.query('.') entirequery,
       n.c.value('(//num/node())[1]','varchar(max)') ChapterNumber,
	   isnull(n.c.value('(//heading/node())[1]','varchar(max)'),'') AS ChapterName,
       n.c.value('num[1]','varchar(max)') AS 'Subchapter',
	 	stuff (' '+n.c.query('(heading)').value('.', 'varchar(max)'),1, 1, '') subchapterTitle,  

		--section num
	
		stuff (' '+n.c.query('(section[1]/num)').value('.', 'varchar(max)'),1, 1, '') SectionNum1,  
		stuff (' '+n.c.query('(section[2]/num)').value('.', 'varchar(max)'),1, 1, '') SectionNum2,
		stuff (' '+n.c.query('(section[3]/num)').value('.', 'varchar(max)'),1, 1, '') SectionNum3,
		stuff (' '+n.c.query('(section[4]/num)').value('.', 'varchar(max)'),1, 1, '') SectionNum4,
		stuff (' '+n.c.query('(section[5]/num)').value('.', 'varchar(max)'),1, 1, '') SectionNum5,
		stuff (' '+n.c.query('(section[6]/num)').value('.', 'varchar(max)'),1, 1, '') SectionNum6,
		stuff (' '+n.c.query('(section[7]/num)').value('.', 'varchar(max)'),1, 1, '') SectionNum7,
		stuff (' '+n.c.query('(section[8]/num)').value('.', 'varchar(max)'),1, 1, '') SectionNum8,
		stuff (' '+n.c.query('(section[9]/num)').value('.', 'varchar(max)'),1,

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.