Help .SQL - is it working? - 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)

Help .SQL - is it working?

Author

Topic

Mallen
Starting Member

27 Posts

Posted - 2010-05-12 : 14:52:41

I'm new to the forum and new to sql. I have just started at a company that would like to move from Access 2007 to SQL 2005 (slowly). I have a query in Access 2007 that I am having trouble converting to SQL 2005.
The original Access query looks like this:
----
UPDATE (Collector_Move_Table INNER JOIN dbo_UVW_SR_ACCOUNT ON Collector_Move_Table.AccountID = dbo_UVW_SR_ACCOUNT.AccountID) INNER JOIN dbo_Bunch ON Collector_Move_Table.Move_To = dbo_Bunch.Description
SET Collector_Move_Table.[Exception] = False, dbo_UVW_SR_ACCOUNT.QueueID = [dbo_Bunch]![BunchID]
WHERE (((Collector_Move_Table.Queue) Like "Dia*" Or (Collector_Move_Table.Queue) Like "Ski*"));
----
I am getting a syntax error for the first '(' after Update but I am unsure of how else to properly join the tables I need for this query. Does anyone know how to rewrite this query to run in SQL 2005 please?

Thank you.
Michelle

Mallen
Starting Member

27 Posts

Posted - 2010-05-12 : 15:45:28

If it helps here is the rest of the procedure I'm trying to get working on the SQL server so that we can have it run every night as a procedure instead of me manually running it in Access.
-----
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Mbobo
-- Create date: 11 May 2010
-- Description: Collectors move accounts to their queue
-- =============================================
CREATE PROCEDURE CollectorAccountMoveTEST
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Create a temporary table containing all accounts flagged for move
SELECT
dbo_UserField.UserDefinedID AS Move_To,
dbo_UserData.AccountID,
dbo_Package.Description AS Package,
dbo_UVW_SR_ACCOUNT.Balance,
dbo_Lookup.LookupValue AS Status,
dbo_Bunch.Description AS Queue, TRUE AS [Exception],
dbo_Employee.LoginName AS Moved_By INTO Collector_Move_Table

FROM dbo_Employee INNER JOIN (((((dbo_UserData INNER JOIN dbo_UserField
ON dbo_UserData.UserDefinedID = dbo_UserField.UserDefinedID)
INNER JOIN dbo_UVW_SR_ACCOUNT ON dbo_UserData.AccountID = dbo_UVW_SR_ACCOUNT.AccountID)
INNER JOIN dbo_Package ON dbo_UVW_SR_ACCOUNT.PackageID = dbo_Package.PackageID)
INNER JOIN dbo_Lookup ON dbo_UVW_SR_Account.ProcessStatusID = dbo_Lookup.LookupID)
INNER JOIN dbo_Bunch ON dbo-UVW_SR_Account.QueueID = dbo_Bunch.BunchID) ON dbo_Employee.EmployeeID = dbo_UserData.EntryUserID
WHERE dbo_UserField.UserDefinedID = 9;

-- Update the BunchID of each flagged account to the number of the
-- Collector's Que. Flag an exception to any requests not from the
-- Dialer or Skip queues.

UPDATE Collector_Move_Table INNER JOIN dbo_UVW_SR_ACCOUNT ON Collector_Move_Table.AccountID = dbo_UVW_SR_ACCOUNT.AccountID
INNER JOIN (dbo_Bunch ON Collector_Move_Table.Move_To = dbo_Bunch.Description)
SET Collector_Move_Table.[Exception]= False, dbo_SR_ACCOUNT.Queue.ID = [dbo_Bunch]![BunchID]
WHERE ((Collector_Move_Table.Queue) Like "Dia*" OR (Collector_Move_Table.Queue) Like "Ski*");

-- Generate a report table showing all updated queues and exceptions.
SELECT
Collector_Move_Table.Move_To,
Collector_Move_Table.Queue AS Moved_From,
Collector_Move_Table.Moved_By,
Collector_Move_Table.Status,
Collector_Move_Table.AccountID,
Collector_Move_Table.Balance,
Collector_Move_Table.Package,
Collector_Move_Table.Exception
From Collector_Move_Table;

-- Clear all flags to move data
DELETE dbo_UserData.UserDefinedID
FROM dbo_UserData
WHERE dbo_UserData.UserDefinedID = 9;

END

GO
-----
Any help would be appreciated. I'm still trying to figure out how to get things working in sql.

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-05-12 : 19:43:40

I hope this modification work for you.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Mbobo
-- Create date: 11 May 2010
-- Description: Collectors move accounts to their queue
-- =============================================
CREATE PROCEDURE CollectorAccountMoveTEST 
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Create a temporary table containing all accounts flagged for move
SELECT dbo_UserField.UserDefinedID AS Move_To, 
		dbo_UserData.AccountID, 
		dbo_Package.Description AS Package, 
		dbo_UVW_SR_ACCOUNT.Balance, 
		dbo_Lookup.LookupValue AS Status,
		dbo_Bunch.Description AS Queue, TRUE AS [Exception],
		dbo_Employee.LoginName AS Moved_By INTO Collector_Move_Table
FROM dbo_Employee dbo_UserData INNER JOIN dbo_UserField ON dbo_UserData.UserDefinedID = dbo_UserField.UserDefinedID
	INNER JOIN dbo_UVW_SR_ACCOUNT 
		ON dbo_UserData.AccountID = dbo_UVW_SR_ACCOUNT.AccountID
	INNER JOIN dbo_Package 
		ON dbo_UVW_SR_ACCOUNT.PackageID = dbo_Package.PackageID
	INNER JOIN dbo_Lookup 
		ON dbo_UVW_SR_Account.ProcessStatusID = dbo_Lookup.LookupID
	INNER JOIN dbo_Bunch 
		ON dbo-UVW_SR_Account.QueueID = dbo_Bunch.BunchID
	INNER JOIN dbo_Employee 
		ON dbo_Employee.EmployeeID = dbo_UserData.EntryUserID
WHERE dbo_UserField.UserDefinedID = 9;

UPDATE Collector_Move_Table 
SET Collector_Move_Table.[Exception]= False, 
    dbo_SR_ACCOUNT.Queue.ID = [dbo_Bunch].[BunchID]
FROM Collector_Move_Table 
	INNER JOIN dbo_UVW_SR_ACCOUNT 
		ON Collector_Move_Table.AccountID = dbo_UVW_SR_ACCOUNT.AccountID 
	INNER JOIN dbo_Bunch 
		ON Collector_Move_Table.Move_To = dbo_Bunch.Description 
WHERE ((Collector_Move_Table.Queue) Like 'Dia%' OR (Collector_Move_Table.Queue) Like 'Ski%');

-- Generate a report table showing all updated queues and exceptions.
SELECT Collector_Move_Table.Move_To, 
		Collector_Move_Table.Queue AS Moved_From,
		Collector_Move_Table.Moved_By,
		Collector_Move_Table.Status,
		Collector_Move_Table.AccountID,
		Collector_Move_Table.Balance,
		Collector_Move_Table.Package,
		Collector_Move_Table.Exception
From Collector_Move_Table;

-- Clear all flags to move data
DELETE dbo_UserData.UserDefinedID
FROM dbo_UserData
WHERE dbo_UserData.UserDefinedID = 9;

END

GO

Mallen
Starting Member

27 Posts

Posted - 2010-05-13 : 11:55:48

Thank you so much, using the FROM did let it execute with no errors BUT (This shows how new I really am). I can't tell if it actually did anything.

I click on the .sql to open it. It opens in SQL Server Management Studio. I hit 'Execute' up on the toolbar and it says 'Commands Completed Successfully'. It doesn't show me the table results I asked for or anything. Did it store the table somewhere I have to go look at it? How does this work?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-13 : 12:41:06

by clicking execute you're creating the procedure rather than executing it. For executing use

EXEC CollectorAccountMoveTEST

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Mallen
Starting Member

27 Posts

Posted - 2010-05-13 : 14:04:54

Oh, of course. Thank you. It seems I am not finished debugging the original query then. When I do an EXEC CollectorAccountMoveTEST and got the error 'Line 13, Invalid object name 'dbo_Employee'. I have checked all my sql permissions and should have access to dbo_Employee as it is stored on my db802(test database) just like every other table I called in there. The spelling and everything seems to match. I'm at a loss as to why it would error on that table and none of the other tables in the database. Is it because of how it is used in the FROM statement?

Here is my procedure now:
---
USE [DB802]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ===
-- Author: Mbobo
-- Create date: 11 May 2010
-- Description: Collectors move accounts to their queue
-- ===
CREATE PROCEDURE CollectorAccountMoveTEST
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Create a temporary table containing all accounts flagged for move
SELECT
dbo_UserField.UserDefinedID AS Move_To,
dbo_UserData.AccountID,
dbo_Package.Description AS Package,
dbo_UVW_SR_ACCOUNT.Balance,
dbo_Lookup.LookupValue AS Status,
dbo_Bunch.Description AS Queue, TRUE AS [Exception],
dbo_Employee.LoginName AS Moved_By
INTO Collector_Move_Table

FROM dbo_Employee INNER JOIN (((((dbo_UserData INNER JOIN dbo_UserField
ON dbo_UserData.UserDefinedID = dbo_UserField.UserDefinedID)
INNER JOIN dbo_UVW_SR_ACCOUNT ON dbo_UserData.AccountID = dbo_UVW_SR_ACCOUNT.AccountID)
INNER JOIN dbo_Package ON dbo_UVW_SR_ACCOUNT.PackageID = dbo_Package.PackageID)
INNER JOIN dbo_Lookup ON dbo_UVW_SR_Account.ProcessStatusID = dbo_Lookup.LookupID)
INNER JOIN dbo_Bunch ON dbo-UVW_SR_Account.QueueID = dbo_Bunch.BunchID) ON dbo_Employee.EmployeeID = dbo_UserData.EntryUserID
WHERE dbo_UserField.UserDefinedID = 9;

-- Update the BunchID of each flagged account to the number of the
-- Collector's Que. Flag an exception to any requests not from the
-- Dialer or Skip queues.

UPDATE Collector_Move_Table
SET Collector_Move_Table.[Exception]= False,
dbo_SR_ACCOUNT.Queue.ID = [dbo_Bunch].[BunchID]
FROM Collector_Move_Table
INNER JOIN dbo_UVW_SR_ACCOUNT
ON Collector_Move_Table.AccountID = dbo_UVW_SR_ACCOUNT.AccountID
INNER JOIN dbo_Bunch
ON Collector_Move_Table.Move_To = dbo_Bunch.Description
WHERE ((Collector_Move_Table.Queue) Like 'Dia%' OR (Collector_Move_Table.Queue) Like 'Ski%');

-- Generate a report table showing all updated queues and exceptions.
SELECT
Collector_Move_Table.Move_To,
Collector_Move_Table.Queue AS Moved_From,
Collector_Move_Table.Moved_By,
Collector_Move_Table.Status,
Collector_Move_Table.AccountID,
Collector_Move_Table.Balance,
Collector_Move_Table.Package,
Collector_Move_Table.Exception
From Collector_Move_Table;

-- Clear all flags to move data
DELETE dbo_UserData.UserDefinedID
FROM dbo_UserData
WHERE dbo_UserData.UserDefinedID = 9;

END

GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-13 : 14:07:30

is name of table dbo_Employee? if dbo is schema name you should refer it as dbo.Employee

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Mallen
Starting Member

27 Posts

Posted - 2010-05-13 : 14:32:23

Oh you're right! When I look in SQL Server Management Studio under the DB802 database all of those tables are listed as dbo.Tablename. So it would be dbo.Employee. BUT... then do I have to type dbo.Employee.LoginName as the syntax for the field? And why did it only give me an error on the Employee table when every table I refrenced before that had the same syntax? *confused*

Mallen
Starting Member

27 Posts

Posted - 2010-05-13 : 15:02:44

Ok.. New problem :(

In my first SELECT statement I am populating a table that I call the data from later to make a report. I have in there to add a new column named Exception and make it default to true. Clearly I need to change the syntax from Access to SQL and I looked up the correct sql syntax to set a default for a column BUT how do I add that column to my table? Do I do a select of all the other columns and then an ADD of that column? Do I write an alter table after that select statement creates the table?
Thanks for all the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources