5/22/2025 8:26:41 PM
|
| |
| slxdeveloper.com Community Forums |
|
| |
|
|
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.
|
|
|
|
calling slx_dbids straight from SQL Server?
Posted: 22 Nov 06 8:14 AM
|
fiogf49gjkf0d Hello.
A little background: I'm trying to create new contact IDs from stored procedures and need to also create the SLX IDs. Great. My problem is that I've tried to create a managed code (DLL) and use that to cann the .NET code to create IDs using hte provider, but I'm having problems actually getting the IDs back from that (I get nulls).
My question is: what can I do to be able to call the slx_dbids proc straight from mine? Do I need to link the servers or something like that? It can't be this convoluted to create the next ID needed with a base 36 increment. I don't want to do this by hand since this part is used to link the SLX system with out in house system and that will be messy keeping everything unique.
Any ideas on how to call the slx_dbids proc from inside another? I've been trying various things for about a week, and now I'm to the point where I want to scrap my managed code call and jsut go straight into the provider's proc.
Thanks for any help that can be offered!
Bryan |
|
|
|
Re: calling slx_dbids straight from SQL Server?
Posted: 22 Nov 06 9:02 AM
|
fiogf49gjkf0d As I understand it, things like slx_dbids are not real stored procedures, they are only callable via the SLX OLEDB Provider, so you can't call them directly from another stored procedure. I just checked in Enterprise Manager, and there are no slx Stored Procedures in our database.
I suppose you could write a wrapper for slx_dbids and call it using xp_cmdshell, but that probably opens another can of worms 
|
|
|
|
Re: calling slx_dbids straight from SQL Server?
Posted: 22 Nov 06 10:51 AM
|
fiogf49gjkf0d Thanks for the reply.
What I've done so far is put the ID creation in a DLL that can then be called from a proc via sp_OAMethod and all that. My problem right now is that my function returns nothing...no errors...but no ID. That lack of a returned value could be a problem of my DLL reference or something on my end, but I'm having a hard time finding that out due to calling managed code from SQL Server isn't technically supported by Microsoft in SQL Server 2k.
After my first post I looked into creating a linked server between where I'm at to the SLX Provider to (hopefully) then expose the "procedures" in the provider. That would then allow me to call slx_dbids and return an ID. I'm wondering if anyone else has done this? I've found very limited information on doing this, and almost none using the SLX provider.
If you or anyone has done this I'll buy them a virtual beer or something. If the ability do to this is nonexistant with the SLX provider then I'd think it's a serious limitation of the SLX framework since this ability seems pretty standard to me.
Thanks again for the reply. I'm going to look into this more, and if I come up with something I'll post it for sure.
Bryan |
|
|
|
Re: calling slx_dbids straight from SQL Server?
Posted: 22 Nov 06 4:45 PM
|
fiogf49gjkf0d You can have just the provider installed by itself and have it connect to your SLX server. The provider has it's own ports that will send the traffic down to the provider at the server end, which will then talk to the SQL backend. Every client has their own OLE DB Provider installed that will communicate with the server remotely (network client) or locally (remote client). Going back to 6.0, the provider has always had a separate install that would install just the provider. It wasn't always included with the CD/DVD I believe, but it should be in the Client OLE DB Provider\ directory (6.2 and 7.0 I can verify).
Having said that, there are T-SQL examples on creating a SLX ID. You could even increment the tables SLX increments every time it makes a slx_dbids call (all found using SLXProfiler.exe no less). Would I recommend it? No way. Even though the T-SQL is pretty easy to copy/paste, all of the other work is considered wasteful when you can just install the provider and be done with it. |
|
|
| |
|
Re: calling slx_dbids straight from SQL Server?
Posted: 24 Nov 06 8:37 AM
|
fiogf49gjkf0d I agree with Jeremy - install the provider and connect to the SQL server. Then from within your .NET code you can call the slx_dbids as though it is a sql command:
public string CreateSalesLogixId(string myTable)
{
OleDbCommand GetSalesLogixIDCommand = new OleDbCommand();
GetSalesLogixIDCommand.CommandType = CommandType.Text;
string commandText = String.Format(CultureInfo.CurrentCulture, "slx_dbids('{0}', 1)", myTable);
GetSalesLogixIDCommand.CommandText = commandText;
object result = null;
try
{
GetSalesLogixIDCommand.Connection = SalesLogixOleDBConnection;
OpenOleDBConnection();
result = GetSalesLogixIDCommand.ExecuteScalar();
CloseOleDBConnection();
}
... |
|
|
|
Re: calling slx_dbids straight from SQL Server?
Posted: 27 Nov 06 11:14 AM
|
fiogf49gjkf0d There are no examples provided, they were posted in various SalesLogix forums. I've seen a couple of examples, but the one I can pull up from memory is here: http://crm.ittoolbox.com/groups/technical-functional/saleslogix-l/unique-id-237268 The keyword is base36, which defines the range of digits in the key (0-9, A-Z).
If you are going to use the routine, the best bet is to profile a typical key creation to see how it updates the sitekeys table. That'll also give you the slx_dbids translation SalesLogix does to match the key with the table. (i.e. slx_dbids('ATTACH') instead of attachment).
Personally I'd just stick with the provider. Most of the time people who need the routine are using it for imports and since DTS accepts the provider, it's not necessary. There are even techniques to get around the autoincrement necessity that I believe made imports syncable back in 6.1. |
|
|
|
Re: calling slx_dbids straight from SQL Server?
Posted: 27 Nov 06 6:18 PM
|
fiogf49gjkf0d Thanks Jeremy.
This is for sure not what I was hoping for! This is just recreating... been there, done that. 
I thought you might have some magical way to call the actual function of SLX_DBID from SQL. My company does quite a bit of backend automation via SQL procs. The ability to use the inline 'sanctioned' function would be a sweet boon.
c |
|
|
|
Re: calling slx_dbids straight from SQL Server?
Posted: 28 Nov 06 8:56 AM
|
fiogf49gjkf0d I have to move on to something else for the time being, but I'll mention where I'm at to this point. I was able to create a .NET managed DLL and call that from a proc. That's not quite as painful as i would have thought. The issue now is that my DLL cannot call the slx_dbids "proc", or even a SQL stmt from the DLL without an invalid class exception thrown (an issue not related to the DLL having no prod ID, but an OLE DB error about the class). I can return a string or other value from the DLL...but nothing using the SLX provider. I know my .NET code works becuse in a test app I'm able to click a button and return new contact IDs, etc.
So right now there seems to be some kind of either permissions issue with the provider, or something is up with the provider itself on the test server. I did the same work on my local machine and got an entirely different error about the length of the string or something.
Anyway, I think it's close, but it looks like for the time being, the SLX ID creation will sit with the other procs needed in a .NET app that scheduled or something since keeping all of the logic in a SQL Server proc doesn't seem to work.
However, this is a dragon that needs to be slain for the sanity of anyone else that is forced to work with SLX.
Thanks for the posts and ideas. If I resolve this in the next couple weeks I'll post my solution.
Bryan
 |
|
|
|
Re: calling slx_dbids straight from SQL Server?
Posted: 25 Feb 08 11:46 PM
|
Hi Bryan,
The good news is that, small tweak to the function given by Rick will fetch you the Unique ID. Here is the code which worked for me...
protected void btnGetID_Click(object sender, EventArgs e)
{
string myTable = "SUBSCRIPTION";
txtID.Text = CreateSalesLogixId(myTable);
}
public string CreateSalesLogixId(string myTable)
{
OleDbConnection vConn = GroupInfo.GetOpenConnection();
string commandText = String.Format(CultureInfo.CurrentCulture, "slx_dbids('{0}', 1)", myTable);
OleDbCommand GetSalesLogixIDCommand = new OleDbCommand(commandText, vConn);
object result = null;
try
{
result = GetSalesLogixIDCommand.ExecuteScalar();
}
catch
{
throw new Exception("cannot fetch ID");
}
finally
{
vConn.Close();
}
return result.ToString();
}
Thanks Guys for all.... |
|
|
|
Re: calling slx_dbids straight from SQL Server?
Posted: 25 Feb 08 11:46 PM
|
Hi Bryan,
The good news is that, small tweak to the function given by Rick will fetch you the Unique ID. Here is the code which worked for me...
protected void btnGetID_Click(object sender, EventArgs e)
{
string myTable = "SUBSCRIPTION";
txtID.Text = CreateSalesLogixId(myTable);
}
public string CreateSalesLogixId(string myTable)
{
OleDbConnection vConn = GroupInfo.GetOpenConnection();
string commandText = String.Format(CultureInfo.CurrentCulture, "slx_dbids('{0}', 1)", myTable);
OleDbCommand GetSalesLogixIDCommand = new OleDbCommand(commandText, vConn);
object result = null;
try
{
result = GetSalesLogixIDCommand.ExecuteScalar();
}
catch
{
throw new Exception("cannot fetch ID");
}
finally
{
vConn.Close();
}
return result.ToString();
}
Thanks Guys for all.... |
|
|
|
Re: calling slx_dbids straight from SQL Server?
Posted: 25 Feb 08 11:46 PM
|
Hi Bryan,
The good news is that, small tweak to the function given by Rick will fetch you the Unique ID. Here is the code which worked for me...
protected void btnGetID_Click(object sender, EventArgs e)
{
string myTable = "SUBSCRIPTION";
txtID.Text = CreateSalesLogixId(myTable);
}
public string CreateSalesLogixId(string myTable)
{
OleDbConnection vConn = GroupInfo.GetOpenConnection();
string commandText = String.Format(CultureInfo.CurrentCulture, "slx_dbids('{0}', 1)", myTable);
OleDbCommand GetSalesLogixIDCommand = new OleDbCommand(commandText, vConn);
object result = null;
try
{
result = GetSalesLogixIDCommand.ExecuteScalar();
}
catch
{
throw new Exception("cannot fetch ID");
}
finally
{
vConn.Close();
}
return result.ToString();
}
Thanks Guys for all.... |
|
|
|
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!
|
|
|
|
|
|
|
|