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

Permission issue but i can't figure it out
0
Optimize your web performance
LVL 1
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

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
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
I've got a client who uses Windows Server 2008 Standard and it appears that sqlservr.exe is taking up enormous amounts of RAM and file sizes.  5GB of RAM to run one instance.

How do I reduce this?  

They are 15 users, using the server for file storage and domain controller/AD only.  Used to run Exchange Server, SharePoint, etc. but now no Exchange, no SharePoint.  If I am running AD, DC, DNS, DHCP, do I have a need for SQL Server?

I need to trim this down, as the system is constantly reading/writing tons of data, and the only massive thing going on appears to be sqlservr.exe.
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
On Demand Webinar: Networking for the Cloud Era
LVL 9
On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

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
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
Want to be a Web Developer? Get Certified Today!
LVL 9
Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

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
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
Can anyone give me step-by-step instructions on how to just get this task to successfully execute my stored procedure.  The error messages are getting worse
  • "Value does not fall within the expected range.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly"
  • "The query failed to parse. Attempted to read or write protected memory. This is often an indication that other memory is corrupt."
  • "The EXEC SQL construct or statement is not supported."

At this point, I'd just like to get the stored procedure working.  All it's doing is having a parameter string value passed into it from a user defined variable in the package and then assigning a value to the SP output parameter which then gets assigned to a different package user variable.

My environment:  Windows 7 Professional, SQL Server/Visual Studio 2005

Any suggestions?
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

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.