SQL 2012 :: Access File Table Shares Exposed On A Server?
May 2, 2014
I've been trying to access the file table shares exposed on a server (I can select, delete, update, etc via SQL) via untransacted access with no luck (the share just sits there doing something when I try to access it, no error message of any kind).
So I thought I'd look to see if there were any open file handles via
SELECT * FROM sys.dm_filestream_non_transacted_handles;
I found there were 10 non transacted handles open, and they've been open for 3 days for automated processes and the files are about 1-2Kb in size.
so I tried to kill them via
exec sp_kill_filestream_non_transacted_handles
that's normally not something that takes very long (in fact when I cleared two for a different database it took just a second or two).
So leaving the stored proc running, I figured I'd connect and fire up Adam Mechanic's sp_whoisactive.
I see the processes to kill the open file handles, with a wait state of FFT_NSO_FILEOBJECT
View 2 Replies
ADVERTISEMENT
Jul 2, 2014
We're getting
Msg 1013, Level 16, State 1, Line 1
The objects "MYTEST2.TEST" and "mytest.TEST" in the FROM clause have the same exposed names. Use correlation names to distinguish them.use of fully qualified names is allowed without having to alias them.One workaround is changing compatibility mode to 80.Is there another (startup flag?).Reason for no alias: MS Reportbuilder doesn't provide them when building queries
CREATE SCHEMA MYTEST;
go
CREATE SCHEMA MYTEST2;
go
CREATE TABLE MYTEST.TEST
[code]....
View 2 Replies
View Related
Jun 17, 2015
I'm working in VS 2010 for SSIS 2012. I have a project whose deployment is done via project deployment to SSISDB. I've got a local flatfile connection manager within one of its packages. When I deploy I'm unable to override the file name property in either package configuration or job step configuration on the SQL Server. If the flatfile connection were at the project level, I could. Not sure of the rationale for this or maybe I'm doing something wrong.
View 2 Replies
View Related
Jul 20, 2005
Im trying to find the error in this statement:CREATE PROCEDURE STP_selectmainASselect a.inventoryid, b.firstname, b.lastname, art.title, art.medium,a.cost, a.inventoryid, a.receivedate, a.dimensions,a.reference, art.provenance, sum(c.restorationcost),sum(d.framingcost), sum(e.cost)from art as a left outer join artist as b on a.artistid =b.artistid,a left outer join restoration as c on a.inventoryid =c.inventoryid,a left outer join outframing as d on a.inventoryid =d.inventoryid,a left outer join basiccosts as e on a.inventoryid =e.inventoryidgroup by a.inventoryid, b.firstname, b.lastname, a.title, a.medium,a.cost, a.inventoryid, a.receivedate, a.dimensions, a.reference,a.provenanceorder by a.inventoryid descGOeveytime I do a syntax check on it I get this error.error 1012: the correlation name 'a' has the same exposed name astable 'a'.Whats the syntax to fix this?thanks-Jim
View 1 Replies
View Related
Aug 31, 2015
I have a FileTable as part of an application that a client uses. In general the response of the SQL Server has been great. However, they have started to make use of a part of the application that handles images. The application has a FileTable set up. However, there is a Stored Procedure that retrieves the path of the requested file. It is taking the Server over two seconds to return a single file path.
USE [BMIPictureDB]
GO
/****** Object: StoredProcedure [dbo].[GetFileTablePath] Script Date: 8/31/2015 6:07:37 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetFileTablePath]
[Code] ....
View 2 Replies
View Related
Aug 31, 2015
I have been tasksed to create a data table and stored procedure to extract a special formatted XML file that is an attachment with a standard XML envelope. The XML file is an attchment in a node within the XML wrapper. There are other MIME files (pdf's ) that are handle by a seperate procedure. But I need to just extract the XML file attached along with those and put it into the datable with some other PK?FK fields.
Is a blob the best datatype. How to I insert that XML file into it?
View 2 Replies
View Related
Jun 6, 2015
how to import a text file with a list of NI numbers into a new table with a column to list all the NI numbers? I think I use the Select INTO clause, but not sure how to do this?
View 1 Replies
View Related
Jul 22, 2013
Overall goal: Write a Bulk Insert statement using the UNC path of a filetable directory.
Issue: When using the UNC path of the filetable directory in a Bulk Insert Statement, receiving "Operating system error code 50(The request is not supported.)" Looking for confirmation as to whether this is truly not supported.
Environment: SQL Server 2012 Standard. Windows Server 2008 R2 Standard
View 9 Replies
View Related
May 29, 2015
I am looking for a way to convert the following format into a sql table. The format it is Bib Tex.
Essentially a new row in the table would be for each entry, denoted by an @ logo and each column is denoted by an =, as you can see from the example data no one contains all the possible columns and some fields can be over two lines long.
To load this I was considering loading it into a table as each line being a row. Adding a row number, then a column counting the @ signs in order and essentially grouping each record, then for each group running through and looking for the column keywords 'author' , 'title' etc then splitting the data out into those constituent parts using substring and charindex.
@Book{hicks2001,
author = "von Hicks, III, Michael",
title = "Design of a Carbon Fiber Composite Grid Structure for the GLAST
Spacecraft Using a Novel Manufacturing Technique",
publisher = "Stanford Press",
year = 2001,
[Code] ....
View 9 Replies
View Related
Sep 21, 2015
I have been given a request at work that requires us to run the SQL scripts that are held in a report configuration table and output the results as .csv or.xls files in a desired folder with a file name that is also specified within the report config table.
An example of the table is as per script below with column A being the desired file name to be created and Column B contains the script that will be executed.
Ideally I require a script that i can drop into a Stored Proc that will run down each row in the table and export and create each rows results as a separate file in a desired folder.
------------------------------------------------------------------------------------------
-----SAMPLE TABLE SCRIPT -----------------------------------------------------------
------------------------------------------------------------------------------------------
/****** Object: Table [dbo].[Test_Table_PS] Script Date: 21/09/2015 09:14:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Test_Table_PS](
[File_Name] [nvarchar](100) NULL,
[Code] ....
View 7 Replies
View Related
Apr 14, 2015
I am having one store procedure which use to load data from flat file to staging table dynamically. everything is working fine.Staging_temp table have single column.All the data stored in that single column below is the sample Data.
AB¯ALBERTA ¯93¯AI
AI¯ALBERTA INDIRECT ¯94¯AI
AL¯ALABAMA ¯30¯
[Code]....
View 2 Replies
View Related
Apr 3, 2015
I am unable to load data from flat file to sql table using bulk insert sql statement
My code:-
DECLARE @filePath VARCHAR(200)
DECLARE @sql VARCHAR(8000)
Declare @filename varchar(100)
set @filename='CCNVZ_150401054418'
SET @filePath = 'I:IncomingFiles'+@FileName+'.txt'
[Code] .....
View 1 Replies
View Related
Jul 2, 2015
I have a filetable that contains a binary file. I need to do a selective read of the file stored in the file table. I can write a C# CLR function that will open the file, read n bytes the from a starting byte. Or I can write a SQL statement that reads the stream in the filetable into a VARBINARY variable using SUBSTRING beginning at the starting byte (offset from 1) for the same n bytes.
Both give me the same result. However, the SQL statement takes considerably longer to read. I know there is overhead in reading through SQL (interpreted language), but the difference in performance is substantial, and I can only attribute this performance degradation if SQL first tries to "load" the entire stream before it identifies the portion of the stream that it needs to read beginning at the starting byte offset.
I wonder if this is the case or if there is another option to read a stream from a filetable directly through SQL queries that is more efficient.
View 3 Replies
View Related
Aug 19, 2014
Get a filestream download link with only access to read and with folder navigation
I need a link with the path to get the file stream blob, that path could be used to download a document using any windows app like windows explorer, etc. the requirement is that path does not allow customer to navigate in filesstream share folders or see other files and only can read the file of the path,
Checking :
[file_stream].GetFileNamespacePath(2)
Allow you navigate in folders.
NON_TRANSACTED_ACCESS read_only, resolve the requirement to disable the save in file table, but allow you navigate and see other files.
View 0 Replies
View Related
Jul 23, 2005
Hi,My SQL server is short on disk space and I need to create a new instanceof SQLServer. I had another server running Linux with enough space leftso i create a new partition on it and shared it through SAMBA.Now I can use this shared disk in RW mode from the SQL server (tested OK).However, when i try to create another database on this network share,SQLServer denies the operation.Does anybody have a clue about what's going on?Isn't it possible to set database files on a network share?Regards.CH COLLIN
View 2 Replies
View Related
Jul 15, 2015
Any specific risks in placing database files on network shares on a windows server (2008 R2). I have read its a bad design, but unsure why, or what problems such a setup may cause?
View 4 Replies
View Related
Jul 31, 2014
I need to write a process to get file size in kb and record count in a file. I was planning on writing a c# console app that takes the file path and name as a param however should i use a CLR?
I cant put a script in the ssis when it's bringing the file down because it has been deemed that we only use ssis for file consumption.
View 1 Replies
View Related
Jan 23, 2015
I want to import an XML file in an Table. The XML file is on an Other Network location than the Database server I’m connected to and I have access to this location with my windows Credential.
I connect from my local PC to the Database server “RS1” with my Windows Credentials.
There I run this script
CREATE TABLE XMLwithOpenXML
(
Id INT IDENTITY PRIMARY KEY,
XMLData XML,
LoadedDateTime DATETIME
)
INSERT INTO XMLwithOpenXML(XMLData, LoadedDateTime)
SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE()
FROM OPENROWSET(BULK '<Networkname><MAP>Name_.xml', SINGLE_BLOB) AS x;
I Get the error:
Msg 4861, Level 16, State 1, Line 10
Cannot bulk load because the file "<Networkname><MAP>Name_.xml'" could not be opened. Operating system error code 5(Access is denied.).
(<Networkname><MAP>Name_.xml' is not the real name )
So it looks like the openrowset connects to the network location with other credentials.
Where I can find (and change) the credentials which is used to connect to the network location?
I found this article on MSDN (Security Considerations) but cannot find solution [URL] .....
View 1 Replies
View Related
Sep 29, 2015
I have two databases DB1 and DB2 DB1 has a source table named 'Source' I have created a login 'Test_user' in DB2 with Public access. I have also created a view named 'Test_view' in DB2 which references data from DB1.dbo.Source
How can I do the following: AS A Test_user
SELECT * FROM DB2.dbo.Test_view --Should work
SELECT * FROM DB1.dbo.Source --Should Not work
View 2 Replies
View Related
Jul 20, 2005
In Access 97, I am trying to attach to a table in my SQL Server 2000db.I use File/Get Data, which displays all the SQL tables including theone I can't attach to. (I can attach to all the other SQL tables.)But, when I select that table I get a message that tells me the nameis invalid. I don't understand this because it gives me the name inthe Get Data dialog, and I don't have to enter it manually.Any ideas?Thanks,Bob C.
View 3 Replies
View Related
Dec 15, 2014
I have a filetable and use a statement like
SELECT [name],file_stream
FROM dbo.Inkoopfacturen
WHERE FREETEXT(file_stream, 'Factuur');
It will return the text found in a .pptx but not in a pdf. There are no errors in the FT-log. It tells me that the FT-index is succesfully build.
I have downloaded the iFilter for Adobe (installed it in a folder with a short name), set the temp variable to the bin folder of the installation.
I've executed the following queries:
EXEC sp_fulltext_service 'update_languages';
EXEC sp_fulltext_service 'load_os_resources', 1;
EXEC sp_fulltext_service 'restart_all_fdhosts';
Exec sp_fulltext_service 'verify_signature', 0
When I run the following statement, it'll show pdf in the resultset.
SELECT * FROM sys.fulltext_document_types order by document_type
Still, content is not found.
View 5 Replies
View Related
May 14, 2008
hello,
I'm not really sure my question belongs to here...
I have a database in Access (from microsoft office, of course), and I want to transfer convert it to SQL Server.
Does anyone know how I can do it? It must be very simple, but I haven't found it yet....
Thanks a lot!
- Miri
View 7 Replies
View Related
Oct 29, 2007
I'm having trouble trying to access a network share that comes via a UNIX server running SAMBA. In the first case, I'm running on my local workstation (A), connected to a remote server (B), and attempting to access directory information for a path like:
\a0amsimmsworkseaborg argets11as2981
This path is fully accessible by me from the workstation (A) and the server (B). The files and directories below "work" in the above path are also wide open on the UNIX side (meaning r-xr-xrwx permissions). However, if I attempt to do something like this:
Code Block
WindowsIdentity newID = SqlContext.WindowsIdentity;
WindowsImpersonationContext impersonatedUser = newID.Impersonate();
bool sim_dir_exists = false;
try
{
impusername = Environment.UserName;
Directory.GetFiles(mdcfullpath);
}
catch (Exception e)
{
impersonatedUser.Undo();
SqlContext.Pipe.Send("Exception getting data: " + e.ToString());
SqlContext.Pipe.Send("CWD is: " + Directory.GetCurrentDirectory());
SqlContext.Pipe.Send("User is: " + impusername);
}
finally
{
impersonatedUser.Undo();
}
The "GetFiles" fails with the following exception:
Exception getting data: System.UnauthorizedAccessException: Access to the path '\a0amsimmsworkseaborg argets11as2981' is denied.
at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
at System.IO.Directory.InternalGetFileDirectoryNames(String path, String userPathOriginal, String searchPattern, Boolean includeFiles, Boolean includeDirs, SearchOption searchOption)
at System.IO.Directory.GetFiles(String path, String searchPattern, SearchOption searchOption)
at System.IO.Directory.GetFiles(String path)
at StoredProcedures.mdcinfo(Int32 sim_id, String mdc_base)
CWD is: C:WINDOWSsystem32
User is: amsimms
Initial is: dbserver
Interestingly, if I run the procedure directly on the server (B), I do not get the exception. So this seems to be more of a delegation problem. The server B's sql server instance is running as a domain account (dbserver), which has been enabled for delegation and an spn has been set up. Is there something beyond this either with the impersonate or delegation configuration that I need to do in order for this to work?
Thanks,
--Andrew
View 5 Replies
View Related
Mar 22, 2007
I've seen examples of creating a Web Service that executes an SSIS package through code. But that involves developing the web service, configuring IIS on the same server as SSIS to expose the web service. I was wondering if it is easy to use the HTTP endpoints within SQL Server 2005 to create a web service interface that calls an SSIS package, supplying any parameters the package needs to run. Also, this would be called from within Excel VBA that uses a web-reference via the Web Reference Toolkit.
Thanks
Kory
View 3 Replies
View Related
Sep 30, 2014
In Access, I can import a txt file data(e.g. Claims.txt) as below specifications:
Choose the delimiter that separates your fields: Other (|)
First row contains field name: Yes
Text Qualifier:"
I need to create a store procedure to read txt file data (d:cliamcliams.txt) first column (ClaimNumber) into a temp table (#claim)
(This #claim table will use for my .Net program)
There about 5 txt files need to process every day.
View 1 Replies
View Related
Jan 29, 2015
I need to add an existing shared folder to a SQL FileTable. So this is the path and I created a SQL Filetable and now I need to add it to the filetable.
View 0 Replies
View Related
Nov 29, 2006
Is there a way to specify a MS Access table (or query object) in the select query of SQL Server.
Ex.:
MSAccessTable (in file.mdb)
col1
col2
a1
a2
b1
b2
SQL query in SQL Server:
SELECT col1, col2 into SqlTable from [file.mdb].MSAccessTable;
Thanks,
View 3 Replies
View Related
Sep 21, 2007
We have some to-disk backups scheduled on our 2000 Enterprise machine - nightly fulls, hourly logs - that go to a network share located on another machine. They were originally stored directly on the same machine as SQL Server, but we changed them to a remote destination within the past few weeks. This works okay, but despite having the maintenance plan set to remove files older than 2 days, old files don't seem to be removed. Understandably, this gets to be a problem when the backup disk becomes filled.
Is there any obvious reason why this option wouldn't work against a network share? I've checked the directory permissions, and the SQL Agent domain account should have no trouble deleting the files.
View 1 Replies
View Related
Dec 10, 2014
If I log on to the SQL server and using IE go to http://sql1/reportServer I see the reportServer webpage just fine.However if I go to another server in the same domain and do exactly the same thing (same user account, same URL) I get a windows box asking for username and password. It doesn't matter what username I use I still get prompted for username and password.
I've looked in the sys.dm_exec_connection and it says NTLM so I am guessing that SPN are not the cause.
We have another SQL instance on the same SQL server and you can reach the reporting webpage just fine no matter where you are.
The only thing I have noticed different is the working instance has login for DPM$ (being a DPM instance) but the default instance does not have a SQL1$ entry. Don't know if that is important.
View 2 Replies
View Related
May 27, 2014
As a database developer, I have so many databases that I "own" scattered across various servers that it''s getting difficult to remember where all of my databases reside. It doesn't work that the DBAs have taken to some very hard to remember server naming conventions.
I was going to create aliases via the configuration manager, but it turns out the DBAs overwrite my entries each night with THEIR aliases and they won't add any for my use.
So I decided to simply add some records to my host file so that instead of having to connect to "SERVER-AD_DADF-DAFDASS" I can just use "CustomerA".
This solution seems to work until I tried to access a linked server. If I connect to the main server via it's actual name, I can hit the remote/linked server with no issue. However, if I connect to the main server using it's alias, connection to the remote/linked server fails with: Login failed for user 'NT AUTHORITYANONYMOUS LOGON'.
The linked server is set up to use current security context and I'm not logging into the alias any differently that I do when using the actual server name.
View 9 Replies
View Related
Aug 4, 2014
First time I've tried doing this - I have SQL Server 2012 installed on my local machine with an instance running (which was set up under an administrator account, not mine, which has no admin rights), and I'm trying to create a linked table from Access (also on the local machine) to a table on the server.
I tried creating a DSN using Windows Authentication, using the server name which is the same as my computer name, and got the error "Error 18452, Login failed. The login is from an untrusted domain and cannot be used with Windows authentication".
I then created a SQL authentication user ID and password, and tried the same thing using that instead, and got "Login failed for user <myusername>"
Is there some particular setting on the local instance I need to change to allow this kind of connection, or do I need to use something other than the server name to connect, such as an IP address? Remember that I have no admin access on this machine so any solution would have to avoid requiring that.
View 3 Replies
View Related
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
Aug 11, 2014
I need to use Bulk insert statement for copying a table with 200 million rows to another table on the same server...the table has no primary key or identity column.... script for BULK INSERT ...
View 9 Replies
View Related