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

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
On Demand Webinar: Networking for the Cloud Era
LVL 9
On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

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
I Need to find all the Wordpress Post that do not have a specific meta data value

select p.`ID`,m.meta_key,m.meta_value 
from wp_posts p, wp_postmeta m 
where p.post_status ='publish' and 
p.`ID`= m,post_id in (select post_id from wp_postmeta where  meta_key = 'DocID');

Open in new window


gives all the published posts with a DocID.
I need to filter it further on another m.meta_key that is missing from some posts

in sudo code it would look something like
select p.`ID`,m.meta_key,m.meta_value 
from wp_posts p, wp_postmeta m 
where p.post_status ='publish' and 
p.`ID`= m,post_id in (select post_id from wp_postmeta where  meta_key = 'DocID')
and not exists (m.meta_key = 'wanted')

Open in new window


Hoping this makes sense and someone  can show me a better way
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
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
NFR key for Veeam Agent for Linux
LVL 1
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

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
Hi there,
I am trying to load info from one table directly into a new table (replicated in the same way), so I can keep an archive , since the org table clears out every 30 days.
I setup a simple ETL flow, but it just hung for hours.
So I wrote an insert statement inT-sql and this took some time, but after hours did run.
so I rewrote another insert statement using top 150k entries per load and this seems to run faster.
so I thought I would use a query insert on SSIS and tried this, but the same query still hangs for hours rather than running in seconds as it did on management studios.

I'm not sure what to look for her, can anyone please advise?
thank you,
Elaine.
0
When I execute a stored procedure
Is there any way to log or track what table triggers execute?
0
I have a column that I need to always be 4 characters

It may at timesbeb 2 or 3

In thise cases I need to pad out to the left some zeroes.
0
Hello,
Can you please help,
I'm using below code, but it  returns multiple rows.
I need to combine the NoteText, seperated by __

Select Distinct FO.Orderno AS [Order #],FO.Orderdate AS [Order Date], NoteText AS [Reason]
From FinalizedOrders FO
INNER JOIN FinalizedOrderNotes ON FO.OrderNo=FinalizedOrderNotes.OrderNo
Where NoteText Like '%QA1%'
AND FO.orderdate >= ................. and ............... and ..................

Example Results:
Order #                       Order Date                                  Reason
7517502                       2017-07-10 08:31:55.000      Q1-Test1
7517502                       2017-07-10 08:31:55.000      Q1-Test2
7517502                       2017-07-10 08:31:55.000      Q1-Test3

Would like to have it
7517502                       2017-07-10 08:31:55.000      Q1-Test1__Q1-Test2__Q1-Test3

Your help is appreciated.
0
Hi there,
  I am in process of configuring logon trigger.
My need for this trigger is to restrict few SQL logins to only login from particular IP address:

I  got the below code from google, but when I setup the trigger, first time the user can able to login but next time when we disconnect and try again then the trigger is not letting to login the sql server even though we tried from the same machine(same IP address)

CREATE TRIGGER [TR_check_ip_address]
ON ALL SERVER
FOR LOGON
AS
BEGIN

    DECLARE @ip_addr varchar(48)

    SELECT @ip_addr = client_net_address
    FROM sys.dm_exec_connections
    WHERE session_id = @@SPID

    IF ORIGINAL_LOGIN() = 'Test' AND @ip_addr <> 'xxx.xx.xxx.xx'
        ROLLBACK;

END

thanks
Deepak
0
What Is Blockchain Technology?
LVL 4
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

I have a table that stors table counts with a running total at a particular time
SELECT tbl, tme, cnt  FROM #RunningCount ORDER BY tbl, cnt
I need to add logic that partitions by tbl value
And gets the difference between the most recent row and the provious row cnt for THAT tbl
0
Hi Experts,


Currently I have created a temp table.  But the problem is when there are large amounts of data.  After the temp table is created (yes very large) then there is a where clause on that temp table to reduce the amount of records.
Declare
@DB_@STATUSTYPE varchar(255) = 'No Status'


Select *
From #TempTable
Where ([StatusType] = @DB_@STATUSTYPE)


This is in the select statement when inserting into the temp table:

Select
CASE WHEN cce.CVA_StatusID = 1 THEN cvs.StatusType
         WHEN cce.CVA_StatusID = 4 THEN cvs.StatusType
         WHEN vra.Actionable = 0 THEN 'Not Actionable'
         WHEN cce.CVA_StatusID IS NOT NULL THEN cvs.StatusType ELSE 'No Status' END AS [StatusType],

From TableName as cce

so this can be done more faster I would like to include:

Where
([StatusType] = @DB_@STATUSTYPE) instead of on the temp table

But because this is a case statement it does not work this way.

What do I have to do in the Where statement to get this to work.

Please help and thanks
0
Hi

In what scenario should I use this command?
0
I need to change the password of a user at SQL Server level (main login of systems).

But my doubt is that other things I should check before making this change, that other factors can be affected by doing this since this user used it almost for everything within this DB.

Obvious that at the application level will make the respective changes with the new password (it is a production pass-through server, nobody has access to this), but my doubt comes from things in the database itself, for example, as the Replicas that I have to other servers, sending mails, or where else should I look where maybe that password is left and I have to update it for the new one perhaps manually.

Here are several servers, I have to check all and see which are connected to my main server, since changing the password will cause several (load processes, dblinks, etc) to fall.

What is the correct way to make this change to decrease the margin of error
0
I am having a problem with my production database and need to know how to fix this issue. This database is currently in use. The error I am getting when I run DBCC CHECKDB with NO_INFOMSGS is below:

Msg 8967, Level 16, State 216, Line 1
An internal error occurred in DBCC that prevented further processing. Contact Customer Support Services.
Msg 8921, Level 16, State 1, Line 1
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.

I have ran DBCC CHECKDB with NO_INFOMSGS on the master, Tempdb, msdb, model and all came back fine but when I run it on my production database for our ERP system I get the above error. How can I fix this?
0
How can I estimate the local area network utilisation involved in remotely executing a simple SQL Server Stored Procedure ?

I have a windows application that uses ADO to execute a stored procedure on a Microsoft SQL Server 2005 database server.
The stored procedure is simple and returns a single result in an  integer output parameter.

I am executing this procedure every 10 seconds and have been asked to estimate the "network utilisation".  There are no input parameters to the stored procedure. I will be answering the question as "virtually no network utilisation" but I anticipate being asked how to justify this statement.
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.