Can I add user and passw to my OLAP ConnectionString?

Hi all,
I have written a custom assembly that works fine in preview mode, but when I deploy the report I get the '#Error' message instead of the output.
I have copied the assembly to the bin folder of Reportserver, placed it in the GAC and even edited the policyfiles.

Any help would be very appriciated,

Thanks in advance

public static class SimCardBuyer
{
public static string ReturnBuyer(string Seller, string PeriodeCode, string ComDevice)
{
string connstring = "PROVIDER=MSOLAP;DATA SOURCE=srv03-gc-10;INITIAL CATALOG=Invoicing";
AdomdConnection conn = new Microsoft.AnalysisServices.AdomdClient.AdomdConnection(connstring);
//Create a new string builder to store the results
System.Text.StringBuilder result = new System.Text.StringBuilder();
//Connect to the local server
using (conn)
{
conn.Open();
//Create a command, using this connection
AdomdCommand cmd = conn.CreateCommand();
cmd.CommandText = @."SELECT NON EMPTY { [Measures].[InvoiceAmount] } ON COLUMNS, NON EMPTY { ([Buyer].[Company].[Company].ALLMEMBERS ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM
( SELECT ( { [Com Device].[Com Device].[" + ComDevice + @."] } ) ON COLUMNS FROM
( SELECT ( { [Invoice].[Period Code].[" + PeriodeCode + @."] } ) ON COLUMNS FROM
( SELECT ( { [Seller].[Company].[" + Seller + @."] } ) ON COLUMNS FROM [Invoicing])))
WHERE ( [Seller].[Company].[" + Seller + @."], [Invoice].[Period Code].[" + PeriodeCode + @."], [Com Device].[Com Device].[" + ComDevice + @."] )";

//Execute the query, returning a cellset
CellSet cs = cmd.ExecuteCellSet();
//Output the column captions from the first axis
//Note that this procedure assumes a single member exists per column.
TupleCollection tuplesOnRows = cs.Axes[1].Set.Tuples;
if (tuplesOnRows.Count > 1)
{
for (int row = 0; row < tuplesOnRows.Count - 1; row++)
{
result.Append(tuplesOnRows[row].Members[0].Caption);
result.Append(" / ");
}
result.Append(tuplesOnRows[tuplesOnRows.Count - 1].Members[0].Caption);
}
else if (tuplesOnRows.Count == 1)
{result.Append(tuplesOnRows[0].Members[0].Caption);}
else{result.Append(Seller);}
conn.Close();
return result.ToString();
}
}

Dear readers,

I have solved the problem above, but now I get an error with the Connectionstring.
I'm getting the next error now:

Either the user, NL01\SRV03-GC-10$, does not have access to the Invoicing database, or the database does not exist.
NL01\SRV03-GC-10$ is not a user in my system, so I'm stunned with this strange error.

I would appriciate any reply on this.

regards.

Can any one help me with this SQLXML 3.0 SP3 Problem?

I'm having a problem with SQLXML. I have written a small VB.NET
application to manipulate xml files and import them into an SQL Server
database.
Every time I run the application, the import fails. The error log
contains the following xml:
<code>
<?xml version="1.0"?>
<Result State="FAILED">
<Error>
<HResult>0x80004005I32</HResult>
<Description><![CDATA[Error connecting to the data
source.]]></Description>
<Source>XML BulkLoad for SQL Server</Source>
<Type>FATAL</Type>
</Error>
</Result State>
</code>
I'm at a complete loss as to what's going on here. Any input would be
greatly appreciated. I've included most of my code for reference. If
anything else is needed, please let me know.
The code that is supposed to be connecting to the database and executing
the bulk transfer is as follows:
<code>
Private Function importToSQL(ByVal importXML As String)
(where importXML = C:\SQL EXCHANGE\IDS\IN\filename.xml)
Dim noErrors As Boolean
Dim connectionString As String = "PROVIDER=SQLOLEDB; Server=(local);
database=database; user id=username; password=password"
Dim errorLog As String = importXML & ".errlog"
Dim dataSchema As String = "C:\SQL EXCHANGE\IDS\IN\IDS XML Importer\IDS
XML Importer.xsd"
Dim bulkLoad As New SQLXMLBULKLOADLib.SQLXMLBulkLoad3
Try
bulkLoad.KeepIdentity = False
bulkLoad.KeepNulls = True
bulkLoad.ErrorLogFile = errorLog
bulkLoad.ConnectionString = connectionString
bulkLoad.Execute(dataSchema, importXML)
Catch ex As Exception
noErrors = False
End Try
End Function
</code>
Lastly, a snippet of the .xsd file that I'm using to show that its
structure:
<code>
<?xml version="1.0" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="File">
<xsd:complexType>
<xsd:choice maxOccurs="unbounded">
<xsd:element name="CLAIM" sql:relation="IMPORT_IHS_DENTAL_CLAIMS">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="CLAIM_NUM" type="xsd:string"
sql:field="CLAIM_NUM" />
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~
<xsd:element name="_240_TREATMENT_ZIP" type="xsd:string"
sql:field="_240_TREATMENT_ZIP" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:choice>
</xsd:complexType>
</xsd:element>
</xsd:schema>
</code>
Thanks in advance.
--
Grant Smith
A+, Net+, MCP x 2Try a semicolon after the password.
Rick Sawtell
MCT, MCSD, MCDBA
"Grant Smith - eNVENT Technologies" <grant.smith@.envent-tech.com> wrote in
message news:4Yudf.327077$084.13767@.attbi_s22...
> I'm having a problem with SQLXML. I have written a small VB.NET
> application to manipulate xml files and import them into an SQL Server
> database.
> Every time I run the application, the import fails. The error log
> contains the following xml:
> <code>
> <?xml version="1.0"?>
> <Result State="FAILED">
> <Error>
> <HResult>0x80004005I32</HResult>
> <Description><![CDATA[Error connecting to the data
> source.]]></Description>
> <Source>XML BulkLoad for SQL Server</Source>
> <Type>FATAL</Type>
> </Error>
> </Result State>
> </code>
> I'm at a complete loss as to what's going on here. Any input would be
> greatly appreciated. I've included most of my code for reference. If
> anything else is needed, please let me know.
> The code that is supposed to be connecting to the database and executing
> the bulk transfer is as follows:
> <code>
> Private Function importToSQL(ByVal importXML As String)
> (where importXML = C:\SQL EXCHANGE\IDS\IN\filename.xml)
> Dim noErrors As Boolean
> Dim connectionString As String = "PROVIDER=SQLOLEDB; Server=(local);
> database=database; user id=username; password=password"
> Dim errorLog As String = importXML & ".errlog"
> Dim dataSchema As String = "C:\SQL EXCHANGE\IDS\IN\IDS XML Importer\IDS
> XML Importer.xsd"
> Dim bulkLoad As New SQLXMLBULKLOADLib.SQLXMLBulkLoad3
> Try
> bulkLoad.KeepIdentity = False
> bulkLoad.KeepNulls = True
> bulkLoad.ErrorLogFile = errorLog
> bulkLoad.ConnectionString = connectionString
> bulkLoad.Execute(dataSchema, importXML)
> Catch ex As Exception
> noErrors = False
> End Try
> End Function
> </code>
> Lastly, a snippet of the .xsd file that I'm using to show that its
> structure:
> <code>
> <?xml version="1.0" ?>
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="File">
> <xsd:complexType>
> <xsd:choice maxOccurs="unbounded">
> <xsd:element name="CLAIM" sql:relation="IMPORT_IHS_DENTAL_CLAIMS">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="CLAIM_NUM" type="xsd:string"
> sql:field="CLAIM_NUM" />
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~
> <xsd:element name="_240_TREATMENT_ZIP" type="xsd:string"
> sql:field="_240_TREATMENT_ZIP" />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:choice>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> </code>
> Thanks in advance.
> --
> Grant Smith
> A+, Net+, MCP x 2|||Hi
The error implies that you have not connected correctly to your database.
Make sure that your connection string is correct. Check out the examples at
http://www.perfectxml.com/articles/XML/ImportXMLSQL.asp
John
"Grant Smith - eNVENT Technologies" <grant.smith@.envent-tech.com> wrote in
message news:4Yudf.327077$084.13767@.attbi_s22...
> I'm having a problem with SQLXML. I have written a small VB.NET
> application to manipulate xml files and import them into an SQL Server
> database.
> Every time I run the application, the import fails. The error log contains
> the following xml:
> <code>
> <?xml version="1.0"?>
> <Result State="FAILED">
> <Error>
> <HResult>0x80004005I32</HResult>
> <Description><![CDATA[Error connecting to the data
> source.]]></Description>
> <Source>XML BulkLoad for SQL Server</Source>
> <Type>FATAL</Type>
> </Error>
> </Result State>
> </code>
> I'm at a complete loss as to what's going on here. Any input would be
> greatly appreciated. I've included most of my code for reference. If
> anything else is needed, please let me know.
> The code that is supposed to be connecting to the database and executing
> the bulk transfer is as follows:
> <code>
> Private Function importToSQL(ByVal importXML As String)
> (where importXML = C:\SQL EXCHANGE\IDS\IN\filename.xml)
> Dim noErrors As Boolean
> Dim connectionString As String = "PROVIDER=SQLOLEDB; Server=(local);
> database=database; user id=username; password=password"
> Dim errorLog As String = importXML & ".errlog"
> Dim dataSchema As String = "C:\SQL EXCHANGE\IDS\IN\IDS XML Importer\IDS
> XML Importer.xsd"
> Dim bulkLoad As New SQLXMLBULKLOADLib.SQLXMLBulkLoad3
> Try
> bulkLoad.KeepIdentity = False
> bulkLoad.KeepNulls = True
> bulkLoad.ErrorLogFile = errorLog
> bulkLoad.ConnectionString = connectionString
> bulkLoad.Execute(dataSchema, importXML)
> Catch ex As Exception
> noErrors = False
> End Try
> End Function
> </code>
> Lastly, a snippet of the .xsd file that I'm using to show that its
> structure:
> <code>
> <?xml version="1.0" ?>
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="File">
> <xsd:complexType>
> <xsd:choice maxOccurs="unbounded">
> <xsd:element name="CLAIM" sql:relation="IMPORT_IHS_DENTAL_CLAIMS">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="CLAIM_NUM" type="xsd:string" sql:field="CLAIM_NUM" />
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~
> <xsd:element name="_240_TREATMENT_ZIP" type="xsd:string"
> sql:field="_240_TREATMENT_ZIP" />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:choice>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> </code>
> Thanks in advance.
> --
> Grant Smith
> A+, Net+, MCP x 2|||John Bell wrote:
> Hi
> The error implies that you have not connected correctly to your database.
> Make sure that your connection string is correct. Check out the examples a
t
> http://www.perfectxml.com/articles/XML/ImportXMLSQL.asp
> John
> "Grant Smith - eNVENT Technologies" <grant.smith@.envent-tech.com> wrote in
> message news:4Yudf.327077$084.13767@.attbi_s22...
>
>
>
Thanks for the input John, but I changed my connection string to match
PerfecXML (which is what I started with, i might add) save for making
UID and PWD appropriate for my server, and I still got the same results.
Grant Smith
A+, Net+, MCP x 2
Quality Production Liaison
Hewlett Packard Company
Database Administrator
Renaissance Systems and Services, LLC|||Rick Sawtell wrote:
> Try a semicolon after the password.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
> "Grant Smith - eNVENT Technologies" <grant.smith@.envent-tech.com> wrote in
> message news:4Yudf.327077$084.13767@.attbi_s22...
>
>
>
I added a semicolon as you directed and still got the same results.
Grant Smith
A+, Net+, MCP x 2
Quality Production Liaison
Hewlett Packard Company
Database Administrator
Renaissance Systems and Services, LLC|||Rick Sawtell wrote:
> Try a semicolon after the password.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
> "Grant Smith - eNVENT Technologies" <grant.smith@.envent-tech.com> wrote in
> message news:4Yudf.327077$084.13767@.attbi_s22...
>
>
>
I tried this and got the same results.
Any more ideas?
Grant Smith
A+, Net+, MCP x 2
Quality Production Liaison
Hewlett Packard Company
Database Administrator
Renaissance Systems and Services, LLC|||John Bell wrote:
> Hi
> The error implies that you have not connected correctly to your database.
> Make sure that your connection string is correct. Check out the examples a
t
> http://www.perfectxml.com/articles/XML/ImportXMLSQL.asp
> John
> "Grant Smith - eNVENT Technologies" <grant.smith@.envent-tech.com> wrote in
> message news:4Yudf.327077$084.13767@.attbi_s22...
>
>
>
I changed my connection string to exactly what PerfectXML had as an example
(by the way, I started with this connection string originally) save for
the username and password values and still got the same results.
Anything else you can think of?
Grant Smith
A+, Net+, MCP x 2
Quality Production Liaison
Hewlett Packard Company
Database Administrator
Renaissance Systems and Services, LLC|||Hi
If the connection string
PROVIDER=SQLOLEDB. 1;SERVER=(local);DATABASE=database;UID=u
sername;PWD=passwo
rd;
does not work, then you may want to check conectivity in general
through query analyser.
John
Grant Smith - eNVENT Technologies wrote:
> Rick Sawtell wrote:
> I added a semicolon as you directed and still got the same results.
> --
> Grant Smith
> A+, Net+, MCP x 2
> Quality Production Liaison
> Hewlett Packard Company
> Database Administrator
> Renaissance Systems and Services, LLC

can any experts pl help me out in insert query..

hi, i have written this insert query for inserting data in database : "insert into leavemaster (leave_code,leave_desc,leave_type, leave_days, leave_valid_month, leave_amount, effective_date, max_limit, leave_num, maintain_bal, leave_encash, leave_fq, encash_limit, encash_fq, carry_frwd, negative_bal, max_encash_bal, leave_limit, holiday_lv, weekoff_lv, del_flag" & _
"values (@.leave_code, @.leave_desc, @.leave_type, @.leave_days, @.leave_valid_month, @.leave_amount, @.effective_date, @.max_limit, @.leave_num, @.maintain_bal, @.leave_encash, @.leave_fq, @.encash_limit, @.encash_fq, @.carry_frwd, @.negative_bal, @.max_encash_bal, @.leave_limit, @.holiday_lv, @.weekoff_lv, @.del_flag)"

and then have passed parameters like:

Dim leave_codeParam As New OleDbParameter("@.leave_code", OleDbType.VarChar, 3)
leave_codeParam.Value = txtLeave_code.Text
cmd.Parameters.Add(leave_codeParam)
for each fields of database table..
but syntax error in insert into statemtent is coming still.. can any experts pl help me out...There is a close bracket missing after del_flag

Regards,
J

can a query be written to do this...

I have the following table defined: Not my design/idea and I can't change
it)
CREATE TABLE [dbo].[Task] (
[TaskID] [ROWIDENTIFIER] NOT NULL ,
[Name] [SHORTNAME] NULL ,
[Description] [SHORTDESCRIPTION] NULL ,
[PreviousTaskID] [ROWIDENTIFIER] NULL ,
[NextTaskID] [ROWIDENTIFIER] NULL ,
[ProcedureID] [ROWIDENTIFIER] NOT NULL ,
[IsActive] [WFBOOL] NULL
) ON [PRIMARY]
PreviousTaskID and NextTaskID form, what amounts to a linked list where
PreviousTaskID points to the TaskID that comes before the current task and
NextTaskID points to the TaskID of the task that follows the current task.
A PreviousTaskID equal to null signifies the first task in a list and a
NextTaskID equal to null signifies it is the last task in the list.
With all of that in mind: Is there any way to write a query that will return
a single set of rows ordered from first to last?
TIA
Brian WBW -
It seems unnecessary to have a Next and Previous so long as the chain is
always 1 for 1 (i.e. Task 2 always comes after Task 1, etc). Anyways, here'
s
something that should get you started:
create table #Task (
TaskID int not null
, [Name] varchar(50) null
, PreviousTaskID int null
, NextTaskID int null
)
insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(1,
'T1', null, 2)
insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(2,
'T1', 1, 3)
insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(3,
'T1', 2, 4)
insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(4,
'T1', 3, 5)
insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(5,
'T1', 4, 6)
insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(6,
'T1', 5, null)
declare @.parent_level int
set @.parent_level = 0
declare @.hierarchy table (parent int, item int, [level] int)
insert into @.hierarchy (parent, item, [level])
select null, taskid, 0
from #Task
where previoustaskid is null
while 1 = 1
begin
insert into @.hierarchy(parent, item, [level])
select nexttaskid, taskid, @.parent_level + 1
from #Task
where previoustaskid in (select item from @.hierarchy where [level] =
@.parent_level)
if @.@.rowcount = 0
break
set @.parent_level = @.parent_level + 1
end
select t.TaskID, h.[level] as Ordering
from #Task t
join @.hierarchy h on t.TaskID = h.item
order by 2 asc|||Perfect!
muchos gracias!
"Cris_Benge" <CrisBenge@.discussions.microsoft.com> wrote in message
news:C3096324-4209-4868-80AB-FA4FA7DB9B97@.microsoft.com...
> BW -
> It seems unnecessary to have a Next and Previous so long as the chain is
> always 1 for 1 (i.e. Task 2 always comes after Task 1, etc). Anyways,
here's
> something that should get you started:
> create table #Task (
> TaskID int not null
> , [Name] varchar(50) null
> , PreviousTaskID int null
> , NextTaskID int null
> )
> insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(1,
> 'T1', null, 2)
> insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(2,
> 'T1', 1, 3)
> insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(3,
> 'T1', 2, 4)
> insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(4,
> 'T1', 3, 5)
> insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(5,
> 'T1', 4, 6)
> insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(6,
> 'T1', 5, null)
> declare @.parent_level int
> set @.parent_level = 0
> declare @.hierarchy table (parent int, item int, [level] int)
> insert into @.hierarchy (parent, item, [level])
> select null, taskid, 0
> from #Task
> where previoustaskid is null
> while 1 = 1
> begin
> insert into @.hierarchy(parent, item, [level])
> select nexttaskid, taskid, @.parent_level + 1
> from #Task
> where previoustaskid in (select item from @.hierarchy where [level] =
> @.parent_level)
> if @.@.rowcount = 0
> break
> set @.parent_level = @.parent_level + 1
> end
> select t.TaskID, h.[level] as Ordering
> from #Task t
> join @.hierarchy h on t.TaskID = h.item
> order by 2 asc
>

