Bulk Insert Permissions For Temp Tables

Feb 21, 2001

Does anyone know what permissions are required to run bulk insert to a temp table?

I've got a procedure that creates a temp table and runs bulk insert on it. Only problem is that it seems that only the dbo can run it. Anyone else gets the following error:
"The current user is not the database or object owner of table '#bulk'. Cannot perform SET operation."

Alternatively, does anyone know how to submit a scheduled job as a different user?

I've got my import system set up to create a scheduled job that kicks off right away. The job runs the 1st step which includes a bulk insert. The second step checks if the first step completely failed or not. Works great except that when the user runs the submission procedure, it comes back with that error.

Microsoft's brain-dead bulk insert command....aaargh. Every method around it's design flaws is blocked by another design flaw.

View 1 Replies


ADVERTISEMENT

Access Permissions To Bulk Insert On # Temp Tables

Oct 24, 2007



Hi ,

I am a newbie and i need to provide access for developer for him to use bulk insert ... on temp tables.
what permission do i need to provide the developer i cannot provide bulkadmin permission to him what are the other ways to provide him the access.

Please help me with this.

View 4 Replies View Related

Permissions For BULK INSERT?

Apr 21, 2006

I'm trying to do a BULK INSERT into a SQL Server 2005 db from an aspx page. When I try to run the page, to insert into [myTable], I get:The current user is not the database or object owner of table 'myTable'. Cannot perform SET operation.But here's the kicker: if I use Server Management Studio to log into the same database, using the same credentials, and run the same query, it works fine.My connection string looks like this:connectionString="data source=myLocalMachine; initial catalog=myCatalog;
user=joe; password=joe;" providerName="System.Data.SqlClient"Can anybody tell me why it works when I log in via SMS, but not when I try the same query from my aspx? I'm totally stuck.Thanks!

View 1 Replies View Related

Bulk Insert And Execute As Permissions

May 9, 2008

Hello,

Consider the following:


create procedure jason_test

as

bulk insert SCORPIO_STAGE_BULK_DATAPDCC from 'd:BulkTestonmech_stat_apd_clark_credit.dat' with (formatfile = 'd:BulkTestDATAPDCC.fmt')

go



alter procedure jason_test_exec

with execute as 'bulk_insert_test_jcb'

as

bulk insert SCORPIO_STAGE_BULK_DATAPDCC from 'd:BulkTestonmech_stat_apd_clark_credit.dat' with (formatfile = 'd:BulkTestDATAPDCC.fmt')

go



Then, log into SQL Server via management stuido as the SQL user "bulk_insert_test_jcb" this user has server-level bulk admin rights and execute rights on both of these stored procs:


exec jason_test
This works


exec jason_test_exec
gives:

Msg 4834, Level 16, State 1, Procedure jason_test_exec, Line 4

You do not have permission to use the bulk load statement.

Can you help me with this? Why is the user prevented from running this bulk insert inside the stored proc with "execute as" ? The profiler trace from both of these stored procs have identical results for the SP: StmtStarting event.

Thanks!
Jason


View 4 Replies View Related

BULK Insert Permissions Question

Dec 7, 2006

To run a BULK INSERT, Sql BOL says I need to have

"INSERT and ADMINISTER BULK OPERATIONS permissions..."

If I am dbo of a database is that sufficient (rights wise) to bulk insert into that database?



TIA,



Barkingdog



View 1 Replies View Related

Transact SQL :: How To Do Bulk Insert Into Temp Table From Text File

Sep 15, 2015

How do I do a bulk insert into a temp table from a text file. Text file looks like that:
 
ver_id TYPE
E57AB326-803C-436E-B491-398A255C919A 58
34D2A601-6BBA-46B1-84E1-3A805FDA3812 58
986E140C-62F1-48F1-B428-3571EBF00DA0 58

My statement looks like this:

CREATE TABLE [dbo].[tblTemp]([ver_id]  [nvarchar](255), [TYPE] [smallint]) 
GO
BULK INSERT [dbo].[tblTemp]
FROM 'C:v2.txt'
I keep receiving errors.

The error I receive is: Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 2 (TYPE).

View 2 Replies View Related

Select From Multiple Tables, Insert In Temp Table

