![SoftTree Technologies](templates/GreenTech/images/logo.gif) |
SoftTree Technologies
Technical Support Forums
|
|
Author |
Message |
jlangland
Joined: 27 Apr 2016 Posts: 16 Country: United States |
|
SQL 2016 Code Generator Error brackets/quotes wrong place |
|
Hi,
I've just recently realized that there is a problem with 9.5 and SQL 2016 server (maybe elsewhere as well).
I am attempting to use sql server CRUD generator (which worked fine in SSMS 2014 and SQL 2014 with the version I was using prior to 9.5)
I have replicated this on multiple workstations with independent installs, multiple SQL 2016 servers and completely different db's. Since these are different workstations, I am sure that there is no common change to any template. I.e. should be the default templates. I've modified some in the distant past, but these all seem to be the default templates, not ones I've modified, and on first machine, I've never modified any templates.
While I first discovered it using SSMS 2017, and thought the problem was there, I've since tested in the SQLA SQL Editor against SQL 2016 server.
When using the CRUD code generator from the SQLA Editor on a table I get:
1/6/2018 5:43:54 PM Start Code Generator "SQL Server CRUD Procedures Template"
1/6/2018 5:43:54 PM Process object [CUSIPDB].[dbo].[cfi_Codes]
1/6/2018 5:43:57 PM Msg 102, Level 15, State 1, Proc "p_Deletecfi_Codes", Incorrect syntax near ')'.
1/6/2018 5:43:57 PM Msg 156, Level 15, State 1, Proc "p_Deletecfi_Codes", Incorrect syntax near the keyword 'SELECT'.
1/6/2018 5:43:57 PM Msg 102, Level 15, State 1, Proc "p_Getcfi_Codes", Incorrect syntax near ')'.
1/6/2018 5:43:57 PM Msg 156, Level 15, State 1, Proc "p_Getcfi_Codes", Incorrect syntax near the keyword 'SELECT'.
1/6/2018 5:43:57 PM Msg 134, Level 15, State 1, Proc "p_Savecfi_Codes", The variable name '@id' has already been declared. Variable names must be unique within a query batch or stored procedure.
1/6/2018 5:43:57 PM Msg 102, Level 15, State 1, Proc "p_Savecfi_Codes", Incorrect syntax near ')'.
1/6/2018 5:43:57 PM Msg 156, Level 15, State 1, Proc "p_Savecfi_Codes", Incorrect syntax near the keyword 'ELSE'.
1/6/2018 5:43:57 PM Msg 156, Level 15, State 1, Proc "p_Savecfi_Codes", Incorrect syntax near the keyword 'END'.
1/6/2018 5:43:57 PM End Code Generator
When using it in SSMS 2017 (same table)
1/6/2018 5:49:05 PM Start Code Generator "SQL Server CRUD Procedures Template"
1/6/2018 5:49:05 PM Process object [CUSIPDB].[dbo].[cfi_Codes]
1/6/2018 5:49:05 PM Msg 105, Level 15, State 1, Unclosed quotation mark after the character string 'cfi_Codes]
'.
1/6/2018 5:49:05 PM Msg 102, Level 15, State 1, Incorrect syntax near 'cfi_Codes]
'.
1/6/2018 5:49:05 PM Msg 103, Level 15, State 4, The identifier that starts with 'cfi_Codes]
/***********************************************************
* Code generated by SoftTree SQL Assistant © v9.5.444
' is too long. Maximum length is 128.
1/6/2018 5:49:05 PM Msg 117, Level 15, State 1, The object name '[CUSIPDB.dbo.cfi_Codes].[p_Delete[CUSIPDB.dbo.cfi_Codes]
/***********************************************************
* Code generated by SoftTree SQL Assistant © v9.5.444
.dbo.cfi_Codes' contains more than the maximum number of prefixes. The maximum is 2.
1/6/2018 5:49:05 PM Msg 117, Level 15, State 1, The object name '[CUSIPDB.dbo.cfi_Codes].[[CUSIPDB.dbo.cfi_Codes]
WHERE /* Cannot find key columns in [CUSIPDB.dbo.cfi_Codes' contains more than the maximum number of prefixes. The maximum is 3.
1/6/2018 5:49:05 PM Msg 105, Level 15, State 1, Unclosed quotation mark after the character string 'cfi_Codes]
'.
1/6/2018 5:49:05 PM Msg 102, Level 15, State 1, Incorrect syntax near 'cfi_Codes]
'.
1/6/2018 5:49:05 PM Msg 103, Level 15, State 4, The identifier that starts with 'cfi_Codes]
/***********************************************************
* Code generated by SoftTree SQL Assistant © v9.5.444
' is too long. Maximum length is 128.
1/6/2018 5:49:05 PM Msg 117, Level 15, State 1, The object name '[CUSIPDB.dbo.cfi_Codes].[p_Get[CUSIPDB.dbo.cfi_Codes]
/***********************************************************
* Code generated by SoftTree SQL Assistant © v9.5.444
.dbo.cfi_Codes' contains more than the maximum number of prefixes. The maximum is 2.
1/6/2018 5:49:05 PM Msg 117, Level 15, State 1, The object name '[CUSIPDB.dbo.cfi_Codes].[[CUSIPDB.dbo.cfi_Codes]
WHERE /* Cannot find key columns in [CUSIPDB.dbo.cfi_Codes' contains more than the maximum number of prefixes. The maximum is 3.
1/6/2018 5:49:05 PM Msg 105, Level 15, State 1, Unclosed quotation mark after the character string 'cfi_Codes]
'.
1/6/2018 5:49:05 PM Msg 102, Level 15, State 1, Incorrect syntax near 'cfi_Codes]
'.
1/6/2018 5:49:05 PM Msg 103, Level 15, State 4, The identifier that starts with 'cfi_Codes]
/***********************************************************
* Code generated by SoftTree SQL Assistant © v9.5.444
' is too long. Maximum length is 128.
1/6/2018 5:49:05 PM Msg 117, Level 15, State 1, The object name '[CUSIPDB.dbo.cfi_Codes].[p_Save[CUSIPDB.dbo.cfi_Codes]
/***********************************************************
* Code generated by SoftTree SQL Assistant © v9.5.444
.dbo.cfi_Codes' contains more than the maximum number of prefixes. The maximum is 2.
1/6/2018 5:49:05 PM Msg 117, Level 15, State 1, The object name '[CUSIPDB.dbo.cfi_Codes].[[CUSIPDB.dbo.cfi_Codes]
WHERE /* Cannot find key columns in [CUSIPDB.dbo.cfi_Codes' contains more than the maximum number of prefixes. The maximum is 3.
1/6/2018 5:49:05 PM Msg 117, Level 15, State 1, The object name '[CUSIPDB.dbo.cfi_Codes].[[CUSIPDB.dbo.cfi_Codes]
(
[Scheme' contains more than the maximum number of prefixes. The maximum is 3.
1/6/2018 5:49:05 PM Msg 117, Level 15, State 1, The object name '[CUSIPDB.dbo.cfi_Codes].[[CUSIPDB.dbo.cfi_Codes]
SET [Scheme' contains more than the maximum number of prefixes. The maximum is 3.
1/6/2018 5:49:05 PM End Code Generator
Depending on the table and DB I get some variants of these messages, sometimes involving length of code exceeding 128.
All seem to be tied to quote identifier issues. E.g. as you can see brackets are in wacky locations causing interpretation issues.
Jerry
|
|
Sat Jan 06, 2018 7:55 pm |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7855
|
|
|
|
May I ask you to use Save Script button in the Code Generator dialog instead of the Execute and then open the saved file and find out what's wrong with the script? From the many syntax error messages it's unclear where the problem starts. If you don't mind, can you please post the output script here too so we can see what was generated?
|
|
Sun Jan 07, 2018 12:41 am |
|
![](templates/GreenTech/images/spacer.gif) |
jlangland
Joined: 27 Apr 2016 Posts: 16 Country: United States |
|
|
|
Actually, should have thought about that as well. Doh.
It seems to be inserting random extra quote marks.
This one is from SSMS 2017, the one from SQLA Editor is a bit different but still has quote issues and is below this one.
---------------------------------------------------------
IF object_id('[[CUSIPDB].[dbo].[cfi_Codes]].[p_Delete[CUSIPDB].[dbo].[cfi_Codes]]') IS NOT NULL
DROP PROCEDURE [[CUSIPDB].[dbo].[cfi_Codes]].[p_Delete[CUSIPDB].[dbo].[cfi_Codes]]
GO
CREATE PROCEDURE [[CUSIPDB].[dbo].[cfi_Codes]].[p_Delete[CUSIPDB].[dbo].[cfi_Codes]]
/***********************************************************
* Code generated by SoftTree SQL Assistant © v9.5.444
*
* Procedure description: This procedure is used for
* deleting records from table
* [CUSIPDB].[dbo].[cfi_Codes]
* Date: 1/8/2018
* Author: jlangland
*
* Changes
* Date Modified By Comments
************************************************************
* 1/8/2018 jlangland Initial version
************************************************************/
(
@id int
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @rowcount INT, @error INT
-- start transaction
BEGIN TRANSACTION
-- delete record using the specified criteria, 1 record deletion is expected
DELETE FROM [[CUSIPDB].[dbo].[cfi_Codes]].[[CUSIPDB].[dbo].[cfi_Codes]]
WHERE [id] = @[id]
-- capture operation completion code and number of records affected
SELECT @rowcount = @@ROWCOUNT,
@error = @@ERROR
-- check for errors
IF @error != 0
BEGIN
-- cancel transaction, undo changes
ROLLBACK TRANSACTION
-- report error and exit with non-zero exit code
RAISERROR('Unable to delete record. See previous message for details.', 16, 1)
RETURN @error
END
-- check for rows updated
IF @rowcount != 1
BEGIN
-- cancel transaction, undo changes
ROLLBACK TRANSACTION
-- report error and exit with non-zero exit code
IF @rowcount = 0
RAISERROR('Warning. No records found for the specified criteria, while just 1 was expected.', 10, 1)
ELSE
RAISERROR('Critical error. More than 1 record found for the specified criteria, while just 1 was expected.', 16, 1)
RETURN 1
END
-- commit changes and return 0 code indicating successful completion
COMMIT TRANSACTION
RETURN 0
END
GO
-- uncomment the following 2 lines if you want to grant procedure permissions to some other user or role
-- GRANT EXECUTE ON [[CUSIPDB].[dbo].[cfi_Codes]].[p_Delete[CUSIPDB].[dbo].[cfi_Codes]] TO [some user or role here]
-- GO
IF object_id('[[CUSIPDB].[dbo].[cfi_Codes]].[p_Get[CUSIPDB].[dbo].[cfi_Codes]]') IS NOT NULL
DROP PROCEDURE [[CUSIPDB].[dbo].[cfi_Codes]].[p_Get[CUSIPDB].[dbo].[cfi_Codes]]
GO
CREATE PROCEDURE [[CUSIPDB].[dbo].[cfi_Codes]].[p_Get[CUSIPDB].[dbo].[cfi_Codes]]
/***********************************************************
* Code generated by SoftTree SQL Assistant © v9.5.444
*
* Procedure description: This procedure is used for
* retrieving records from table
* [CUSIPDB].[dbo].[cfi_Codes]
* Date: 1/8/2018
* Author: jlangland
*
* Changes
* Date Modified By Comments
************************************************************
* 1/8/2018 jlangland Initial version
************************************************************/
(
@id int
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @rowcount INT, @error INT
-- search and return records
SELECT [id],
[Scheme],
[CFI],
[Category],
[CategoryName],
[Group],
[GroupName],
[Attribute1],
[Attribute1Name],
[Attribute1Value],
[Attribute1Description],
[Attribute2],
[Attribute2Name],
[Attribute2Value],
[Attribute2Description],
[Attribute3],
[Attribute3Name],
[Attribute3Value],
[Attribute3Description],
[Attribute4],
[Attribute4Name],
[Attribute4Value],
[Attribute4Description]
FROM [[CUSIPDB].[dbo].[cfi_Codes]].[[CUSIPDB].[dbo].[cfi_Codes]]
WHERE [id] = @[id]
-- capture operation completion code and number of records affected
SELECT @rowcount = @@ROWCOUNT,
@error = @@ERROR
IF @error != 0
BEGIN
-- report error and exit with non-zero exit code
RAISERROR('Unable to retrieve records. See previous message for details.', 16, 1)
RETURN @error
END
IF @rowcount = 0
BEGIN
-- report error and exit with non-zero exit code
RAISERROR('Critical error. No records found for the specified criteria.', 16, 1)
RETURN 1
END
IF @rowcount > 1
BEGIN
-- report error and exit with non-zero exit code
RAISERROR('Warning. More than 1 record found for the specified criteria, while just 1 is expected.', 10, 1)
RETURN 1
END
-- commit changes and return 0 code indicating successful completion
RETURN 0
END
GO
-- uncomment the following 2 lines if you want to grant procedure permissions to some other user or role
-- GRANT EXECUTE ON [[CUSIPDB].[dbo].[cfi_Codes]].[p_Get[CUSIPDB].[dbo].[cfi_Codes]] TO [some user or role here]
-- GO
IF object_id('[[CUSIPDB].[dbo].[cfi_Codes]].[p_Save[CUSIPDB].[dbo].[cfi_Codes]]') IS NOT NULL
DROP PROCEDURE [[CUSIPDB].[dbo].[cfi_Codes]].[p_Save[CUSIPDB].[dbo].[cfi_Codes]]
GO
CREATE PROCEDURE [[CUSIPDB].[dbo].[cfi_Codes]].[p_Save[CUSIPDB].[dbo].[cfi_Codes]]
/***********************************************************
* Code generated by SoftTree SQL Assistant © v9.5.444
*
* Procedure description: This procedure is used for adding
* and updating records in table
* [CUSIPDB].[dbo].[cfi_Codes]
* Date: 1/8/2018
* Author: jlangland
*
* Changes
* Date Modified By Comments
************************************************************
* 1/8/2018 jlangland Initial version
************************************************************/
(
@id int = NULL,
@Scheme char(3) = NULL,
@CFI char(6) = NULL,
@Category char(1) = NULL,
@CategoryName varchar(100) = NULL,
@Group char(1) = NULL,
@GroupName varchar(100) = NULL,
@Attribute1 char(1) = NULL,
@Attribute1Name varchar(100) = NULL,
@Attribute1Value varchar(100) = NULL,
@Attribute1Description varchar(255) = NULL,
@Attribute2 char(1) = NULL,
@Attribute2Name varchar(100) = NULL,
@Attribute2Value varchar(100) = NULL,
@Attribute2Description varchar(255) = NULL,
@Attribute3 char(1) = NULL,
@Attribute3Name varchar(100) = NULL,
@Attribute3Value varchar(100) = NULL,
@Attribute3Description varchar(255) = NULL,
@Attribute4 char(1) = NULL,
@Attribute4Name varchar(100) = NULL,
@Attribute4Value varchar(100) = NULL,
@Attribute4Description varchar(255) = NULL
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @rowcount INT, @error INT, @id INT
-- start transaction
BEGIN TRANSACTION
-- check if the specified record already exists, if yes, update it, if no, create it
IF NOT EXISTS
(
SELECT *
FROM [[CUSIPDB].[dbo].[cfi_Codes]].[[CUSIPDB].[dbo].[cfi_Codes]]
WHERE [id] = @[id]
)
BEGIN
-- insert new record
INSERT INTO [[CUSIPDB].[dbo].[cfi_Codes]].[[CUSIPDB].[dbo].[cfi_Codes]]
(
[Scheme],
[CFI],
[Category],
[CategoryName],
[Group],
[GroupName],
[Attribute1],
[Attribute1Name],
[Attribute1Value],
[Attribute1Description],
[Attribute2],
[Attribute2Name],
[Attribute2Value],
[Attribute2Description],
[Attribute3],
[Attribute3Name],
[Attribute3Value],
[Attribute3Description],
[Attribute4],
[Attribute4Name],
[Attribute4Value],
[Attribute4Description]
)
VALUES
(
@Scheme,
@CFI,
@Category,
@CategoryName,
@Group,
@GroupName,
@Attribute1,
@Attribute1Name,
@Attribute1Value,
@Attribute1Description,
@Attribute2,
@Attribute2Name,
@Attribute2Value,
@Attribute2Description,
@Attribute3,
@Attribute3Name,
@Attribute3Value,
@Attribute3Description,
@Attribute4,
@Attribute4Name,
@Attribute4Value,
@Attribute4Description
)
END
ELSE
BEGIN
-- update existing record
UPDATE [[CUSIPDB].[dbo].[cfi_Codes]].[[CUSIPDB].[dbo].[cfi_Codes]]
SET [Scheme] = @Scheme,
[CFI] = @CFI,
[Category] = @Category,
[CategoryName] = @CategoryName,
[Group] = @Group,
[GroupName] = @GroupName,
[Attribute1] = @Attribute1,
[Attribute1Name] = @Attribute1Name,
[Attribute1Value] = @Attribute1Value,
[Attribute1Description] = @Attribute1Description,
[Attribute2] = @Attribute2,
[Attribute2Name] = @Attribute2Name,
[Attribute2Value] = @Attribute2Value,
[Attribute2Description] = @Attribute2Description,
[Attribute3] = @Attribute3,
[Attribute3Name] = @Attribute3Name,
[Attribute3Value] = @Attribute3Value,
[Attribute3Description] = @Attribute3Description,
[Attribute4] = @Attribute4,
[Attribute4Name] = @Attribute4Name,
[Attribute4Value] = @Attribute4Value,
[Attribute4Description] = @Attribute4Description
WHERE [id] = @[id]
END
-- capture operation completion code and number of records affected
SELECT @rowcount = @@ROWCOUNT,
@error = @@ERROR,
@id = SCOPE_IDENTITY()
IF @error != 0
BEGIN
-- cancel transaction, undo changes
ROLLBACK TRANSACTION
-- report error and exit with non-zero exit code
RAISERROR('Unable to update or insert new record. See previous message for details.', 16, 1)
RETURN @error
END
IF @rowcount != 1
BEGIN
-- cancel transaction, undo changes
ROLLBACK TRANSACTION
-- report error and exit with non-zero exit code
RAISERROR('Critical error. More than 1 record found for the specified criteria, just 1 is expected.', 16, 1)
RETURN 1
END
-- commit changes and return 0 code indicating successful completion
COMMIT TRANSACTION
-- if operation type 'Add record', return result set with the last inserted column value
IF @id IS NOT NULL
SELECT @id AS NewRecordID
RETURN 0
END
GO
-- uncomment the following 2 lines if you want to grant procedure permissions to some other user or role
-- GRANT EXECUTE ON [[CUSIPDB].[dbo].[cfi_Codes]].[p_Save[CUSIPDB].[dbo].[cfi_Codes]] TO [some user or role here]
-- GO
SQLA Editor-------------------------------------------------------------------------------------------------------------------------------
IF object_id('[[CUSIPDB].[dbo].[cfi_Codes]].[p_Delete[CUSIPDB].[dbo].[cfi_Codes]]') IS NOT NULL
DROP PROCEDURE [[CUSIPDB].[dbo].[cfi_Codes]].[p_Delete[CUSIPDB].[dbo].[cfi_Codes]]
GO
CREATE PROCEDURE [[CUSIPDB].[dbo].[cfi_Codes]].[p_Delete[CUSIPDB].[dbo].[cfi_Codes]]
/***********************************************************
* Code generated by SoftTree SQL Assistant © v9.5.444
*
* Procedure description: This procedure is used for
* deleting records from table
* [CUSIPDB].[dbo].[cfi_Codes]
* Date: 1/8/2018
* Author: jlangland
*
* Changes
* Date Modified By Comments
************************************************************
* 1/8/2018 jlangland Initial version
************************************************************/
(
@id int
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @rowcount INT, @error INT
-- start transaction
BEGIN TRANSACTION
-- delete record using the specified criteria, 1 record deletion is expected
DELETE FROM [[CUSIPDB].[dbo].[cfi_Codes]].[[CUSIPDB].[dbo].[cfi_Codes]]
WHERE [id] = @[id]
-- capture operation completion code and number of records affected
SELECT @rowcount = @@ROWCOUNT,
@error = @@ERROR
-- check for errors
IF @error != 0
BEGIN
-- cancel transaction, undo changes
ROLLBACK TRANSACTION
-- report error and exit with non-zero exit code
RAISERROR('Unable to delete record. See previous message for details.', 16, 1)
RETURN @error
END
-- check for rows updated
IF @rowcount != 1
BEGIN
-- cancel transaction, undo changes
ROLLBACK TRANSACTION
-- report error and exit with non-zero exit code
IF @rowcount = 0
RAISERROR('Warning. No records found for the specified criteria, while just 1 was expected.', 10, 1)
ELSE
RAISERROR('Critical error. More than 1 record found for the specified criteria, while just 1 was expected.', 16, 1)
RETURN 1
END
-- commit changes and return 0 code indicating successful completion
COMMIT TRANSACTION
RETURN 0
END
GO
-- uncomment the following 2 lines if you want to grant procedure permissions to some other user or role
-- GRANT EXECUTE ON [[CUSIPDB].[dbo].[cfi_Codes]].[p_Delete[CUSIPDB].[dbo].[cfi_Codes]] TO [some user or role here]
-- GO
IF object_id('[[CUSIPDB].[dbo].[cfi_Codes]].[p_Get[CUSIPDB].[dbo].[cfi_Codes]]') IS NOT NULL
DROP PROCEDURE [[CUSIPDB].[dbo].[cfi_Codes]].[p_Get[CUSIPDB].[dbo].[cfi_Codes]]
GO
CREATE PROCEDURE [[CUSIPDB].[dbo].[cfi_Codes]].[p_Get[CUSIPDB].[dbo].[cfi_Codes]]
/***********************************************************
* Code generated by SoftTree SQL Assistant © v9.5.444
*
* Procedure description: This procedure is used for
* retrieving records from table
* [CUSIPDB].[dbo].[cfi_Codes]
* Date: 1/8/2018
* Author: jlangland
*
* Changes
* Date Modified By Comments
************************************************************
* 1/8/2018 jlangland Initial version
************************************************************/
(
@id int
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @rowcount INT, @error INT
-- search and return records
SELECT [id],
[Scheme],
[CFI],
[Category],
[CategoryName],
[Group],
[GroupName],
[Attribute1],
[Attribute1Name],
[Attribute1Value],
[Attribute1Description],
[Attribute2],
[Attribute2Name],
[Attribute2Value],
[Attribute2Description],
[Attribute3],
[Attribute3Name],
[Attribute3Value],
[Attribute3Description],
[Attribute4],
[Attribute4Name],
[Attribute4Value],
[Attribute4Description]
FROM [[CUSIPDB].[dbo].[cfi_Codes]].[[CUSIPDB].[dbo].[cfi_Codes]]
WHERE [id] = @[id]
-- capture operation completion code and number of records affected
SELECT @rowcount = @@ROWCOUNT,
@error = @@ERROR
IF @error != 0
BEGIN
-- report error and exit with non-zero exit code
RAISERROR('Unable to retrieve records. See previous message for details.', 16, 1)
RETURN @error
END
IF @rowcount = 0
BEGIN
-- report error and exit with non-zero exit code
RAISERROR('Critical error. No records found for the specified criteria.', 16, 1)
RETURN 1
END
IF @rowcount > 1
BEGIN
-- report error and exit with non-zero exit code
RAISERROR('Warning. More than 1 record found for the specified criteria, while just 1 is expected.', 10, 1)
RETURN 1
END
-- commit changes and return 0 code indicating successful completion
RETURN 0
END
GO
-- uncomment the following 2 lines if you want to grant procedure permissions to some other user or role
-- GRANT EXECUTE ON [[CUSIPDB].[dbo].[cfi_Codes]].[p_Get[CUSIPDB].[dbo].[cfi_Codes]] TO [some user or role here]
-- GO
IF object_id('[[CUSIPDB].[dbo].[cfi_Codes]].[p_Save[CUSIPDB].[dbo].[cfi_Codes]]') IS NOT NULL
DROP PROCEDURE [[CUSIPDB].[dbo].[cfi_Codes]].[p_Save[CUSIPDB].[dbo].[cfi_Codes]]
GO
CREATE PROCEDURE [[CUSIPDB].[dbo].[cfi_Codes]].[p_Save[CUSIPDB].[dbo].[cfi_Codes]]
/***********************************************************
* Code generated by SoftTree SQL Assistant © v9.5.444
*
* Procedure description: This procedure is used for adding
* and updating records in table
* [CUSIPDB].[dbo].[cfi_Codes]
* Date: 1/8/2018
* Author: jlangland
*
* Changes
* Date Modified By Comments
************************************************************
* 1/8/2018 jlangland Initial version
************************************************************/
(
@id int = NULL,
@Scheme char(3) = NULL,
@CFI char(6) = NULL,
@Category char(1) = NULL,
@CategoryName varchar(100) = NULL,
@Group char(1) = NULL,
@GroupName varchar(100) = NULL,
@Attribute1 char(1) = NULL,
@Attribute1Name varchar(100) = NULL,
@Attribute1Value varchar(100) = NULL,
@Attribute1Description varchar(255) = NULL,
@Attribute2 char(1) = NULL,
@Attribute2Name varchar(100) = NULL,
@Attribute2Value varchar(100) = NULL,
@Attribute2Description varchar(255) = NULL,
@Attribute3 char(1) = NULL,
@Attribute3Name varchar(100) = NULL,
@Attribute3Value varchar(100) = NULL,
@Attribute3Description varchar(255) = NULL,
@Attribute4 char(1) = NULL,
@Attribute4Name varchar(100) = NULL,
@Attribute4Value varchar(100) = NULL,
@Attribute4Description varchar(255) = NULL
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @rowcount INT, @error INT, @id INT
-- start transaction
BEGIN TRANSACTION
-- check if the specified record already exists, if yes, update it, if no, create it
IF NOT EXISTS
(
SELECT *
FROM [[CUSIPDB].[dbo].[cfi_Codes]].[[CUSIPDB].[dbo].[cfi_Codes]]
WHERE [id] = @[id]
)
BEGIN
-- insert new record
INSERT INTO [[CUSIPDB].[dbo].[cfi_Codes]].[[CUSIPDB].[dbo].[cfi_Codes]]
(
[Scheme],
[CFI],
[Category],
[CategoryName],
[Group],
[GroupName],
[Attribute1],
[Attribute1Name],
[Attribute1Value],
[Attribute1Description],
[Attribute2],
[Attribute2Name],
[Attribute2Value],
[Attribute2Description],
[Attribute3],
[Attribute3Name],
[Attribute3Value],
[Attribute3Description],
[Attribute4],
[Attribute4Name],
[Attribute4Value],
[Attribute4Description]
)
VALUES
(
@Scheme,
@CFI,
@Category,
@CategoryName,
@Group,
@GroupName,
@Attribute1,
@Attribute1Name,
@Attribute1Value,
@Attribute1Description,
@Attribute2,
@Attribute2Name,
@Attribute2Value,
@Attribute2Description,
@Attribute3,
@Attribute3Name,
@Attribute3Value,
@Attribute3Description,
@Attribute4,
@Attribute4Name,
@Attribute4Value,
@Attribute4Description
)
END
ELSE
BEGIN
-- update existing record
UPDATE [[CUSIPDB].[dbo].[cfi_Codes]].[[CUSIPDB].[dbo].[cfi_Codes]]
SET [Scheme] = @Scheme,
[CFI] = @CFI,
[Category] = @Category,
[CategoryName] = @CategoryName,
[Group] = @Group,
[GroupName] = @GroupName,
[Attribute1] = @Attribute1,
[Attribute1Name] = @Attribute1Name,
[Attribute1Value] = @Attribute1Value,
[Attribute1Description] = @Attribute1Description,
[Attribute2] = @Attribute2,
[Attribute2Name] = @Attribute2Name,
[Attribute2Value] = @Attribute2Value,
[Attribute2Description] = @Attribute2Description,
[Attribute3] = @Attribute3,
[Attribute3Name] = @Attribute3Name,
[Attribute3Value] = @Attribute3Value,
[Attribute3Description] = @Attribute3Description,
[Attribute4] = @Attribute4,
[Attribute4Name] = @Attribute4Name,
[Attribute4Value] = @Attribute4Value,
[Attribute4Description] = @Attribute4Description
WHERE [id] = @[id]
END
-- capture operation completion code and number of records affected
SELECT @rowcount = @@ROWCOUNT,
@error = @@ERROR,
@id = SCOPE_IDENTITY()
IF @error != 0
BEGIN
-- cancel transaction, undo changes
ROLLBACK TRANSACTION
-- report error and exit with non-zero exit code
RAISERROR('Unable to update or insert new record. See previous message for details.', 16, 1)
RETURN @error
END
IF @rowcount != 1
BEGIN
-- cancel transaction, undo changes
ROLLBACK TRANSACTION
-- report error and exit with non-zero exit code
RAISERROR('Critical error. More than 1 record found for the specified criteria, just 1 is expected.', 16, 1)
RETURN 1
END
-- commit changes and return 0 code indicating successful completion
COMMIT TRANSACTION
-- if operation type 'Add record', return result set with the last inserted column value
IF @id IS NOT NULL
SELECT @id AS NewRecordID
RETURN 0
END
GO
-- uncomment the following 2 lines if you want to grant procedure permissions to some other user or role
-- GRANT EXECUTE ON [[CUSIPDB].[dbo].[cfi_Codes]].[p_Save[CUSIPDB].[dbo].[cfi_Codes]] TO [some user or role here]
-- GO
|
|
Mon Jan 08, 2018 11:51 am |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7855
|
|
|
|
Very interesting case. It might be connection or server version specific. It appears like fully qualified object name is inserted where a one part name is expected, resulting in extra [ ] brackets and weird names, leading to other side effects. Give me some time to experiment with that and try to reproduce it.
|
|
Mon Jan 08, 2018 2:28 pm |
|
![](templates/GreenTech/images/spacer.gif) |
jlangland
Joined: 27 Apr 2016 Posts: 16 Country: United States |
|
|
|
Have you had any luck reproducing it?
Still exists for me.
|
|
Mon Feb 12, 2018 12:55 pm |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7855
|
|
|
|
I think, there is a simple solution for this issue.
Open SQL Assistant's options dialog.
Click Code Generator tab
Select SQL Server CRUD Procedures Template
Select Delete template on the left, and in the template code on the rite, remove all [ and ] brackets
Do the same for Get and Save templates.
Hope this works for you.
|
|
Tue Feb 13, 2018 11:37 am |
|
![](templates/GreenTech/images/spacer.gif) |
jlangland
Joined: 27 Apr 2016 Posts: 16 Country: United States |
|
|
|
Good theory, however, this is causing it to duplicate prefixes on all database objects.
Below is the resultant code for one table generated after taking out the [ and ]
At one time, long ago, I had customized all the CRUD templates and if I still had those templates, I'd just replace the newer default templates with my modified older ones.
Unfortunately that was like 4 years ago. In recent years I haven't used them that much and just took the defaults.
_______________________________
IF object_id('[CurveMaster].[dbo].[coreTradedMunis].p_Delete[CurveMaster].[dbo].[coreTradedMunis]') IS NOT NULL
DROP PROCEDURE [CurveMaster].[dbo].[coreTradedMunis].p_Delete[CurveMaster].[dbo].[coreTradedMunis]
GO
CREATE PROCEDURE [CurveMaster].[dbo].[coreTradedMunis].p_Delete[CurveMaster].[dbo].[coreTradedMunis]
/***********************************************************
* Code generated by SoftTree SQL Assistant © v9.5.444
*
* Procedure description: This procedure is used for
* deleting records from table
* [CurveMaster].[dbo].[coreTradedMunis]
* Date: 2/19/2018
* Author: jlangland
*
* Changes
* Date Modified By Comments
************************************************************
* 2/19/2018 jlangland Initial version
************************************************************/
(
@mrid int
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @rowcount INT, @error INT
-- start transaction
BEGIN TRANSACTION
-- delete record using the specified criteria, 1 record deletion is expected
DELETE FROM [CurveMaster].[dbo].[coreTradedMunis].[CurveMaster].[dbo].[coreTradedMunis]
WHERE [mrid] = @[mrid]
-- capture operation completion code and number of records affected
SELECT @rowcount = @@ROWCOUNT,
@error = @@ERROR
-- check for errors
IF @error != 0
BEGIN
-- cancel transaction, undo changes
ROLLBACK TRANSACTION
-- report error and exit with non-zero exit code
RAISERROR('Unable to delete record. See previous message for details.', 16, 1)
RETURN @error
END
-- check for rows updated
IF @rowcount != 1
BEGIN
-- cancel transaction, undo changes
ROLLBACK TRANSACTION
-- report error and exit with non-zero exit code
IF @rowcount = 0
RAISERROR('Warning. No records found for the specified criteria, while just 1 was expected.', 10, 1)
ELSE
RAISERROR('Critical error. More than 1 record found for the specified criteria, while just 1 was expected.', 16, 1)
RETURN 1
END
-- commit changes and return 0 code indicating successful completion
COMMIT TRANSACTION
RETURN 0
END
GO
-- uncomment the following 2 lines if you want to grant procedure permissions to some other user or role
-- GRANT EXECUTE ON [CurveMaster].[dbo].[coreTradedMunis].p_Delete[CurveMaster].[dbo].[coreTradedMunis] TO some user or role here
-- GO
IF object_id('[CurveMaster].[dbo].[coreTradedMunis].p_Get[CurveMaster].[dbo].[coreTradedMunis]') IS NOT NULL
DROP PROCEDURE [CurveMaster].[dbo].[coreTradedMunis].p_Get[CurveMaster].[dbo].[coreTradedMunis]
GO
CREATE PROCEDURE [CurveMaster].[dbo].[coreTradedMunis].p_Get[CurveMaster].[dbo].[coreTradedMunis]
/***********************************************************
* Code generated by SoftTree SQL Assistant © v9.5.444
*
* Procedure description: This procedure is used for
* retrieving records from table
* [CurveMaster].[dbo].[coreTradedMunis]
* Date: 2/19/2018
* Author: jlangland
*
* Changes
* Date Modified By Comments
************************************************************
* 2/19/2018 jlangland Initial version
************************************************************/
(
@mrid int
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @rowcount INT, @error INT
-- search and return records
SELECT [mrid],
[SecurityIndexId],
[IssuerEntityIndexId],
[HarvestId],
[CUSIP],
[ISIN],
[spSecurityId],
[mdyIssueId],
[fitchIssueId],
[ID_BB_UNIQUE],
[spSecuritySymbolValue],
[PrimarySourceDB]
FROM [CurveMaster].[dbo].[coreTradedMunis].[CurveMaster].[dbo].[coreTradedMunis]
WHERE [mrid] = @[mrid]
-- capture operation completion code and number of records affected
SELECT @rowcount = @@ROWCOUNT,
@error = @@ERROR
IF @error != 0
BEGIN
-- report error and exit with non-zero exit code
RAISERROR('Unable to retrieve records. See previous message for details.', 16, 1)
RETURN @error
END
IF @rowcount = 0
BEGIN
-- report error and exit with non-zero exit code
RAISERROR('Critical error. No records found for the specified criteria.', 16, 1)
RETURN 1
END
IF @rowcount > 1
BEGIN
-- report error and exit with non-zero exit code
RAISERROR('Warning. More than 1 record found for the specified criteria, while just 1 is expected.', 10, 1)
RETURN 1
END
-- commit changes and return 0 code indicating successful completion
RETURN 0
END
GO
-- uncomment the following 2 lines if you want to grant procedure permissions to some other user or role
-- GRANT EXECUTE ON [CurveMaster].[dbo].[coreTradedMunis].p_Get[CurveMaster].[dbo].[coreTradedMunis] TO some user or role here
-- GO
IF object_id('[CurveMaster].[dbo].[coreTradedMunis].p_Save[CurveMaster].[dbo].[coreTradedMunis]') IS NOT NULL
DROP PROCEDURE [CurveMaster].[dbo].[coreTradedMunis].p_Save[CurveMaster].[dbo].[coreTradedMunis]
GO
CREATE PROCEDURE [CurveMaster].[dbo].[coreTradedMunis].p_Save[CurveMaster].[dbo].[coreTradedMunis]
/***********************************************************
* Code generated by SoftTree SQL Assistant © v9.5.444
*
* Procedure description: This procedure is used for adding
* and updating records in table
* [CurveMaster].[dbo].[coreTradedMunis]
* Date: 2/19/2018
* Author: jlangland
*
* Changes
* Date Modified By Comments
************************************************************
* 2/19/2018 jlangland Initial version
************************************************************/
(
@mrid int = NULL,
@SecurityIndexId int = NULL,
@IssuerEntityIndexId int = NULL,
@HarvestId int = NULL,
@CUSIP char(9) = NULL,
@ISIN char(12) = NULL,
@spSecurityId int = NULL,
@mdyIssueId int = NULL,
@fitchIssueId int = NULL,
@ID_BB_UNIQUE varchar(50) = NULL,
@spSecuritySymbolValue varchar(200) = NULL,
@PrimarySourceDB int = NULL
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @rowcount INT, @error INT, @id INT
-- start transaction
BEGIN TRANSACTION
-- check if the specified record already exists, if yes, update it, if no, create it
IF NOT EXISTS
(
SELECT *
FROM [CurveMaster].[dbo].[coreTradedMunis].[CurveMaster].[dbo].[coreTradedMunis]
WHERE [mrid] = @[mrid]
)
BEGIN
-- insert new record
INSERT INTO [CurveMaster].[dbo].[coreTradedMunis].[CurveMaster].[dbo].[coreTradedMunis]
(
[SecurityIndexId],
[IssuerEntityIndexId],
[HarvestId],
[CUSIP],
[ISIN],
[spSecurityId],
[mdyIssueId],
[fitchIssueId],
[ID_BB_UNIQUE],
[spSecuritySymbolValue],
[PrimarySourceDB]
)
VALUES
(
@SecurityIndexId,
@IssuerEntityIndexId,
@HarvestId,
@CUSIP,
@ISIN,
@spSecurityId,
@mdyIssueId,
@fitchIssueId,
@ID_BB_UNIQUE,
@spSecuritySymbolValue,
@PrimarySourceDB
)
END
ELSE
BEGIN
-- update existing record
UPDATE [CurveMaster].[dbo].[coreTradedMunis].[CurveMaster].[dbo].[coreTradedMunis]
SET [SecurityIndexId] = @SecurityIndexId,
[IssuerEntityIndexId] = @IssuerEntityIndexId,
[HarvestId] = @HarvestId,
[CUSIP] = @CUSIP,
[ISIN] = @ISIN,
[spSecurityId] = @spSecurityId,
[mdyIssueId] = @mdyIssueId,
[fitchIssueId] = @fitchIssueId,
[ID_BB_UNIQUE] = @ID_BB_UNIQUE,
[spSecuritySymbolValue] = @spSecuritySymbolValue,
[PrimarySourceDB] = @PrimarySourceDB
WHERE [mrid] = @[mrid]
END
-- capture operation completion code and number of records affected
SELECT @rowcount = @@ROWCOUNT,
@error = @@ERROR,
@id = SCOPE_IDENTITY()
IF @error != 0
BEGIN
-- cancel transaction, undo changes
ROLLBACK TRANSACTION
-- report error and exit with non-zero exit code
RAISERROR('Unable to update or insert new record. See previous message for details.', 16, 1)
RETURN @error
END
IF @rowcount != 1
BEGIN
-- cancel transaction, undo changes
ROLLBACK TRANSACTION
-- report error and exit with non-zero exit code
RAISERROR('Critical error. More than 1 record found for the specified criteria, just 1 is expected.', 16, 1)
RETURN 1
END
-- commit changes and return 0 code indicating successful completion
COMMIT TRANSACTION
-- if operation type 'Add record', return result set with the last inserted column value
IF @id IS NOT NULL
SELECT @id AS NewRecordID
RETURN 0
END
GO
-- uncomment the following 2 lines if you want to grant procedure permissions to some other user or role
-- GRANT EXECUTE ON [CurveMaster].[dbo].[coreTradedMunis].p_Save[CurveMaster].[dbo].[coreTradedMunis] TO some user or role here
-- GO
|
|
Mon Feb 19, 2018 11:22 am |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7855
|
|
|
|
I'm still unable to reproduce your results using out of the box configuration. I'm running development version 9.5.459 on my test system.
You have mentioned customizing templates in the past. I wonder if there is some sort of a conflict in your SQL Assistant settings resulting fro ma mix of old and new things. Can you please try reverting to the factory default settings? Note that all your configuration file customizations are saved and never deleted. Please look in the %APP_DATA%\SQL Assistant\ version specific files here. Look for files with .SAS extensions. You can copy things between the files using the Import/Export buttons in the Options dialog.
If you use the same computer as 4 years ago, your old custom templates should be still available.
By the way, the header in your code refers to 9.5.444. the latest publicly released version is 9.5.452. I'm not sure if it matters. To upgrade, please right-click SQL Assistant's icon in the system tray and select Check for Updates menu
|
|
Mon Feb 19, 2018 1:11 pm |
|
![](templates/GreenTech/images/spacer.gif) |
jlangland
Joined: 27 Apr 2016 Posts: 16 Country: United States |
|
|
|
fixed in v9.5.469
However, now CRUD tool can't find PK's.
See new topic.
|
|
Fri Jun 22, 2018 11:50 am |
|
![](templates/GreenTech/images/spacer.gif) |
|
|
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
|
|
|