problem running vb.net application with sqlexpress in system with only .NET FRAMEWORK

Hi,

I have developed a stand alone application that get data from excel and dumps it in local database (sql server database primary data file) in visual studio .net 2005. I had no issues while developing the application. When i am trying to install this application in the standalone system with .net framework 2.0 and no backend database it giving the following error

provider: sql network interfaces, error 26 - Error locating Server/ Instance Specified

connection string i am using is

Dim objLocalDB As System.Data.SqlClient.SqlConnection

objLocalDB = New System.Data.SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=" & System.AppDomain.CurrentDomain.BaseDirectory & "LFDB.mdf;Integrated Security=True;User Instance=True")

I dont want to use any backend database. I only want to use the database that comes with .net (i.e sqlexpress)

Please help me how can i get through this problem.

hi,

Manyam wrote:

When i am trying to install this application in the standalone system with .net framework 2.0 and no backend database it giving the following error

provider: sql network interfaces, error 26 - Error locating Server/ Instance Specified

I can't understand if you installed SQLExpress as well, on the target computer... did you?

regards

|||SQL Server Express is a backend database although the GUI tools are more lightweight than the ones of the more bigger editions. If you want to use user instances as mentioned in the connection string, you will need to have a SQL Server Express Service in place to attach the database to the Service. SQL Server Express can be deployed within your application (even through clickonce deplyoment but at the end need to be installed).

HTH, Jens K. Suessmeyer.

http://www.sqlserver0205.de
|||

Manyam wrote:

I dont want to use any backend database. I only want to use the database that comes with .net (i.e sqlexpress)

Please help me how can i get through this problem.

SQL Express is not part of the .NET Framework, it is a completely separate product that must be installed if you are going to use it. You can add SQL Express as a prerequisite to you application installation automatically using either ClickOnce or standard Setup Projects in VS 2005, in your projects properties, click the Prerequisite button and make sure SQL Express is checked in the list. This will automatically include the SQL Express installer in your deployment and install it on the target computer if it is needed.

Mike

|||

Hi,

Thanks for your suggestion. So we cannot run vb.net application that is developed using sql server management studio without SQL EXPRESS and only .net framework.

I tried to open the link provided by you. Its not opening. I am getting page cannot be displayed.

|||

Hi,

Thanks for your valueable suggestion. So i cannot run a vb application developed using sql server management studio without SQL EXPRESS.I tried installing SQL EXPRESS in standalone system, but its giving issues.

Process followed -->

Uninstalled existing framework.

Installed .NET framework 2.0Dowloaded SQLEXPRESS and istalled in system.

Its throwing a error that .net should require some updates... What updates do we need to install to get this working.

Thanks in advance.

|||What's your OS? If WIndows XP Pro, this needs SP2 and updated security patches

Problem returning a datarow

Hi,

I have a client/server app. that uses a windows service for the server and asp.net web pages for the client side. My server class has 3 methods that Fill, Add a new record and Update a record. The Fill and Add routines work as expected but unfortunately the update request falls at the 1st hurdle.

I pass two params to the remote(server) method for the update, one is the unique ID and the other is a string that is the name of the table in the database. See code below. I need the SelectedRow method to return a datarow that will then populate textbox's on another page. When the method is called I get an 'internal system error....please turn on custom errors in the web.config file on the server for more info.(unfortunately my server is not s web server so I don't have a web.config file!!).

Can anyone see anything obvious.

Cheers. >
Calling routine:
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

System.Threading.Thread.CurrentThread.CurrentCultu re = New CultureInfo("en-GB")

hsc = CType(Activator.GetObject(GetType(IHelpSC), _
"tcp://192.168.2.3:1234/HelpSC"), IHelpSC)

Dim drEdit As DataRow
Dim intRow As Integer = CInt(Request.QueryString("item"))

strDiscipline = Request.QueryString("discipline")
drEdit = hsc.SelectedRow(intRow, strDiscipline) <<Call the remote method
strRecord = drEdit.Item(0)
txtLogged.Text = drEdit(1)
txtEngineer.Text = drEdit.Item(3)

End Sub

Remote Class Function:

Public Function SelectedRow(ByVal id As Integer, ByVal discipline As String) As System.Data.DataRow Implements IHelpSC.SelectedRow

strDiscipline = Trim(discipline)
Dim cmdSelect As SqlCommand = sqlcnn.CreateCommand
Dim drResult As DataRow
Dim strQuery As String = "SELECT * FROM " & strDiscipline & _
" WHERE CallID=" & id

cmdSelect.CommandType = CommandType.Text
cmdSelect.CommandText = strQuery

sqlda = New SqlDataAdapter
sqlda.SelectCommand = cmdSelect

ds = New DataSet
sqlda.Fill(ds, "Results")
drResult = ds.Tables(0).Rows(0)

Return drResult

End FunctionPhil (Phil@.nospam.com) writes:
> I have a client/server app. that uses a windows service for the server
> and asp.net web pages for the client side. My server class has 3 methods
> that Fill, Add a new record and Update a record. The Fill and Add
> routines work as expected but unfortunately the update request falls at
> the 1st hurdle.
> I pass two params to the remote(server) method for the update, one is
> the unique ID and the other is a string that is the name of the table in
> the database. See code below. I need the SelectedRow method to return a
> datarow that will then populate textbox's on another page. When the
> method is called I get an 'internal system error....please turn on
> custom errors in the web.config file on the server for more
> info.(unfortunately my server is not s web server so I don't have a
> web.config file!!).

I don't really have an idea, but the error message does not look
like it comes from SQL Server. Maybe you should try an ADO .Net group.

>Dim intRow As Integer = CInt(Request.QueryString("item"))
>strDiscipline = Request.QueryString("discipline")
>drEdit = hsc.SelectedRow(intRow, strDiscipline) <<Call the remote method
> Dim strQuery As String = "SELECT * FROM " & strDiscipline & _
> " WHERE CallID=" & id

I don't know what this Request.QueryString implies, but this is any
sorr of user input, you have a major hole here. What if the user
specifies a table that does not exist? What if he specifies
"tbl; DROP DATABASE important; --"? This is called SQL injection,
and is a popular way for intruders to get access to things they should
have access to.

I don't know why you pass the table name as a parameter, but it's
not likely to be good design. For the CallID you should in any case
use a parameter:

Dim strQuery As String = "SELECT * FROM " & strDiscipline & _
" WHERE CallID=@.id"
cmdSelect.AddParameter(@.id, SqlInt, Id)

(With all reservations for the exact syntax.) Parameterizing your
SQL statements protects you from SQL injection.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Erland,

Thanks for your response. Although we haven't found my problem I will just
comment on your response FWIW :_)

The QueryString property of the HTTPRequest class adds two, lets call them
parameters are passed from the calling page. These params are 'hard-coded'
items in a dropdownlist and selected row from a datagrid. So, I utterly
agree with your concerns regarding SQL injection but 'hopefully' in this
instance I'm ok...!!! The other two method calls to the database do in fact
use parameterised stored procedures (if that absolves me in any way :-).

My problem/puzzlement is that if I run the client app. with the data layer
class (with no changes, ie. still accesses the remote server), it works
perfectly. Just to clarify.....the class with the data layer (ie.
interfacing directly with the dB via direct sql calls or parameterised
stored procs) normally resides on the server and the client communicates
with this class using .NET remoting. Just to remember, I have 3 methods. The
Fill method is called when the client page is 1st loaded and populates a
datagrid...this works. I also have a button on the same page as the datagrid
that calls the AddNew method to add a new record to the db, this also works
fine. Finally, the datagrid has a button column that is for edit/update of
the selected record. This is where I receive the error BUT.........it
works if I 'move' the data layer class to the client side and call the
method ...GGGrrrr...it's very frustrating!!

Thanks for your help.

Phil

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns964E55D31DC2Yazorman@.127.0.0.1...
> Phil (Phil@.nospam.com) writes:
>> I have a client/server app. that uses a windows service for the server
>> and asp.net web pages for the client side. My server class has 3 methods
>> that Fill, Add a new record and Update a record. The Fill and Add
>> routines work as expected but unfortunately the update request falls at
>> the 1st hurdle.
>>
>> I pass two params to the remote(server) method for the update, one is
>> the unique ID and the other is a string that is the name of the table in
>> the database. See code below. I need the SelectedRow method to return a
>> datarow that will then populate textbox's on another page. When the
>> method is called I get an 'internal system error....please turn on
>> custom errors in the web.config file on the server for more
>> info.(unfortunately my server is not s web server so I don't have a
>> web.config file!!).
> I don't really have an idea, but the error message does not look
> like it comes from SQL Server. Maybe you should try an ADO .Net group.
>>Dim intRow As Integer = CInt(Request.QueryString("item"))
>>
>>strDiscipline = Request.QueryString("discipline")
>>drEdit = hsc.SelectedRow(intRow, strDiscipline) <<Call the remote method
>>
>> Dim strQuery As String = "SELECT * FROM " & strDiscipline & _
>> " WHERE CallID=" & id
> I don't know what this Request.QueryString implies, but this is any
> sorr of user input, you have a major hole here. What if the user
> specifies a table that does not exist? What if he specifies
> "tbl; DROP DATABASE important; --"? This is called SQL injection,
> and is a popular way for intruders to get access to things they should
> have access to.
> I don't know why you pass the table name as a parameter, but it's
> not likely to be good design. For the CallID you should in any case
> use a parameter:
> Dim strQuery As String = "SELECT * FROM " & strDiscipline & _
> " WHERE CallID=@.id"
> cmdSelect.AddParameter(@.id, SqlInt, Id)
> (With all reservations for the exact syntax.) Parameterizing your
> SQL statements protects you from SQL injection.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Phil (Phil@.nospam.com) writes:
> The QueryString property of the HTTPRequest class adds two, lets call
> them parameters are passed from the calling page. These params are
> 'hard-coded' items in a dropdownlist and selected row from a datagrid.
> So, I utterly agree with your concerns regarding SQL injection but
> 'hopefully' in this instance I'm ok...!!!

It it was a Windows Forms client, it would be safe I guess. But you
have a web client, right? Somehow the information on what the user
select must be passed over the network. The obvious case is when the
parameter appears in a URL. But anything which is over a network port
over which an intruder has full control of his end could be susceptible.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Problem Report Viewer Control

