SQL,tables in a DB: access

Showing posts with label access. Show all posts

Sunday, March 25, 2012

(newbie ques) Access 2000 to SQL Server migrate sync

I am a db newbie and am looking for advice on how to migrate a (fairly large - 900mb) Access b/e to SQL server 2000. The f/e is Access. I've read most of the upsizing info, but need help in understanding how to merge in new records/data which occur while the upsizing is done. (i estimate it will take several days to sort out the upsizing mess). This is a very active b/e, and not a lot of downtime is avail.

thanks for any expert advice

crunchy f.alter the access tables to add an extra column, let's call it Converted, with Required=yes, Allow zero length=No, Default='n'

run the extract to begin the upsizing process, then set Converted='y' for all records before putting the access database back online

all your existing apps can then continue as before

when the first stage of upsizing is done, do another extract, pulling only records where Converted='n' and set these to 'y' before putting the database back online

lather, rinse, repeat

rudy
http://rudy.ca/|||I am also interested in this. But just for my curiosity, can you let me know the significance of this new column etc.,
By the way, my knowledge of access and SQL and Vb is limited as I have learnt through forums like this
Thanks for your time and patience|||the significance of this new column? it marks which rows have been upsized

when you first add the column to the table, all rows have N

then you upsize all rows, and mark them Y (converted)

then the access application adds some more rows into the table, and they all get N by default

now you want to upsize only the recent ones, so you simply select them with converted='N' so that you don't pick up any rows that have already been upsized

rudy

(MS Access) FIRST command equivalent in SQL Server

Is there an equivalent to the command FIRST from MS Access for SQL Server?
The command 'Top 1' doesn't seem to be the same...
:confused:What functionality do you need ?

(local) database wont start?

I dont know how this happened but my (local) SQL Server wont start. I cant access any of the databases. I just get this error:

'A connection could not be established to (local).

Reason: SQL Server does not exist or access denied.
ConnectionOpen (Connect())

Please verify SQL Server is running and check your SQL Server registration properties (by right-clicking on the (local) node) and try again.'

If I go to check the properties I get the same error. Im completely locked out. I looked at the folder and the database files are there, but I cant get to them through Enterprise Manager.

I have no idea how this problem could have happened. It just seemed to have stopped working. All my other databases can be reached (they are external). Could this be a virus or something? Can someone help me out on this one? I dont have a clue what the problem is, much less how to fix it.

Thanks.Are you CERTAIN that the MSSQLServer service is running? A virus is unlikely the cause.

Tuesday, March 20, 2012

"VFPOLEDB" for linked server reported an error. Access denied

Hi Cindy,
After hours (days?) researching this, your name keeps popping up, you must be the GOTO person for VFP. So:

We have a server that runs a VFP application and stores .dbf free tables in a directory shared as F:\apps\tele.

I am working on an XPPro box running SQL Server Express 2005 using Management Studio Express.

I am logged on as a user with administrator rights for the domain

We would like to be able to query this data and move it to our SQL tables as needed.

Here is what I have:

EXEC sp_addlinkedserver @.server = 'tele',
@.srvproduct = 'VFP',
@.provider = 'VFPOLEDB',
@.datasrc = 'F:\apps\tele',
@.provstr = 'VFPOLEDB.1'
GO

SELECT * FROM tele...PROSPECT_DIVU

Here is what I get:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "VFPOLEDB" for linked server "tele" reported an error. Access denied.
Msg 7301, Level 16, State 2, Line 1
Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB provider "VFPOLEDB" for linked server "tele".

I have tried:

exec sp_addlinkedsrvlogin
@.rmtsrvname = 'tele',
@.useself = false,
@.locallogin = NULL,
@.rmtuser = 'admin',
@.rmtpassword = NULL
go
Same result.

I have also tried:

EXEC sp_addlinkedserver
'tele2',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'F:\apps\tele',
NULL,
'dBase IV'
GO

When I run:

select * from tele2...prospect_divu

I get:

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "tele2" returned message "The Microsoft Jet database engine could not find the object 'prospect_divu'. Make sure the object exists and that you spell its name and the path name correctly.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_COLUMNS" for OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "tele2". The provider supports the interface, but returns a failure code when it is used.

What would you suggest?

FOR SQL MSExpress

In object explored drill down

Server Objects

Linked Servers

Providers

VFPOLEDB

Right Click Select Properties

Check "Allow Inprocess"

Click OK

DONE

|||

Hi JC,

I've created a new thread with your post. Here's some content I've posted several times in the NNTP groups:

"What permissions does the SQL Server local system account have? I'm ok on my laptop with the SQL Server started with "local system account" and my Windows login being MachineName\Cindy. When I tried against my SQL Express instance which was started using the "NT AUTHORITY\NetworkService" I had the same errors you report. "

Basically, the account the SQL Server runs under needs to have permissions to the directory where the DBFs are stored.

Your error with Jet may stem from the fact that the structure of Fox DBFs has changed over time and the changes aren't compatible with the dBase IV format that Jet recognizes.

|||In addition to Cindys recommondation I would add that you try to use a common query to identity if the connection is working to the dbf file. I am not familiar with the dbf file schema, but there could be an object (table etc.) which always exists in the schema which you could query to eliminate other problems (like in SQL Server the sysobjects object). I also guess that the problem is based upon file access security. Depengin on the settings of the linked server you connect with the "users context" or with SQL server service account security (which can be in some case not priviledged anough to access / modify the datafiles.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

I am still fighting with this as well. But one thing I have noticed "Select *" it does not like, "Select col1, col1, col3" it is ok with and the error I was get with "*" was the access denyed. You will probably notice that in addition to the Messages tab with the errors that you also have a Results tab with only the column headings.

Other then that I am still having issues connecting to a UNC as the data source. Like \\dispatch\dispatchtables\

Hope it helps you some.

Paul

"The report server is no responding" error!

Hello!

When I try to access http://localhost/reportserver, I get

Could not load file or assembly 'ReportingServicesWebServer, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The parameter is incorrect.

Can someone help me? Here is the log!

Thank you!

Microsoft.ReportingServices.UI.Global+RSWebServiceWrapper+CantCommunicateWithReportServerException: The report server is not responding. Verify that the report server is running and can be accessed from this computer.

.....

Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'.
The request failed with the error message:
--
<html>
<head>
<title>Configuration Error</title>
<style>
body {font-family:"Verdana";font-weight:normal;font-size: .7em;color:black;}
p {font-family:"Verdana";font-weight:normal;color:black;margin-top: -5px}
b {font-family:"Verdana";font-weight:bold;color:black;margin-top: -5px}
H1 { font-family:"Verdana";font-weight:normal;font-size:18pt;color:red }
H2 { font-family:"Verdana";font-weight:normal;font-size:14pt;color:maroon }
pre {font-family:"Lucida Console";font-size: .9em}
.marker {font-weight: bold; color: black;text-decoration: none;}
.version {color: gray;}
.error {margin-bottom: 10px;}
.expandable { text-decoration:underline; font-weight:bold; color:navy; cursor:hand; }
</style>
</head>

<span><H1>Server Error in '/ReportServer' Application.<hr width=100% size=1 color=silver></H1>

<h2> <i>Configuration Error</i> </h2></span>

<b> Description: </b>An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately.
<br><br>

<b> Parser Error Message: </b>Could not load file or assembly 'ReportingServicesWebServer, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The parameter is incorrect. (Exception from HRESULT: 0x80070057 (E_INVALIDARG))<br><br>

<b>Source Error:</b> <br><br>

Line 27: <assemblies>
Line 28: <clear />
<font color=red>Line 29: <add assembly="ReportingServicesWebServer" />
</font>Line 30: </assemblies>
Line 31: </compilation></pre></code>

</td>
</tr>
</table>

<br>

<b> Source File: </b> C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportServer\web.config<b> Line: </b> 29
<br><br>

<b>Version Information:</b> Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42

</font>

</body>
</html>
<!--
[ArgumentException]: Element does not specify a class.
at System.Security.Policy.PolicyLevel.GenericResolve(Evidence evidence, Boolean& allConst)
at System.Security.Policy.PolicyLevel.Resolve(Evidence evidence, Int32 count, Char[] serializedEvidence)
at System.Security.PolicyManager.CodeGroupResolve(Evidence evidence, Boolean systemPolicy)
at System.Security.PolicyManager.ResolveHelper(Evidence evidence)
at System.Security.HostSecurityManager.ResolvePolicy(Evidence evidence)
at System.Security.PolicyManager.Resolve(Evidence evidence)
at System.Security.SecurityManager.ResolvePolicy(Evidence evidence, PermissionSet reqdPset, PermissionSet optPset, PermissionSet denyPset, PermissionSet& denied, Boolean checkExecutionPermission)
at System.Security.SecurityManager.ResolvePolicy(Evidence evidence, PermissionSet reqdPset, PermissionSet optPset, PermissionSet denyPset, PermissionSet& denied, Int32& securitySpecialFlags, Boolean checkExecutionPermission)
[FileLoadException]: Could not load file or assembly 'ReportingServicesWebServer, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The parameter is incorrect. (Exception from HRESULT: 0x80070057 (E_INVALIDARG))
at System.Reflection.Assembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.Load(String assemblyString)
at System.Web.Configuration.CompilationSection.LoadAssemblyHelper(String assemblyName, Boolean starDirective)
[ConfigurationErrorsException]: Could not load file or assembly 'ReportingServicesWebServer, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The parameter is incorrect. (Exception from HRESULT: 0x80070057 (E_INVALIDARG)) (C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportServer\web.config line 29)
at System.Web.Configuration.CompilationSection.LoadAssemblyHelper(String assemblyName, Boolean starDirective)
at System.Web.Configuration.CompilationSection.LoadAssembly(AssemblyInfo ai)
at System.Web.Compilation.BuildManager.GetReferencedAssemblies(CompilationSection compConfig)
at System.Web.Compilation.BuildProvidersCompiler..ctor(VirtualPath configPath, Boolean supportLocalization, String outputAssemblyName)
at System.Web.Compilation.ApplicationBuildProvider.GetGlobalAsaxBuildResult(Boolean isPrecompiledApp)
at System.Web.Compilation.BuildManager.CompileGlobalAsax()
at System.Web.Compilation.BuildManager.EnsureTopLevelFilesCompiled()
[HttpException]: Could not load file or assembly 'ReportingServicesWebServer, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The parameter is incorrect. (Exception from HRESULT: 0x80070057 (E_INVALIDARG)) (C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportServer\web.config line 29)
at System.Web.Compilation.BuildManager.ReportTopLevelCompilationException()
at System.Web.Compilation.BuildManager.EnsureTopLevelFilesCompiled()
at System.Web.Hosting.HostingEnvironment.Initialize(ApplicationManager appManager, IApplicationHost appHost, IConfigMapPathFactory configMapPathFactory, HostingEnvironmentParameters hostingParameters)
[HttpException]: Could not load file or assembly 'ReportingServicesWebServer, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The parameter is incorrect. (Exception from HRESULT: 0x80070057 (E_INVALIDARG)) (C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportServer\web.config line 29)
at System.Web.HttpRuntime.FirstRequestInit(HttpContext context)
at System.Web.HttpRuntime.EnsureFirstRequestInit(HttpContext context)
at System.Web.HttpRuntime.ProcessRequestInternal(HttpWorkerRequest wr)

I had this same issue. Check the path that the root of the website is using. It should be some location that is not the path of the virtual directory.|||

I have the same issue, directly after installing Reportserver
Will you please you explain "Check the path that the root of the website is using. "

"The report server is no responding" error!

Hello!

When I try to access http://localhost/reportserver, I get

Could not load file or assembly 'ReportingServicesWebServer, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The parameter is incorrect.

Can someone help me? Here is the log!

Thank you!

.....

<span><H1>Server Error in '/ReportServer' Application.<hr width=100% size=1 color=silver></H1>

<h2> <i>Configuration Error</i> </h2></span>

<b>Source Error:</b> <br><br>

Line 27: <assemblies>
Line 28: <clear />
<font color=red>Line 29: <add assembly="ReportingServicesWebServer" />
</font>Line 30: </assemblies>
Line 31: </compilation></pre></code>

</td>
</tr>
</table>

<br>

<b> Source File: </b> C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportServer\web.config<b> Line: </b> 29
<br><br>

<b>Version Information:</b> Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42

</font>

I had this same issue. Check the path that the root of the website is using. It should be some location that is not the path of the virtual directory.|||

I have the same issue, directly after installing Reportserver
Will you please you explain "Check the path that the root of the website is using. "

Monday, March 19, 2012

"The report server is no responding" error!

Hello!

When I try to access http://localhost/reportserver, I get

Could not load file or assembly 'ReportingServicesWebServer, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The parameter is incorrect.

Can someone help me? Here is the log!

Thank you!

.....

<span><H1>Server Error in '/ReportServer' Application.<hr width=100% size=1 color=silver></H1>

<h2> <i>Configuration Error</i> </h2></span>

<b>Source Error:</b> <br><br>

Line 27: <assemblies>
Line 28: <clear />
<font color=red>Line 29: <add assembly="ReportingServicesWebServer" />
</font>Line 30: </assemblies>
Line 31: </compilation></pre></code>

</td>
</tr>
</table>

<br>

<b> Source File: </b> C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportServer\web.config<b> Line: </b> 29
<br><br>

<b>Version Information:</b> Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42

</font>

I had this same issue. Check the path that the root of the website is using. It should be some location that is not the path of the virtual directory.|||

I have the same issue, directly after installing Reportserver
Will you please you explain "Check the path that the root of the website is using. "

Friday, March 16, 2012

"Remote access to report data sources and/or the report server database" is not suppor

I'm using SQL Express with Advance Services & I get this error when I try to deploy my reports. Why do I get this error

An error has occurred during report processing. (rsProcessingAborted)

Cannot create a connection to data source 'EDPSYS'. (rsErrorOpeningConnection)

The feature: "Remote access to report data sources and/or the report server database" is not supported in this edition of Reporting Services. (rsOperationNotSupported)

Justin Song

SQL Express does not allow access to data sources not on the local machine. You will need to point to local data in your report.|||

I'm new user of Reporting Services. I'm trying use Samples (AdventureWorks) and can't use it because this error. How do I point to local data in this samples? I create a Data Source named AdventureWorks with this string:

data source="(local)";persist security info=False;initial catalog=AdventureWorks

but is not working...

|||

I've seen this problem before. The way I solved it previously was to update the connectionstring from an old-style ADO format to the new SQL 2005 format.

Old:

data source=(local);persist security info=False;initial catalog=AdventureWorks

New:

Server=(local); Database=AdventureWorks; Trusted_Connection=yes;

Hope this helps. Mark

"Remote access to report data sources and/or the report server database" is not suppor

I'm using SQL Express with Advance Services & I get this error when I try to deploy my reports. Why do I get this error

An error has occurred during report processing. (rsProcessingAborted)

Cannot create a connection to data source 'EDPSYS'. (rsErrorOpeningConnection)

The feature: "Remote access to report data sources and/or the report server database" is not supported in this edition of Reporting Services. (rsOperationNotSupported)

Justin Song

SQL Express does not allow access to data sources not on the local machine. You will need to point to local data in your report.|||

data source="(local)";persist security info=False;initial catalog=AdventureWorks

but is not working...

|||

I've seen this problem before. The way I solved it previously was to update the connectionstring from an old-style ADO format to the new SQL 2005 format.

Old:

data source=(local);persist security info=False;initial catalog=AdventureWorks

New:

Server=(local); Database=AdventureWorks; Trusted_Connection=yes;

Hope this helps. Mark

"Remote access to report data sources and/or the report server database" is not su

I'm using SQL Express with Advance Services & I get this error when I try to deploy my reports. Why do I get this error

An error has occurred during report processing. (rsProcessingAborted)

Cannot create a connection to data source 'EDPSYS'. (rsErrorOpeningConnection)

The feature: "Remote access to report data sources and/or the report server database" is not supported in this edition of Reporting Services. (rsOperationNotSupported)

Justin Song

SQL Express does not allow access to data sources not on the local machine. You will need to point to local data in your report.|||

data source="(local)";persist security info=False;initial catalog=AdventureWorks

but is not working...

|||

I've seen this problem before. The way I solved it previously was to update the connectionstring from an old-style ADO format to the new SQL 2005 format.

Old:

data source=(local);persist security info=False;initial catalog=AdventureWorks

New:

Server=(local); Database=AdventureWorks; Trusted_Connection=yes;

Hope this helps. Mark

Thursday, March 8, 2012

"Key cannot be null. Parameter name: key"

We are using forms authentication with SSRS2005 and we are able to access Report Manager (http://servername/reports) and Reportserver (http://servername/reportserver). The problem we are having is every time we add a user to the Content Manager or the Publisher role. The user is able to login and reach the Report Manager but no folder is showing up and also we have the following error message : "Key cannot be null. Parameter name: key".

We removed users from the Content Manager and Publisher Roles and they are able to reach report manager and see the folders. This only happens with report manager. everything works fine with reportserver(http://servername/reportserver).

Any idea why we're getting an error message when trying to assign the Content Manager and publisher Roles to a user.

here is the error log from ReportServerWebApp :

aspnet_wp!ui!1!3/27/2007-16:38:49:: e ERROR: Key cannot be null.
Parameter name: key
aspnet_wp!ui!1!3/27/2007-16:38:49:: e ERROR: HTTP status code --> 500
-Details--
System.ArgumentNullException: Key cannot be null.

Parameter name: key

at System.Collections.Hashtable.Insert(Object key, Object nvalue, Boolean add)

at System.Collections.Hashtable.Add(Object key, Object value)

at Microsoft.ReportingServices.UI.BasePermissions.ReadPermissions(String[] permissions)

at Microsoft.ReportingServices.UI.Permissions.GetPermissions()

at Microsoft.ReportingServices.UI.Permissions.CurrentUser(String itemPath)

at Microsoft.ReportingServices.UI.FolderPage.Page_Init(Object sender, EventArgs e)

at System.EventHandler.Invoke(Object sender, EventArgs e)

at System.Web.UI.Control.OnInit(EventArgs e)

at System.Web.UI.Page.OnInit(EventArgs e)

at System.Web.UI.Control.InitRecursive(Control namingContainer)

at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
aspnet_wp!ui!1!3/27/2007-16:38:49:: e ERROR: Exception in ShowErrorPage: System.Threading.ThreadAbortException: Thread was being aborted.
at System.Threading.Thread.AbortInternal()
at System.Threading.Thread.Abort(Object stateInfo)
at System.Web.HttpResponse.End()
at System.Web.HttpServerUtility.Transfer(String path, Boolean preserveForm)
at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg) at at System.Threading.Thread.AbortInternal()
at System.Threading.Thread.Abort(Object stateInfo)
at System.Web.HttpResponse.End()
at System.Web.HttpServerUtility.Transfer(String path, Boolean preserveForm)
at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg)

Thanks!

chi

Check the following post out, it may help

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=327150&SiteID=1

|||

i checked the link, hmmm... did not work. any other suggestions.

chi

"Key cannot be null. Parameter name: key"

Any idea why we're getting an error message when trying to assign the Content Manager and publisher Roles to a user.

here is the error log from ReportServerWebApp :

Parameter name: key

at System.Collections.Hashtable.Insert(Object key, Object nvalue, Boolean add)

at System.Collections.Hashtable.Add(Object key, Object value)

at Microsoft.ReportingServices.UI.BasePermissions.ReadPermissions(String[] permissions)

at Microsoft.ReportingServices.UI.Permissions.GetPermissions()

at Microsoft.ReportingServices.UI.Permissions.CurrentUser(String itemPath)

at Microsoft.ReportingServices.UI.FolderPage.Page_Init(Object sender, EventArgs e)

at System.EventHandler.Invoke(Object sender, EventArgs e)

at System.Web.UI.Control.OnInit(EventArgs e)

at System.Web.UI.Page.OnInit(EventArgs e)

at System.Web.UI.Control.InitRecursive(Control namingContainer)

Thanks!

chi

Check the following post out, it may help

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=327150&SiteID=1

|||

i checked the link, hmmm... did not work. any other suggestions.

chi

Tuesday, March 6, 2012

"dynamical" view

Hi,

I'm currently migrating an MS Access database to SQL 2000 Standard, and i have a problem with queries migration.

In acccess, a screen allows me to view some informations depending on Criteria, and results are returned with a query (we'll call it Q1).

Then, when correct data are returned by the query Q1, some other queries compute sums, average, group by orders, based on Q1's results.

Now my question : as in sql server, a view cannot accept parameters, the solution i adopted is :

- create a generic Q1 view, not filtered.

- then, all the subqueries based on this view are Functions or Stored procedures, which accepts parameters and do the filter on the main view (Q1).

The problem is that writing such functions or stored procedures is quite long, and interface code has to be deeply modified to use these parameters.

First, I had the idea of creating a temporary table, in which i would have inserted the good data, an the other queries would have used it. Unfortunately, views can't use temporary tables.

Any good idea is expected. Thanks in advance,

So, i found a first way to do this, maybe not the best, but it seems to work correctly.

I generate the script to dynamically generate the view with the correct "where" statements.

The view's name is MyView_ + Host_Id().

It should work since we don't use a citrix server for client hosting...

Sunday, February 19, 2012

"...report server cannot access internal information..."

I've been unsuccessful getting Reporting Services 2005 to work after reinstalling SQL 2005. The RS service cannot connect to its database, as reported in the Event Log. If I visit http://localhost/Reports, I get the error "Key not valid for use in specified state. (Exception from HRESULT: 0x8009000B) (rsRPCError)"

I've used the RS Config Mgr to create a fresh database for it named SSRS (and SSRSTembDB), and it is set to use Service Credentials in the db connection. The RS service is running using the same admin account as MSSQL, .\SQLExec. I have confirmed that .\SQLExec is in the RSExecRole role in the SSRS database.

When I created the SSRS database, the Config Mgr had a green check for every step except the last. For "Setting Connection Info for the Reporting Server" there was a yellow '!', with the explanation, "Although saving the database connection succeeded, the report server cannot access internal information about this deployment to determine if the current configuration is valid for this edition. ..."

I examined rsreportserver.config to look for any issues. The <dsn> element has an encrypted string, the <ConnectionType> element says "Default", and the logon elements are empty. I could not find the db names, SSRS and SSRSTempDB, anywhere in the file. Where are these kept? I don't know what else to look for.

Also, in RS Config Mgr, the 'Encryption Keys' page has a blue '!', and only the 'Restore' and 'Delete' buttons are enabled. The 'Initialization' page has a red 'X'. The local RS is not listed (the grid is empty), and clicking the 'Initialize' button does nothing.

How can I diagnose and fix this installation?
Check and see if you had changed your password on your Administrator account for your server after installing Reporting Services. This will cause the problem if you have.|||I posted the solution in another thread. See:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1258874&SiteID=1

"...report server cannot access internal information..."

(rsRPCError)"

How can I diagnose and fix this installation?Check and see if you had changed your password on your Administrator account for your server after installing Reporting Services. This will cause the problem if you have.|||I posted the solution in another thread. See:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1258874&SiteID=1

"...report server cannot access internal information..."

<server> does not exist or access denied

The message "SQL Server does not exist or access denied" comes up when I try
editing a data-driven subscription.
The subscription worked fine until I had a problem wherein I lost access to
the report server with the message "Key not valid for use in specified
state". Access was restored by deleting keys (rskeymgmt -d), then running
RSactivate -c so that the path points to the config file in c:\program
files\mssql\reporting services. IIS was then stopped and restarted
(iisreset).
This solved the original problem, but I now have a new problem. As this new
problem did not exist before, I think it may be related to the first problem.
Please can you help?
ViolaI've sorted it -- it was a problem with the report -- nothing to do with the
previous problem.
Viola
"Viola" wrote:
> The message "SQL Server does not exist or access denied" comes up when I try
> editing a data-driven subscription.
> The subscription worked fine until I had a problem wherein I lost access to
> the report server with the message "Key not valid for use in specified
> state". Access was restored by deleting keys (rskeymgmt -d), then running
> RSactivate -c so that the path points to the config file in c:\program
> files\mssql\reporting services. IIS was then stopped and restarted
> (iisreset).
> This solved the original problem, but I now have a new problem. As this new
> problem did not exist before, I think it may be related to the first problem.
> Please can you help?
> Viola
>

<ReportServerExternalUrl> with SSRS 2005

When we were on SSRS 2000, I had to add a line in my RSWebApplication.config
file in order for people outside our firewall to access our internal report
server. It looked like
<ReportServerExternalUrl>http://xxx.mydomain.com</ReportServerExternalUrl>
We just upgraded to SSRS 2005 and I wonder if I have to add this line back
into the config file to get/keep this capability.
Thanks.I tried accessing my report server after I got home and had no problems so
the answer must be that I don't need to insert this extra line manually into
the config file. That's nice since it seemed an unnecessary step in SSRS
2000.
Jon

Thursday, February 16, 2012

>> Help with Stored Procedure

Hi, I'm so new to programming in MS SQL that the paint is still wet behind m
y
ears...
Using sql 2000 and vb.net (I normally develop in MS Access)
I have a search form set up so that users can enter any combination of the
following search criteria:
User types all or part business name,
User types all or part account name,
User selects from a ComboBox a business type
User types all or part contact name,
User selects from a ComboBox a contact type
I want to have a stored procedure that can handle any combinations of data
entry. The following is my guess for structure and it does not compile...
CREATE PROCEDURE GetSearchResult
@.Account nvarchar(10),
@.Business nvarchar(100),
@.BusinessTypeID Int,
@.ContactID Int,
@.Contact nvarchar(100),
@.ContactTypeID Int
DECLARE @.SearchOptions Int
DECLARE @.ErrorStatus Int OUTPUT
AS
IF LEN(@.Business)>0
SET @.SearchOptions = 1
IF LEN(@.Account) > 0
SET @.SearchOptions = @.SearchOptions + 2
IF @.BusinessTypeID > 0
SET @.SearchOptions = @.SearchOptions + 4
IF LEN(@.Contact) > 0
SET @.SearchOptions = 10
IF @.ContactTypeID > 0
SET @.SearchOptions = @.SearchOptions + 11
CASE @.SearchOptions
WHEN 1 THEN
-- Only search for Business name
BEGIN
SELECT * FROM vwBusinessSearchResult
WHERE bBusiness LIKE '%' + @.Business + '%'
END
WHEN 2 THEN
-- Only search for Account name
BEGIN
SELECT * FROM vwBusinessSearchResult
WHERE bAccount LIKE '%' + @.Account + '%'
END
WHEN 3 THEN
-- Search for Business name and Account
BEGIN
SELECT * FROM vwBusinessSearchResult
WHERE bBusiness LIKE '%' + @.Business + '%'
AND bAccount LIKE '%' + @.Account + '%'
END
WHEN 4 THEN
-- Only search for Business type id
BEGIN
SELECT * FROM vwBusinessSearchResult
WHERE bID Exists In(SELECT bbtBusinessID
FROM tblBusinessBusinessType
WHERE bbtBusinessTypeID = @.BusinessTypeID)
END
WHEN 5 THEN
-- Search for Business name and Business type id
BEGIN
SELECT * FROM vwBusinessSearchResult
WHERE bBusiness LIKE '%' + @.Business + '%'
AND bID Exists In(SELECT bbtBusinessID
FROM tblBusinessBusinessType
WHERE bbtBusinessTypeID = @.BusinessTypeID)
END
WHEN 6 THEN
-- Search for Account and Business type id
BEGIN
SELECT * FROM vwBusinessSearchResult
WHERE bAccount LIKE '%' + @.Account + '%'
AND bID Exists In(SELECT bbtBusinessID
FROM tblBusinessBusinessType
WHERE bbtBusinessTypeID = @.BusinessTypeID)
END
WHEN 7 THEN
-- Search for Business name, Account and Business type id
BEGIN
SELECT * FROM vwBusinessSearchResult
WHERE bBusiness LIKE '%' + @.Business + '%'
AND bAccount LIKE '%' + @.Account + '%'
AND bID Exists In(SELECT bbtBusinessID
FROM tblBusinessBusinessType
WHERE bbtBusinessTypeID = @.BusinessTypeID)
END
WHEN 10 THEN
-- Only Search for Contact name
BEGIN
SELECT * FROM vwBusinessContactSearchResult
WHERE cFullName LIKE '%' + @.Contact + '%'
END
WHEN 11 THEN
-- Only search for Contact type id
BEGIN
SELECT * FROM vwBusinessContactSearchResult
WHERE cID Exists In(SELECT cctContactID
FROM tblContactContactType
WHERE cctContactTypeID = @.ContactTypeID)
END
WHEN 21 THEN
-- Search for Contact name name Contact type id
BEGIN
SELECT * FROM vwBusinessContactSearchResult
WHERE cFullName LIKE '%' + @.Contact + '%'
AND cID Exists In(SELECT cctContactID
FROM tblContactContactType
WHERE cctContactTypeID = @.ContactTypeID)
END
END
GO
GRANT EXECUTE ON GetSearchResult TO PUBLIC
GO
Can you please give me advice regarding my approach to facilitating a search
form and corrections for more stored procedure
Many thanks
Jonathan ParminterIf the user doen't select an entry what gets passed in?
if null then
SELECT * FROM vwBusinessSearchResult
where Account like '%' + coalesce(@.Account, '') + '%'
and Business like '%' + coalesce(@.Business, '') + '%'
and Contact like '%' + coalesce(@.Contact, '') + '%'
and BusinessTypeID = coalesce(@.BusinessTypeID, BusinessTypeID)
and ContactID = coalesce(@.ContactID, ContactID)
and ContactTypeID = coalesce(@.ContactTypeID, ContactTypeID)
if empty string and 0 from the IDs then
SELECT * FROM vwBusinessSearchResult
where Account like '%' + @.Account + '%'
and Business like '%' + @.Business + '%'
and Contact like '%' + @.Contact + '%'
and (BusinessTypeID = @.BusinessTypeID or @.BusinessTypeID = 0)
and (ContactID = @.ContactID or @.ContactID = 0)
and (ContactTypeID = @.ContactTypeID or @.ContactTypeID = 0)|||Nigel, excellant
--
Many thanks
Jonathan Parminter
"Nigel Rivett" wrote:

> If the user doen't select an entry what gets passed in?
> if null then
> SELECT * FROM vwBusinessSearchResult
> where Account like '%' + coalesce(@.Account, '') + '%'
> and Business like '%' + coalesce(@.Business, '') + '%'
> and Contact like '%' + coalesce(@.Contact, '') + '%'
> and BusinessTypeID = coalesce(@.BusinessTypeID, BusinessTypeID)
> and ContactID = coalesce(@.ContactID, ContactID)
> and ContactTypeID = coalesce(@.ContactTypeID, ContactTypeID)
> if empty string and 0 from the IDs then
> SELECT * FROM vwBusinessSearchResult
> where Account like '%' + @.Account + '%'
> and Business like '%' + @.Business + '%'
> and Contact like '%' + @.Contact + '%'
> and (BusinessTypeID = @.BusinessTypeID or @.BusinessTypeID = 0)
> and (ContactID = @.ContactID or @.ContactID = 0)
> and (ContactTypeID = @.ContactTypeID or @.ContactTypeID = 0)
>|||Nigel,
Just curious as to the use of coalesce on the @.account, @.business, and
@.contact (given it wasn't the first thing that sprang to mind for me.
Is there any difference performance wise between using coalesce() in
this fashion vs isnull() ?
Glenn|||Jonathan (Jonathan@.discussions.microsoft.com) writes:
> IF @.ContactTypeID > 0
> SET @.SearchOptions = @.SearchOptions + 11
> CASE @.SearchOptions
> WHEN 1 THEN
There is no CASE statement in Transact-SQL. There is a CASE *expression*
which can be used in WHERE clauses, SELECT lists or anywhere else you
can use an expression.
To achieve the same logic, you would have to use bunch of IF ELSE.
For a longer discussion on this particular problem - dynamic search
conditions - I have an article on my web site that discusses a number
of alternatives: http://www.sommarskog.se/dyn-search.html.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>> Is there any difference performance wise between using coalesce() in
this fashion vs isnull() ? <<
COALESCE () is Standard SQL and takes a list of parameters. It is a
member fo the CASE expression family.
COALESCE correctly promotes its arguments to the highest data type in
the expression:
13 / COALESCE(CAST(NULL AS INTEGER), 2.00) = 6.5
The proprietary ISNULL() uses the first data type and gets things wrong
13 / ISNULL(CAST(NULL AS INTEGER), 2.00) = 6
You would need to write:
13 / ISNULL(CAST(NULL AS DECIMAL(4,2)), 2.00)

>> Can Google web search index the data in my database?

I have content about projects that I store in a MS SQL database.
People access this information through pages which are dynamically
created in PHP. Since my content is stored in the database, how can I
get google web search (at google.com) to included that content when it
indexes my site? Is it a possibility? Thank you so much!I don't think this is the right group for this question (but I'm not
sure which group is right). Here are some links that might help
(especially the first one):
https://www.google.com/webmasters/s...ocs/en/faq.html
http://desktop.google.com/developerguide.html
http://www.google.com/enterprise/mini/index.html

Razvan|||gene.ellis@.gmail.com wrote:
> Since my content is stored in the database, how can I
> get google web search (at google.com) to included that content when it
> indexes my site? Is it a possibility? Thank you so much!

Google will NOT index your database. However, Google will follow all
the links on your website. If your website is designed so that the
dynamic content is only accessible though, for example form fields,
then google will not be able to find it. On the other hand, any
dynamic content that is accessible through a simple hypertext link will
be found and indexed by google.

> I have content about projects that I store in a MS SQL database.

As an example, let's say that you have a page named projectinfo.php
that accepts as input a variable named ProjectID and looks up and
displays info about that project. On some other page, a user selects
the project that they want to view. The user is then directed to
projectinfo.php. If that other page consists of a form with a
drop-down box, so that the user selects a project from the drop down
and then hit's a submit button, then google probably will not be able
to follow it.

On the other hand, if that other page contains anchors with hrefs like:
projectinfo.php?projectID=1
projectinfo.php?projectID=2
projectinfo.php?projectID=3

Then google absolutely will be able to follow those links and index the
pages.

Hope that helps

Sunday, March 25, 2012

(newbie ques) Access 2000 to SQL Server migrate sync

(MS Access) FIRST command equivalent in SQL Server

(local) database wont start?

Tuesday, March 20, 2012

"VFPOLEDB" for linked server reported an error. Access denied

"The report server is no responding" error!

"The report server is no responding" error!

Monday, March 19, 2012

"The report server is no responding" error!

Friday, March 16, 2012

"Remote access to report data sources and/or the report server database" is not suppor

"Remote access to report data sources and/or the report server database" is not suppor

"Remote access to report data sources and/or the report server database" is not su

Thursday, March 8, 2012

"Key cannot be null. Parameter name: key"

"Key cannot be null. Parameter name: key"

Tuesday, March 6, 2012

"dynamical" view

Sunday, February 19, 2012

"...report server cannot access internal information..."

"...report server cannot access internal information..."

"...report server cannot access internal information..."

<server> does not exist or access denied

<ReportServerExternalUrl> with SSRS 2005

Thursday, February 16, 2012

>> Help with Stored Procedure

>> Can Google web search index the data in my database?

SQL,tables in a DB

Blog Archive

About Me