Can a managed udf contain a static dictionary<>? Is this a wise idea?

I currently have a udf written in T-SQL that's getting way too logically complicated!

It’s typically accessed like this:

SELECT PartNumber,dbo.PartPrice(Manufacturer, Model, AssemblageInfo, Version, CustomerDiscountLevel) FROM WorkOrders where OrderNumber=123456

The udf does some complicated manipulations on the parameters and eventually does a SELECT on a lookup table and returns the result.

If I make this a managed code udf, the logic gets much simpler to write (great!).

But, my question is:

Can I take the lookup table and embed it in the udf--so the udf doesn't have to go to the database to do the lookup?

Would I do that in a STATIC dictionary<>?

Is it wise to keep the info statically?

The lookup table consists of 3600(+/-) elements and changes exactly once a month.

The SELECT statement using the udf typically returns several thousand rows.

The SELECT is done often.

--Mark

If the table changes over the time, I would not implement this as a static dictionary as you would have to recreate the function everytime the tables changes and you would hae more trouble changing the data of the table than it would be in a normal static data table in SQL Server.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Jens!

Thanks for the response!

I thought of the refresh function thing.

I 'm willing to make it a monthly chore to refresh the function in exchange for a faster static dictionary lookups--but only if it really will speed up the SELECTs.

