|
indian143 wrote: but some how its deducting the value
Computers are deterministic. They do what they do because some code explicitly to do it that way.
Not sure how I can make it clearer. If you go to the store and open your wallet and expect it to have $20 and it doesn't do you continue to look at the wallet expecting the $20 to show up. Or do you think back where you were before the store to figure out where that money went?
Same thing it true for this code.
You posted a line where you use the reportId variable. At that point it already had a value. The computer doesn't give it a value at that point, because it already had one.
There is code before that line that gives it a value. You must find that code, not the code that you are looking at (again the code you posted) and then figure out from that why it has the value that it does.
|
|
|
|
|
Ok Got it my friend thank you, I put an another sql task that just updates that Column value, so I think we are good now my friend.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
The AspNetUsers table uses a GUID for the Id. I'm adding a dozen tables or so, and was interested in what is considered "best practice" where Id columns in my own tables are concerned. I understand it's almost impossible to infer other IDs if they're GUIDs, and there's a possibility that even ids from my tables could show up in cookies or query strings, so it sounds to me like the best way forward is to also use GUIDs for my IDs as well.
Are there any hidden/subtle down-sides to using GUIDs for identity columns, or overriding benefits for using longs instead of GUIDs?
".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
|
|
|
|
|
Despite of the small amount of extra space needed for GUID it has multiple benefits. For example:
- It's unique even if you run multiple SQL Server instances
- It can be used as a key in replicated environment
- Reseeding a table does not produce duplicates
- If you use newsequentialid the values are 'close to each other' thus behaving more nicely with clustered keys, just like identity values
|
|
|
|
|
John Simmons / outlaw programmer wrote: Are there any hidden/subtle down-sides to using GUIDs for identity columns, or overriding benefits for using longs instead of GUIDs?
GUIDs are 'probably' unique. So very, very likely unique.
Longs are sequential. Always (unless you do something weird.) So they implicitly define insertion order even in cases when a database reuses slots. Sometimes GUIDs do that but most do not. Normally I insertion order a significant factor when I need to debug subtle production errors. Other than that they are convenient alternative which can only otherwise be solved (sort of) by adding a 'creation time' column to each table.
Small extra space that they use hasn't been a concern of mine for a very long time. But your business model might define something different. If so then you might even want to consider an int rather than long (again business model drives that.)
As you noted GUIDs have an obscurity advantage when exposed. Of course that is easy to do the same thing by creating the client to server interface such that it creates ids specific to that (so map it.)
Syncing two disparate databases (perhaps offline to main) is at least somewhat easier with GUIDs because one can ignore the id overlap that would exist with longs.
I would say that these days although I still like the sequential nature of longs, I would probably start with GUIDs unless I knew of something specific that I needed to support. But I would also add a 'creation time' regardless.
|
|
|
|
|
Quote: Other than that they are convenient alternative which can only otherwise be solved (sort of) by adding a 'creation time' column to each table.
GUID Version 1 includes a timestamp to within 100 nano-seconds, and a "globally unique node identifier", which would normally be your MAC address. If you need insertion order, then a V1 GUID might meet your needs. Since you also get the node identifier, then you not only know when, but where the data comes from. Whether the risks with that are acceptable, is up to you, of course.
See How to make a GUID
|
|
|
|
|
Databases, these days, have their own GUID process. I figure there is very likely optimizations in the database based on that specific data type. Something an external value as text isn't going to have. So it means the attributes of the native type are what I end up with.
|
|
|
|
|
As already noted, a GUID is a little bit longer than a simple number. That also means that your indexes will be a bit longer.
For all the pros given one would still prefer the GUID over a long, especially if your keys are visible (which they shouldn't, imo).
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Some observations:
Upper case Guids "read / display better" (IMO) than the default lower case.
Guids are suprisingly easier to differentiate on a screen than "smaller" keys.
Reserve Guid.Empty for "not assigned".
They look "official" (e.g. generated certificates).
Goes with the notion that primary keys should be "nonsense".
Never wonder why there's a "gap" in your sequence...
"(I) am amazed to see myself here rather than there ... now rather than then".
― Blaise Pascal
|
|
|
|
|
Hi,
I am trying to insert values in a table directly from executing a Store Proc, but I want to use the Column names of the resultset of the Stored Procedure and of the table in which the column values are being inserted.
My stored Procedure is as below:
alter PROCEDURE [GL].[PopulateStageRpt129_New] (
@ReportId Varchar(5) = '1-085'
, @filepath nvarchar(max)=N'Database=\bsc\cscm\IM_PHI\Facets_Team\User.Developed.Reports\HPXR\1-085 Cancelled with Cash-Report 129_2017-10-09.xls'
, @sheetname nvarchar(max)=N'[Sheet1$]'
, @providername nvarchar(max)=N'Microsoft.ACE.OLEDB.12.0'
, @providerversion nvarchar(max)=N'Excel 12.0'
)
AS
BEGIN
--declare @filepath nvarchar(max)='', @sheetname nvarchar(max)='', @providername nvarchar(max)='', @providerversion nvarchar(max)=''
--, @tempString nvarchar(max)='', @selectquery nvarchar(max)='';
--set @filepath=N'Database=\bsc\cscm\IM_PHI\Facets_Team\User.Developed.Reports\HPXR\1-085 Cancelled with Cash-Report 129_2017-10-09.xls;'
<pre>
select @ReportId= REPLACE(@ReportId,';','')
select @filepath= REPLACE(@filepath,';','')
select @sheetname= REPLACE(@sheetname,';','')
select @providername= REPLACE(@providername,';','')
select @providerversion= REPLACE(@providerversion,';','')
declare @tempString nvarchar(max)='', @selectquery nvarchar(max)=''
if (@sheetname is null) or (@sheetname='')
set @sheetname=N'[Sheet1$]'
if (@providername is null) or (@providername='')
set @providername=N'Microsoft.ACE.OLEDB.12.0'
if (@providerversion is null) or (@providerversion='')
set @providerversion=N'Excel 12.0' + N';'
else
set @providerversion=@providerversion + N';'
if (@filepath is not null) or (@filepath <> '')
set @filepath=@filepath + N';'
set @tempString=@providerversion+@filepath
--truncate table saw_raw.[GL].[HpxrStage]
set
@selectquery=
'select *
,''' + @reportid + '''
from OPENROWSET(''' + @providername + ''',''' +
@tempString+ ''',''' +
N'SELECT * FROM ' + @sheetname + ''')'
--print @selectquery
exec (@selectquery)
END
--truncate table saw_raw.[GL].[HpxrStage]
--exec SAW_raw.[GL].[PopulateStageRpt129_New]
And I calling it as below
Insert into saw_raw.[GL].[HpxrStage]
exec
Saw_Raw.[GL].[PopulateStageRpt129_New]
But I want to use as below:
Insert into saw_raw.[GL].[HpxrStage]
(([Category]
,[Line of Business ID]
,[Line Of Business]
,[Delinquent Definition]
,[Subscriber ID]
,[Last Name]
,[First Name]
,[Group ID]
,[Subgroup ID]
,[Class ID]
,[Paid within Tolerance]
,[Elig Thru Dt]
,[Created End Date]
,[Outstanding Balance]
,[Receipt Payment Type]
,[Date Posted]
,[UNREC Cash]
,[Payment Due Date]
,[PNC Generated]
,[Cancel Reason Code]
)
exec <Related Column names here or some where>
Saw_Raw.[GL].[PopulateStageRpt129_New]
When I am trying with the OpenQuery as below its giving me the error:
Select * from OPENQUERY([WSQL123S,50101],
'EXEC xxxx.[GL].[PopulateStageRpt129_New] @ReportId=''1-085''
, @filepath=''Database=\xxxxx\1-085 Cancelled with Cash-Report 129_2017-10-09.xls''
, @sheetname=''[Sheet1$]''
, @providername=''Microsoft.ACE.OLEDB.12.0''
, @providerversion=''Excel 12.0''')
I am getting the following error if I am using the OpeQuery
Could not find server 'WSQL123S,50101' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.
Anybody can help me please, any help would be very very helpful - thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
Error text seems rather obvious to me.
Following in google seems to return quite a few suggestions.
TSQL openquery "Could not find server" "sys.servers"
|
|
|
|
|
If that's the case then the below script working fine and giving me the results though.
Insert into xxxxx.[GL].[HpxrStage]
exec
xxxxx.[GL].[PopulateStageRpt129_New]
The only thing is I want to use the Column names in both the Inserting table and for the Stored Procedure, is there anyway to do that? Any help would be greatly helpful. Thanks for the help my friend.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
-- modified 16-Oct-17 13:26pm.
|
|
|
|
|
Now when I try to use OpenQuery as below, it gives me the error message as below:
Select * from OPENQUERY([MySQLServer],
'EXEC xxxx.[GL].[PopulateStageRpt129_New] @ReportId=''1-085''
, @filepath=''Database=\\xxxxxx\User.Developed.Reports\HPXR\1-085 Cancelled with Cash-Report 129_2017-10-09.xls''
, @sheetname=''[Sheet1$]''
, @providername=''Microsoft.ACE.OLEDB.12.0''
, @providerversion=''Excel 12.0''')
Error message
The metadata could not be determined because statement 'exec (@selectquery)' in procedure
'PopulateStageRpt129_New' contains dynamic SQL.
Consider using the WITH RESULT SETS clause to explicitly describe the result set.
And when I use the ColumnNames as below in the query
Select * from OPENQUERY([WSQL569S],
'EXEC Saw_Raw.[GL].[PopulateStageRpt129_New] @ReportId=''1-085''
, @filepath=''Database=\\bsc\cscm\IM_PHI\Facets_Team\User.Developed.Reports\HPXR\1-085 Cancelled with Cash-Report 129_2017-10-16.xls''
, @sheetname=''[Sheet1$]''
, @providername=''Microsoft.ACE.OLEDB.12.0''
, @providerversion=''Excel 12.0''
WITH RESULT SETS ([Category]
,[Line_of_Business_ID]
,[Line_of_Business]
,[Delinquent_Definition]
,[Subscriber_ID]
,[Last_Name]
,[First_Name]
,[Group_ID]
,[Subgroup_ID]
,[Class_ID]
,[Paid_within_Tolerance]
,[Elig_Thru_Dt]
,[Created_End_Date]
,[Outstanding_Balance]
,[Receipt_Payment_Type]
,[Date_Posted]
,[UNREC_Cash]
,[Payment_Due_Date]
,[PNC_Generated]
,[Cancel_Reason_Code]
,[ReportId])'
)
It is giving me the error as below:
OLE DB provider "SQLNCLI11" for linked server "WSQL569S" returned message "Deferred prepare could not be completed.".
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'Category'.
I want to somehow get the column names of the stored procedures I am trying all the options anything that can help me is going to be very helpful. Thanks in advance my friends.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
-- modified 16-Oct-17 13:54pm.
|
|
|
|
|
|
Hi all,
I am trying to read from an Excel file using OpenRowSet, I am getting the above error, as I found in the google I tried the below script.
USE master
GO
sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OverRide
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE WITH OverRide
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
GO
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
Still no luck it would be very helpful if anybody can help me out, I am also trying different options from online but somehow its not working. Here is the script I am trying.
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=C:\TestFiles\1-085 Cancelled with Cash-Report 129_2017-10-03.xls;',
'SELECT * FROM [Sheet1$]')
And the error message full is:
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
This error is happening even if I open VS on the server itself and run this openrowset script and even if I run from the client machine like my local machine, in both cases the openrowset is failing any help can be greatly appreciated friends.
Thanks in advance friends.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
|
I tried all of them nothing worked for me so far. It is working but when I remote into the Server and run this OpenRowSet statement there but when I run openrowset on my Local Machine its giving me the same error, any help would be greatly helpful. Thanks in advance.
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
-- modified 12-Oct-17 19:54pm.
|
|
|
|
|
indian143 wrote: linked server "(null)".
I think the word null in that message may be a clue.
|
|
|
|
|
|
Hi,
I am trying to run an SSIS Package that's installed in MSDB on my Server manually, the problem is, when I run the Package in 32 bit mode, it gives me the following error:
The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered. If the 32-bit driver is not installed, run the package in 64-bit mode
When I run the Package in 64 bit mode it gives me the following error:
The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode
When I am checking the drivers that are installed I am seeing the Microsoft.ACE.OLEDB.12.0 driver there, when I try to install it, it gives me error message saying its already there I need to uninstall the existing one 32 bit, and I am seeing the Excel 14.0 drivers there too, just paused not understanding what to do, because its happening only on Server and only for Excel files, even though the SSIS uses Ace 12.0 drivers for csv and txt (comma separated, tab delimited or pipe delimited) files, they are not giving any problem (I mean the Packages that are using those files are importing fine only Packages using Excel files are giving problems), and I am importing the files successfully without any issues.
I tried to change the provider at runtime using by Changing the connection string like below: "Provider=Microsoft.ACE.OLEDB.14.0;Data Source=" + @[User::FilePath] + ";Extended Properties=\"EXCEL 14.0 XML;HDR=YES\"", its giving compile error, so just paused not able to find anything I can do. In google its only showing to change the mode of the Package running 32 bit and 63 bit, I did try both modes both are giving me errors, and this Package is running fine in Development environment, I mean if I am running within SSDT, any help can be very helpful.
Can somebody suggest me anything I can do except converting the Excel files into Pipe delimited (I have already implemented that option) but its more work on the SSIS side to change all those Packages to be able to import the files into SQL Server db.
Any help would be greatly helpful, thanks in advance my friends.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
-- modified 11-Oct-17 15:29pm.
|
|
|
|
|
i see this article How to prevent SQL Injection in Stored Procedures[^]
i compose my code like that way but not save from sql injection in my code. what is wrong there in my code ?
DECLARE @Name NVARCHAR(50)
DECLARE @sqlcmd NVARCHAR(MAX);
DECLARE @params NVARCHAR(MAX);
SET @Name ='like ''%ra%''';
SET @sqlcmd = N'SELECT * FROM [TestDB].[dbo].emp1 WHERE Name '+@Name;
SET @params = N'@Name NVARCHAR(50)';
EXECUTE sp_executesql @sqlcmd, @params, @Name;
suppose i have store proc where i will send name when i will call store proc. so we can send parameter value like 'like ''%ra%'''
sp_executesql not saving me from injection problem. guide me how to prevent injection in procedure. thanks
|
|
|
|
|
Simple - don't use string concatenation to build your query.
DECLARE @Name NVARCHAR(50);
DECLARE @sqlcmd NVARCHAR(MAX);
DECLARE @params NVARCHAR(MAX);
SET @Name = '%ra%';
SET @sqlcmd = N'SELECT * FROM [TestDB].[dbo].emp1 WHERE Name Like @Name';
SET @params = N'@Name NVARCHAR(50)';
EXECUTE sp_executesql @sqlcmd, @params, @Name;
Now the question is, why are you using dynamic SQL for such a simple query?
DECLARE @Name NVARCHAR(50);
SET @Name = '%ra%';
SELECT * FROM [TestDB].[dbo].emp1 WHERE Name Like @Name;
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
i just try to simulate how to prevent sql injection in sql server store proc but i saw it did not work. %ra% works. i thought sp_executesql will prevent that but did not.
so tell me what is way out
see my fresh code where %ra% is working instead of protection.
DECLARE @Name NVARCHAR(50)
DECLARE @sqlcmd NVARCHAR(MAX);
DECLARE @params NVARCHAR(MAX);
SET @Name ='%ra%';
SET @sqlcmd = N'SELECT * FROM [TestDB].[dbo].emp1 WHERE Name like @Name';
SET @params = N'@Name NVARCHAR(50)';
EXECUTE sp_executesql @sqlcmd, @params, @Name;
|
|
|
|
|
Mou_kol wrote: i thought sp_executesql will prevent that but did not.
"sp_executesql" executes sql. The SQL-command has no need to check for injection by the user, as most users will not directly access the database.
There is no way you can add strings and magically secure them.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
