SQL SP Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL SP Error

Author

Topic

satya068
Posting Yak Master

233 Posts

Posted - 2010-04-15 : 07:19:35

Hi..
i created sp in pas_load database when i executed this sp i should get two columns table_name and row_count into the FULL_LOAD_RECORD_COUNT from systables.

the error message i am getting

Msg 208, Level 16, State 1, Line 1
Invalid object name 'FULL_LOAD_RECORD_COUNT'.

this is my SP

USE [PAS_LOAD]
GO
/****** Object: StoredProcedure [dbo].[SP_LOAD_PAS_DAILY_RECORD_COUNT] Script Date: 04/15/2010 10:09:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_LOAD_PAS_DAILY_RECORD_COUNT] AS

DECLARE @TableName sysname,
@SQL nvarchar(max),
@RUN_DATE datetime,
@DATABASE varchar(255)
DECLARE table_cursor CURSOR FAST_FORWARD
FOR
SELECT name FROM sys.tables
WHERE (NAME like 'LOAD_PAS%')

OPEN table_cursor

FETCH NEXT FROM table_cursor INTO @TableName
WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @SQL =
'Insert into FULL_LOAD_RECORD_COUNT (TABLE_NAME, ROW_COUNT) SELECT ''' + @TableName + '''as TABLE_NAME, COUNT(*)as ROW_COUNT ' +

'FROM ['+ @TableName+']'

print @SQL

EXEC SP_EXECUTESQL @SQL

PRINT 'got to step 1'

FETCH NEXT FROM table_cursor INTO @TableName

END
CLOSE table_cursor
DEALLOCATE table_cursor

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-15 : 07:27:13

Where are you executing the stored proc? It will try to insert into FULL_LOAD_RECORD_COUNT on whatever database you are in.

Are you in master maybe?

Does the table exist?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

satya068
Posting Yak Master

233 Posts

Posted - 2010-04-15 : 07:33:39

Hi charlie..
i am executing the SP in PAS_LOAD database and i am trying to insert records into FULL_LOAD_RECORD_COUNT which is in same database.

Satya

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-15 : 07:39:56

What is this for? @DATABASE varchar(255)

No, you're never too old to Yak'n'Roll if you're too young to die.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-15 : 07:40:31

Can you run the printed @sql manually and does it work?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

satya068
Posting Yak Master

233 Posts

Posted - 2010-04-15 : 08:00:58

web...iam sorry there is noo need for database declaration..i deleted that from my script now.

Satya

satya068
Posting Yak Master

233 Posts

Posted - 2010-04-15 : 08:03:07

this is my printed sql
Insert into FULL_LOAD_RECORD_COUNT (TABLE_NAME, ROW_COUNT) SELECT 'LOAD_PAS_RETENTION_REASONS'as TABLE_NAME, COUNT(*)as ROW_COUNT FROM [LOAD_PAS_RETENTION_REASONS]
Msg 208, Level 16, State 1, Line 1
Invalid object name 'FULL_LOAD_RECORD_COUNT'.

if i get rid of that insert statement its working from select
if i run the complete statement its not working.

Satya

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-15 : 08:28:33

OK -- so if you run the INSERT INTO FULL_LOAD_RECORD_COUNT it doesn't work even though you are running it manually...

Do you need to declare a different schema name? (username on 2005)

What happens if you do.


SELECT * FROM FULL_LOAD_RECORD_COUNT

Are you sure you have the name right. What is the name if you look in the object explorer in management studio?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

satya068
Posting Yak Master

233 Posts

Posted - 2010-04-15 : 08:32:49

charlie..when i run the script it should create a table FULL_LOAD_RECORD_COUNT in the same databse right?
or do i need to create a table manually?

Satya

satya068
Posting Yak Master

233 Posts

Posted - 2010-04-15 : 08:39:21

I CREATED TABLE MANUALLY NOW IT WORKS ...

i want to add getdate() as RUN_DATE column to the table.

where shell i add in this script.

SELECT @SQL =
'Insert into FULL_LOAD_RECORD_COUNT (TABLE_NAME, ROW_COUNT) SELECT ''' + @TableName + '''as TABLE_NAME, COUNT(*)as ROW_COUNT ' +

'FROM ['+ @TableName+']'

Satya

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-15 : 08:52:35

You can either.

1) Drop and recreate the table. Add your column RUN_DATE with a datatype of DATETIME and then reference the column in the same way as the other columns in your sql string.

2) Add a new column to the table with a default value of GETDATE() and then you don't have to explicitly reference the column.

So your CREATE TABLE statement would look like


CREATE TABLE FULL_LOAD_RECORD_COUNT (
	TABLE_NAME VARCHAR(512)
	, ROW_COUNT INT
	, RUN_DATE DATETIME DEFAULT GETDATE()
	)

Then if you insert into the table and don't reference RUN_DATE then it will get the default value (which is GETDATE())

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-15 : 08:53:16

quote:
Originally posted by satya068

charlie..when i run the script it should create a table FULL_LOAD_RECORD_COUNT in the same databse right?
or do i need to create a table manually?

Satya

Ah -- it makes sense now. No INSERT INTO xyz doesn't create the table xyz if it doesn't exist

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

satya068
Posting Yak Master

233 Posts

Posted - 2010-04-15 : 09:07:18

HELLO CHARLIE..

i used the below script tocreate getdate() column now every thing looks fine,

each time when i am running the script records getting double,and aslo at the end of my script i tried to insert records from FULL_LOAD_RECORD_COUNT to another table other database.
i am not getting any error but the records are not getting into final table which is PAS_AUDIT.dbo.FULL_LOAD_PAS_DAILY_RECORD_COUNT

this is my script

IF (Object_ID(N'FULL_LOAD_RECORD_COUNT')IS NOT NULL)
DROP TABLE FULL_LOAD_RECORD_COUNT

SELECT TABLE_NAME,ROW_COUNT AS RECORD_COUNT,getdate() as RUN_DATE from FULL_LOAD_RECORD_COUNT

order by 1

INSERT INTO PAS_AUDIT.dbo.FULL_LOAD_PAS_DAILY_RECORD_COUNT
SELECT * FROM FULL_LOAD_RECORD_COUNT

Satya

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-15 : 09:17:13

OK -- back up a bit.

What are you actually trying to do?

The script you've posted now doesn't seem to have anything to do with the script you posted earlier. Also. It doesn't make any sense


IF (Object_ID(N'FULL_LOAD_RECORD_COUNT')IS NOT NULL)
DROP TABLE FULL_LOAD_RECORD_COUNT

-- This drops the table FULL_LOAD_RECORD_COUNT if it exists?


SELECT TABLE_NAME,ROW_COUNT AS RECORD_COUNT,getdate() as RUN_DATE from FULL_LOAD_RECORD_COUNT

This tries to select records from the table you just dropped?????


ORDER BY 1

huh?? what did you want to do there?


INSERT INTO PAS_AUDIT.dbo.FULL_LOAD_PAS_DAILY_RECORD_COUNT
SELECT * FROM FULL_LOAD_RECORD_COUNT

OK so you are trying to dump the data into another database in another table from a table that doesn't exist (because you dropped it????)

I'm shocked that your script doesn't error. It looks like it should

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

satya068
Posting Yak Master

233 Posts

Posted - 2010-04-15 : 09:22:58

i am trying to get data from systable of a database PAS_LOAD i created a stored procedure in this databse and a table as well,finally i want all the data in the table to be dumped into another table in other database.

in my PAS_LOAD database table FULL_LOAD_RECORD_COUNT i can see the record actual record are only 90 but each time whin i execte the script its getting double.

this is my complete SP

USE [PAS_LOAD]
GO
/****** Object: StoredProcedure [dbo].[SP_LOAD_PAS_DAILY_RECORD_COUNT] Script Date: 04/15/2010 10:09:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_LOAD_PAS_DAILY_RECORD_COUNT] AS
-- =============================================
-- Procedure to be scheduled to be run DAILY
-- counting numbers of records for each table
-- specified in FULL_PAS_DAILY_RECORD_COUNT.
-- These numbers of records will be used to
-- check overnight FULL PAS jobs
--
-- Change History
---==============
-- SS 15/04/2010 Initial Creation
-- =============================================

DECLARE @TableName sysname,
@SQL nvarchar(max),
@RUN_DATE datetime

TRUNCATE TABLE FULL_LOAD_RECORD_COUNT
DECLARE table_cursor CURSOR FAST_FORWARD
FOR
SELECT name FROM sys.tables
WHERE (NAME like 'LOAD_PAS%')

OPEN table_cursor

FETCH NEXT FROM table_cursor INTO @TableName
WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @SQL =
'Insert into FULL_LOAD_RECORD_COUNT (TABLE_NAME, ROW_COUNT) SELECT ''' + @TableName + '''as TABLE_NAME, COUNT(*)as ROW_COUNT ' +

'FROM ['+ @TableName+']'

print @SQL

EXEC SP_EXECUTESQL @SQL

PRINT 'got to step 1'

FETCH NEXT FROM table_cursor INTO @TableName

END
CLOSE table_cursor
DEALLOCATE table_cursor

IF (Object_ID(N'FULL_LOAD_RECORD_COUNT')IS NOT NULL)
DROP TABLE FULL_LOAD_RECORD_COUNT

SELECT TABLE_NAME,ROW_COUNT AS RECORD_COUNT,getdate() as RUN_DATE from FULL_LOAD_RECORD_COUNT

order by 1

INSERT INTO PAS_AUDIT.dbo.FULL_LOAD_PAS_DAILY_RECORD_COUNT
SELECT * FROM FULL_LOAD_RECORD_COUNT

Satya

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-15 : 10:31:21

OK -- I'd get rid of the table FULL_LOAD_RECORD_COUNT entirely and the CURSOR. Try replacing the stored proc with this:


USE [PAS_LOAD]
GO
/****** Object: StoredProcedure [dbo].[SP_LOAD_PAS_DAILY_RECORD_COUNT] Script Date: 04/15/2010 10:09:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_LOAD_PAS_DAILY_RECORD_COUNT] AS BEGIN

	CREATE TABLE #logDetails (
		[TABLE_NAME] VARCHAR(512)
		, [ROW_COUNT] INT
		, [RUN_DATE] DATETIME
		)

	DECLARE @sql NVARCHAR(MAX)

	-- Initialise @sql
	SET @sql = N''

	-- Set up the inserts into #logDetails
	SELECT @sql = @sql + N'

	INSERT INTO #logDetails ([TABLE_NAME], [ROW_COUNT], [RUN_DATE])
	SELECT
		' + QUOTENAME([name], '''') + '
		, COUNT(*)
		, GETDATE()
	FROM
		' + QUOTENAME([name])
	FROM
		sys.tables
	WHERE
		[name] LIKE 'LOAD_PAS%'

	-- UNCOMMENT this to show the SQL
	-- PRINT @sql

	-- DO IT!
	EXEC SP_EXECUTESQL @SQL 


	-- Insert the temp table into the permanent table in PAS_AUDIT
	INSERT INTO PAS_AUDIT.dbo.FULL_LOAD_PAS_DAILY_RECORD_COUNT (
		[TABLE_NAME]
		, [ROW_COUNT]
		, [RUN_DATE]
		)
	SELECT
		[TABLE_NAME]
		, [ROW_COUNT]
		, [RUN_DATE]
	FROM
		#logDetails

END

This does away with the cursor and builds individual INSERTS into a temp table #logDetails for all the tables you wanted.

Then finally it inserts the data into your permanent location.

IF you have any questions about any parts ask.

Regards,

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

satya068
Posting Yak Master

233 Posts

Posted - 2010-04-15 : 10:41:43

THANX CHARLIE.
ILL TRY WITH THIS SCRIPT.

Satya

satya068
Posting Yak Master

233 Posts

Posted - 2010-04-15 : 11:24:56

hi..charlie,

syntax error near select statement at the end of the script....

SELECT
[TABLE_NAME]
, [ROW_COUNT]
, [RUN_DATE]

i checked all the posibilites and also checked by repalcing
select * from #logDetails

but error pointing to the select

Satya

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-15 : 11:44:20

I missed a bracket the last insert should be


INSERT INTO PAS_AUDIT.dbo.FULL_LOAD_PAS_DAILY_RECORD_COUNT (
		[TABLE_NAME]
		, [ROW_COUNT]
		, [RUN_DATE]
		)
	SELECT
		[TABLE_NAME]
		, [ROW_COUNT]
		, [RUN_DATE]
	FROM
		#logDetails

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

satya068
Posting Yak Master

233 Posts

Posted - 2010-04-15 : 11:58:13

Thanx charlie,

thanx for alloting ur valuable time on my query.

now i got all the data in my distination table.

ur script looks really straight farward.

Satya

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-16 : 04:28:32

you are welcome. It's nice to be thanked.

The only slightly tricky part of the script I posted is this part


-- Set up the inserts into #logDetails
	SELECT @sql = @sql + N'

	INSERT INTO #logDetails ([TABLE_NAME], [ROW_COUNT], [RUN_DATE])
	SELECT
		' + QUOTENAME([name], '''') + '
		, COUNT(*)
		, GETDATE()
	FROM
		' + QUOTENAME([name])
	FROM
		sys.tables
	WHERE
		[name] LIKE 'LOAD_PAS%'

Which builds up the insert statements using sting concatenation.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources