How to remote processing reports?

Hi everyone!

I have my reports published in a report server. What do I need to do so that the user can processing reports from a windows application?

There are many ways to do that. Within VIsual Studio 2005 there is a reportviewer control which you can use within your application to contact and display report. If you only want to get a report from the Server and present the user a byte stream to save to a file (something like a button which says Save the report to PDF...) THen you can just use the Report Server Services interface. WHat do you actually want to do ? (Meanwhile you can take a look on: http://www.gotreportviewer.com/)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

sql

how to remote connection to an SQL server on the same network?

hi
i added 2 databases to my SQL server on a machine next door using Microsoft SQL server management studio express... I made sure that the server allows remote connection

then from this computer i pass a connection string to my SQLCLient in VB2005 that looks a bit like this

Code: ( text )

1. ConnString = "Server=USER-PTDH8Z5ESH\SQLEXPRESS;Database=FSS.MDB;Trusted_Con nection=True;User Instance=True"

for which it gives me this error when i open the connection
SQLConnecton.Open()

System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
at System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.Att emptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.Log inNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at System.Data.SqlClient.SqlInternalConnectionTds.Ope nLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ct or(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateC onnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.Creat ePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateOb ject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCrea teRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConne ction(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetCo nnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenCo nnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Flying_School_Scheduler.DBEngine.StartConnection() in

whats the big idea?
i'm in a bit of a tight schedule here... can anyone help me here? ?

First, let me express my aplogies for not getting to your question sooner. It would be not too trite to say that since most of us that take our time to respond to questions such as yours, are volunteers, and we are often also on a tight schedule. Simple, straightforward requests for assistance are looked at and perhaps even responded to far sooner than demanding, whining ones...

As to the 'big deal' with your connection string: There are a couple of possibilites:

Often using 'prohibited' characters in a server name requires extra handling, such as enclosing the name in square brackets. You might try:
Server=[USER-PTDH8Z5ESH\SQLEXPRESS]; Is the database itself named FSS.MDB, or could that, by mistake, be the database file name?|||

Normally, error 26 is caused by a few common problems that are easily fixed. Please take a look at this posting as it should probably fix your problem: http://blogs.msdn.com/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx

Thanks,

Il-Sung.

How to register remote server? Authentication method is grayed out!

In SQL Server management Studio. I was trying to add remote server, so it would show up in registered servers. The authentication method is grayed out!

The only option to connect to the server is by clicking connect button. It remebers the old settings. It also shows me all server databases, even I have selected my database in options...

Can you please describe exact steps that you are following. Also please make sure that the server type is 'Database Engine'.|||

I think the reason is that I have SQl Server Express installed, but SQL Server 2005 interface. Do you know how to upgrade it to full version? I have DVD, but got some errors while running setup again "Can not read from device...". Thanks

|||

You may have a problem with your DVD disk or device. You may want to check with product support to resolve this issue.

Note this should just work, you should not need to install another version.

|||SQL Server Management Studio supports connecting to SQL Express. Are you trying to connect from the same machine or from a remote box? DO you experience this problem for this instance only?|||Same machine, I have realased I did not upgrade the original 2000 version! It asks me about service packs, however I have XP SP 2 already installed...|||

Could you send a screen shot of the authentication dialog to my email address when it is in the state that you descibe?

Also, please provide step by step explanation how it gets into this state (please also send this as a reply to this posting). Example:

1. Start SQL Server Mananagement Studio

2. Right click Database Engine in the Registered Servers dialog

etc. -- you get the picture...

Note that you have to remove the 'online' part from my email address.

How to register remote server? Authentication method is grayed out!

In SQL Server management Studio. I was trying to add remote server, so it would show up in registered servers. The authentication method is grayed out!

The only option to connect to the server is by clicking connect button. It remebers the old settings. It also shows me all server databases, even I have selected my database in options...

Can you please describe exact steps that you are following. Also please make sure that the server type is 'Database Engine'.|||

I think the reason is that I have SQl Server Express installed, but SQL Server 2005 interface. Do you know how to upgrade it to full version? I have DVD, but got some errors while running setup again "Can not read from device...". Thanks

|||

You may have a problem with your DVD disk or device. You may want to check with product support to resolve this issue.

Note this should just work, you should not need to install another version.

|||SQL Server Management Studio supports connecting to SQL Express. Are you trying to connect from the same machine or from a remote box? DO you experience this problem for this instance only?|||Same machine, I have realased I did not upgrade the original 2000 version! It asks me about service packs, however I have XP SP 2 already installed...|||

Could you send a screen shot of the authentication dialog to my email address when it is in the state that you descibe?

Also, please provide step by step explanation how it gets into this state (please also send this as a reply to this posting). Example:

1. Start SQL Server Mananagement Studio

2. Right click Database Engine in the Registered Servers dialog

etc. -- you get the picture...

Note that you have to remove the 'online' part from my email address.

How to register remote server? Authentication method is grayed out!

In SQL Server management Studio. I was trying to add remote server, so it would show up in registered servers. The authentication method is grayed out!

The only option to connect to the server is by clicking connect button. It remebers the old settings. It also shows me all server databases, even I have selected my database in options...

Can you please describe exact steps that you are following. Also please make sure that the server type is 'Database Engine'.|||

I think the reason is that I have SQl Server Express installed, but SQL Server 2005 interface. Do you know how to upgrade it to full version? I have DVD, but got some errors while running setup again "Can not read from device...". Thanks

|||

You may have a problem with your DVD disk or device. You may want to check with product support to resolve this issue.

Note this should just work, you should not need to install another version.

|||SQL Server Management Studio supports connecting to SQL Express. Are you trying to connect from the same machine or from a remote box? DO you experience this problem for this instance only?|||Same machine, I have realased I did not upgrade the original 2000 version! It asks me about service packs, however I have XP SP 2 already installed...|||

Could you send a screen shot of the authentication dialog to my email address when it is in the state that you descibe?

Also, please provide step by step explanation how it gets into this state (please also send this as a reply to this posting). Example:

1. Start SQL Server Mananagement Studio

2. Right click Database Engine in the Registered Servers dialog

etc. -- you get the picture...

Note that you have to remove the 'online' part from my email address.

How to read data from remote server inside a transaction?

Hello, everyone:

I have a local transaction,

BEGIN TRAN
INSERT Z_Test SELECT STATE_CODE FROM View_STATE_CODE
COMMIT

View_STATE_CODE points to remote SQL server named PROD. There is error when I run this query:

Server: Msg 8501, Level 16, State 1, Line 12
MSDTC on server 'PROD' is unavailable.
Server: Msg 7391, Level 16, State 1, Line 12
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d01c].

It looks like remote server is not available inside the local transaction. How to handle that?

Thanks

ZYT1) make sure MSDTC is running, u can see that from SQl Service Manager.
2) add this code before 'begin tran' , SET XACT_ABORT ON
3) instead of 'begin tran' begin distributed tran'
4) And SET XACT_ABORT OFF, AFTER COMMIT TRAN
Eg:

