?? table name creation is prefixed with user domain name. 1

[sparkbyte]

I am not sure if this is the correct place, but I will start here.

I have an Access ADP to which I have created the following Stored procedure. Prior to the SP running the table is dropped. Then the SP creates the table [tblTrackingParse] copies a date range selected data set then "parses" the UPS tracking number for reporting purposes.

The weird thing is that if I run the SP everything runs as expected. But when anyone else the runs the SP the table that gets created is prefixed with their domain and username like so [myDomain\myUserName.tblTrackingParse]. I have never seen this behaviour before and need to get it corrected.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



ALTER PROCEDURE [dbo].[usp_DoAll_Parse]
@DateParam1 datetime,
@DateParam2 datetime
AS
SET NOCOUNT ON;

--Select
--DATEADD(dd, 0, DATEADD(mm, DATEDIFF(mm, 0, CURRENT_TIMESTAMP), 0)),  -- First Day of Month
--DATEADD(ss,-1,DATEADD(dd, DATEDIFF(dd, 0, CURRENT_TIMESTAMP), 1))    -- End of Current Day


---------------------------------------------------------------
---------------------------------------------------------------
--
--  Create the Parsing Table
--
---------------------------------------------------------------
---------------------------------------------------------------

IF OBJECT_ID('dbo.[tblTrackingParse','U') IS NOT NULL
DROP TABLE dbo.[tblTrackingParse]

CREATE TABLE 
	  tblTrackingParse(Tracking_ID int
	, EmployeeID varchar(50)
	, MachineName varchar(20)
	, BoxNumber varchar(45)
	, FileNumber varchar(25)
	, TrackingDate Datetime
	, TrackingNumberPrefix varchar(2)
	, TrackingNumberAct varchar(6)
	, TrackingNumberShipping varchar(2)
	, TrackingNumberParsel varchar(8))

---------------------------------------------------------------
---------------------------------------------------------------
--
--  Copy records into the Parsing Table.
--
---------------------------------------------------------------
---------------------------------------------------------------

INSERT INTO tblTrackingParse
			(Tracking_ID, EmployeeID, MachineName, BoxNumber, FileNumber, TrackingDate)

SELECT	TOP (100) PERCENT Tracking_ID, EmployeeID, MachineName, BoxNumber, FileNumber, TrackingDate
FROM	tblTrackingTable

WHERE		(TrackingDate BETWEEN CONVERT(DATETIME, @DateParam1, 102) 
				AND	DATEADD(s, -1, CONVERT(DATETIME, @DateParam2, 102))) 
AND			(FileNumber NOT LIKE '.box.end.')

ORDER BY	TrackingDate DESC

---------------------------------------------------------------
---------------------------------------------------------------
--
--  Change all File Numbers to UpperCase
--
---------------------------------------------------------------
---------------------------------------------------------------

UPDATE tblTrackingParse 
	SET tblTrackingParse.FileNumber = 
		Upper(tblTrackingParse.FileNumber)

---------------------------------------------------------------
---------------------------------------------------------------
--
--  Make sure all File Numbers are 9 digits long
--
---------------------------------------------------------------
---------------------------------------------------------------

UPDATE tblTrackingParse SET tblTrackingParse.FileNumber = 
	case 
		when ascii(substring(FileNumber,3,1))> 65 then FileNumber --If the 3-rd char is a letter, return the code
		when ascii(substring(FileNumber,2,1))> 65 then 'Second char LETTER' --If the 2-nd char is a letter, show error text
		when len(FileNumber)<10 then left(FileNumber,1) + replicate('0', 10-len(FileNumber)) 
			+ right(FileNumber, len(FileNumber)-1) --pad with zeroes
--      else 'Too long: ' + cast(len(FileNumber) as varchar(3)) + ' CHARS' 
	end 
FROM tblTrackingParse

---------------------------------------------------------------
---------------------------------------------------------------
--
--  Parse all the UPS tracking Numbers for reporting on
--  the method of shipment.
--
---------------------------------------------------------------
---------------------------------------------------------------

UPDATE tblTrackingParse 
SET tblTrackingParse.TrackingNumberPrefix = upper(substring(BoxNumber,1,2))
	, tblTrackingParse.TrackingNumberAct = upper(substring(BoxNumber,3,6))
	, tblTrackingParse.TrackingNumberShipping = upper(substring(BoxNumber,9,2))
	, tblTrackingParse.TrackingNumberParsel = upper(substring(BoxNumber,11,8))
FROM tblTrackingParse
WHERE (((substring(BoxNumber,1,2))='1Z'))

Thanks

John Fuhrman

 

[sparkbyte]

It appears to have something to do with the schema. Because the table is showing the Domain/Username as the schema.

Thanks

John Fuhrman

 

[gmmastros]

put dbo in front of the table name when you create it, like this...

[tt][blue]
CREATE TABLE
[!]dbo.[/!]tblTrackingParse(Tracking_ID int
, EmployeeID varchar(50)
, MachineName varchar(20)
[/blue][/tt]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sparkbyte]

OK, another star for George!!

My pride takes it on the nose again..................................

It's always the simple solutions too.

That did it. THANKS!!

Thanks

John Fuhrman

 

[sparkbyte]

So then the question begging to get asked is...

Should I then always use the format [schema.tablename]

such as

Select [fields] from dbo.tblTracking

UPDATE dbo.tblTrackingParse
SET dbo.tblTrackingParse.FileNumber =
Upper(dbo.tblTrackingParse.FileNumber)


INSERT INTO dbo.tblTrackingParse
(Tracking_ID, EmployeeID, MachineName, BoxNumber, FileNumber, TrackingDate)

SELECT TOP (100) PERCENT Tracking_ID, EmployeeID, MachineName, BoxNumber, FileNumber, TrackingDate
FROM dbo.tblTrackingTable


Thanks

John Fuhrman

 

[gmmastros]

What version of SQL Server are you using?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sparkbyte]

2005

Thanks

John Fuhrman

 

[gmmastros]

With SQL 2000, I would recommend that you use dbo. in front of all your table references. With SQL 2005, you may want to consider actually using schemas to help you organize your tables and procedures.

In either case, it's best to use the schema in your code. It won't make a huge difference, but the performance may be slightly better. It's a good practice to get in to.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sparkbyte]

Thanks for the advise. I will start updating the VBA and queries to include the schema references.

Thanks

John Fuhrman