Database

Re: Math expression as Column value as string in the Dynamic Sql

25-Oct-17 9:19

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.

indian14330-Oct-17 7:37

GUID or long for MVC site

30-Oct-17 7:37

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."

#realJSOP17-Oct-17 6:37

#realJSOP

17-Oct-17 6:37

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

Wendelius17-Oct-17 7:01

Wendelius

17-Oct-17 7:01

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

jschell17-Oct-17 7:43

17-Oct-17 7:43

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.

k505418-Oct-17 5:30

k5054

18-Oct-17 5:30

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

jschell20-Oct-17 8:34

20-Oct-17 8:34

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.

Eddy Vluggen17-Oct-17 23:38

Eddy Vluggen

17-Oct-17 23:38

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 Suspicious | :suss:

If you can't read my code, try converting it here[^]

Inserting the values of a Stored Procedure directly into a Table by using the Column names

Gerry Schmitz18-Oct-17 18:32

Gerry Schmitz

18-Oct-17 18:32

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

indian14313-Oct-17 12:59

Re: Inserting the values of a Stored Procedure directly into a Table by using the Column names

13-Oct-17 12:59

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."

jschell15-Oct-17 8:15

Re: Inserting the values of a Stored Procedure directly into a Table by using the Column names

15-Oct-17 8:15

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"

indian14315-Oct-17 15:03

Re: Inserting the values of a Stored Procedure directly into a Table by using the Column names

15-Oct-17 15:03

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.

indian14316-Oct-17 7:25

Re: Inserting the values of a Stored Procedure directly into a Table by using the Column names

16-Oct-17 7:25

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.

Eddy Vluggen17-Oct-17 2:14

Eddy Vluggen

17-Oct-17 2:14

See sql - Retrieve column names and types of a stored procedure? - Stack Overflow[^], first answer.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

OpenRowSet is giving error: Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

indian14312-Oct-17 7:38

Re: OpenRowSet is giving error: Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

12-Oct-17 7:38

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."

Victor Nijegorodov12-Oct-17 9:00

Victor Nijegorodov

12-Oct-17 9:00

Did you try:
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".
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) - Google-Suche

Re: OpenRowSet is giving error: Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

indian14312-Oct-17 11:30

Re: OpenRowSet is giving error: Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

12-Oct-17 11:30

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.

Richard MacCutchan12-Oct-17 22:11

Richard MacCutchan

12-Oct-17 22:11

indian143 wrote:
linked server "(null)".

I think the word null in that message may be a clue.

Re: OpenRowSet is giving error: Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Richard Deeming13-Oct-17 2:10

Richard Deeming

13-Oct-17 2:10

Here's another post on the same issue:
OLE DB provider "MICROSOFT.JET.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error". – Tips and tricks from a Developer Support perspective.[^]

It definitely sounds like a permissions issue on the "temp" directory used by the SQL service account.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0209302

indian14311-Oct-17 7:36