Is there a session variable? Microsoft please help

Is there a way to access a session variable for the url in reporting services? This is hard to explain, but our internal users access our website and view reports via one url, while external users use a different url to view the website and reports. I used the jump to url in some of my reports, but I need the report to read which url they came in on so that it can jump to the correct url that I need it to. Has anyone done anything similar to this?Anyone have any ideas? I use "jump to url" in quite a few of our reports w/ the link that it is jumping to being different information on our website. Our internal users see our website on a different url than our external users, so in my jump to link I need to conditionally set whether they go to the one url or the other, but I have to determine how they came in to begin with to determine which one they jump to.

Is there a session variable?

Is there a way to access a session variable for the url in reporting services? This is hard to explain, but our internal users access our website and view reports via one url, while external users use a different url to view the website and reports. I used the jump to url in some of my reports, but I need the report to read which url they came in on so that it can jump to the correct url that I need it to. Has anyone done anything similar to this?Anyone have any ideas? I use "jump to url" in quite a few of our reports w/ the link that it is jumping to being different information on our website. Our internal users see our website on a different url than our external users, so in my jump to link I need to conditionally set whether they go to the one url or the other, but I have to determine how they came in to begin with to determine which one they jump to.

Is there a maximum perameter set for sql server?

This code produces a table fine, however its when I try to pull data from it in Access is when I get the problem. The error I receive in access is: Insufficient number of arguments were supplied for the procedure or function.

ALTER FUNCTION dbo.R114ReportCopy (@.Acyear smallint)
RETURNS @.RtnTab table
(DeptName varchar(100),Acyear smallint,
Eid smallint,FacName varchar(50),
FormName varchar(100), FormNo smallint,
IndN smallint, DivN smallint, DepN smallint,
IndAvCont float, IndSDCont float,
IndAvVal float, IndSDVal float,
DivAvCont float, DivSDCont float,
DivAvVal float, DivSDVal float,
DepAvCont float, DepSDCont float,
DepAvVal float, DepSDVal float,

LIndAvCont float, LIndSDCont float,
LIndAvVal float, LIndSDVal float, LIndN smallint,
LDivAvCont float, LDivSDCont float,
LDivAvVal float, LDivSDVal float, LDivN smallint,
LDepAvCont float, LDepSDCont float,
LDepAvVal float, LDepSDVal float, LDepN smallint,

CIndAvCont float, CIndSDCont float,
CIndAvVal float, CIndSDVal float, CIndN smallint,
CDivAvCont float, CDivSDCont float,
CDivAvVal float, CDivSDVal float, CDivN smallint,
CDepAvCont float, CDepSDCont float,
CDepAvVal float, CDepSDVal float, CDepN smallint,

Comments text)

AS
BEGIN
Declare @.LastYear smallint
Set @.Lastyear=@.Acyear-1

Insert into @.RtnTab
Select dbo.deptcodename(dept) Department,Num.*, comments
from

(
Select @.acyear acyear,T.*,
Lavcontact,Lsdcontact,Lavvalue,Lsdvalue,Ln,
Ldvcontactav,Ldvcontactsd,Ldvvalueav,Ldvvaluesd,Ld vn,
Ldepcontactav,Ldepcontactsd,Ldepvalueav,Ldepvalues d,Ldepn,
Cavcontact,Csdcontact,Cavvalue,Csdvalue,Cn,
Cdvcontactav,Cdvcontactsd,Cdvvalueav,Cdvvaluesd,Cd vn,
Cdepcontactav,Cdepcontactsd,Cdepvalueav,Cdepvalues d,Cdepn
from

(
Select eid,dbo.idtoname(eid) Name,description formname,f.formno,
N,DvN,DepN,
avcontact,sdcontact,AvValue,SdValue,
dvcontactAV,dvcontactSD,dvValueAv,dvValueSD,
depContactAV,depContactSD,depValueAv,DepValueSD from R114ByYear(@.Acyear,@.Acyear) R, R114FormName F
where R.formno=F.formno
) T left join

(
Select eid,formno,
avcontact Lavcontact,sdcontact Lsdcontact,avvalue Lavvalue,sdvalue Lsdvalue,n Ln,
dvcontactav Ldvcontactav,dvcontactsd Ldvcontactsd,dvvalueav Ldvvalueav,dvvaluesd Ldvvaluesd,dvn Ldvn,
depcontactav Ldepcontactav,depcontactsd Ldepcontactsd,depvalueav Ldepvalueav,depvaluesd Ldepvaluesd,depn Ldepn
from R114ByYear(@.Lastyear,@.Lastyear) L
) L on T.eid=L.eid and T.formno=L.formno
left join

(
Select eid,formno,
avcontact Cavcontact,sdcontact Csdcontact,avvalue Cavvalue,sdvalue Csdvalue,n Cn,
dvcontactav Cdvcontactav,dvcontactsd Cdvcontactsd,dvvalueav Cdvvalueav,dvvaluesd Cdvvaluesd,dvn Cdvn,
depcontactav Cdepcontactav,depcontactsd Cdepcontactsd,depvalueav Cdepvalueav,depvaluesd Cdepvaluesd,depn Cdepn
from R114ByYear(1995,@.Acyear) C
) C on T.eid=C.eid and T.formno=c.formno
) Num Left join

(
Select eid,formtype formno, comments from R114
where acadyear=@.Acyear
and (comments is not null
or cast(comments as varchar(10)) not in ('NULL','NA','none','n/a','n.a',' ') )
) comment on Num.eid=comment.eid and Num.formno=comment.formno
,RVUMasterList M
where
M.acyear=@.acyear
and Num.eid=M.eid
order by 1,4,5
Return
ENDIt sounds like you are not supplying the right number of arguments to one of your function calls.sql

Is there a log of activity as compared to the Tran log?

I am trying to identify if I have unwanted guests gaining access to my
system or it's a run away query by a user on my network. I am thinking of
recording SPIDs as one way.
Via Spotlight I can see that I'm having beyond normal usage. Granted I have
a new PHB who likes to run queries off his laptop to show off to other
managers. Real scary when he has a little understanding of the data.
The box running Spotlight is WAY under powered, but can it keep that logging
data instead of the server itself?You can create your own log with profiler - it's a good tool
to track down performance issues in a database. If you use
it and performance is a concern, don't trace to the database
or from the database. Trace from a client and if tracing to
a file, have it go on that client.
Or use a server side trace. But you'd probably want to play
around with profiler from a client first.
-Sue
On Fri, 23 Sep 2005 08:30:52 -0500, "Stephen Russell"
<srussell@.transactiongraphics.com> wrote:

>I am trying to identify if I have unwanted guests gaining access to my
>system or it's a run away query by a user on my network. I am thinking of
>recording SPIDs as one way.
>Via Spotlight I can see that I'm having beyond normal usage. Granted I hav
e
>a new PHB who likes to run queries off his laptop to show off to other
>managers. Real scary when he has a little understanding of the data.
>The box running Spotlight is WAY under powered, but can it keep that loggin
g
>data instead of the server itself?
>|||2 things here really
1. Tracking "unwanted guests" gaining access to your SQL Server is easily
tracked with no perf hit by setting "Audit level" to "All". This is strictly
for login attempts and can be se via EM. Right click on your server, select
properties and click on the security tab. This puts an entry in the logs
everytime a login attempt is successfully or failed. At the very minimum,
you should log failures.
2. Runaway queries are a little harder to do with SQL Server 2000. First,
you need to define what you mean by runaway queries. CPU consumption?
Memory? Length of query? SQL Profiler and a bunch of 3rd party tools from
folks like Quest, BMC, etc... let's you capture the appropriate data to
"look at" so you can figure out who's got a runaway query. Depending on what
you use, you may have to do extra work. For example, if you capture trace
info with Profiler or SQL Trace, you'll need a way to track the data and
raise alerts based on thresholds that you set. Life gets a LOT better in
2005 with the default "reports" (it's a cool dashboard really) but in 2000,
there's a little bit of work needed to get you there. :-)
Btw, one thing you might want to consider is the Query Governor cost limit
option. It's not the most "precise" tool you can have since it works based
on estimates but it's a pretty good start with minimal effort. Look it up in
BOL. It's well documented.
joe.
"Stephen Russell" <srussell@.transactiongraphics.com> wrote in message
news:u5%23LlMEwFHA.664@.tk2msftngp13.phx.gbl...
>I am trying to identify if I have unwanted guests gaining access to my
>system or it's a run away query by a user on my network. I am thinking of
>recording SPIDs as one way.
> Via Spotlight I can see that I'm having beyond normal usage. Granted I
> have a new PHB who likes to run queries off his laptop to show off to
> other managers. Real scary when he has a little understanding of the
> data.
> The box running Spotlight is WAY under powered, but can it keep that
> logging data instead of the server itself?
>
>

Is the a similar function to NOW()

Hi,

I just imported a MS Access table to SQL Server via DTC. The table has a date field in it that I would like to populate automatically. In fact the access table used the Now() funtion to do just that. Evertime an insert statement occured the current date and time would be inserted into the u_createDate field.

The now function is not working in MS SQL. Is there a similar funciton that will automatically update the u_createDate field when a record in inserted?

MikeYou can use the GETDATE() function.|||Originally posted by manowar
You can use the GETDATE() function.

Thank you, Mike

Is SQL required for 15 users

Hello:
I have 15 clients that connect to an Access database that is hosted on a sha
red network drive. We experienced data corruprion recently and the database
designer that was hired to fix this problem recommended that we introduce a
nd SQL server. Workstatio
ns are WinXP, with Office and Access 2000. Two clients are using Office 200
0, on Win2000Pro. The server is Win2000 with active directory.
This is a costly endeavour, and I wonder if it is an overkill for such a sma
ll site. A maximum of four users need to have write access simultanoeusly.
The database is less than 50Mb in size, and it is stored on a network dri
ve where all have full read
and write access. I would tend to reconfigure security appropriately on the
server and isolate the database and roll out ACCESS 2003 as a first solutio
n to resolve this issue, and then introduce SQL if problem persist. Does i
t make sense, or would you
recommend I roll-in a SQL server?
Thanks"CC" <anonymous@.discussions.microsoft.com> wrote in message
news:F2ED7F5C-8C6F-4FA5-99E6-A28116D5169D@.microsoft.com...
> Hello:
> I have 15 clients that connect to an Access database that is hosted on a
shared network drive. We experienced data corruprion recently and the
database designer that was hired to fix this problem recommended that we
introduce and SQL server. Workstations are WinXP, with Office and Access
2000. Two clients are using Office 2000, on Win2000Pro. The server is
Win2000 with active directory.
> This is a costly endeavour, and I wonder if it is an overkill for such a
small site. A maximum of four users need to have write access
simultanoeusly. The database is less than 50Mb in size, and it is stored
on a network drive where all have full read and write access. I would tend
to reconfigure security appropriately on the server and isolate the database
and roll out ACCESS 2003 as a first solution to resolve this issue, and then
introduce SQL if problem persist. Does it make sense, or would you
recommend I roll-in a SQL server?
> Thanks
>
Access 2003 has the same problems as Access 2000 with respect to corruption.
See if the developer can get MSDE. It's SqlServer, it's free and for a
small database with 15 clients it will work fine.
David|||A properly designed Access project can handle your description. It will by
definition be file-server and not client-server, but each user should have
their own front-end mdb and the back end should be at least one mdb. You
should seek help in nntp://comp.databases.ms-access.
hth
Eric
"CC" <anonymous@.discussions.microsoft.com> wrote in message
news:F2ED7F5C-8C6F-4FA5-99E6-A28116D5169D@.microsoft.com...
> Hello:
> I have 15 clients that connect to an Access database that is hosted on a
shared network drive. We experienced data corruprion recently and the
database designer that was hired to fix this problem recommended that we
introduce and SQL server. Workstations are WinXP, with Office and Access
2000. Two clients are using Office 2000, on Win2000Pro. The server is
Win2000 with active directory.
> This is a costly endeavour, and I wonder if it is an overkill for such a
small site. A maximum of four users need to have write access
simultanoeusly. The database is less than 50Mb in size, and it is stored
on a network drive where all have full read and write access. I would tend
to reconfigure security appropriately on the server and isolate the database
and roll out ACCESS 2003 as a first solution to resolve this issue, and then
introduce SQL if problem persist. Does it make sense, or would you
recommend I roll-in a SQL server?
> Thanks
>
>
>
>|||A properly designed Access DB may or may not be able to handle 15 users.
Probably yes. But we don't know the workload. We do know that they have been
having corruption problems which Access is susecptible to.
A single processor license of SQL Standard goes for less than 5K. MSDE of
course would be free if they can use that. Perhaps the MSDE version with
Access?
But even at 5K... I question whether or not it's truly a costly endeavor.
How much was paid to have the consultant come in and 'fix the problem'.
What's the cost to the business, including lost worker productivity, in
dealing with these corruption problems?
Access is a great DB. But Access doesn't scale as well and if customer is
already seeing corruption, perhaps they should consider SQL. I don't
honestly think cost shoudl be the deciding factor in a situation like this,
at least not unless the 'cost' of having corrputed Access db's in factored.
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Eric Sabine" <mopar41@.___ho_y_tmail.ScPoAmM> wrote in message
news:eYY9g$tBEHA.712@.tk2msftngp13.phx.gbl...
> A properly designed Access project can handle your description. It will
by
> definition be file-server and not client-server, but each user should have
> their own front-end mdb and the back end should be at least one mdb. You
> should seek help in nntp://comp.databases.ms-access.
> hth
> Eric
>
> "CC" <anonymous@.discussions.microsoft.com> wrote in message
> news:F2ED7F5C-8C6F-4FA5-99E6-A28116D5169D@.microsoft.com...
> shared network drive. We experienced data corruprion recently and the
> database designer that was hired to fix this problem recommended that we
> introduce and SQL server. Workstations are WinXP, with Office and Access
> 2000. Two clients are using Office 2000, on Win2000Pro. The server is
> Win2000 with active directory.
> small site. A maximum of four users need to have write access
> simultanoeusly. The database is less than 50Mb in size, and it is stored
> on a network drive where all have full read and write access. I would
tend
> to reconfigure security appropriately on the server and isolate the
database
> and roll out ACCESS 2003 as a first solution to resolve this issue, and
then
> introduce SQL if problem persist. Does it make sense, or would you
> recommend I roll-in a SQL server?
>|||We experienced data corruption once since my arrival, but no maintenance was
performed on the databases at all for a few years. We were plagued with ne
twork slowdows at the time of the incident. Maintenance was performed on th
e server (among other thing
s that were found is 16% fragmentation). The contractor that created the
database invoice a day of work. I understand the problem was resolved by s
imply using compact and repair.
The database was created originally under PAradox, then migrated to ACCESS 9
7 and then to 2000. I suspect that this is the root of the problem, coupled
with the fact that we are using 2000 as front end and that there was no mai
ntenance.|||"cc" <anonymous@.discussions.microsoft.com> wrote in message
news:5293B9C9-9C65-4406-9659-954DF6BD5227@.microsoft.com...
> We experienced data corruption once since my arrival, but no maintenance
was performed on the databases >at all for a few years. We were plagued
with network slowdows at the time of the incident. Maintenance was
>performed on the server (among other things that were found is 16%
fragmentation). The contractor that >created the database invoice a day
of work. I understand the problem was resolved by simply using compact >and
repair.
> The database was created originally under PAradox, then migrated to ACCESS
97 and then to 2000. I >suspect that this is the root of the problem,
coupled with the fact that we are using 2000 as front end and that >there
was no maintenance.
Network problems will almost always cause database corruption in Access.
If your network problems are solved, your corruption problems may be solved
too.
David

Is SQL Native Client required on a client machine?

If I understand correctly, SQL Native Client must be used to access the new
features in SQL Server 2005. What if I just want to access SQL Server 2005
but don't use any new feature, can I not install it onto client machine and
just use SQL Server driver comes with ODBC?Hi
MDAC 2.6 and later is supported if you are not using any new features.
IMHO, it would be best to be on MDAC 2.8
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:B420C925-C42C-4DE3-A5E6-B337637B7BB3@.microsoft.com...
> If I understand correctly, SQL Native Client must be used to access the
> new
> features in SQL Server 2005. What if I just want to access SQL Server
> 2005
> but don't use any new feature, can I not install it onto client machine
> and
> just use SQL Server driver comes with ODBC?
>

Is SQL Native Client required on a client machine?

If I understand correctly, SQL Native Client must be used to access the new
features in SQL Server 2005. What if I just want to access SQL Server 2005
but don't use any new feature, can I not install it onto client machine and
just use SQL Server driver comes with ODBC?
Hi
MDAC 2.6 and later is supported if you are not using any new features.
IMHO, it would be best to be on MDAC 2.8
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:B420C925-C42C-4DE3-A5E6-B337637B7BB3@.microsoft.com...
> If I understand correctly, SQL Native Client must be used to access the
> new
> features in SQL Server 2005. What if I just want to access SQL Server
> 2005
> but don't use any new feature, can I not install it onto client machine
> and
> just use SQL Server driver comes with ODBC?
>

Is Reporting Services Suppot to take the RDL Report Objects in Windows Form

i have place the textboxes in RDL report in Reporting Services and set there name. i want to access those text boxes from the Windows Form.

For Example

In Windows Form

Dim obj as RDLReport

Obj.TextBox1.Text="Ali How are you"

.Can i do that.?

No. You can set the properties of the text box to be an expression, but you cannot programmatically write to the text box like that.|||To do something like this you should bind the text in the textbox to a parameter value, and then pass that parameter to your viewer control.

Is Replication of entire server possible?

I have 1 sql 2000 server in place that has about 150 databases on it. Two
applications work access those databases half are for one application half
are for the other application. Since the server is becoming quite
overloaded lately I've decided to take another server with sql 2000 on it
and split one application off of the original server and place it's
databases on the second effectively splitting the load between two servers.
Now I'd like to use replication to have both servers be almost identical to
give me some redundency in the hardware. Can I easily set up replication so
that server 1 and server 2 both contain the same databases and the data is
replicated between the two? I know I can go in and set up a merge
replication for each DB and this will work ( I've done this with 2 db's
already) but can I set it so that the server figures out what DB's there
are and replicates them? This will allow me to not need to take the time
to setup more replication at the time of a new DB's creation.Nope, it's manual.
I'd think hard about the manageability of a replication setup with 150
database. There are other ways to split the load while providing redundancy.
For example, you could setup a multi-instance failover cluster and make sure
you mirror the disks (always a good practice anyway). Slightly higher cost
in initial hardware purchase but IMHO, much less pain (consequently; lower
cost) in the long run.
joe.
"jamie" <strider3700@.nospam.nospam> wrote in message
news:%232hnndn3FHA.2676@.TK2MSFTNGP15.phx.gbl...
>I have 1 sql 2000 server in place that has about 150 databases on it. Two
>applications work access those databases half are for one application half
>are for the other application. Since the server is becoming quite
>overloaded lately I've decided to take another server with sql 2000 on it
>and split one application off of the original server and place it's
>databases on the second effectively splitting the load between two servers.
> Now I'd like to use replication to have both servers be almost identical
> to give me some redundency in the hardware. Can I easily set up
> replication so that server 1 and server 2 both contain the same databases
> and the data is replicated between the two? I know I can go in and set
> up a merge replication for each DB and this will work ( I've done this
> with 2 db's already) but can I set it so that the server figures out what
> DB's there are and replicates them? This will allow me to not need to
> take the time to setup more replication at the time of a new DB's
> creation.
>|||clustering would require stepping up to sql 200 enterprise edition would it
not? I'm stuck with two copies of standard addition making all the fun
tools a little out of reach it feels like.
the original server has a good raid system mirroring disks but the issue is
the OS is starting to get a little flakey after 3 years of hard use. A
complete second box to keep us running even if I manually have to tell
everyone to use the other box is the only thing that would keep me
comfortable.
"Joe Yong" <NO_jyong_SPAM@.scalabilityexperts.com> wrote in message
news:u0G4Jhp3FHA.3600@.TK2MSFTNGP12.phx.gbl...
> Nope, it's manual.
> I'd think hard about the manageability of a replication setup with 150
> database. There are other ways to split the load while providing
> redundancy. For example, you could setup a multi-instance failover cluster
> and make sure you mirror the disks (always a good practice anyway).
> Slightly higher cost in initial hardware purchase but IMHO, much less pain
> (consequently; lower cost) in the long run.
>
> joe.
> "jamie" <strider3700@.nospam.nospam> wrote in message
> news:%232hnndn3FHA.2676@.TK2MSFTNGP15.phx.gbl...
>>I have 1 sql 2000 server in place that has about 150 databases on it. Two
>>applications work access those databases half are for one application half
>>are for the other application. Since the server is becoming quite
>>overloaded lately I've decided to take another server with sql 2000 on it
>>and split one application off of the original server and place it's
>>databases on the second effectively splitting the load between two
>>servers.
>> Now I'd like to use replication to have both servers be almost identical
>> to give me some redundency in the hardware. Can I easily set up
>> replication so that server 1 and server 2 both contain the same databases
>> and the data is replicated between the two? I know I can go in and set
>> up a merge replication for each DB and this will work ( I've done this
>> with 2 db's already) but can I set it so that the server figures out
>> what DB's there are and replicates them? This will allow me to not
>> need to take the time to setup more replication at the time of a new DB's
>> creation.
>|||Hello,
SQL Standard Edition doesn't support cluster:
Features Supported by the Editions of SQL Server 2000
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_
ar_ts_8dbn.asp
However, you may do it by using SQLDMO for replication. Here are scripts
for transactional with vb.net and SQL DMO.
<http://www.replicationanswers.com/Script1.asp>
<http://www.replicationanswers.com/Script2.asp>
Here are some scripts to achieve similar ends using the ActiveX controls in
C# for transactional and merge.
<http://support.microsoft.com/default.aspx?scid=kb;EN-US;319649>
<http://support.microsoft.com/default.aspx?scid=kb;EN-US;319646>
Note: This response contains a reference to a third party World Wide Web
site. Microsoft is providing this information as a convenience to you.
Microsoft does not control these sites and has not tested any software or
information found on these sites; therefore, Microsoft cannot make any
representations regarding the quality, safety, or suitability of any
software or information found there. There are inherent dangers in the use
of any software found on the Internet, and Microsoft cautions you to make
sure that you completely understand the risk before retrieving any software
from the Internet.
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Is Replication of entire server possible?

I have 1 sql 2000 server in place that has about 150 databases on it. Two
applications work access those databases half are for one application half
are for the other application. Since the server is becoming quite
overloaded lately I've decided to take another server with sql 2000 on it
and split one application off of the original server and place it's
databases on the second effectively splitting the load between two servers.
Now I'd like to use replication to have both servers be almost identical to
give me some redundency in the hardware. Can I easily set up replication so
that server 1 and server 2 both contain the same databases and the data is
replicated between the two? I know I can go in and set up a merge
replication for each DB and this will work ( I've done this with 2 db's
already) but can I set it so that the server figures out what DB's there
are and replicates them? This will allow me to not need to take the time
to setup more replication at the time of a new DB's creation.
Nope, it's manual.
I'd think hard about the manageability of a replication setup with 150
database. There are other ways to split the load while providing redundancy.
For example, you could setup a multi-instance failover cluster and make sure
you mirror the disks (always a good practice anyway). Slightly higher cost
in initial hardware purchase but IMHO, much less pain (consequently; lower
cost) in the long run.
joe.
"jamie" <strider3700@.nospam.nospam> wrote in message
news:%232hnndn3FHA.2676@.TK2MSFTNGP15.phx.gbl...
>I have 1 sql 2000 server in place that has about 150 databases on it. Two
>applications work access those databases half are for one application half
>are for the other application. Since the server is becoming quite
>overloaded lately I've decided to take another server with sql 2000 on it
>and split one application off of the original server and place it's
>databases on the second effectively splitting the load between two servers.
> Now I'd like to use replication to have both servers be almost identical
> to give me some redundency in the hardware. Can I easily set up
> replication so that server 1 and server 2 both contain the same databases
> and the data is replicated between the two? I know I can go in and set
> up a merge replication for each DB and this will work ( I've done this
> with 2 db's already) but can I set it so that the server figures out what
> DB's there are and replicates them? This will allow me to not need to
> take the time to setup more replication at the time of a new DB's
> creation.
>
|||clustering would require stepping up to sql 200 enterprise edition would it
not? I'm stuck with two copies of standard addition making all the fun
tools a little out of reach it feels like.
the original server has a good raid system mirroring disks but the issue is
the OS is starting to get a little flakey after 3 years of hard use. A
complete second box to keep us running even if I manually have to tell
everyone to use the other box is the only thing that would keep me
comfortable.
"Joe Yong" <NO_jyong_SPAM@.scalabilityexperts.com> wrote in message
news:u0G4Jhp3FHA.3600@.TK2MSFTNGP12.phx.gbl...
> Nope, it's manual.
> I'd think hard about the manageability of a replication setup with 150
> database. There are other ways to split the load while providing
> redundancy. For example, you could setup a multi-instance failover cluster
> and make sure you mirror the disks (always a good practice anyway).
> Slightly higher cost in initial hardware purchase but IMHO, much less pain
> (consequently; lower cost) in the long run.
>
> joe.
> "jamie" <strider3700@.nospam.nospam> wrote in message
> news:%232hnndn3FHA.2676@.TK2MSFTNGP15.phx.gbl...
>
|||Hello,
SQL Standard Edition doesn't support cluster:
Features Supported by the Editions of SQL Server 2000
http://msdn.microsoft.com/library/de...us/architec/8_
ar_ts_8dbn.asp
However, you may do it by using SQLDMO for replication. Here are scripts
for transactional with vb.net and SQL DMO.
<http://www.replicationanswers.com/Script1.asp>
<http://www.replicationanswers.com/Script2.asp>
Here are some scripts to achieve similar ends using the ActiveX controls in
C# for transactional and merge.
<http://support.microsoft.com/default...b;EN-US;319649>
<http://support.microsoft.com/default...b;EN-US;319646>
Note: This response contains a reference to a third party World Wide Web
site. Microsoft is providing this information as a convenience to you.
Microsoft does not control these sites and has not tested any software or
information found on these sites; therefore, Microsoft cannot make any
representations regarding the quality, safety, or suitability of any
software or information found there. There are inherent dangers in the use
of any software found on the Internet, and Microsoft cautions you to make
sure that you completely understand the risk before retrieving any software
from the Internet.
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Is Replication of entire server possible?

I have 1 sql 2000 server in place that has about 150 databases on it. Two
applications work access those databases half are for one application half
are for the other application. Since the server is becoming quite
overloaded lately I've decided to take another server with sql 2000 on it
and split one application off of the original server and place it's
databases on the second effectively splitting the load between two servers.
Now I'd like to use replication to have both servers be almost identical to
give me some redundency in the hardware. Can I easily set up replication so
that server 1 and server 2 both contain the same databases and the data is
replicated between the two? I know I can go in and set up a merge
replication for each DB and this will work ( I've done this with 2 db's
already) but can I set it so that the server figures out what DB's there
are and replicates them? This will allow me to not need to take the time
to setup more replication at the time of a new DB's creation.Nope, it's manual.
I'd think hard about the manageability of a replication setup with 150
database. There are other ways to split the load while providing redundancy.
For example, you could setup a multi-instance failover cluster and make sure
you mirror the disks (always a good practice anyway). Slightly higher cost
in initial hardware purchase but IMHO, much less pain (consequently; lower
cost) in the long run.
joe.
"jamie" <strider3700@.nospam.nospam> wrote in message
news:%232hnndn3FHA.2676@.TK2MSFTNGP15.phx.gbl...
>I have 1 sql 2000 server in place that has about 150 databases on it. Two
>applications work access those databases half are for one application half
>are for the other application. Since the server is becoming quite
>overloaded lately I've decided to take another server with sql 2000 on it
>and split one application off of the original server and place it's
>databases on the second effectively splitting the load between two servers.
> Now I'd like to use replication to have both servers be almost identical
> to give me some redundency in the hardware. Can I easily set up
> replication so that server 1 and server 2 both contain the same databases
> and the data is replicated between the two? I know I can go in and set
> up a merge replication for each DB and this will work ( I've done this
> with 2 db's already) but can I set it so that the server figures out what
> DB's there are and replicates them? This will allow me to not need to
> take the time to setup more replication at the time of a new DB's
> creation.
>|||clustering would require stepping up to sql 200 enterprise edition would it
not? I'm stuck with two copies of standard addition making all the fun
tools a little out of reach it feels like.
the original server has a good raid system mirroring disks but the issue is
the OS is starting to get a little flakey after 3 years of hard use. A
complete second box to keep us running even if I manually have to tell
everyone to use the other box is the only thing that would keep me
comfortable.
"Joe Yong" <NO_jyong_SPAM@.scalabilityexperts.com> wrote in message
news:u0G4Jhp3FHA.3600@.TK2MSFTNGP12.phx.gbl...
> Nope, it's manual.
> I'd think hard about the manageability of a replication setup with 150
> database. There are other ways to split the load while providing
> redundancy. For example, you could setup a multi-instance failover cluster
> and make sure you mirror the disks (always a good practice anyway).
> Slightly higher cost in initial hardware purchase but IMHO, much less pain
> (consequently; lower cost) in the long run.
>
> joe.
> "jamie" <strider3700@.nospam.nospam> wrote in message
> news:%232hnndn3FHA.2676@.TK2MSFTNGP15.phx.gbl...
>|||Hello,
SQL Standard Edition doesn't support cluster:
Features Supported by the Editions of SQL Server 2000
http://msdn.microsoft.com/library/d...-us/architec/8_
ar_ts_8dbn.asp
However, you may do it by using SQLDMO for replication. Here are scripts
for transactional with vb.net and SQL DMO.
<http://www.replicationanswers.com/Script1.asp>
<http://www.replicationanswers.com/Script2.asp>
Here are some scripts to achieve similar ends using the ActiveX controls in
C# for transactional and merge.
<http://support.microsoft.com/defaul...kb;EN-US;319649>
<http://support.microsoft.com/defaul...kb;EN-US;319646>
Note: This response contains a reference to a third party World Wide Web
site. Microsoft is providing this information as a convenience to you.
Microsoft does not control these sites and has not tested any software or
information found on these sites; therefore, Microsoft cannot make any
representations regarding the quality, safety, or suitability of any
software or information found there. There are inherent dangers in the use
of any software found on the Internet, and Microsoft cautions you to make
sure that you completely understand the risk before retrieving any software
from the Internet.
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Is ODBC right for me?

I am a forum newbie - appreciate your patience.
We are embarking on a new adventure to scale up an Access DB up to SQL
Server. We are not going to use the upsizing wizardry, but have chosen to
build native SQL tables instead.
The question is which route to go with the Front-End?
- Access 2003: SQL tables linked via ODBC
- Access Data Project(ADP)
- Access 2007: SQL tables linked (MDB or ACCDB?)
We were prepared to move ahead with creating an Access Data Project using
Access 2003, but then I read the following on microsoft.com ...
" Access creates front-end applications that leverage SQL Server as a
backend data source. Access forms and reports can be optimized as efficiently
as Visual Basic front-end for SQL Server. Office Access 2007 offers two ways
to connect to SQL Server data: linking to SQL Server and Access Data Projects
(ADPs).
The preferred way to connect to SQL Server is MDB file format or ACCDB file
format. This enables you to use the full flexibility of local tables and
local queries, while leveraging the full power of SQL Server. In addition,
MDB and ACCDB files link to multiple SQL Servers and a wide variety of other
data sources. Office Access 2007 contains many new features available in both
MDB and ACCDB file formats, but only a subset of those features are available
in ADPs. "
This seems to state that best practice is to use linked tables. Is this true
in the "real world"? Isn't using the layers of ODBC going to make for a
slower app? Is ADP a technology that may not be supported by MS in the near
future?
Where would I find info to learn how to optimize Access/ODBC to work as well
as a VB front-end as stated in the quote above?
Your thoughts would be appreciated!
Hi Dirn,
I'm not an Access guru but one thing I found when working with an ADP
against SQL 2005 is that the database must be in SQL 2000 compatible format
since an Access ADP isn't compatible with any schema but DBO.
You can use SQL Express if the amount of your data is within its size
limitations.
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@.cindywinegarden.com
"DIRN" <DIRN@.discussions.microsoft.com> wrote in message
news:BB082997-C04F-4C65-BF6F-496C53A07E9D@.microsoft.com...

> ... The question is which route to go with the Front-End?
> - Access 2003: SQL tables linked via ODBC
> - Access Data Project(ADP)
> - Access 2007: SQL tables linked (MDB or ACCDB?) ...
|||Cindy & Van T.
Thanks for taking time to respond to my question. I still have three
lindering wunderabouts:
- Which approach (ADP vs. Linked Tables) to a Access FE / SQL Server BE app
does MS consider best practice with the current revs of their Access & SQL
products?
- If, linked tables - how to choose between MDB and ACCDB?
- What did they really mean in the quote above about making an Access
FE as effiecient as a VB FE?
If you have any additional info or know of a resource who might be of
assistance please respond further.
Your help is so incredibly appreciated.
Best Regards,
David Hogan
"Van T. Dinh" wrote:

> ADP is still supported in Access 2007 which means that it will be supported
> to at least end of 2016 (5 years mainstream support + 5 years extended
> support).
> I am sure there are lots of ADPs in the real-world but the majority of
> Access databases still use MDB format.
> In my experience, there is no problems using ODBC provided you follow a few
> simple techniques on reducing the amount of data being pulled through the
> network (this applies to all Client / Server database applications, anyway).
> There are a number of articles in the Microsoft KB, e.g:
> http://support.microsoft.com/kb/208858
> and
> http://support.microsoft.com/kb/209551
> --
> HTH
> Van T. Dinh
> MVP (Access)
>
> "DIRN" <DIRN@.discussions.microsoft.com> wrote in message
> news:BB082997-C04F-4C65-BF6F-496C53A07E9D@.microsoft.com...
>
>
|||Van
Thanks again for the wisdom. Access "Vision" document was very helpful. I
will begin to start looking for material on how to code an application
optimzed for a SQL server BE. I think your thoughts on minimizing network
traffic are right on.
I appreciate your generous assistance!
David
"Van T. Dinh" wrote:

> * Definitely Linked Tables from what I have heard for some time now. When
> Access 2000 & Access 2002 came out, Microsoft was pushing ADP but it is
> definitely not the case with Access 2007. In fact, I think the new ACCDB
> format utilises the new JET engine (and JET engine was supposed to be in
> maintenance mode only!)
> See http://www.tinyurl.com/y2yjzn
> and have a look at the first paragraph of page 10 from the Microsoft
> document above and I am sure you can infer what Microsoft impplies.
> * I haven't done much testing with Access 2007 + new ACCDB format but AFAIK,
> you need A2007 to use ACCDB. If you have a uniform enviroment where
> everyone has A2007, ACCDB will probably be better (since the Microsoft
> Access / JET engineers have exclusive control of the engine which enable
> them to tune it to suit Access while the JET 4 engine is shared in the OS so
> they can't change it easily). If you have a mixed environment, I think it
> is safer to stick to the MDB / MDE format for the moment.
> * I am confused about "VB" nowadays since it could mean either the old VB6
> or VB.Net or VB2005 but don't you have to use some sort of interface such as
> OLEDB to access data regardles of the BE engine? In my databases, I used
> ODBC-linked Tables (for Forms , Reports, etc ...) but in code, I use mostly
> ADO / OLEDB for SQL Server to access and manipulate data and the speed is
> quite fast.
> I think the major bottleneck in most database application is network
> transmission bandwidth, not the database engine or the interface to the
> database (I once added a million simple records one at a time to a JET Table
> using DAO Recordset code and it took only about 3-4 seconds). I concentrate
> on reducing the need to transfer data across the network to a minimum, e.g.
> getting the SQL Server to do most processing and only pass back the
> processed result to Access FE.
> --
> HTH
> Van T. Dinh
> MVP (Access)
>
> "DIRN" <DIRN@.discussions.microsoft.com> wrote in message
> news:04CD459E-1686-41C1-90AF-A773661CF283@.microsoft.com...
>
>

Is ODBC right for me?

I am a forum newbie - appreciate your patience.
We are embarking on a new adventure to scale up an Access DB up to SQL
Server. We are not going to use the upsizing wizardry, but have chosen to
build native SQL tables instead.
The question is which route to go with the Front-End?
- Access 2003: SQL tables linked via ODBC
- Access Data Project(ADP)
- Access 2007: SQL tables linked (MDB or ACCDB?)
We were prepared to move ahead with creating an Access Data Project using
Access 2003, but then I read the following on microsoft.com ...
" Access creates front-end applications that leverage SQL Server as a
backend data source. Access forms and reports can be optimized as efficientl
y
as Visual Basic front-end for SQL Server. Office Access 2007 offers two ways
to connect to SQL Server data: linking to SQL Server and Access Data Project
s
(ADPs).
The preferred way to connect to SQL Server is MDB file format or ACCDB file
format. This enables you to use the full flexibility of local tables and
local queries, while leveraging the full power of SQL Server. In addition,
MDB and ACCDB files link to multiple SQL Servers and a wide variety of other
data sources. Office Access 2007 contains many new features available in bot
h
MDB and ACCDB file formats, but only a subset of those features are availabl
e
in ADPs. "
This seems to state that best practice is to use linked tables. Is this true
in the "real world"? Isn't using the layers of ODBC going to make for a
slower app? Is ADP a technology that may not be supported by MS in the near
future?
Where would I find info to learn how to optimize Access/ODBC to work as well
as a VB front-end as stated in the quote above?
Your thoughts would be appreciated!Hi Dirn,
I'm not an Access guru but one thing I found when working with an ADP
against SQL 2005 is that the database must be in SQL 2000 compatible format
since an Access ADP isn't compatible with any schema but DBO.
You can use SQL Express if the amount of your data is within its size
limitations.
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@.cindywinegarden.com
"DIRN" <DIRN@.discussions.microsoft.com> wrote in message
news:BB082997-C04F-4C65-BF6F-496C53A07E9D@.microsoft.com...

> ... The question is which route to go with the Front-End?
> - Access 2003: SQL tables linked via ODBC
> - Access Data Project(ADP)
> - Access 2007: SQL tables linked (MDB or ACCDB?) ...|||ADP is still supported in Access 2007 which means that it will be supported
to at least end of 2016 (5 years mainstream support + 5 years extended
support).
I am sure there are lots of ADPs in the real-world but the majority of
Access databases still use MDB format.
In my experience, there is no problems using ODBC provided you follow a few
simple techniques on reducing the amount of data being pulled through the
network (this applies to all Client / Server database applications, anyway).
There are a number of articles in the Microsoft KB, e.g:
http://support.microsoft.com/kb/208858
and
http://support.microsoft.com/kb/209551
HTH
Van T. Dinh
MVP (Access)
"DIRN" <DIRN@.discussions.microsoft.com> wrote in message
news:BB082997-C04F-4C65-BF6F-496C53A07E9D@.microsoft.com...
>I am a forum newbie - appreciate your patience.
> We are embarking on a new adventure to scale up an Access DB up to SQL
> Server. We are not going to use the upsizing wizardry, but have chosen to
> build native SQL tables instead.
> The question is which route to go with the Front-End?
> - Access 2003: SQL tables linked via ODBC
> - Access Data Project(ADP)
> - Access 2007: SQL tables linked (MDB or ACCDB?)
> We were prepared to move ahead with creating an Access Data Project using
> Access 2003, but then I read the following on microsoft.com ...
> " Access creates front-end applications that leverage SQL Server as a
> backend data source. Access forms and reports can be optimized as
> efficiently
> as Visual Basic front-end for SQL Server. Office Access 2007 offers two
> ways
> to connect to SQL Server data: linking to SQL Server and Access Data
> Projects
> (ADPs).
> The preferred way to connect to SQL Server is MDB file format or ACCDB
> file
> format. This enables you to use the full flexibility of local tables and
> local queries, while leveraging the full power of SQL Server. In addition,
> MDB and ACCDB files link to multiple SQL Servers and a wide variety of
> other
> data sources. Office Access 2007 contains many new features available in
> both
> MDB and ACCDB file formats, but only a subset of those features are
> available
> in ADPs. "
> This seems to state that best practice is to use linked tables. Is this
> true
> in the "real world"? Isn't using the layers of ODBC going to make for a
> slower app? Is ADP a technology that may not be supported by MS in the
> near
> future?
> Where would I find info to learn how to optimize Access/ODBC to work as
> well
> as a VB front-end as stated in the quote above?
> Your thoughts would be appreciated!|||Cindy & Van T.
Thanks for taking time to respond to my question. I still have three
lindering wunderabouts:
- Which approach (ADP vs. Linked Tables) to a Access FE / SQL Server BE app
does MS consider best practice with the current revs of their Access & SQL
products?
- If, linked tables - how to choose between MDB and ACCDB?
- What did they really mean in the quote above about making an Access
FE as effiecient as a VB FE?
If you have any additional info or know of a resource who might be of
assistance please respond further.
Your help is so incredibly appreciated.
Best Regards,
David Hogan
"Van T. Dinh" wrote:

> ADP is still supported in Access 2007 which means that it will be supporte
d
> to at least end of 2016 (5 years mainstream support + 5 years extended
> support).
> I am sure there are lots of ADPs in the real-world but the majority of
> Access databases still use MDB format.
> In my experience, there is no problems using ODBC provided you follow a fe
w
> simple techniques on reducing the amount of data being pulled through the
> network (this applies to all Client / Server database applications, anyway
).
> There are a number of articles in the Microsoft KB, e.g:
> http://support.microsoft.com/kb/208858
> and
> http://support.microsoft.com/kb/209551
> --
> HTH
> Van T. Dinh
> MVP (Access)
>
> "DIRN" <DIRN@.discussions.microsoft.com> wrote in message
> news:BB082997-C04F-4C65-BF6F-496C53A07E9D@.microsoft.com...
>
>|||* Definitely Linked Tables from what I have heard for some time now. When
Access 2000 & Access 2002 came out, Microsoft was pushing ADP but it is
definitely not the case with Access 2007. In fact, I think the new ACCDB
format utilises the new JET engine (and JET engine was supposed to be in
maintenance mode only!)
See http://www.tinyurl.com/y2yjzn
and have a look at the first paragraph of page 10 from the Microsoft
document above and I am sure you can infer what Microsoft impplies.
* I haven't done much testing with Access 2007 + new ACCDB format but AFAIK,
you need A2007 to use ACCDB. If you have a uniform enviroment where
everyone has A2007, ACCDB will probably be better (since the Microsoft
Access / JET engineers have exclusive control of the engine which enable
them to tune it to suit Access while the JET 4 engine is shared in the OS so
they can't change it easily). If you have a mixed environment, I think it
is safer to stick to the MDB / MDE format for the moment.
* I am confused about "VB" nowadays since it could mean either the old VB6
or VB.Net or VB2005 but don't you have to use some sort of interface such as
OLEDB to access data regardles of the BE engine? In my databases, I used
ODBC-linked Tables (for Forms , Reports, etc ...) but in code, I use mostly
ADO / OLEDB for SQL Server to access and manipulate data and the speed is
quite fast.
I think the major bottleneck in most database application is network
transmission bandwidth, not the database engine or the interface to the
database (I once added a million simple records one at a time to a JET Table
using DAO Recordset code and it took only about 3-4 seconds). I concentrate
on reducing the need to transfer data across the network to a minimum, e.g.
getting the SQL Server to do most processing and only pass back the
processed result to Access FE.
HTH
Van T. Dinh
MVP (Access)
"DIRN" <DIRN@.discussions.microsoft.com> wrote in message
news:04CD459E-1686-41C1-90AF-A773661CF283@.microsoft.com...
> Cindy & Van T.
> Thanks for taking time to respond to my question. I still have three
> lindering wunderabouts:
> - Which approach (ADP vs. Linked Tables) to a Access FE / SQL Server BE
> app
> does MS consider best practice with the current revs of their Access & SQL
> products?
> - If, linked tables - how to choose between MDB and ACCDB?
> - What did they really mean in the quote above about making an Access
> FE as effiecient as a VB FE?
> If you have any additional info or know of a resource who might be of
> assistance please respond further.
> Your help is so incredibly appreciated.
> Best Regards,
> David Hogan|||Van
Thanks again for the wisdom. Access "Vision" document was very helpful. I
will begin to start looking for material on how to code an application
optimzed for a SQL server BE. I think your thoughts on minimizing network
traffic are right on.
I appreciate your generous assistance!
David
"Van T. Dinh" wrote:

> * Definitely Linked Tables from what I have heard for some time now. When
> Access 2000 & Access 2002 came out, Microsoft was pushing ADP but it is
> definitely not the case with Access 2007. In fact, I think the new ACCDB
> format utilises the new JET engine (and JET engine was supposed to be in
> maintenance mode only!)
> See http://www.tinyurl.com/y2yjzn
> and have a look at the first paragraph of page 10 from the Microsoft
> document above and I am sure you can infer what Microsoft impplies.
> * I haven't done much testing with Access 2007 + new ACCDB format but AFAI
K,
> you need A2007 to use ACCDB. If you have a uniform enviroment where
> everyone has A2007, ACCDB will probably be better (since the Microsoft
> Access / JET engineers have exclusive control of the engine which enable
> them to tune it to suit Access while the JET 4 engine is shared in the OS
so
> they can't change it easily). If you have a mixed environment, I think it
> is safer to stick to the MDB / MDE format for the moment.
> * I am confused about "VB" nowadays since it could mean either the old VB6
> or VB.Net or VB2005 but don't you have to use some sort of interface such
as
> OLEDB to access data regardles of the BE engine? In my databases, I used
> ODBC-linked Tables (for Forms , Reports, etc ...) but in code, I use mostl
y
> ADO / OLEDB for SQL Server to access and manipulate data and the speed is
> quite fast.
> I think the major bottleneck in most database application is network
> transmission bandwidth, not the database engine or the interface to the
> database (I once added a million simple records one at a time to a JET Tab
le
> using DAO Recordset code and it took only about 3-4 seconds). I concentra
te
> on reducing the need to transfer data across the network to a minimum, e.g
.
> getting the SQL Server to do most processing and only pass back the
> processed result to Access FE.
> --
> HTH
> Van T. Dinh
> MVP (Access)
>
> "DIRN" <DIRN@.discussions.microsoft.com> wrote in message
> news:04CD459E-1686-41C1-90AF-A773661CF283@.microsoft.com...
>
>|||You're welcome ... Glad to help ...
HTH
Van T. Dinh
MVP (Access)
"DIRN" <DIRN@.discussions.microsoft.com> wrote in message
news:7444F6AD-25F7-4DB5-B9E2-FD249F9C4C40@.microsoft.com...
> Van
> Thanks again for the wisdom. Access "Vision" document was very helpful. I
> will begin to start looking for material on how to code an application
> optimzed for a SQL server BE. I think your thoughts on minimizing network
> traffic are right on.
> I appreciate your generous assistance!
> David
>