I guess this brings the question of when the static dictionary goes out of scope on a function call.

-

For example, if the dictionary is reloaded for each row of a SELECT, then it's worse than worthless!

If the dictionary is retained for all rows in a SELECT statement, it **might** be useful.

If the dictionary is retained over many SELECT's then it's worthwhile.

If the dictionary is retained all month, then it's priceless (as the mastercard commercial says!).

When does a static dictionary within a function go out of scope?

Is a static dictionary retained in memory for all rows in a SELECT statement?

Is a static dictionary retained in memory between function calls?

Does the SQL engine cache the function and then release the dictionary from memory after a time-out period?

Thanks, Mark

|||

I finally got to a machine with SQL2005 to try the static dictionary<>

Turns out you can't even use static variables in a managed udf--at least not without declaring the code as unsafe.

--Mark

Calling Visual Foxpro 7 COM

Dear Experts,

We are creating instances of COM Objects written in Visual FoxPro 7 in our SQL Server 2005 stored procedures.
The problem is that VFP Automation Manager which shows number of connections to number of objects is showing 3 connections to 3 objects(instead 1 to 1).

We are releasing the object in SQL Server 2005 using the sp_OADestroy @.Object but the Automation Manager leaves 1 connection to 1 object.

The Automation manager connection doesn't release all the connections.

