SQL 2012 :: Allow Authenticated User To Query Excel File Via Linked Server?

May 25, 2014

I have a 3rd party dashboard application that I can only use SQL authenticated logins to connect to the database.

I'm trying to create a query within the application that will directly access an excel file through a linked server.

As a test, I login to SSMS as the sql auth user to run the linked server query below but the following error is returned:

select *
from Corporate...[Sheet1$]OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Corporate" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Corporate" reported an error. Authentication failed.
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 "Corporate".

When I login as a Windows auth user, I can successfully run the above query.

I noticed within the linked server's security definition that I cannot specify a windows auth user as the mapped Remote User or as the Remote login

I've tried creating a Credential object with the identity of the windows user and assign that object to the sql auth user but to no avail. I still get the same error

I am using SQL Server express so the option of an automated server agent job to import the excel file is not available.

Details:
SQL Server Express 2012
Office version: Excel 10
Provider: Microsoft.ACE.OLEDB.12.0

View 9 Replies


ADVERTISEMENT

SQL Server 2012 :: Query From A List In Excel File

Feb 11, 2014

I have database tables for

Stores
StoreId, Name

Products
ProductId, Name

Transactions
TransactionId, StoreId, ProductId

I was just given an excel file with a list of 300 Stores.

I need to find out if these stores are selling our products and if they are , how many products they are selling.

One way of doing this , that I can think of right now is individually querying the Transactions table for each of the store in the excel sheet and then copy the results output back to the excel sheet.

Is there a way I can write a query against all the Store names from the excel file ? I need to get this done in the next few hours.

View 9 Replies View Related

Query To Add Windows Authenticated User Via Remote

Jun 17, 2007

I was able to do this on a Windows XP machine but I am not able to get this to work on a Windows 2003 Server machine.
I am connecting to the Windows 2003 Server and MS SQL Express via remote desktop on the lan.

This is what worked for me before:
EXEC sp_grantlogin '4800X2ASPNET'
USE MyDB
EXEC sp_grantdbaccess '4800X2ASPNET'
EXEC sp_addrolemember 'db_owner', '4800X2ASPNET'

But for some reason now I am getting this error:
Msg 15401, Level 11, State 1, Procedure sp_grantlogin, Line 49
Windows NT user or group '4800X2ASPNET' not found. Check the name again.
Msg 15401, Level 16, State 1, Line 1
Windows NT user or group '4800X2ASPNET' not found. Check the name again.
Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 75
User or role '4800X2ASPNET' does not exist in this database.

I thought what I needed to add was the name of this pc that will connect remotely, but that doesn't seem to be working.
What weird is the old copy of this query I used had the local pc as the computer name. If I exchange the computer name from the remote pc name to the local 2003 Server machine name, the query works fine. Although that still doesn't let me add connections via Web Developer Express without error. It doesn't make sense to me anyhows, how could I have used the local machine's name and not the remote machine's name to get this to work previously. So confused.

Thank you in advance for help.

View 1 Replies View Related

Query To Add Windows Authenticated User Via Remote Desktop [MS SQL Express]

Jun 17, 2007

I was able to do this on a Windows XP machine but I am not able to get this to work on a Windows 2003 Server machine.I am connecting to the Windows 2003 Server and MS SQL Express via remote desktop on the lan.This is what worked for me before:EXEC sp_grantlogin '4800X2ASPNET'USE MyDBEXEC sp_grantdbaccess '4800X2ASPNET'EXEC sp_addrolemember 'db_owner', '4800X2ASPNET'But for some reason now I am getting this error:Msg 15401, Level 11, State 1, Procedure sp_grantlogin, Line 49Windows NT user or group '4800X2ASPNET' not found. Check the name again.Msg 15401, Level 16, State 1, Line 1Windows NT user or group '4800X2ASPNET' not found. Check the name again.Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 75User or role '4800X2ASPNET' does not exist in this database.I thought what I needed to add was the name of this pc that will connect remotely, but that doesn't seem to be working.What
weird is the old copy of this query I used had the local pc as the
computer name. If I exchange the computer name from the remote pc name
to the local 2003 Server machine name, the query works fine. Although
that still doesn't let me add connections via Web Developer Express
without error. It doesn't make sense to me anyhows, how could I have
used the local machine's name and not the remote machine's name to get
this to work previously. So confused.Thank you in advance for help.

View 1 Replies View Related

Query To Add Windows Authenticated User Via Remote Desktop [MS SQL Express]

Jun 17, 2007