I am having an authentication problem using the Report Viewer Control. The
situation is that I have developed a simple ASP.NET application that will
used as an interface for Report Viewing. First, I have a TreeView Control
located on the default page that is being populated with the current reports
that are published on the Report Server. Second, I have a aspx page called
ReportViewer that holds the Report Viewer Control. Third, when a user clicks
on one of the reports in the TreeView control the report is generated in an
IFrame located in a table cell on the default page.
My Problem is that everytime I click to generate a report I am asked for
windows credentials. I am passing the full network credentials(testing
environment going to use an application pool when live and pass default
credentials) example System.Net.NetworkCredential("username", "password",
"domain") in the load event of both aspx pages and IIS directory security is
setup with anonymous access & integrated windows authentication(I have tried
about everything in IIS Directory Security). I have tried about everything
and for the life of me cannot get around this issue any assistance would be
greatly appreciated.
Thanks,
--
Andrew
MCSA,MCDBAIf your asp app is on a different machine then I think you are hitting the
double hop issue. You need to be using Kerberos to support double hop. This
is an IIS issue. Here is a good link:
http://support.microsoft.com/default.aspx?scid=kb;en-us;264921
I notice that you have enable anonymous on IIS where RS is located. This is
a bad idea. It will cause difficulties with managing Report Server. Either
you need to setup your security using Kerberos or you will have to implement
forms based authentication. If there is another way around it I am not sure
what it would be.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:60F64653-3B04-4E3C-9ADA-D06A191AB9EE@.microsoft.com...
>I am having an authentication problem using the Report Viewer Control. The
> situation is that I have developed a simple ASP.NET application that will
> used as an interface for Report Viewing. First, I have a TreeView Control
> located on the default page that is being populated with the current
> reports
> that are published on the Report Server. Second, I have a aspx page called
> ReportViewer that holds the Report Viewer Control. Third, when a user
> clicks
> on one of the reports in the TreeView control the report is generated in
> an
> IFrame located in a table cell on the default page.
> My Problem is that everytime I click to generate a report I am asked for
> windows credentials. I am passing the full network credentials(testing
> environment going to use an application pool when live and pass default
> credentials) example System.Net.NetworkCredential("username", "password",
> "domain") in the load event of both aspx pages and IIS directory security
> is
> setup with anonymous access & integrated windows authentication(I have
> tried
> about everything in IIS Directory Security). I have tried about everything
> and for the life of me cannot get around this issue any assistance would
> be
> greatly appreciated.
> Thanks,
> --
> Andrew
> MCSA,MCDBA|||Bruce,
Thanks for the response and I am just using windows authentication on the
live report server. I enabled the anonymous access in a testing environment
just to try to get around the authentication problems I was encountering. My
issue was that I am developing in a different domain and I am accessing the
report server by IP address. Well when I implemented the application on the
production domain I never changed the report server path to the actual report
server machine name and this caused my authentication problem. Just one of
those little slip ups that seem to cause the biggest problems. Thanks,
--
Andrew
MCSA,MCDBA
--
Andrew
MCSA,MCDBA
"Bruce L-C [MVP]" wrote:
> If your asp app is on a different machine then I think you are hitting the
> double hop issue. You need to be using Kerberos to support double hop. This
> is an IIS issue. Here is a good link:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;264921
> I notice that you have enable anonymous on IIS where RS is located. This is
> a bad idea. It will cause difficulties with managing Report Server. Either
> you need to setup your security using Kerberos or you will have to implement
> forms based authentication. If there is another way around it I am not sure
> what it would be.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Andrew" <Andrew@.discussions.microsoft.com> wrote in message
> news:60F64653-3B04-4E3C-9ADA-D06A191AB9EE@.microsoft.com...
> >I am having an authentication problem using the Report Viewer Control. The
> > situation is that I have developed a simple ASP.NET application that will
> > used as an interface for Report Viewing. First, I have a TreeView Control
> > located on the default page that is being populated with the current
> > reports
> > that are published on the Report Server. Second, I have a aspx page called
> > ReportViewer that holds the Report Viewer Control. Third, when a user
> > clicks
> > on one of the reports in the TreeView control the report is generated in
> > an
> > IFrame located in a table cell on the default page.
> >
> > My Problem is that everytime I click to generate a report I am asked for
> > windows credentials. I am passing the full network credentials(testing
> > environment going to use an application pool when live and pass default
> > credentials) example System.Net.NetworkCredential("username", "password",
> > "domain") in the load event of both aspx pages and IIS directory security
> > is
> > setup with anonymous access & integrated windows authentication(I have
> > tried
> > about everything in IIS Directory Security). I have tried about everything
> > and for the life of me cannot get around this issue any assistance would
> > be
> > greatly appreciated.
> >
> > Thanks,
> >
> > --
> > Andrew
> > MCSA,MCDBA
>
>|||Ahh, same symptom as double hop but different reason. Glad you got it figure
out.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:28AC4137-F5A4-4F09-8B00-80126775DBB6@.microsoft.com...
> Bruce,
> Thanks for the response and I am just using windows authentication on the
> live report server. I enabled the anonymous access in a testing
> environment
> just to try to get around the authentication problems I was encountering.
> My
> issue was that I am developing in a different domain and I am accessing
> the
> report server by IP address. Well when I implemented the application on
> the
> production domain I never changed the report server path to the actual
> report
> server machine name and this caused my authentication problem. Just one
> of
> those little slip ups that seem to cause the biggest problems. Thanks,
> --
> Andrew
> MCSA,MCDBA
> --
> Andrew
> MCSA,MCDBA
>
> "Bruce L-C [MVP]" wrote:
>> If your asp app is on a different machine then I think you are hitting
>> the
>> double hop issue. You need to be using Kerberos to support double hop.
>> This
>> is an IIS issue. Here is a good link:
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;264921
>> I notice that you have enable anonymous on IIS where RS is located. This
>> is
>> a bad idea. It will cause difficulties with managing Report Server.
>> Either
>> you need to setup your security using Kerberos or you will have to
>> implement
>> forms based authentication. If there is another way around it I am not
>> sure
>> what it would be.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Andrew" <Andrew@.discussions.microsoft.com> wrote in message
>> news:60F64653-3B04-4E3C-9ADA-D06A191AB9EE@.microsoft.com...
>> >I am having an authentication problem using the Report Viewer Control.
>> >The
>> > situation is that I have developed a simple ASP.NET application that
>> > will
>> > used as an interface for Report Viewing. First, I have a TreeView
>> > Control
>> > located on the default page that is being populated with the current
>> > reports
>> > that are published on the Report Server. Second, I have a aspx page
>> > called
>> > ReportViewer that holds the Report Viewer Control. Third, when a user
>> > clicks
>> > on one of the reports in the TreeView control the report is generated
>> > in
>> > an
>> > IFrame located in a table cell on the default page.
>> >
>> > My Problem is that everytime I click to generate a report I am asked
>> > for
>> > windows credentials. I am passing the full network credentials(testing
>> > environment going to use an application pool when live and pass default
>> > credentials) example System.Net.NetworkCredential("username",
>> > "password",
>> > "domain") in the load event of both aspx pages and IIS directory
>> > security
>> > is
>> > setup with anonymous access & integrated windows authentication(I have
>> > tried
>> > about everything in IIS Directory Security). I have tried about
>> > everything
>> > and for the life of me cannot get around this issue any assistance
>> > would
>> > be
>> > greatly appreciated.
>> >
>> > Thanks,
>> >
>> > --
>> > Andrew
>> > MCSA,MCDBA
>>|||Andrew wrote:
> Bruce,
> Thanks for the response and I am just using windows authentication on the
> live report server. I enabled the anonymous access in a testing environment
> just to try to get around the authentication problems I was encountering. My
> issue was that I am developing in a different domain and I am accessing the
> report server by IP address. Well when I implemented the application on the
> production domain I never changed the report server path to the actual report
> server machine name and this caused my authentication problem. Just one of
> those little slip ups that seem to cause the biggest problems. Thanks,
Andrew,
you will not be authenticated with IIS if you have anonymous access
enabled.Even if you pass credentials with
System.Net.NetworkCredential(user,pass,domain).

problem rendering a logo (external static image)