What effects is this having to our SQL Server 2005 installation and how can we avoid it?

We noticed that when we stop the SQL Service, connections to the Automation Manager are also released.

Thank you for your time.

Regards,

Spyros Christodoulou

You could stop the OLE Automation execution environment itself by calling sp_OAStop. Note that this affects the execution environment at the server-level. Generally, you should avoid using OLE automation extensively since it is resource intensive and running these in-proc can destabilize the server. Try creating the ole server out-of-proc for better reliability with some performance penalty. What is the reason for calling the VFP object from TSQL? Is this absoultely necessary? Note that you can perform queries against VFP databases/tables using linked servers. This is something you should look at also.|||Dear Umachandar,

Thanks for your reply.

Unfortunately we cannot stop OLE Automation execution environment because other users may executing OLE Methods as well.

The reason we are calling VFP from TSQL is that we need VFP to export some files.
I know that we could use a linked server, but in our case it doesn't apply because of the following:
1) VFP database and tables are changing a lot and we don't want the SQL team to be involved every time the VFP team changes tables required for the Import in SQL.
2) The export process is very complicated and it may need to call 2 or 3 VFP methods which means on a change we need to look into these methods as well. (Both VFP and SQL Teams)

For these reasons and the fact that it is much faster to develop the export method in VFP it is better to call VFP method to export data for SQL server.