Feb 18, 2004

What's the best way to go about inserting data from several tables that all contain the same type of data I want to store (employeeID, employerID, date.. etc) into a temp table based on a select query that filters each table's data?


Any ideas?

Thanks in advance.

View 6 Replies View Related

Bulk Sql Insert Task Can Do Tables With Identity?

Mar 23, 2006

 

 

 i got some bulk insert tasks in SSIS inserting into some tables with identity set ON....

in 1 column. Can the bulk insert task go smoothly?

 PS: i cannot find anywhere in the bulk insert task that can set the ignore identity columns...........

 

 

now my steps are prepare database -> create database -> bulk insert into tables

working on my previous problem, Jamie.

View 1 Replies View Related

DB Engine :: Bulk Update Is Recorded As Delete And Insert In CDC Tables?

Nov 18, 2015

I have a fundamental problem with how CDC works for bulk updates.When CDC enabled table is updated for single row - My CDC system tables its recording it as update (3 & 4)  which is perfect and what it should be. No Complains!But when I do a bulk update in the same CDC enabled tables for the same columns - My CDC system tables its recording as delete and then insert (1 & 2). This is not correct and this is what my problem is.  We used triggers before CDC we did not face this problem with triggers every thing was fine with triggers other than performance.The way how the CDC  is handling the bulk update is  a big problem for me because based on the output of CDC system tables we are doing some migration work to legacy system.

It will be impossible  for me to go and change my migration logic scripts because we have 100's or procedures in it.Is it a know problem with CDC? Is there any solution in CDC when a bulk update happens on a table the CDC system tables record it as updates. I don't think CDC 'net changes' in this situation because the net change would show as single inserted row.If this can't be done with CDC then I have to completely abandon CDC and go back to triggers..

View 5 Replies View Related

How Do You Use An Identity Column When Doing A Bulk Insert Using The Bulk Insert Task Editor

Apr 18, 2008



Hello,

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.

Thanks.

View 8 Replies View Related

A Curious Error Message, Local Temp Vs. Global Temp Tables?!?!?

Nov 17, 2004

Hi all,

Looking at BOL for temp tables help, I discover that a local temp table (I want to only have life within my stored proc) SHOULD be visible to all (child) stored procs called by the papa stored proc.

