|
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[^]
|
|
|
|
|
|
The primary use of sp_ExecuteSQL is when you the Database Developer absolutely needs to create Dynamic SQL, and not for protection.
While what you have written appears safe, there is no need for Dynamic SQL and you are only adding a layer of service and reducing performance.
Director of Transmogrification Services
Shinobi of Query Language
Master of Yoda Conditional
|
|
|
|
|
Hi,
I am getting a flat file that's getting number values like integer and decimal values also withing "" like id is also coming as "1" or "100" etc, so in ssis package in Data Conversion its failing, can I get any expression or something to eliminate those double quotes and import the value into the Database with proper conversion everything.
I am getting the error message as below:
FileFullName: \\xxxxx\\DataExchange\ShieldLink\Test\32 Bit and 64 Bit Testing\Thirty Two Bit.csv, SSISPackageName: ImportPipeDelimitedCSV.dtsx, Source : Microsoft.SqlServer.Dts.Runtime.TaskHost, ErrorCode : -1071607767, Description : SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Data Conversion.Outputs[Data Conversion Output].Columns[Copy of Column1]" failed because error code 0xC020907F occurred, and the error row disposition on "Data Conversion.Outputs[Data Conversion Output].Columns[Copy of Column1]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
, SubComponent: Data Conversion [2], HelpFiledtsmsg110.rll, HelpContext0, IdofInterfaceWithError{B6F6D221-FC27-4F71-B5A0-597583986C28}
This is failing because the values are coming as below:
Column1|Column2|Column3|
"1"|"Column 2 Value 1"|"Column 3 Value 1"
For texts its able to convert properly but for the numerical values its failing is there anything that I can do to eliminate those " and implement Data conversion without any problems thanks in advance.
But in some situations I get this error, I am not sure why is this error coming, could it lead to not load data, sometimes even with this warning also we are able to load the Data I think so, but I am not sure, the Warning is as below:
FileFullName: \\xxxxx\ShieldLink\Test\32 Bit and 64 Bit Testing\Thirty Two Bit.csv, SSISPackageName: ImportPipeDelimitedCSV.dtsx, Source : Microsoft.SqlServer.Dts.Runtime.TaskHost, WarningCode : -2147183868, Description : Warning: Could not open global shared memory to communicate with performance DLL; data flow performance counters are not available. To resolve, run this package as an administrator, or on the system's console.
, SubComponent: SSIS.Pipeline, HelpFiledtsmsg110.rll, HelpContext0, IdofInterfaceWithError{B6F6D221-FC27-4F71-B5A0-597583986C28}
I am not understanding is it a Data conversion issue or some other privileges issue, any help would be greatly helpful, thanks in advance friends.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
-- modified 5-Oct-17 20:31pm.
|
|
|
|
|
indian143 wrote: is there anything that I can do to eliminate those " and implement Data conversion without any problems
Based on the following - no.
Connect to a Flat File Data Source (SQL Server Import and Export Wizard) | Microsoft Docs[^]
You might want to review the actual input spec yourself in the hope the docs are wrong.
Presuming there is no option, and you cannot get the source adjusted, then you must
1. Take all of the values as strings
2. Add a post process step to convert to correct values (remove quotes and convert to numeric as needed.)
|
|
|
|
|
Hi friends,
I am getting the following message when I am trying to install the Ace 16.0 64 bit drivers
You cannot install the 64-bit version of Microsoft Access Database Engine 2016 because you currently have 32-bit Office Products installed. If you want to install 64-bit MS access Engine 2016, you need remove the 32-bit installation of Office Products.
But here is the thing I am trying to install these drivers with Passive switch as below, still I am getting this error
C:\Personal Docs\DownLoads\Ace Drivers>AccessDatabaseEngine_X64.exe /passive
Just as it is mentioned here: https://knowledge.autodesk.com/support/autocad-civil-3d/learn-explore/caas/sfdcarticles/sfdcarticles/How-to-install-64-bit-Microsoft-Database-Drivers-alongside-32-bit-Microsoft-Office.html
Earlier I did it with Ace 12.0 64 bit drivers, I could able to install both the 32 and 64 bit Ace 12.0 drivers side by side with Passive switch, to do the same with Ace 16.0 its not working is there any Work around to install these 64 and 32 bit drivers side by side, any link, a suggestion anything helps my friends its little urgent - thanks in advance my friends.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
Did you fudge the registry as suggested in the article? Either way, it is unlikely that Microsoft would support this, so if it does not work there is probably nothing you can do.
|
|
|
|
|
--- Solution 1
Use a Virtual Machine (VM). One OS in VM 'A' would have one version and the other VM would have the other.
If you actually need both on a single machine in the delivery (production) then you could.
1. Create a service that encapsulates one service and exposes one, not both of the drivers. Call this driver 'A'
2. It doesn't do anything but allow you to run the driver.
3. Run that in a VM
4. On the regular machine (not VM) install the other driver 'B', then you app uses 'B' directly and makes rest calls to the service, via the VM, to run 'A'.
--- Solution 1
This might or might not work. And it would take a lot of experimentation. And you MUST check the license agreement to validate this usage.
1. Neither driver should be installed.
2. Install one driver, probably 32 bit one
3. Determine exactly what files were installed.
4. Copy those files somewhere.
5. Uninstall the driver.
6. Install the other driver.
7. Create a library that encapsulates the first driver in the executable itself. So not as an installed driver but rather as a library. Your library exposes the functionality you need.
8. Use your library to get to the first driver and the regular idiom to get to the second.
Note that if you can get this to work then it might best to do it with both drivers rather than relying on different access methodologies.
|
|
|
|
|
Wondering on how much these dot net data structures are used by everyone else ?
|
|
|
|
|
Quite often, even though I personally rarely use them; you'll find them in a lot of brownfields, as it is quite easy to load a datatable using a reader. Those are also common examples in most tutorials, making it quite accessible
I prefer to fetch the raw values from said reader, as I rarely need the overhead a table-class introduces.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Use them most of the time as they are easy and do all the hard work.
|
|
|
|
|
As soon as I get the data from the database I move it into a model/object so I never work with the dataset/table object. In future I will move the datatable to a Json string on the server and let the client move it into a model/object.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I am trying to make a website like SONGS STORE where people can download there favorite song of their artists.So for this I want a songs collection database.Where can i get this kind of database?
|
|
|
|
|
Since most music is under copyright, your "obtaining" a database of music to download would be illegal, and we do not get involved in that at all.
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
thank you .for your reply mate.
|
|
|
|
|
I would suppose any of the existing sites would allow you to relabel to your brand for enough money.
Otherwise you would need to do it like they did, go to the artists/companies and get their permission (contract agreement) to allow you to host their music.
|
|
|
|
|