Have you tried calling VFP out-of-proc COM objects? Does it properly release memory when you call sp_OADestroy @.Object ?

Regards,

Spyros Christodoulou
|||I was suggesting out-of-proc instantiation for better reliability/isolation on the server-side. Some of this might be just behavior of OLE automation. Can you try similar test from say VB and see if the connections are released properly after the OLE object is destroyed?|||Hi Umachandar,

Thanks for your reply,

I found a post you made at microsoft.public.sqlserver.programming on august of 2001.

"I have used this. It works perfectly fine. Are you making a EXE? Does it

have UI? Make sure it doesn't have any of those. Here is one sample:

NOTE -- Save this to a file called "VFPOLE.PRG" in "C:\TEMP"
DEFINE CLASS vfpole AS Custom OLEPUBLIC
prop1 = 'VFPOLE'
ENDDEFINE

NOTE Run these in the VFP command window
CD C:\Temp
BUILD PROJECT VFPOLE FROM VFPOLE.PRG
BUILD DLL VFPOLE FROM VFPOLE

-- Now run this on the SQL Server

declare @.o int, @.h int, @.p varchar( 255 )

exec @.h = sp_OACreate 'VFPOLE.VFPOLE', @.o out

if @.@.error|@.h <> 0 exec sp_displayoaerrorinfo @.o, @.h