However, the following code works just peachy when I use a GLOBAL temp table (i.e., ##MyTempTbl) but fails when I use a local temp table (i.e., #MyTempTable). Through trial and error, and careful weeding efforts, I know that the error I get on the local version is coming from the xp_sendmail call. The error I get is: ODBC error 208 (42S02) Invalid object name '#MyTempTbl'.

Here is the code that works:SET NOCOUNT ON

CREATE TABLE ##MyTempTbl (SeqNo int identity, MyWords varchar(1000))
INSERT ##MyTempTbl values ('Put your long message here.')
INSERT ##MyTempTbl values ('Put your second long message here.')
INSERT ##MyTempTbl values ('put your really, really LONG message (yeah, every guy says his message is the longest...whatever!')
DECLARE @cmd varchar(256)
DECLARE @LargestEventSize int
DECLARE @Width int, @Msg varchar(128)
SELECT @LargestEventSize = Max(Len(MyWords))
FROM ##MyTempTbl

SET @cmd = 'SELECT Cast(MyWords AS varchar(' +
CONVERT(varchar(5), @LargestEventSize) +
')) FROM ##MyTempTbl order by SeqNo'
SET @Width = @LargestEventSize + 1
SET @Msg = 'Here is the junk you asked about' + CHAR(13) + '----------------------------'
EXECUTE Master.dbo.xp_sendmail
'YoMama@WhoKnows.com',
@query = @cmd,
@no_header= 'TRUE',
@width = @Width,
@dbuse = 'MyDB',
@subject='none of your darn business',
@message= @Msg
DROP TABLE ##MyTempTbl

The only thing I change to make it fail is the table name, change it from ##MyTempTbl to #MyTempTbl, and it dashes the email hopes of the stored procedure upon the jagged rocks of electronic despair.

Any insight anyone? Or is BOL just full of...well..."stuff"?

View 2 Replies View Related

Bulk Insert Using Script And Not Bulk Insert Task

Nov 2, 2007



Does anyone know how to do a bulk insert using just the script task? I've been searching everyehere but can't seem to find a sample.

View 6 Replies View Related

Bulk Insert - Bulk Load Data Conversion Error

Jan 17, 2008

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 ?

View 13 Replies View Related

I Don't Suppose BULK UPDATE Exists?... Like BULK INSERT?

Sep 27, 2007

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!

View 1 Replies View Related

Cannot Fetch A Row From OLE DB Provider BULK With Bulk Insert Task

Nov 23, 2005

Hi, folks:

View 18 Replies View Related

Bulk Insert Multiple Files To Multiple Tables - How?

Feb 15, 2008

I need to be able to bulk insert a bunch of tables from their corresponding flat file. I have created an XML file (see below) which has the file name/table name pair at each node. I then created a ForEachLoop task and used the Node enumeration type and the following OuterXpathString: ReferenceFiles/File. At this point I get lost. How do I pass the 2 inside node values (file name and table name) to variables which I can then use as expressions for the bulk insert task inside the Foreach?

Here is XML file:




Code Snippet
<ReferenceFiles>

<File>


<FileName>Ref_Categories.txt</FileName>
<TableName>Ref_Categories</TableName>
</File>
<File>

<FileName>Ref_Configs.txt</FileName>
<TableName>Ref_Configs</TableName>
</File>
</ReferenceFiles>






Thanks.

View 1 Replies View Related

Global Temp Table Permissions

Jul 23, 2005

I have a pivot table implementation, part of which is posted below. Itreturns no errors in query analyzer, but when profiler is run, it showsthat "Error 208" is happening. I looked that up in BOL and it meansthat an object doesn't exist. This block of code below works fine on mylocal development machine, but not on our shared development serveruntil I go into the tempdb and make the user have the role db_owner.Even wierder is that when I do a select * from ##pivot there is noerror, but if I specify the single column name (pivot) i.e. selectpivot from ##pivot, it takes the error...Obviously this is a rights issue, but is there any way around thisother than making the user owner of tempdb??declare @select varchar(8000), @PackageId intset @PackageId = 10set @select = 'selectCompany = COALESCE(Users.Company, Contact.Company, ''''),SubContractPackageVendor.Id, SubContractPackageVendor.isActive,SubContractPackageVendor.isAwarded,SubContractPackageVendor.UserOrContactType,SubContractPackageVendor.UserOrContactIdFROMSubContractPackageVendorLEFT JOIN SubContractPackage ON SubContractPackageVendor.PackageId =SubContractPackage.IdLEFT JOIN Users ON UserOrContactType = ''User'' AND UserOrContactId =Users.UserIdLEFT JOIN UserRoles ON UserOrContactType = ''User'' ANDUserRoles.UserId = Users.UserId AND UserRoles.ProjectId =SubContractPackage.ProjectIdLEFT JOIN Role ON Role.RoleId = UserRoles.RoleIdLEFT JOIN Contact ON UserOrContactType = ''Contact'' ANDUserOrContactId = Contact.IdLEFT JOIN SubContractLineItem ONSubContractLineItem.RefType = ''Package'' ANDSubContractLineItem.RefId = SubContractPackageVendor.PackageIdLEFT JOIN SubContractLineItem as SubContractPackageVendorItem ONSubContractPackageVendorItem.RefType = ''PackageVendor'' ANDSubContractPackageVendorItem.RefId = SubContractPackageVendor.Id ANDSubContractPackageVendorItem.RefSubId = SubContractLineItem.IdWhereSubContractPackageVendor.PackageId = ' + CAST(@PackageId as varchar)+ 'GROUP BYSubContractPackageVendor.Id, SubContractPackageVendor.isActive,SubContractPackageVendor.isAwarded, Users.Company, Contact.Company,SubContractPackageVendor.UserOrContactType,SubContractPackageVendor.UserOrContactId'--print @sqldeclare @sumfunc varchar(100),@pivot varchar(100),@table varchar(100),@FieldPrefix varchar(5),@TotalFieldName varchar(50),@PivotFieldFilter varchar(1000)select@sumfunc ='Sum(isnull(SubContractPackageVendorItem.Total,0) )' ,@pivot ='SubContractLineItem.Category' ,@table ='SubContractLineItem' ,@FieldPrefix='~' ,@TotalFieldName = 'Total' ,@PivotFieldFilter = ' AND RefType=''Package'' AND RefId=' +CAST(@PackageId as varchar)set nocount onDECLARE @sql varchar(8000), @delim varchar(1), @TotalSql varchar(8000)SET NOCOUNT ONSET ANSI_WARNINGS OFFEXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + 'WHERE 1=2')EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' +@table + ' WHERE '+ @pivot + ' Is Not Null ' + @PivotFieldFilter)SELECT @sql='', @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )SELECT @delim=CASE Sign( CharIndex('char',data_type)+CharIndex('date', data_type) )WHEN 0 THEN '' ELSE '''' ENDFROM tempdb.information_schema.columnsWHERE table_name='##pivot' AND column_name='pivot'select * from ##pivotDROP TABLE ##pivot

View 6 Replies View Related

Pros: How To Bulk Delete And Bulk Insert?

Oct 11, 2000

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


Any comments are appreciated,

-Marc

View 3 Replies View Related

Temp Tables Vs Temp Variables

Jul 20, 2005

I have an application that I am working on that uses some small temptables. I am considering moving them to Table Variables - Would thisbe a performance enhancement?Some background information: The system I am working on has numeroustables but for this exercise there are only three that really matter.Claim, Transaction and Parties.A Claim can have 0 or more transactions.A Claim can have 1 or more parties.A Transaction can have 1 or more parties.A party can have 1 or more claim.A party can have 1 or more transactions. Parties are really many tomany back to Claim and transaction tables.I have three stored procsinsertClaiminsertTransactioninsertPartiesFrom an xml point of view the data looks like this<claim><parties><info />insertClaim takes 3 sets of paramters - All the claim levelinformation (as individual parameters), All the parties on a claim (asone xml parameter), All the transactions on a claim(As one xmlparameter with Parties as part of the xml)insertClaim calls insertParties and passes in the parties xml -insertParties returns a recordset of the newly inserted records.insertClaim then uses that table to join the claim to the parties. Itthen calls insertTransaction and passes the transaction xml into thatsproc.insertTransaciton then inserts the transactions in the xml, and alsocalls insertParties, passing in the XML snippet

View 2 Replies View Related

Error: 0xC002F304 At Bulk Insert Task, Bulk Insert Task: An Error Occurred With The Following Error Message: Cannot Fetch A Row

Apr 8, 2008


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

View 1 Replies View Related

BULK INSERT ERROR Using Format File - Bulk Load Data Conversion Error

Jun 29, 2015

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]...

View 5 Replies View Related

Questions About Bulk Copy Insert Using 'Memory Based Bulk Copy Operations'

Feb 1, 2007

Hi~,

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

- any other guide lines

View 1 Replies View Related

Can I Insert/Update Large Text Field To Database Without Bulk Insert?

Nov 14, 2007

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.

View 6 Replies View Related

Temp. Tables / Variables / Process Keyed Tables ?

Feb 22, 2008

I have 3 Checkbox list panels that query the DB for the items. Panel nº 2 and 3 need to know selection on panel nº 1. Panels have multiple item selection. Multiple users may use this at the same time and I wanted to have a full separation between the application and the DB. The ASP.net application always uses Stored Procedures to access the DB. Whats the best course of action? Using a permanent 'temp' table on the SQL server? Accomplish everything on the client side?

[Web application being built on ASP.net 3.5 (IIS7) connected to SQL Server 2005)

View 1 Replies View Related

How To Insert Data From A File Into Table Having Two Columns-BULK INSERT

Oct 12, 2007



Hi,
i have a file which consists data as below,

3
123||
456||
789||

Iam reading file using bulk insert and iam inserting these phone numbers into table having one column as below.


BULK INSERT TABLE_NAME FROM 'FILE_PATH'
WITH (KEEPNULLS,FIRSTROW=2,ROWTERMINATOR = '||')

but i want to insert the data into table having two columns. if iam trying to insert the data into table having two columns its not inserting.

can anyone help me how to do this?

Thanks,
-Badri

View 5 Replies View Related

Insert Trigger For Bulk Insert

Nov 25, 2006

In case of a bulk insert, the “FOR INSERT� trigger fires for each recod or only once?
Thanks,

View 1 Replies View Related

Compare BULK INSERT Vs INSERT

Apr 26, 2006

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.

Sincerely,
-Lawrence

View 3 Replies View Related

Questions About Memory Based Bulk Copy Operation(InsertRow Count,array Insert Directly,set Memory Based Bulk Copy Option)

Feb 15, 2007

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

m_TableID.uName.pwszName = m_wszTableName;
m_TableID.eKind = DBKIND_NAME;

DBPROP rgProps[1];
DBPROPSET PropSet[1];

rgProps[0].dwOptions = DBPROPOPTIONS_REQUIRED;
rgProps[0].colid = DB_NULLID;
rgProps[0].vValue.vt = VT_BSTR;
rgProps[0].dwPropertyID = SSPROP_FASTLOADOPTIONS;
rgProps[0].vValue.bstrVal = L"ROWS_PER_BATCH = 10000,TABLOCK";

PropSet[0].rgProperties = rgProps;
PropSet[0].cProperties = 1;
PropSet[0].guidPropertySet = DBPROPSET_SQLSERVERROWSET;

if(m_pIOpenRowset)
{
if(FAILED(m_pIOpenRowset->OpenRowset(NULL,&m_TableID,NULL,IID_IRowsetFastLoad,1,PropSet,(LPUNKNOWN*)&m_pIRowsetFastLoad)))
{
return FALSE;
}
}
else
{
return FALSE;
}

View 6 Replies View Related

DTS - SP And Temp Tables

Nov 3, 2000

I am attempting to execute a stored procedure as the sql query for a data transformation from sql into an excel file. The stored procedure I am calling uses temp tables (#tempT1, #tempT2, etc.) to gather results from various calculations. When I try to execute this sp, I get
'Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: Invalid Object name "#tempT1"'

Is there a way to make a DTS package call a stored procedure that uses temp tables?

Thanks.

View 2 Replies View Related

Temp Tables

Jun 12, 2002

Hi,

I want to check to see if a temporary table exists before I try creating one but I can't seem to find which sys table or schema collection I check. Any ideas?

Seoras

View 2 Replies View Related

##Temp Tables

Jun 16, 2004

I have a stored proc that creates a temporary table, then calls several other stored procs to insert data.


CREATE PROCEDURE usp_CreateTakeoff
@iEstimate int,
AS

CREATE TABLE ##Temp_Takeoff
(
Field1 ......
Field2 ......
)

-- Add Structural data
usp_AddStructural @iEstimateID, 1, 'Structural'
usp_AddForming @iEstimateID, 2, 'Forming'
...
...
...
GO


Now, a couple of problems, after the table is created and populated, I cannot find it in my list of tables, even after "refreshing".

I checked to ensure that it exists using the query analyzer and it does so I know the table is being created.

Also, I cannot see the table using crystal reports, connecting etc...... Can I not access a temporary table from 3rd party applications? I have crystal reports 7.0 professional.

Any ideas?

Mike B

View 4 Replies View Related

Temp Tables And UDF's

Dec 14, 2004

Hey,

I am in the process of modifying some stored procedures that currently do not use temp tables. For this modification I am required to make the stored procedures use temp tables. There are several UDF's within this stored procedure that will need to use the temp tables, and this is where in lies the problem. Does anyone know of a work around that would allow UDF's to use temp tables, or does anyone know of alternate methods instead of temp tables that wouldn't involve too much change?

Thanks

View 1 Replies View Related

Temp Tables

Apr 7, 2006

Hi,

I have a called stored procedure which creates a bunch of temporary tables, inserts data into them, indexes the tables and then returns to the main calling SP. In the main stored procedure I then do SELECTs from the temporary tables. My problem is I keep getting
invalid object errors on the temporary tables:
Invalid object name '#temp_table1'

The stored procedure is in a test environment. In the SELECT I tried a prefix of database owner (my logon) as well as "dbo." but still get the error. Any suggestions as to what I am doing wrong would be much appreciated.

Thanks,
Jeff

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved