Getting Print Statements from SQL SERVER 2000

Using ADO and Visual Basic 6, I am executing SQL stored
procedures, and I would like to be able to output
SQL "print" statements. I have not yet found a way to do
so, please help if you can.
Thanks
Mike BUse the error collection.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mike B" <anonymous@.discussions.microsoft.com> wrote in message
news:027701c3c0ed$07bf1010$a301280a@.phx.gbl...
> Using ADO and Visual Basic 6, I am executing SQL stored
> procedures, and I would like to be able to output
> SQL "print" statements. I have not yet found a way to do
> so, please help if you can.
> Thanks
> Mike B

Getting messages sent while JDBC Driver calls stored procedure

Hi.

How can I get the messages sent by the server while I'm executing a stored procedure via the JDBC driver?

I need to get my own debug messages (done through the print() function)

and also standard server messages (such as "x row(s) affected" or

results from SET STATISTICS TIME ON). Is this possible?

Many thanks.

Carlos

Hi Carlos,

These messages come back as warnings. You need to call Statement.getWarnings to retrieve them. See http://java.sun.com/j2se/1.4.2/docs/api/java/sql/Statement.html#getWarnings() for usage.

Hope this helps.

--David Olix

JDBC Development

|||Hi David.

That didn't work for me. I am using SQL Server 2005 JDBC driver version 1.1, and here's my calling syntax:
SQLServerConnectionPoolDataSource ds = new SQLServerConnectionPoolDataSource();

//(sic) set the datasource parameters...

Connection c = c.getPooledConnection().getConnection();

CallableStatement cs = c.prepareCall("{call myStoredProcedure(?,?)");

//(sic) set the stored procedure parameters...

ResultSet rs = cs.executeQuery();
SQLWarning w = cs.getWarnings();
while( w!=null ){

System.out.println(w.getMessage());

w=w.getNextWarning()

}

When debugging, I see that the first call to

cs.getWarnings() returns a null value. I also tryed rs.getWarnings()

and c.getWarnings(), and both returned null as well. When calling the

same stored

procedure through QueryAnalyser, all the messages are there.

Any ideas on what might be causing this?

Thx,

Carlos

|||

What you have should have worked if the warning message appeared before the result set. If the message came after the result set, however, you would need to process the rows in the result set before the messages can be extracted with cs.getWarnings. To be sure that you're getting through all of the results, including any warnings, you should call Statement.getMoreResults until there are no more results. See http://java.sun.com/j2se/1.4.2/docs/api/java/sql/Statement.html#getMoreResults() for how.

Thanks,

--David

Getting exception on executing ExecuteCellSet on AdomdCommand object

I'm trying to execute an mdx query in C# and getting exception on calling ExecuteCellSet() on AdomdCommand object,

This is the code snippet

// initialize a new adomdcommand object for the specified connection

objCommand = connection.CreateCommand();

// supply the command text for the adomdcommand object

objCommand.CommandText = queryString;

// attempt to run the command on the cell cellset

try

{

objCellSet = objCommand.ExecuteCellSet();

// return the retrieved cellset

return objCellSet;

}

catch (Exception ex)

{

Console.Write("Exception: " + ex);

objCellSet = null;

return objCellSet;

}