print @.o

exec @.h = sp_OAGetProperty @.o, 'Prop1', @.p OUT

if @.@.error|@.h <> 0 exec sp_displayoaerrorinfo @.o, @.h

print @.p

exec sp_oadestroy @.o"

I tried it and it works as an in-process OLE server. It seems that it concumes some SQL Server memory every time I run it. How to register and call the dll if it resides on a different server. (like when you register the VFP .vbr file using clireg32.exe)

Do you know how to make it an out of process and called it based on the following scenario:

Server 1: VFP Database
Server 2: SQL Server

I need to run (SP on Server 2) the dll or exe using Ole Automation on Server 1. (Server 2 will have share access on Server 1)

Any help will be greatly appreciated.

Thanks again,

Spyros Christodoulou

|||The third parameter to sp_OACreate specifies the context for the OLE server. If you specify it as 4 then out-of-proc activation will take place. See Books Online for more details on the parameter.|||

Hi Umachandar and Spyros:

I am trying to do something very similar in SQL Server 2000 with VFP 8 objects.

The remaining problem is also similar as the one Spyros described:

"(...)

Server 1: VFP Database
Server 2: SQL Server

I need to run (SP on Server 2) the dll or exe using Ole Automation on Server 1. (Server 2 will have share access on Server 1)

