SQL Server 2012 :: Getting Filepath From File Table
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]
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?
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
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?
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
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,
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,
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.
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.
Hi in my table i have a field which would store the file path of an image, the datatype is a varchar, and i wrote the following filepath in it but it didnt work, when i tried to call it up in the browser it didnt show, is the way i saved it below the right format?
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.
I have to create a view where the filename is seperated from the pathto the file. Examples of the data include:m:imagesigmyimg.jpgm:imagesmediummyimg.jpgz:mediaimageshighqualmyimg.jpgIs there a function that will return the position in a string of thelast ? With this I can easily use substring or similar to pull andpush this stuff into the fields I want. If this function does notexist how do I move forward?
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] .....
I am trying to save an uploaded picture filepath to a sql database record. The record that the filepath will be saved to will be the record determined by the logged in userid. I get an error stating Object not set to an instance of an object. This is my code for when the upload button is hitProtected Sub uploadbutton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles uploadbutton.Click If UploadTest.HasFile = False Then ' No file uploaded! UploadDetails.Text = "Please first select a file to upload..." Else ' Display the uploaded file's detailsUploadDetails.Text = String.Format( _ "Uploaded file: {0}<br />" & _"File size (in bytes): {1:N0}<br />" & _"Content-type: {2}", _ UploadTest.FileName, _ UploadTest.FileBytes.Length, _ UploadTest.PostedFile.ContentType) ' Save the fileDim filePath As String = Server.MapPath("~/ProfilePictures/" & UploadTest.FileName) UploadTest.SaveAs(filePath)Dim datasources1 As New SqlDataSourcedatasources1.ConnectionString = ConfigurationManager.ConnectionStrings("aspnetdb_connection").ToString() datasources1.UpdateCommandType = SqlDataSourceCommandType.Text Dim myUserID As String = Membership.GetUser().ProviderUserKey.ToStringdatasources1.UpdateParameters("userid").DefaultValue = myUserID datasources1.UpdateCommand = "UPDATE into profile_details (pic) VALUES (@filepath) WHERE ([UserId] = @UserId)" End If End Sub
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.
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 ...
i am inserting something into the temp table even without creating it before. But this does not give any compilation error. Only when I want to execute the stored procedure I get the error message that there is an invalid temp table. Should this not result in a compilation error rather during the execution time.?
--create the procedure and insert into the temp table without creating it. --no compilation error. CREATE PROC testTemp AS BEGIN INSERT INTO #tmp(dt) SELECT GETDATE() END
only on calling the proc does this give an execution error
What is the best way to transfer data from the staging table into the main table.
Example: Staging Table Name: TableA_satge (# of rows - millions) Main Table Name: TableA_main (# of rows - billions)
Note: Staging table may have some data same as the main table.
Currently I am doing: - Load data into staging table (TableA_stage) - Remove any duplication of rows from the staging table (TableA_stage) - Disable all indexes on main table (TableA_main) - Insert into main table (TableA_main) from staging table (TableA_stage) - Remove any duplication of rows from the main table using CTE (TableA_main) - Rebuild indexes on main_table (TableA_main)
The problem with the above method is that, it takes a lot of time and log file size grows very big.
I have table 'stores' that has 3 columns (storeid, article, doc), I have a second table 'allstores' that has 3 columns(storeid(always 'ALL'), article, doc). The stores table's storeid column will have a stores id, then will have multiple articles, and docs. The 'allstores' table will have 'all' in the store for every article and doc combination. This table is like the master lookup table for all possible article and doc combinations. The 'stores' table will have the actual article and doc per storeid.
What I am wanting to pull is all article, doc combinations that exist in the 'allstores' table, but do not exist in the 'stores' table, per storeid. So if the article/doc combination exists in the 'allstores' table and in the 'stores' table for storeid of 50 does not use that combination, but store 51 does, I want the output of storeid 50, and what combination does not exist for that storeid. I will try this example:
'allstores' 'Stores' storeid doc article storeid doc article ALL 0010 001 101 0010 001 ALL 0010 002 101 0010 002 ALL 0011 001 102 0011 002 ALL 0011 002
So I want the query to pull the one from 'allstores' that does not exist in 'stores' which in this case would the 3rd record "ALL 0011 001".
I started with an inline table returning function with a hard coded input table name. This works fine, but my boss wants me to generalize the function, to give it in input table parameter. That's where I'm running into problems.
In one forum, someone suggested that an input parameter for a table is possible in 2012, and the example I saw used "sysname" as the parameter type. It didn't like that. I tried "table" for the parameter type. It didn't like that.
The other suggestion was to use dynamic sql, which I assume means I can no longer use an inline function.
This means switching to the multi-line function, which I will if I have to, but those are more tedious.
Any syntax for using the inline function to accomplish this, or am I stuck with multi-line?
A simple example of what I'm trying to do is below:
Create FUNCTION [CSH388102].[fnTest] ( -- Add the parameters for the function here @Source_Tbl sysname ) RETURNS TABLE AS RETURN ( select @Source_Tbl.yr from @Source_Tbl )
Error I get is:
Msg 1087, Level 16, State 1, Procedure fnTest, Line 12 Must declare the table variable "@Source_Tbl".
If I use "table" as the parameter type, it gives me:
Msg 156, Level 15, State 1, Procedure fnTest, Line 4 Incorrect syntax near the keyword 'table'. Msg 137, Level 15, State 2, Procedure fnTest, Line 12 Must declare the scalar variable "@Source_Tbl".
I just created a new table with over 100 Columns and I need to populated just the first 2 columns.
The first columns to populate is an identify column that is the primary key. The second column is a foreign_key to an other column and I am trying to populate this columns with all the values from the foreign_key value. This is what I am trying to do.
column1 = ID column2= P_CLIENT_D
SET IDENTITY_INSERT PIM1 ON
INSERT INTO PIM1 (P_CLIENT_ID) SELECT Client.ID FROMP_Client
So I am trying to insert both an identity values and a value from an other table while leaving the other columns blank. How do I go about doing this.
I am trying to use a stored procedure to update a column in a sql table using the value from a variable table I getting errors because my syntax is not correct. I think table aliases are not allowed in UPDATE statements.
This is my statement:
UPDATE [dbo].[sessions_teams] stc SET stc.[Talks] = fmt.found_talks_type FROM @Find_Missing_Talks fmt WHERE stc.sessionid IN (SELECT sessionid FROM @Find_Missing_Talks) AND stc.coupleid IN (SELECT coupleid FROM @Find_Missing_Talks)
I have a function that returns a table from a comma-delimited string.
I want to take this a step further and create a function that will return a set of tablenames in a table based on a 'group' parameter which is a simple integer...1->9, etc.Obviously, what I am doing is not working out.
CREATE FUNCTION dbo.fnReturnTablesForGroup ( @whichgroup int ) RETURNS @RETTAB TABLE ( TABLENAME VARCHAR(50)
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.
I have a 2010 SSIS package where I am reading csv files with different fields and formatted data, I have created separate packages for each file and I am formatting the data to fit the final destination tables data elements, I've been instructed to create 7 separate packages to read each file and the format the data from the 7 csv files, and insert into their table tbl1, tbl2, tbl3...etc then, I'm taking a execute sql task and wanting to insert the tbl1, tbl2, tbl3...etc into destination table that will be the final table for all reports and other uses.
1- should I create a ID? 2- these files will be read once a month 3- I want to append the data, not drop and recreate each run, 4- It's 2012 SQL and 2010 SSIS
Each csv file is in a different format, some have 15 columns, other have 8 I have to parse the data, in SP to align with the fields in the destination table.
5- Can I force RowID to be the next auto gen number from tbl1,for the start of insert for tbl2, then last row of tbl2 for insert of tbl3???
In return I need to check column Status for whenever the value has changed and need to store that in my table. If there are two records for which value in column Status is same, I need to pick only one of the records and that being the earliest of them and therefore the Date field is mentioned in my table . My output should be something like below.
