For Bulk Load requests in SQL server, Are there any specific profiler event? Like the one we have for RPC RPC:Starting and for Batch Requests, we have SQL:BatchStarting.
Are Bulk Load requests that are being monitored through Profiler captured as SQL:Batch... events at the backend?
Are there any new features added in 2012 or 2014 to identify a Bulk request submitted through bcp.exe utility or any other sqlbulkcopy program?
I have a VB.NET scheduled job as a task scheduler ( windows 2012) that calls a stored procedure that bulk inserts. I have added the user in the server role "bulkadmin" yet I get the "You do not have permission to use the bulk load statement error"
System.Data.SqlClient.SqlException (0x80131904): You do not have permission to use the bulk load statement.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream,
[code]....
Is there a switch I can use to force a bulk insert and if data is truncated, I'm good with that. The truncated data, in this case, is not data I can use anyway if it is long enough to be truncated.
I need to keep the field at VARCHAR(23) and if I expand it, I won't be able to join on it after the file load completes. I'd like the data to be inserted (truncated if need be) and then I'll deal with the records that are truncated after I load the file.
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] .....
Im having some issues with bulk insert.
This is the table:
CREATE TABLE [dbo].[tmp_GA_status](
[GA_recno] [int] NOT NULL,
[GA_desc] [varchar](40) NULL
)
This is the file (unicode):
1|"test1"
2|"test2"
3|"test3"
4|"test4"
5|"test5"
6|"test6"
7|"test7"
8|"test8"
and this is the sql:
bulk insert tmp_GA_status from 'C: empTextDumpGA_status.dta'
with (CODEPAGE='RAW', FIELDTERMINATOR='|', ROWTERMINATOR='
', DATAFILETYPE='widechar')
so yeah, pretty simple. But whatever I do I get this;
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 2 (GA_desc).
So what am I doing wrong ?
I am looking for a way to log all events that occur on the server. I have used quary profier and that gets me the info i need but is there a way to truncate the file, say only log the last 30 minutes of activity and having it rolling so that it is always the last 30 minutes. Or is there some other software that will do this. The database times out at random times and I am trying to fix the problem but dont want to keep houndreds of log files that query profiler will create by leaving a trace running 24/7. Any suggestions.
Thanks
Mark
I have discovered trace output in MSSQLDATAMSSQL.1MSSQLLOG that I have not kicked off. It is at various times and limited to 20MB. So that tells me a server event is kicking off a pre-defined trace. The trace contains mostly hash warnings and sort warnings.
I have looked through my Agent Jobs, Agent Alerts, and perfmon and don't find anything that is set up to kick off a trace under a specified condition.
I have checked the job activity, SQL error logs, SQL server logs, and the server's event viewer for any odd events or event times that correlate with the times of the traces.
I have checked each database's sys.sql_modules for a definition containing '%sp_trace%'.
Where else can I check to find what would be triggering these traces?
Our app logins don't have permissions high enough to run traces, I verified:
You do not have permission to run 'SP_TRACE_CREATE'
I am the DBA, not a .NET programmer -- so I am lacking experience if there's anything on the .NET side.
This is SQL 2005 64-bit running active/passive on a Win2003 clustered pair.
I'm trying to use Bulk insert for the first time and getting the following error. I think it might have something to do with my Format File and from the error msg there's a conversion error for the first column. In my database the Field is nvarchar(6) so my best guess is to use SQLNChar for the first column. I've checked the end of each line is CR LF therefore the is correct for line 7 right?
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 1, column 1 (ASXCode).
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" 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 "BULK" for linked server "(null)".
BULK
INSERTtbl_ASX_Data_temp
FROM
'M:DataASXImportTest.txt'
WITH
(FORMATFILE='M:DataASXSQLFormatImport.Fmt')
[code]...
I have numerous jobs that use the Bulk Load object to transfer data. Once or twice a day, one of the jobs will fail with the folowing error:
Error: 0xC0202009 at Data Flow Task, SQL Server Destination [73]: An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'GlobalDTSQLIMPORT ' could not be opened. Operating system error code 8(Not enough storage is available to process this command.). Make sure you are accessing a local server via Windows security.".
Error: 0xC0202071 at Data Flow Task, SQL Server Destination [73]: Unable to prepare the SSIS bulk insert for data insertion.
After receiving this error, any job that uses the bulk load object that attempts to run will fail with the same error. After restarting the SQL Server service, all jobs will run ok.
I can find virtually nothing on this particular error so if you have seen it before please let me know. This has become a maintenance nightmare!
Hi All,
Im bulk loading a ton of data into MSSQL SERVER 2005 Standard Edition. I used to do this process in version 2000. It seems there is some more overhead in 2005. Is there a way to drop logging to almost null to speed up insert?
This is my current sql statment to load data.
EXEC sp_dboption 'my_stuff', 'select into/bulkcopy', 'true'
SET ANSI_WARNINGS OFF
BULK INSERT mystuff.dbo.[v1]
FROM 'c:myfile.txt'
WITH
(
FIRSTROW = 1,
FORMATFILE = 'c:scriptsv1.fmt',
MAXERRORS=2000,
ROWS_PER_BATCH=100000
)
Thanks,
Mike
hi all,
I am getting this error when I try to insert an image or update the database image. I am the owner of the database. I am trying to insert or update using an ASP.Net form (published) from a remote computer. Recently I changed a few permissions (added a user with login name and password) to that database to allow Reporting services access to all my group employees. Earlier everything was working absolutely fine. But now it is denying permissions when I try to insert or update the data when I access it from a remote system. I am able to get the data incase I do a get using the ID. But if I start the application on the localhost (Start Debugging) then its working fine and i am able to insert the data. Can someone suggest what could be wrong?
Check it out!!!! There is an exception!!!!! System.Data.SqlClient.SqlException: You do not have permission to use the bulk load statement. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at _Default.Insert_Method()
Thanks
Is there a way to use the script file from one database to load all the stored procedures into another database? I have used DTS to do them one at a time since the SQL task is very limited in the amount of text that it will handle. There has to be a way to use a script filel with all of the procedures in it to load a new database. Any suggestions are appreciated.
View 2 Replies View Relatedi have the stored procedure...
ALTER procedure [dbo].[xmlpaths]
as
Declare @xml VARCHAR(MAX)
Declare @i as int
select @xml=BulkColumn from openrowset(bulk 'C:Documents and SettingsKasiDesktopote.xml', single_clob)as cse
EXEC sp_xml_preparedocument @i OUTPUT,@xml
Select * From OpenXML(@i,'/college/cse',2) With (name varchar(50), rollno int, year int)
i got the output...
but i want to give path as parameter during execution of procedure name. can anyone help me..
thanks in advance
I have a file which has * as the field delimiter and ~ as the record delimiter, but I don't know how much columns each row will have. Only known is the maximum which can be 15.
The file looks something like: A1*A2*A3*~B1*B2~C1*C2*C3*C4*C5~
SO I have created a table with 15 columns(since 15 can be the max) but now when I try to insert it to that table, I inserts only the entire file into 1 single column.
The command which I am using is: BULK INSERT tablename FROM filename WITH (FIELDTERMINATOR = '*' , ROWTERMINATOR = '~')
but this is not giving the correct output.
The output expected is
A1 A2 A3
B1 B2
C1 C2 C3 C4 C5
I am trying to configure a bulk table upload from a 2003 Access table to a matching table in SQL with SSIS. I can configure the source file but am unable to configure the destination. When I configure the SQL source and use SQL Native Client I get an error message of:
The selected connection manager uses an earlier version of a SQL server provider. Bulk insert requires a connection that uses a SQL server 2005 provider.
When I go through the new connection setup I don't see any available provider named like that. I believe the SQL server I am loading to is a 2003 version.
I need to execute the following:
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
before I do a bulk load because the table I am inserting into has an indexed view created on it. Whats the best way to set these options prior to a bulk load?
I need to update a number of sql server tables, the data sources for these coming from a number of stored procedures. I want a generic way of getting the data and then passing this data to the tables.I am thinking of doing this for each table:Populating a datasetWriting this dataset to XMLUsing SQLXML Bulk Load to pass this XML to the database to updateI can create the xml data file by:
dataset.WriteXml("C:data.xml")The problem I have is that the example (http://support.microsoft.com/default.aspx/kb/316005/en-us) I looked at relies on the schema being defined:<?xml version="1.0" ?><Schema xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:xml:datatypes" xmlns:sql="urn:schemas-microsoft-com:xml-sql" > <ElementType name="CustomerId" dt:type="int" /> <ElementType name="CompanyName" dt:type="string" /> <ElementType name="City" dt:type="string" /> <ElementType name="ROOT" sql:is-constant="1"> <element type="Customers" /> </ElementType> <ElementType name="Customers" sql:relation="Customer"> <element type="CustomerId" sql:field="CustomerId" /> <element type="CompanyName" sql:field="CompanyName" /> <element type="City" sql:field="City" /> </ElementType></Schema>Is there any way I can create the schema 'on the fly' similar to how I did for the data source file.As I could then pass these files to the database:objBL.Execute ("schema.xml","data.xml");
Is that possible to load files (*.bmp, *.jpg etc) to table (field type IMAGE) using BULK INSERT?
Or is it better to do it otherwise?
Thanks
hello,
I am working on an application that will import data from ascii tab-delimited files into corresponding tables in a sql server 2005 express db.
The problem I am facing is that i get errors when running bulk insert.
The tables all have one extra column which is a primary key identity value.
Additionaly, the column data types include:
int, bigint, nchar, nvarchar, datetime and bit
an example table looks like this:
CREATE TABLE [dbo].[counties](
[id] [int] IDENTITY(1,1) NOT NULL,
[InternalID] [int] NULL,
[Active] [bit] NULL,
[Code] [nchar](10) COLLATE Greek_CI_AS NULL,
[Description] [nvarchar](50) COLLATE Greek_CI_AS NULL,
[StartDate] [datetime] NULL
)
An example data file looks like this:
InternalIDActiveCodeDescriptionDate
1 101Αι�„�‰Î»Î¿Î±ÎºÎ±��ν16/11/1909
2 102Α��γολίδο�‚29/04/1949
3 103Α��καδία�‚16/11/1909
4 104Ά���„η�‚ 16/11/1909
5 105Α�„�„ική�‚ 26/07/1943
So, what I do is:
1. for each table I generate a character format file with the following command:
bcp mydb..table format nul -f tableformat.fmt -c -T -S hostsqlexpress
2. I modify the format file to exclude the first identity column by zeroing the field length, the column order and terminator. The resulting format file looks like this:
9.0
6
1 SQLCHAR 0 0 "" 0 id ""
2 SQLCHAR 0 12 " " 2 InternalID ""
3 SQLCHAR 0 3 " " 3 Active ""
4 SQLCHAR 0 20 " " 4 Code Greek_CI_AS
5 SQLCHAR 0 100 " " 5 Description Greek_CI_AS
6 SQLCHAR 0 24 " " 6 StartDate ""
3. I run BULK INSERT
BULK INSERT tablename
FROM dataFile
WITH (
FIRSTROW=2,
FORMATFILE = formatFile
DATAFILETYPE = 'char'
FIELDTERMINATOR=' '
ROWTERMINATOR='
'
KEEPNULLS
)
As a result of the above configuration I get this:
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 4, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 5, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 6, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 7, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 8, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 9, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 10, column 6 (EndDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 11, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 12, column 6 (EndDate).
Msg 4865, Level 16, State 1, Line 1
Cannot bulk load because the maximum number of errors (10) was exceeded.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" 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 "BULK" for linked server "(null)".
What am I doing wrong here? With previous configurations I got errors about the BIT fields. In general, I only receive errors... The only thing that worked was with a table that only had nvarchar. Is there a fundamental mistake in what i do? I have read many posts, but nothing specific about handling different data types with bcp and bulk insert.
I would appreciate any help, as I am running out of time
Thank you.
Dimitris Chrysomallis
Hello,
I am working on an application that will import data from ascii tab-delimited files into corresponding tables in a sql server 2005 express db.
The problem I am facing is that i get errors when running bulk insert.
The tables all have one extra column which is a primary key identity value.
Additionaly, the column data types include:
int, bigint, nchar, nvarchar, datetime and bit
an example table looks like this:
CREATE TABLE [dbo].[counties](
[id] [int] IDENTITY(1,1) NOT NULL,
[InternalID] [int] NULL,
[Active] [bit] NULL,
[Code] [nchar](10) COLLATE Greek_CI_AS NULL,
[Description] [nvarchar](50) COLLATE Greek_CI_AS NULL,
[StartDate] [datetime] NULL
)
An example data file looks like this:
InternalIDActiveCodeDescriptionDate
1 101Αι�„�‰Î»Î¿Î±ÎºÎ±��ν16/11/1909
2 102Α��γολίδο�‚29/04/1949
3 103Α��καδία�‚16/11/1909
4 104Ά���„η�‚ 16/11/1909
5 105Α�„�„ική�‚ 26/07/1943
So, what I do is:
1. for each table I generate a character format file with the following command:
bcp mydb..table format nul -f tableformat.fmt -c -T -S hostsqlexpress
2. I modify the format file to exclude the first identity column by zeroing the field length, the column order and terminator. The resulting format file looks like this:
9.0
6
1 SQLCHAR 0 0 "" 0 id ""
2 SQLCHAR 0 12 " " 2 InternalID ""
3 SQLCHAR 0 3 " " 3 Active ""
4 SQLCHAR 0 20 " " 4 Code Greek_CI_AS
5 SQLCHAR 0 100 " " 5 Description Greek_CI_AS
6 SQLCHAR 0 24 " " 6 StartDate ""
3. I run BULK INSERT
BULK INSERT tablename
FROM dataFile
WITH (
FIRSTROW=2,
FORMATFILE = formatFile,
DATAFILETYPE = 'char',
FIELDTERMINATOR=' ',
ROWTERMINATOR='
',
KEEPNULLS
)
As a result of the above configuration I get this:
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 4, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 5, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 6, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 7, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 8, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 9, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 10, column 6 (EndDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 11, column 5 (StartDate).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 12, column 6 (EndDate).
Msg 4865, Level 16, State 1, Line 1
Cannot bulk load because the maximum number of errors (10) was exceeded.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" 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 "BULK" for linked server "(null)".
What am I doing wrong here? With previous configurations I got errors about the BIT fields. In general, I only receive errors... The only thing that worked was with a table that only had nvarchar. Is there a fundamental mistake in what i do? I have read many posts, but nothing specific about handling different data types with bcp and bulk insert.
I would appreciate any help, as I am running out of time
Thank you.
Dimitris Chrysomallis
I have a Bulk insert that doesn't load but doesn't error,
SET @SQL= 'BULK INSERT dbo.LexisNexis_import_BANKRUPTCY FROM ''' + @ImportFile + ''' WITH (FIRSTROW = 2, FORMATFILE = ''' + @FormatFilePath + ''' )'
EXEC(@SQL)
All columns in the csv are double quoted so I stip them out in a format file.There is data in the source file. Why this Isn't working?
Hi All,I have an asp.net 2.0 app that needs to bulk load data from an xml file into a Sql Server (Express) table. Is there an easy way to do this?Thanks,Claude.
View 3 Replies View RelatedI want to import the contents of a remote database every morning via a Web service to populate a data mart. I am looking for the most efficient way to do this. Step 1 is to use the Web Service Task in SSIS to grab the data and save to an XML file (since the content is far too large to store in the String data type).
For step 2, I can either use the SQLXMLBulkLoad object in VB Script to read in the file and populate the tables. Or I can using the XML Source object in Integration Services. It is clear that SQLXMLBulkLoad is the most efficient way to load data, but is the XML Source object in SSIS just a graphical representation of the SQLXMLBulkLoad object, or is it something else entirely that is inefficient because it requires loading of the XML content entirely into RAM before it can process the XML?
Any other suggestions are welcome.
SQL Server 2005 SP2.
Thank you,
Mike Chabot
Hi,
I am new to SSIS but i have avg working knowledge in sql.
My problem is as follows ,I have a text pipe dilimited file in some folder and the number of columns and the name of the column is not consistant. It can have N number of column and it can have any column names. I need to load this text file data into a sql table. All that i want is to load this file to SQL Database with some temp name. Once i get the table in SQL Database, i can match the column names of both taget table and this temp table and only push those column which matches with the target table. For this i can frame Dynamic SQL. This part is clear to me.
Now the problem is , I developed a SSIS pacakge to push the text file to SQL Table. I am able to do this. But if i change the column names or added new column SSIS is not able to push the new columns. Is this functionality available in SSIS, is it can be dynamic like this?
I hope i am clear with my prob... if need any clarification please let me know
thanks in advance
Mike
I have a dataflow step (flat file -> Sql Server Destination), with a batch size of 2500 records. It fails consistently around 3.6 million records in, with only this error -
[SQL Server Destination [4076]] Error: Unable to prepare the SSIS bulk insert for data insertion.
[DTS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "SQL Server Destination" (4076) failed with error code 0xC0202071. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
[DTS.Pipeline] Error: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0202071. There may be error messages posted before this with more information on why the thread has exited.
[Flat File Source [1]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
[DTS.Pipeline] Error: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.
[DTS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Flat File Source" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
[DTS.Pipeline] Error: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread1" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.
[DTS.Pipeline] Error: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
How can I debug this further to see what's going wrong?
SELECT from openrowset(BULKÂ 'SERVERNAMEsomepathsomefile.csv'... fails while SELECT from openrowset(BULK 'c:somepathsomefile.csv' ... works.
I am running the task as a specific sql server user. If I run the same query in management studio using execute as login='batchuser', it also works for any path.
How can I make this work without an extra step moving the data to the local server? Because that would cause extra administration.
We have some Deadlock alerts set up in SQL Agent that email us when the performance counter for deadlocks goes above zero. I've used the following script to identify the event file which has deadlock information in there.
select CAST(target_data as xml) as TargetData
from sys.dm_xe_session_targets st
join sys.dm_xe_sessions s on s.address = st.event_session_address
where name = 'system_health'
Now that is fine, and we're looking into that (number of deadlocks appear to be 0.5) but out of interest ran a SQL Profiler session to capture the details as well and nothing is showing, I've received a few alerts and the trace file has information in there - but profiler shows absolutely nothing (all deadlock events are captured)
I can't use DTS nor DTSwizard as I need to put it in a .sql and run it through a command line via .bat file (it's more for the users).
Each row ends with an EOL character, the fields are all fixed width, but I have a little problem here, some rows are empty but just with a EOL character.
How shall I go about it?
many thanks! :D
Is there a way to setup a trace to show only direct TSQL statements triggered on my server? note I don't want to capture Procedure calls or the statements called within the procs.
Actually many people are firing direct SQL statements on server. And some are coming from entity framework as well. I just want to capture those.
I am facing a issue with bulk upload on Test Server.
Issue: When running Openrowset command from SQL server other that Test Server query runs fine when trying to run the same command from Test Server it gives error.
Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "\ServerNameinputFileName.csv" could not be opened. Operating system error code 5(Access is denied.).
For example: If the command is run from System A connecting to SQL Server instance on Test Server Test Server it gives this error. If the same command with same rights is run from any other SQL server instance say Dev1 its running fine.
If the command is run from Test Server connecting to any SQL Server instance including Test Server it is running fine.
Tried: 1) Given the read/write rights on shared folder, to user under which the SQL server service is running on Test Server
2) Given the read/write rights on shared folder to everyone.
Query:
SELECT DISTINCT * FROM OPENROWSET
(
BULK '\ServerNameinputFileName.csv',
FORMATFILE='\ServerNameFormat.xml'
)
AS FileList
Please provide me with some solution. What can be the reason for such behaviour?
I am attempting to create a new trace but I get the following error message: "failed to start a new trace".
I have been doing some digging and as I understand it, I had to find the directory Profiler uses for temporary files. So, I typed the following in the command window "SET TMP" and I received the following reply:
C:UsersRossAppDataLocalTemp
Now, according to the forum: [URL] ...
I am supposed to check that the system folder pointed to by the TMP environment variable exists and is not crammed with files.
Well, when I went to the directory C:UsersRossAppDataLocalTemp, it is indeed full of both files and directories. The size is 16.3 MB and has 133 files and 63 folders.
When I had a look at the Environment Variables window and chose TMP the value is "%USERPROFILE%AppDataLocalTemp" which according to my limited understanding is the equivalent to C:UsersRossAppDataLocalTemp.
So, what I am wondering is am I supposed to totally clear out this directory? I am not too keen on doing this because I don't want to stuff my PC up.
Set up a trace with the events RPC:Completed, SQL:BatchCompleted, SQL:BatchStarting, and SQL:StmtCompleted.
When I issue the statement: SELECT * FROM XyzView there is nothing captured in Profiler. If I script out the view and then execute the select statement that defines the view, it does show up in Profiler.
I've tried adding a lot of the other events, i.e. SP:StmtCompleted and the various other StmtStarting events and the trace still does not capture anything.
Am I capturing the wrong events or is this known behavior? My goal is to see what the overhead is for using a view versus persisting the results of the view as a table and referencing that instead. The view in question is against static data, joins 9 tables, and is referenced a lot.
I can use the stats generated when I execute the select that defines the view but I still find this to be curious behavior so I assume I'm doing something wrong.
which have a lot of impact to database performace since it do not fit onto memory and spill over to disks.
The question is when i ran a profiler and tried to catch a sort warning i cannot find the query which cause the alert.