Help! Transfer of database from SQL Express to SQL 2005 host environment

Help!

I have been testing my system on my development PC using SQL Express. Great! Now its all working and I have 4.5Mb of data, and about 50 tables plus queries.

HOW DO I GET THIS TO MY NEWLY SETUP HOST ENVIRONMENT!

Argh... There seems to be no way to export from SQL Express to SQL 2005. The host company cannot restore from backup (I think this is reasonable.)

What was microsofts plan here? What am I missing? I have searched the net, and I have found about 20 people asking the same question with no answer.

Best answer I found was on this forum where somebody said download SQL 2000 as a trial, then steal a tool called DTS which apparently does not expire. Not clear if DTS 2000 will work with 2005 express so I haven't tried that yet.

Somebody else said download SQL 2005 as a trial. I made the mistake of doing this. It doesn't load unless I unload my SQL Express. If I do that then how do I make development changes after the trial ends?

Somebody else says use SQL 2005 developer edition. Only 80 bucks. GREAT... I'll take it. So I search for how to get this wonder tool, and all the links end up with the generic 2005 system. No mention of how to obtain this "developer edition"

I really feel I must be missing something here.

HOW DID MICROSOFT FIGURE that people would deploy their systems?

What I am missing?

Mitch

Hello Mitch -

I'm not certain I understand your issue. If you need to copy an entire database from SQL Server 2005 Express or MSDE to SQL Server 2005, you have many options. The first is that you can back up the database in question and restore it to the 2K5 server. I have done this many times and it works fine. You can also "detach" the database, copy the MDF and LDF files to the 2K5 server and "attach" it there using stored procedures or graphical methods. You can also use a wizard within SQL Server 2005 to transfer the database. Another method is to use the bcp program to copy out the data and import it into a SQL2K5 database. All of these methods have been tested and work correctly.

From your other statements it may be that you're trying to move a SQL Server 2005 Express database to SQL Server 2000. This is also possible, as long as you haven't used any of the extended features in 2005 on the source database. Set the database compatibility level to 80, and then use the database transfer wizard to transfer the database to SQL Server 2000, or use the bcp method.

Buck

|||

Buck,

Thanks for the reply.

You are correct that I am trying to move from SQL Server 2005 Express to SQL Server 2005.

The reason the backup/restore approach doesn't work, is that the hosting company does not do restores for people. I can understand why. They can't spend 15 minutes everytime somebody wants to upload their SQL Server 2005 Express DB. And for whatever reason when I try to do the restore myself, I can't get it to access my backup file.

I do not understand the detach, reattach method, and doubt it would work from the host I am on.

The hosting company recommends using the SQL2K5 transfer wizard.

However as noted, I don't have SQL2K5.

I am currently in the process of installing the "trial version" of SQL2K5. To do this I first had to uninstall SQL 2005 Express. Now I am doing the SQL2K5 install, and it has taken over an hour and its not done yet.

I am hoping to figure out how to enable this $80.00 developers license, but while its mentioned here and there, there is no clear instructions on how exactly to buy such a thing.

By the way the reason that I don't want to go with the BCP program, is that I have about 50 tables and queries, and it seems that each table is a manual step.

Again thanks for the response, any advice would be appreciated.

Mitch

|||

That is a little clearer. So the problem isn't with SQL Server, it's with the hosting company that won't restore the DB. If you're able to get to the SQL Server instance on your hosting service, you can just ask them to place the file for you and you can restore it yourself. If they won't do that, I'd investigate another hosting service.

The detach and attach method will have those same file copy and placement issues. It's better to use the backup and restore method if you can.

As far as the developer's license, you're not able to put that into production. You're only allowed to use that on your own machine for development purposes. If you're talking about installing it on your system, It may take a while to install, since it contains the Visual Studio-type interface for the BI Development Studio.

Buck

|||

Mitch,

For deployment, take a look at "Distributing SQL Server Express Applications" in Books Online: http://msdn2.microsoft.com/en-us/library/ms165639(SQL.90).aspx

You don't have to uninstall SQL Server 2005 Express to install the Evaluation edition. SQL Server is a multi-instance product which means it can be installed side-by-side so long as the instance name is unique.

Cheers,
Dan

btw: SQL Server Express is SQL2K5.

Help! Top N in SQL Server?

Hi,

I'm working on a SQL Server project right now, and I'm not sure how to
approach one part. Basically I have a table full of orders for a
software program to process, then mark with the date/time to show it's
been finished.

What's in the Q at any time could be a few orders, or several hundred
thousand. So I don't want to return the whole query; only the first
chunk, then when the program is done with that it can move on and grab
more.

That means SELECT TOP N, except that N needs to be a variable. When
CPU and network traffic are free it should grab more rows, and when
demand is high, it should have a coffee break.

I've tried:

Create Procedure vwAutoQ
@.GrabRowCount Int = 100
As

Select Top @.GrabRowCount
[...]

From
[...]

Where
[...]

Order By
[...]

And I get the following error:

Server: Msg 170, Level 15, State 1, Procedure vwAutoQ, Line 5
Line 5: Incorrect syntax near '@.GrabRowCount'.

Is this possible, what I'm trying to do? Otherwise I'll need to drop
it down to ~15 and fire the proc a bunch of times...On 22 Nov 2004 18:17:28 -0800, Thug Passion wrote:

(snip)
>That means SELECT TOP N, except that N needs to be a variable. When
>CPU and network traffic are free it should grab more rows, and when
>demand is high, it should have a coffee break.

Hi Thug,

You can't use a variable on the TOP keyword. But there is a workaround:
use SET ROWCOUNT. This will take a variable.

SET ROWCOUNT @.GrabRowCount
SELECT ...
FROM ...
WHERE ...
ORDER BY ...
SET ROWCOUNT 0

(Don't forget to set rowcount back to 0 after the query, as this is a
sticky setting: the limited rowcount remains active until you reset it or
drop the connection)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Various paging techniques discussed here:
http://www.aspfaq.com/2120

--
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<RIydnb-xceGbSz7cRVn-hA@.giganews.com>...
> Various paging techniques discussed here:
> http://www.aspfaq.com/2120

An alternative is to use Dynamic SQL, that is, assign your SQL to an
nvarchar variable substituting in your number of rows and then use the
EXEC command to execute it

DECLARE @.sSQL NVARCHAR(500)

SELECT @.sSQL = 'SELECT TOP ' + CONVERT(NVARCHAR,@.iNoRows) + ' rest of
string ' ...

EXEC(@.sSQL)|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<RIydnb-xceGbSz7cRVn-hA@.giganews.com>...
> Various paging techniques discussed here:
> http://www.aspfaq.com/2120

An alternative is to use Dynamic SQL, that is, assign your SQL to an
nvarchar variable substituting in your number of rows and then use the
EXEC command to execute it

DECLARE @.sSQL NVARCHAR(500)

SELECT @.sSQL = 'SELECT TOP ' + CONVERT(NVARCHAR,@.iNoRows) + ' rest of
string ' ...

EXEC(@.sSQL)|||> Hi Thug,

Hi!

> You can't use a variable on the TOP keyword. But there is a workaround:
> use SET ROWCOUNT. This will take a variable.

Awesome! I love it! That gets me exactly what I need, and except for
those two lines it doesn't change my SQL at all. I had no idea I
could use a variable with that type of (non-relational) command -
thanks very much!!|||> DECLARE @.sSQL NVARCHAR(500)

Hi,

Thanks for the response! I try to avoid this approach whenever
possible, it's gotten me in trouble in the past. I had a search
function in an SP that built a dynamic SQL command to take advantage
of indexes on whatever fields were passed in ( instead of a bunch of
like '%' statements ).

I declared a varchar(2000) to hold my command, and if I passed in
enough parameters, it came up to about 2300. But that was the last
thing I ever thought of to check...

HELP! SUBREPORTS!!

Hi All, I really need some help with subreporting in SQLRS. I have wasted 2 full days trying to get a simple subreport working in Reporting Services and am on the brink of going back to Active Reports if RS continues to be so confusing!
I have a main report and for each line I need a subreport linked on an OrderID parameter. I have followed every single RS books online How-to (as vague as some of them are) and still no luck. This is what I have done
I have created main report and a subreport.
I have placed the subreport on the main report and in its properties have added a parameter â'OrderIdâ' linked to the OrderId field from the main report. No complaints from RS so far.
In the subreport, I click on the â'â?¦â' to edit the DataSet and add a parameter OrderId here whos value is Parameters!OrderId.Value
Surely now when I run the report, the main report will pass its OrderId through to the sub report and I will get a subreport for each line?
All I get is all my main reports lines and then one subreport listing everything in the table?
If anyone can, please could you explain how exactly the parameters are meant to be set up as the online documentation assumes one has been using RS for 50 years or more!
Many thanks in advance
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.This is a very rough guess, but it sounds like you need to do some sort of
grouping (on OrderID perhaps) on each main report line item...
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:uoqz3rnaEHA.2340@.TK2MSFTNGP09.phx.gbl...
> Hi All, I really need some help with subreporting in SQLRS. I have wasted
2 full days trying to get a simple subreport working in Reporting Services
and am on the brink of going back to Active Reports if RS continues to be so
confusing!
> I have a main report and for each line I need a subreport linked on an
OrderID parameter. I have followed every single RS books online How-to (as
vague as some of them are) and still no luck. This is what I have done
> I have created main report and a subreport.
> I have placed the subreport on the main report and in its properties have
added a parameter â?~OrderIdâ?T linked to the OrderId field from the main
report. No complaints from RS so far.
> In the subreport, I click on the â?oâ?¦â' to edit the DataSet and add a
parameter OrderId here whos value is Parameters!OrderId.Value
> Surely now when I run the report, the main report will pass its OrderId
through to the sub report and I will get a subreport for each line?
> All I get is all my main reports lines and then one subreport listing
everything in the table?
> If anyone can, please could you explain how exactly the parameters are
meant to be set up as the online documentation assumes one has been using RS
for 50 years or more!
> Many thanks in advance
>
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.|||Break this down. First, work on the two reports separately until you get
what you want. Create the subreport as a stand alone report. This report
should have a report parameter and a query parameter. You should be able to
fully test your subreport stand alone. Then put the subreport into the mail
report. Do a right mouse click onto the subreport and then you can set the
parameter to the value of the orderid field.
Bruce L-C
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:uoqz3rnaEHA.2340@.TK2MSFTNGP09.phx.gbl...
> Hi All, I really need some help with subreporting in SQLRS. I have wasted
2 full days trying to get a simple subreport working in Reporting Services
and am on the brink of going back to Active Reports if RS continues to be so
confusing!
> I have a main report and for each line I need a subreport linked on an
OrderID parameter. I have followed every single RS books online How-to (as
vague as some of them are) and still no luck. This is what I have done
> I have created main report and a subreport.
> I have placed the subreport on the main report and in its properties have
added a parameter â?~OrderIdâ?T linked to the OrderId field from the main
report. No complaints from RS so far.
> In the subreport, I click on the â?oâ?¦â' to edit the DataSet and add a
parameter OrderId here whos value is Parameters!OrderId.Value
> Surely now when I run the report, the main report will pass its OrderId
through to the sub report and I will get a subreport for each line?
> All I get is all my main reports lines and then one subreport listing
everything in the table?
> If anyone can, please could you explain how exactly the parameters are
meant to be set up as the online documentation assumes one has been using RS
for 50 years or more!
> Many thanks in advance
>
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.

HELP! SQL Server Agent not working.

I was wondering if someone would be so kind as to tell me if i'm
missing something or just plain dumb:
I am running a 2000 Advanced Server and SQL 2000 Enterprise SP3. I
changed the SQL SERVER SERVICE account to use a Local NT User account
with no enhanced privileges standard user and another account with the
same level of permissions for the SQL SERVER AGENT.
Both services start fine and I configured the SQL server to start using
the service account thru enterprise manager. As well as the
configuration for SQL SERVER AGENT was also done in Enterprise Manager.
The file system and Program files directories are configured with the
default ACL's. There is no GPO dictating who has the "log on as service
right" and both accounts (SERVER & AGENT) have the sysadmin role in
SQL.
HOWEVER: whenever I try to run a job thru SQL SERVER AGENT it does not
run and i get a message stating that the service is not running. This
message dissapears as soon as i make the accounts members of the local
administrators group.
Would there be any way to run these 2 services under the local USER
permissions and have the jobs execute sucessfully?
ANY HELP would be appreciated. I have been doing research for more than
5 days now and can't seem to find an answer.
THANks!
Miguel A. Escalante, MCSEIs the error actually:
Error 22022: SQLServerAgent is not currently running so it
cannot be notified of this action.
Some things to check - Are you running SQL Server in fiber
mode (lightweight pooling)? Have you ensured that SQL Agent
is truly running and not hung up in a starting status? Have
you checked your sqlagent.out file?
In addition to lightweight pooling being enabled, Agent can
also get hung up due to a bad mail profile.
-Sue
On 2 Nov 2005 20:13:15 -0800, miguel.a.escalante@.gmail.com
wrote:

>I was wondering if someone would be so kind as to tell me if i'm
>missing something or just plain dumb:
>I am running a 2000 Advanced Server and SQL 2000 Enterprise SP3. I
>changed the SQL SERVER SERVICE account to use a Local NT User account
>with no enhanced privileges standard user and another account with the
>same level of permissions for the SQL SERVER AGENT.
>Both services start fine and I configured the SQL server to start using
>the service account thru enterprise manager. As well as the
>configuration for SQL SERVER AGENT was also done in Enterprise Manager.
>
>The file system and Program files directories are configured with the
>default ACL's. There is no GPO dictating who has the "log on as service
>right" and both accounts (SERVER & AGENT) have the sysadmin role in
>SQL.
>HOWEVER: whenever I try to run a job thru SQL SERVER AGENT it does not
>run and i get a message stating that the service is not running. This
>message dissapears as soon as i make the accounts members of the local
>administrators group.
>Would there be any way to run these 2 services under the local USER
>permissions and have the jobs execute sucessfully?
>ANY HELP would be appreciated. I have been doing research for more than
>5 days now and can't seem to find an answer.
>THANks!
>Miguel A. Escalante, MCSEsql

HELP! SQL Gurus Needed!

I have a SQL database. I have a scenario I want to pose to anybody who's willing to give this a shot. I'm working with three tables.

Employees
=============
ID
First
Last
Office (same as [Offices.Name])

Offices
=============
ID
Name

PostcardTracking
=============
ID
Agent (same as [Employees.ID])
Office (same as [Offices.Name])
mListQty

I need to display this information (in a web report - don't include details about formatting or anything):

Office | #Employees/Office | Mailing | Total Pieces

This is what needs to happen... I need to display each office name once in the Office field of the web report. Along with each office I need to display the number of employees in the office (each is in the Employees table only one time), the % of Employees that show up in the PostcardTracking table per office, and the total pieces of mail (mListQty) sent from users in that office.

I need to build this information into rows (JOIN) so I can output it to a dataset and write it to screen.

This seems like a trivial task, but my mind has come to a halt and im just totally stumped... please help!!!:: the % of Employees that show up in the PostcardTracking table per office
can you xplain a little more of this..

also, you might want to change the 'Id' in each table to the appropriate Id
employees_Id, office_id, postcard_id to avoid confusion about the ids. also i think Id is key word.|||Ok, so I'm not a SQL Guru but luckily you don't need one.

For the number of employees per office:

select O.ID as OfficeId,
O.Name as Office,
count(*) as [Employees/Office]
from Offices O
join Employees E
on O.Name = E.Office
group by O.ID, O.Name

For the percentage of employees that's tracking postcards (whatever that means):

select O.ID as OfficeId,
O.Name as Office,
(select count (distinct Agent)
from PostcardTracking T
where T.Office = O.Name)
/ count(*) * 100 as Mailing
from Offices O
join Employees E
on O.Name = E.Office
group by O.ID, O.Name

For the total number of pieces modify the first query. Then whack them all together any way you want - you can use one big select, or a temp table - whatever your brain can make work. My solution assums you don't want to see offices with no employees, which is probably a fair assumption.

Couple of things please:
1) Normalise your DB
2) Use OfficeID instead of just ID
3) Use singulars for table names, because if you think about it all tables will have more than one row and so end up all being plural. Use singulars and you save yourself from typing an 's' everytime you use a table.
4) Add some foreign key constraints to your tables. How do I know you don't use them? I'm a clairvoyant.
5) Normalise your DB.|||Thanks, Pierre.

I appreciate the tips. Most of what's there was there when I got where I am ;) None the less, I'm new at database design/engineering and have learned a bunch in the short amount of time that i've been doing this. Your input is much appreciated.

And thanks for the help with the queries :) they did the trick!!!

Help! RS REPORT Manager is not lanching!

Hi all,
I have been working on Reporting Services for a while I build and deploy
reports. Just today I tried to go on htt://myserver/reports >> and I get
the following error:
<Error Message Starts
Client found response content type of 'text/html', but expected 'text/xml'.
The request failed with the error message: -- <html> <head>
<title>Server Unavailable </title> </head> <body> <h1><font
face=Verdana color=#ff3300>Server Application Unavailable </font></h1>
<p> <font face=Verdana> The web application you are attempting
to access on this web server is currently unavailable. Please hit the
"Refresh" button in your web browser to retry your request. </p> <p>
<b>Administrator Note: </b> An error message detailing the cause of this
specific request failure can be found in the application event log of the
web server. Please review this log entry to discover what caused this
error to occur. </p> </body> </html> --.
Home
<Error Message End>
When I type http://myserver/reportserver >> I get the default page with a
list of my reports and can lauch one.
I WOULD REALLY APPRECIATE ANYONE'S INPUT ON WHY MY REPORT MANAGER IS NOT
LANCHING EVEN THOUGH REPORTSERVER IS WORKING FINE?
THANK YOU IN ADVANCE
--
Message posted via http://www.sqlmonster.comCan you get to http://<server>/ReportServer/ReportService.asmx ? This is
the web service URL.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"James Woo via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:c5d39b721ee04a35b1cfe8a5b7e8031b@.SQLMonster.com...
> Hi all,
> I have been working on Reporting Services for a while I build and deploy
> reports. Just today I tried to go on htt://myserver/reports >> and I get
> the following error:
> <Error Message Starts
> Client found response content type of 'text/html', but expected
> 'text/xml'.
> The request failed with the error message: -- <html> <head>
> <title>Server Unavailable </title> </head> <body> <h1><font
> face=Verdana color=#ff3300>Server Application Unavailable </font></h1>
> <p> <font face=Verdana> The web application you are attempting
> to access on this web server is currently unavailable. Please hit
> the
> "Refresh" button in your web browser to retry your request. </p> <p>
> <b>Administrator Note: </b> An error message detailing the cause of this
> specific request failure can be found in the application event log of the
> web server. Please review this log entry to discover what caused this
> error to occur. </p> </body> </html> --.
> Home
> <Error Message End>
> When I type http://myserver/reportserver >> I get the default page with a
> list of my reports and can lauch one.
> I WOULD REALLY APPRECIATE ANYONE'S INPUT ON WHY MY REPORT MANAGER IS NOT
> LANCHING EVEN THOUGH REPORTSERVER IS WORKING FINE?
> THANK YOU IN ADVANCE
> --
> Message posted via http://www.sqlmonster.com|||I got the following error when I ran
http://myserver/ReportServer/ReportService.asmx ?
<<<<
Server Application Unavailable
The web application you are attempting to access on this web server is
currently unavailable. Please hit the "Refresh" button in your web browser
to retry your request.
Administrator Note: An error message detailing the cause of this specific
request failure can be found in the application event log of the web
server. Please review this log entry to discover what caused this error to
occur
However when I ran any of my reports that were deployed to the reports
server sucj as:
http://vsvradaher/ReportServer?%2fMDXSamples%
2fMDX_Expression&rs:Command=Render
I had no problem rendering the reports.
Also, I stopped and started the RS service in the Windows
AdministrativeTools, with no avail.
This is nuts. It just happened suddenly.
I really want to avaoid uninstalling and reinstalling RS.
I am tempted to say it is a Reporting Services - IIS issue.
What are the recommended configurations for that?
Thanks again in advance.
--
Message posted via http://www.sqlmonster.com|||Well,
1- The "DafaultAppPool" under "Application Pools" in IIS, where "Reports"
and "ReportServer" Applications reside, had in its ("DafaultAppPool")
properties "Performance" TAB had the "Idle timeout" checked. I unchecked
"Idle timeout", Even though I started my services again ( for Report server
under Control panel, that did not restart the report server applications.
2- Also, In IIS, under "Default Web Site" I CHECKED permissions on the
"reportserver" BIn directory to:
READ - Directory Browsing - and to execute permissions:Script and
Executables.
I am not sure that whatever I did here, is kosher for IIS / RS security,
and If it is , I have no clue how these permissions were off.
I would appreciate it if anyone can guide me to the propper preset
permissions of IIS - Reporting Services.
--
Message posted via http://www.sqlmonster.com

HELP! Report Parameters not working.

I bought a book, seems good, on Reporting Services. Since I am new to this
all, I am on the uphill learning curve. I have a dataset I created and added
parameters. After trying to run the report with the new parameters, I always
get the same error, "The report parameter â'SystemNameâ' has a DefaultValue or
a ValidValue that depends on the report parameter â'SystemNameâ'. Forward
dependencies are not valid." I have read other posts where a solution
provider says that the dataset is not being populated before getting to the
parameter. How do I force the dataset to be populated first? In all the
examples I worked with, I never saw where I had to do something specific
after creating the dataset to initiate the parameter.
Also, after creating a dataset I am able to see the fields without issues.
If I drag/drop one of those fields on my form, I
get:"=First(Fields!FieldName.Value)". In all the examples in the book, I
don't get First(Fields!blah.blah). It is always without parathesis in the
book. I know I am not doing something correctly. Can someone please help me
out.
Thanks much,To address a couple of things:
1) on the parameters:
Are you specifying the source of the parameter is a datasource? I'm
assuming yes, other wise it is simply input by the user and you wouldn't have
problems. In this case, make sure that the datasource you are using actually
returns data. Under the Data tab, open up the datasource for you parameter.
Click on Refresh, and then click the "!" icon to see what data you get. Does
this populate your datasource? Does this give you data to provide for your
parameters in the report?
2) The First(Fields!blah.Value) come out because you probably are just
dragging a field onto the report, and not putting it into a data region (such
as a table or list). The First() thing is saying "give me the first value
returned for this dataset." The reason being, otherwise it doesn't know how
to display the data (i.e. which piece of data do you want to display).
If you create a table, then drag a field to the "details" row of the table,
you should not see the First() thing come up. This is because a table (in
the report) automatically displays a new row for each new row of data
returned in the dataset.
Hope this helps!
"cmcdavid" wrote:
> I bought a book, seems good, on Reporting Services. Since I am new to this
> all, I am on the uphill learning curve. I have a dataset I created and added
> parameters. After trying to run the report with the new parameters, I always
> get the same error, "The report parameter â'SystemNameâ' has a DefaultValue or
> a ValidValue that depends on the report parameter â'SystemNameâ'. Forward
> dependencies are not valid." I have read other posts where a solution
> provider says that the dataset is not being populated before getting to the
> parameter. How do I force the dataset to be populated first? In all the
> examples I worked with, I never saw where I had to do something specific
> after creating the dataset to initiate the parameter.
> Also, after creating a dataset I am able to see the fields without issues.
> If I drag/drop one of those fields on my form, I
> get:"=First(Fields!FieldName.Value)". In all the examples in the book, I
> don't get First(Fields!blah.blah). It is always without parathesis in the
> book. I know I am not doing something correctly. Can someone please help me
> out.
> Thanks much,
>|||David,
Yes, I am wanting the parameter to be filled with a drop-down box on the
report. Yes, I have run this and get back a result. When assigning a field
a parameter, when I run it, it pops up a dialogue box asking for the
parameters. After supplying it with the intial values, it returns what I
want. After running in the report, it doesn't work as I preview it.
Chris
"david boardman" wrote:
> To address a couple of things:
> 1) on the parameters:
> Are you specifying the source of the parameter is a datasource? I'm
> assuming yes, other wise it is simply input by the user and you wouldn't have
> problems. In this case, make sure that the datasource you are using actually
> returns data. Under the Data tab, open up the datasource for you parameter.
> Click on Refresh, and then click the "!" icon to see what data you get. Does
> this populate your datasource? Does this give you data to provide for your
> parameters in the report?
> 2) The First(Fields!blah.Value) come out because you probably are just
> dragging a field onto the report, and not putting it into a data region (such
> as a table or list). The First() thing is saying "give me the first value
> returned for this dataset." The reason being, otherwise it doesn't know how
> to display the data (i.e. which piece of data do you want to display).
> If you create a table, then drag a field to the "details" row of the table,
> you should not see the First() thing come up. This is because a table (in
> the report) automatically displays a new row for each new row of data
> returned in the dataset.
> Hope this helps!
> "cmcdavid" wrote:
> > I bought a book, seems good, on Reporting Services. Since I am new to this
> > all, I am on the uphill learning curve. I have a dataset I created and added
> > parameters. After trying to run the report with the new parameters, I always
> > get the same error, "The report parameter â'SystemNameâ' has a DefaultValue or
> > a ValidValue that depends on the report parameter â'SystemNameâ'. Forward
> > dependencies are not valid." I have read other posts where a solution
> > provider says that the dataset is not being populated before getting to the
> > parameter. How do I force the dataset to be populated first? In all the
> > examples I worked with, I never saw where I had to do something specific
> > after creating the dataset to initiate the parameter.
> >
> > Also, after creating a dataset I am able to see the fields without issues.
> > If I drag/drop one of those fields on my form, I
> > get:"=First(Fields!FieldName.Value)". In all the examples in the book, I
> > don't get First(Fields!blah.blah). It is always without parathesis in the
> > book. I know I am not doing something correctly. Can someone please help me
> > out.
> >
> > Thanks much,
> >
> >

Help! Replication jobs fails when it was working fine.

Hello,
I have a push subscription setup to replicate some tables. Initially, this
was setup properly and everything was working perfectly. Until recently, the
jobs started failing even though nothing was changed. The errors do not give
me enough information to find out what the problem is. Can someone give me
some hints or ideas of what to do?
FYI, I've re-generated snapshots and re-initialized the subscriptions but it
still fails.
FIRST ERROR
==============================================
Date4/21/2005 9:01:35 AM
LogJob History (XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX)
Job NameXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Step ID2
Step NameRun agent.
MessageExecuted as user: xxx\Administrator. The step did not generate any
output. NOTE: The step was retried the requested number of times (10)
without succeeding. The step failed.
Duration00:05:10
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted10
SECOND ERROR
==============================================
Date4/21/2005 9:06:46 AM
LogJob History (XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX)
Job NameXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Step ID3
Step NameDetect nonlogged agent shutdown.
MessageExecuted as user: NT AUTHORITY\SYSTEM. Replication-Replication
Distribution Subsystem: agent XXXXXXXX for XXXXXXXXXX failed. Executed as
user: Domain\Administrator. The step did not generate any output. NOTE: The
step was retried the requested number of times (10) without succeeding. The
step failed. [SQLSTATE 42000] (Error 14151). The step failed.
Duration00:00:00
Sql Severity18
Sql Message ID14151
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
its hard to say. Can you do logging to see if it reveals anything? also
look for dumps in case there were any access violations.
Follow these instructions to enable logging.
http://support.microsoft.com/default...b;en-us;312292
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
|||Also run profiler on the subscriber, check for blocking, or any exceptions
being thrown.
Donna
"Hilary Cotter" wrote:

> its hard to say. Can you do logging to see if it reveals anything? also
> look for dumps in case there were any access violations.
> Follow these instructions to enable logging.
> http://support.microsoft.com/default...b;en-us;312292
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>

Help! Oracle linked server not working

we upgraded servers and now i can't link to an oracle server
old config:
windows2000
sql2000 sp4
mdac 2.8
oracle client 9.2 with latest patches
new config:
windows2003
sql2000 sp4
mdac 2.8
oracle client 9.2 with latest patches
the oracle db is 9.2.
i get the error 7399: OLE DB provider 'MSDAORA' reported an error.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005:]
i've scoured the internet and found numerous solutions to this, none of
which work.
i found an article in oracle technet that said to redo all permissions
on the oraclehome
directory. that didn't work.
i made sure the registry entries pointed to the correct oracle dll's.
no help.
i tried the 10g client with latest patch. no help.
i deleted and readded the linked server. no help.
i can use sqlplus or netmanager and connect to the oracle db just fine
from the sql server.
i don't know what else to try.
i'm wondering if it's windows2003 that's screwing things up.
has anybody been able to link to an oracle server from a win2003 server
running sql server sp4?
Some additional questions:
What platform is the Oracle database?
Is connection pooling being used?
What language is doing the call or are you doing linked servers (four level
table qualifier)?
That particular error has a lot of possibles, as you found. Don't want to
re-hash what you have already ruled out.
Joseph R.P. Maloney, CSP,CCP,CDP
"ch" wrote:

> we upgraded servers and now i can't link to an oracle server
> old config:
> windows2000
> sql2000 sp4
> mdac 2.8
> oracle client 9.2 with latest patches
>
> new config:
> windows2003
> sql2000 sp4
> mdac 2.8
> oracle client 9.2 with latest patches
>
> the oracle db is 9.2.
> i get the error 7399: OLE DB provider 'MSDAORA' reported an error.
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
> returned 0x80004005:]
> i've scoured the internet and found numerous solutions to this, none of
> which work.
> i found an article in oracle technet that said to redo all permissions
> on the oraclehome
> directory. that didn't work.
> i made sure the registry entries pointed to the correct oracle dll's.
> no help.
> i tried the 10g client with latest patch. no help.
> i deleted and readded the linked server. no help.
> i can use sqlplus or netmanager and connect to the oracle db just fine
> from the sql server.
> i don't know what else to try.
> i'm wondering if it's windows2003 that's screwing things up.
> has anybody been able to link to an oracle server from a win2003 server
> running sql server sp4?
>

Help! Oracle linked server not working

we upgraded servers and now i can't link to an oracle server
old config:
windows2000
sql2000 sp4
mdac 2.8
oracle client 9.2 with latest patches
new config:
windows2003
sql2000 sp4
mdac 2.8
oracle client 9.2 with latest patches
the oracle db is 9.2.
i get the error 7399: OLE DB provider 'MSDAORA' reported an error.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005:]
i've scoured the internet and found numerous solutions to this, none of
which work.
i found an article in oracle technet that said to redo all permissions
on the oraclehome
directory. that didn't work.
i made sure the registry entries pointed to the correct oracle dll's.
no help.
i tried the 10g client with latest patch. no help.
i deleted and readded the linked server. no help.
i can use sqlplus or netmanager and connect to the oracle db just fine
from the sql server.
i don't know what else to try.
i'm wondering if it's windows2003 that's screwing things up.
has anybody been able to link to an oracle server from a win2003 server
running sql server sp4?Some additional questions:
What platform is the Oracle database?
Is connection pooling being used?
What language is doing the call or are you doing linked servers (four level
table qualifier)?
That particular error has a lot of possibles, as you found. Don't want to
re-hash what you have already ruled out.
--
Joseph R.P. Maloney, CSP,CCP,CDP
"ch" wrote:
> we upgraded servers and now i can't link to an oracle server
> old config:
> windows2000
> sql2000 sp4
> mdac 2.8
> oracle client 9.2 with latest patches
>
> new config:
> windows2003
> sql2000 sp4
> mdac 2.8
> oracle client 9.2 with latest patches
>
> the oracle db is 9.2.
> i get the error 7399: OLE DB provider 'MSDAORA' reported an error.
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
> returned 0x80004005:]
> i've scoured the internet and found numerous solutions to this, none of
> which work.
> i found an article in oracle technet that said to redo all permissions
> on the oraclehome
> directory. that didn't work.
> i made sure the registry entries pointed to the correct oracle dll's.
> no help.
> i tried the 10g client with latest patch. no help.
> i deleted and readded the linked server. no help.
> i can use sqlplus or netmanager and connect to the oracle db just fine
> from the sql server.
> i don't know what else to try.
> i'm wondering if it's windows2003 that's screwing things up.
> has anybody been able to link to an oracle server from a win2003 server
> running sql server sp4?
>

Help! Oracle linked server not working

we upgraded servers and now i can't link to an oracle server
old config:
windows2000
sql2000 sp4
mdac 2.8
oracle client 9.2 with latest patches
new config:
windows2003
sql2000 sp4
mdac 2.8
oracle client 9.2 with latest patches
the oracle db is 9.2.
i get the error 7399: OLE DB provider 'MSDAORA' reported an error.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005:]
i've scoured the internet and found numerous solutions to this, none of
which work.
i found an article in oracle technet that said to redo all permissions
on the oraclehome
directory. that didn't work.
i made sure the registry entries pointed to the correct oracle dll's.
no help.
i tried the 10g client with latest patch. no help.
i deleted and readded the linked server. no help.
i can use sqlplus or netmanager and connect to the oracle db just fine
from the sql server.
i don't know what else to try.
i'm wondering if it's windows2003 that's screwing things up.
has anybody been able to link to an oracle server from a win2003 server
running sql server sp4?Some additional questions:
What platform is the Oracle database?
Is connection pooling being used?
What language is doing the call or are you doing linked servers (four level
table qualifier)?
That particular error has a lot of possibles, as you found. Don't want to
re-hash what you have already ruled out.
Joseph R.P. Maloney, CSP,CCP,CDP
"ch" wrote:

> we upgraded servers and now i can't link to an oracle server
> old config:
> windows2000
> sql2000 sp4
> mdac 2.8
> oracle client 9.2 with latest patches
>
> new config:
> windows2003
> sql2000 sp4
> mdac 2.8
> oracle client 9.2 with latest patches
>
> the oracle db is 9.2.
> i get the error 7399: OLE DB provider 'MSDAORA' reported an error.
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initializ
e
> returned 0x80004005:]
> i've scoured the internet and found numerous solutions to this, none of
> which work.
> i found an article in oracle technet that said to redo all permissions
> on the oraclehome
> directory. that didn't work.
> i made sure the registry entries pointed to the correct oracle dll's.
> no help.
> i tried the 10g client with latest patch. no help.
> i deleted and readded the linked server. no help.
> i can use sqlplus or netmanager and connect to the oracle db just fine
> from the sql server.
> i don't know what else to try.
> i'm wondering if it's windows2003 that's screwing things up.
> has anybody been able to link to an oracle server from a win2003 server
> running sql server sp4?
>

Help! INSERT Replication not working on Subscriber FOR ONE TABLE ONLY

Everything is working great except ONE table is not replicating INSERTS from
the subscriber back to the publisher. UPDATES *are* being replicating, and
INSERTS *are* being replicated for every other table! I can't figure out
what's going on.
When I first setup the subscription/publication, the table did have a "int
identity" field. This field has now been removed but INSERTS are still not
being replicated.
Can someone please help!
Thanks!
-Ryan
Ben,
what type of replication are you using. Assuming it is transactional, have a
look at the article properties for the problematic table, and the commands
tab. Check that the replace Insert command isn't set to 'NONE'.
HTH,
Paul Ibison
|||Ben,
what type of replication are you using. Assuming it is transactional, have a
look at the article properties for the problematic table, and the commands
tab. Check that the replace Insert command isn't set to 'NONE'.
HTH,
Paul Ibison
|||Paul,
I'm using merge replication. What is the "commands tab"? I can't find this
in the articles properties page.
I ended up getting this thing to work by dropping the publication and
recreating it, then reinitializing my subscriber. But I'd still like to
konw why it wasn't working before. Did it have something to do with the INT
IDENTITY field? Why didn't it work after I deleted this field?
(btw, this is Ryan, my last message was sent from a coworker's computer)
Thanks, Ryan
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eVpz8BbXEHA.2840@.TK2MSFTNGP11.phx.gbl...
> Ben,
> what type of replication are you using. Assuming it is transactional, have
a
> look at the article properties for the problematic table, and the commands
> tab. Check that the replace Insert command isn't set to 'NONE'.
> HTH,
> Paul Ibison
>
|||Paul,
I'm using merge replication. What is the "commands tab"? I can't find this
in the articles properties page.
I ended up getting this thing to work by dropping the publication and
recreating it, then reinitializing my subscriber. But I'd still like to
konw why it wasn't working before. Did it have something to do with the INT
IDENTITY field? Why didn't it work after I deleted this field?
(btw, this is Ryan, my last message was sent from a coworker's computer)
Thanks, Ryan
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eVpz8BbXEHA.2840@.TK2MSFTNGP11.phx.gbl...
> Ben,
> what type of replication are you using. Assuming it is transactional, have
a
> look at the article properties for the problematic table, and the commands
> tab. Check that the replace Insert command isn't set to 'NONE'.
> HTH,
> Paul Ibison
>
|||Ryan,
I don't know what was wrong previously. As you are using merge, I would have
checked that the merge trigger was firing and that the record in
msmerge_contents was being inserted. Then there are dummy updates and the
use of profiler to track what's happening. Anyway, if this crops up again
please report back and we can investigate it then.
Regards,
Paul Ibison
|||Ryan,
I don't know what was wrong previously. As you are using merge, I would have
checked that the merge trigger was firing and that the record in
msmerge_contents was being inserted. Then there are dummy updates and the
use of profiler to track what's happening. Anyway, if this crops up again
please report back and we can investigate it then.
Regards,
Paul Ibison

HELP! BACK button not working on matrix

I have a report that has a summary matrix containing all urls for a client
which will drill to a second report which is a detail matrix that shows
detail for the selected url from report #1. The problem is that when they
drill to report #2 ... if they expand the matrix to show individual detail
for past 30 days and then want to go back to the previous report and choose
another url ... the back button wont work unless they hit it twice. If they
happen to expand AND contract the detail and THEN want to go back to the prev
report they have to hit the browser back button THREE times. Report manager
or the browser seems to be keeping track of key strokes - however my user is
NOT HAPPY. How can I solve this? I need the equivalent of a BACK button.
But then ... do I have to put in all the parameters that the first report
required again? I am not sure what to do but I have to do it fast!Are you using the browser's back button or the report's back button? If
you're not using the browser's, try it. That should bring you back to
report #1.
You could also launch report two in a new window so that they could
just toggle between the two as they see fit, but I've had trouble
getting a report to launch in a new window.
Mike|||Mike,
Thanks for your reply. I am using the browser's back button (the report
doesnt have one that I can see that will take it to the previous report). I
have not had this problem with other reports ... just the matrix report and I
it has to do with the toggle to expand/contract detail. Launching another
window from the summary (first rpt) isnt allowed because this is deployed to
a portal and I am already launching a separate window there from an asp .net
page to pass report parameters to the first report. They dont want yet
*another* window to get the detail. Any other suggestions? I am not an asp
.net or vb .net wizard - someone else coded the initial UI that launches the
report so if you suggest something along those lines ... please be specific
with an example if possible. Thanks verry much!!
"Bassist695" wrote:
> Are you using the browser's back button or the report's back button? If
> you're not using the browser's, try it. That should bring you back to
> report #1.
> You could also launch report two in a new window so that they could
> just toggle between the two as they see fit, but I've had trouble
> getting a report to launch in a new window.
> Mike
>

HELP! activation fails CantCommunicateWithReportServerException

Hi
I have read all of the posts in this group (and others!) but still
can't get RS working. I am installing RS on an XP sp2 machine with
VS2003 and SQL2000 sp4. Installation reports activation failed, but
when I check Keys table in ReportServer db there is a row with the
client as 1 and both key binary values.
I have tried rskeymgmt -d, rsactivate -c "path" which generates a new
key, but I still get
" Unable to communicate with report server. Please verify that the
report server is operational. " when I go to
http://localhost/Reports/Pages/Folder.aspx.
And the log file has
aspnet_wp!ui!f80!19/07/2005-09:47:35:: e ERROR: HTTP status code -->
500
--Details--
CantCommunicateWithReportServerException: Unable to communicate with
report server. Please verify that the report server is operational.
at
Microsoft.ReportingServices.UI.RSWebServiceWrapper.GetSecureMethods()
at
Microsoft.SqlServer.ReportingServices.RSConnection.IsSecureMethod(String
methodname)
at Microsoft.ReportingServices.UI.Global.SecureAllAPI()
at
Microsoft.ReportingServices.UI.ReportingPage.EnsureHttpsLevel(HttpsLevel
level)
at
Microsoft.ReportingServices.UI.ReportingPage.ReportingPage_Init(Object
sender, EventArgs args)
at System.EventHandler.Invoke(Object sender, EventArgs e)
at System.Web.UI.Control.OnInit(EventArgs e)
at System.Web.UI.Control.InitRecursive(Control namingContainer)
at System.Web.UI.Page.ProcessRequestMain()
aspnet_wp!ui!f80!19/07/2005-09:47:37:: e ERROR: Exception in
ShowErrorPage: System.Threading.ThreadAbortException: Thread was being
aborted.
etc...
I'd be very greatful if someone could assist as I am tearing what's
left of my hair out!Hi
I have found a solution (may not be the only one!).
So, for anyone who is having the same problem...
1. Uninstalled RS
2. I reinstalled the .NET framework (i think this made no difference,
bu who knows)
3. Granted admin rights to ASPNET and IWAM accounts
4. I reinstalled RS, using a domain account rather than the default for
the NT Service
5. replaced the <assemblies> section in the <system.web> section of the
C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
Services\ReportServer\web.config file with
<assemblies>
<clear />
<add assembly="ReportingServicesWebServer" />
<add assembly="mscorlib" />
</assemblies>
6. Change the following line in both web.config files
C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
Services\ReportServer
and: C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
Services\ReportManager
with <trust level="Full" originUrl="" />
And for me, this fixed it. I <i>really</i> hope this fixes it for you,
too. This has been a REAL pain in the you-konw-what. Why Microsoft
can't get the default installation to work out it is on XP and not 2003
and act accordingly is beyond me.
shl wrote:
> Hi
> I have read all of the posts in this group (and others!) but still
> can't get RS working. I am installing RS on an XP sp2 machine with
> VS2003 and SQL2000 sp4. Installation reports activation failed, but
> when I check Keys table in ReportServer db there is a row with the
> client as 1 and both key binary values.
> I have tried rskeymgmt -d, rsactivate -c "path" which generates a new
> key, but I still get
> " Unable to communicate with report server. Please verify that the
> report server is operational. " when I go to
> http://localhost/Reports/Pages/Folder.aspx.
> And the log file has
> aspnet_wp!ui!f80!19/07/2005-09:47:35:: e ERROR: HTTP status code -->
> 500
> --Details--
> CantCommunicateWithReportServerException: Unable to communicate with
> report server. Please verify that the report server is operational.
> at
> Microsoft.ReportingServices.UI.RSWebServiceWrapper.GetSecureMethods()
> at
> Microsoft.SqlServer.ReportingServices.RSConnection.IsSecureMethod(String
> methodname)
> at Microsoft.ReportingServices.UI.Global.SecureAllAPI()
> at
> Microsoft.ReportingServices.UI.ReportingPage.EnsureHttpsLevel(HttpsLevel
> level)
> at
> Microsoft.ReportingServices.UI.ReportingPage.ReportingPage_Init(Object
> sender, EventArgs args)
> at System.EventHandler.Invoke(Object sender, EventArgs e)
> at System.Web.UI.Control.OnInit(EventArgs e)
> at System.Web.UI.Control.InitRecursive(Control namingContainer)
> at System.Web.UI.Page.ProcessRequestMain()
> aspnet_wp!ui!f80!19/07/2005-09:47:37:: e ERROR: Exception in
> ShowErrorPage: System.Threading.ThreadAbortException: Thread was being
> aborted.
> etc...
> I'd be very greatful if someone could assist as I am tearing what's
> left of my hair out!

HELP! " the table schema changed after the cursor was declared"

I have a DTS package working from backend and command line. Then I did an enhancement. It works from backend but not from command line. The error says:

Step Error Description:Could not complete cursor operation because the table schema changed after the cursor was declared.

But I don't see "table schema" change?!! Any idea?

Thanks,

LiliOriginally posted by lili3000
I have a DTS package working from backend and command line. Then I did an enhancement. It works from backend but not from command line. The error says:

Step Error Description:Could not complete cursor operation because the table schema changed after the cursor was declared.

But I don't see "table schema" change?!! Any idea?

Thanks,

Lili

I ran in to the same problem. As it turned out I was running the application at 1:00 am to process credit card transaction (This application is a batched response from shipper) Anyway. I had inadvertly schedule maintence during this time. The problem was resolved by moving the Maintence time.

I hope this helps.

Terry

Help writing the SqlConnectionString.

Hi.

I realize this may have been asked a thousand times before, but it's still not working for me. I would appreciate any help with it.

First, I created a GridView, and from it I created a new SqlDataSource and let it point to the database in C:\My Project\App_Data\ASPNETDB.MDF. This automatically created a connectionString in the Web.Config file, saying the following:

 "R1ASPNETDBConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename="C:\My Project\App_Data\ASPNETDB.MDF";Integrated Security=True;Connect Timeout=30;User Instance=True" providerName="System.Data.SqlClient" />

Now when I try to programatically use that connection string, the program crashes.

Below is my code:

<script runat="server"
 String sqlConnectionString = @."Data Source=.\SQLEXPRESS;AttachDbFilename="C:\My Project\App_Data\ASPNETDB.MDF";Integrated Security=True;Connect Timeout=30;User Instance=True";protected void Page_Load(object sender, EventArgs e)
 {
using (SqlConnection conn =new SqlConnection(sqlConnectionString))
 {
 conn.Open();
const String selectQuery ="SELECT StatusName FROM Status ORDER BY StatusName";
 Label1.Text ="";
using (SqlCommand cmd =new SqlCommand(selectQuery, conn))
 {
 SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
 {
 Label1.Text += dr[0];
while (dr.Read())
 {
 Label1.Text +=", ";
 Label1.Text += dr[0];
 }
 Label1.Text +=". ";
 }
else Label1.Text ="None.";
 }
if (conn !=null)
 conn.Close();
 }
 }

Here's the error message I'm getting:

Server Error in '/My Project' Application.

---

Keyword not supported: 'c:\my project\app_data\aspnetdb.mdf";integrated security'.

Description:Anunhandled exception occurred during the execution of the current webrequest. Please review the stack trace for more information about theerror and where it originated in the code.

Exception Details:System.ArgumentException: Keyword not supported: 'c:\my project\app_data\aspnetdb.mdf";integrated security'.

Source Error:

Line 15: protected void Page_Load(object sender, EventArgs e)
Line 16: {
Line 17: using (SqlConnection conn = new SqlConnection(sqlConnectionString))
Line 18: {
Line 19: conn.Open();

Source File: c:\My Project\test.aspx Line: 17

Stack Trace:

[ArgumentException: Keyword not supported: 'c:\my project\app_data\aspnetdb.mdf";integrated security'.]
 System.Data.Common.DbConnectionOptions.ParseInternal(Hashtable parsetable, String connectionString, Boolean buildChain, Hashtable synonyms, Boolean firstKey) +417
 System.Data.Common.DbConnectionOptions..ctor(String connectionString, Hashtable synonyms, Boolean useOdbcRules) +99
 System.Data.SqlClient.SqlConnectionString..ctor(String connectionString) +52
 System.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous) +25
 System.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(String connectionString, DbConnectionPoolGroupOptions poolOptions, DbConnectionOptions& userConnectionOptions) +141
 System.Data.SqlClient.SqlConnection.ConnectionString_Set(String value) +38
 System.Data.SqlClient.SqlConnection.set_ConnectionString(String value) +4
 System.Data.SqlClient.SqlConnection..ctor(String connectionString) +21
 ASP.test_aspx.Page_Load(Object sender, EventArgs e) in c:\My Project\test.aspx:17
 System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +13
 System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +45
 System.Web.UI.Control.OnLoad(EventArgs e) +80
 System.Web.UI.Control.LoadRecursive() +49
 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3745

My question is: How should I write the connection string? I have a feeling the problem is with the quotes ("), but I can't figure out how to write it otherwise.

Thank you very much.

Hi

the solution is to remove the double quotation that is inside the connections string :

String sqlConnectionString = @."Data Source=.\SQLEXPRESS;AttachDbFilename=C:\My Project\App_Data\ASPNETDB.MDF;Integrated Security=True;Connect Timeout=30;User Instance=True";

Still this is not a good practice ,since you are storing an absolute path for your MDB which causes the problems when you deploy the APP.

so you need this instead:

String sqlConnectionString = @.Data Source=.\SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|ASPNETDB.MDF;Connect Timeout=30;

Note that |DataDirectory| will be replaced automaticly with the App_Data path by the runtime .

|||

anas:

String sqlConnectionString = @."Data Source=.\SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|ASPNETDB.MDF;Connect Timeout=30";

Awesome, it's working. Thank you very much for taking the time and explaining it.

Help with weird error

I am working with Access frontends and a SQL Server 2000 backend.
One user gets the following error when trying to insert directly into
a table through access or through a form into that same table: "String
or binary data would be truncated". It doesn't matter into which type
of column the data is entered. Int, datetime, varchar all produce the
same error.
This user has access to the db this table is stored in and can write
to other tables in that db with no problem. In fact, none of the other
users with the same permission settings to this db has this problem.
They can insert into or update this same table just fine.
This is also pc independent for this user. It does seem to be a matter
of account setting though, because none of the other users has this
problem.
What could be wrong? Does anybody have any ideas? As far as I know
this user has the exact same permission setting as others. What else
could I look into to solve this mystery?
Thanks for your help.> One user gets the following error when trying to insert directly into
> a table through access or through a form into that same table: "String
> or binary data would be truncated".
This sounds like a string is attempting to be inserted but it is too long to
fit in the column. This is not a "weird" error, in fact it is quite common.
I highly doubt it has anything to do with this user or his/her permissions,
unless there is a trigger on the table and the failure is occurring because
his domain username is too long to fit into the auditing table.|||I fixed the problem. As it turned out, for this particular user the
username is saved with its domain name. The table field to which this
user info is saved wasn't large enough to hold both the username and
domain name.
Thanks for your reply, Aaron. As you predicted, the username was too
long to be stored.
On Mar 11, 9:33 am, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> > One user gets the following error when trying to insert directly into
> > a table through access or through a form into that same table: "String
> > or binary data would be truncated".
> This sounds like a string is attempting to be inserted but it is too long to
> fit in the column. This is not a "weird" error, in fact it is quite common.
> I highly doubt it has anything to do with this user or his/her permissions,
> unless there is a trigger on the table and the failure is occurring because
> his domain username is too long to fit into the auditing table.

Help with weird error

I am working with Access frontends and a SQL Server 2000 backend.
One user gets the following error when trying to insert directly into
a table through access or through a form into that same table: "String
or binary data would be truncated". It doesn't matter into which type
of column the data is entered. Int, datetime, varchar all produce the
same error.
This user has access to the db this table is stored in and can write
to other tables in that db with no problem. In fact, none of the other
users with the same permission settings to this db has this problem.
They can insert into or update this same table just fine.
This is also pc independent for this user. It does seem to be a matter
of account setting though, because none of the other users has this
problem.
What could be wrong? Does anybody have any ideas? As far as I know
this user has the exact same permission setting as others. What else
could I look into to solve this mystery?
Thanks for your help.
> One user gets the following error when trying to insert directly into
> a table through access or through a form into that same table: "String
> or binary data would be truncated".
This sounds like a string is attempting to be inserted but it is too long to
fit in the column. This is not a "weird" error, in fact it is quite common.
I highly doubt it has anything to do with this user or his/her permissions,
unless there is a trigger on the table and the failure is occurring because
his domain username is too long to fit into the auditing table.
|||I fixed the problem. As it turned out, for this particular user the
username is saved with its domain name. The table field to which this
user info is saved wasn't large enough to hold both the username and
domain name.
Thanks for your reply, Aaron. As you predicted, the username was too
long to be stored.
On Mar 11, 9:33 am, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> This sounds like a string is attempting to be inserted but it is too long to
> fit in the column. This is not a "weird" error, in fact it is quite common.
> I highly doubt it has anything to do with this user or his/her permissions,
> unless there is a trigger on the table and the failure is occurring because
> his domain username is too long to fit into the auditing table.