Microsoft SQL Server 2008

49K

Solutions

17K

Contributors

Microsoft SQL Server 2008 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. Major improvements include the  Always On technologies and support for unstructured data types.

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

Sign up to Post

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
Free Tool: Port Scanner
LVL 9
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.

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
hi,

I am studying diff in Oracle and MS SQL, do they have any connection limit?
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
I have a sql server table that looks like this

Table.PNG
The script to create table looks like this:

USE [Northwind]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EmployeesTest1](
	[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
	[LastName] [nvarchar](20) NOT NULL,
	[FirstName] [nvarchar](10) NOT NULL,
	[Title] [nvarchar](30) NULL,
	[TitleOfCourtesy] [nvarchar](25) NULL,
	[BirthDate] [datetime] NULL,
	[OrderDate] [datetime] NULL,
	[Address] [nvarchar](60) NULL,
	[City] [nvarchar](15) NULL,
	[Region] [nvarchar](15) NULL,
	[PostalCode] [nvarchar](10) NULL,
	[Country] [nvarchar](15) NULL,
	[HomePhone] [nvarchar](24) NULL,
 CONSTRAINT [PK_EmployeesTest1] PRIMARY KEY CLUSTERED 
(
	[EmployeeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[EmployeesTest1] ON 

INSERT [dbo].[EmployeesTest1] ([EmployeeID], [LastName], [FirstName], [Title], [TitleOfCourtesy], [BirthDate], [OrderDate], [Address], [City], [Region], [PostalCode], [Country], [HomePhone]) VALUES (1, N'Davolio', N'Nancy', N'Sales Representative', N'Ms.', CAST(N'1968-12-08T00:00:00.000' AS DateTime), CAST(N'2014-05-01T00:00:00.000' AS DateTime), N'507 - 20th Ave. E.
Apt. 2A', N'Seattle', N'WA', N'98122', N'USA', N'(206) 555-9857')
INSERT [dbo].[EmployeesTest1] ([EmployeeID], [LastName], [FirstName], [Title], [TitleOfCourtesy], [BirthDate], 

Open in new window

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 EE,

It is possible in SQL Server to trace whether a SQL query is generated from;
- A stored procedure and/or function via the application parsing some parameters.
- The application itself parsing whole SQL queries to the database.

Any assistance is appreciated.

Thank you.
0
Hi EE,

I have been handed the following script:

/* 2008r2 and older */
USE master;
GO
-- Create the Event Session
IF EXISTS ( SELECT *
				FROM sys.server_event_sessions
				WHERE name = 'AuditDeprecated' )
	DROP EVENT SESSION AuditDeprecated
    ON SERVER;
GO

EXECUTE xp_create_subdir 'E:\Database\XE';
GO


CREATE EVENT SESSION [AuditDeprecated] ON SERVER
ADD EVENT sqlserver.deprecation_announcement (
	ACTION ( sqlserver.database_id, 
	sqlserver.nt_username, sqlserver.sql_text, sqlserver.username,sqlserver.session_nt_username,
	sqlserver.client_app_name, sqlserver.session_id, sqlserver.client_hostname)
	WHERE  [sqlserver].[database_id] > 4 --exclude system databases
			AND [sqlserver].[client_app_name] <> 'Microsoft SQL Server Management Studio - Transact-SQL IntelliSense'
		),
ADD EVENT sqlserver.deprecation_final_support (
	ACTION ( sqlserver.database_id, 
	sqlserver.nt_username, sqlserver.sql_text, sqlserver.username,sqlserver.session_nt_username,
	sqlserver.client_app_name, sqlserver.session_id, sqlserver.client_hostname)
	WHERE  [sqlserver].[database_id] > 4
			AND [sqlserver].[client_app_name] <> 'Microsoft SQL Server Management Studio - Transact-SQL IntelliSense'
		)
ADD TARGET package0.asynchronous_file_target (  SET filename = N'E:\Database\XE\AuditDeprecated.xel'
									, max_rollover_files = ( 25 ) )
WITH (  MAX_MEMORY = 4096 KB
		, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS
		, MAX_DISPATCH_LATENCY = 30 SECONDS
		, MAX_EVENT_SIZE = 0 KB
		, 

Open in new window

0
Given a table that looks like the following:
TableI need to construct a query that will produce the effective properties for a given object. A matrix to represent this might look like this:
MatrixThe rules are as follows:
* The inheritance order is known at the time the query is run. It is not guaranteed to be in any sorted order.
* The inheritance list may be anywhere from 2 to n layers deep
* The first non-null property becomes the effective property.
So, for Id 203 the effective properties would be:
Width: 500
Height: 200
Color: Blue
Font: Helvetica

For Id 47 the effective properties would be:
Width: 600
Height: 200
Color: Black
Font: Arial

I need a query that will accept a list of IDs of arbitrary length and coalesce all the property values to achieve a table with the effective properties of that object. Anyone know how I might do this?

I'm not sure if I'm asking this question the right way so please feel free to ask for clarification.
0
Hello,
How to write a query to find year from a date parameter in sql stored procedure.

Regards
0
Veeam Task Manager for Hyper-V
LVL 1
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

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
Right now I have some old SQL which uses a cursor to perform this operation. I need to turn it into a set based operation. Given the following data:
BeforeHow do I insert a value into LineNumber such that each line with a duplicate invoice number has a sequential line number and the value of LineNumber resets to 1 every time a new invoice number is found. The end result would look like this:
AfterThe actual order of LineNumber isn't particularly relevant in this case as long as each row with the same InvoiceNumber gets a unique LineNumber within its own group.
0
Hi.
Is anyone out there using SQL 2008r2 as a witness for mirroring SQL 2014 databases ?

Thanks
0
Hi EE,

Note: The following SP works in my UAT environment, not in production though.

I trying to execute a Stored Procedure called Security_Extract see attached. However, when I run the following the query:
USE msdb
 EXEC sp_send_dbmail
      @profile_name = 'Security_Extract',
      @recipients = '',
      @subject = 'Database access report',
      @body = 'The result from exec Security_Extract are below.',
      @execute_query_database = 'msdb',
      @query = 'exec Security_Extract'

Open in new window


To execute the Stored Procedure it I get an error message:

Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 504
Query execution failed: -----------------------------------------
-- Users accessing databases on Server --
-----------------------------------------
LoginName                                                                      SID                                                                                     DefDBName        DefLangName          AUser ARemote
------------------------------------------------------------------------------ --------------------------------------------------------------------------------------- ---------------- -------------------- ----- -------
##MS_AgentSigningCertificate##                                                 0x01060000000000090100000046118475F61285F02F9210FAF708E0B1B357ABF7                     …
0
Im looking for a query to find the users those
who modified the SQL physical memory in SQL server 2008 R2 enterprise edition.
0
hi,

we are looking for continue data protection on SQL server so that RPO/RTO=0.

any idea?

any tools needs?

https://technet.microsoft.com/en-us/library/hh758033(v=sc.12).aspx
?
0
how to write a query to genate a target column?

can any one help me
0
Hello - we're setting up our application on SQL Svr 2012 and are looking for quick help with the best-practice performance settings for SQL.  For example - we know with "Max Degrees of Parallelism" its recommended to set that at "0" for auto-detect, correct?  What else should we look at?

Thanks for your help.
Damian
0
U.S. Department of Agriculture and Acronis Access
LVL 4
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Trying to sum an alias column and group by an ID.  

	select dt.member_id, dt.NumberOfMonthsBetween
	FROM ( 
			select member_id, DATEDIFF(month,date_credentialed,renewal_date) as NumberOfMonthsBetween
			from member_credential
		  ) dt
	where dt.NumberOfMonthsBetween > 6

Open in new window



Current Result:
_____________________________________________
|Member_ID |  NumberOfMonthsBetween|
---------------------------------------------------------------
|    48             |                11                              |
|     1              |                12                              |
|     6              |                12                              |
|     8              |                12                              |
|     8              |                8                                |
|    41             |               11                               |
|   48              |               10                               |
---------------------------------------------------------------


What I am looking for:
_____________________________________________
|Member_ID |  NumberOfMonthsBetween|
---------------------------------------------------------------
|     1              |                12                              |
|     6              |                12                              |
|     8              |               20                               |
|    41             |               11                               |
|   48              |               21                               |
---------------------------------------------------------------
0
I have two tables with related data in them. They can be joined on DocNbr (which is a varchar(10) column). I am trying to audit deletes from these two tables by sending a simple email when a record is deleted. The problem becomes, I am trying to include data from both tables in the email body and I'm not able to, as trying to join the two tables together on the DocNbr field isn't possible any longer (as one or both tables have now had their record deleted).

What are my alternative options here?
0
Hello.

I would like to start an eCommerce store.  We already have a commercial business, but we are not selling through a website.
We have about 150,000 items that we would like to list.

Our database is managed locally by MS SQL-server 2008 R2.  We would like our sever to upload our products using SSMS.

I looked at sites including Volusion and Web.com which offer turn-key eCommerce sites with lots of templates, however none of these sites use MS SQL server, or at least, they do not expose the management feature of MS SQL that are provided with typical hosting plans.

Instead, these sites commonly use a website interface to upload the products database.  This does not work for us because 1, it can not be automated, and 2, they have file size limitations.  For example, Volusion only allows us to upload 500 items at a time, which would mean we would need to do about 30 uploads manually, every day.  This is not an option.

Does anyone know of an eCommerce site that allows us to upload using MS-SQL?  We don't want to do a custom site because it will take too long to roll out.

Thanks
0
Hi experts,

I'm using sql server 2008.

I have a table with a column called Phones1 that holds phone numbers.
Some phone numbers are 7 digits and some are 10 digits.

I have a select query that gets all the columns from my table

if a phone number is 7 digits I want to format it like this:  123-4567
if a phone number is 10 digits I want to format it like this:  (204)123-4567

What's the best way to format this column like this  in my query?
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 2008

49K

Solutions

17K

Contributors

Microsoft SQL Server 2008 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. Major improvements include the  Always On technologies and support for unstructured data types.