Is using linked server with MSDE the way to go for this problem?

We have a problem and I'm not sure of the best way to solve it. Our situation:
We have an ASP.NET application which uses a full SQL Server database.
We want to add the capability for salespeople to initially install (in a
separate
procedure) our app locally on a tablet PC with a blank MSDE
database. We'll figure this part out.
Then, periodically, when they are able to access our
'master' ASP.NET app from the road, we would like for them to be able
to download selected rows from a number of tables from the
central SQL db to their local MSDE. They would make changes locally
(offline),
then reconnect later on and upload the data back to the central db.
I am thinking that creating a linked server temporarily on the master
db (via a call to sp_addlinkedserver) to transfer this data back and forth
to the MSDE db may be the way to go, but haven't found any specific info/case
studies/etc. that supports that. In addition, I created an MSDE database on
a tablet PC which is hooked to our network, but haven't found a way to
successfully 'broadcast' its presence to the SQL Server db so that I can
create a linked-server definition via Enterprise Manager.
Does anyone have any thoughts either about how to get this approach to work,
or a better way to solve this problem?
Thanks in advance for your help!
hi Rick,
"Rick Pinson" <rickpinson@.yahoo.com(donotspam)> ha scritto nel
messaggio news:69A042D0-CEF0-4AC8-A210-7BC29A6CD5E2@.microsoft.com
> We have a problem and I'm not sure of the best way to solve it. Our
> situation:
> We have an ASP.NET application which uses a full SQL Server database.
> We want to add the capability for salespeople to initially install
> (in a separate
> procedure) our app locally on a tablet PC with a blank MSDE
> database. We'll figure this part out.
> Then, periodically, when they are able to access our
> 'master' ASP.NET app from the road, we would like for them to be able
> to download selected rows from a number of tables from the
> central SQL db to their local MSDE. They would make changes locally
> (offline),
> then reconnect later on and upload the data back to the central db.
> I am thinking that creating a linked server temporarily on the master
> db (via a call to sp_addlinkedserver) to transfer this data back and
> forth
> to the MSDE db may be the way to go, but haven't found any specific
> info/case studies/etc. that supports that.
this seems like you want to perform a push operation, from the "master" to
all "remote" MSDE instances...
I'd go the opposite way... when the remote MSDE instance is able to connect
to the "master", add a linked server from remote to master, perform the
operations you need, drop the the linked server...

>In addition, I created an
> MSDE database on
> a tablet PC which is hooked to our network, but haven't found a way to
> successfully 'broadcast' its presence to the SQL Server db so that I
> can create a linked-server definition via Enterprise Manager.
the broadcasting mechanism to propagate server presence over the lan is not
always granted as lot of stuff is involved...
for instance: have a look at ListAvailableServer exposed by SQL-DMO:
ListAvailableServer uses ODBC function SQLBrowseConnect() provided by ODBC
libraries installed by Mdac;
this is a mechanism working in broadcast calls, which result never are
conclusive and consistent, becouse results are influenced of various
servers's answer states, answer time, etc.
Until Mdac 2.5, SQLBrowseConnect function works based on a NetBIOS
broadcast, on which SQL Servers respond (Default protocol for SQL Server
7.0), while in SQL Server 2000 the rules changed, because the default client
protocol changed to TCP/IP and now a UDP broadcast is used, beside a NetBIOS
broadcast, listening on port 1434:
which is using a UDP broadcast on port 1434, if instance do not listen or
not respond on time they will not be part of the enumeration.
Some basic rules for 7.0 are:
- SQL Servers have to be running on Windows NT or Windows 2000 and have to
listen on Named Pipes, that is why in 7.0 Windows 9x SQL Servers will never
show up, because they do not listen on Named Pipes.
- The SQL Server has to be running in order to respond on the broadcast.
There is a gray window of 15 minutes after shutdown, where a browse master
in the domain may respond on the broadcast and answer.
- If you have routers in your network, that do not pass on NetBIOS
broadcasts, this might limit your scope of the broadcast.
- Only servers within the same NT domain (or trust) will get enumerated.
In SQL Server 2000 using MDAC 2.6 this changes a little, because now the
default protocol has been changed to be TCP/IP sockets and instead of a
NetBIOS broadcast, they use a TCP UDP to detect the servers. The same logic
still applies roughly.
- SQL Server that are running
- SQL Server that listening on TCP/IP
- Running on Windows NT or Windows 2000 or Windows 9x
- If you use routers and these are configured not to pass UDP broadcasts,
only machines within the same subnet show up.
Upgrading to Service Pack 2 of SQL Server 2000 is required in order to have
..ListAvailableServer method to work properly, becouse precding release of
Sql-DMO Components of Sql Server 2000 present a bug in this area.
The Service Pack 3a introduced some new amenity in order to prevent MSDE
2000 to be hit by Internet worms like Slammer and Saphire virus and to
increase security, so that Microsoft decided to default for disabling
SuperSockets Network Protocols on new MSDE 2000 installation.
Instances of SQL Server 2000 SP3a or MSDE 2000 SP3a will stop listening on
UDP port 1434 when they are configured to not listen on any network
protocols. This will stop enlisting these servers.
of course, Id suggest you to test your network protocols settings...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

