|
I have run across this in stored procedures, one of the more esoteric problems was "parameter sniffing", basically it comes down to moving your input parameter values to local variables.
Create proc ....
@InputID int
as
Declare LocalID int
Set @LocalID = @InputID
And use @LocalID int the body of the procedure
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
how to find one employee belongs to max dept
|
|
|
|
|
Please think about what your question says. We have no idea what your table structures are, or what fields are associated with an employee record.
|
|
|
|
|
What is "employee"?
What is "dept"?
What is the relationship between them?
|
|
|
|
|
With as little information as you have supplied this may give you some ideas.
select *
from Employee
where DepartmentID =(
select top 1 DepartmentID from(
select DepartmentID , count(*) C
from Employee
group by DepartmentID) x order by C desc)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi I have a SQL Agent task which sends an email to all staff. The email is different because it sends it to a group which has "require that all senders are authenticated" ticked in their settings. This task works fine from one node in our availability group. I've copied the job to a secondary node and it says successful but no email is sent. We've investigated and can see a blocked email with the error: "Your message can't be delivered because delivery to this address is restricted".
I have restarted the SQL Agent service which didn't make any difference. The database mail looks to be configured in exactly the same way it is on the other node where it works fine. Any ideas why it is not authenticating properly please?
A reboot is an option but we'd rather not do this.
|
|
|
|
|
I suspect that message is originating from your email service not your application. So your service is attempting to send email via an API call to a server, and that server is telling you that it doesn't like what you are doing.
That is a configuration issue and probably one that can only be fixed via the email service. As a guess perhaps there is a security restriction based on IP/DNS (box of agent) and it needs to be updated.
|
|
|
|
|
Thanks for your help and reply but I think the solution isn't to change the security setting on the email group - it is to ensure that the user authentication is completing successfully. This is what I have no idea about how to check. A reboot of the node may resolve it but I'd like to think it may be caused by a SQL Server user configuration setting that I can test/change and resolve without the need for a reboot.
I'd like to understand more about the problem as I don't have much knowledge of diagnosing this user configuration/authentication.
|
|
|
|
|
Hi,
We are migrating to a new server, since the location of the server has changed. With that said, we are process of migrating over the report server. I backedup and restored two databases "ReportServer" and "ReportServerTempDB". We have configured the reporting services.
Now we have many reports, that have the datasource specified within the report as well. There are a total of 426 rows in the "DataSource" table. How do we find the datasource for these reports? The table has mostly encrypted values.
thank you!
|
|
|
|
|
We're about to migrate from SQL Server 2008R2 to SQL Server 2016. We have SSIS packages in both the package store and the file system. I want to establish a standard beginning with this migration so that we always put packages into the same location.
So, package store, or file system, and why?
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
Since the package store allows you to store your packages in either the File System or the Database I suspect the question you you actually want an answer for is whether to store your packages in the File System or the Database. The package store itself is more of a management solution.
Pros for Database: One point of backup, and probably easier to handle in a multiple instance system. Easier to use from any server or client.
Pros for File System: Easier to export/import between systems. Just copy the files.
I suspect it's mostly depends on how you work and develop your packages, I would also guess there are security concerns to think about.
|
|
|
|
|
John Simmons / outlaw programmer wrote: So, package store, or file system, and why?
A file-system is preferred for files that are manipulated a lot. A database is preferred for files that are mostly read and not changed.
My "why" is based on the Dokan-drive experiment; a database will grow quick if you keep editing small files. It is convenient OTOH to have all your files in a single db-file.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
The only thing consistent about our data is change. Requirements sometimes change on an hourly basis.
File system packages FTW.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
FS, in an Active Directory and with regular backup 
You could opt for a database if you have a lot of pictures or music that doesn't change; in that case, a NOSQL db might perform better than SQL Server.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
I work for the DoD. We use Sql Server. Period. That will not change for the foreseeable future. None of our data involves pictures/music (we don't write mobile crapps).
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
John Simmons / outlaw programmer wrote: I work for the DoD. We use Sql Server. Period.
Yes, unlimited money brings unlimited idiocy
Most of us need to be efficient though.
John Simmons / outlaw programmer wrote: None of our data involves pictures/music (we don't write mobile crapps).
Your department might not, but the DoD does. They even create computer-games 
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
This isn't about efficiency or idiocy. I have a specific set of environmental conditions (sql server and ssis packages). The question is about the best way to store them. None of the rest of the stuff you're talking about matters. At all.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
John Simmons / outlaw programmer wrote: This isn't about efficiency or idiocy. I have a specific set of environmental conditions (sql server and ssis packages).
It's not an environmental condition, it is policy.
John Simmons / outlaw programmer wrote: None of the rest of the stuff you're talking about matters. At all.
It does to me; you are not the only reader. And in the grand scheme of things, none of it matters
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
We're about to update from SQL 2008R2 to 2016. Of course, this means that SSMS will get updated. Will the new SSMS work with the SQL 2008 database instances while we transition?
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
Yes.
This version of SSMS works with all supported versions of SQL Server 2008 - SQL Server 2017 and provides the greatest level of support for working with the latest cloud features in Azure SQL Database and Azure SQL Data Warehouse.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
Yes, it will. As well as with SQL Server 2012...
|
|
|
|
|
NB: SSMS doesn't ship with SQL any more; it's a separate download:
Download SQL Server Management Studio (SSMS) | Microsoft Docs[^]
The good news is, it's free, so you don't need a SQL license to deploy it.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
That doesn't answer my question. I just wanted to know if the ssms for 2016 (or most recent version) would let me work with the 2008 instances.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
Is there a way to watch sql server agents execute on a step-by step basis?
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
Hi,
I wanted to create script with Check for Objects existence in SQL Server, when I have enabled that option from Options -> SQL Server Object Explorer->Check for object existence, its creating the script for the Check but Stored Procedures are created using dynamic sql as below, any help would be very very helpful thanks in advance friends.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Usp_Add_AddressCommunication]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
/****************************************************************************************************/
/* NAME : [Usp_Add_AddressCommunication] /
/ PURPOSE : THIS STORED PROCEDURE Addes address communication /
/ TABLES USED: /
/ [dbo].[CommunicationTypeLKP] , AddressCommunication , Address /
<h2>/ VERSION HISTORY:- */</h2>
<h2>/* VERSION NUMBER| DATE | AUTHOR | CHANGES */</h2>
/* 1.0 | 09/19/2017 | aaleti | INITIAL VERSION */
<hr />
ALTER PROCEDURE [dbo].[Usp_Add_AddressCommunication]
(
@AddressId int,
@CommunicationType varchar(60),
@CommunicationValue varchar(60),
@CreatedBy varchar(30)
)
AS
BEGIN
DECLARE @CommTypeId int
SET @CommTypeId = (Select top 1 CT.PKCommunicationTypeLKPId from CommunicationTypeLKP CT where CT.CommunicationTypeDesc = @CommunicationType)
INSERT INTO [dbo].[AddressCommunication]
([FKAddressId]
,[FKCommunicationTypeLKPId]
,[CommunicationValue]
,[ValidFlag]
,[CreatedDate]
,[CreatedBy]
,[ModifiedDate]
,[ModifiedBy]
)
VALUES
(@AddressId
,@CommTypeId
,@CommunicationValue
,1
,getdate()
,@CreatedBy
,getdate()
,@CreatedBy)
select SCOPE_IDENTITY() as PkAddrCommId
END
'
END
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
