|
Hi all,
I am passing column names in dynamic sql like 'col1, col2, col3' + ', ''' + @reportId + '''', up to col3 all the columns are giving correct values, but the @reportId is a string which has value as '1-085' that's being converted into integers and giving me the value '-084'. I mean may its calculating the expression and returning the resultant value.
But I want that string to be as it is, means I want that value as '1-085' instead of a calculated value ie -84, is there anyway to do it. Any help would be greatly helpful, thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
If it is a string then it will be saved as a string. Your code must be doing something strange.
|
|
|
|
|
No I am passing it as a string only from an ssis package variable which is of type string, I am appending both these strings as 'col1, col2, col3' + ', ''' + @reportId + '''' and passing it as nvarchar parameter into a stored procedure
Exec [ETL].[PopulateStageTable] ?, ?, ?, ?, ?, ?, ?
There are other Parameters but they are not giving any problem excel this, I checked even the execute sql too has this parameter defined as nvarchar and same thing in the Stored procedure too, nothing is converted to number or anything like that
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
Rephrasing what the previous response was suggesting (I believe.)
You posted a line where @reportId is used. Nothing in that line would change the value of that.
That line has nothing to do with the value in @reportId. At that point it already has the value you are seeing.
Consequently you are doing something before that which causes it to have that value. You need to find that and correct it.
|
|
|
|
|
To rectify you, this is not something you believe, but deduced. It sounds correct, hence the upvote.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
I didn't quite get your answer, but some how its deducting the value and putting it there. If you can answer that's fine, but you are not able that's fine I just added one more step in Control flow which updates that Column with the correct value. But if you know it would be helpful for me.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
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.
|
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
