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

I have date format 2017-08-17 10:04:44.697 in my sql server table. I want to select rows for particular date irrespective of time. What date formate i need to use in my where condition for performance point of view?
0
Free Tool: ZipGrep
LVL 9
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Hi Experts,


I have a case when statement
when cce.CVA_StatusID is not null then cvs.StatusType else 'No Status' end as [Status]

Open in new window


and would like to do:
INNER JOIN  @SEV as sta on sta.stat = Status

But one can see this is not the correct way to do this.

Any idea would be great?  Maybe a CTE?  but would need help.

Thank You....
0
I can not send mail through the SMTP connection manager (send mail task). System.net.webexepction

attched file
www.png
0
hi,

I am studying diff in Oracle and MS SQL, do they have any connection limit?
0
Hello,
How can i add a field in a table  at particular position.

Is it possible to avoid dropping the table and recreating it.

Regards
0
Hello Experts

I have developed a package that works very well in development. I do 2 things
1. I publish it to the SSIS server
2. I call you through a job

In the execution of the job falls for a subject of permissions
This is the error
Error: 2017-08-12 13:53:28.56
   Code: 0xC0016016
   Source: DWH 
   Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Clave no v lida para utilizar en el estado especificado.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.
End Error
err_DTS.txt
0
convert month name to month number in sql server 2012
Eg : Aug means current yearand month (201708)
0
Hi,

I am studying the pros and cons of multi site architecture of MS SQL, oracle, MySQL and sybase. I know about MySQL and I am focusing on MS SQL now.

other than AOG, anything else can be good for MS SQL on 2 x data center MS SQL configuration/topology ?
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 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
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.

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
The logon account for the SQL Server service cannot be a local user account or LOCAL SERVICE.  You must configure the SQL Server service to use a valid domain account, NETWORK SERVICE, or LOCAL SYSTEM.
Im running Windows Server 2008 R2 and i have installed SQL Server 2008 R2.
I have changed the accounts from Network Service account to Domain account with admin rights and still overtime i restart the service and then re-run the checker it still fails.

Anyones help would be highly appreciated.

Ciao
0
Hello,
How to write a query to find year from a date parameter in sql stored procedure.

Regards
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
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
What statement would you use to remove all rows from a table
0
I was trying to install MS SQl server 2008R2 on windows Server 2012R2.
But the installation was successfull with following error message.

What problem could i face in future ?

Error Message-----------------------
Note : PkgMgr.exe has been deprecated.Please update your scripts to use DISM.exe to install,COnfigure, and update features and packages
and packages for windows.

See http://go.microsoft.com/fwlink/?LinkID=195329 for more information.

Operation failed with 0x8007000B
---------------------------
0
Get your Disaster Recovery as a Service basics
LVL 1
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

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
actually i am making a pharmacy maintenance project using sql server and i want to fire a trigger from dbo.SaleInfo to dbo.stockinfo using the foreign key in both tables MedID and when stock is decreamented it reduce the stock from dbo.stockinfo?
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

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.