is this possible?

Can this be done on asp.net 2.0??

<asp:SqlDataSource id="SqlDataSource2" runat="server" connectionstring="<%$ ConnectionStrings:BonsaiDataBaseConnectionString %>" selectcommand="SELECT [Nederlandse Naam] AS Nederlandse_Naam FROM [BonsaiSoorten]WHERE [Botanische Naam]=' <% DDL1.SelectedItem.Value%> ' ">
</asp:SqlDataSource>

I don't think that's possible, but you could use a ControlParameter in the SelectParameters property of the SqlDataSource to do that.

HTH,
Ryan

|||You can do the following instead (example using TableMaster and TableDetail with MasterId in common):

<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource1"
DataTextField="Description" DataValueField="MasterId">
</asp:DropDownList
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:myConnectionString %>"
SelectCommand="SELECT * FROM [TableMaster]"></asp:SqlDataSource
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="DetailId"
DataSourceID="SqlDataSource2">
<Columns>
<asp:BoundField DataField="DetailId" HeaderText="DetailId" ReadOnly="True" SortExpression="DetailId" />
<asp:BoundField DataField="MasterId" HeaderText="MasterId" SortExpression="MasterId" />
<asp:BoundField DataField="Description" HeaderText="Description" SortExpression="Description" />
</Columns>
</asp:GridView
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:myConnectionString %>"
SelectCommand="SELECT * FROM [TableDetail] WHERE ([MasterId] = @.MasterId)">
<SelectParameters>
<asp:ControlParameterControlID="DropDownList1" Name="MasterId" PropertyName="SelectedValue" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
|||Thanks for your quick reply!!

Can this value be added to a label?? I tried several things but nothing worked out.|||What value? Please be more specific, providing code where applicable.|||I've got a table in a database with two rows, a 'botanic name tree' and a 'dutch name tree' column. The dropdownlist is filled with the values of the 'botanic name tree' column. When a value is selected I like to show the correspondending value of the 'dutch name tree' column, in the same row as the selected botanic name, in a label.

Now I've got this working with your code in combination with two dropdownlists. Now I like to use it with a dropdownlist and a label.|||Provide a sample code if possible. I still can't visualize what it is that you're trying to achieve. I assume that you're still using a GridView control where you want to have a DDL as one of the columns along with a Label, right? Providing a sample code will make it much more clear.|||It's all upside down already. I wasn't using a Gridview. I got a ddl with a label and I wanted to change the label.text property with the correspondending value (in the database) of the selected value in the ddl.

It's hard for me to explain in english and I don't have the code anymore because I was trying to get it to work. I've got now new ideas to get my page done and the issues I'm walking against are totally different so when I have a new question I post it in a new thread.

Thanks for your replies and patience.sql

Is this expected behavior?

I posted this at the asp.net forums but somone suggested I post it here. So:

Try this in sql server:

select COALESCE(a1, char(254)) as c1 from

(select 'Z' as a1 union select 'Ya' as a1 union select 'Y' as a1 union select 'W' as a1) as b1

group by a1

with rollup order by c1

select COALESCE(a1, char(255)) as c1 from

(select 'Z' as a1 union select 'Ya' as a1 union select 'Y' as a1 union select 'W' as a1) as b1

group by a1

with rollup order by c1

The only difference is that the first one uses 254 and the second one uses 255. The first sorts like this:

W
Y
Ya
Z
t

The second one sorts like this:

W
Y
?
Ya
Z

Is this expected behavior?

It is because the sort order is based on the character set you are using, not where the characters show up in the ascii or unicode charts. If you want it to sort based on position like that, you need to use a binary sort order.

select COALESCE(a1, char(254)) COLLATE Latin1_General_BIN as c1
from (select 'Z' as a1
union select 'Ya' as a1
union select 'Y' as a1
union select 'W' as a1) as b1
group by a1
with rollup order by c1

select COALESCE(a1, char(255)) COLLATE Latin1_General_BIN as c1
from (select 'Z' as a1
union select 'Ya' as a1
union select 'Y' as a1
union select 'W' as a1) as b1
group by a1
with rollup order by c1

In this case both characters that are NULL will sort to the end of the results.

Is this a bug?

RS 2005 (SQL 2005 SP1).

When you edit the ASP.Net tab (in IIS) for the ReportServer folder and change the Authentication Mode from Windows to None the Report Server will bomb out. Trying to open th ereportserver returns "Report Server is not responding, verify the report server is running and can be accessed from this computer".

