|
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."
|
|
|
|
|
Help with what? That's just the way that feature works.
Prior to SQL Server 2016 SP1[^], there's no CREATE OR ALTER PROCEDURE statement. And as the documentation[^] says: "The CREATE PROCEDURE statement cannot be combined with other Transact-SQL statements in a single batch." Which means there's no way to do a CREATE / ALTER within an If block. So the only option is to execute a string.
If you're willing to manually change the output, you can reduce the duplication slightly:
DECLARE @statement nvarchar(MAX) = N'/****************************************************************************************************/
...
';
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[Usp_Add_AddressCommunication]'))
BEGIN
SET @statement = @statement + N'CREATE ';
END
Else
BEGIN
SET @statement = @statement + N'ALTER ';
END;
SET @statement = @statement + N'PROC [dbo].[Usp_Add_AddressCommunication]
...';
EXEC dbo.sp_executesql @statement = @statement;
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I am not quite familiar with SQL injection and the different methods one could use to infiltrate one's database code, so I have been using PDO with parameter binding with most of my implementation. I do find it a bit clumsy and long sometimes and am tempted to just skip it.
So my question is - what circumstances can I NOT use parameter binding without worrying about possible hacks - for less critical information?
And the flip side - when should really use it? For more sensitive information only like user emails, id's? or?
Would appreciate some feedback or links for further reading.
modified 17-Jun-18 22:31pm.
|
|
|
|
|
You should always use parameter binding.
One exception only, when there are no parameters.
<edit>After reading Mycrofts answer I realize I should change my second sentence to: One exception only, when the parameter is a constant. </edit>
modified 25-Jun-18 7:23am.
|
|
|
|
|
I'm not as fanatical as Jorgen, there is no circumstance where you should NOT use parameter binding. However there are circumstances (most) where you MUST use parameter binding. If a user or application has anything to do with the values then you MUST use parameters.
The only time I would risk not using parameters is when there is no external input of the values.
An example of where I will risk string queries would be if you have a master table of countries with an Active attribute field. I might use select * from Country where ActiveFlag = 1 from my BL layer.
But seeing as I have a code generator that automatically builds the DAL, model and viewmodel code for me I ALWAYS use parameters and stored procedures
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
To be fair, SQL Injection can only occur where the user has influence over the parameter. If your parameter is a constant, or provided by you, there's little risk since the user cannot change the statement that is going to be executed.
..but, parameterized queries are not done "just" to prevent SQL injection, they provide more benefits. While it may take you a minute longer to write it, it makes the code a lot more readable, improving maintainability. There's also an impact on performance[^] if you re-execute the query.
That's not something that every hobbyist wants to learn, so the advice has become that it is NEVER OK to NOT use them. I agree with that advice, since adding the code will not have a downside.
needAbreakNow wrote: I do find it a bit clumsy and long sometimes and am tempted to just skip it.
This will sound rude but that's just being lazy. If it is worth doing at all, it is worth doing it correctly.
Upvoted for asking "why am I doing this"; it proves you are actually thinking about what you are doing, and trying to find alternatives.
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.
|
|
|
|
|
needAbreakNow wrote: and am tempted to just skip it
Versus what? If you create a composition then that can be rather complicated as well.
Perhaps you are comparing it to simple cases like when you want to add just one parameter?
If so I would say that consistency of usage overrides the ease of one-off cases.
|
|
|
|
|
I'm trying to fetch the previous and next record using PDO/MYSQL. The SQL code for Next fetches the next record, but for Previous always returns the first record. Don't understand why Previous does not work. There are 7 records in this test set and the 'article_id' field is not contiguous (due to deletions) but is broken up as such: 24, 45,46,47, 48, 50, 51. Code below:
$id = 47;
echo 'current id is:'.$id;
$stmt= $db->prepare("SELECT * FROM blog WHERE article_id<$id LIMIT 0,1");
$stmt->execute();
$row=$stmt->fetch(PDO::FETCH_ASSOC);
echo '<br>';
echo 'Previous ID is:'.$row['article_id'];
$stmt= $db->prepare("SELECT * FROM blog WHERE article_id>$id LIMIT 0,1");
$stmt->execute();
$row=$stmt->fetch(PDO::FETCH_ASSOC);
echo '<br>';
echo 'Next ID is:'.$row['article_id'];
The resultant output is:
current id is:47
Previous ID is:24
Next ID is:48
The previous ID should be 46, not 24!
I've tried the same SQL in phpMyAdmin directly and it still returns 24 for the previous, but works fine for the next. I've seen many other examples on the web showing the similar code as I have, but theirs seems to work (via youtube), mine doesn't. Any help would be appreciated!
Thanks in advance.
|
|
|
|
|
I am not a SQL expert, but the following occurs to me. Your SELECT statement says: find all records whose article_id is less than the value given (i.e. 47), and return the first record found. So SQL starts searching the table at the beginning, checks the first record and its id is 24, and that matches your search criteria. In order to do what you want you need somehow to tell it to find the record with the highest id that is less than 47. But I am not sure how you would specify that.
|
|
|
|
|
To extend Richard's reply:
you may want to refine your SQL query using ORDER BY clause or/and MAX / MIN functions.
|
|
|
|
|
Solved the issue, and thanks for the hints!
$stmt= $db->prepare("SELECT * FROM blog WHERE article_id<$id ORDER by article_id DESC LIMIT 0,1");
It seems like the for the next record, the system naturally assumes ascending, but for the previous, I had to put in the DESC order in!
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
