SQL 2012 :: OpenRowSet Works Locally But Not Over Networked Drives?
Jul 10, 2013
getting SQL able to import files via ORS. So we got it to work on local drives, but not over networked drives. This is the code and error:
select * into sample.dbo.[eriktest] from
OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
'Data Source=serverSample est.xlsx;
Extended Properties="Excel 12.0 XML;HDR=YES;IMEX=1"')...[Sheet1$]
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the 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)".
But going from the local C: or D: drives is fine. I also tried using xp_cmdshell (OPC) to map a drive, and then tried using ORS with the drive letter, and got the same error.
I am very confused. I've finally gotten everything to work the way I want on my pc, but when I ftp to the Internet (URL is hosted on GoDaddy), everything goes haywire. I had to create a new database on the host server, and when I use an aspx login, I get the following message:
The SSE Provider did not find the database file specified in the connection string. At the configured trust level (below High trust level), the SSE provider can not automatically create the database file.
I know what the connection string to the database is, but where do I enter that information? I'm obviously new at asp.net and sql, and I need major help.
I have an application that utilizes aspnet for membership. The application utilizes the same database (in SQLServer) for storage of other things. I have a view as follows:
USE [DB_Name] GO /****** Object: View [dbo].[Authorization] Script Date: 06/29/2007 15:27:25 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW Authorization AS SELECT dbo.Domain.Domain_ID, dbo.Domain.Domain_Name, dbo.Alias.Alias_Name, dbo.Alias.Alias_Authority, dbo.Domain.Domain_ProcessType, dbo.Domain.Domain_Status, dbo.aspnet_Users.UserName AS Owner_Name FROM dbo.Alias INNER JOIN dbo.Domain ON dbo.Alias.Alias_Parent_ID = dbo.Domain.Domain_ID INNER JOIN dbo.aspnet_Users ON dbo.Domain.Domain_Owner_ID = dbo.aspnet_Users.UserId
This works fine locally. However, I am hosting this on GoDaddy and though it allows me to create the table, it seems to have a problem (I'm guessing because it is referencing differing schemas?) and I get an error of: Incorrect syntax near the keyword 'Authorization'.through the SQLServer Web interface whenever I attempt to view the data from this view. I don't doubt that there is some kind of difference between the manner that SQLServer has set up the schemas and how they are set up on my local version. Any help would be greatly appreciated. I am at a loss of how to get this info and truly want to utilize the view as it would improve performance over a query - at least I believe so. My thinking is that although there may be a performance drop when the item is being added, I gain on the back-end where I really need the performance over utilizing a query.
I have a DTS package that I am calling in my code-behind (vb) of one of my asp.net pages on my intranet. The package takes a FoxPro table that is on our network and inserts it into a SQL Server table in one of our databases. The package gets called and executed properly when I am working on it in development on my local workstation, but when I publish it to our intranet, which is in the same domain and behind the same firewall, the package does not execute. No error shows up on the published page, but also nothing gets executed. Has anyone encountered this? Here is my code, which is pretty straightforward: oPkg = Server.CreateObject("DTS.Package")oPkg.LoadFromSQLServer("[servername]", "sa", "[password]", DTSSQLStgFlag_Default, "", "", "", "Invoice Import")oPkg.Execute() Any help would be appreciated. Thanks.
Hi all, I hope this is a simple issue that I'm overlooking something or have contradicting permissions set somewhere. I have a fairly basic site with a subfolder containing an aspx page. The user logs in to gain access to the subfolder. That part works fine.On another page(also within a subfolder in the subfolder), they can post form data to a database (SQL Server 2000) which exists on a different server than my web server. This page opens fine, but when submitting the data (only one value to one field for my testing purposes), it bombs and returns the error "Login failed for user"...In my connection string I've specified the username and password which I assigned to the db in Enterprise Manager (not sa, btw). I've also tried letting IIS control the UID/PW, and even specified no UID/PW in either the connection string or the db. Anonymous access enabled in IIS for this virtual directory also returns the error. (?)I should mention that in Visual Studio, the connection string was automatically set to my local development machine's workstation ID. I removed that and have tried replacing it with the server name, blank, etc.(in notepad) - to no avail. dll's are in their proper places, etc. The only thing not working is the database connection.Using: IIS v6, SQL Server 2000, and Visual Studio.NET 2003 (1.1 framework)Anyway - any suggestions? There are so many places to enable/disable permissions, it's getting confusing. I hope it's something I'm overlooking and not a problem with our SQL installation...Thanks!--Donnie
I am having the most baffling problem with DTS.... :confused:
I have a set of ActiveX transforms that execute on my customers flat transaction data files, destination a single database table. Since they switched to a new method of generating the flat file using SAS, the DTS package mysteriously will fail at a couple select records. The error is always the same, and turning on error logging in DTS yielded this:
Step 'DTSStep_DTSDataPumpTask_1' failed
Step Error Source: Microsoft Data Transformation Services Flat File Rowset Provider Step Error Description:Too many columns found in the current row; non-whitespace characters were found after the last defined column's data. Step Error code: 80043013 Step Error Help File: DTSFFile.hlp Step Error Help Context ID:0
The exact same file parses all the way through on my laptop, with the same DTS package. Tests have revealed no strange characters or whitespaces in the data file, not at that record (running a Test... on any of the active x transforms will fail at row 515186 always, until that row is deleted and it fails on some subsequent row - this iteration went on at the customer site until about 5 rows were deleted this month and it finally worked), not at any other records. My database and the customer database are both using the same, default character set.
The only microsoft KB article referencing anything resembling my problem is http://support.microsoft.com/default.aspx?scid=kb;en-us;292588 but this does not hold because I am not specifying fixed width, but rather comma delimited.
If anyone has any ideas about what other environmental variables are coming into play here, please let me know - I'm at the end of my rope. I believe we are both patched up to SQL 2000 SP3. They have an XP client connecting to a 2003 server; I have an XP client/server. Neither machine has the NLS_LANG environment variable set.
People,I'm trying to publish my first website and am having a few problems.I've got Visual Web Developer 2005 Express and am trying to use the Personal Website Starter Kit. (my SQL server is SQL Server Express Edition 2005 - which is also running on my local machine)It seems to work fine when I run it on my localhost, as soon as I ftp it up to my web hosting company, I get an error message (see below) :-An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) My hypothesis is :-It would appear to me that when running locally, the starter kit website uses my installation of SQL 2005 Express Edition, but when I upload all the files, I'm guessing the application is still trying to point at a local instance of SQL on my local PC which it now cannot see. I'm guessing I need to somehow upload the SQL database onto my web host (I've purchased 100M of SQL Server 2005 space), and point the application at that SQL instance instead. But I don't know if I'm right about all this, or indeed how to do it if I am. Can anyone help?Much thanks in advance,Will
HTTP Error 401.1 - Unauthorized: Access is denied due to invalid credentials. Internet Information Services (IIS)
when I attempt to connect to Reports or ReportServer from my desktop, but it works normally when I login with the same userid and run it directly on the server, using IE. It is prompting me for a login 2 or 3 times before failing.
My configuration is:
Report Server system:
Windows Server 2003 R2 SP2 - 32bit
SQL Server 2005 Reporting Services Enterprise Edition Windows & Web Service run as a domain account Database connection is using domain account - not in db_owner, but in RSExec roles Database is on another server, and is Native, not SharePoint integrated Reports & ReportServer are in a separate application pool Reporting Services had SP2 installed before it was configured The Rport Server is a VM
Database server
Windows Server 2003 R2 SP2 - x64 SQL Server 2005 Enterprise Edition 64bit Version 9.00.3200.00
The Report Manager was not working at all, and I discovered that there was no entry in the .Net Framework version on the APS.Net tab in properties for the Reports virtual directory. I am not seeing any errors or anything unusual in the Event log or in the ReportServer log files.
FrontPage 2002 extensions were installed, and then removed. I noticed that this installed a SharePoint virtual directory, and that disappeared when I removed FrontPage extensions.
The domain group my userid is in is in the local Administrators group on the ReportServer system, and I have added this group as a System Administrator and Content Manager through the report Manager.
I need to set up web services locally on my machine.I am working in a team. They gave project that I mapped to my local drive and take the latest version and build the solution. Some of them are configured to use IIS. So before build the solution, in the IIS manager I created the self certificate and added https binding.
I am not sure after that what I have to do? How to test my web services are running locally or not?Do I need to do anything else to set up web services locally?
I have tried doing sql server backups to a file share and that has been taking too long. So I've decided to backup locally and then taking those backups and getting them off the server. For those that are doing this what do you use to get your backups off the server?
Is it a best practice to disable "Allow files on this drive to have contents indexed" on NTFS drives used by SQL for its data, log, tempdb, etc?
In what I've read it seems to be a best practice for Filestream objects and Flash storage drives. We don't currently use Filestream objects or have Flash drives.
Are there any benefits or drawbacks to disabling this feature on an NTFS drive connected to SAN LUNs under mount points?
We are going to install a SQL Server 2012 Ent. Edition two node (Active/Passive) cluster. Only one instance. Issue is, a separate shared storage is not provision for MSDTC.
1. Is it mandatory to configure MSDTC for a single SQL 2012 instance ?
2. Can we use one of the shared drives (Data/log/bkp/temp) for configuring MSDTC ?
how to update a SQL 2012 DB that is hosted off-site with a SQL DB that is running locally. This is a situation where the updates are one-way, local to remote.
I have an XML file about 25MB. When I read it with openrowset it gives me 'System.OutOfMemoryException'. The machine I'm running has 16GB of RAM and memory is definetely not exceeded. When I run smaller XML files it works fine.
I've read that older versions of SQL Server had this problem and it was caused by the parser having limited amount of memory. Is this still the case? Is there a way to change this?
I am writing a custom query to determine if a legacy table exists or not. From My CMS Server I already have all the instances I have to query and I store the name of the instance in the @Instance variable. I cannot get those stubborn ticks to work right in my query. Below I am using the IF EXISTS statement to search the metadata for the legacy table.
DECLARE @Found tinyint DECLARE @Instance varchar(100) set @Instance = 'The Instance' IF (EXISTS (SELECT a.* FROM OPENROWSET('SQLNCLI', 'Server=' + @Instance + ';UID=DBAReader;PWD=DBAReader;','SELECT * FROM [DBA].INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''TheTable''') AS a)) SET @Found = 1 ELSE SET @Found = 0
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.
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.
Hello all. I have just built another PC (New PC)and have it connected to myOriginal PC via a LAN. I have my SQL Server 2000 installed on the OriginalPC. Both PCs use WinXP Pro OS. I believe I now have to install the ClientTools on the New PC but after that, how do I access the SQL Server that ison the Original PC. The SQL Server is setup with mixed (both WindowsAuthentication and SQL Server Authentication). Just how do I use the SQLQuery Analyzer that I just installed on the New PC to connect to andretreive data from the SQL Databases which are on the Original PC? Thanksin advance for any help, Jim.
Ain't sure if this is the right place. but anyway, here is the situation. I have 2 PCs and both are running Windows XP (A is using SP 2 and B is using SP 1) which are connected via a router and A can ping B and vice versa. I have SQL 2000 installed on A, so how do i go about setting up B so that B can access the database and tables of SQK 2000?Thanks.
I want to connect to SQLExpress database on the network. I can do so using code but I want to use the Data Source Configuration Wizard. It seems this only works for local instances of SQL server. Is this true?
I can connect just fine to the database on the network using Microsoft SQL Server Management Studio Express...for what this is worth.
I have tried to map a drive to the SQL server machine and pick the mdf file but I get the error msg of network path is not supported for database files....I have turned on allow remote connections, allow tcp/ip, named pipes, added sqlserv.exe and sqlbrowser.exe to my firewall exceptions ect..
I want to be able to use Table Adapters, SqlDataAdapters ect. After reading numerous forum questions/ kb articles this seems to be a common problem. I am on the verge of just saying forget it and use MS Access instead.
Create table #test ( id int primary key, Name varchar(100) ) insert into #test values (1,'John') insert into #test values (2,'Walker')
[Code] ....
-- Query 1 : update #test set name = 'Joney' where id = 1
-- Query 2 : set rowcount 1 update #test set name = 'Joney' where id = 1 set rowcount 0
1. #test table have primary key & clustered index. 2. Obviously only one row will be available for an id. 3. In query 1, will the sql server look for matching rows even after it found 1 row? 4. Will query 2 really gains some performance?
I've made a report in which there are always one tablix under each textbox. I tried to produce a document map by giving document map labels for each textbox.
Everything works fine in Report Builder (3.0), but when run in the browser (IE, Firefox) the document map doesn't seem to work: when trying to navigate with document map by clicking the labels the report runs to the end of the report - not to the textbox where it should go...
I tried to solve problem using bookmarks instead of Document map, but there similar problem occurred. Bookmarks seem to work with Google Chrome, but I need a solution that would work better in most of the browsers.
So the problem seems to be that document map label navigates to bottom of the page. Added pagebreak after each tablix worked but since the tablixes are longer than screen there's still problem - document map should be set to navigate to the top of the page.
DECLARE @DatePartitionFunction nvarchar(max) = N'CREATE PARTITION FUNCTION DatePartitionFunction (datetime) AS RANGE RIGHT FOR VALUES ('; DECLARE @i datetime = '2007-09-01 00:00:00.000'; WHILE @i < '2008-10-01 00:00:00.000' BEGIN SET @DatePartitionFunction += '''' + CAST(@i as nvarchar(10)) + '''' + N', ';
[Code] ....
Msg 7705, Level 16, State 2, Line 1 Could not implicitly convert range values type specified at ordinal 1 to partition function parameter type.
However if I change to datetime2 it works
DECLARE @DatePartitionFunction nvarchar(max) = N'CREATE PARTITION FUNCTION DatePartitionFunction (datetime2) AS RANGE RIGHT FOR VALUES ('; DECLARE @i datetime2 = '2007-09-01 00:00:00.000'; WHILE @i < '2008-10-01 00:00:00.000' BEGIN SET @DatePartitionFunction += '''' + CAST(@i as nvarchar(10)) + '''' + N', ';
[Code] ...
Is the data type of the column used for partitioning. All data types are valid for use as partitioning columns, except text, ntext, image, xml, timestamp, varchar(max), nvarchar(max), varbinary(max), alias data types, or CLR user-defined data types.
In this case why isn't datetime works?
version is as follow:
Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) Dec 28 2012 20:23:12 Copyright (c) Microsoft Corporation Enterprise Evaluation Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
from [URL] .....
Table and index partitioning is supported in this edition
I am trying to send out notifications when jobs complete (fail or succeed). I have database mail working fine on my DEV server, but I am having issues with it on my PROD server. I am currently having people look into if McAfee may be blocking it.
I am able to send out a test email from SSMS>Management>Database Mail, but when I set a Notification for a job, the job will complete and in the history, it will say "NOTE: Failed to notify 'User' via email."
I have created an Operator and set up Profiles and Accounts, just as I did on my DEV server.
I am trying to delete tables from data where the ModifiedDates older than 9 years in AdventureWorks2012 database . I get console notified that foreign keys are dropped but the delete statement is throwing errors. I am sure that somewhere the key constraints are not getting altered, but i'm not able to figure it out as i'm a relative beginner to T-SQL. The error and code:
The DELETE statement conflicted with the REFERENCE constraint "FK_SalesOrderHeaderSalesReason_SalesReason_SalesReasonID". The conflict occurred in database "AdventureWorks2012", table "Sales.SalesOrderHeader [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null $option_drop = new-object Microsoft.SqlServer.Management.Smo.ScriptingOptions; $option_drop.ScriptDrops = $true;
I've got a popular problem so i get a message that server acces denied! ..
But that what is different in my error.... When i use same setting same database and connection string (on MSDE server) there is no problem...
On SQL server i have got windwos authentication but i added all accounts as ASPNET and SA.... and when i try to connect by
RETTO - name of my server
server=RETTO;uid=sa;pwd=password;database=db1; or by Integrated Security=SSPIserver=RETTO;uid=RETTOASPNET;database=db1;
I CAN BROWSE RECORDS THERE ARE NO PROBLEMS WITH CONNECTION!!! but when i try to update or iinsert or delete something in database there becomame this error that access denied or server does not exist!!!
PLEASE HELP I'm FIGHTING WITH THAT FOR OVER 5 DAYS!!!
I MADE FOR MY ACCOUNTS (SA, ASPNET) ALL THINGS ALLOWED AS EXECUTING stored procedures.. OR ACCESING datatables with insert delete and update query WHERE IS THE PROBLEM!!!??
I'm having a strange problem with this but I know (and admit) that the problem is on my PC and nowhere else. My firewall was causing a problem because I was unable to PING the database server, switching this off gets a successful PING immediately. The most useful utility to date is running netstat -an in the command window. This illustrates all the connections that are live and ports that are being listed to. I can establish a connection both by running
TCP 81.105.102.47:1134 217.194.210.169:1433 ESTABLISHED
TCP 81.105.102.47:1135 217.194.210.169:1433 ESTABLISHED
TCP 127.0.0.1:1031 0.0.0.0:0 LISTENING
TCP 127.0.0.1:5354 0.0.0.0:0 LISTENING
TCP 127.0.0.1:51114 0.0.0.0:0 LISTENING
TCP 127.0.0.1:51201 0.0.0.0:0 LISTENING
TCP 127.0.0.1:51202 0.0.0.0:0 LISTENING
TCP 127.0.0.1:51203 0.0.0.0:0 LISTENING
TCP 127.0.0.1:51204 0.0.0.0:0 LISTENING
TCP 127.0.0.1:51206 0.0.0.0:0 LISTENING
UDP 0.0.0.0:445 *:*
UDP 0.0.0.0:500 *:*
UDP 0.0.0.0:1025 *:*
UDP 0.0.0.0:1030 *:*
UDP 0.0.0.0:3456 *:*
UDP 0.0.0.0:4500 *:*
UDP 81.105.102.47:123 *:*
UDP 81.105.102.47:1900 *:*
UDP 81.105.102.47:5353 *:*
UDP 127.0.0.1:123 *:*
UDP 127.0.0.1:1086 *:*
UDP 127.0.0.1:1900 *:*
Both these utilities show as establishing a connection in netstat so I am able to connect the database server every time, this worked throughout yesterday and has continued this morning.
The problem is when I attempt to use SQL Server Management Studio. When I attempt to connect to tcp:sql5.hostinguk.net, 1433 nothing shows in netstat at all. There is an option to encrypt the connection in the connection properties tab in management studio, when I enable this I do get an entry in netstat -an, see below:
Amost as if it's trying the different ports but you get this time_wait thing. The error message is more meaningful and hopefull because I get:
A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) (.Net SqlClient Data Provider)
I would expect this as the DNS has not been advised to encrypt the conection.
This is much better than the : Login failed for user 'COX10289'. (.Net SqlClient Data Provider) that I get, irrespective of whether I enter a password or not.
This is on a XP machine trying to connect to the remote webhosting company via the internet.
I can ping the server
I have enabled shared memory and tcp/ip in protocols, named pipes and via are disabled
I do not have any aliases set up
No I do not force encryption
I wonder if you have any further suggestions to this problem?
I am trying to run a program called NVivo7, which - when I install - also requires installation of SQL Server. Because my C drive is small (and nearly full), I am trying to run NVivo7 off my D drive, though SQL seems to install on C. Is it possible, do you think, to use 2 different drives in this way, or do both the program and the Server need to be on the same one? If so, is there any way to get them both on D?
Im running sql 2005 developer edition on my xp pro workstation. For some reason I can no longer create a database. I get an error such as the following: "CREATE DATABASE permission denied in database 'master' (Microsoft sql server error: 262)"
How can i create a new db to run locally on my pc using sql server. I need also to install a copy of this new db in another pc. Do i need the sql server installed in the other machine also? I am thinking to create an application in vb to play with the db. Every time i start the sql server i see the window telling to connect do database engine, may be i am wrong but with this window i can create just a db in the server? i couldn't create a db locally yet, anyone can help me?
Hello Forum, after installing 2005, if trying to connect to local server it is giving me the following error:
cannot connect to ols00038
An error has occurred while establishing a connection to the server. when connecting to SQL server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote Connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error:2)
So i'm not really new, but got a question. i've recently been looking to to the Western Digital Raptor Drives.
as far as performance, it's always been my understanding that the speed of the hard drive is just about always the bottle-neck of a computer. i'm currently running 2 stripped WD 500gb SATA drives for my SQL server (dual Xeon 2.8 with 2GB memory).
i'm thinking of upgarding to 4 WD Raptors (10k RPM) drives, the new 150GB models. anyone have an opionion? do you think i'll get a large performance increase?
the database that i run queries off of now is about 125 Million names, with about 80 fields in width. so it's rather large, and usally takes a fair abount of time to get my results back (we're talking anywhere from a minute, to half the day.)
do you think the raptors will slim that down significantly?