Changing it back to "Windows" makes no difference, even re-starting the Reporting Service and IIS makes no difference, the report server will not respond.

Can some one test this?

I ended up having to re-install RS 2005....!!

What does the venet log / the logs in the log directory tell you ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

In the SQLDUMPER_ERRORLog.log file there wass this..

07/13/06 12:53:35, ERROR , SQLDUMPER_UNKNOWN_APP.EXE, AdjustTokenPrivileges () failed (00000514)
07/13/06 12:53:35, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Input parameters: 4 supplied
07/13/06 12:53:35, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ProcessID = 328
07/13/06 12:53:35, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ThreadId = 0
07/13/06 12:53:35, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Flags = 0x0
07/13/06 12:53:35, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDumpFlags = 0x0
07/13/06 12:53:35, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, SqlInfoPtr = 0x47405860
07/13/06 12:53:35, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, DumpDir = <NULL>
07/13/06 12:53:35, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ExceptionRecordPtr = 0x00000000
07/13/06 12:53:35, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ContextPtr = 0x00000000
07/13/06 12:53:35, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ExtraFile = <NULL>
07/13/06 12:53:35, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, InstanceName = <NULL>
07/13/06 12:53:35, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ServiceName = <NULL>
07/13/06 12:53:36, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 11 not used
07/13/06 12:53:37, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 7 not used
07/13/06 12:53:37, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDump completed: C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\LogFiles\SQLDmpr0001.mdmp
07/13/06 12:53:37, ACTION, w3wp.exe, Watson Invoke: No

|||Is the report service running after that bug ? What does the reportserver log tell you ? Are there any event log entries ? It would be helpful to have all event log (at leat the last entries) to identify the bug.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de
|||

Yes the report service was running after this.

Did you try to replicate the problem (on a test machine of course)....

|||

Hey, niallhannon,

I have read some of your posting and found out that you had most of the problems with the report service I have now, I wonder if you have set up the sample reports from Microsoft.

I am setting up the sample report on the testing server and I have problems with deploying to the http://myserver/reportserver and it gave the the following error, see if you can provide some tips to me:

TITLE: Microsoft Report Designer

A connection could not be made to the report server http://10.128.64.223/reportserver.

ADDITIONAL INFORMATION:

Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'.
The request failed with the error message:
--
<html>
<head>
<title>
SQL Server Reporting Services
</title><meta name="Generator" content="Microsoft SQL Server Reporting Services 9.00.1399.00" />
<meta name="HTTP Status" content="500" />
<meta name="ProductLocaleID" content="9" />
<meta name="CountryLocaleID" content="1033" />
<meta name="StackTrace" content />
<style>
BODY {FONT-FAMILY:Verdana; FONT-WEIGHT:normal; FONT-SIZE: 8pt; COLOR:black}
H1 {FONT-FAMILY:Verdana; FONT-WEIGHT:700; FONT-SIZE:15pt}
LI {FONT-FAMILY:Verdana; FONT-WEIGHT:normal; FONT-SIZE:8pt; DISPLAY:inline}
.ProductInfo {FONT-FAMILY:Verdana; FONT-WEIGHT:bold; FONT-SIZE: 8pt; COLOR:gray}
A:link {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR:#3366CC; TEXT-DECORATION:none}
A:hover {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR:#FF3300; TEXT-DECORATION:underline}
A:visited {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR:#3366CC; TEXT-DECORATION:none}
A:visited:hover {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; color:#FF3300; TEXT-DECORATION:underline}

</style>
</head><body bgcolor="white">
<h1>
Reporting Services Error<hr width="100%" size="1" color="silver" />
</h1><ul>
<li>An internal error occurred on the report server. See the error log for more details. (rsInternalError) <a href="http://go.microsoft.com/fwlink/?LinkId=20476&EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&EvtID=rsInternalError&ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&ProdVer=9.00.1399.00" target="_blank">Get Online Help</a></li><ul>
<li>Object reference not set to an instance of an object.</li>
</ul>
</ul><hr width="100%" size="1" color="silver" /><span class="ProductInfo">SQL Server Reporting Services</span>
</body>
</html>
--. (Microsoft.ReportingServices.Designer)

BUTTONS:

OK

Is there something wrong with my IF code...

<%
If required_equip = Desktop Then
If required_equip = Laptop Then
End If
If required_equip = Other Then
End If
Response.Redirect "nh_request.asp"
End If
%>
Just wondering if the above would work within my form to check a radio
button on submission...
I have 1 radio button, that if selected i want the person to be taken to a
2nd form after they submit the first form... if that radio button is NOT
selected.. then they submit the form and they are done...
can anyone tell me if the above would work and if so, where i should place
it within my code so it checks when the submit button is clicked.
What does this have to do with SQL Server?
http://www.aspfaq.com/
(Reverse address to reply.)
"Daniel_Cha" <dan_cha@.hotmail.com> wrote in message
news:uj#03$0cEHA.3420@.TK2MSFTNGP12.phx.gbl...
> <%
> If required_equip = Desktop Then
> If required_equip = Laptop Then
> End If
> If required_equip = Other Then
> End If
> Response.Redirect "nh_request.asp"
> End If
> %>
> Just wondering if the above would work within my form to check a radio
> button on submission...
> I have 1 radio button, that if selected i want the person to be taken to a
> 2nd form after they submit the first form... if that radio button is NOT
> selected.. then they submit the form and they are done...
> can anyone tell me if the above would work and if so, where i should place
> it within my code so it checks when the submit button is clicked.
>
>
|||You are asking this question in the wrong forum... This should be posted in
one of the development forums.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Daniel_Cha" <dan_cha@.hotmail.com> wrote in message
news:uj%2303$0cEHA.3420@.TK2MSFTNGP12.phx.gbl...
> <%
> If required_equip = Desktop Then
> If required_equip = Laptop Then
> End If
> If required_equip = Other Then
> End If
> Response.Redirect "nh_request.asp"
> End If
> %>
> Just wondering if the above would work within my form to check a radio
> button on submission...
> I have 1 radio button, that if selected i want the person to be taken to a
> 2nd form after they submit the first form... if that radio button is NOT
> selected.. then they submit the form and they are done...
> can anyone tell me if the above would work and if so, where i should place
> it within my code so it checks when the submit button is clicked.
>
>

Is there something wrong with my IF code...

<%
If required_equip = Desktop Then
If required_equip = Laptop Then
End If
If required_equip = Other Then
End If
Response.Redirect "nh_request.asp"
End If
%>
Just wondering if the above would work within my form to check a radio
button on submission...
I have 1 radio button, that if selected i want the person to be taken to a
2nd form after they submit the first form... if that radio button is NOT
selected.. then they submit the form and they are done...
can anyone tell me if the above would work and if so, where i should place
it within my code so it checks when the submit button is clicked.What does this have to do with SQL Server?
http://www.aspfaq.com/
(Reverse address to reply.)
"Daniel_Cha" <dan_cha@.hotmail.com> wrote in message
news:uj#03$0cEHA.3420@.TK2MSFTNGP12.phx.gbl...
> <%
> If required_equip = Desktop Then
> If required_equip = Laptop Then
> End If
> If required_equip = Other Then
> End If
> Response.Redirect "nh_request.asp"
> End If
> %>
> Just wondering if the above would work within my form to check a radio
> button on submission...
> I have 1 radio button, that if selected i want the person to be taken to a
> 2nd form after they submit the first form... if that radio button is NOT
> selected.. then they submit the form and they are done...
> can anyone tell me if the above would work and if so, where i should place
> it within my code so it checks when the submit button is clicked.
>
>|||You are asking this question in the wrong forum... This should be posted in
one of the development forums.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Daniel_Cha" <dan_cha@.hotmail.com> wrote in message
news:uj%2303$0cEHA.3420@.TK2MSFTNGP12.phx.gbl...
> <%
> If required_equip = Desktop Then
> If required_equip = Laptop Then
> End If
> If required_equip = Other Then
> End If
> Response.Redirect "nh_request.asp"
> End If
> %>
> Just wondering if the above would work within my form to check a radio
> button on submission...
> I have 1 radio button, that if selected i want the person to be taken to a
> 2nd form after they submit the first form... if that radio button is NOT
> selected.. then they submit the form and they are done...
> can anyone tell me if the above would work and if so, where i should place
> it within my code so it checks when the submit button is clicked.
>
>

Is there any database midware like asp script?

Is there any 'database midware' liked asp script? The following code is 'database midware' class in java, is there any code/script for asp3 (not asp.net) in jscript/vbscript ?
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Connection;
import java.util.List;

public class ASPD {
public ASPD() {
}

private Connection connection;
private Statement stmt;
private ResultSet rs;

public void connect(String connectionString) {
//TODO
}

public void disconnect() {
//TODO
}

public void query(String sql) {
//TODO
}

public int update(String sql) {
//TODO
return 0;
}

public boolean execute() {
//TODO
return false;
}

public void preparedQuery(String sql, List parameters) {
//TODO
}

public int preparedUpdate(String sql, List parameters) {
//TODO
return 0;
}

public boolean preparedExecute(String sql, List parameters) {
//TODO
return false;
}
}//EOF
Any help is thankful.If you are talking about classes that support data access, you might want to look at Active X Data Objects that come bundled in MDAC.|||There are tons of example scripts available, look for example here (http://www.aspwebpro.com/tutorials/asp/dbconnectionopen.asp)

Hope this helps.