(...)"

In server 1, I have the COM+ application o package correctly installed and working since years

In Clients computers various front end consume the server classes via, for example in VB

CreateObject('AppMastervs.cOrga', mtsServer1)

or in VFP

CreateObjectEx('AppMastervs.cOrga', mtsServer1)

These clients has registered the corresponding .vbr & tlb via CliReg32

I also registered the server classes in the Server 2 (the one with SQLServer) and it work fine with the Front ends, but was impossible to me to create the class in SQLServer using

EXEC @.hr = sp_OACreate 'AppMastervs.cOrga', @.object OUT ,1

EXEC @.hr = sp_OACreate 'AppMastervs.cOrga', @.object OUT ,4

EXEC @.hr = sp_OACreate 'AppMastervs.cOrga', @.object OUT ,5

Then I copied and registered the class as local in the the Server 2 (as COM+ app in Component services) and then it worked fine (with the 3rd parameter in 4 or 5), but the problem is that must not be done beacuse this carges the sqlServer, generate unnecesary traffic and dificults the server update process.

So, please let me know if is really possible to create, in SQL Server 2000, a server class working as COM+ application in other server, without register it locally as COM+ Application.

Thanks in advance;

Claudio Facundo Lacivita

Calling Visual Foxpro 7 COM

Dear Experts,

We are creating instances of COM Objects written in Visual FoxPro 7 in our SQL Server 2005 stored procedures.
The problem is that VFP Automation Manager which shows number of connections to number of objects is showing 3 connections to 3 objects(instead 1 to 1).

We are releasing the object in SQL Server 2005 using the sp_OADestroy @.Object but the Automation Manager leaves 1 connection to 1 object.

The Automation manager connection doesn't release all the connections.

What effects is this having to our SQL Server 2005 installation and how can we avoid it?

We noticed that when we stop the SQL Service, connections to the Automation Manager are also released.

Thank you for your time.

Regards,

Spyros Christodoulou

You could stop the OLE Automation execution environment itself by calling sp_OAStop. Note that this affects the execution environment at the server-level. Generally, you should avoid using OLE automation extensively since it is resource intensive and running these in-proc can destabilize the server. Try creating the ole server out-of-proc for better reliability with some performance penalty. What is the reason for calling the VFP object from TSQL? Is this absoultely necessary? Note that you can perform queries against VFP databases/tables using linked servers. This is something you should look at also.|||Dear Umachandar,

Thanks for your reply.

Unfortunately we cannot stop OLE Automation execution environment because other users may executing OLE Methods as well.

The reason we are calling VFP from TSQL is that we need VFP to export some files.
I know that we could use a linked server, but in our case it doesn't apply because of the following:
1) VFP database and tables are changing a lot and we don't want the SQL team to be involved every time the VFP team changes tables required for the Import in SQL.
2) The export process is very complicated and it may need to call 2 or 3 VFP methods which means on a change we need to look into these methods as well. (Both VFP and SQL Teams)

For these reasons and the fact that it is much faster to develop the export method in VFP it is better to call VFP method to export data for SQL server.

Have you tried calling VFP out-of-proc COM objects? Does it properly release memory when you call sp_OADestroy @.Object ?

Regards,

Spyros Christodoulou
|||I was suggesting out-of-proc instantiation for better reliability/isolation on the server-side. Some of this might be just behavior of OLE automation. Can you try similar test from say VB and see if the connections are released properly after the OLE object is destroyed?|||Hi Umachandar,

Thanks for your reply,

I found a post you made at microsoft.public.sqlserver.programming on august of 2001.