Exception: System.Data.DuplicateNameException: A DataTable named 'UOM' already b
elongs to this DataSet.
at System.Data.DataTableCollection.RegisterName(String name)
at System.Data.DataTableCollection.BaseAdd(DataTable table)
at System.Data.DataTableCollection.Add(DataTable table)
at Microsoft.AnalysisServices.AdomdClient.MDDatasetFormatter.ReadAxesInfo(Xml
Reader reader)
at Microsoft.AnalysisServices.AdomdClient.MDDatasetFormatter.ReadMDDataset(Xm
lReader reader)
at Microsoft.AnalysisServices.AdomdClient.SoapFormatter.ReadDataSetResponsePr
ivate(XmlReader reader)
at Microsoft.AnalysisServices.AdomdClient.SoapFormatter.ReadExecuteResponsePr
ivate(XmlReader reader, Boolean throwOnInlineErrors)
at Microsoft.AnalysisServices.AdomdClient.SoapFormatter.ReadResponse(XmlReade
r reader, Boolean throwOnInlineErrors)
at Microsoft.AnalysisServices.AdomdClient.XmlaClientProvider.Microsoft.Analys
isServices.AdomdClient.IExecuteProvider.ExecuteMultidimensional(ICommandContentP
rovider contentProvider, AdomdPropertyCollection commandProperties, IDataParamet
erCollection parameters)
at Microsoft.AnalysisServices.AdomdClient.AdomdCommand.ExecuteCellSet()
at ceat.excel_writer.ceat_writer.ceat_writer_runQuery(AdomdConnection& connec
tion, String queryString)

When I run the query in MDX sample application editor, it is returning values.

Thanks for your help

DRK12345,

I am having a similar problem. I am wondering whether you solved your problem. Please let me know..

Thanks,

Golf

|||

hello,

which version of AS server are you connecting to? If this code works against AS2000, then i think the issue is that an older version of the msolap80 is used. Installing the latest version of msolap80 available should help i think. [can be downloaded for example from http://www.microsoft.com/downloads/details.aspx?FamilyID=df0ba5aa-b4bd-4705-aa0a-b477ba72a9cb&DisplayLang=en (under Microsoft SQL Server 2000 PivotTable Services) or hotfix http://support.microsoft.com/?kbid=819609 could be used as well i think).

hope this helps,

|||

Mary,

Installing latest version of msolap80 fixed the problem. Thanks a lot for the help. I appreciate it.

Getting exception on executing ExecuteCellSet on AdomdCommand object

I'm trying to execute an mdx query in C# and getting exception on calling ExecuteCellSet() on AdomdCommand object,

This is the code snippet

// initialize a new adomdcommand object for the specified connection

objCommand = connection.CreateCommand();

// supply the command text for the adomdcommand object

objCommand.CommandText = queryString;

// attempt to run the command on the cell cellset

try

{

objCellSet = objCommand.ExecuteCellSet();

// return the retrieved cellset

return objCellSet;

}

catch (Exception ex)

{

Console.Write("Exception: " + ex);

objCellSet = null;

return objCellSet;

}

Exception: System.Data.DuplicateNameException: A DataTable named 'UOM' already b
elongs to this DataSet.
at System.Data.DataTableCollection.RegisterName(String name)
at System.Data.DataTableCollection.BaseAdd(DataTable table)
at System.Data.DataTableCollection.Add(DataTable table)
at Microsoft.AnalysisServices.AdomdClient.MDDatasetFormatter.ReadAxesInfo(Xml
Reader reader)
at Microsoft.AnalysisServices.AdomdClient.MDDatasetFormatter.ReadMDDataset(Xm
lReader reader)
at Microsoft.AnalysisServices.AdomdClient.SoapFormatter.ReadDataSetResponsePr
ivate(XmlReader reader)
at Microsoft.AnalysisServices.AdomdClient.SoapFormatter.ReadExecuteResponsePr
ivate(XmlReader reader, Boolean throwOnInlineErrors)
at Microsoft.AnalysisServices.AdomdClient.SoapFormatter.ReadResponse(XmlReade
r reader, Boolean throwOnInlineErrors)
at Microsoft.AnalysisServices.AdomdClient.XmlaClientProvider.Microsoft.Analys
isServices.AdomdClient.IExecuteProvider.ExecuteMultidimensional(ICommandContentP
rovider contentProvider, AdomdPropertyCollection commandProperties, IDataParamet
erCollection parameters)
at Microsoft.AnalysisServices.AdomdClient.AdomdCommand.ExecuteCellSet()
at ceat.excel_writer.ceat_writer.ceat_writer_runQuery(AdomdConnection& connec
tion, String queryString)

When I run the query in MDX sample application editor, it is returning values.

Thanks for your help

DRK12345,

I am having a similar problem. I am wondering whether you solved your problem. Please let me know..

Thanks,

Golf

|||

hello,

which version of AS server are you connecting to? If this code works against AS2000, then i think the issue is that an older version of the msolap80 is used. Installing the latest version of msolap80 available should help i think. [can be downloaded for example from http://www.microsoft.com/downloads/details.aspx?FamilyID=df0ba5aa-b4bd-4705-aa0a-b477ba72a9cb&DisplayLang=en (under Microsoft SQL Server 2000 PivotTable Services) or hotfix http://support.microsoft.com/?kbid=819609 could be used as well i think).

hope this helps,

|||

Mary,

Installing latest version of msolap80 fixed the problem. Thanks a lot for the help. I appreciate it.

Getting error while executing sp_addextendedproperty in SQL 2005

I've read through existing threads on this topic but haven't found anything that matches my situation exactly.

I'm trying to update the MS_Description on every table in the database. A sample of the syntax is:

EXEC sp_addextendedproperty

@.name = 'MS_Description',

@.value = 'Persons email',

@.level0type = 'SCHEMA',

@.level0name = 'dbo',

@.level1type = 'TABLE',

@.level1name = 'email',

@.level2type = 'COLUMN',

@.level2name = 'cemail'

Fairly straightforward, right? I'm receiving that awful, vague error "Object is invalid. Extended properties are not permitted on 'dbo.email.cemail', or the object does not exist." I'm in the correct database and am logged in as 'sa'.

I need to update over 2,000 column names, so I really don't want to do this manually via SQL Server Management Studio. The only solutions I've seen so far include scripting out the tables (I have data in them already) and checking to make sure that the user has permissions to the table (again, I'm signed in as 'sa').

Does anyone have any idea how to get around this? Thanks in advance.

Please ignore! The column name was incorrect. I just started here and found out they use a naming convention of using the datatype in the column name on the list I received. So 'cemail' is really just 'email' and the 'c' represents a character data type. Sorry for the inconvenience. I found this by checking the sys.columns table.

Getting error context from executed package

Background:

I am executing a package programmatically from ASP.NET using C#. The package is being loaded from SQL Server at runtime and then it is executed from a custom class written in C#.

Problem:

When a task fails, I'd like information on why it failed. I read about implemeting the IDTSEvents interface as a way to have my package call back to code. This works fine (I have code in the OnTaskFailed event handler), but I'm not sure how to find out why a task failed. Since my task is loading a flat file to SQL Server, failure will likely come in one of two flavors: either the file format will be wrong or the data will violate a database constraint. In etiher case, I'd like that feedback. I am looking that the TaskHost object that gets passed to my event handler and I haven't figured out how to access this information. Am I going about this the right way? If yes, how do I access the error information I am looking for?

Try the OnError event handler. There are variables scoped to that eventhandler that contain the error code and error description.

-Jamie

|||

OK. That works fine. I can now get the description of the errror. When I get an error, I'd like to stop executing the package and surface the error to the through the UI (which happens to be a web page). There are a couple of things I noticed with this call back to the OnError event handler:

The event handler is triggered multiple times even though I return false, which is supposed to cancel the task. What I'd like is to abort the execution of the package (not just cancel the task). I tried to throw a custom exception within the handler, but the exception doesn't propogate from the OnError handler to my calling code (the code that executes the package) and it doesn't stop the handler from being called multiple times.

Any ideas?

Getting error 0x80010112 while executing stored package

Hello everybody,

I can't find any posts concerning this error. While executing the stored package (using Execute Package Utility). I get error: SSIS Execution Properties "Could not load package "\File System\migration_package_1" because of error 0x80010112." The package is there and it was correctly loaded.

Please can anybody suggest what do do now or some workaround?

Thank you for any suggestions

Patrik Svestka

It seems this error was produced due to the SQL Server 2005 client & windows xp registry error. The only way out of this problem is complete system reinstalation :(.

Patrik Svestka