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] .....
how I can load the CSV file data into the sql server table. I know there are ways like bulk insert and other to load the csv file data into the table. But in my case the table doesn't exist and has to be created at the run time. With simple insert in temp table we do like select * into #temp from tablename and that creates the temp table. So. I need something like that which create the temp table and load the data into it. because the CSV file would have different number of columns and names so I can not create the table structure in advance. I have to create the table at run time.
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?
I have an Excel file with .csv extension . it has on sheet with name Sheet1.
Now, I'm trying to insert this Excel data into one #temp table. I tried with syntax:
---------------- Exec sp_configure 'show advanced options', 1; RECONFIGURE; GO Exec sp_configure 'Ad Hoc Distributed Queries', 1; RECONFIGURE; GO EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1;
[Code] ...
But, I'm getting error:
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.
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
If I'm executing for .xls file this statement is working finr and rows are inserted into #temp table. How to take excel file of .csv extension??
I have someone who is sending me .htm documents, with a table in them, and I was wondering if there is a way to import the data from those tables into a SQL table, probably using an SSIS Package.
I'm moving data between identical tables and have to use a flat file as an intermediary. I thought: "No problem, SSIS can do a quick export to a file, then move the file to another server, then use SSIS to import the data to the new server."
Seems simple, right?
I'm hitting all sorts of surprising data conversion errors. I used the export wizard to create the export package. This works fine. However using the same flat file definition, the import package fails -- even when I have no destination. That is I have just one data flow task that contains only one control: the Flat File source. When I run the package the flat file definition fails with data type conversion and truncation errors. One of the obvious errors is for boolean types. The SQL field is a bit, SSIS defined the column as DT_BOOL, the output of the data are literal text values "TRUE" and "FALSE". So SSIS converts a sql datatype of bit to "TRUE" and "FALSE" on export, but can't make the reverse conversion on import?
Does anyone else find this surprising? I would expect that what SSIS exports, it can import given all the same table and flat file definitions. Is SSIS the wrong tool to do such simple bulk copies? I'd like to avoid using BCP because this process will need to run automatically within SQL Agent so we can leverage all the error tracking and system monitoring.
I'm trying to use Excel in SSIS to import the data from spreadsheet to a staging table. The package runs well from the web server using SSMS. But when I deploy and try to execute the package, I'm getting the below error. I've a question, whether I've to install the AccessDatabaseEngine driver in SQL database server or the web server where I'm executing the SSIS?
Error: The requested OLE DB provider Microsoft.Jet.OLEDB.4.0 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode.
I have tried my file name vendor_insertFromFile2.sqlBULK INSERT (dbo.vendors VendorName, Street, City, Region, Country, PostalCode, Telephone, PortalId, Fax, Email, Website, CreatedDate, Unit, LastName, Cell) from 'D:AccessNAICSxPortal0_SampleData.txt'error messageServer: Msg 170, Level 15, State 1, Line 2Line 2: Incorrect syntax near '('.Data in the "CompanyName","Address","City","State","country","fullzip","FullPhone","Portal","Fullfax","Email","URL","date","ID","Contact_1","800No""DAVID STRAWN","460 FLOWERING TRL","GRAYSON","GA","United States","30017?,"770-277-8709",0,"“,"bkroom@bellsouth.net","WWW.BACKROOM4MEN.COM","09/02/05",1,"DAVID STRAWN","BRADRUSS","3353 S MAIN ST STE 130","SALT LAKE CITY","UT","United States","84115?,"801-975-0374",0,"“,"rusbrad@msn.com","WWW.BADANDNASTY.COM","09/02/05",2,"BRAD JENSEN",Thanks in advance on what is wrong or a link with working examples
I have several bcp output files I need to import into tables. I do not have format files for them. As far as I know they are in native format. I do not know the layout of the destination table they would populate.
1) how can I determine from the bcp file itself the schema of the destination table? Once I know that I should be able to import the data into the table.
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
I am working with the SP tuning. I want to know clearly about the temp tables.Instead of Select * into #table, it is always better to create temp table structure and insert the data later.Is it true in the case, if the data is small? insert into #table (select colmn1, column 2 from TableA join Table B on JoinC on joinD on..If they use several joins, in that situation, which way of creating temp table would be better?
Is there a way to change the order that "select * from #table" returns data? It doesn't appear to return data in the same order that it was inserted into the table. This wasn't a problem with the same code in SQL Server 2005.
it runs over night, pulls 10.5m rows. Inserts into a table, from a select (so "insert...select", rather than "select into"), from many tables, grouping on a max. It's complex. During the day, it runs fine - maybe 25 minutes. At night it *sometimes* runs fine, but then sometimes takes 4hours.Checking this morning there were 230 threads open for this one query. Checking sys.dm_os_tasks and sys.dm_os_waiting_tasks there were no other wait types on that session_id. None at all. Checking activity monitor, most of the existing threads were suspended on the insert.
There are 24 cores, but NUMA'd. We have maxdop on the server of 8. The maxdop option on the query is 12, just to speed up the select. Index and Stats refreshed daily. We've eight identical tempdb data files, on a separate spindle. Checking those, they are filling up using the round robin correctly
Would the delay be due to SQL trying to combine so many 'select' threads into one 'insert' thread (as it can't insert in parallel; 2014 can, apparently. Upgrades not available!)Should i change the SP to run the select into a temp table (table variable?) with a maxdop of 12, then do the insert into the actual table using a maxdop of 1. Checking the execution plans for a table variable implies the subtree cost comes down from 2.5m to 357k. What's best - temp table or table variable?
I try to modify a code to create a temp table but got an error.
Declare @headerid Int Declare @Providerid Int BELOW CODE IS WORKING: SELECT @headerid=HEADERID,@Providerid=PROVIDERID FROM CLAIM WITH(NOLOCK) WHERE EDI_CLM_NUM= 123
[code]...
Error: Incorrect syntax near '='. Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
I come from a web based world were loading 1.5 million records into a temp table is suicide. I’m doing more data warehouse stuff now and I was looking into optimizing a buddies proc and noticed he was loading 1.5 million records into a temp table. We had a discussion about it because being from a web world I was drastically against it. He on the other hand didn’t feel it was an issue being it gets called once maybe twice a day. The tempdb is set to autogrow and it is on a different drive than all the other databases on the box. It has one ldf and mdf. He’s creating an index on the table after load. Why we shouldn’t be loading 1.5 million recs into temp table?
I am trying to insert a single row in Temp table #InventoryItems . The temp table is mentioned in a curosor childcur_inventory. While looping through the cursor I have mentioned nested if else condition . In the Else condition where I have mentioned PRINT "Hello World" I want to insert a single row in the temp #InventoryItems. I trying to use Select Top 1 but the cursor is looping indefinitely trying to insert multiple record
I want to insert only one record with null values.
DECLARE childcur_inventory CURSOR FOR SELECT Structure_Number, State, Neighbor_State, Border_Bridge_Structure_Number FROM #InventoryItems OPEN childcur_inventory FETCH childcur_inventory INTO @Structure_Number, @State, @Neighbour_State, @Border_Bridge_Structure_Number
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,
Can we insert the data from #temp table to csv file using bcp in sql server ? I really appreciate if anyone can answer me this or any workarounds for this.
As far as I know temp tables/objects will be created inside the default filegroup of the partially contained database and not in tempdb. Is it possible to either define a set of files dedicated to temp objects or define a second partially contained database dedicated to temp objects like tempdb?
In a stored procedure I dynamically create a temp table by selecting the name of Applications from a regular table. Then I add a date column and add the last 12 months. See attachment.
So far so good. Now I want to update the data in columns by querying another regular table. Normally it would be something like:
UPDATE ##TempTable SET [columName] = (SELECT SUM(columName) FROM RegularTable WHERE FORMAT(RegularTable.Date,'MM/yyyy') = FORMAT(##TempMonths.x,'MM/yyyy'))
However, since I don't know what the name of the columns are at any given time, I need to do this dynamically.
how can I get the column names of a Temp table dynamically while doing an Update?
I could deploy across my environment, which is a mix of 2008R2/2012 servers, to give some information on log files. Running into a silly issue right off the bat. The table that DBCC LogInfo() conjures out of magic is different between the two. In 2012 it gained the RecoveryUnitID column. So I'm trying to write some logic to create a temp table based on which version is running. I would like to avoid a global temp table if possible. Here's what I've tried:
sp_executesql creates a table outside of the scope of my session: DECLARE @PrVers NVARCHAR(128) , @PrVersNum DECIMAL(10,2) , @StageTable NVARCHAR(1024) = N''
A view named "Viw_Labour_Cost_By_Service_Order_No" has been created and can be run successfully on the server. I want to import the data which draws from the view to a table using SQL Server Import and Export Wizard. However, when I run the wizard on the server, it gives me the following error message and stop on the step Setting Source Connection
Operation stopped...
- Initializing Data Flow Task (Success)
- Initializing Connections (Success)
- Setting SQL Command (Success) - Setting Source Connection (Error) Messages Error 0xc020801c: Source - Viw_Labour_Cost_By_Service_Order_No [1]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0014019. There may be error messages posted before this with more information on why the AcquireConnection method call failed. (SQL Server Import and Export Wizard)
Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)
- Setting Destination Connection (Stopped)
- Validating (Stopped)
- Prepare for Execute (Stopped)
- Pre-execute (Stopped)
- Executing (Stopped)
- Copying to [NAV_CSG].[dbo].[Report_Labour_Cost_By_Service_Order_No] (Stopped)
- Post-execute (Stopped)
Does anyone encounter this problem before and know what is happening?