"I have used this. It works perfectly fine. Are you making a EXE? Does it

have UI? Make sure it doesn't have any of those. Here is one sample:

NOTE -- Save this to a file called "VFPOLE.PRG" in "C:\TEMP"
DEFINE CLASS vfpole AS Custom OLEPUBLIC
prop1 = 'VFPOLE'
ENDDEFINE

NOTE Run these in the VFP command window
CD C:\Temp
BUILD PROJECT VFPOLE FROM VFPOLE.PRG
BUILD DLL VFPOLE FROM VFPOLE

-- Now run this on the SQL Server

declare @.o int, @.h int, @.p varchar( 255 )

exec @.h = sp_OACreate 'VFPOLE.VFPOLE', @.o out

if @.@.error|@.h <> 0 exec sp_displayoaerrorinfo @.o, @.h

print @.o

exec @.h = sp_OAGetProperty @.o, 'Prop1', @.p OUT

if @.@.error|@.h <> 0 exec sp_displayoaerrorinfo @.o, @.h

print @.p

exec sp_oadestroy @.o"

I tried it and it works as an in-process OLE server. It seems that it concumes some SQL Server memory every time I run it. How to register and call the dll if it resides on a different server. (like when you register the VFP .vbr file using clireg32.exe)

Do you know how to make it an out of process and called it based on the following scenario:

Server 1: VFP Database
Server 2: SQL Server

I need to run (SP on Server 2) the dll or exe using Ole Automation on Server 1. (Server 2 will have share access on Server 1)

Any help will be greatly appreciated.

Thanks again,

Spyros Christodoulou

|||The third parameter to sp_OACreate specifies the context for the OLE server. If you specify it as 4 then out-of-proc activation will take place. See Books Online for more details on the parameter.|||

Hi Umachandar and Spyros:

I am trying to do something very similar in SQL Server 2000 with VFP 8 objects.

The remaining problem is also similar as the one Spyros described:

"(...)

Server 1: VFP Database
Server 2: SQL Server

I need to run (SP on Server 2) the dll or exe using Ole Automation on Server 1. (Server 2 will have share access on Server 1)

(...)"

In server 1, I have the COM+ application o package correctly installed and working since years

In Clients computers various front end consume the server classes via, for example in VB

CreateObject('AppMastervs.cOrga', mtsServer1)

or in VFP

CreateObjectEx('AppMastervs.cOrga', mtsServer1)

These clients has registered the corresponding .vbr & tlb via CliReg32

I also registered the server classes in the Server 2 (the one with SQLServer) and it work fine with the Front ends, but was impossible to me to create the class in SQLServer using

EXEC @.hr = sp_OACreate 'AppMastervs.cOrga', @.object OUT ,1

EXEC @.hr = sp_OACreate 'AppMastervs.cOrga', @.object OUT ,4

EXEC @.hr = sp_OACreate 'AppMastervs.cOrga', @.object OUT ,5

Then I copied and registered the class as local in the the Server 2 (as COM+ app in Component services) and then it worked fine (with the 3rd parameter in 4 or 5), but the problem is that must not be done beacuse this carges the sqlServer, generate unnecesary traffic and dificults the server update process.

So, please let me know if is really possible to create, in SQL Server 2000, a server class working as COM+ application in other server, without register it locally as COM+ Application.

Thanks in advance;

Claudio Facundo Lacivita

Calling VBA functions from SQL

I have a rather complex function (part of a production planning engine) that is written in VBA, and is part of my front end app. I'd like to be able to somehow call this function from either a sproc or DTS package in SQL. Is this possible, or am I going to have to convert the function to a SQL sproc (ugh)?Plop the VBA script into an Active-X task within a DTS package, and you should be "good to go". Another choice that folks often overlook is to make the VBA an ActiveX Script step in a SQL Agent job.

-PatP|||Thanks, Pat.

I was thinking along those lines, so I did a little more research while waiting for an answer. It appears that VBScript (and ActiveX??) doesn't like arrays, especially the dynamic variety. I think what I'm trying to do may be misuse of a sproc, but since I want the task to run as part of a rather large string of scheduled tasks, I'm stuck with converting it.