Problems installing SRS

I'm trying to install Reporting Services on a new XP Pro SP2 IIS 6
development box. The local database is SQL Server 2000 sp3.
At the end of the installation I get an error message indicating that
the 'Setup could not initialize the report server' and that I'll have
to manually initialize the Report Server.
After running rsactive I got the message indicating that the "Report
Server Web Service has not generated a public key".
Looking in the log files is less-than-helpful (hundreds of lines
indicating that some -unknown- file is missing):
===============================================================================
aspnet_wp!library!a4!12/01/2004-10:15:58:: e ERROR: Throwing
Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
An internal error occurred on the report server. See the error log for
more details., ;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
An internal error occurred on the report server. See the error log for
more details. --> System.IO.FileNotFoundException: The system cannot
find the file specified.
at System.Runtime.InteropServices.Marshal.ThrowExceptionForHR(Int32
errorCode, IntPtr errorInfo)
at RSManagedCrypto.RSCrypto.ExportPublicKey()
at Microsoft.ReportingServices.Library.ConnectionManager.GetEncryptionKey()
at Microsoft.ReportingServices.Library.ConnectionManager.ConnectStorage()
at Microsoft.ReportingServices.Library.ConnectionManager.VerifyConnection()
at Microsoft.ReportingServices.Library.ConnectionManager.get_Connection()
at Microsoft.ReportingServices.Library.Storage.get_Connection()
at Microsoft.ReportingServices.Library.Storage.NewStandardSqlCommand(String
storedProcedureName)
at Microsoft.ReportingServices.Library.DBInterface.GetOneConfigurationInfo(String
key)
at Microsoft.ReportingServices.Library.CachedSystemProperties.GetSystemProperty(String
name)
at Microsoft.ReportingServices.Library.CachedSystemProperties.Get(String
name)
at Microsoft.ReportingServices.Library.CachedSystemProperties.GetParameter(String
name)
at Microsoft.ReportingServices.Library.RSService.get_MyReportsEnabled()
at Microsoft.ReportingServices.Library.RSService.PathToInternal(String
source)
at Microsoft.ReportingServices.Diagnostics.CatalogItemContext.SetPath(String
path, Boolean validate, Boolean convert, Boolean translate)
at Microsoft.ReportingServices.Diagnostics.CatalogItemContext.SetPath(String
path)
at Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderPageContent()
at Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderPage()
-- End of inner exception stack trace --
a
===============================================================================
When I navigate to the http://localhost/ReportServer I get the
following error message:
An internal error occurred on the report server. (again not very
helpful).
IIS is listening on port 80 (and there are no other apps on the port).
SO... I'm running out of things to try and patience. Is there anyone
out there who can help me get over this problem? I really need to
get this installed ASAP and there doesn't seem to be any documentation
anywhere that explains what is going wrong!!!
Any help appreciated.
GlennAfter much trail-and-error and finally a call to Microsoft I was able to
install the SRS and get everything working. I'm posting this in hopes that my
solution will help someone in a similar situation.
Ultimately the problem was solved by:
1) accessing the machine.config file
2) find the user name in the process model section (by default it's set
to 'machine')
3) change this to 'system' (temporarily)
4) using EnterpriseManager open the Keys table of the ReportServer
database (three are probably 2 keys)
5) delete the NON-ZERO key (don't delete the ZERO key)
6) browse to http:/localhost/ReportServer (should no longer produce any
errors!!!)
7) browse to http:/localhost/Reposrts
Hope this helps...
Glenn
"Glenn" wrote:
> I'm trying to install Reporting Services on a new XP Pro SP2 IIS 6
> development box. The local database is SQL Server 2000 sp3.
> At the end of the installation I get an error message indicating that
> the 'Setup could not initialize the report server' and that I'll have
> to manually initialize the Report Server.
> After running rsactive I got the message indicating that the "Report
> Server Web Service has not generated a public key".
> Looking in the log files is less-than-helpful (hundreds of lines
> indicating that some -unknown- file is missing):
> ===============================================================================> aspnet_wp!library!a4!12/01/2004-10:15:58:: e ERROR: Throwing
> Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
> An internal error occurred on the report server. See the error log for
> more details., ;
> Info: Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
> An internal error occurred on the report server. See the error log for
> more details. --> System.IO.FileNotFoundException: The system cannot
> find the file specified.
> at System.Runtime.InteropServices.Marshal.ThrowExceptionForHR(Int32
> errorCode, IntPtr errorInfo)
> at RSManagedCrypto.RSCrypto.ExportPublicKey()
> at Microsoft.ReportingServices.Library.ConnectionManager.GetEncryptionKey()
> at Microsoft.ReportingServices.Library.ConnectionManager.ConnectStorage()
> at Microsoft.ReportingServices.Library.ConnectionManager.VerifyConnection()
> at Microsoft.ReportingServices.Library.ConnectionManager.get_Connection()
> at Microsoft.ReportingServices.Library.Storage.get_Connection()
> at Microsoft.ReportingServices.Library.Storage.NewStandardSqlCommand(String
> storedProcedureName)
> at Microsoft.ReportingServices.Library.DBInterface.GetOneConfigurationInfo(String
> key)
> at Microsoft.ReportingServices.Library.CachedSystemProperties.GetSystemProperty(String
> name)
> at Microsoft.ReportingServices.Library.CachedSystemProperties.Get(String
> name)
> at Microsoft.ReportingServices.Library.CachedSystemProperties.GetParameter(String
> name)
> at Microsoft.ReportingServices.Library.RSService.get_MyReportsEnabled()
> at Microsoft.ReportingServices.Library.RSService.PathToInternal(String
> source)
> at Microsoft.ReportingServices.Diagnostics.CatalogItemContext.SetPath(String
> path, Boolean validate, Boolean convert, Boolean translate)
> at Microsoft.ReportingServices.Diagnostics.CatalogItemContext.SetPath(String
> path)
> at Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderPageContent()
> at Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderPage()
> -- End of inner exception stack trace --
> a
> ===============================================================================> When I navigate to the http://localhost/ReportServer I get the
> following error message:
> An internal error occurred on the report server. (again not very
> helpful).
> IIS is listening on port 80 (and there are no other apps on the port).
>
> SO... I'm running out of things to try and patience. Is there anyone
> out there who can help me get over this problem? I really need to
> get this installed ASAP and there doesn't seem to be any documentation
> anywhere that explains what is going wrong!!!
> Any help appreciated.
> Glenn
>

Problems deploying website

My ASP.NET application runs fine within the VS2005 IDE; but, when I deploy to my local machine to try to run it under IIS and calling it up in a browser, I'm getting the following exception:

Exception Details: System.Data.SqlClient.SqlException: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734979
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
System.Data.SqlClient.SqlConnection.Open() +111
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83
System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770
System.Web.UI.WebControls.ListControl.OnDataBinding(EventArgs e) +92
System.Web.UI.WebControls.ListControl.PerformSelect() +31
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
System.Web.UI.WebControls.ListControl.OnPreRender(EventArgs e) +26
System.Web.UI.Control.PreRenderRecursiveInternal() +77
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360

I'm a novice at this and really need some help. What stupid newbie mistake am I making here? Thanks in advance for any guidance/assistance rendered.

When you deplyed the app you left the same SQL trusted connection, and most probably there is no local SQL server running in the same machine. Make sure to change the SQL string to the IP, username and password of the database and will work really well

Al

|||

I remember I got this problem too when I did my first try. It seems that your are using trusted connection, you need assign the domain user in your database with appropriate access right. Or you can create a database user in the database to use standard User/password connection method in your database is in mixed mode..

You can easily find the syntax for the connection string. As to create user in database, you need to create a login account under Security on the Server, in the database, you assign this user for access under database security tab.

Limno

Problems connectiong to a remote DB

Hello

I developed a vb.net win app which interacts with a sqlserver 2005 express DB.

When the DB is located in my local computer everything works just fine.

I'm trying to connect to a remote DB (mdf file that located on a remote server) but without any success.

I assume that a connectionstring change is needed, however I can't find what change to perform (I tried to insert the server IP address to the connectionstring but it didn't work either).

the app connectionstring looks like that:

connectionString

="Data Source=.\SQLExpress;AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DBName.mdf;Integrated Security=True;User Instance=False" providerName="System.Data.SqlClient" />

Hope someone can help me find the solution.

Since your question concerns a Windows Forms application, and not ASP.NET, you should ask it on theMSDN Forums.|||Try looking atwww.connectionstrings.com...|||Hi yarit, post the connection failure error message would help to solve the issue. You can also take a look at this post:
http://forums.asp.net/thread/1289341.aspx|||

Hello

Firstly thanks for all of your replies.

Now, I've succeeded to connect my app to a remote DBbut only in my company's network, the DB is located on a different machine in the company's network and I can connect to it using this connection string:

connectionString

="Data Source=Inner network IP address \ SQLEXPRESS;Database=DBName;Uid=xxx;Pwd=yyy;"providerName="System.Data.SqlClient">

However I have no success connecting it to an outerIP address, although the machine is connected to the internet via a router and has an outerIP address.

I'll justindicate that my GUI is supposed to be accessed via the internet (a web app) so I must be able to connecteventually via an outer IP address.

Does anybody know if I need to change some config or if it is a pure network problem.

|||

Hello

Firstly thanks for all of your replies.

Now, I've succeeded to connect my app to a remote DBbut only in my company's network, the DB is located on a different machine in the company's network and I can connect to it using this connection string:

connectionString

="Data Source=Inner network IP address \ SQLEXPRESS;Database=DBName;Uid=xxx;Pwd=yyy;"providerName="System.Data.SqlClient">

However I have no success connecting it to an outerIP address, although the machine is connected to the internet via a router and has an outerIP address.

I'll justindicate that my GUI is supposed to be accessed via the internet (a web app) so I must be able to connecteventually via an outer IP address.

Does anybody know if I need to change some config or if it is a pure network problem.

|||Hi yairt, first of all you have to make sure on teh SQL box no firewall is blocking TCP/IP communication to the SQL Express service from Internet. You can use telnet to check this, please refer to the post I mentioned in my previous post.|||

hi Iori jay

thanks for your reply.

The problem was solved (or better said was by-passed), apparently it has nothing to do with SQLSERVER or connection strings.

We connected the server to the internet via ADSL line (`and not through a router that probably concealed its address) and everything worked just fine.

Problems connecting to server using Management Studio

Hi all,
I got this problem which makes me struggle in the last few days.
I am able to connect to my local server using a query analyzer or calling sql commands in a window application, but there's no way I can access it in Management Studio: by clicking "Connect" it just waits forever, and it doesn't respond to any other command (it must be shut by terminating the process)
Got any hints?

Thanks

Mauro

Try restarting the sql services may be your server is in hunged state......but i am not sure if that would solve the problem

|||Unfortunately, it doesn't do the trick... in fact, it's like Manag. Studio is able to connect to the server (I can see the list of databases in advanced options) but it doesn't show the result afterwards. Could it be a security/account problem?

Thanks

M|||Hi again,
just another hint trying to understand what's going on.
I just spent a couple of hours removing both the server and the client components and install it again, but the problems remains. Does configuration survive removal? Or should I look somewhere else?

Thanks

Mauro
|||From Management Studio are u trying to connect using Windows authentication or SQL authentication?
|||Both actually. And the result is the same connecting to a local or remote machine, that's why I am quite curious about this error (that in fact makes development harder... )

Mauro

Problems connecting to server using Management Studio

Hi all,
I got this problem which makes me struggle in the last few days.
I am able to connect to my local server using a query analyzer or calling sql commands in a window application, but there's no way I can access it in Management Studio: by clicking "Connect" it just waits forever, and it doesn't respond to any other command (it must be shut by terminating the process)
Got any hints?

Thanks

Mauro

Try restarting the sql services may be your server is in hunged state......but i am not sure if that would solve the problem

|||Unfortunately, it doesn't do the trick... in fact, it's like Manag. Studio is able to connect to the server (I can see the list of databases in advanced options) but it doesn't show the result afterwards. Could it be a security/account problem?

Thanks

M|||Hi again,
just another hint trying to understand what's going on.
I just spent a couple of hours removing both the server and the client components and install it again, but the problems remains. Does configuration survive removal? Or should I look somewhere else?

Thanks

Mauro
|||From Management Studio are u trying to connect using Windows authentication or SQL authentication?
|||Both actually. And the result is the same connecting to a local or remote machine, that's why I am quite curious about this error (that in fact makes development harder... )

Mauro

Problems connecting to local SQL server [HELP!]

Hi all,

I have the developer edition of Visual Studio 2005. After successfully installing it in my local machine, I wanted to create a small application which connects to a database. I accessed the Server Explorer tool, right clicked on Data Connections and brought up the Create New SQL Server Database option.

The issue now is that I don't see a list of servers in the "Server Name" drop down box although in the Server Explorer itself, I have a server listed (your-c6b5e4eabc - The default computer name of my local machine).

I am given to believe that SQL server is unable to make a connection because I have not configured something which is essential.

I hope someone can point me as to how to make databases using SQL server and connect them to the application the way developers usually do. I'm a complete novice to VS 2005, and the SQL server configuration options have got me confused.

I'm not sure if this will help, but I'll include this here anyway :

When I tried copying and pasting the server name (your-c6b5e4eabc) into the "Server Name" drop down box and creating a database by entering a name in the "Enter database name" textbox, I get the following error message -

An error occured 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 : Named Pipes Provider error : 40 Error : Could not open a connection to SQL Server.

I'm running Windows XP Media Center Edition v2002 SP 2.

I just want to be able to create and work with databases from within the applications I create in Visual Studio 2005. I played around with the configuration options (like enabling some protocols, the SQL server browser etc., but to no avail.) Is there a way to revert back to the default settings in an easy way? Thanks in advance for all your guidance...

Have a look in my screencasts section on my site showing you the way to enabled remote connections.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

|||

Hey there,

Thanks very much for your screencast link. I think we should have more such user-friendly guides for first timers to Visual Studio. Unfortunately, I'm still facing the same problem creating a database from the Server Explorer.

I also have a rider question here : Although I've made a typical installation of Visual Studio 2005, the service I seem to be running isn't MSSQLSERVER, but rather SQLEXPRESS. I really need someone to throw more light on this aspect of Visual Studio 2005...or is my case an aberration?

Also, I mentioned playing around with the settings of the SQL Server 2005 Configuration Manager...can that be affecting things in any way? What are the ideal settings to connect to the server from within the server explorer in VS 2005? Most notably, I've set the SQL Server 2005 Services (SQLExoress and SQLBrowser) to log on as "NT Authority/LocalService"...is this the right setting?

Thanks again.

|||

Hi, thanks for the feedback, if you have any upcoming ideas about screencasts, feel free to contact me :-).

1.
SQL Server Express comes with VS 2005, you can use SQL Server Express at no charge. The SQL Server instances are reflected in the Service names, which are for a default instance MSSQLSERVER and for named Instances MSSQL$InstanceName. Named instances can be accessed by using the MachineName\InstanceName (and in addition if you don′t use the default port the ",portnumber" addition to the) name.

2.
Sure, changing the configuration will affect the current SQL Server instance (some changes might be reflected after the next reboot, like the remote connections setting). Local Services is fine if you don′t need any network ressources.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Problems connecting to "(LOCAL)" from SSIS

I've recently moved from June CTP to RTM. I'm now having a problem connecting to the (LOCAL) server in SSIS via a Native OLE DB\Microsoft OLE DB Provider for SQL Server connection. The error is:

Test connection failed because of an error in initializing provider. [DBNETLIB][ConnectionOpen(Connect()).]SQL Server does not exist or access denied.

I'm connecting with Windows Authentication, and I can connect to (LOCAL) with Windows Authentication via Management Studio. If I change the server name to the actual name of my development box then it will connect. I'd like to use (LOCAL) though so it's easily portable between different development environments.

Anyone have any ideas?This is not an SSIS problem; the underlying OLE DB provider is evaluating server names.

Have you tried with "localhost" or "."?

Thanks.|||This doesn't appear to work unless you have TCP/IP enabled. It is disabled by default. It seems that "(local)" no longer forces the use of Shared Memory.

-Doug

Problems Backing up database to a PC with 200GB drive

SQL Server 2000 on Windows 2000.
Is this possible. I try to create a new backup device and the only thing I
can locate is the local partitioned drives. I try to put in a UNC and it
does not work.
Is this not possible to put in a UNC to a shared drive on a PC. The backup
is needed to get off of the server because of space. We don't have a server
big enough to handle the size of the database.
Is it possible this is a a rights issue and I can't get off the server due
to rights. I have shared the heck out of the share on the PC.
Thanks for any help.
Gordon
I assume you tried mapping a drive letter to the share? I have a network
share mapped as a local drive letter "L:". I just did a backup from SQL2K to
"L:\backup\mydb.bak" using Enterprise Mgr. I added that path to the backup
device window in the backup dialog. What happens when you do this?
"Gordon" wrote:

> SQL Server 2000 on Windows 2000.
> Is this possible. I try to create a new backup device and the only thing I
> can locate is the local partitioned drives. I try to put in a UNC and it
> does not work.
> Is this not possible to put in a UNC to a shared drive on a PC. The backup
> is needed to get off of the server because of space. We don't have a server
> big enough to handle the size of the database.
> Is it possible this is a a rights issue and I can't get off the server due
> to rights. I have shared the heck out of the share on the PC.
> Thanks for any help.
> Gordon
|||I cannot see the share when I mapped it. When I go into the backup device
dialog, I can only see the partitioned drives. When I use a UNC to get to
anything else I get a message saying, "The backup device cannot see the
drive. Do you want to continue using this backup device."
Funny thing is, I tried this on another SQL server and I can get to the
shared drive on the PC. It is only on this server. Some setting somewhere.
All the rights are the same for the logged in account on both servers.
UGGGGHHHHH.
Thanks for any help you can give.
"kaborka" wrote:
[vbcol=seagreen]
> I assume you tried mapping a drive letter to the share? I have a network
> share mapped as a local drive letter "L:". I just did a backup from SQL2K to
> "L:\backup\mydb.bak" using Enterprise Mgr. I added that path to the backup
> device window in the backup dialog. What happens when you do this?
> "Gordon" wrote:
|||When a backup occurs, regardless of who is doing it the account that gets
used is the one that SQL Server is running under. THat account must be a
domain account with permissions to read and write to that share. UNC's are
the preferred way to backup to a remote share but you need the rght account
to access it.
Andrew J. Kelly SQL MVP
"Gordon" <Gordon@.discussions.microsoft.com> wrote in message
news:42BB3F1A-DCDC-471D-A221-2221304FE4F0@.microsoft.com...[vbcol=seagreen]
>I cannot see the share when I mapped it. When I go into the backup device
> dialog, I can only see the partitioned drives. When I use a UNC to get to
> anything else I get a message saying, "The backup device cannot see the
> drive. Do you want to continue using this backup device."
> Funny thing is, I tried this on another SQL server and I can get to the
> shared drive on the PC. It is only on this server. Some setting
> somewhere.
> All the rights are the same for the logged in account on both servers.
> UGGGGHHHHH.
> Thanks for any help you can give.
> "kaborka" wrote:

Problems Backing up database to a PC with 200GB drive

SQL Server 2000 on Windows 2000.
Is this possible. I try to create a new backup device and the only thing I
can locate is the local partitioned drives. I try to put in a UNC and it
does not work.
Is this not possible to put in a UNC to a shared drive on a PC. The backup
is needed to get off of the server because of space. We don't have a server
big enough to handle the size of the database.
Is it possible this is a a rights issue and I can't get off the server due
to rights. I have shared the heck out of the share on the PC.
Thanks for any help.
GordonI assume you tried mapping a drive letter to the share? I have a network
share mapped as a local drive letter "L:". I just did a backup from SQL2K t
o
"L:\backup\mydb.bak" using Enterprise Mgr. I added that path to the backup
device window in the backup dialog. What happens when you do this?
"Gordon" wrote:

> SQL Server 2000 on Windows 2000.
> Is this possible. I try to create a new backup device and the only thing
I
> can locate is the local partitioned drives. I try to put in a UNC and it
> does not work.
> Is this not possible to put in a UNC to a shared drive on a PC. The backu
p
> is needed to get off of the server because of space. We don't have a serv
er
> big enough to handle the size of the database.
> Is it possible this is a a rights issue and I can't get off the server due
> to rights. I have shared the heck out of the share on the PC.
> Thanks for any help.
> Gordon|||I cannot see the share when I mapped it. When I go into the backup device
dialog, I can only see the partitioned drives. When I use a UNC to get to
anything else I get a message saying, "The backup device cannot see the
drive. Do you want to continue using this backup device."
Funny thing is, I tried this on another SQL server and I can get to the
shared drive on the PC. It is only on this server. Some setting somewhere.
All the rights are the same for the logged in account on both servers.
UGGGGHHHHH.
Thanks for any help you can give.
"kaborka" wrote:
[vbcol=seagreen]
> I assume you tried mapping a drive letter to the share? I have a network
> share mapped as a local drive letter "L:". I just did a backup from SQL2K
to
> "L:\backup\mydb.bak" using Enterprise Mgr. I added that path to the backu
p
> device window in the backup dialog. What happens when you do this?
> "Gordon" wrote:
>|||When a backup occurs, regardless of who is doing it the account that gets
used is the one that SQL Server is running under. THat account must be a
domain account with permissions to read and write to that share. UNC's are
the preferred way to backup to a remote share but you need the rght account
to access it.
Andrew J. Kelly SQL MVP
"Gordon" <Gordon@.discussions.microsoft.com> wrote in message
news:42BB3F1A-DCDC-471D-A221-2221304FE4F0@.microsoft.com...[vbcol=seagreen]
>I cannot see the share when I mapped it. When I go into the backup device
> dialog, I can only see the partitioned drives. When I use a UNC to get to
> anything else I get a message saying, "The backup device cannot see the
> drive. Do you want to continue using this backup device."
> Funny thing is, I tried this on another SQL server and I can get to the
> shared drive on the PC. It is only on this server. Some setting
> somewhere.
> All the rights are the same for the logged in account on both servers.
> UGGGGHHHHH.
> Thanks for any help you can give.
> "kaborka" wrote:
>

problem with workflow in dts designer...looping?

what im trying to do is:

1) ftp a file from a server to a local directory,

2) check the file for its lastmodified date

3) depending on a constraint, perform a data import.

However, if the file is not modified, this means that the server thats supposed to ftp and update the file hasnt done its job yet, in this case i would like dts to wait a few minutes and then again go to step 1. I want this to repeat for a whole hour until the data has finally imported OR if it hasnt imported at the end of the hour it will send me an email saying it failed.

The closest I have come to this type of functionality in DTS designer is picture 1.

In picture 1 i am using the WAITFOR DELAY '000:02:00' to wait 5 seconds between every step. However To do this would require me to create about 30 iterations to span the whole hour! The activeX portion works fine its just learning the flow in dts designer that is giving me the problems.

It would be so much easier to do what I did in picture 2, but nothing runs.

here are the pics:
http://www.geocities.com/samirrahan/picture1.gif
http://www.geocities.com/samirrahan/picture2.gif

I would appreciate any help you can offer.



Thank you.I would do it differently...

Each time I successfully import a file (eg I have found a new file and processed it) I would set the next run date.

I would then set a job up to run the dts package every X minutes (5 for example) between the hours that you expect the file to turn up.

The first step of the package would be to check if it is the run date is less then or equal to today, if it is then you continue processing, if not then you halt processing.

This will solve your problem. Yes, it will mean that the package will run more often then it technically needs to, but it will only do the actual processing once.

HTH|||here is the link to my workflow pictures:
http://www.geocities.com/samirrahan/index.html

ideally i would want the dts to stop running as soon as the file import is successful. i guess i could do that on a success by rescheduling the job.

by the way on another note? do u use the designer or just a vb exe. itself? can this be alot easier if i dont use the designer?

thanks.|||I use the designer...

There is a method to change the jobs schedule using sql. Using sp_add_jobschedule and sp_delete_jobschedule what you could do is...

step 1, check for new file if fail end DTS Pakcage else step 2

step 2, ftp file

step 3, process file

step 4, execute sql task - sp_delete_jobschedule

step 5, execute sql task - sp_add_jobschedule

Problem with way to access all recordsets returned by sp_spaceused using ODBC APIs on C++

Hi all,
I have a problem executing a sp_spaceused on a database.
Please execute sp_spaceused on your local database so that further
discussion will be more easier to understand.
The code snippet is as follows :
SQLCHAR database_size[15];
SQLCHAR unused[15];
The database_size is member of first record set and
unused is member of second recordset returned by the sp_spaceused.
SQLINTEGER rlength;
retcode = SQLAllocHandle(SQL_HANDLE_STMT,hDBC,&hStmt);
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
{
retcode = SQLExecDirect(hStmt,(unsigned char *)sqlCommand,
SQL_NTS);
retcode = SQLBindCol(hStmt,2, SQL_CHAR, (SQLPOINTER) &database_size,
sizeof(database_size),&rlength);
//Problem - was not able to bind any
column of the second recordset so not able to fetch the unused space.
if (retcode != SQL_SUCCESS )
{
AfxMessageBox("Error occured while binding");
}
CString csDatabase_size;
//The while loop below returns after the first iteration as only first
recordset is available and nothing is accessible of second recordset so
cant get 'unused' field
while ((retcode = SQLFetch(hStmt)) == SQL_SUCCESS)
{
stored the return
values of database_size and unused.
}
SQLFreeHandle(SQL_HANDLE_STMT, hStmt);
}
else
{
AfxMessageBox("error while allocating a statement handle");
}
So the Problem is :
Is there any way to access all recordsets returned by sp_spaceused
using ODBC APIs on C++?
Please reply.
Thanks in advance
-Ashutosh
exec sp_spaceused returns:
Result set 1:
database_name
database_size
unallocated space
Result set 2:
reserved
data
index_size
unused
So, from your description you are trying to get database_size (col2) from
first result set, and unused (col4) from second result set.
You have to:
SQLExecDirect()
SQLBindCol( database_name, col2 )
SQLFetch() until all records read from first result set
SQLMoreResults() if returns SQL_SUCESS then you have another result set, so
...
SQLFreeStmt( SQL_UNBIND )
SQLBindCol( unused, col4 )
SQLFetch() until all records read from second result set
cmk
|||Thanx alot for your help !
Chris Kushnir wrote:
> exec sp_spaceused returns:
> Result set 1:
> database_name
> database_size
> unallocated space
> Result set 2:
> reserved
> data
> index_size
> unused
> So, from your description you are trying to get database_size (col2) from
> first result set, and unused (col4) from second result set.
> You have to:
> SQLExecDirect()
> SQLBindCol( database_name, col2 )
> SQLFetch() until all records read from first result set
> SQLMoreResults() if returns SQL_SUCESS then you have another result set, so
> ...
> SQLFreeStmt( SQL_UNBIND )
> SQLBindCol( unused, col4 )
> SQLFetch() until all records read from second result set
>
> cmk

Problem with VPN connection on SQL server

> Provider=sqloledb;driver={SQL Server};Network=DBMSSOCN;uid=xxx;seagreen">
> server=evnapp;database=xxx and so on the local engine is connected ?
I think you mess ODBC and OLEDB connection strings here...
I think it must be
Provider=sqloledb;Data Source=123.123.123.123,1433;Network
Library=DBMSSOCN;Initial Catalog=yourdb;User ID=dbuser;Password=dbpass
Bojidar AlexandrovNo.
This connections string works fine. I do have it working with other
customers.
By the way it also works in the LAN enviroment.....
"Bojidar Alexandrov" <bojo@.kodar.net> schreef in bericht
news:uiFAzXFNEHA.2996@.TK2MSFTNGP12.phx.gbl...
> I think you mess ODBC and OLEDB connection strings here...
> I think it must be
> Provider=sqloledb;Data Source=123.123.123.123,1433;Network
> Library=DBMSSOCN;Initial Catalog=yourdb;User ID=dbuser;Password=dbpass
>
> Bojidar Alexandrov
>|||I do not say that it does not work, but that it is wrong. Please see
somewhere conenction string reference

Problem with VPN connection on SQL server

Hi,
my customer has a strange problem. In the LAN-enviroment the application can
connect the local SQL server as well as on the central database. The problem
occurs when a laptopuser wants to connect the central server via VPN.
The server (evnapp) and the disks of the server are accessible. A ping gives
the correct IP address.
But when connecting the database via:
Provider=sqloledb;driver={SQL Server};Network=DBMSSOCN;uid=xxx;
server=evnapp;database=xxx and so on the local engine is connected ?
There is a host table on the pc (laptops) which maps evnapp to the correct
IP.
When using the IP addres in stead of the server name the same thing happens.
A local connection.
The only time it does work is when the host table is removed after the
connection is estabilshed.
Is it the host table? Or the VPN-software?(Tunix/VPN for windows) . What can
it be. But stations (desktops) who were installed several months ago are
using the same host table and do not have any problems.
All pc's are running win2000. SQL 2000 is used.
Does this ring a bell ? If so please reply
Ton
begin 666 LMHOSTS.dat
M,3(W+C$V."XQ.# N-#@.)979N9&,P,0T*,3(W+C$V."XQ.# N-30)979N9&,P
M,@.T*,3(W+C$V."XQ.# N,S4)979N9G,P,0T*,3(W+C$V."XQ.# N-#,);FAL
M<W)V,#0-"C$R-RXQ-C@.N,3@.T+C0R"65V;F1C<F0-"C$R-RXQ-C@.N,3@.P+C0W
D"65V;F%P<# R#0HQ,C<N,38X+C$X,"XS- EN:&QS<G8Q. T*
`
end
begin 666 hosts.dat
M(R!(;W-T($-O<'ER:6=H="!.:65U=R!(;VQL86YD<V4@.3&QO>60@.,3DM,#8M
M,C P,@.T*#0HQ,C<N,"XP+C$@.(" @.(" @.;&]C86QH;W-T#0H-"B,@.<VUT< T*
M,3(W+C$V."XQ.# N-#,);FAL<W)V,#0-"@.T*(R!(5%10#0HQ,C<N,38X+C$X
M,"XS,0EN:&QS<G8Q.0T*,3(W+C$V."XQ.# N,S$):6YT<F%N970N;FAL+79E
M<GIE:V5R:6YG96XN;FP-"@.T*(R!!4R\T,# -"C$R-RXQ-C@.N,3@.P+C(S"5,T
M-$$V,#(V#0HQ,C<N,38X+C$X,"XR- E3-#1&,38Q. T*,3(W+C$V."XQ.# N
M,C8)4S0T,4%#.4$-"@.T*(R!305 -"C$R-RXQ-C@.N,3@.P+C8P"65V;G-A<'!R
M9 T*,3(W+C$V."XQ.# N,S<)979N<V%P86-C#0HQ,C<N,38X+C$X,"XU.0EE
M=FYS87!O;G0-"C$R-RXQ-C@.N,3@.P+C,V"65V;G-A<&1E=@.T*#0HC($YE=$))
M3U,-"C$R-RXQ-C@.N,3@.P+C0X"65V;F1C,#$-"C$R-RXQ-C@.N,3@.P+C4T"65V
M;F1C,#(-"C$R-RXQ-C@.N,3@.P+C,U"65V;F9S,#$-"C$R-RXQ-C@.N,3@.P+C0V
M"65V;F9S,#(-"C$R-RXQ-C@.N,3@.P+C0U"65V;FEI<S Q#0HQ,C<N,38X+C$X
M,"XV,0EE=FYI<V$-"C$R-RXQ-C@.N,3@.P+C,T

Problem with UPDATE to another server.

Hi Arnie!

Thanks for the info!! So I got that join to work, but now I need to take that data from "SAHTest" and update Pharm Local Test. I'm not sure what is the best and quickest way. Do I make a temp table, or can i grab it from "inserted"? Any way, here was my shot.

DECLARE @.Acct nvarchar(50)

SET @.Acct ='L1234'

BEGIN

SELECT x.account, x.Fname, x.lname, x.dob

FROM [MKE01-Demo-01].SAHPharm.dbo.testact AS x JOIN

[Pharm Test Local].dbo.Active_Orders AS t ON t.Account_Number = x.account

WHERE(x.account = @.Acct)

END

BEGIN

UPDATE [Pharm Test Local].dbo.Active_Orders AS t

SET Account = t.Account_Number, Fname = t.First_Name, LName = t.Last_Name,

dob = t.DOB

FROM [MKE01-Demo-01].SAHPharm.dbo.testact

WHERE [MKE01-Demo-01].SAHPharm.dbo.testact.account = t.Account_Number

End

Any ideas?

Thanks!

Rudy

You should be able to combine the two queries into one.

Code Snippet

UPDATE [Pharm Test Local].dbo.Active_Orders
SET
l.Account = t.Account_Number,
l.Fname = t.First_Name,
l.LName = t.Last_Name,
l.dob = t.DOB
FROM [Pharm Test Local].dbo.Active_Orders l
JOIN [MKE01-Demo-01].SAHPharm.dbo.testact t
ON l.Account_Number = t.Account
WHEREt.Account_Number = @.Acct

|||

Hi Arnie!

Thank you for the relpy. I tried your above solution, but it doesn't work. It seems I can't use JOIN on a SQL 2005 box. My Local server is SQL 2005, and SQL 2000 is on my target. So I'm trying to figure out the new OUTER APPLY that I discovered. I'm getting this error when I run the above statement in you post,

"

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "l.Account_Number" could not be bound."

I'll keep you updated in my findings, but if there is anybody who can enlighten me, I'm all ears!

Thanks!

Rudy

|||

It seems I can't use JOIN on a SQL 2005 box

Now that totally doesn't make sense. JOIN is so basic to SQL, it's like air, you can't exists without it. ALL versions of SQL Server support JOIN.

So I'm trying to figure out the new OUTER APPLY that I discovered

NOW that definitely will not work on a SQL 2000 server.

If you would carefully examine the statement I suggested, you would see that I made a transposition error in this line:

ON l.Account_Number = t.Account

I believe it should be: ON l.Account = t.Account_Number instead.

|||

Hi Arnie!

I did catch that. I have another guy here baffled as well. He told me that the JOIN will work, I'm just not sure why I'm getting that error. I'll keep plugging away.

Thanks

Rudy

|||

Hello Arnie!

After a little digging, I figured it out. Heres what I got!

Thanks again for all your help on this!

Rudy

UPDATE [Pharm Test Local].dbo.Active_Orders

SET

Account_Number = t.Account,

First_Name = t.Fname,

Last_Name = t.Lname,

dob = t.DOB

FROM [MKE01-DEMO-01].SAHPharm.dbo.testact t

WHERE Account_Number = t.Account