|
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
Forum to discuss general T-SQL questions and help with queries related to SalesLogix data. View the code of conduct for posting guidelines.
|
|
|
| |
|
Re: GET new ACCOUNTID, CONTACTID, ADDRESSID throgh SQL
Posted: 21 Feb 08 5:13 PM
|
There is no supported way of doing this outside of the SLX OLEDB provider (and when you are at the SQL level, you are definitely outside it). But you do not need to be 'inside' SLX either - external .NET, VBScript or SSIS routines can all do the job.
Phil |
|
|
|
Re: GET new ACCOUNTID, CONTACTID, ADDRESSID throgh SQL
Posted: 22 Feb 08 6:54 AM
|
The method og getting an ID external to the SLX client is though the SLX OleDb Providers stored procedures. To get IDs you would do something like this
Public Function GetNewTableIds(ByVal table As String, ByVal count As Integer) As List(Of String)
Dim list As New List(Of String)
Using conn As OleDbConnection = New OleDbConnection(m_Connection.ToString())
conn.Open()
Using cmd As OleDbCommand = New OleDbCommand(String.Format("slx_dbids('{0}', {1})", table, count), conn)
Dim reader As OleDbDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
Do While (reader.Read())
list.Add(reader.GetString(0))
Loop
reader.Close()
End Using
End Using
Return list
End Function
Regards,
Mark
Note the slx_dbids stored proc where you pass in the name of the table and the number of Id's you want to generate.
Mark |
|
|
|
Re: GET new ACCOUNTID, CONTACTID, ADDRESSID throgh SQL
Posted: 22 Feb 08 9:50 AM
|
I actually figured out a way to do it. Here is the code if anyone needs it.
Create the stored procedure, then you would call it and supply the required variables from your sysdba.SITEKEYS table.
DECLARE @ContactID varchar(12), @AccountID varchar(12)
-- Contact is the KEYDESC and C would be what the ID should start with.
EXECUTE @AccountID = SalesLogix.dbo.GetNewSalesLogixID 'Account', 'A'
EXECUTE @ContactID = SalesLogix.dbo.GetNewSalesLogixID 'Contact', 'C'
CREATE PROCEDURE [dbo].[GetNewSalesLogixID]
(
-- Add the parameters for the function here
@KeyDesc VARCHAR(255), @FirstLetter VARCHAR(1)
)
AS
BEGIN
-- Declare the return variable here
DECLARE @KeyValue VARCHAR(32), @NewKeyValue VARCHAR(32), @CurrentCharacter VARCHAR(1), @NextCharacter VARCHAR(1), @ResultVar VARCHAR(12)
-- Add the T-SQL statements to compute the return value here
SELECT @KeyValue=KEYVALUE
FROM saleslogix.sysdba.SITEKEYS
WHERE KEYDESC=@KeyDesc
IF SUBSTRING(@KeyValue, 7, 1)<>'Z'
BEGIN
SET @CurrentCharacter = SUBSTRING(@KeyValue, 7, 1)
EXECUTE @NextCharacter=saleslogix.dbo.GetNextCharacter @CurrentCharacter
SET @NewKeyValue=SUBSTRING(@KeyValue, 1, 6)+@NextCharacter
END
ELSE
IF SUBSTRING(@KeyValue, 6, 1)<>'Z'
BEGIN
SET @CurrentCharacter = SUBSTRING(@KeyValue, 6, 1)
EXECUTE @NextCharacter=saleslogix.dbo.GetNextCharacter @CurrentCharacter
SET @NewKeyValue=SUBSTRING(@KeyValue, 1, 5)+@NextCharacter+'0'
END
ELSE
IF SUBSTRING(@KeyValue, 5, 1)<>'Z'
BEGIN
SET @CurrentCharacter = SUBSTRING(@KeyValue, 5, 1)
EXECUTE @NextCharacter=saleslogix.dbo.GetNextCharacter @CurrentCharacter
SET @NewKeyValue=SUBSTRING(@KeyValue, 1, 4)+@NextCharacter+'00'
END
ELSE
IF SUBSTRING(@KeyValue, 4, 1)<>'Z'
BEGIN
SET @CurrentCharacter = SUBSTRING(@KeyValue, 4, 1)
EXECUTE @NextCharacter=saleslogix.dbo.GetNextCharacter @CurrentCharacter
SET @NewKeyValue=SUBSTRING(@KeyValue, 1, 3)+@NextCharacter+'000'
END
ELSE
IF SUBSTRING(@KeyValue, 3, 1)<>'Z'
BEGIN
SET @CurrentCharacter = SUBSTRING(@KeyValue, 3, 1)
EXECUTE @NextCharacter=saleslogix.dbo.GetNextCharacter @CurrentCharacter
SET @NewKeyValue=SUBSTRING(@KeyValue, 1, 2)+@NextCharacter+'0000'
END
ELSE
IF SUBSTRING(@KeyValue, 2, 1)<>'Z'
BEGIN
SET @CurrentCharacter = SUBSTRING(@KeyValue, 2, 1)
EXECUTE @NextCharacter=saleslogix.dbo.GetNextCharacter @CurrentCharacter
SET @NewKeyValue=SUBSTRING(@KeyValue, 1, 1)+@NextCharacter+'00000'
END
UPDATE saleslogix.sysdba.SITEKEYS SET KEYVALUE=@NewKeyValue WHERE KEYDESC=@KeyDesc
-- Return the result of the function
SELECT UPPER(@FirstLetter)+'6UJ9'+@NewKeyValue
END |
|
|
| |
| |
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|