Hi,
I am having a problem rendering a logo, which is inside my asp.net
application, but first let me clear out what is exactly what I want to do and
what I have been able to do:
I am using soap access to render reports in our asp.net application, and it
works well (aside from a performance issue which I have not yet looked at).
The reports contain a logo, and it gets displayed pretty well. So basically,
everything is working.
The problem resides in that we wont be giving external access to Reporting
Services and the url of the images being displayed are pointing to reporting
services. I know there is a work around, since I could configure it as an
embedded image, and then use the render stream method of the web service. I
want to avoid doing so, since it is just a simple static image, which is
already rendered on the asp.net application. If I do so, I will have to deal
with creating temporary copies of it, and then deleting them (which I dont
know when to ... but that is another story).
So, the question is: is there a simple way to render the report in such a
way the image url the browser receive is that of the static file saved in our
asp.net application? if so, will exporting to other formats keep working
normally?I have been digging about the issue, and I dont seem to find any way to do
what I wanna do, in fact it seems that not being able to show external images
directly
from their source is by design ...
so, I am stuck with the normal routes ... I did find around in the boards,
how I can do it without ever writting the images to disk ... here, the post
before the last one
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=uzmGLaeAEHA.3316%40TK2MSFTNGP11.phx.gbl&rnum=1&prev=/groups%3Fq%3Daspx%2Bimage%2Bgroup:*.reportingsvcs%2Bauthor:teo%26hl%3Den%26lr%3D%26ie%3DUTF-8%26scoring%3Dd%26selm%3DuzmGLaeAEHA.3316%2540TK2MSFTNGP11.phx.gbl%26rnum%3D1
In normal use of images it sounds pretty great, but I am concerned in my
case this might unnecesarily degrade performance (since I would be calling
the web service per image served, even if it is an already server image to
that client ... and it is a logo that appears on every page ... and the
report is showing all pages), even more than saving a copy of the static
image per client to the server, and then figuring out when to delete them
(and the way it is showed on the thread I posted above, doesnt help either
... because it assumes the image will get served once, and mine gets served
several times ...)
I would highly appreciate any lights in this, since I am in the dark ...
a couple comments:
to be honest, altough I undestand security implications I am not sure if it
solves the problem more than making devs lifes harder (I usually dont make
this type of comment :P :(:( ) ...
(*) One can render the report trough Soap access and get the images from rs,
then one is the server, in fact as long as u use the same generated id that u
get from the render call, u can even serve the image from a different
location (and setting the streamroot appropiately) ... in fact when using it
trough Soap access u can more easily mess with the reports itself (mess as
modify :P) ... this is a pretty good reason why restricting truly external
images when rendering in HTML trough soap access doesnt make any sense, it
isnt adding any degree of security ... in my opinion ...

problem regarding relationship

this is my diagram http://aspspider.net/vhalexxs/relationship.jpg

here is the error while saving the diagram...

'rco_prodattr' table saved successfully
'rco_prodacc' table
- Unable to create relationship 'FK_rco_prodacc_rco_prodattr1'.
Introducing FOREIGN KEY constraint 'FK_rco_prodacc_rco_prodattr1' on table 'rco_prodacc' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Could not create constraint. See previous errors.

is there a way to create a constrain with cascade delete and update on rco_prodacc, or if not what will be the best possible solution for this.

thanks....

Do not enforce second relationship and maintain it by triggers on rco_prodattr.|||

does trigger also roll back when you perform a rollback transaction command in SQL

|||Rollback command rollbacks the transaction whatever it called from within.sql

Problem reaching sqlserver2000 database with asp.net 2.0 application when deploying

I have developped an ASP.NET 2.0 webb application localy on my workstation and have the MSSQL2000 database on a
server.There has been no problem accessing the database during development.

Im now finnished and put the application on a web server.

No when i try to access the application i get the following problem as it tries to reach the database:

An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the
fact that under the default settings SQL Server does not allow remote connections.
(provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

I still use the same database so remote connections are allowed and there are lot of application against that sqlserver already running.

I have anonumouse access and use a domain service account for the application and that very same account is added
as a login to the sqlserver and a domain\user in the database.

I have Forms authentication in my web.config file.

the connectionstring i use look like this:

<appSettings>
<add key="app1" value="Data Source=server01;Initial Catalog=database1; Integrated Security=SSPI;"/>
</appSettings>

Can somebody please tell me what is wrong and how to fix it. I dont change any configuration betweeen my workstation and the server in web.config

/ in pain!!

Hi Vader,

Use IP Address of your server to describe Data Source in your config settings like 192.192.xx.xx

<appSettings>
<add key="app1" value="Data Source=192.192.xx.xx;Initial Catalog=database1; Integrated Security=SSPI;"/>
</appSettings>

I hope this will work..

problem passing parameter to crystal from .net

I do this:

' Set the name of the parameter to modify.
paramField.ParameterFieldName = "P1"
' Set a value to the parameter.
paramValue.Value = strTemp
paramField.CurrentValues.Add(paramValue)
paramField.DefaultValues.Add(paramValue)
' Add the parameter to the ParameterFields collection.
paramFields.Add(paramField)
CrystalReportViewer.ParameterFieldInfo = paramFields
strReportSource = strReportSource & "Transaction For Specified Account.rpt"
CrystalReportViewer.ReportSource = strReportSource

But I get "Load reort failed".

Any suggestions...

Thanks....hi njuser,

if you get "load report failed" you do not have a problem with passing parameters, the report generator doesn't find the report!

when you call reportDocument.Load() it needs the path of the report like this: ( use the full path of the report-file )

"c:\\inetpub\\wwwroot\\reports\\myreport.rpt"

important are the "\\", the reportdocument class don't accept a single "\".

if you have problems with passing parameters you get a message like:
"current parameter-value missing"

cu,
tomcat

Problem passing parameter from VB.Net 2005 to crystal - code included

I am trying to pass a few parameters to crystal from vb.net 2005. Here is what I have, and it is not working. Everything else on the report works fine. Any ideas?

It is asking me to enter the value in a new window when the report loads..."You can provide a single value for this parameter. Enter the value you want to include........."

Dim strReportPath1 As String = "MainSummary"
Dim strConnection As String = "Data Source=localhost;Integrated Security=SSPI;" & _
"Initial Catalog=Carc2;"
Dim Connection As New SqlConnection(strConnection)
Dim strSQL As String = "My Select statement is here...too long to show in code"
Dim DA As New SqlDataAdapter(strSQL, Connection)
Dim DS As New DataSet

DA.Fill(DS, "SummaryInfo")

Dim strReportPath As String = Application.StartupPath & "\" & strReportPath1 & ".rpt"

If Not IO.File.Exists(strReportPath) Then
Throw (New Exception("Unable to locate report file:" & vbCrLf & strReportPath))
End If

If frmSummaryRpt.RadioShip.Checked = True Then
cr.SetParameterValue("ShipDate", frmSummaryRpt.txtStart.Text & " - " & frmSummaryRpt.txtEnd.Text)
ElseIf frmSummaryRpt.RadioShip.Checked = False Then
cr.SetParameterValue("ShipDate", "No ShipDate Selected")
End If

cr.Load(strReportPath)
cr.SetDataSource(DS.Tables("SummaryInfo"))This is resolved. The problem was that my if statements were before the report was loaded. I moved the area that they occured and it worked finesql

Problem Ordering XML using ADO.Net

I am converting some legacy ado code to ado.net. This function uses
'for xml explict' and returns the string representation of the xml. I
add a root node (<sales> ) around that string and load into an xml
document. It would return the following:
<sales>
<transaction>
<terms/>
<terms/>
</transaction>
<transaction>
<terms/>
<terms/>
</transaction>
</sales>
My new code looks like this, it uses the exact same SQL query, The
variable 'RootNode' is passed in and is set to 'sales':
Command = New SqlCommand
Command.CommandText = SQL & ", XMLDATA"
Command.CommandType = CommandType.Text
Command.Connection = Connection
Try
xrReader = Command.ExecuteXmlReader()
Try
Dim ds As DataSet = New DataSet
ds.ReadXml(xrReader, XmlReadMode.Fragment)
ds.DataSetName = RootNode.ToString
Dim DataDoc As XmlDataDocument = New
XmlDataDocument(ds)
strReturn = DataDoc.InnerXml
Catch ex As Exception
strReturn = "</" & RootNode & ">"
End Try
Catch ex As Exception
SaveEvent("CommonADO", "Application", ex.Message,
EventLogEntryType.Error, "EMK3Common.dll")
End Try
This function returns strReturn and looks like the following:
<sales>
<transaction/>
<transaction/>
<terms/>
<terms/>
<terms/>
<terms/>
</sales>
As you can see it does not return the same value. I'm hoping someone
can see what I've done wrong and suggest a fix that will return the
same thing as my old ado dll.
Thanks for your help.Can you post the SQL, i.e. whatever 'SQL' is in the
statement below
Command.CommandText = SQL & ", XMLDATA"
Chances are the ORDER BY clause is incorrect or
missing.|||The SQL is posted below. It returns the data in the correct format
when running it through query analyzer and has worked correctly for the
past two years. The problem seems to be in the conversion from dataset
to xml. Thanks for looking at this.
SELECT 1 AS Tag, NULL AS Parent, lc.TransactionID AS
[transaction!1!TransactionID], lc.ContractID AS
[transaction!1!ContractID],
lc.ContractBeginDate AS
[transaction!1!ContractBeginDate], lc.ContractEndDate AS
[transaction!1!ContractEndDate],
lc.TerminationDate AS
[transaction!1!TerminationDate], lc.LeaseID AS [transaction!1!LeaseID],
lc.NetEstimatedMCFsDay AS
[transaction!1!NetEstimatedMCFsDay], lc.BTUValue AS
[transaction!1!BTUValue],
lc.ServiceType AS [transaction!1!ServiceType],
lc.EstimatedVolumeSell AS [transaction!1!EstimatedVolumeSell],
lc.EstimatedVolumeSell2 AS
[transaction!1!EstimatedVolumeSell2], lc.AutomaticExtensionType AS
[transaction!1!AutomaticExtensionType],
lc.AutomaticExtensionInstr AS
[transaction!1!AutomaticExtensionInstr],
lc.ContractInstr AS
[transaction!1!ContractInstr], lc.EFP AS [transaction!1!EFP],
lc.ConfirmationNumber AS
[transaction!1!ConfirmationNumber], lc.AnalysisDate AS
[transaction!1!AnalysisDate],
lc.ConfirmationDate AS
[transaction!1!ConfirmationDate], lc.BuyerOperationsName AS
[transaction!1!BuyerOperationsName],
lc.BuyerOperationsPhone AS
[transaction!1!BuyerOperationsPhone], lc.BuyerOperationsFax AS
[transaction!1!BuyerOperationsFax],
lc.InvoiceVolumeBasedOn AS
[transaction!1!InvoiceVolumeBasedOn], c.ContractType AS
[transaction!1!ContractType],
l.LeaseType AS [transaction!1!LeaseType],
c.LocalBuyerID AS [transaction!1!LocalBuyerID], c.ProducerID AS
[transaction!1!ProducerID],
c.ContractNo AS [transaction!1!ContractNo],
lc.TransactionID AS [terms!2!TransactionID], NULL AS [terms!2!TermsID],
NULL AS [terms!2!BidValue], NULL
AS [terms!2!BidPercent], NULL AS
[terms!2!PriceBasis], NULL AS [terms!2!NYMEXTriggerRights], NULL AS
[terms!2!NYMEXTriggerParam], NULL
AS [terms!2!NYMEXTriggerDeadline], NULL AS
[terms!2!IndexType1], NULL AS [terms!2!IndexPosting1], NULL AS
[terms!2!TermsBeginDate], NULL
AS [terms!2!TermsEndDate], NULL AS
[terms!2!PercentofProceedsType], NULL AS [terms!2!PercentofProceeds],
NULL
AS [terms!2!NYMEXTriggerPrice], NULL AS
[terms!2!NYMEXTriggerOn], NULL AS [terms!2!NYMEXPriceBasis], NULL
AS [terms!2!AOCalculationType], NULL AS
[terms!2!AOPriceScheduleID1], NULL AS [terms!2!AOPriceScheduleID2],
NULL
AS [terms!2!AOPriceScheduleID3], NULL AS
[terms!2!VolumeCriteria], NULL AS [terms!2!VolumeMin], NULL AS
[terms!2!VolumeMax], NULL
AS [terms!2!VolumeUnit]
FROM gmm_lease l INNER JOIN
gmm_contracttransaction lc ON (l.SellerID =
lc.SellerID AND l.LeaseID = lc.LeaseID) INNER JOIN
gmm_contract c ON (lc.SellerID = c.SellerID AND
lc.ContractID = c.ContractID)
WHERE l.SellerID = 29 AND c.ContractType = 1 AND
(lc.ContractBeginDate <= '5/31/2005' AND (lc.TerminationDate >=
'5/1/2005' OR
lc.TerminationDate IS NULL)) AND l.LeaseID = 2066
GROUP BY lc.TransactionID, lc.ContractID, lc.ContractBeginDate,
lc.ContractEndDate, lc.TerminationDate, lc.LeaseID,
lc.NetEstimatedMCFsDay, lc.BTUValue,
lc.ServiceType, lc.EstimatedVolumeSell,
lc.EstimatedVolumeSell2, lc.AutomaticExtensionType,
lc.AutomaticExtensionInstr, lc.ContractInstr, lc.EFP,
lc.ConfirmationNumber, lc.AnalysisDate,
lc.ConfirmationDate, lc.BuyerOperationsName, lc.BuyerOperationsPhone,
lc.BuyerOperationsFax,
lc.InvoiceVolumeBasedOn, c.ContractType,
l.LeaseType, c.LocalBuyerID, c.ProducerID, c.ContractNo
UNION
SELECT 2, 1, lc.TransactionID, lc.ContractID, lc.ContractBeginDate,
lc.ContractEndDate, lc.TerminationDate, lc.LeaseID,
lc.NetEstimatedMCFsDay, lc.BTUValue,
lc.ServiceType, lc.EstimatedVolumeSell,
lc.EstimatedVolumeSell2, lc.AutomaticExtensionType,
lc.AutomaticExtensionInstr, lc.ContractInstr, lc.EFP,
lc.ConfirmationNumber, lc.AnalysisDate,
lc.ConfirmationDate, lc.BuyerOperationsName, lc.BuyerOperationsPhone,
lc.BuyerOperationsFax,
lc.InvoiceVolumeBasedOn, c.ContractType,
l.LeaseType, c.LocalBuyerID, c.ProducerID, c.ContractNo,
lct.TransactionID, lct.TermsID, lct.BidValue,
lct.BidPercent, lct.PriceBasis,
lct.NYMEXTriggerRights, lct.NYMEXTriggerParam,
lct.NYMEXTriggerDeadline, lct.IndexType1, lct.IndexPosting1,
lct.TermsBeginDate, lct.TermsEndDate,
lct.PercentofProceedsType, lct.PercentofProceeds,
lct.NYMEXTriggerPrice, lct.NYMEXTriggerOn,
lct.NYMEXPriceBasis, lct.AOCalculationType,
lct.AOPriceScheduleID1, lct.AOPriceScheduleID2, lct.AOPriceScheduleID3,
lct.VolumeCriteria,
lct.VolumeMin, lct.VolumeMax, lct.VolumeUnit
FROM gmm_lease l INNER JOIN
gmm_contracttransaction lc ON (l.SellerID =
lc.SellerID AND l.LeaseID = lc.LeaseID) INNER JOIN
gmm_contract c ON (lc.SellerID = c.SellerID AND
lc.ContractID = c.ContractID) INNER JOIN
gmm_contracttransactionterms lct ON (lc.SellerID
= lct.SellerID AND lc.ContractID = lct.ContractID AND lc.TransactionID
= lct.TransactionID)
WHERE l.SellerID = 29 AND c.ContractType = 1 AND
(lc.ContractBeginDate <= '5/31/2005' AND (lc.TerminationDate >=
'5/1/2005' OR
lc.TerminationDate IS NULL)) AND l.LeaseID = 2066
ORDER BY [transaction!1!LeaseID], [transaction!1!TransactionID],
[terms!2!TransactionID], [terms!2!TermsBeginDate]|||The SQL is posted below. It returns the data in the correct format
when running it through query analyzer and has worked correctly for the
past two years. The problem seems to be in the conversion from dataset
to xml. Thanks for looking at this.
SELECT 1 AS Tag, NULL AS Parent, lc.TransactionID AS
[transaction!1!TransactionID], lc.ContractID AS
[transaction!1!ContractID],
lc.ContractBeginDate AS
[transaction!1!ContractBeginDate], lc.ContractEndDate AS
[transaction!1!ContractEndDate],
lc.TerminationDate AS
[transaction!1!TerminationDate], lc.LeaseID AS [transaction!1!LeaseID],
lc.NetEstimatedMCFsDay AS
[transaction!1!NetEstimatedMCFsDay], lc.BTUValue AS
[transaction!1!BTUValue],
lc.ServiceType AS [transaction!1!ServiceType],
lc.EstimatedVolumeSell AS [transaction!1!EstimatedVolumeSell],
lc.EstimatedVolumeSell2 AS
[transaction!1!EstimatedVolumeSell2], lc.AutomaticExtensionType AS
[transaction!1!AutomaticExtensionType],
lc.AutomaticExtensionInstr AS
[transaction!1!AutomaticExtensionInstr],
lc.ContractInstr AS
[transaction!1!ContractInstr], lc.EFP AS [transaction!1!EFP],
lc.ConfirmationNumber AS
[transaction!1!ConfirmationNumber], lc.AnalysisDate AS
[transaction!1!AnalysisDate],
lc.ConfirmationDate AS
[transaction!1!ConfirmationDate], lc.BuyerOperationsName AS
[transaction!1!BuyerOperationsName],
lc.BuyerOperationsPhone AS
[transaction!1!BuyerOperationsPhone], lc.BuyerOperationsFax AS
[transaction!1!BuyerOperationsFax],
lc.InvoiceVolumeBasedOn AS
[transaction!1!InvoiceVolumeBasedOn], c.ContractType AS
[transaction!1!ContractType],
l.LeaseType AS [transaction!1!LeaseType],
c.LocalBuyerID AS [transaction!1!LocalBuyerID], c.ProducerID AS
[transaction!1!ProducerID],
c.ContractNo AS [transaction!1!ContractNo],
lc.TransactionID AS [terms!2!TransactionID], NULL AS [terms!2!TermsID],
NULL AS [terms!2!BidValue], NULL
AS [terms!2!BidPercent], NULL AS
[terms!2!PriceBasis], NULL AS [terms!2!NYMEXTriggerRights], NULL AS
[terms!2!NYMEXTriggerParam], NULL
AS [terms!2!NYMEXTriggerDeadline], NULL AS
[terms!2!IndexType1], NULL AS [terms!2!IndexPosting1], NULL AS
[terms!2!TermsBeginDate], NULL
AS [terms!2!TermsEndDate], NULL AS
[terms!2!PercentofProceedsType], NULL AS [terms!2!PercentofProceeds],
NULL
AS [terms!2!NYMEXTriggerPrice], NULL AS
[terms!2!NYMEXTriggerOn], NULL AS [terms!2!NYMEXPriceBasis], NULL
AS [terms!2!AOCalculationType], NULL AS
[terms!2!AOPriceScheduleID1], NULL AS [terms!2!AOPriceScheduleID2],
NULL
AS [terms!2!AOPriceScheduleID3], NULL AS
[terms!2!VolumeCriteria], NULL AS [terms!2!VolumeMin], NULL AS
[terms!2!VolumeMax], NULL
AS [terms!2!VolumeUnit]
FROM gmm_lease l INNER JOIN
gmm_contracttransaction lc ON (l.SellerID =
lc.SellerID AND l.LeaseID = lc.LeaseID) INNER JOIN
gmm_contract c ON (lc.SellerID = c.SellerID AND
lc.ContractID = c.ContractID)
WHERE l.SellerID = 29 AND c.ContractType = 1 AND
(lc.ContractBeginDate <= '5/31/2005' AND (lc.TerminationDate >=
'5/1/2005' OR
lc.TerminationDate IS NULL)) AND l.LeaseID = 2066
GROUP BY lc.TransactionID, lc.ContractID, lc.ContractBeginDate,
lc.ContractEndDate, lc.TerminationDate, lc.LeaseID,
lc.NetEstimatedMCFsDay, lc.BTUValue,
lc.ServiceType, lc.EstimatedVolumeSell,
lc.EstimatedVolumeSell2, lc.AutomaticExtensionType,
lc.AutomaticExtensionInstr, lc.ContractInstr, lc.EFP,
lc.ConfirmationNumber, lc.AnalysisDate,
lc.ConfirmationDate, lc.BuyerOperationsName, lc.BuyerOperationsPhone,
lc.BuyerOperationsFax,
lc.InvoiceVolumeBasedOn, c.ContractType,
l.LeaseType, c.LocalBuyerID, c.ProducerID, c.ContractNo
UNION
SELECT 2, 1, lc.TransactionID, lc.ContractID, lc.ContractBeginDate,
lc.ContractEndDate, lc.TerminationDate, lc.LeaseID,
lc.NetEstimatedMCFsDay, lc.BTUValue,
lc.ServiceType, lc.EstimatedVolumeSell,
lc.EstimatedVolumeSell2, lc.AutomaticExtensionType,
lc.AutomaticExtensionInstr, lc.ContractInstr, lc.EFP,
lc.ConfirmationNumber, lc.AnalysisDate,
lc.ConfirmationDate, lc.BuyerOperationsName, lc.BuyerOperationsPhone,
lc.BuyerOperationsFax,
lc.InvoiceVolumeBasedOn, c.ContractType,
l.LeaseType, c.LocalBuyerID, c.ProducerID, c.ContractNo,
lct.TransactionID, lct.TermsID, lct.BidValue,
lct.BidPercent, lct.PriceBasis,
lct.NYMEXTriggerRights, lct.NYMEXTriggerParam,
lct.NYMEXTriggerDeadline, lct.IndexType1, lct.IndexPosting1,
lct.TermsBeginDate, lct.TermsEndDate,
lct.PercentofProceedsType, lct.PercentofProceeds,
lct.NYMEXTriggerPrice, lct.NYMEXTriggerOn,
lct.NYMEXPriceBasis, lct.AOCalculationType,
lct.AOPriceScheduleID1, lct.AOPriceScheduleID2, lct.AOPriceScheduleID3,
lct.VolumeCriteria,
lct.VolumeMin, lct.VolumeMax, lct.VolumeUnit
FROM gmm_lease l INNER JOIN
gmm_contracttransaction lc ON (l.SellerID =
lc.SellerID AND l.LeaseID = lc.LeaseID) INNER JOIN
gmm_contract c ON (lc.SellerID = c.SellerID AND
lc.ContractID = c.ContractID) INNER JOIN
gmm_contracttransactionterms lct ON (lc.SellerID
= lct.SellerID AND lc.ContractID = lct.ContractID AND lc.TransactionID
= lct.TransactionID)
WHERE l.SellerID = 29 AND c.ContractType = 1 AND
(lc.ContractBeginDate <= '5/31/2005' AND (lc.TerminationDate >=
'5/1/2005' OR
lc.TerminationDate IS NULL)) AND l.LeaseID = 2066
ORDER BY [transaction!1!LeaseID], [transaction!1!TransactionID],
[terms!2!TransactionID], [terms!2!TermsBeginDate] FOR XML EXPLICIT|||Your SQL looks okay. Suggest you post to an ADO.NET newsgroup.|||I've had another look at this and think that
problem is that the relationship between transaction
and terms isn't coming through from the query. One
solution is to add it yourself by supplying a new
DataRelation object
After
ds.DataSetName = RootNode.ToString
add this (this is C#, but should translate to VB.NET easily)
DataColumn parentCol =
ds.Tables["transaction"].Columns["TransactionID"];
DataColumn childCol = ds.Tables["terms"].Columns["TransactionID"];
DataRelation TransactionTerms = new DataRelation("TransactionTerms",
parentCol, childCol);
ds.Relations.Add(TransactionTerms);
TransactionTerms.Nested = true;

Problem of vale setting

I have this .NET (VB) code:

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Dim myConnection As New SqlConnection(ConnectionString)
myConnection.Open()

Dim CommandText As String = "SELECT firstName FROM students Where Students.ID='" & IDinput & "' "

Dim myCommand As New SqlCommand(CommandText, myConnection)

'****this the problem, somthing is mising here******
Dim name As String = myCommand.ExecuteReader(CommandBehavior.CloseConnection)

end sub

The SQL qurey return only one record each time (only one student name with specific ID number)

I want that variable "name" will get the value of the "firstName" filed.

How to do it??Use ExecuteScalar instead of ExecuteReader. ExecuteScalar is designed for situations like this where you want the query to return a single value.

problem of uploading asp.net website on web server

i have developed a website using asp.net, c# withSQL SERVER EXPRESS EDITION 2005. The database is being used both for retreival and updation purpose. the website is working accordingly when i m running it on my system, ie., data is getting retreived from the database and it is also getting updated on button click. But when i m uloading my site on the httpdocs folder of web server, connectivity with database is failing miserably, ie neither getting retreived from the database nor getting updated. The error message displayed by the web server is given underneath. i have used Grid view for displaying data from the database and Details view for updating the database.

i have used window authentication for connecting with the database.

Please help me with finding out a solution for it. give me proper explanation and i need to do

Server Error in '/' Application.

---

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

smartymca:

this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections

Have you checked to see if this is the case or not? Simply pasting this error into google should show you how to do this.

|||

Hello smartymca,

I have recently uploaded my first ASP.NET website to the web, and I had exactly the same problem and error message. I found there were two reasons for this:

1. My hosting company require that my SQL Server database be located on a different server to the rest of my files! So check with your provider to see if your database needs to be in a specific place. I had to remove my database from my App_Data folder and recreate it on the correct server. I also had to change the connection string in my application to match the database's new location.

2. Now that my database was on a different server, the login system didn't work! This was because the default connection string for the Membership provider was no longer valid. There is however a very good video on this website that guides you through setting this up. It's the"ASP.NET How Do I? #13" video.

I hope this helps. If you think your problem is caused by the same thing then write back and maybe I can help.

|||

You will probably want to you SQL authentication because chances are, the SQL Server and the application server are on two different servers.

Who is your host?

|||

my web services provider is PROWEBS (prowebs.co.in).

|||

thanks for your reply. Yes i think my problem is same as your's, so please guide me.

My provider is (prowebs.co.in).

Tell me the steps you followed.

|||

smarty,

I've had a look at your provider's website and it appears to be exactly the same setup as my provider. You have to place your SQL Server database on a specific server for it to work - in your case, it is the "3Essentials MSSQL" server.

Unfortunately you cannot simply copy your existing database to the server (I had the same problem). A new empty database must be created on the 3Essentials server. Then, there are two things you can do.

If your database is still quite small and simple, you could recreate the tables yourself by connecting to your new database through MS Visual Studio. This is what I did.

If your database already has a lot of data in it and would take too long to recreate, you can transfer the data from your old database to your new database. This requires making a backup of your database using MS SQL Enterprise Manager (supposedly free to download from Microsoft, although when I tried, the download wasn't there) and then sending this backup to your provider so they can upload it for you. Detailed instructions can be found in your provider's FAQ.

smarty - are you using MS Visual Studio? If so, and you would like to try the first option, let me know and perhaps I could walk you through it. Send me a private message.

Patrick

|||

Patrick,

thnx for ur prompt reply..........

yes i m using MS Visual Studio and m also interested in using the first option............So, plz guide me through...........

|||

Ok,

1. Login to your user account at Prowebs. Find the database management section, and create a new MS SQL 2005 database. You'll give it a name, and a username and password.

2. Find out the server address for your new database. I guess it will be something like sql.3essentials.com. Also try and find out the connection string - my hosting company has a neat tool that tells you what it is. If yours doesn't, don't worry - you can find out in Visual Studio later.

3. Open up Visual Studio 2005. Click on the View menu and choose Server Explorer. Click on 'Connect to Database'.

4. In the new window, select the following options:

Datasource: Microsoft SQL Server

|||

i didn't get the server name. i put my dbi.3Essentials.com as server name but it still showing the same error which i have already given u.

|||

smarty,

Did you go through all the steps that I gave you? If not, how far did you get?

Problem of Permissions for Linked Servers in .net page

I searching using Keywords in MS Word and pdf documents for which i
have used the Index Server and linked with SQL Server but when i am
running the stored procedure thro my webpage i'm getting these errors.
And when i run this code in Query Analyzer its giving expeceted
results. I dont know where the problem is can anyone help please . I'm
really badly stuck. Thanks in Advacne
User does not have permission to perform this action. Line 3: Incorrect
syntax near '@.searchstring'. Invalid object name 'FileSearchResults'.
And my stored procedure is given below
CREATE PROCEDURE SelectIndexServerCVpaths
(
@.searchstring varchar(100)
)
AS
Exec sp_addlinkedserver FileSystem,
'Index Server',
'MSIDXS',
'Web',
'c:\inetpub\wwwroot\sap-resources\Uploads'
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'FileSearchResults')
DROP VIEW FileSearchResults
EXEC ('CREATE VIEW FileSearchResults AS SELECT * FROM
OPENQUERY(FileSystem,''SELECT Directory, FileName,
DocAuthor, Size, Create, Write, Path FROM
SCOPE() WHERE FREETEXT(''@.searchstring'')'')')
SELECT * FROM FileSearchResults F, CVdetails C WHERE C.CV_Path = F.PATH
AND C.DefaultID=1
GONext time print out the strings you build before executing them.
See if this helps:
exec ('CREATE VIEW FileSearchResults AS SELECT * FROM
OPENQUERY(FileSystem,''SELECT Directory, FileName,
DocAuthor, Size, Create, Write, Path FROM
SCOPE() WHERE FREETEXT(' + @.searchstring + ')'')')
ML
http://milambda.blogspot.com/|||Thanks for your reply
its not working out.
I modified the stored procedure as shown below
its giving no errors but no results but if i execute the same without
the stored i'm gettting the expected results
Thanks in Advance
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'FileSearchResults')
DROP VIEW FileSearchResults
EXEC ('CREATE VIEW FileSearchResults AS SELECT * FROM
OPENQUERY(FileSystem,''SELECT Directory, FileName,
DocAuthor, Size, Create, Write, Path FROM
SCOPE('''' "c:\inetpub\wwwroot\sap-resources\Uploads" '''') WHERE
FREETEXT(''''@.searchstring'''')'')')
SELECT * FROM FileSearchResults F, CVdetails C WHERE C.CV_Path = F.PATH
AND C.DefaultID=1

Problem of Parent-Child package with ASP.Net

I have an ASP.NET application that calls a SSIS package. The SSIS package internally calls some other child packages. I observed that sometimes some child pacakges are not even called by the parent. and the behaviour is very indeterminate. sometimes they work fine. sometimes they don't. There is no clue available for this behaviour when the pacakges are not executed. (One general observation is that the memory consumption is very high. but that is the case always.)

I have enabled logging on all child packages. The log is not updated at all when the child packages failed to execute. i.e. the package execution does not start.

Could somebody explain why this is happening? any suggestions/ similar experiences?

Regards

Saurabh

One more observation: if i run the same parent package as a stand-alone. (i.e. not through the asp.net application) it always executes fine. it calls all the child packages very well always. what is different in the asp.net context?

problem of connection with sqlserver2005

Hello,

I installed sqlserver2005_express on my PC (localhost) and I try to execute a sql query with ASP.NET.

Here's my page :
Dim mySqlConnection as SqlConnection = new SqlConnection("server=JOE10155\SQLEXPRESS;Trusted_Connection=yes;UID=sa;PWD=xxxx;database=xxxx")
Dim mySqlDataAdapter as SqlDataAdapter = new SqlDataAdapter("SELECT * FROM Entreprise WHERE (EntrepriseNr =999)", mySqlConnection)
Dim myDataSet as DataSet = new DataSet()
mySqlDataAdapter.Fill(myDataSet,"Entreprise")

The problem is :
System.Data.SqlClient.SqlException: Autorisation SELECT refusée sur l'objet 'Entreprise', base de données 'xxxx', schéma dbo'.

if someone have an idea?

Thanks

PS : I use Microsoft SQL Server Management Studio Express.

Hi,

that is French? :-)

I'm not good with it, but sounds as if the user who you connect as (give the credentiuals into connection string) wouldn't have permissions to SELECT fromEntreprise table

Problem Moving and Restoring a Large database

We have a 48 gb production database (SQL 2000 sp4 and Windows 2003) and I
have requested the Net Admin to take the last full backup of this database,
move it to another server and restore it on that server.
The problem we seem to be having is taking the backup (which is on a NAS)
and copying it to another server, the job takes forever, slowing things
down so the process ends up getting killed. The Net admin is now (I should
say for the past several days) zipping up the .bak in chunks of about 100
mb, but even this process never seems to end.
The whole process has been started and stopped, for one reason or another,
several times in the past 2 weeks. I have never had to do this myself, but
it just seems to me that this process should not be so time consuming or
painful.
Any one have any ideas on what we might be doing wrong? Or a better way to
do it?
Any ideas appreciated.
TIA,
Nancy Lytle"Nancy Lytle" <lytlen@.mdon-line.com> wrote in message
news:%23ByrI%23k2FHA.476@.TK2MSFTNGP15.phx.gbl...
> We have a 48 gb production database (SQL 2000 sp4 and Windows 2003) and I
> have requested the Net Admin to take the last full backup of this
> database, move it to another server and restore it on that server.
> The problem we seem to be having is taking the backup (which is on a NAS)
> and copying it to another server, the job takes forever, slowing things
> down so the process ends up getting killed. The Net admin is now (I
> should say for the past several days) zipping up the .bak in chunks of
> about 100 mb, but even this process never seems to end.
> The whole process has been started and stopped, for one reason or another,
> several times in the past 2 weeks. I have never had to do this myself,
> but it just seems to me that this process should not be so time consuming
> or painful.
> Any one have any ideas on what we might be doing wrong?
Sounds like your NAS solution is insufficient. If you can't move your
backup file to a new server in a reasonable amount of time, how would you
ever recover in case of a disaster?
David|||That is a good question, one I have asked also. We currently have a 2 node
active passive cluster (I know very little about the network side of things,
and I think the net admin likes it that way).
The NAS size is 450 gb for "everything", all the databases, etc. It isn't a
fibre channel but he says the throughput (?) is comparable, so that
shouldn't be the bottleneck.
I forgot to mention that the production server and NAS are in one domain,
(on the West coast) to a server on another domain (we have several domains
because the production domain is W2k3 and the others are W2k) at the office
here on the East Coast.
All this was done shortly before I came on board, we have no DBA, I am
serving as SQL DBA/Developer as well as the Access developer.
Any ideas on a better solution?
Nancy
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:Oo7pxDl2FHA.1292@.TK2MSFTNGP12.phx.gbl...
>
> Sounds like your NAS solution is insufficient. If you can't move your
> backup file to a new server in a reasonable amount of time, how would you
> ever recover in case of a disaster?
>
> David
>|||One or two questions you may not be able to answer:
Are both the source and target servers using the same NAS? If so, is there
an NAS utility that can duplicate the files? (Split a mirror and re-attach
the mirror to the other server, for example--a technique we used to move a
multi-terabyte db.)
Is your problem in the backup or the transfer? If it is the transfer, how
are you doing the transfer, ie, are you on a private,dedicated link or using
the Internet (since your source and destination are 3K miles apart.)
What is unacceptable timing? For a 50GB database, I would not be surprised
at a 36-48 hour transfer time (500KBytes per second=1Gbyte per hour with
handshaking, etc).
If you have a high capacity tape drive, it might be faster to backit up to
tape and then overnight the tape to the destination.
Joseph R.P. Maloney, CSP,CCP,CDP
"Nancy Lytle" wrote:
> That is a good question, one I have asked also. We currently have a 2 node
> active passive cluster (I know very little about the network side of things,
> and I think the net admin likes it that way).
> The NAS size is 450 gb for "everything", all the databases, etc. It isn't a
> fibre channel but he says the throughput (?) is comparable, so that
> shouldn't be the bottleneck.
> I forgot to mention that the production server and NAS are in one domain,
> (on the West coast) to a server on another domain (we have several domains
> because the production domain is W2k3 and the others are W2k) at the office
> here on the East Coast.
> All this was done shortly before I came on board, we have no DBA, I am
> serving as SQL DBA/Developer as well as the Access developer.
> Any ideas on a better solution?
> Nancy
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:Oo7pxDl2FHA.1292@.TK2MSFTNGP12.phx.gbl...
> >
> >
> > Sounds like your NAS solution is insufficient. If you can't move your
> > backup file to a new server in a reasonable amount of time, how would you
> > ever recover in case of a disaster?
> >
> >
> > David
> >
>
>|||The source is on a NAS, the destination is not.
The backup is fine, it is the transfer.
As to the time involved, I'm guessing it was longer than 36-48 hours because
the method he is using now is to compress the .bak file using winrar and he
expects that to take 13 hours total to compress the database and chop it up
into 100MB chunks using winrar. He has paused this process until off hours
since winrar is very cpu intensive. He expects this to be completely
archived in 2 days. Then he is going to transfer (so I am guessing the
winrar is being done on the NAS) the files via cable modem (since it is
about 7 times faster than the office T1 and will not interfere with our
email or phones during business hours). He is then going to burn the file
to a CD and bring it in for me.
As I mentioned earlier, there's just something about this that doesn't seem
quite right. I requested this be done over 2 weeks ago, and the request has
still not been completed.
Thanks for you insight.
Nancy
"jrpm" <jrpm@.discussions.microsoft.com> wrote in message
news:1EB18358-280F-4DCD-B513-64685666F6DE@.microsoft.com...
> One or two questions you may not be able to answer:
> Are both the source and target servers using the same NAS? If so, is
> there
> an NAS utility that can duplicate the files? (Split a mirror and re-attach
> the mirror to the other server, for example--a technique we used to move a
> multi-terabyte db.)
> Is your problem in the backup or the transfer? If it is the transfer, how
> are you doing the transfer, ie, are you on a private,dedicated link or
> using
> the Internet (since your source and destination are 3K miles apart.)
> What is unacceptable timing? For a 50GB database, I would not be
> surprised
> at a 36-48 hour transfer time (500KBytes per second=1Gbyte per hour with
> handshaking, etc).
> If you have a high capacity tape drive, it might be faster to backit up to
> tape and then overnight the tape to the destination.
>
> --
> Joseph R.P. Maloney, CSP,CCP,CDP
>
> "Nancy Lytle" wrote:
>> That is a good question, one I have asked also. We currently have a 2
>> node
>> active passive cluster (I know very little about the network side of
>> things,
>> and I think the net admin likes it that way).
>> The NAS size is 450 gb for "everything", all the databases, etc. It
>> isn't a
>> fibre channel but he says the throughput (?) is comparable, so that
>> shouldn't be the bottleneck.
>> I forgot to mention that the production server and NAS are in one domain,
>> (on the West coast) to a server on another domain (we have several
>> domains
>> because the production domain is W2k3 and the others are W2k) at the
>> office
>> here on the East Coast.
>> All this was done shortly before I came on board, we have no DBA, I am
>> serving as SQL DBA/Developer as well as the Access developer.
>> Any ideas on a better solution?
>> Nancy
>> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
>> message news:Oo7pxDl2FHA.1292@.TK2MSFTNGP12.phx.gbl...
>> >
>> >
>> > Sounds like your NAS solution is insufficient. If you can't move your
>> > backup file to a new server in a reasonable amount of time, how would
>> > you
>> > ever recover in case of a disaster?
>> >
>> >
>> > David
>> >
>>|||Why don't you try moving the file using FTP rather than copy. If you have a
destination disk that can accomodate the file size then you can do the
following.
1. Install FTP (IIS) on the destination server if not already done. You can
set this to allow anonymouse connections.
2. Backup the database to a flat file using TRANSACT-SQL
BACKUP DATABASE (dbname) TO DISK=N'C:\destination\db.bak' with init
From a client computer initiate an FTP session
such as
C:\> open servername.domain.com
(Username) anonymous
(Password) admin@.domain.com
cd to the directory on the destination server where you want to put the file.
put (filename) where filename is the name of the database file.
FTP is much faster than a standard copy. I have a database that is 11GB and
FTP takes about 20 minutes to move the file. So you can cut the time down to
80 minutes or so, depending on the system.
Then you can restore the file to the new destination and use the move
command to tell the system where to move the file. You will need to
understand the RESTORE HEADERONLY command and the RESTORE COMMAND.
Hope this helps.
--
Thanks,
David
"Nancy Lytle" wrote:
> The source is on a NAS, the destination is not.
> The backup is fine, it is the transfer.
> As to the time involved, I'm guessing it was longer than 36-48 hours because
> the method he is using now is to compress the .bak file using winrar and he
> expects that to take 13 hours total to compress the database and chop it up
> into 100MB chunks using winrar. He has paused this process until off hours
> since winrar is very cpu intensive. He expects this to be completely
> archived in 2 days. Then he is going to transfer (so I am guessing the
> winrar is being done on the NAS) the files via cable modem (since it is
> about 7 times faster than the office T1 and will not interfere with our
> email or phones during business hours). He is then going to burn the file
> to a CD and bring it in for me.
> As I mentioned earlier, there's just something about this that doesn't seem
> quite right. I requested this be done over 2 weeks ago, and the request has
> still not been completed.
> Thanks for you insight.
> Nancy
> "jrpm" <jrpm@.discussions.microsoft.com> wrote in message
> news:1EB18358-280F-4DCD-B513-64685666F6DE@.microsoft.com...
> > One or two questions you may not be able to answer:
> > Are both the source and target servers using the same NAS? If so, is
> > there
> > an NAS utility that can duplicate the files? (Split a mirror and re-attach
> > the mirror to the other server, for example--a technique we used to move a
> > multi-terabyte db.)
> >
> > Is your problem in the backup or the transfer? If it is the transfer, how
> > are you doing the transfer, ie, are you on a private,dedicated link or
> > using
> > the Internet (since your source and destination are 3K miles apart.)
> >
> > What is unacceptable timing? For a 50GB database, I would not be
> > surprised
> > at a 36-48 hour transfer time (500KBytes per second=1Gbyte per hour with
> > handshaking, etc).
> >
> > If you have a high capacity tape drive, it might be faster to backit up to
> > tape and then overnight the tape to the destination.
> >
> >
> > --
> > Joseph R.P. Maloney, CSP,CCP,CDP
> >
> >
> > "Nancy Lytle" wrote:
> >
> >> That is a good question, one I have asked also. We currently have a 2
> >> node
> >> active passive cluster (I know very little about the network side of
> >> things,
> >> and I think the net admin likes it that way).
> >> The NAS size is 450 gb for "everything", all the databases, etc. It
> >> isn't a
> >> fibre channel but he says the throughput (?) is comparable, so that
> >> shouldn't be the bottleneck.
> >> I forgot to mention that the production server and NAS are in one domain,
> >> (on the West coast) to a server on another domain (we have several
> >> domains
> >> because the production domain is W2k3 and the others are W2k) at the
> >> office
> >> here on the East Coast.
> >> All this was done shortly before I came on board, we have no DBA, I am
> >> serving as SQL DBA/Developer as well as the Access developer.
> >>
> >> Any ideas on a better solution?
> >> Nancy
> >> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> >> message news:Oo7pxDl2FHA.1292@.TK2MSFTNGP12.phx.gbl...
> >> >
> >> >
> >> > Sounds like your NAS solution is insufficient. If you can't move your
> >> > backup file to a new server in a reasonable amount of time, how would
> >> > you
> >> > ever recover in case of a disaster?
> >> >
> >> >
> >> > David
> >> >
> >>
> >>
> >>
>
>|||I would also try downloading a trial version of SQL Litespeed from
imceda.com, and installing it on both your originating server &
destination servers. Their compression rates are incredible. You can
probably get your 48gb down to 8-10gb. Dump it locally, then move.|||Sorry,
Mis-typed.
C:\> ftp
ftp> open servername.domain.com
username
password
cd (destination directory)
put filename
ftp>bye
C:\> exit
--
Thanks,
David
"david" wrote:
> Why don't you try moving the file using FTP rather than copy. If you have a
> destination disk that can accomodate the file size then you can do the
> following.
> 1. Install FTP (IIS) on the destination server if not already done. You can
> set this to allow anonymouse connections.
> 2. Backup the database to a flat file using TRANSACT-SQL
> BACKUP DATABASE (dbname) TO DISK=N'C:\destination\db.bak' with init
> From a client computer initiate an FTP session
> such as
> C:\> open servername.domain.com
> (Username) anonymous
> (Password) admin@.domain.com
> cd to the directory on the destination server where you want to put the file.
> put (filename) where filename is the name of the database file.
> FTP is much faster than a standard copy. I have a database that is 11GB and
> FTP takes about 20 minutes to move the file. So you can cut the time down to
> 80 minutes or so, depending on the system.
> Then you can restore the file to the new destination and use the move
> command to tell the system where to move the file. You will need to
> understand the RESTORE HEADERONLY command and the RESTORE COMMAND.
> Hope this helps.
> --
> Thanks,
> David
>
> "Nancy Lytle" wrote:
> > The source is on a NAS, the destination is not.
> > The backup is fine, it is the transfer.
> > As to the time involved, I'm guessing it was longer than 36-48 hours because
> > the method he is using now is to compress the .bak file using winrar and he
> > expects that to take 13 hours total to compress the database and chop it up
> > into 100MB chunks using winrar. He has paused this process until off hours
> > since winrar is very cpu intensive. He expects this to be completely
> > archived in 2 days. Then he is going to transfer (so I am guessing the
> > winrar is being done on the NAS) the files via cable modem (since it is
> > about 7 times faster than the office T1 and will not interfere with our
> > email or phones during business hours). He is then going to burn the file
> > to a CD and bring it in for me.
> >
> > As I mentioned earlier, there's just something about this that doesn't seem
> > quite right. I requested this be done over 2 weeks ago, and the request has
> > still not been completed.
> >
> > Thanks for you insight.
> >
> > Nancy
> > "jrpm" <jrpm@.discussions.microsoft.com> wrote in message
> > news:1EB18358-280F-4DCD-B513-64685666F6DE@.microsoft.com...
> > > One or two questions you may not be able to answer:
> > > Are both the source and target servers using the same NAS? If so, is
> > > there
> > > an NAS utility that can duplicate the files? (Split a mirror and re-attach
> > > the mirror to the other server, for example--a technique we used to move a
> > > multi-terabyte db.)
> > >
> > > Is your problem in the backup or the transfer? If it is the transfer, how
> > > are you doing the transfer, ie, are you on a private,dedicated link or
> > > using
> > > the Internet (since your source and destination are 3K miles apart.)
> > >
> > > What is unacceptable timing? For a 50GB database, I would not be
> > > surprised
> > > at a 36-48 hour transfer time (500KBytes per second=1Gbyte per hour with
> > > handshaking, etc).
> > >
> > > If you have a high capacity tape drive, it might be faster to backit up to
> > > tape and then overnight the tape to the destination.
> > >
> > >
> > > --
> > > Joseph R.P. Maloney, CSP,CCP,CDP
> > >
> > >
> > > "Nancy Lytle" wrote:
> > >
> > >> That is a good question, one I have asked also. We currently have a 2
> > >> node
> > >> active passive cluster (I know very little about the network side of
> > >> things,
> > >> and I think the net admin likes it that way).
> > >> The NAS size is 450 gb for "everything", all the databases, etc. It
> > >> isn't a
> > >> fibre channel but he says the throughput (?) is comparable, so that
> > >> shouldn't be the bottleneck.
> > >> I forgot to mention that the production server and NAS are in one domain,
> > >> (on the West coast) to a server on another domain (we have several
> > >> domains
> > >> because the production domain is W2k3 and the others are W2k) at the
> > >> office
> > >> here on the East Coast.
> > >> All this was done shortly before I came on board, we have no DBA, I am
> > >> serving as SQL DBA/Developer as well as the Access developer.
> > >>
> > >> Any ideas on a better solution?
> > >> Nancy
> > >> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> > >> message news:Oo7pxDl2FHA.1292@.TK2MSFTNGP12.phx.gbl...
> > >> >
> > >> >
> > >> > Sounds like your NAS solution is insufficient. If you can't move your
> > >> > backup file to a new server in a reasonable amount of time, how would
> > >> > you
> > >> > ever recover in case of a disaster?
> > >> >
> > >> >
> > >> > David
> > >> >
> > >>
> > >>
> > >>
> >
> >
> >

Problem Moving and Restoring a Large database

We have a 48 gb production database (SQL 2000 sp4 and Windows 2003) and I
have requested the Net Admin to take the last full backup of this database,
move it to another server and restore it on that server.
The problem we seem to be having is taking the backup (which is on a NAS)
and copying it to another server, the job takes forever, slowing things
down so the process ends up getting killed. The Net admin is now (I should
say for the past several days) zipping up the .bak in chunks of about 100
mb, but even this process never seems to end.
The whole process has been started and stopped, for one reason or another,
several times in the past 2 weeks. I have never had to do this myself, but
it just seems to me that this process should not be so time consuming or
painful.
Any one have any ideas on what we might be doing wrong? Or a better way to
do it?
Any ideas appreciated.
TIA,
Nancy Lytle
"Nancy Lytle" <lytlen@.mdon-line.com> wrote in message
news:%23ByrI%23k2FHA.476@.TK2MSFTNGP15.phx.gbl...
> We have a 48 gb production database (SQL 2000 sp4 and Windows 2003) and I
> have requested the Net Admin to take the last full backup of this
> database, move it to another server and restore it on that server.
> The problem we seem to be having is taking the backup (which is on a NAS)
> and copying it to another server, the job takes forever, slowing things
> down so the process ends up getting killed. The Net admin is now (I
> should say for the past several days) zipping up the .bak in chunks of
> about 100 mb, but even this process never seems to end.
> The whole process has been started and stopped, for one reason or another,
> several times in the past 2 weeks. I have never had to do this myself,
> but it just seems to me that this process should not be so time consuming
> or painful.
> Any one have any ideas on what we might be doing wrong?
Sounds like your NAS solution is insufficient. If you can't move your
backup file to a new server in a reasonable amount of time, how would you
ever recover in case of a disaster?
David
|||That is a good question, one I have asked also. We currently have a 2 node
active passive cluster (I know very little about the network side of things,
and I think the net admin likes it that way).
The NAS size is 450 gb for "everything", all the databases, etc. It isn't a
fibre channel but he says the throughput (?) is comparable, so that
shouldn't be the bottleneck.
I forgot to mention that the production server and NAS are in one domain,
(on the West coast) to a server on another domain (we have several domains
because the production domain is W2k3 and the others are W2k) at the office
here on the East Coast.
All this was done shortly before I came on board, we have no DBA, I am
serving as SQL DBA/Developer as well as the Access developer.
Any ideas on a better solution?
Nancy
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:Oo7pxDl2FHA.1292@.TK2MSFTNGP12.phx.gbl...
>
> Sounds like your NAS solution is insufficient. If you can't move your
> backup file to a new server in a reasonable amount of time, how would you
> ever recover in case of a disaster?
>
> David
>
|||One or two questions you may not be able to answer:
Are both the source and target servers using the same NAS? If so, is there
an NAS utility that can duplicate the files? (Split a mirror and re-attach
the mirror to the other server, for example--a technique we used to move a
multi-terabyte db.)
Is your problem in the backup or the transfer? If it is the transfer, how
are you doing the transfer, ie, are you on a private,dedicated link or using
the Internet (since your source and destination are 3K miles apart.)
What is unacceptable timing? For a 50GB database, I would not be surprised
at a 36-48 hour transfer time (500KBytes per second=1Gbyte per hour with
handshaking, etc).
If you have a high capacity tape drive, it might be faster to backit up to
tape and then overnight the tape to the destination.
Joseph R.P. Maloney, CSP,CCP,CDP
"Nancy Lytle" wrote:

> That is a good question, one I have asked also. We currently have a 2 node
> active passive cluster (I know very little about the network side of things,
> and I think the net admin likes it that way).
> The NAS size is 450 gb for "everything", all the databases, etc. It isn't a
> fibre channel but he says the throughput (?) is comparable, so that
> shouldn't be the bottleneck.
> I forgot to mention that the production server and NAS are in one domain,
> (on the West coast) to a server on another domain (we have several domains
> because the production domain is W2k3 and the others are W2k) at the office
> here on the East Coast.
> All this was done shortly before I came on board, we have no DBA, I am
> serving as SQL DBA/Developer as well as the Access developer.
> Any ideas on a better solution?
> Nancy
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:Oo7pxDl2FHA.1292@.TK2MSFTNGP12.phx.gbl...
>
>
|||The source is on a NAS, the destination is not.
The backup is fine, it is the transfer.
As to the time involved, I'm guessing it was longer than 36-48 hours because
the method he is using now is to compress the .bak file using winrar and he
expects that to take 13 hours total to compress the database and chop it up
into 100MB chunks using winrar. He has paused this process until off hours
since winrar is very cpu intensive. He expects this to be completely
archived in 2 days. Then he is going to transfer (so I am guessing the
winrar is being done on the NAS) the files via cable modem (since it is
about 7 times faster than the office T1 and will not interfere with our
email or phones during business hours). He is then going to burn the file
to a CD and bring it in for me.
As I mentioned earlier, there's just something about this that doesn't seem
quite right. I requested this be done over 2 weeks ago, and the request has
still not been completed.
Thanks for you insight.
Nancy
"jrpm" <jrpm@.discussions.microsoft.com> wrote in message
news:1EB18358-280F-4DCD-B513-64685666F6DE@.microsoft.com...[vbcol=seagreen]
> One or two questions you may not be able to answer:
> Are both the source and target servers using the same NAS? If so, is
> there
> an NAS utility that can duplicate the files? (Split a mirror and re-attach
> the mirror to the other server, for example--a technique we used to move a
> multi-terabyte db.)
> Is your problem in the backup or the transfer? If it is the transfer, how
> are you doing the transfer, ie, are you on a private,dedicated link or
> using
> the Internet (since your source and destination are 3K miles apart.)
> What is unacceptable timing? For a 50GB database, I would not be
> surprised
> at a 36-48 hour transfer time (500KBytes per second=1Gbyte per hour with
> handshaking, etc).
> If you have a high capacity tape drive, it might be faster to backit up to
> tape and then overnight the tape to the destination.
>
> --
> Joseph R.P. Maloney, CSP,CCP,CDP
>
> "Nancy Lytle" wrote:
|||Why don't you try moving the file using FTP rather than copy. If you have a
destination disk that can accomodate the file size then you can do the
following.
1. Install FTP (IIS) on the destination server if not already done. You can
set this to allow anonymouse connections.
2. Backup the database to a flat file using TRANSACT-SQL
BACKUP DATABASE (dbname) TO DISK=N'C:\destination\db.bak' with init
From a client computer initiate an FTP session
such as
C:\> open servername.domain.com
(Username) anonymous
(Password) admin@.domain.com
cd to the directory on the destination server where you want to put the file.
put (filename) where filename is the name of the database file.
FTP is much faster than a standard copy. I have a database that is 11GB and
FTP takes about 20 minutes to move the file. So you can cut the time down to
80 minutes or so, depending on the system.
Then you can restore the file to the new destination and use the move
command to tell the system where to move the file. You will need to
understand the RESTORE HEADERONLY command and the RESTORE COMMAND.
Hope this helps.
Thanks,
David
"Nancy Lytle" wrote:

> The source is on a NAS, the destination is not.
> The backup is fine, it is the transfer.
> As to the time involved, I'm guessing it was longer than 36-48 hours because
> the method he is using now is to compress the .bak file using winrar and he
> expects that to take 13 hours total to compress the database and chop it up
> into 100MB chunks using winrar. He has paused this process until off hours
> since winrar is very cpu intensive. He expects this to be completely
> archived in 2 days. Then he is going to transfer (so I am guessing the
> winrar is being done on the NAS) the files via cable modem (since it is
> about 7 times faster than the office T1 and will not interfere with our
> email or phones during business hours). He is then going to burn the file
> to a CD and bring it in for me.
> As I mentioned earlier, there's just something about this that doesn't seem
> quite right. I requested this be done over 2 weeks ago, and the request has
> still not been completed.
> Thanks for you insight.
> Nancy
> "jrpm" <jrpm@.discussions.microsoft.com> wrote in message
> news:1EB18358-280F-4DCD-B513-64685666F6DE@.microsoft.com...
>
>
|||I would also try downloading a trial version of SQL Litespeed from
imceda.com, and installing it on both your originating server &
destination servers. Their compression rates are incredible. You can
probably get your 48gb down to 8-10gb. Dump it locally, then move.
|||Sorry,
Mis-typed.
C:\> ftp
ftp> open servername.domain.com
username
password
cd (destination directory)
put filename
ftp>bye
C:\> exit
Thanks,
David
"david" wrote:
[vbcol=seagreen]
> Why don't you try moving the file using FTP rather than copy. If you have a
> destination disk that can accomodate the file size then you can do the
> following.
> 1. Install FTP (IIS) on the destination server if not already done. You can
> set this to allow anonymouse connections.
> 2. Backup the database to a flat file using TRANSACT-SQL
> BACKUP DATABASE (dbname) TO DISK=N'C:\destination\db.bak' with init
> From a client computer initiate an FTP session
> such as
> C:\> open servername.domain.com
> (Username) anonymous
> (Password) admin@.domain.com
> cd to the directory on the destination server where you want to put the file.
> put (filename) where filename is the name of the database file.
> FTP is much faster than a standard copy. I have a database that is 11GB and
> FTP takes about 20 minutes to move the file. So you can cut the time down to
> 80 minutes or so, depending on the system.
> Then you can restore the file to the new destination and use the move
> command to tell the system where to move the file. You will need to
> understand the RESTORE HEADERONLY command and the RESTORE COMMAND.
> Hope this helps.
> --
> Thanks,
> David
>
> "Nancy Lytle" wrote:

Problem Moving and Restoring a Large database

We have a 48 gb production database (SQL 2000 sp4 and Windows 2003) and I
have requested the Net Admin to take the last full backup of this database,
move it to another server and restore it on that server.
The problem we seem to be having is taking the backup (which is on a NAS)
and copying it to another server, the job takes forever, slowing things
down so the process ends up getting killed. The Net admin is now (I should
say for the past several days) zipping up the .bak in chunks of about 100
mb, but even this process never seems to end.
The whole process has been started and stopped, for one reason or another,
several times in the past 2 weeks. I have never had to do this myself, but
it just seems to me that this process should not be so time consuming or
painful.
Any one have any ideas on what we might be doing wrong? Or a better way to
do it?
Any ideas appreciated.
TIA,
Nancy Lytle"Nancy Lytle" <lytlen@.mdon-line.com> wrote in message
news:%23ByrI%23k2FHA.476@.TK2MSFTNGP15.phx.gbl...
> We have a 48 gb production database (SQL 2000 sp4 and Windows 2003) and I
> have requested the Net Admin to take the last full backup of this
> database, move it to another server and restore it on that server.
> The problem we seem to be having is taking the backup (which is on a NAS)
> and copying it to another server, the job takes forever, slowing things
> down so the process ends up getting killed. The Net admin is now (I
> should say for the past several days) zipping up the .bak in chunks of
> about 100 mb, but even this process never seems to end.
> The whole process has been started and stopped, for one reason or another,
> several times in the past 2 weeks. I have never had to do this myself,
> but it just seems to me that this process should not be so time consuming
> or painful.
> Any one have any ideas on what we might be doing wrong?
Sounds like your NAS solution is insufficient. If you can't move your
backup file to a new server in a reasonable amount of time, how would you
ever recover in case of a disaster?
David|||That is a good question, one I have asked also. We currently have a 2 node
active passive cluster (I know very little about the network side of things,
and I think the net admin likes it that way).
The NAS size is 450 gb for "everything", all the databases, etc. It isn't a
fibre channel but he says the throughput (?) is comparable, so that
shouldn't be the bottleneck.
I forgot to mention that the production server and NAS are in one domain,
(on the West coast) to a server on another domain (we have several domains
because the production domain is W2k3 and the others are W2k) at the office
here on the East Coast.
All this was done shortly before I came on board, we have no DBA, I am
serving as SQL DBA/Developer as well as the Access developer.
Any ideas on a better solution?
Nancy
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:Oo7pxDl2FHA.1292@.TK2MSFTNGP12.phx.gbl...
>
> Sounds like your NAS solution is insufficient. If you can't move your
> backup file to a new server in a reasonable amount of time, how would you
> ever recover in case of a disaster?
>
> David
>|||One or two questions you may not be able to answer:
Are both the source and target servers using the same NAS? If so, is there
an NAS utility that can duplicate the files? (Split a mirror and re-attach
the mirror to the other server, for example--a technique we used to move a
multi-terabyte db.)
Is your problem in the backup or the transfer? If it is the transfer, how
are you doing the transfer, ie, are you on a private,dedicated link or using
the Internet (since your source and destination are 3K miles apart.)
What is unacceptable timing? For a 50GB database, I would not be surprised
at a 36-48 hour transfer time (500KBytes per second=1Gbyte per hour with
handshaking, etc).
If you have a high capacity tape drive, it might be faster to backit up to
tape and then overnight the tape to the destination.
Joseph R.P. Maloney, CSP,CCP,CDP
"Nancy Lytle" wrote:

> That is a good question, one I have asked also. We currently have a 2 nod
e
> active passive cluster (I know very little about the network side of thing
s,
> and I think the net admin likes it that way).
> The NAS size is 450 gb for "everything", all the databases, etc. It isn't
a
> fibre channel but he says the throughput (?) is comparable, so that
> shouldn't be the bottleneck.
> I forgot to mention that the production server and NAS are in one domain,
> (on the West coast) to a server on another domain (we have several domains
> because the production domain is W2k3 and the others are W2k) at the offic
e
> here on the East Coast.
> All this was done shortly before I came on board, we have no DBA, I am
> serving as SQL DBA/Developer as well as the Access developer.
> Any ideas on a better solution?
> Nancy
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:Oo7pxDl2FHA.1292@.TK2MSFTNGP12.phx.gbl...
>
>|||The source is on a NAS, the destination is not.
The backup is fine, it is the transfer.
As to the time involved, I'm guessing it was longer than 36-48 hours because
the method he is using now is to compress the .bak file using winrar and he
expects that to take 13 hours total to compress the database and chop it up
into 100MB chunks using winrar. He has paused this process until off hours
since winrar is very cpu intensive. He expects this to be completely
archived in 2 days. Then he is going to transfer (so I am guessing the
winrar is being done on the NAS) the files via cable modem (since it is
about 7 times faster than the office T1 and will not interfere with our
email or phones during business hours). He is then going to burn the file
to a CD and bring it in for me.
As I mentioned earlier, there's just something about this that doesn't seem
quite right. I requested this be done over 2 weeks ago, and the request has
still not been completed.
Thanks for you insight.
Nancy
"jrpm" <jrpm@.discussions.microsoft.com> wrote in message
news:1EB18358-280F-4DCD-B513-64685666F6DE@.microsoft.com...[vbcol=seagreen]
> One or two questions you may not be able to answer:
> Are both the source and target servers using the same NAS? If so, is
> there
> an NAS utility that can duplicate the files? (Split a mirror and re-attach
> the mirror to the other server, for example--a technique we used to move a
> multi-terabyte db.)
> Is your problem in the backup or the transfer? If it is the transfer, how
> are you doing the transfer, ie, are you on a private,dedicated link or
> using
> the Internet (since your source and destination are 3K miles apart.)
> What is unacceptable timing? For a 50GB database, I would not be
> surprised
> at a 36-48 hour transfer time (500KBytes per second=1Gbyte per hour with
> handshaking, etc).
> If you have a high capacity tape drive, it might be faster to backit up to
> tape and then overnight the tape to the destination.
>
> --
> Joseph R.P. Maloney, CSP,CCP,CDP
>
> "Nancy Lytle" wrote:
>|||Why don't you try moving the file using FTP rather than copy. If you have a
destination disk that can accomodate the file size then you can do the
following.
1. Install FTP (IIS) on the destination server if not already done. You can
set this to allow anonymouse connections.
2. Backup the database to a flat file using TRANSACT-SQL
BACKUP DATABASE (dbname) TO DISK=N'C:\destination\db.bak' with init
From a client computer initiate an FTP session
such as
C:\> open servername.domain.com
(Username) anonymous
(Password) admin@.domain.com
cd to the directory on the destination server where you want to put the file
.
put (filename) where filename is the name of the database file.
FTP is much faster than a standard copy. I have a database that is 11GB and
FTP takes about 20 minutes to move the file. So you can cut the time down to
80 minutes or so, depending on the system.
Then you can restore the file to the new destination and use the move
command to tell the system where to move the file. You will need to
understand the RESTORE HEADERONLY command and the RESTORE COMMAND.
Hope this helps.
--
Thanks,
David
"Nancy Lytle" wrote:

> The source is on a NAS, the destination is not.
> The backup is fine, it is the transfer.
> As to the time involved, I'm guessing it was longer than 36-48 hours becau
se
> the method he is using now is to compress the .bak file using winrar and h
e
> expects that to take 13 hours total to compress the database and chop it u
p
> into 100MB chunks using winrar. He has paused this process until off hour
s
> since winrar is very cpu intensive. He expects this to be completely
> archived in 2 days. Then he is going to transfer (so I am guessing the
> winrar is being done on the NAS) the files via cable modem (since it is
> about 7 times faster than the office T1 and will not interfere with our
> email or phones during business hours). He is then going to burn the file
> to a CD and bring it in for me.
> As I mentioned earlier, there's just something about this that doesn't see
m
> quite right. I requested this be done over 2 weeks ago, and the request h
as
> still not been completed.
> Thanks for you insight.
> Nancy
> "jrpm" <jrpm@.discussions.microsoft.com> wrote in message
> news:1EB18358-280F-4DCD-B513-64685666F6DE@.microsoft.com...
>
>|||I would also try downloading a trial version of SQL Litespeed from
imceda.com, and installing it on both your originating server &
destination servers. Their compression rates are incredible. You can
probably get your 48gb down to 8-10gb. Dump it locally, then move.|||Sorry,
Mis-typed.
C:\> ftp
ftp> open servername.domain.com
username
password
cd (destination directory)
put filename
ftp>bye
C:\> exit
--
Thanks,
David
"david" wrote:
[vbcol=seagreen]
> Why don't you try moving the file using FTP rather than copy. If you have
a
> destination disk that can accomodate the file size then you can do the
> following.
> 1. Install FTP (IIS) on the destination server if not already done. You ca
n
> set this to allow anonymouse connections.
> 2. Backup the database to a flat file using TRANSACT-SQL
> BACKUP DATABASE (dbname) TO DISK=N'C:\destination\db.bak' with init
> From a client computer initiate an FTP session
> such as
> C:\> open servername.domain.com
> (Username) anonymous
> (Password) admin@.domain.com
> cd to the directory on the destination server where you want to put the fi
le.
> put (filename) where filename is the name of the database file.
> FTP is much faster than a standard copy. I have a database that is 11GB an
d
> FTP takes about 20 minutes to move the file. So you can cut the time down
to
> 80 minutes or so, depending on the system.
> Then you can restore the file to the new destination and use the move
> command to tell the system where to move the file. You will need to
> understand the RESTORE HEADERONLY command and the RESTORE COMMAND.
> Hope this helps.
> --
> Thanks,
> David
>
> "Nancy Lytle" wrote:
>