exec('bulk insert SCORPIO_STAGE_BULK_DATAPDCC from ''\shodbs29CDRData
onmech_stat_apd_clark_credit.dat'' with (formatfile = ''\dixdbs01ScorpioBulkDATAPDCC.fmt'')')
This is a stored proc with execute as a SQL user. It runs one bulk insert. The user bulk_insert_test_jcb does have BulkAdmin rights and if the user is logged in directly to the server, this works fine. If a SQL user is logged in and runs it (a user other than bulk_insert_test_jcb), this also works
However, if I run this as a windows user logged into the server
alter database stage_scorpio_bulk_jcb set trustworthy off
exec jason_test
--Msg 4834, Level 16, State 4, Procedure jason_test, Line 4
--You do not have permission to use the bulk load statement.
I expect this because the server-level permissions (bulk) are stripped off unless the database is trustworthy, so...
alter database stage_scorpio_bulk_jcb set trustworthy on
exec jason_test
--Msg 4861, Level 16, State 1, Procedure jason_test, Line 4
--Cannot bulk load because the file "\shodbs29CDRData
onmech_stat_apd_clark_credit.dat" could not be opened. Operating system error code 5(Access is denied.).
Why does this happen? I thought that, since I'm executing as a SQL user, SQL Server would authenticate over to the server with the datafiles as the service account, but I see the following in the log at SHODBS29
--User Logoff:
-- User Name: ANONYMOUS LOGON
-- Domain: NT AUTHORITY
-- Logon ID: (0x0,0x4C99BD2F)
-- Logon Type: 3
--
--
--For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Any ideas? It seems as if it is still trying to used windows authentication even though the stored proc is supposed to execute as a SQL user.
Someone in another forum said that ownership chaining not being allowed for bulk operations was the problem, but I don't think so, since if I put an "execute as user='bulk_insert_test_jcb'" into the exec string, it still fails with the same issue.
given a variable @requestID and @session ID, I need to move requests from a holding table to the request table using the generated request ID.
In a perfect world...
INSERT INTO Requests(ReqID, field1, field2) VALUES (SELECT @requestID AS RID, field1, field2 FROM holding WHERE session = '1234')
So that all 5 or so rows from the holding table having a session ID of 1234 get transfered to the request table using the variable value @requestid as the value satisfying ReqID.
I'm just learning SSIS and I've hit my first bump. I am doing a bulk import from a tab delimited text file to an empty sql table that has a Idendity column defined. How do I tell the bulk insert task to skip that column when inserting from the text file. If I remove the identity column it imports the data fine, but I want to create the indentity column in the table too.
Firstly, I am new to sql so please excuse my ignorance!
I have a stored procedure that tests the import of about 100 text files. This is to screen for file errors prior to processing. The results for each file are held in a table. The procedure script is as follows:
<snip>
insert into st_fileimport (dealernumber, handheld_file) values ('01', 'Fail')
BULK INSERT dbo.filetesthandheld FROM 'c:inetpubwwwrootS_Datadealers 1foo.csv' WITH ( FIELDTERMINATOR = ',' , ROWTERMINATOR = '' , ROWS_PER_BATCH = 500 )
update st_fileimport set handheld_file = 'Pass' where dealernumber = '01'
</snip>
This is then repeated for each file. This works fine, but as soon as an error is encountered the script terminates which means that you need to re-run this procedure iteratively until all files pass.
Is there a way of structuring the stored procedure so it will fail on one import and then move onto the next? Or, a better methodology all together?
hi all, lets say i have this insert command being executed from C# to a SQL Db. //store transaction log SqlCommand cmdStoreTrans = new SqlCommand("INSERT into Transactions(ImportID,ProfileID,RowID) values (@ImportID,@ProfileID,@RowID);",conn); cmdStoreTrans.Parameters.Add("@ImportID",importId); cmdStoreTrans.Parameters.Add("@ProfileID",profileId); cmdStoreTrans.Parameters.Add("@RowID",i); try { conn.Open(); cmdStoreTrans.ExecuteNonQuery(); conn.Close(); } catch(SqlException ex) { throw(ex); }I need the new Identity number of that record added. how can i get that within THIS Sqlcommand. Currently im closing the connection, creating a new command with 'SELECT MAX(id) from transactions" and getting the value that way, im sure there is a easier way keeping it all within one command object? someone mentioned using something liek @@Identity any help appreciatedTIA, mcm
Hi, I'm fairly new to strored procedures. what I need to do is create a new row with an INSERT specifying value A as a param ( so far no problem ) and value B as a value from table2. Is this possible? Thanks for your help Raif
I can't seem to get this stored proc to run properly. It won't let me insert records into it. Any suggestions? Can someone tell me if something doesn't look right? I've been over this a million times!
CREATE PROCEDURE TEST_PROC
/* My Name */
AS
BEGIN
SET NOCOUNT ON
DECLARE @MAVG2003 AS MONEY
SET @MAVG2003 = (SELECT AVG(PRICE) AS AVERAGE FROM ASSESSMENT.DBO.CUSTOMER C WITH (NOLOCK) LEFT JOIN ASSESSMENT.DBO.ORDER)HEADER OH WITH (NOLOCK) ON OH.CUSTOMER_ID = C.CUSTOMER_ID LEFT JOIN ASSESSMENT.DBO.ORDER_DETAIL OD WITH (NOLOCK) ON OD.ORDER_HEADER_ID = OH.ORDER_HEADER_ID LEFT JOIN ASSESSMENT.DBO.PRODUCTS P WITH (NOLOCK) ON P.PRODUCT_ID = OD.PRODUCT_ID WHERE DATEPART(YYYY, OH.ORDER_DATE) = '2003')
INSERT INTO #FORECAST SELECT C.CUSTOMER_ID, C.FIRST_NAME, C.LAST_NAME, TOTAL_2003 SUM(P.PRICE) AS TOTALYEAR, 0, 0 FROM ASSESSMENT.DBO.CUSTOMER C WITH (NOLOCK) LEFT JOIN ASSESSMENT.DBO.ORDER)HEADER OH WITH (NOLOCK) ON OH.CUSTOMER_ID = C.CUSTOMER_ID LEFT JOIN ASSESSMENT.DBO.ORDER_DETAIL OD WITH (NOLOCK) ON OD.ORDER_HEADER_ID = OH.ORDER_HEADER_ID LEFT JOIN ASSESSMENT.DBO.PRODUCTS P WITH (NOLOCK) ON P.PRODUCT_ID = OD.PRODUCT_ID WHERE DATEPART(YYYY, OH.ORDER_DATE) = '2003' GROUP BY C.CUSTOMER_ID
UPDATE #FORECAST SET FORECAST_2004 = TOTAL_2003 * (TOTAL_2003 / @MAVG2003)
UPDATE #FORECAST SET FORECAST_2005 = FORECAST_2004 + (FORECAST_2004 * .0105)
SELECT CUSTOMER_ID, TOTAL_2003, FORECAST_2004, FORECAST_2005 FROM #FORECAST ORDER BY TOTAL_2003 DESC
Hi, i'm an SQL newbie. I'm trying to figure out if there's an easy way to take a single field record set from a SELECT statement and then do an INSERT using that record set, all in one single Stored Procedure.
Here's what i tried to do, but this returns an error "The name "phonenumber" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.".
The common field in both SELECT and INSERT is phonenumber.
quote:PROCEDURE [dbo].[usp_select_and_insert]
@name varchar(20),
AS
SELECT phonenumber FROM USERLIST where OWNERNAME LIKE @name INSERT INTO LOGLOG (destination,content) values(phonenumber,'hello world');
GO
Hope that one of you can be kind enough to give me some guidance. Appreciate in advance. :)
Hi, How would I check if an insert via stored proc succeeded? Here's the proc I'm using:set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO -- ============================================= -- Author:<Author,,Name> -- Create date: <Create Date,,> -- Description:<Description,,> -- ============================================= ALTER PROCEDURE [dbo].[showtube_addNewSUser] -- Add the parameters for the stored procedure here @UserId uniqueidentifier, @FirstName nvarchar(32), @LastName nvarchar(32), @DescShort nvarchar(256), @DescLong ntext, @ClanID uniqueidentifier AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
-- Insert statements for procedure here Insert into dbo.Users (UserId, FirstName, LastName, DescShort, DescLong, ClanID) values (@UserId, @FirstName, @LastName, @DescShort, @DescLong, @ClanID); END
I tried adding a Return @@RowCount before the END statement, but it always seems to return -1. Could someone tell me what I'm doing wrong? Thanks.
Hello I need to write a proc to load data from txt files I receive into a table. It works fine when I specify bulk insert.... from 'myfilename.txt' BUT my filename will always change and I store it into a variable @filename
When I try to run the bulk insert instruction ... from @filename it doesn't work.. do you know why?
I have a stored procedure that dynamically bulk loads several tables from several text files. If I encounter an error bulk loading a table in the stored procedure, all I get is the last error code produced, but if I run the actual bulk load commands through SQL Management Studio, it gives much more usable errors, which can include the column that failed to load. We have tables that exceed 150 columns (don't ask), and having this information cuts troubleshooting load errors from hours down to minutes. Onto my question..., is there any way to capture all of the errors produced by the bulk load from within a stored procedure (see examples below)?
Running this...
BULK INSERT Customers
FROM 'c: estcustomers.txt'
WITH (TabLock, MaxErrors = 0, ErrorFile = 'c: estcustomers.txt.err')
Produces this (notice column name at the end of the first error)...
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 1 (CustId).
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)".
Running this (similar to code in my stored procedure)...
BEGIN TRY
BULK INSERT Customers
FROM 'c: estcustomers.txt'
WITH (TabLock, MaxErrors = 0, ErrorFile = 'c: estcustomers.txt.err')
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH
Produces something similar to this (which is useless)... ...Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
At my customer's site they get this error trying to run a stored procedure I wrote that does BULK INSERT.
-2147217900 [Microsoft ODBC SQL Server Driver][SQL Server] You do not have permission to use the bulk load statement. upImportFromICPMSRaw 'GSADC1CompanyInstrumentOutputFilesICPMSNew185367.csv', tblFromICPMSRaw
The customer has SQL Server 2008 R2 Express installed
The connection string to the database works on everything else and it is the sa account with password
On my own development system with SQL Server 2008 R2 Standard, it works perfectly OK.
I am working with a large application and am trying to track down a bug. I believe an error that occurs in the stored procedure isbubbling back up to the application and is causing the application not to run. Don't ask why, but we do not have some of the sourcecode that was used to build the application, so I am not able to trace into the code. So basically I want to examine the stored procedure. If I run the stored procedure through Query Analyzer, I get the following error message: Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74RAISERROR could not locate entry for error 60002 in sysmessages. (1 row(s) affected) (1 row(s) affected) I don't know if the error message is sufficient enough to cause the application from not running? Does anyone know? If the RAISERROR occursmdiway through the stored procedure, does the stored procedure terminate execution? Also, Is there a way to trace into a stored procedure through Query Analyzer? -------------------------------------------As a side note, below is a small portion of my stored proc where the error is being raised: SELECT @PortalPermissionValue = isnull(max(PermissionValue),0)FROM Permission, PermissionType, #GroupsWHERE Permission.ResourceId = @PortalIdAND Permission.PartyId = #Groups.PartyIdAND Permission.PermissionTypeId = PermissionType.PermissionTypeId IF @PortalPermissionValue = 0BEGIN RAISERROR (60002, 16, 1) return -3END
I have to update a field within a table of 60 records or so. Each record has a different field value. it's type varchar. i was given an excel file with the field values and was thinking of a bulk update like bulk insert, but i don't recall that it's possible that way.
Is the only way to create a table, bulk insert, then merge the two tables together with UPDATE?
Just wanted to see if there was an easier way to do it, otherwise i'll take the latter route. Thanks!
I have a table containing 8 million records. I need to replace 2 million of these records with a scaled down query that goes something like: SELECT 1, ShareholderID, Assets1 FROM MyTable (Yields appx. 200,000 recods) SELECT 2, ShareholderID, Assets2 FROM MyTable (Yields appx. 200,000 recods) . . . SELECT 10, ShareholderID, Assets1 + Assest2 + Assets3 + ... + Assets9 FROM MyTable (Yields appx. 200,000 recods)
Updates and cursors just seem to be too slow.
So far I have done the following, but was wondering if anyone could think of a better way. SELECT 6 million records that don't need to be deleted into a #TempTable Use statements above to select into same #TempTable DROP and recreate Original Table SELECT 6 + 2 million records INTO original table.
This seems rather convoluted. Is there a better approach? Would it be worth while to dump data to a file and use bcp / Bulk Insert
I receive the following error message when I try to use the Bulk Insert Task to load BCP data into a table:
Error: 0xC002F304 at Bulk Insert Task, Bulk Insert Task: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 4. Verify that the field terminator and row terminator are specified correctly.Bulk load data conversion error (overflow) for row 1, column 1 (rowno).".
Task failed: Bulk Insert Task
In SSMS I am able to issue the following command and the data loads into a TableName table with no error messages: BULK INSERT TableName FROM 'C:DataDbTableName.bcp' WITH (DATAFILETYPE='widenative');
What configuration is required for the Bulk Insert Task in SSIS to make the data load? BTW - the TableName.bcp file is bulk copy file as bcp widenative data type. The properties of the Bulk Insert Task are the following: DataFileType: DTSBulkInsert_DataFileType_WideNative RowTerminator: {CR}{LF}
Any help getting the bcp file to load would be appreciated. Let me know if you require any other information, thanks for all your help. Paul
I am having trouble executing a stored procedure on a remote server. On my local server, I have a linked server setup as follows: Server1.abcd.myserver.comSQLServer2005,1563
This works fine on my local server:
Select * From [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.dbo.TableName
This does not work (Attempting to execute a remote stored proc named 'Data_Add':
When I attempt to run the above, I get the following error: Could not locate entry in sysdatabases for database 'Server1.abcd.myserver.comSQLServer2005,1563'. No entry found with that name. Make sure that the name is entered correctly.
Could anyone shed some light on what I need to do to get this to work?
Hi All,Quick question, I have always heard it best practice to check for exist, ifso, drop, then create the proc. I just wanted to know why that's a bestpractice. I am trying to put that theory in place at my work, but they areasking for a good reason to do this before actually implementing. All Icould think of was that so when you're creating a proc you won't get anerror if the procedure already exists, but doesn't it also have to do withCompilation and perhaps Execution. Does anyone have a good argument fordoing stored procs this way? All feedback is appreciated.TIA,~CK
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')
I have an ASP that has been working fine for several months, but itsuddenly broke. I wonder if windows update has installed some securitypatch that is causing it.The problem is that I am calling a stored procedure via an ASP(classic, not .NET) , but nothing happens. The procedure doesn't work,and I don't get any error messages.I've tried dropping and re-creating the user and permissions, to noavail. If it was a permissions problem, there would be an errormessage. I trace the calls in Profiler, and it has no complaints. Thedatabase is getting the stored proc call.I finally got it to work again, but this is not a viable solution forour production environment:1. response.write the SQL call to the stored procedure from the ASPand copy the text to the clipboard.2. log in to QueryAnalyzer using the same user as used by the ASP.3. paste and run the SQL call to the stored proc in query analyzer.After I have done this, it not only works in Query Analyzer, but thenthe ASP works too. It continues to work, even after I reboot themachine. This is truly bizzare and has us stumped. My hunch is thatwindows update installed something that has created this issue, but Ihave not been able to track it down.
Before implementing memory based bulk copy insert with IRowsetFastLoad interface of SQL Server 2005 OLE DB provider, I want to know some considerations.
- performance : compared with T-SQL's "BULK INSERT ..." and bcp utility
- SQL Server's resource usage : when running memory based bulk copy, server resource's influence
- server side action(behavior) : when server is busy, delayed-update means IRowsetFastLoad::Commit(true) method can insert right after?
- row-count : The rowcount limitation can be inserted by IRowsetFastLoad::InsertRow() method before IRowsetFastLoad::Commit
I have a web form with a text field that needs to take in as much as the user decides to type and insert it into an nvarchar(max) field in the database behind. I've tried using the new .write() method in my update statement, but it cuts off the text after a while. Is there a way to insert/update in SQL 2005 this without resorting to Bulk Insert? It bloats the transaction log and turning the logging off requires a call to sp_dboptions (or a straight-up ALTER DATABASE), which I'd like to avoid if I can.
I have a stored procedure "uspX" that calls another stored procedure "uspY" and I need to retrieve the return value from uspY and use it within uspX. Does anyone know the syntax for this?
I have about 5 stored procedures that, among other things, execute exactly the same SELECT statement
Instead of copying the SELECT statement 5 times, I'd like each stored proc to call a single stored proc that executes the SELECT statement and returns the resultset to the calling stored proc
The SELECT statement in question retrieves a single row from a table containing 10 columns.
Is there a way for a stored proc to call another stored proc and gain access to the resultset of the called stored proc?
I know about stored proc return values and about output parameters, but I think I am looking for something different.
I would like to know if the following is possible/permissible:
myCLRstoredproc (or some C# stored proc) { //call some T SQL stored procedure spSQL and get the result set here to work with
INSERT INTO #tmpCLR EXECUTE spSQL }
spSQL (
INSERT INTO #tmpABC EXECUTE spSQL2 )
spSQL2 ( // some other t-sql stored proc )
Can we do that? I know that doing this in SQL server would throw (nested EXECUTE not allowed). I dont want to go re-writing the spSQL in C# again, I just want to get whatever spSQL returns and then work with the result set to do row-level computations, thereby avoiding to use cursors in spSQL.
Hello, I am wondering is the Transaction Log logged differently between BULK INSERT vs INSERT? Performance speaking, which operations is generally faster given the same amout of data inserted.
Hi~, I have 3 questions about memory based bulk copy.
1. What is the limitation count of IRowsetFastLoad::InsertRow() method before IRowsetFastLoad::Commit(true)? For example, how much insert row at below sample?(the max value of nCount) for(i=0 ; i<nCount ; i++) { pIFastLoad->InsertRow(hAccessor, (void*)(&BulkData)); }
2. In above code sample, isn't there method of inserting prepared array at once directly(BulkData array, not for loop)
3. In OLE DB memory based bulk copy, what is the equivalent of below's T-SQL bulk copy option ? BULK INSERT database_name.schema_name.table_name FROM 'data_file' WITH (ROWS_PER_BATCH = rows_per_batch, TABLOCK);
------------------------------------------------------- My solution is like this. Is it correct?
// CoCreateInstance(...); // Data source // Create session