SET XACT_ABORT ON
begin distributed tran

insert into sometable select * from remoteserver.database.dbo..foo

commit tran
SET XACT_ABORT OFF


or check this site http://support.microsoft.com/default.aspx?scid=kb;en-us;839279 if ur SQL Server 2000 server installed on Windows Server 2003 or Windows XP Service Pack 2|||Hello, Mailler:

Thanks. MSDTC is running. The query you posted doesn't work.

ZYT|||what error u getting now.Is SQL Server 2000 server installed on Windows Server 2003 or Windows XP Service ?|||Hi, Mailler:

Thanks. The error is still "MSDTC on server 'PROD' is unavailable.
". The SQL Server 2000 is running on Windows XP SP1. Is it the reason?

ZYT|||check the link i given in first reply

How to query an remote excel file using OPENROWSET

Hi,

can anyone help me out this problem.

i want to query an excel file which at remote location through openrowset.

Select * from openrowset(.... ?

thanks

Sunila, this will query an excel spreadsheet that is local or on a shared network drive:

SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Home\dannyboy\WORK\opeid_PEPS_database\PEPS_Reference_Codes.xls', [ref_territory$])

Replace the database=\\server\direcorty\filename.xls to access a different server (say a remote server). In the above case, ref_territory is a tab name in the Excel spreadsheet. Note you need to add a $ at the end of the tab name.

You could also define a linked server to the excel spreadsheet and then query without using OPENROWSET...

Assume Linked Server name is SomeLinkedServer

Select * from SomeLinkedServer...ref_territory$ (note there are 3 periods between Linked Server name and tab name)

See Books Online concerning OPENROWSET and/or Linked Servers for more info.

|||

thanks a lot

i need more clarification for the same. if the excel file is kept in a remote mechine then i need to access the same (\\ipaddress\foldername\xlsfilename.xls). the same query will work if i give like this?

|||Sunila, give it a try. It should work assuming the security is in place to allow you to access the remote directory or the services (SQL Agent, etc) have proper security if you are running this from a job...|||

i got another solution..

like

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;HDR=Yes;IMEX=1;database=\\<computername>\filename.xls;',
'SELECT * FROM [Sheet1$]')

How to query a remote server from sql 2005

Ok,,im stuck.
I can access a remote 2000 sql server from sql 2005. I connect directly to
the 2000 database ok. I can run queries on it all day.
I can do the same thing with another remote ODBC server with SQL2005.
THe question is how do you login to SQL2000 from sql 2005 and yet still be
able to run queries on the other remote ODBC server?
--
HarryTry using opendatasource which will accept a connection string to access the
remote server.
--
"Harry" wrote:

> Ok,,im stuck.
> I can access a remote 2000 sql server from sql 2005. I connect directly t
o
> the 2000 database ok. I can run queries on it all day.
> I can do the same thing with another remote ODBC server with SQL2005.
> THe question is how do you login to SQL2000 from sql 2005 and yet still be
> able to run queries on the other remote ODBC server?
> --
> Harry|||Create linked servers and use openquery() method.
"Omnibuzz" wrote:
> Try using opendatasource which will accept a connection string to access t
he
> remote server.
> --
>
>
> "Harry" wrote:
>|||Create linked servers and use OpenQuery() method
"Harry" wrote:

> Ok,,im stuck.
> I can access a remote 2000 sql server from sql 2005. I connect directly t
o
> the 2000 database ok. I can run queries on it all day.
> I can do the same thing with another remote ODBC server with SQL2005.
> THe question is how do you login to SQL2000 from sql 2005 and yet still be
> able to run queries on the other remote ODBC server?
> --
> Harry