Stored Procedures With Temp Tables
Nov 23, 2007
If you use a stored procedure that references one or more temp tables as data dource for a report, you get an error saying that the temp tables cannot be found when you click on the Layout tab. This happens even if you have executed the query in the Data tab before going to the Layout tab. The work around is to simply ignore the error but it is a distraction for the user. Is this a known big that is going to be fixed in a future release?
View 3 Replies
ADVERTISEMENT
Feb 12, 2008
Are they unique to a user/session? Like if 2 users simultaneously run the stored procedure?
TIA!
View 13 Replies
View Related
Jul 20, 2004
I have 3 jobs each consists of set of stored procedures.The stored procedures have lots of temp tables. And all the jobs run at the same time.
job1:
EXEc sp1
EXEC sp2
EXEC sp3
Job2
EXEC abc1
EXEC abc2
EXEC abc3
EXEC abc4
EXEC abc5
Job3
EXEC xyz1
EXEC xyz2
EXEC xyz3
EXEC xyz4
But the issue is that the stored procedures in the job1 has temp tables with the same name as stored procedures in the job 2 have.
Eg:
procedure abc1 and procedure sp2 have the temp table #temp1.
procedure abc4 and procedure xyz2 have the temp table #temp3.
Like this i have some more common temp tables. So my question is that can I use the temp tables like that.If so does it degrade the perforamnce of the sps.
View 1 Replies
View Related
Jun 8, 2001
We are migrating a SQL 6.5 application with 1900 stored procedures that use 100's of temp tables to SQL 2000.
A problem we have encountered was that we started out getting an "invalid column" errors on certain procedures. Investigation determined that the error was being generated in a nested procedure. The table that caused the error ended up being a temp table that was created using "select into". The following select statement from that temp table gave the invalid column error.
First thinking it was the "Select Into" we then discovered that the outer most procedure had created a temp table of the same name prior to executing the lower level procedure. After the select into, the next statement was a SELECT that went against what it thought was the inner temp table. However, it grabbed the outermost temp table and then couldn't find the appropriate columns and generated the error.
The solution, of course, was to rename the inner most temp table. We also remove the "select into" in the procedure by explicitly creating the temp table.
We tried creating some test procedures to attempt to reproduce this scenario without complete success.
Our test created 3 procedures (sp1 calling sp2 calling sp3) to mimic the current scenario. Sp1 created a temp table and executed sp2, which executed sp3. Sp3 created another temp table using the same name as the one created in sp1.
If we create all three procedures at the same time, it doesn't matter if we change the order in which they are created or whether we create the inner temp table explicitly or with a "select into", SQL Query Analyzer won't let us create the procedure because it finds that the temp table has been declared twice. If we create the procedures separately however, they compile and allow sp3 to create a temp table by the same name as sp1. After creating the procedures independently, they runs properly in all cases with everything in proper scope and no problems.
Admittedly, this is bad coding to start with, but what is happening with the scope of the temp tables within the stored procedures?
Thanks,
Glen Smith
View 1 Replies
View Related
Oct 22, 2007
Hi
I have one stored procedure which uses temp tables in it .
I am trying to use this stored procedure in my SSRS report as the source.
I am getting an error which says that the temporary table doesnot exist while validating the sql syntax in report.
Also, this stored proc executes in SSMS ithout any problem and giving the result as desired.
Can we use Temp tables in Stored proc and call that in SSRS?
If not, what is the workaround for that.
Thanks
View 13 Replies
View Related
Nov 18, 2005
I am trying to create a SQL data adapter via the wizard, however, I get
the error "Invalid object name #ords" because the stored procedure uses
a temp table. Anyway around this? Thanks.
View 11 Replies
View Related
Mar 2, 1999
I have an sql file that contains several queries that are generating numbers to populate a sql table. The sql file is too large for a single sp so I am nesting them. I have 4 nested stored procedures. Each of the queries in each stored procedure dumps into its own global temp table. The final stored procedure needs to insert into a sql table all the information gathered in the global temp tables. So the final stored proc. looks something like:
"Create procedure usp_myProc_4 AS EXEC usp_myProc_3
INSERT INTO mySQLTable (a,b,c)
SELECT a, b, c FROM ##myTempTable (which was created in usp_myProc_1)
INSERT INTO mySQLTable (a,b,c)
SELECT a, b, c FROM ##myOtherTempTable
INSERT INTO......etc;"
I have done this befor and it worked fine. The only difference is that when I did this before these insert statements were being called from within an sp_makewebtask procedure.
Now when I try to save this final stored procedure it tells me "Invalid Object Name: ##myTempTable"
How do I call on these global temp tables from my final nested stored procedure?
Thanks for any help.
View 1 Replies
View Related
Apr 29, 2008
How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out?
SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
View 1 Replies
View Related
Feb 8, 2008
This is killing me...and not slowly.
I have a stored procedure (a) which calls another stored procedure (b).
Stored procedure (b) is a C# stored procedure which simply writes out to a file data in XML format. Internally, it calls...select fld1, fld2, fld3, fld4, fld5from #tmptable for xml auto, elements
If I call stored procedure (a) from Query Analyser / SQL Management Studio everything works fine. Perfect.
But....we need this all to run asynchronously. So we used the Service Broker, configured the queues and messages and off we went. All worked as planned except our XML files were empty.
Further investigation showed that if we call select fld1, fld2, fld3, fld4, fld5from #tmptable
- without the 'xml' bits, we got a resultset back. But if we call it with the for xml auto, elements, the reader was empty. No errors are visible in the profiler, but the XmlReader refuses to read.
The binary / extended stored procedure is the same pysical binary that is called from Query analyser that works, but via the Service Broker refuses to do anything XML based. Outputting the data as normal text is cool, but not what we want.
----------------- UPDATE --------------
I changed the code so the CLR Stored proc was fired as a trigger on an update to a table. If I update the table in Query analyser, the trigger fires, the CLR Stored proc is called, the XML is generated.
If I update the table as part of my message handling in the Service Broker queue, the trigger is fired, the CLR Stored proc is called, the XML is generated EMPTY!!! The other TSQL statements work fine, but selecting for xml simply will not work for a procedure called, either implicitly or explicitly from the service broker.
View 3 Replies
View Related
Feb 11, 2015
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
View 3 Replies
View Related
Mar 26, 2008
Hello
I'm start to work with SSIS.
We have a lot (many hundreds) of old (SQL Server2000) procedures on SQL 2005.
Most of the Stored Procedures ends with the following commands:
SET @SQLSTRING = 'SELECT * INTO ' + @OutputTableName + ' FROM #RESULTTABLE'
EXEC @RETVAL = sp_executeSQL @SQLSTRING
How can I use SSIS to move the complete #RESULTTABLE to Excel or to a Flat File? (e.g. as a *.csv -File)
I found a way but I think i'ts only a workaround:
1. Write the #Resulttable to DB (changed Prozedure)
2. create data flow task (ole DB Source - Data Conversion - Excel Destination)
Does anyone know a better way to transfer the #RESULTTABLE to Excel or Flat file?
Thanks for an early Answer
Chaepp
View 9 Replies
View Related
Jul 20, 2005
SQL Server 2000:Question 1If you drop / rename a table and then recreate the table with the SAMENAME, what impact does it have on stored procedures that use these tables?From a system perspective, do you have to rebuild / recompile ALL the storedprocedures that use this table?I had a discussion with someone that said that this is a good idea, sincethe IDs of the tables change in sysobjects and from a SQL SERVER query planperspective, this needs to be done...Question 2If you Truncate a Tables as part of a BEGIN TRANSACTION, what happens if anerror occurs? Will it Rollback? The theory is that it won't becauseTruncate doesn't utilize the logs where as Delete From uses the SQL Logs?Thanks!
View 3 Replies
View Related
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
Apr 28, 2007
Hi,I'm trying to insert some values into 2 tables using stored procedures (which should be pretty straight forward) but I'm running into problems.Given below are my 2 sp that I'm using:
View 5 Replies
View Related
Feb 14, 2008
hi I'm working in VS2005 with SQL server Express database.How can I copy some tables, stored procedures and diagrams from one database to another? thanks
View 3 Replies
View Related
Feb 14, 2006
Hello all!
What I need to do is take a site that I have and make 3 copies of it so I will have 4 separate sites with 4 separate DB's but running on the same server. One of the sites is complete but what I need to know is how do I make a complete copy of the DB including all stored procedures and populate a blank DB that has a different name with contents from the master DB???
So if DB1 is complete and I want to now populate DB2, DB3 and DB4 with everything from DB1 (tables, stored procedures, data etc.) what would be the best way to do this?
The new sites are already setup in IIS and I have already transferred the files to each sites root, so all that is left is to setup the new DB's. They are all running on the same server… I think that is about everything someone would need to know to help me!
Any help would be greatly appreciated!!!
View 3 Replies
View Related
Mar 2, 2006
Hi,
I am hoping someone can help me with creation of a stored procedure. What i would basically like to do is have a SELECT stored Procedure that gets values from one table and then uses these values to retrieve data from another table.
i.e. I would like to retrieve 2 dates from another table (the table has a single record that holds the 2 dates) then I would like to use those two dates to retrieve all records of another table that fall between those 2 dates.
As I know nothing about stored procedures I'm not even sure how to go about this??
Regards.
Peter.
View 2 Replies
View Related
Jan 17, 2008
Is there any oen can tell me how i can run some system sp or exec something to list all tables or stored procedures in a database, like i use select statement to list some coulmn data? thanks in advance.
View 3 Replies
View Related
Sep 3, 2007
Hi,
I am using multiple stored procedures which are using same set of tables at a time .
As stored procedures dont have any DMLs. they are just select statement copied into a temporary table for further processing.
My issue is ,I dont want to wait one stored procedure until the other stored procedure is completed.
as one stored procedure is taking 43 secs and another sp is taking one min .they are conmbinely taking 1:43 mins
where i want to take just 1 min which is the time took by second sp
I want this because i am calling all the stored procedures more than 5 in my reporting services to show in one report which is taking huge time
Please suggest me how to proceed here.i am stuck
what should i do with the tables or stored procedures?
Thank you
Raj Deep.A
View 4 Replies
View Related
Sep 3, 2007
Hi ,
I have a Report which has 8 stored procedures to get 8 resultant data sets . the stored procedures are almost similar such that they have only difference is the where clause and column getting returned.
So ,every stored access same set of tables and temporary tables getting created to store some set of active data derived from big table.
what happening is ,when i run the stored procedures individually in query analyser ,it is taking the time which is accepatable individually,but when i keep them in the same report. it is taking the time which is equal to sum of all the times taken by the stored procedures ran individually in query analyser which is some what not acceptable
can anybody through an idea,what can be done here. i already thought of locks and kept set transaction isolation level read uncomitted for all the stored procedures.but the time taking is same.
please help me here,i am stuck
Thank you
View 2 Replies
View Related
Sep 12, 2006
Hi,Is there a way I can change schema name on tables and stored procedures? How do I do this?I´m very news to SQL and .netThanks
View 1 Replies
View Related
Dec 20, 2007
Can a SqlDataSource or a TableAdapter be attached to a stored procedure that returns a temporary table? I am using Sql Server 2000.
The SqlDataSource is created with the wizard and tests okay but 2.0 controls will not bind to it.
The TableAdapter wizard says 'Invalid object name' and displayes the name of the temporary table.ALTER PROCEDURE dbo.QualityControl_Audit_Item_InfractionPercentageReport
@AuditTypeName varchar(50), @PlantId int = NULL,
@BuildingId int = NULL, @AreaId int = NULL,
@WorkCellId int = NULL, @WorkShift int = NULL,
@StartDate datetime = NULL, @EndDate datetime = NULL,
@debug bit = 0 AS
CREATE TABLE #QualityControl_Audit_Item_SelectDistinctByFilters_TempTable (ItemHeader varchar(100), Item varchar(250), HeaderSequence int, ItemSequence int, MajorInfractionPercent money, MinorInfractionPercent money)
DECLARE @sql nvarchar(4000), @paramlist nvarchar(4000)
SELECT @sql = '
INSERT INTO #QualityControl_Audit_Item_SelectDistinctByFilters_TempTable
(ItemHeader, Item, HeaderSequence, ItemSequence, MajorInfractionPercent, MinorInfractionPercent)
SELECT DISTINCT QualityControl_Audit_Item.Header,
QualityControl_Audit_Item.AuditItem,
QualityControl_Audit_Item.HeaderSequence,
QualityControl_Audit_Item.AuditItemSequence,
NULL, NULL
FROM QualityControl_Audit INNER JOIN
QualityControl_Audit_Item ON QualityControl_Audit.QualityControl_Audit_Id = QualityControl_Audit_Item.QualityControl_Audit_Id
WHERE (QualityControl_Audit.AuditType = @xAuditTypeName)'
IF @PlantId IS NOT NULL SELECT @sql = @sql + ' AND QualityControl_Audit.PlantId = @xPlantId'
IF @BuildingId IS NOT NULL SELECT @sql = @sql + ' AND QualityControl_Audit.BuildingId = @xBuildingId'
IF @AreaId IS NOT NULL SELECT @sql = @sql + ' AND QualityControl_Audit.AreaId = @xAreaId'
IF @WorkCellId IS NOT NULL SELECT @sql = @sql + ' AND QualityControl_Audit.WorkCellId = @xWorkCellId'
IF @WorkShift IS NOT NULL SELECT @sql = @sql + ' AND QualityControl_Audit.WorkShift = @xWorkShift'
IF @StartDate IS NOT NULL SELECT @sql = @sql + ' AND QualityControl_Audit.DateAuditPerformed >= @xStartDate'
IF @EndDate IS NOT NULL SELECT @sql = @sql + ' AND QualityControl_Audit.DateAuditPerformed <= @xEndDate'SELECT @sql = @sql + '
ORDER BY QualityControl_Audit_Item.HeaderSequence, QualityControl_Audit_Item.AuditItemSequence'
IF @debug = 1 PRINT @sqlSELECT @paramlist = '@xAuditTypeName varchar(50), @xPlantId int, @xBuildingId int, @xAreaId int, @xWorkCellId int,
@xWorkShift int, @xStartDate datetime, @xEndDate datetime'
EXEC sp_executesql @sql, @paramlist, @AuditTypeName, @PlantId, @BuildingId, @AreaId, @WorkCellId, @WorkShift, @StartDate, @EndDateDECLARE my_cursor CURSOR FOR
SELECT ItemHeader, Item, MajorInfractionPercent, MinorInfractionPercent FROM #QualityControl_Audit_Item_SelectDistinctByFilters_TempTable
OPEN my_cursor
--Perform the first fetchDECLARE @ItemHeader varchar(100), @Item varchar(250), @MajorInfractionPercent money, @MinorInfractionPercent money
FETCH NEXT FROM my_cursor INTO @ItemHeader, @Item, @MajorInfractionPercent, @MinorInfractionPercent
--Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
--Major
EXEC dbo.QualityControl_Audit_Item_InfractionPercentageReport_CalculateForMajorOrMinor @AuditTypeName, @PlantId, @BuildingId, @AreaId, @WorkCellId, @WorkShift, @StartDate, @EndDate, @ItemHeader, @Item, 'Major', @debug, @InfractionPercent = @MajorInfractionPercent OUTPUTUPDATE #QualityControl_Audit_Item_SelectDistinctByFilters_TempTable
SET MajorInfractionPercent = @MajorInfractionPercentWHERE (((ItemHeader IS NULL) AND (@ItemHeader IS NULL) OR (ItemHeader = @ItemHeader)) AND (Item = @Item))
--Minor
EXEC dbo.QualityControl_Audit_Item_InfractionPercentageReport_CalculateForMajorOrMinor @AuditTypeName, @PlantId, @BuildingId, @AreaId, @WorkCellId, @WorkShift, @StartDate, @EndDate, @ItemHeader, @Item, 'Minor', @debug, @InfractionPercent = @MinorInfractionPercent OUTPUTUPDATE #QualityControl_Audit_Item_SelectDistinctByFilters_TempTable
SET MinorInfractionPercent = @MinorInfractionPercentWHERE (((ItemHeader IS NULL) AND (@ItemHeader IS NULL) OR (ItemHeader = @ItemHeader)) AND (Item = @Item))
FETCH NEXT FROM my_cursor INTO @ItemHeader, @Item, @MajorInfractionPercent, @MinorInfractionPercent
END
CLOSE my_cursor
DEALLOCATE my_cursor
SELECT * FROM #QualityControl_Audit_Item_SelectDistinctByFilters_TempTable
View 8 Replies
View Related
Apr 8, 2008
Hello there!
I've been sitting here thinking how to work through this problem but I am stuck. First off I have two tables I am working with.
Request TablerqstKey (this is the primary key and has the identity set to yes)entryDte datetimesummary nvarchar(50)etryUserID nvarchar(50)rqstStatusCdeKey bigint
ReqStatusCode TablerqstStatusCdeKey bigintstatusCode nvarchar(50)
I have webforms that are using Session variables to store information from the webpages into those variables and insert them into these tables. However, I am wanting to insert the rqstStatusCdeKey (from the ReqStatusCode Table) value into the Request Table. The rqstStatusCdeKey value i am wanting to insert is '3' which stands for "in Design" (((this is going to be the default value))). I need help :-/ If you need me to explain more/ clarify, and show more information I can. Thank you so much!!!
View 4 Replies
View Related
May 19, 2004
Hi,
I was just wondering if something could be explained to me.
I have the following:
1. A table which has fields with data types and lengths / sizes
2. A stored procedure for said table which also declares variables with datatype and lengths/ sizes
3. A function in written in VB .net that uses said stored procudure. The code used to add the parameters to the sql command also requires that i give a data type and size.
How come i need to specify data type and length in three different places? Am i doin it wrong?
Any information is greatly appreciated.
Thanks
Im using SQL Server 2000 with Visual Studio .Net using Visual Basic..
View 1 Replies
View Related
Nov 18, 2005
Hello, I am a beginner using MSDE & Visual Basic.NET standard version (not Visual Studio.NET) . MSDE was downloaded from microsoft.com yesterday, which is sql2ksp3.exe.
View 15 Replies
View Related
Feb 12, 2008
Hello Friends!!
I have a great problem !!
I have a database having around 50 tables and around 70 t0 100 stored procedures.
due to certain reason name of all the table and procedure was prefixed with "cls" ,
now i want to rename them by "tbl_" using some query !! in bulk ,
and tables have relation ships.
so how to rename them !!
is there any way that I can rename them by modifying data in any system table !?
View 6 Replies
View Related
Apr 21, 2006
I converted a program from SQL 2000 to SQL 2005 all went well. I created a number of tables and stored procedures after the conversion. I backed up my .mdf and .idf files. I was having problems with SQL so I uninstalled and re-installed it. Once I re-installed it I could no longer display some tables and files. Since I am the dbo, I think I should be able to access them. There obviously is something I am missing, hopefully not the tables and sps.
I would appreciate any suggestions.
Thank you.
LitePipe
View 4 Replies
View Related
Jun 3, 2006
There are two ways to create a temporary tables in stored procedures
1: Using Create Table <Table Name> & then Drop table
ex. Create Table emp (empno int, empname varchar(20))
at last : drop table emp
2. Using Create table #tempemp
( empno int, empname varchar(20))
at last : delete #tempemp
---which one is preferrable & why.
what are the advantages & disadvantages of the above two types.
View 5 Replies
View Related
Sep 3, 2007
I am learning T SQL and SQL queries and have limited VB knowledge, and have a some simple queries to run on a table with parameters, and would like verification of the proposed methodology and suggestions.
Simply put, I have a [Transactions] table with columns [Price], [Ticker], [TransDate], [TransType] and calculated columns for [Days] and [Profit].
There are two parameters, [@Dys] (to query a the table for transactions within a certain period[Days = 30] and [@TT] to query only the closed transactions ie... [TransType='C']
I have been studying Stored Procedures and will be writing a Stored Procedure, but need verification if the following will work... Getting the SUM and AVG calcluations for the fields above is not a problem but I need to display SUM and AVG information also for those transactions where [Profit >0] and [Profit <0], which is easy enough by creating a subquery. But the problem is:
1. If I use a SubQuery for [Profit <0] and for [Profit>0], can I create an alias for [Count(*)] (to get a row or transaction count for each, and then divide that into the Total [Count(*)] alias for the Transactions table to get a value for % profitable or Probability (% total Profitable trades versus % total Unprofitable trades)?
2. Or, do I need to create either temporary tables or views to have 3 distinct tables (1 table for Transactoins and 2 temp or Views for [Profit >0] and [Profit <0])?
Any suggestions and advice or examples on how to do this would be appreciated.
Craig
My questions are:
View 2 Replies
View Related
Oct 5, 2004
Hi My friends
I have develop big Database System in Microsoft SQL Server 2000 and my DB System content more then 30 Table and also more then 150 Stored procedure and from that stored procedures I have call my the tables
But the problem when I change the name of any table, SQL Server don’t prompt my about the stored procedure that use that table .. so I cant remember witch Stored procedure use that table
By example if I am in programming language like C# or VB.NET when I try to change any name of any class the compiles catch all the error that coming from changing the name of the class and simply I will fix the problem because the compiler help me and list all the object content the error
But in SQL Server how can I search for stored procedure that use un existing table like compiler in any programming language ??
Or is there any why to solve my problem ??
And the above problem also there when I change the filed name
And thanks with my best regards
Fraas
View 1 Replies
View Related
Feb 26, 2007
Hi all,
I’m currently writing a web application on student exam timetables, I’m using SQL Server 2005 as the back-end for the database.
At present, the case states that if a student is in one examination, he/she can’t attend, or be allocated another examination while the first examination is in place, which would result in a clash
The way I’m going to target this is by writing a stored procedure in SQL Server 2005 to return an error code, which I’ll translate using ASP,NET, however at present I’m having difficulty writing the SQL code. This is because…
I’m using SQL Server 2005 Management Studio; I created the tables using MS Access and upsized them using the wizard. I can now access my database, but having difficulty editing my tables and with code…
Any ideas??
Thank-you
View 2 Replies
View Related
Feb 20, 2007
Hi all--I used the following type of script to migrate tables, views and stored procedures from 'dbo' to 'hqmsuser':
alter authorization on dbo.PK_T_PQ_MASTER to HQMSUSER
alter schema HQMSUSER transfer dbo.PK_T_PQ_MASTER
go
This was per recommendations from the BOL to use instead of the 'sp_changeobjectowner' procedure. I returned the following error:
Msg 15346, Level 16, State 1, Line 3
Cannot change owner for an object that is owned by a parent object. Change the owner of the parent object instead.
Msg 15347, Level 16, State 1, Line 4
Cannot transfer an object that is owned by a parent object.
How do I find and change the parent object on this from 'dbo' to a non-dbo schema ownership?
View 1 Replies
View Related
May 21, 2008
Hi Folks,
I am writing a program that transforms a generic MS SQL database to make it compatible with our application. In order to make the transformation, I have to access all the elements of the generic database programmatically.
I know that the Master database contains all this information. Is there a query that allows me to access the "non-system" tables, sps, views, and functions?
For Tables, I need to know the Name(s) of the tables, the column names, the column type, ALLOW Nulls, Primary Key, Identity Seed settings, and Triggers.
For SPs, I need to know the Name(s) and access the SP source code (assuming it's unencrypted).
For Views, I need to know the Name(s) and access the Views Source
For functions, I need to know the Name(s) and access the function source.
I can see various tables in the Master database from management studio, like sys.objects, sys.tables - which look like they have the info I need. However, when I run a query against master like:
select * from sys.objects .. I get an error:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.objects'.
Thank you in advance.
View 13 Replies
View Related