I was able to do this on a Windows XP machine but I am not able to get this to work on a Windows 2003 Server machine.
I am connecting to the Windows 2003 Server and MS SQL Express via remote desktop on the lan.

This is what worked for me before:
EXEC sp_grantlogin '4800X2ASPNET'
USE MyDB
EXEC sp_grantdbaccess '4800X2ASPNET'
EXEC sp_addrolemember 'db_owner', '4800X2ASPNET'

But for some reason now I am getting this error:
Msg 15401, Level 11, State 1, Procedure sp_grantlogin, Line 49
Windows NT user or group '4800X2ASPNET' not found. Check the name again.
Msg 15401, Level 16, State 1, Line 1
Windows NT user or group '4800X2ASPNET' not found. Check the name again.
Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 75
User or role '4800X2ASPNET' does not exist in this database.

I thought what I needed to add was the name of this pc that will connect remotely, but that doesn't seem to be working.
What weird is the old copy of this query I used had the local pc as the computer name. If I exchange the computer name from the remote pc name to the local 2003 Server machine name, the query works fine. Although that still doesn't let me add connections via Web Developer Express without error. It doesn't make sense to me anyhows, how could I have used the local machine's name and not the remote machine's name to get this to work previously. So confused.

Thank you in advance for help.

View 1 Replies View Related

How To Monitor Linked Server Authenticated Users

Oct 24, 2007

Hello experts. I inherited two SQL2000 SP4 servers. ServerA is configured with a Linked Server (ServerB). The problem is that liked_user which is the login configured "For a login not identified in the list above, connections will be made using this security context" is a sysadmin on ServerB. This means that any login from ServerA can do havoc on all databases in ServerB! There are many DBs and logins on both servers and no one can tell us which login is supposed to connect to the other server on which DB with what permission. Now, we want to trace which logins in ServerA use this Link so we can reconfigure this nasty setting.


I started the following SQL Profilers: On ServerA --> Filter TextData Like %ServerB%. On ServerB --> Filter LoginName Like liked_user AND HostName Like ServerA


Interesting enough, several entries were logged on the profiler i ran in ServerB which means that liked_user logged into ServerB from ServerA. Since liked_user is only used by the Link, i can safely conclude that the link was used by some login in ServerA. BUT, nothing was logged in the profiler i ran on ServerA!

Can anyone help me sort this one out? Thanks in advance,

View 1 Replies View Related

SQL 2012 :: Output A Query As Excel File

Nov 16, 2013

I'm a newbie to SQL. I'm using SQL Server 2012 on my local machine and I need to find a way to output my queries as Excel files. I came across these codes for Interactive SQL (what is intercative SQL by the way?) but they don't work in the SQL query window:

SELECT * FROM SalesOrders;
OUTPUT USING 'Driver=Microsoft Excel Driver (*.xls);
DBQ=c: estsales.xls;
READONLY=0' INTO "newSalesData";

It seems the "OUTPUT" command is not a valid command. I really liked that piece of code (may be because it is so simple and carries over very few lines)! Do we have something similar for SQL Server 2012 that can do the job?

View 9 Replies View Related

Excel File As A Linked Server

Apr 25, 2000

I am trying to link an excel file as a linked server. I tried two methods unsuccessfully.

first: using

EXEC sp_addlinkedserver 'Ex',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'c:list5.xls',
NULL,
'Excel 5.0'
GO

When I run
EXEC sp_tables_ex Ex, I get the error below.

Server: Msg 7303, Level 16, State 2, Procedure sp_tables_ex, Line 20
Could not initialize data source object of OLE DB provider 'Microsoft.Jet.OLEDB.4.0'.
[OLE/DB provider returned message: Cannot start your application. The workgroup information file is missing or opened exclusively by another user.]


Second: I link the excel file using ODBC datasource:

EXEC sp_tables_ex mylink, works fine with mylink being the linked server name. I get the table names eg. table1$, table2$.... However when I try to query the tables, I get this error:

Server: Msg 7313, Level 16, State 1, Line 1
Invalid schema or catalog specified for provider 'MSDASQL'.


thanks
FS

View 1 Replies View Related

Cannot Query Excel Linked Server

Dec 8, 2006

Hello,

I have attempted to set up a linked server to an Excel 2003 workbook, and I get an OLEDB error when I attempt to query against it. Some notes about the workbook;

-It has one worksheet in it named 'Add Revenue Accts'.
-The name of the workbook is 'Revenue_to_All_Accounts.xls'
-Its location is \cdnbwfin1dataCDunnComdataReportsReba_HolmesRevenue_All_Accounts

I have the linked server configured as follows;

-Linked Server; REVENUE_TO_ALL_ACCOUNTS
-Provider; Microsoft Jet 4.0 OLE DB Provider
-Data Source; \cdnbwfin1dataCDunnComdataReportsReba_HolmesRevenue_All_AccountsRevenue_to_All_Accounts.xls
-Provider String; Microsoft.Jet.OLEDB.4.0;Data Source=\Cdnbwfin1DataCDunnComdataReportsReba_HolmesRevenue_All_AccountsRevenue_to_All_Accounts.xls;Persist Security Info=False

When I attempt the following query;
SELECT * FROM OPENQUERY(REVENUE_TO_ALL_ACCOUNTS, 'SELECT * FROM [Add Revenue Accts$]')

The following message appears, and no results are returned;

[OLE/DB provider returned message: Unspecified error]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].
Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 20
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.

I have Googled this error, but I have not found anything that really points to what the problem might be. What could be the problem?

Thank you for your help!

cdun2

View 1 Replies View Related

Cannot Logon With Authenticated User When User Database Is Removed

Dec 12, 2007



Hello,

We have a new production server with SQLserver 2005.
Our department responsible ofr the installation made it as secure as possible, however.
I have to put a new backup on to the server from the old production server.
We ran into a problem when we removed the old database.
I could not longer logon to the server with an authenticated user.

"A connection was successfully established with the server, but an error occured during the login process"
"Shared memory provider : error 0 : No process is on the other end of the pipe"

Strange thing is when i create a new database with the same name. The user can logon to the server again.

Any idea's ?


Jurgen

View 1 Replies View Related

SQL 2012 :: Linked Server To CSV File

Aug 5, 2014

I upgraded my 2008 R2 instance to 2012 and the Linked Server I use for aconnect to a csv file stopped working.

Create script:

EXEC sp_addlinkedserver @server ='Stats_CA',
@srvproduct='',
@provider ='Microsoft.ACE.OLEDB.12.0',
@datasrc='D:RLAPPSStatsStats_CA',
@provstr='Text'

Error message:
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Stats_CA" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Stats_CA".

I uninstalled and reinstalled the AccessDatabaseEngine - no change.

The provider is displayed, so it's not that.

I verified temp and csv location folder permissions

I tried the -g512 startup option

The csv has a schema.ini as required

It was an 'in place' upgrade, so nothing else has change.

View 6 Replies View Related

SQL Server 2012 :: Query To Find User Who Last Modified User Roles / Access?

Dec 6, 2013

I would like to know if there is a way to find out who changed a users roles/access WITHOUT using the audit function. For example, if a user account was created and given SA access then changed to read only, how can I find out who made that change? I tried searching for an answer, but kept getting no results. I'm thinking this may tie into the sys.sysusers view?

View 3 Replies View Related

Importing Excel Using Linked Server And Passthrough Query

Jan 24, 2008

What: I am trying to import data from spreadsheets to SQL Server.

Where: Windows Vista, SQL Server 2005 Express, Office 2007.

How: Using linked servers following KB306397.


In SQL Management Studio Express, I created a new Linked Server as follows, with everything else at default:


Linked server: XLSX

Provider: Microsoft Office 12.0 Access Database Engine OLE DB Provider

Product name: XLSX

Data source: D:XLSX.xlsx

Provider string: Excel 12.0
The linked server was created ok.

I then followed KB321686 and ran this:



select * from OPENQUERY(XLSX, 'Select * From [Sheet1$]')

and got this:


Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XLSX" reported an error. Access denied.

Msg 7350, Level 16, State 2, Line 1

Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XLSX".

But if I ran: select * from OPENQUERY(XLSX, 'Select * From [nonexistent$]')

the error is:



Msg 7357, Level 16, State 2, Line 1

Cannot process the object "Select * From [nonexistent$]". The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XLSX" indicates that either the object has no columns or the current user does not have permissions on that object.



It seems that there is an access rights problem if I get the sheet name correct. May I know what I must do to get this to work. I have already given read/write rights to the spreadsheet to NETWORK SERVICE and SQLServer2005MSSQLUser$servername$SQLEXPRESS.

From the same KB article, I also tried:



select * from XLSX...[Sheet1$]

but got this:



Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XLSX" reported an error. The provider did not give any information about the error.

Msg 7330, Level 16, State 2, Line 1

Cannot fetch a row from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XLSX".

Help help.

Thanks.

View 5 Replies View Related

User Authenticated

Sep 15, 2005

Hello,
In SQL, suser_sname() gives me connection user name (I am using a single user to create all connections), I authenticate users through a login form aginst a SQL login table. Is there any way I can get a username authenticated in an SQL query?
 
Thanks,

View 1 Replies View Related

SQL 2012 :: Not Exists Linked Server Object Query

Aug 10, 2015

So, I've got this query running and it works great providing there is a record in both DataBases. Now, I need to get all of those that have a record in DBServer1 but not in TranscendDB. I assume i'd use an If not exists, but can't figure out the syntax when using the Linked Object...

select Portfolio
from [TranscendDB].[dbo].[CMContactEvents] as CM
inner join
(
select N.SSN, A.ACCOUNTNUMBER
from [DBServer1].[DB1].[dbo].[Account] AS A,

[Code] ......

View 4 Replies View Related

Adding An Authenticated User From External App

Apr 23, 2007

I have an ASP.NET 2 external application that is using and mix of the web services and tables/sps available from my 2005 SSRS.

I want to add an authenticated user from the external application. I don't see a web service that will do this. I have been through the SP's and can create roles, authenticated users, policies etc. But I think I need to update the XmlDescription field in the SecData table.



Do I need to build this XML string from scratch or is there a function / sp / web service that will do this. XML is a long ways from my strong suit.



Or am I just crazy and I should redirect my users to the Security tab!



Any help or advise would be appreciated.

View 6 Replies View Related

Connection String With Authenticated User Info

Jul 6, 2006

Hi.  New to ASP.NET and first time posting.
My web app connects to a SQL database - SQL authentication. 
Users login to the web app through the login server control.  Once authenticated, it is my understanding that the user name and password are stored on the client as a cookie. 
How do you programmatically get this user info and use it for the userid and password parameters of the connection string?
Is there a better way to use the authenticated user info to access a SQL database?
Thanks

View 2 Replies View Related

SQL Server 2012 :: Read Dynamic Columns From Excel File Into SSIS

Nov 11, 2014

I have an excel file which has dynamic columns

i.e. Col1, Col2, Col3 this week. next week i will have a new Col4 in the sheet. This will keep on adding every week.

My problem is to Unpivot the data

Date 8/2/2013 8/9/2013 8/16/2013

Stock 1,561 1,661 1,761

i.e. the abobe table should become as

Date Stock

8/2/2013 1561
8/9/2013 1661
8/16/2013 1,761

How can I unpivot the dynamic columns given that the columns will keep on increasing every week.

View 1 Replies View Related

SQL Server 2012 :: Query For User Access To Application Modules

Mar 24, 2015

I have to create a query that will be returning the users and the application modules they have access for. Along with the list of users and modules the query should also return if any module is not accessible for that user.

The 'ApplicationUsers' table is like this:

CREATE TABLE #ApplicationUsers
(
userId INT,
UserName VARCHAR(50)
)
INSERT INTO #ApplicationUsers VALUES
(1, 'Daniel'), (2,'Martin'), (3, 'Brandon'), (4, 'Doug')The 'ApplicationModule' table is like this:

[Code] .....

I used pivot query but there are a couple of limitations in it(may be I am making mistakes in it). First, the pivot columns are static whereas I need dynamic column names(Application Modules). Second, I need to include all the users with all the modules with access or without access that I am unable to achieve.

View 5 Replies View Related

SQL Server 2012 :: Select Query To XLS Output - Export Data In Columns To Separate Tabs In Excel

Apr 21, 2015

Using below script to export the select statement result to .xls

declare @sql varchar(8000)
select @sql = 'bcp "select * from Databases..Table" queryout c:bcpTom.xls -c -t, -T -S' + @@servername
exec master..xp_cmdshell @sql

But result is not exporting in seperate tabs, all 4 column details are exporting in single cell.

how to export the data in columns to separate tabs in excel.

View 2 Replies View Related

SQL Server 2012 :: Creating XML File From Query?

Apr 15, 2015

What I am trying to recreate is:

<value version="5" type="database">
<name>master</name>
<server>servername</server>
<integratedSecurity>True</integratedSecurity>
<connectionTimeout>15</connectionTimeout>

[Code] ....

with this query:

SELECT
'version="5" type="database"' AS 'value',
'master' AS 'name',
LTRIM(RTRIM(([Server Name]))) AS 'server',
'True' AS 'integratedSecurity',

[Code] ....

BUt my output is not correct, it is creating this:

<value>
<value>version="5" type="database"</value>
<name>master</name>
<server>ServerName</server>
<integratedSecurity>True</integratedSecurity>

[Code] .....

So my question is how to I get <value>version="5" type="database"</value> as the first 'value' node?

I've tried multiple ways, but no success.

View 2 Replies View Related

How To Write A Query To View OS And Db Authenticated Users ...

Aug 13, 2003

Hi,

we have a sqlserver 2000 db with a mixture of OS and NT authentication.How to write a query (or get info) on users.


The query should be able to tell me that these users are OS authentiacted and these users are db authenticated.


Thanks,
-copernicus

View 4 Replies View Related

Passing A Variable To A Linked Query (OPENROWSET For Excel Syntax)

May 11, 2007

Hello,

I responded to a very old discussion thread & afraid I buried it too deep.

I have studied the article: How to Pass a Variable to a Linked Query (http://support.microsoft.com/default.aspx?scid=kb;en-us;q314520)



but I have not gotten all the ''''' + @variable syntax right.



Here is my raw openrowset with what I am aiming at.






Code Snippet

-- I want to use some kind of variable, like this to use in the file:

DECLARE @FIL VARCHAR(65)

SET @FIL = 'C:company foldersDocumentationINVENTORY.xls;'

--

SELECT FROM OPENROWSET('MSDASQL', 'Driver=Microsoft Excel Driver (*.xls);DBQ=C:company foldersDocumentationINVENTORY.xls;', 'SELECT * FROM [Inventory$]')

AS DT



Anyone game? Many thank-yous, in advance.



Kind Regards,

Claudia.

View 1 Replies View Related

SQL 2012 :: Reading From Excel File Into SSIS?

Nov 17, 2014

I have an excel file which i am reading through SSIS through MICROSOFT Office 12.0 Access Database Engine OLE DB Provider. My problem is that if the Excel file has data in a column as 123.45 which is displayed as 123. SSIS is able to pick 123 only and not 123.45. however if I change the data type to Numeric in the excel, SSIS is able to read data as 123.45.Any solution other than changing data type in Excel?

View 2 Replies View Related

SQL 2012 :: Is JET Or ACE Is Used By SSIS For Excel File Import

Jun 28, 2015

I want to import excel file in sqlserver , i was informed that we have to pay of drivers like .jet and .ace.

i have sqlserver and OS windows licences and do not want to pay just to import excel in sqlserver.

q1) Is ssis requires .jet or .ace or any other componenet for xls import which is lincenced.

apart from sqlserver and OSwindows licence ,.

Q2)is it possible to convert xls into micorsoft word and then transport it.

Q3) do i have to pay any thing if i use bulk insert to import .txt or .csv file.

what other type of files can be imported by bulk insert.

Q4) how many type of files like .txt or .csv , can be imported using bcp?

View 4 Replies View Related

Importing Excel File Into 2012 That Is On Oracle VM?

Jun 23, 2015

I am currently trying to migrate a excel file into SQL 2012 database on an Oracle Virtual Box VM. I am new to SQL and this type of IT in general.

View 3 Replies View Related

Import Excel Data From User-selected Source File

Dec 4, 2007

Is it possible to import data from an Excel spreadsheet using OPENROWSET or OPENDATASOURCE without having to explicitly define the filepath of the source file? Currently, I have this piece of code within a sproc:


INSERT INTO [dbo].[ProductionRequirementDetail]

([ProductionRequirementHeaderID], [SKU], [Quantity])

SELECT @ProductionRequirementHeaderID,

[SKU],

[LAMPS]

FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0','Excel 8.0; Database=C:WeeklySchedule.xls', 'SELECT * FROM [Master$C5:Q65536]') AS XL

LEFT JOIN [dbo].[PartMaster] ON (RIGHT([XL].[CODE], 7) = [PartMaster].[SKU])

WHERE [SKU] IS NOT NULL

AND [CODE] IS NOT NULL

AND [LAMPS] IS NOT NULL

AND [LAMPS] > 0

AND [LampTypeID] = @LampTypeID




I would like to remove the hardcoded reference 'Database=C:WeeklySchedule.xls' and replace it with a parameter for the filepath. Is this possible? This is in SQL Server 2000. Also, if there is a way to do this with DTS I'd be open to doing it that way too.

View 8 Replies View Related

SQL Server 2012 :: Loading Image From File System Into Query Using Openrowset

May 14, 2014

I have a directory with images and a table in my DB with the path of each file. The main application allow me to create reports where I can display an image, so I was thinking to use a query like:

SELECT [ID]
,[PT_CODE]
,[FILE_PATH],
CASE WHEN [FILE_PATH] IS NOT NULL THEN
(SELECT * FROM OPENROWSET(BULK [FILE_PATH], SINGLE_BLOB) TT)
END
AS IMAGE_LOADED
FROM [DB].[dbo].[TABLE_MR_FILES]But I keep getting the error:

Incorrect syntax near 'FILE_PATH'.I have try multiple combinations without luck to make the OPENROWSET read the path stored in the column [FILE_PATH]. What am I missing?

Note: I am using MSSQL 2012. I don't want to import the images into the DB just load them in the fly as needed by the report runned from the application. I have full access to the DB so if a store procedure is the solution I can go with it.

View 4 Replies View Related

SQL Server 2012 :: Query Servers From A List For File Storage Information

Oct 13, 2014

I have a group of about 5 servers (which will likely grow toabout 25 in the near future) with their names listed in a table in a database on one of the servers. I want to query all servers in that table using the following query to pull the storage drive, database name, created date, age and size of the databases for each server listed in the table:

SELECT left(mf.Physical_Name,2) AS Storage_Drive,
DB_NAME(mf.database_id) AS DatabaseName,
db.create_Date,
DateDiff(day, db.create_date, getDate()) Age,
sum((mf.size*8))/1024 SizeMB

[Code] ...

How would I best accomplish this if I want to implement it using a TSQL procedure?

View 4 Replies View Related

SQL 2012 :: SSIS And Excel - Import Some Data From XLSX File

Mar 7, 2014

I am using VS2012 and creating a package on a 64bit machine to import some data from a .xlsx file. My question is that I am getting an error for the Excel connection manager, do I need to install some kind of excel drive or excel itself on the machine in order to be able to import the data?

View 6 Replies View Related

SQL 2012 :: SSIS - Import Date As Variable From Excel File

Feb 5, 2015

I am using vs 2010 and I have an .xls file that I am trying to import into SQL Server 2012, and I have most of it figured out, but I have a date field that is giving me problems, and what I would like to do is put that date in a variable so I can add it to every record in my SQL Table.

I am using a SQL Task Editor with an excel connection and I have no problem getting other data from the excel document and putting into my variable, its just the date that I have problems.

View 0 Replies View Related

Add Linked Server At Excel VBA

Aug 29, 2006



I had SQLDMO at reference library.

Private Sub test()
Dim s As SQLDMO.SQLServer
Dim ls As SQLDMO.LinkedServer
Set s = New SQLDMO.SQLServer
s.Connect "Server1", "ID", "Password"
Set ls = New SQLDMO.LinkedServer
With ls
.Name = "Server2"
.ProviderName = "SQLOLEDB"
.DataSource = "Server2"
' .ProviderString = ""

End With
s.LinkedServers.Add ls
s.Close

'End Sub

I block ProviderString as i don't know what is it.

I got an error message when it's running the line "s.LinkedServers.Add ls" that

"Run-time error '-2147206257 (80043b8f)':

Automation error"

My question:

1. What is providerstring? what should be put here?

2. How to fix the error

View 1 Replies View Related

SQL 2012 :: OPENROWSET To Read In Data From Excel 2010 File (XLSX)

Jun 12, 2015

I have a test server (TEST1) running SQL 2012 and Windows 2012R2. One of the developers wants to use OPENROWSET to read in data from an Excel 2010 file (an xlsx file).

I have loaded the Microsoft Drivers in "AccessDatabaseEngine_64.exe" and enabled the Ad Hoc Distributed Queries option in SQL.

This is the sample code we are working with:

SELECT
X.MEMBID
FROM OPENROWSET(
'MSDASQL',
'Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=etworkserverFolder1Folder2MEMBIDs.xlsx',
'SELECT * FROM [Sheet1$]'
) AS X

I can run the sample query from my laptop with SSMS (I have admin rights) and I can also run it as SA from my laptop. So all is good, right?

But if I RDC into TEST1, I cannot run the query. I get this error:

OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Excel Driver] Your network access was interrupted. To continue, close the database, and then open it again.".
OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Excel Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x574 Thread 0xb74 DBC 0x1d07f08 Excel'.".
OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Excel Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x574 Thread 0xb74 DBC 0x1d07f08 Excel'.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".But wait! It gets better.

I can run the query as SA from TEST1.

And of course, the developer can't run it either.

And it works fine in the production server.

I'm thinking the basics are there but something isn't right in some permission somewhere.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved