Database

Re: A question about the behavior of Jet and Ace drivers

24-Oct-17 7:58

Hi,

It shows both the Jet and Ace drivers on a Server machine, and user the Sql Agent Job is running with, doesn't have admin privileges. The Packages that are running with Jet drivers are working fine on that Server where as the Packages that are using Ace drivers are not running Properly. There are various questions and eye brows that are raising here

There are Ace 14.0 drivers there on that machine, were the Ace 12.0 drivers overwritten by Ace 14.0 when we installed newer version of Ace or office pack
Don't the Ace 14.0 drivers which are advanced than the Ace 12.0 have backward compatibility, why is it? When we try Ace 12.0 drivers with the Packages that are using csv or flat files are working fine where as only the Excel files both (xls and xlsx) are not working properly why?

a. Are the Ace 14.0 drivers backward compatible with the csv and flat files or do the Ace 12.0 drivers can import from the csv and flat files without Admin
privileges Many questions are raising with this odd behavior. Because the csv and flat files are getting imported without admin privileges too

b. Are the Ace 12.0 drivers not available only 14.0 are running all this but because of the registry entry or some uncleaned stuff from the installation is
showing the Ace 12.0 drivers in the drivers list? Many many questions are raising.
As the Packages with the Jet drivers are working fine I am not understanding what is missing or messing up here, is it the unavailability of Ace drivers or do the Ace drivers need Admin privileges and Jet drivers don't? If unavailability is the case then why Ace 12.0 drivers are fine with csv and flat files?

The problem for us in continuing with the Jet drivers is, we have some packages with xls which is fine with Jet drivers but we have some Packages with the xlsx files which are not going to work with the Jet drivers.

Is there any buddy who can answer me these questions? Any help would be greatly appreciated my friends. I am also researching doing lot of studies about behavior of these drivers.

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."

PIEBALDconsult24-Oct-17 8:32

PIEBALDconsult

24-Oct-17 8:32

Are you talking about SSIS packages?
I recommend using only ACE, but you may need to run your packages as 32-bit.

Re: A question about the behavior of Jet and Ace drivers

indian14325-Oct-17 7:15

Re: A question about the behavior of Jet and Ace drivers

25-Oct-17 7:15

I did, but still Ace drivers are not working but Jet drivers are working on my machine, but Ace drivers are showing in the drivers list, with Admin rights Ace drivers are also Working, when user is not Admin Jet drivers are working fine, how is it different for different drivers when the user is same?

I have Ace 14.0 drivers but Packages by default taking only Ace 12.0 drivers, when I check in the drivers list Ace 12.0 drivers are showing in the list are the Ace 12.0 drivers cheating that they show-up from registry entries but don't exist?

Why SSIS Packages only take Ace 12.0 drivers, why isn't there backward compatibility?
why don't they take Ace 14.0 drivers?
Why Jet drivers can read the Excel files without the user that doesn't have Admin privileges but Ace 12.0 drivers can't?

Any help would be greatly helpful friends - thanks in advance.

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."

jschell27-Oct-17 7:43

Math expression as Column value as string in the Dynamic Sql

27-Oct-17 7:43

indian143 wrote:
with Admin rights Ace drivers are also Working,

So rephrasing to make it very clear what the problem is.

1. When you run the application ON the target machine with admin privileges it both drivers work.

2. When you run the application ON the target machine with non-admin privileges, doing exactly the same as above for 1, then only one driver works. The other fails.

If so your problem is with the non-admin user and/or the access rights for the driver that does not work.

indian14319-Oct-17 7:19

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

19-Oct-17 7:19

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

Richard MacCutchan19-Oct-17 7:33

Richard MacCutchan

19-Oct-17 7:33

If it is a string then it will be saved as a string. Your code must be doing something strange.

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

indian14319-Oct-17 8:10

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

19-Oct-17 8:10

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

jschell20-Oct-17 9:27

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

20-Oct-17 9:27

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.

Eddy Vluggen20-Oct-17 10:47

Eddy Vluggen

20-Oct-17 10:47

To rectify you, this is not something you believe, but deduced. It sounds correct, hence the upvote.

Bastard Programmer from Hell Suspicious | :suss:

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

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

indian14324-Oct-17 6:41

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

24-Oct-17 6:41

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

jschell25-Oct-17 9:19

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