Hi all,
Our client wants some guidance on how to handle backup files created by
SQL Server 2005 for our product.
I was thinking that we would create
1) a nightly backup to say e:\backup
2) create a transaction log backup every hour to e:\backup
Now is it best to create a new file each time - or is it best to
overwrite the original file?
Reason I ask is the client will need to them back these files up to an
external tape drive.
It will probably be easier for them if they have the same 20+ files
each time (ie. one full backup, and hourly transaction files)
If the filenames incremented, then they would need to delete older
files that have been backed up (which can invite user error)
What are peoples opinions on this? Is there a "good way" to do this'
Cheers
DavidHi
Well, in our shop we do a full backup every night to c:\ and log backup
every 15 minutes to d:\
A log file may contain many files so you'll need to specify i like
drop database test
go
create database test
GO
create table test..test(id int identity)
insert test..test default values
backup database test to disk = 'd:\db.bak' WITH INIT
insert test..test default values
backup log test to disk = 'd:\log.bak'WITH INIT
insert test..test default values
backup log test to disk = 'd:\log.bak' WITH NOINIT
GO
RESTORE DATABASE test FROM disk = 'd:\db.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 2, recovery
...............
.............
<davconts@.gmail.com> wrote in message
news:1151992078.629506.142340@.p79g2000cwp.googlegroups.com...
> Hi all,
> Our client wants some guidance on how to handle backup files created by
> SQL Server 2005 for our product.
> I was thinking that we would create
> 1) a nightly backup to say e:\backup
> 2) create a transaction log backup every hour to e:\backup
> Now is it best to create a new file each time - or is it best to
> overwrite the original file?
> Reason I ask is the client will need to them back these files up to an
> external tape drive.
> It will probably be easier for them if they have the same 20+ files
> each time (ie. one full backup, and hourly transaction files)
> If the filenames incremented, then they would need to delete older
> files that have been backed up (which can invite user error)
> What are peoples opinions on this? Is there a "good way" to do this'
> Cheers
> David
>|||This is a multi-part message in MIME format.
--070701000301070106090101
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
davconts@.gmail.com wrote:
> Hi all,
> Our client wants some guidance on how to handle backup files created by
> SQL Server 2005 for our product.
> I was thinking that we would create
> 1) a nightly backup to say e:\backup
> 2) create a transaction log backup every hour to e:\backup
> Now is it best to create a new file each time - or is it best to
> overwrite the original file?
> Reason I ask is the client will need to them back these files up to an
> external tape drive.
> It will probably be easier for them if they have the same 20+ files
> each time (ie. one full backup, and hourly transaction files)
> If the filenames incremented, then they would need to delete older
> files that have been backed up (which can invite user error)
> What are peoples opinions on this? Is there a "good way" to do this'
> Cheers
> David
>
Hi David
I don't think there's a single "good way" to do it. It pretty much
depends on personal preferences and I don't think one mtethod is better
than others.
Bottomline is that no matter how you decide to do it, it will require
some degree of knowledge on how it works in order to maintain it and
restore a backup in case of a failure.
I prefer to only have one backup in each file and then name the files
with a name that are composed by date and a forth running number( for
the log files). This is because I like to know that each file only
contains one backup so when I need to restore I have to find the full
backup file and then the corresponding logfile 1, 2, 3... up to the
point in time I need. If I had more backups in the same file, I'd first
have to find out which file number I had to restore for the fulle backup
and then I'd also have to find out from which filenumber the logfile
backup would start. Another thing that I don't like about having many
backups in one file, is when the backups starts to get too big. In that
case you suddenly get some quite big backup files that can be a pain to
"administer". The backup of one of our databases are currently around 85
GB and if you have a number of these in the same file, it will easily
get quite "un-handy".
I think the biggest advantage of having several files in one backup
file, is that it's easier to let SQL server delete old backups in the
file. If you have seperate files with naming by date, you'll have to do
some scripting to delete the files.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator
--070701000301070106090101
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:davconts@.gmail.com">davconts@.gmail.com</a> wrote:
<blockquote
cite="mid1151992078.629506.142340@.p79g2000cwp.googlegroups.com"
type="cite">
<pre wrap="">Hi all,
Our client wants some guidance on how to handle backup files created by
SQL Server 2005 for our product.
I was thinking that we would create
1) a nightly backup to say e:\backup
2) create a transaction log backup every hour to e:\backup
Now is it best to create a new file each time - or is it best to
overwrite the original file?
Reason I ask is the client will need to them back these files up to an
external tape drive.
It will probably be easier for them if they have the same 20+ files
each time (ie. one full backup, and hourly transaction files)
If the filenames incremented, then they would need to delete older
files that have been backed up (which can invite user error)
What are peoples opinions on this? Is there a "good way" to do this'
Cheers
David
</pre>
</blockquote>
<font size="-1"><font face="Arial">Hi David<br>
<br>
I don't think there's a single "good way" to do it. It pretty much
depends on personal preferences and I don't think one mtethod is better
than others.<br>
Bottomline is that no matter how you decide to do it, it will require
some degree of knowledge on how it works in order to maintain it and
restore a backup in case of a failure.<br>
I prefer to only have one backup in each file and then name the files
with a name that are composed by date and a forth running number( for
the log files). This is because I like to know that each file only
contains one backup so when I need to restore I have to find the full
backup file and then the corresponding logfile 1, 2, 3... up to the
point in time I need. If I had more backups in the same file, I'd first
have to find out which file number I had to restore for the fulle
backup and then I'd also have to find out from which filenumber the
logfile backup would start. Another thing that I don't like about
having many backups in one file, is when the backups starts to get too
big. In that case you suddenly get some quite big backup files that can
be a pain to "administer". The backup of one of our databases are
currently around 85 GB and if you have a number of these in the same
file, it will easily get quite "un-handy".<br>
I think the biggest advantage of having several files in one backup
file, is that it's easier to let SQL server delete old backups in the
file. If you have seperate files with naming by date, you'll have to do
some scripting to delete the files.<br>
<br>
<br>
-- <br>
Regards<br>
Steen Schlüter Persson<br>
Databaseadministrator / Systemadministrator<br>
<br>
</font></font>
</body>
</html>
--070701000301070106090101--|||In addition to the other posts:
I agree that there is no "best way" for this. Understand the backup command and the options (mainly
INIT and NOINIT). Then you can consider:
Maintenance plans (to do backups). It creates a new file for each time a backup is taken and also
remove files older than X days.
Do above with your own TSQL code. This opens the ability for further customization.
Have several backups on same file. For instance, have a Monday backup file which you backup to on
Mondays and overwrite each time. This will give you 7 days worth of backup generations. Or some
variation of this. One advantage is reduced number of files. Just make sure that the people doing
restore know about RESTORE HEADERONLY and FILELISTONY and the FILE option.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<davconts@.gmail.com> wrote in message news:1151992078.629506.142340@.p79g2000cwp.googlegroups.com...
> Hi all,
> Our client wants some guidance on how to handle backup files created by
> SQL Server 2005 for our product.
> I was thinking that we would create
> 1) a nightly backup to say e:\backup
> 2) create a transaction log backup every hour to e:\backup
> Now is it best to create a new file each time - or is it best to
> overwrite the original file?
> Reason I ask is the client will need to them back these files up to an
> external tape drive.
> It will probably be easier for them if they have the same 20+ files
> each time (ie. one full backup, and hourly transaction files)
> If the filenames incremented, then they would need to delete older
> files that have been backed up (which can invite user error)
> What are peoples opinions on this? Is there a "good way" to do this'
> Cheers
> David
>
Showing posts with label create. Show all posts
Showing posts with label create. Show all posts
Tuesday, March 27, 2012
Advice on dynamically generating RDL files
I would prefer to dynamically create my RDL files on the fly instead of
building them ahead of time. My question is this: the creation of these files
would occur on the server, but the reporting server may or may not be the
same box. Is the best way to upload the RDL file to use the web service
interface and import it, or are there other options here?
Thanks!
MichaelHi moflaherty,
You can create your RDL on the fly, the creation of the files can be
located at the client or server side, it does not matter as long you can
create the XML/RDL after that you can use the WS interface to upload them
into the server. In this example
http://www.rdlcomponents.com/ASPExamples/Default.aspx?sm=b1_a
You can see how the RDL creation process is isolated from the upload process.
Thanks
JErry
"moflaherty" wrote:
> I would prefer to dynamically create my RDL files on the fly instead of
> building them ahead of time. My question is this: the creation of these files
> would occur on the server, but the reporting server may or may not be the
> same box. Is the best way to upload the RDL file to use the web service
> interface and import it, or are there other options here?
> Thanks!
> Michael
>sql
building them ahead of time. My question is this: the creation of these files
would occur on the server, but the reporting server may or may not be the
same box. Is the best way to upload the RDL file to use the web service
interface and import it, or are there other options here?
Thanks!
MichaelHi moflaherty,
You can create your RDL on the fly, the creation of the files can be
located at the client or server side, it does not matter as long you can
create the XML/RDL after that you can use the WS interface to upload them
into the server. In this example
http://www.rdlcomponents.com/ASPExamples/Default.aspx?sm=b1_a
You can see how the RDL creation process is isolated from the upload process.
Thanks
JErry
"moflaherty" wrote:
> I would prefer to dynamically create my RDL files on the fly instead of
> building them ahead of time. My question is this: the creation of these files
> would occur on the server, but the reporting server may or may not be the
> same box. Is the best way to upload the RDL file to use the web service
> interface and import it, or are there other options here?
> Thanks!
> Michael
>sql
Sunday, March 25, 2012
advice
Hi,
Apologies if this sounds dumb but I'm very new to this.
I'm just trying to create a simple login page (
http://aarkenuk.co.uk/barnesg/login.php) In my MySQL databases I have the
correct database selected, but it's the users I'm struggling with. You get
like a front end with my hosting space (CpanelX) and in there I've created
user "admin" password "admin" but I cannot seem to login.
This is what I see in the front end..
Users in test
barnes_admin (Privileges: ALL PRIVILEGES)
Connection Strings
Perl $dbh =
DBI->connect("DBI:mysql:barnes_test:localhost","barnes_admin","<PASSWORD
HERE>");
PHP $dbh=mysql_connect ("localhost", "barnes_admin", "<PASSWORD
HERE>") or die ('I cannot connect to the database because: ' .
mysql_error());
mysql_select_db ("barnes_test");
Is there anything more I need to do? Do you login with barnes_admin or just
admin? (I've tried both) and why does it created barnes_admin and not just
admin? Also what are the connection strings for? do I need to do anything
with these?
Thanks very much in advance for any advice..
Gary"gjb" <Kookookaachoo@.hotmail.com> wrote in message
news:CkZLf.26271$wl.7873@.text.news.blueyonder.co.u k...
> Hi,
> Apologies if this sounds dumb but I'm very new to this.
> I'm just trying to create a simple login page (
> http://aarkenuk.co.uk/barnesg/login.php) In my MySQL databases I have the
> correct database selected, but it's the users I'm struggling with. You get
> like a front end with my hosting space (CpanelX) and in there I've created
> user "admin" password "admin" but I cannot seem to login.
This is a Microsoft SQL Server group, not MySQL group so you're probably not
going to find as much help here as you'd ilke.
However, I would say taht you probably don't want an admin being able to
access your database from a webpage simply for security reasons.
> This is what I see in the front end..
> Users in test
> barnes_admin (Privileges: ALL PRIVILEGES)
> Connection Strings
> Perl $dbh =
> DBI->connect("DBI:mysql:barnes_test:localhost","barnes_admin","<PASSWORD
> HERE>");
> PHP $dbh=mysql_connect ("localhost", "barnes_admin", "<PASSWORD
> HERE>") or die ('I cannot connect to the database because: ' .
> mysql_error());
> mysql_select_db ("barnes_test");
>
> Is there anything more I need to do? Do you login with barnes_admin or
just
> admin? (I've tried both) and why does it created barnes_admin and not just
> admin? Also what are the connection strings for? do I need to do anything
> with these?
> Thanks very much in advance for any advice..
> Gary|||gjb (Kookookaachoo@.hotmail.com) writes:
> Apologies if this sounds dumb but I'm very new to this.
> I'm just trying to create a simple login page (
> http://aarkenuk.co.uk/barnesg/login.php) In my MySQL databases I have the
> correct database selected, but it's the users I'm struggling with. You get
> like a front end with my hosting space (CpanelX) and in there I've created
> user "admin" password "admin" but I cannot seem to login.
I don't know whether the best place for this question is a newsgroup
about PHP or MySQL, but thius group is about Microsoft SQL Server, and your
chances to get help are not fantastic.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Ah, again apologies for my ignorance, I wasn't aware there was a
differance...!
the admin thing was just a test login, but couldn't get it to work. I've
spotted a mysql newsgroup which I will post in...
Many thanks for the help.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:6OZLf.3901$5M6.2416@.newsread2.news.atl.earthl ink.net...
> "gjb" <Kookookaachoo@.hotmail.com> wrote in message
> news:CkZLf.26271$wl.7873@.text.news.blueyonder.co.u k...
>> Hi,
>>
>> Apologies if this sounds dumb but I'm very new to this.
>>
>> I'm just trying to create a simple login page (
>> http://aarkenuk.co.uk/barnesg/login.php) In my MySQL databases I have the
>> correct database selected, but it's the users I'm struggling with. You
>> get
>> like a front end with my hosting space (CpanelX) and in there I've
>> created
>> user "admin" password "admin" but I cannot seem to login.
>>
>
> This is a Microsoft SQL Server group, not MySQL group so you're probably
> not
> going to find as much help here as you'd ilke.
>
> However, I would say taht you probably don't want an admin being able to
> access your database from a webpage simply for security reasons.
>
>> This is what I see in the front end..
>>
>> Users in test
>> barnes_admin (Privileges: ALL PRIVILEGES)
>>
>> Connection Strings
>> Perl $dbh =
>> DBI->connect("DBI:mysql:barnes_test:localhost","barnes_admin","<PASSWORD
>> HERE>");
>> PHP $dbh=mysql_connect ("localhost", "barnes_admin", "<PASSWORD
>> HERE>") or die ('I cannot connect to the database because: ' .
>> mysql_error());
>> mysql_select_db ("barnes_test");
>>
>>
>>
>> Is there anything more I need to do? Do you login with barnes_admin or
> just
>> admin? (I've tried both) and why does it created barnes_admin and not
>> just
>> admin? Also what are the connection strings for? do I need to do anything
>> with these?
>>
>> Thanks very much in advance for any advice..
>>
>> Gary
>>
>>
>>|||I'd learn about the differences between the two first! Its useful to know
the advantages and disadvantages of both.
Cheers, Ash
"gjb" <Kookookaachoo@.hotmail.com> wrote in message
news:4SZLf.26282$wl.20042@.text.news.blueyonder.co. uk...
> Ah, again apologies for my ignorance, I wasn't aware there was a
> differance...!
> the admin thing was just a test login, but couldn't get it to work. I've
> spotted a mysql newsgroup which I will post in...
> Many thanks for the help.
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
> message news:6OZLf.3901$5M6.2416@.newsread2.news.atl.earthl ink.net...
>>
>> "gjb" <Kookookaachoo@.hotmail.com> wrote in message
>> news:CkZLf.26271$wl.7873@.text.news.blueyonder.co.u k...
>>> Hi,
>>>
>>> Apologies if this sounds dumb but I'm very new to this.
>>>
>>> I'm just trying to create a simple login page (
>>> http://aarkenuk.co.uk/barnesg/login.php) In my MySQL databases I have
>>> the
>>> correct database selected, but it's the users I'm struggling with. You
>>> get
>>> like a front end with my hosting space (CpanelX) and in there I've
>>> created
>>> user "admin" password "admin" but I cannot seem to login.
>>>
>>
>>
>> This is a Microsoft SQL Server group, not MySQL group so you're probably
>> not
>> going to find as much help here as you'd ilke.
>>
>>
>> However, I would say taht you probably don't want an admin being able to
>> access your database from a webpage simply for security reasons.
>>
>>
>>> This is what I see in the front end..
>>>
>>> Users in test
>>> barnes_admin (Privileges: ALL PRIVILEGES)
>>>
>>> Connection Strings
>>> Perl $dbh =
>>> DBI->connect("DBI:mysql:barnes_test:localhost","barnes_admin","<PASSWORD
>>> HERE>");
>>> PHP $dbh=mysql_connect ("localhost", "barnes_admin", "<PASSWORD
>>> HERE>") or die ('I cannot connect to the database because: ' .
>>> mysql_error());
>>> mysql_select_db ("barnes_test");
>>>
>>>
>>>
>>> Is there anything more I need to do? Do you login with barnes_admin or
>> just
>>> admin? (I've tried both) and why does it created barnes_admin and not
>>> just
>>> admin? Also what are the connection strings for? do I need to do
>>> anything
>>> with these?
>>>
>>> Thanks very much in advance for any advice..
>>>
>>> Gary
>>>
>>>
>>>
>>
>>
Thursday, March 22, 2012
AdventureWorks Sample Reports
Hi folks,
Newbie question. Anyone knows how to create dropdown selection boxes in a report just like in the Employee Sales Summary report? Cheers!
Moving thread to the Reporting Services Group|||If you install the sample reports, you can just open them in report designer and investigate the Report - Report Parameter settings in the VS menu. The Employee Sales Summary report uses a query to determine the valid values list of the parameter - which results in the drop down being shown.
Alternatively, this MSDN topic should get you started: http://msdn2.microsoft.com/en-us/library/ms155917.aspx
-- Robert
Tuesday, March 20, 2012
Advantages of using nonclustered index after using clustered index on one table
Hi everyone,
When we create a clustered index firstly, and then is it advantageous to create another index which is nonclustered ?
In my opinion, yes it is. Because, since we use clustered index first, our rows are sorted and so while using nonclustered index on this data file, finding adress of the record on this sorted data is really easier than finding adress of the record on unsorted data, is not it ?
Thanks
Yes, you are correct. A nonclustered index like an index in a book "points" to a particular record which makes them ideal for exact matches queries. A NCI will either point to a rowid in a heap situation or the key in a CI-based table.
BOL 2005 excerpt:
If the underlying table is sorted using a clustered index, the location is the clustering key value; otherwise, the location is the row ID (RID) comprised of the file number, page number, and slot number of the row
HTH,
Derek
|||see more info on the other post
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=522859&SiteID=1
creation of Clustered index must happen first because
if NCIs are created first. The creation of a clustered index later will
force the recreation of all the NCIs to force it to use CI's rather than RID as locator
thereby causing a lot of trouble in the database.
|||
It is not entirely accurate that rows are sorted and stored in a clustered index. The b-tree structure ensures logical ordering of the rows and physically the rows may not be sorted in the same manner or present next to one another in a page for example. Please take a look at the link below for series of BOL topics on indexes:
http://msdn2.microsoft.com/en-us/library/ms189271.aspx
Monday, March 19, 2012
Advantage Database Server
I have to pass some data from Advantage Database Server (a database wich is
used generally with Delphy) to SQL-Server.
In order to create a Linked Server from SQL-Server to the above mentioned da
tabase- what should I do?
If there is an ODBC for the database- should it be installed on the server o
f the SQL-Server or on my terminal?Yeah...if I remember right from using it before, you need to
install the drivers and ole db provider on SQL Server. Then
you can create the linked server to the Advantage server.
They should have information in the help files on this or
else on the web site.
-Sue
On Wed, 23 Feb 2005 19:41:08 +0200, "Geri Reshef"
<gershon_reshef@.recanati-alum.tau.ac.il> wrote:
>I have to pass some data from Advantage Database Server (a database wich is
used generally with Delphy) to SQL-Server.
>In order to create a Linked Server from SQL-Server to the above mentioned d
atabase- what should I do?
>If there is an ODBC for the database- should it be installed on the server of the S
QL-Server or on my terminal?
used generally with Delphy) to SQL-Server.
In order to create a Linked Server from SQL-Server to the above mentioned da
tabase- what should I do?
If there is an ODBC for the database- should it be installed on the server o
f the SQL-Server or on my terminal?Yeah...if I remember right from using it before, you need to
install the drivers and ole db provider on SQL Server. Then
you can create the linked server to the Advantage server.
They should have information in the help files on this or
else on the web site.
-Sue
On Wed, 23 Feb 2005 19:41:08 +0200, "Geri Reshef"
<gershon_reshef@.recanati-alum.tau.ac.il> wrote:
>I have to pass some data from Advantage Database Server (a database wich is
used generally with Delphy) to SQL-Server.
>In order to create a Linked Server from SQL-Server to the above mentioned d
atabase- what should I do?
>If there is an ODBC for the database- should it be installed on the server of the S
QL-Server or on my terminal?
Advantage Database Server
I have to pass some data from Advantage Database Server (a database wich is used generally with Delphy) to SQL-Server.
In order to create a Linked Server from SQL-Server to the above mentioned database- what should I do?
If there is an ODBC for the database- should it be installed on the server of the SQL-Server or on my terminal?
Yeah...if I remember right from using it before, you need to
install the drivers and ole db provider on SQL Server. Then
you can create the linked server to the Advantage server.
They should have information in the help files on this or
else on the web site.
-Sue
On Wed, 23 Feb 2005 19:41:08 +0200, "Geri Reshef"
<gershon_reshef@.recanati-alum.tau.ac.il> wrote:
>I have to pass some data from Advantage Database Server (a database wich is used generally with Delphy) to SQL-Server.
>In order to create a Linked Server from SQL-Server to the above mentioned database- what should I do?
>If there is an ODBC for the database- should it be installed on the server of the SQL-Server or on my terminal?
In order to create a Linked Server from SQL-Server to the above mentioned database- what should I do?
If there is an ODBC for the database- should it be installed on the server of the SQL-Server or on my terminal?
Yeah...if I remember right from using it before, you need to
install the drivers and ole db provider on SQL Server. Then
you can create the linked server to the Advantage server.
They should have information in the help files on this or
else on the web site.
-Sue
On Wed, 23 Feb 2005 19:41:08 +0200, "Geri Reshef"
<gershon_reshef@.recanati-alum.tau.ac.il> wrote:
>I have to pass some data from Advantage Database Server (a database wich is used generally with Delphy) to SQL-Server.
>In order to create a Linked Server from SQL-Server to the above mentioned database- what should I do?
>If there is an ODBC for the database- should it be installed on the server of the SQL-Server or on my terminal?
Advanced Stored procedure...
Hello,
I have not written SQL for a long time so the quote inside string part might be wrong.|||Exact :) ,
(Excuse my english, i'm a french man)
I want to make a stored procedure on that model :
CREATE Procedure SGCP_GetEleveArgs
(
@.Arg1 nVarChar,
@.Val1 nVarChar
)
AS
SELECT *
FROM Eleve
WHERE @.Arg1 = @.Val1
So you understand that i want to pass my argument (@.Arg1) as a field of a column.
but it gives :
WHERE 'Nom' = 'Dupont'
but i want to have :
WHERE Nom = Dupont
for it to work.
Have you an idea to solve that problem ?
ThanxYou will want to use the EXEC function in SQL:
EXEC('select * from someTable WHERE ' + @.arg1 + ' = ''' + @.Val1 + '''')
I have not written SQL for a long time so the quote inside string part might be wrong.|||Exact :) ,
it seems to be the only one solution...
Thanx a lot
______________
Mik|||You could autogenerate the procs. For example, if you've got a table with the cols you could create
MyProcTableName1 @.Arg2
MyProcTableName2 @.Arg2
MyProcTableName3 @.Arg2
Then the client simply appends the first arg to the name...
CommandText = "MyProc" + TableName + strArg1
You get the idea...|||After research,
i found that we lose the advantages of stored procedure using EXEC because we don't use most important with stored procedure : precompilation of the command.
So I will not make like that
thanx|||Take a look at sp_executesql. This will used a compiled execution plan in some cases. There's really not any other dynamic way other than creating a dynamic SQL statement. Depending on the number of fields that could be searched, you could just put in several similar queries and use the appropriate one based on the field name. Could be cumbersome, but not bad if it's only a few.
if @.arg1 = 'field1'
select ... from table1 where field1 = @.arg2if @.arg1 = 'field2'
select ... from table1 where field2 = @.arg2
You could also use a CASE statement:
Select ...
from table1
where
case @.arg1
when 'field1' then field1
when 'field2' then field2
end = @.arg2
Might work. One problem is that if your stored procedure compiles an execution plan, it may use the wrong one. For example, the first time you run it, you search on field1. If you run it again, it may use the execution plan optimized for field1, but you're searching field2. You may get just as good results using dynamic SQL. It will create a new execution plan each time, but one that's optimized for the current query, not the last one.
Bon chance! (Only 5 years of French classes a LONG time ago.)
Sunday, March 11, 2012
advanced editor
I am used to DTS packages in sql server 2000 and now I want to use Integrated
services to create 'DTS' packages. Going to a .net users group meeting, I was
told to use the 'Advanced editor' since it would help me alot. However, I
can not find
the 'advanced editor' in sql server 2005. Thus can you tell me where the
'advanced editor'is and what are some good uses of the advanced editor?
Thanks!
Wendy Elizabeth (WendyElizabeth@.discussions.microsoft.com) writes:
> I am used to DTS packages in sql server 2000 and now I want to use
> Integrated services to create 'DTS' packages. Going to a .net users
> group meeting, I was told to use the 'Advanced editor' since it would
> help me alot. However, I can not find the 'advanced editor' in sql
> server 2005. Thus can you tell me where the 'advanced editor'is and what
> are some good uses of the advanced editor?
Are you looking in SQL Server Mangement Studio? You find precious little
support for Integration Services there. You should have a look at
Business Intelligence Development Studio (BIDS), which really is Visual
Studio under a custom name.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||I did find it was in Business Intelligence part of Visual studio.net. But
once you are in BIS, where do you find the advanced editor?
Thanks!
Diane Stein
"Erland Sommarskog" wrote:
> Wendy Elizabeth (WendyElizabeth@.discussions.microsoft.com) writes:
> Are you looking in SQL Server Mangement Studio? You find precious little
> support for Integration Services there. You should have a look at
> Business Intelligence Development Studio (BIDS), which really is Visual
> Studio under a custom name.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
|||Wendy Elizabeth (WendyElizabeth@.discussions.microsoft.com) writes:
> I did find it was in Business Intelligence part of Visual studio.net. But
> once you are in BIS, where do you find the advanced editor?
Now you asking me questions that goes over my head. :-) I don't use SSIS
much, or rather not at all. I just wanted to save you from looking in
SSMS. But isn't this "Advanced Editor" simply the Package Designer you
get when you create an Integration Services project?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||The "Advanced Editor' most likely refers to a property dialog for SSIS
components that allows you to set any property available on that component
and configure a lot of settings beyond what's typically available on the
regular Properties dialog for the same component.
In general, to use new SSIS designer, you start BIDS application, and create
a new SSIS project (click New -> Project, and pick SSIS project from the
list). This will create a new SSIS package within a project and open it up.
From there you will see the package's design surface, where you can drop
control flow components from a toolbox window, connect them into a flow and
configure. If you right click on any component, you will see an 'Advanced
Editor' option. If you have more questions, there is a separate SSIS forum
where you will find more experts ready to answer those.
Regards,
Maciek Sarnowicz
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99E4C3B06E16BYazorman@.127.0.0.1...
> Wendy Elizabeth (WendyElizabeth@.discussions.microsoft.com) writes:
> Now you asking me questions that goes over my head. :-) I don't use SSIS
> much, or rather not at all. I just wanted to save you from looking in
> SSMS. But isn't this "Advanced Editor" simply the Package Designer you
> get when you create an Integration Services project?
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
services to create 'DTS' packages. Going to a .net users group meeting, I was
told to use the 'Advanced editor' since it would help me alot. However, I
can not find
the 'advanced editor' in sql server 2005. Thus can you tell me where the
'advanced editor'is and what are some good uses of the advanced editor?
Thanks!
Wendy Elizabeth (WendyElizabeth@.discussions.microsoft.com) writes:
> I am used to DTS packages in sql server 2000 and now I want to use
> Integrated services to create 'DTS' packages. Going to a .net users
> group meeting, I was told to use the 'Advanced editor' since it would
> help me alot. However, I can not find the 'advanced editor' in sql
> server 2005. Thus can you tell me where the 'advanced editor'is and what
> are some good uses of the advanced editor?
Are you looking in SQL Server Mangement Studio? You find precious little
support for Integration Services there. You should have a look at
Business Intelligence Development Studio (BIDS), which really is Visual
Studio under a custom name.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||I did find it was in Business Intelligence part of Visual studio.net. But
once you are in BIS, where do you find the advanced editor?
Thanks!
Diane Stein
"Erland Sommarskog" wrote:
> Wendy Elizabeth (WendyElizabeth@.discussions.microsoft.com) writes:
> Are you looking in SQL Server Mangement Studio? You find precious little
> support for Integration Services there. You should have a look at
> Business Intelligence Development Studio (BIDS), which really is Visual
> Studio under a custom name.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
|||Wendy Elizabeth (WendyElizabeth@.discussions.microsoft.com) writes:
> I did find it was in Business Intelligence part of Visual studio.net. But
> once you are in BIS, where do you find the advanced editor?
Now you asking me questions that goes over my head. :-) I don't use SSIS
much, or rather not at all. I just wanted to save you from looking in
SSMS. But isn't this "Advanced Editor" simply the Package Designer you
get when you create an Integration Services project?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||The "Advanced Editor' most likely refers to a property dialog for SSIS
components that allows you to set any property available on that component
and configure a lot of settings beyond what's typically available on the
regular Properties dialog for the same component.
In general, to use new SSIS designer, you start BIDS application, and create
a new SSIS project (click New -> Project, and pick SSIS project from the
list). This will create a new SSIS package within a project and open it up.
From there you will see the package's design surface, where you can drop
control flow components from a toolbox window, connect them into a flow and
configure. If you right click on any component, you will see an 'Advanced
Editor' option. If you have more questions, there is a separate SSIS forum
where you will find more experts ready to answer those.
Regards,
Maciek Sarnowicz
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99E4C3B06E16BYazorman@.127.0.0.1...
> Wendy Elizabeth (WendyElizabeth@.discussions.microsoft.com) writes:
> Now you asking me questions that goes over my head. :-) I don't use SSIS
> much, or rather not at all. I just wanted to save you from looking in
> SSMS. But isn't this "Advanced Editor" simply the Package Designer you
> get when you create an Integration Services project?
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Thursday, March 8, 2012
Adult Content Filter
I want to create a message board designed for a church. Their concern was if people were to type in offensive words; they didn't want to display that particular post.
Does anyone have suggestions on the best way to run through a particular block of text, searching if an offensive word was present and then omitting the post?
Obviousily the front end would be a web app, but the back end dbase for this is M$ SQL Server 2000.
Any ideas or comments would be appreciated!U need a dictionary in your database, and a fulltext search engine to deal with the posted text, to find if the words in the dictionary appear in the text, and also the count of them. Then to decide if the text is clean.|||I would look to perl, ruby, or python for this solution. Perl handles strings very well and would be a natural for this task. You could do this on your web server and reject the post or give the author a chance to correct the indiscretion if there was a problem.|||Paul,
The webserver is running Win2K and IIS 5.0. Do you think some ASP code would work? What would be the most effiecient place for it to process? Client, Webserver or DBase side?
Thanks for the response.|||First of, I am NOT a WEB developer so I may not be the best person to answer this question.
The way I see this problem is you need to take a list of words and pass them over some text to see if you get a match. This does not sound like something a DB could do efficiently. I am 100% posative I could setup a DB solution in less than a day but I suspect that using Perl and Regulare Expresions would be a better answer. If you haven't used Perl or RegEx. both excel at string manipulation.
As for running the code on the client or web server, I personaly would choose the web server. It seems like the deployment and maintinance would be simplified.|||Thanks for the info. I will post with the solution I come up with. Then maybe some has some ideas on whether they can come up with something better!
Thanks again.
Does anyone have suggestions on the best way to run through a particular block of text, searching if an offensive word was present and then omitting the post?
Obviousily the front end would be a web app, but the back end dbase for this is M$ SQL Server 2000.
Any ideas or comments would be appreciated!U need a dictionary in your database, and a fulltext search engine to deal with the posted text, to find if the words in the dictionary appear in the text, and also the count of them. Then to decide if the text is clean.|||I would look to perl, ruby, or python for this solution. Perl handles strings very well and would be a natural for this task. You could do this on your web server and reject the post or give the author a chance to correct the indiscretion if there was a problem.|||Paul,
The webserver is running Win2K and IIS 5.0. Do you think some ASP code would work? What would be the most effiecient place for it to process? Client, Webserver or DBase side?
Thanks for the response.|||First of, I am NOT a WEB developer so I may not be the best person to answer this question.
The way I see this problem is you need to take a list of words and pass them over some text to see if you get a match. This does not sound like something a DB could do efficiently. I am 100% posative I could setup a DB solution in less than a day but I suspect that using Perl and Regulare Expresions would be a better answer. If you haven't used Perl or RegEx. both excel at string manipulation.
As for running the code on the client or web server, I personaly would choose the web server. It seems like the deployment and maintinance would be simplified.|||Thanks for the info. I will post with the solution I come up with. Then maybe some has some ideas on whether they can come up with something better!
Thanks again.
ADS Wizard, tutorials and examples - Please Help.
Hi,
I'm new to SQL Server Technology and am trying to learn how to create a Mobile Device application and to sync it with a desktop application that uses Access. I have found a lot of information but some of it doesn't seem to make sense.
I found this blog: http://blogs.msdn.com/sqlservereverywhere/archive/2006/08/29/729730.aspx which is the announcement of Access Database Synchronizer (ADS) CTP. I downloaded it and installed it with all the prerequisites. This is the part i can't understand:
"The CTP setup installs the desktop component required for synchronizing Microsoft Access database with SQL Server Everywhere Edition database on the device."
I cannot seem to find the desktop component to try out! The Readme shows you how to pull and push data but i can't see where the desktop data sync wizard is located. Should there be something installed on my desktop that i can see?
Also, there doesn't seem to be much help or examples around on this component as yet. Can anyone point me in the right direction for examples, help, or tutorials on this.
This paragraph also confuses me:
"Note: As of today only SQL Server Mobile Edition is available for devices. SQL Server Everywhere Edition whould be available for devices at time of SQL Server Everywhere Edition RTW whihc is planned for November 2006".
Does this mean SQL Everywhere won't work on mobile devices until November?
Sorry for all the questions but i'm still trying to learn this stuff so i can write my application for Windows CE. Thanks in advance.
Hi
Let me just give you a brief overview here. ADS is a solution which helps to synchronize a Access database on the desktop with a SQL Mobile/SQL Everywhere database on a device. There is no UI associated with the dektop component of ADS. The desktop component just listens for syncronization requests from a client (device with SQL MObile/SQL Everywhere) and performs the required actions.
Generally the applications written to utilize this soltuon would reside on the device. ADS Wizard is one such sample application we have provided along with source code to helpdevelopers write thier own applications.
If you have installed ADS at the default installation path, you will find the ADS wizard at the following path
C:\Program Files\Microsoft SQL Server Everywhere Edition\Access\Sample\AccessSync
Just open the .sln file in Visual Studio, build and deploy the solution on a PPC device and you are ready to go. Please read the "redme.htm" for more details
Hope this helps. If you have any more questions let me know
Regards
Manish
|||Hi,
Thanks for your reply. I have got the sample working now with an emulator and can sync a test.sdf database with an access database on my desktop. So far so good!
I have another question though....Is it possible to create an application on the desktop to do the same thing or can this only run from the device? I want to create an application on the desktop that can read the SDF file on the device and sync it with the access database on my desktop. The reason for this is that the user must have options to select certain criteria of data to put onto the device. I may need to extract the selected data from my main Access database to a temporary database then push that to the device. Then when the user has used the device for the day, they just cradle it then run this application which syncs the data back to the temporary table. Then the application copies this data back to the main database as it needs to update certain fields, not just synchronize the entire database.
Does this sound possible? I have tried connection strings like below but it doesn't seem to work.
Dim conStringDevice As String = ("Data Source=Mobile Device\Test123.sdf")
m_connDevice.Open()
I just get an error: "The Path is not valid...Path = Mobile Device\Test123.sdf"
The device is connected and cradled and ActiveSync is running. I can use VS2005 to connect to this database no problems. Can i do this programmatically?
Thanks
|||"Mobile Device" is only for Designers to differentiate between device and desktop connection. The runtime bits does not really understand this. To simply say, try removing "Mobile Device" Prefix.
Thanks,
Laxmi
|||I tried removing "Mobile Device" and it still doesn't work. Surely there needs to be some reference to where the file is stored?
The ADS sample makes reference to the access database stored on "C:\" and the SQL database as just "Test123.sdf". But this is run from the Mobile device and picks up the local file. If run from the desktop, how can i reference to the mobile device file "Test123.sdf"?
|||Ok to put this simply....Can an ADS application be created to run on a dekstop to pull and push data from and to the mobile device? As i stated before the sample runs on the device itself, but i want to write an application to run on the desktop. Can someone please help?|||"The_Nod", did you ever find any resolution to this problem? I am in the exact same place you are/were right now? I'm trying to control the sync from a desktop app as well.
|||Hi mhawb,
No unfortunately. I was writing an application for Windows mobile 5.0 which was to synchronize with a desktop application that is still using an access database but i stopped doing it all together. I found problems with synchronizing as the field type were a lot different than the Access database and thought it couldn't be done that way.
I was going down the path of actually copying the SDF file from the mobile device to the desktop (as you cant read the SDF file directly from the desktop), then writing an application that manually updated the data to the Access Database. I found something called OpenNETCF which uses RAPI to copy the SQL databases to and from the desktop device. Here is a link to RAPI on MSDN: http://msdn2.microsoft.com/en-US/library/ms837846.aspx
Hope this helps. I stopped doing this project as i couldn't find the time. I may get back into it soon so if anyone has any better ideas and any ADS examples that would be great.
ADS Wizard, tutorials and examples - Please Help.
Hi,
I'm new to SQL Server Technology and am trying to learn how to create a Mobile Device application and to sync it with a desktop application that uses Access. I have found a lot of information but some of it doesn't seem to make sense.
I found this blog: http://blogs.msdn.com/sqlservereverywhere/archive/2006/08/29/729730.aspx which is the announcement of Access Database Synchronizer (ADS) CTP. I downloaded it and installed it with all the prerequisites. This is the part i can't understand:
"The CTP setup installs the desktop component required for synchronizing Microsoft Access database with SQL Server Everywhere Edition database on the device."
I cannot seem to find the desktop component to try out! The Readme shows you how to pull and push data but i can't see where the desktop data sync wizard is located. Should there be something installed on my desktop that i can see?
Also, there doesn't seem to be much help or examples around on this component as yet. Can anyone point me in the right direction for examples, help, or tutorials on this.
This paragraph also confuses me:
"Note: As of today only SQL Server Mobile Edition is available for devices. SQL Server Everywhere Edition whould be available for devices at time of SQL Server Everywhere Edition RTW whihc is planned for November 2006".
Does this mean SQL Everywhere won't work on mobile devices until November?
Sorry for all the questions but i'm still trying to learn this stuff so i can write my application for Windows CE. Thanks in advance.
Hi
Let me just give you a brief overview here. ADS is a solution which helps to synchronize a Access database on the desktop with a SQL Mobile/SQL Everywhere database on a device. There is no UI associated with the dektop component of ADS. The desktop component just listens for syncronization requests from a client (device with SQL MObile/SQL Everywhere) and performs the required actions.
Generally the applications written to utilize this soltuon would reside on the device. ADS Wizard is one such sample application we have provided along with source code to helpdevelopers write thier own applications.
If you have installed ADS at the default installation path, you will find the ADS wizard at the following path
C:\Program Files\Microsoft SQL Server Everywhere Edition\Access\Sample\AccessSync
Just open the .sln file in Visual Studio, build and deploy the solution on a PPC device and you are ready to go. Please read the "redme.htm" for more details
Hope this helps. If you have any more questions let me know
Regards
Manish
|||Hi,
Thanks for your reply. I have got the sample working now with an emulator and can sync a test.sdf database with an access database on my desktop. So far so good!
I have another question though....Is it possible to create an application on the desktop to do the same thing or can this only run from the device? I want to create an application on the desktop that can read the SDF file on the device and sync it with the access database on my desktop. The reason for this is that the user must have options to select certain criteria of data to put onto the device. I may need to extract the selected data from my main Access database to a temporary database then push that to the device. Then when the user has used the device for the day, they just cradle it then run this application which syncs the data back to the temporary table. Then the application copies this data back to the main database as it needs to update certain fields, not just synchronize the entire database.
Does this sound possible? I have tried connection strings like below but it doesn't seem to work.
Dim conStringDevice As String = ("Data Source=Mobile Device\Test123.sdf")
m_connDevice.Open()
I just get an error: "The Path is not valid...Path = Mobile Device\Test123.sdf"
The device is connected and cradled and ActiveSync is running. I can use VS2005 to connect to this database no problems. Can i do this programmatically?
Thanks
|||"Mobile Device" is only for Designers to differentiate between device and desktop connection. The runtime bits does not really understand this. To simply say, try removing "Mobile Device" Prefix.
Thanks,
Laxmi
|||I tried removing "Mobile Device" and it still doesn't work. Surely there needs to be some reference to where the file is stored?
The ADS sample makes reference to the access database stored on "C:\" and the SQL database as just "Test123.sdf". But this is run from the Mobile device and picks up the local file. If run from the desktop, how can i reference to the mobile device file "Test123.sdf"?
|||Ok to put this simply....Can an ADS application be created to run on a dekstop to pull and push data from and to the mobile device? As i stated before the sample runs on the device itself, but i want to write an application to run on the desktop. Can someone please help?|||"The_Nod", did you ever find any resolution to this problem? I am in the exact same place you are/were right now? I'm trying to control the sync from a desktop app as well.
|||Hi mhawb,
No unfortunately. I was writing an application for Windows mobile 5.0 which was to synchronize with a desktop application that is still using an access database but i stopped doing it all together. I found problems with synchronizing as the field type were a lot different than the Access database and thought it couldn't be done that way.
I was going down the path of actually copying the SDF file from the mobile device to the desktop (as you cant read the SDF file directly from the desktop), then writing an application that manually updated the data to the Access Database. I found something called OpenNETCF which uses RAPI to copy the SQL databases to and from the desktop device. Here is a link to RAPI on MSDN: http://msdn2.microsoft.com/en-US/library/ms837846.aspx
Hope this helps. I stopped doing this project as i couldn't find the time. I may get back into it soon so if anyone has any better ideas and any ADS examples that would be great.
ADS Wizard, tutorials and examples - Please Help.
Hi,
I'm new to SQL Server Technology and am trying to learn how to create a Mobile Device application and to sync it with a desktop application that uses Access. I have found a lot of information but some of it doesn't seem to make sense.
I found this blog: http://blogs.msdn.com/sqlservereverywhere/archive/2006/08/29/729730.aspx which is the announcement of Access Database Synchronizer (ADS) CTP. I downloaded it and installed it with all the prerequisites. This is the part i can't understand:
"The CTP setup installs the desktop component required for synchronizing Microsoft Access database with SQL Server Everywhere Edition database on the device."
I cannot seem to find the desktop component to try out! The Readme shows you how to pull and push data but i can't see where the desktop data sync wizard is located. Should there be something installed on my desktop that i can see?
Also, there doesn't seem to be much help or examples around on this component as yet. Can anyone point me in the right direction for examples, help, or tutorials on this.
This paragraph also confuses me:
"Note: As of today only SQL Server Mobile Edition is available for devices. SQL Server Everywhere Edition whould be available for devices at time of SQL Server Everywhere Edition RTW whihc is planned for November 2006".
Does this mean SQL Everywhere won't work on mobile devices until November?
Sorry for all the questions but i'm still trying to learn this stuff so i can write my application for Windows CE. Thanks in advance.
Hi
Let me just give you a brief overview here. ADS is a solution which helps to synchronize a Access database on the desktop with a SQL Mobile/SQL Everywhere database on a device. There is no UI associated with the dektop component of ADS. The desktop component just listens for syncronization requests from a client (device with SQL MObile/SQL Everywhere) and performs the required actions.
Generally the applications written to utilize this soltuon would reside on the device. ADS Wizard is one such sample application we have provided along with source code to helpdevelopers write thier own applications.
If you have installed ADS at the default installation path, you will find the ADS wizard at the following path
C:\Program Files\Microsoft SQL Server Everywhere Edition\Access\Sample\AccessSync
Just open the .sln file in Visual Studio, build and deploy the solution on a PPC device and you are ready to go. Please read the "redme.htm" for more details
Hope this helps. If you have any more questions let me know
Regards
Manish
|||Hi,
Thanks for your reply. I have got the sample working now with an emulator and can sync a test.sdf database with an access database on my desktop. So far so good!
I have another question though....Is it possible to create an application on the desktop to do the same thing or can this only run from the device? I want to create an application on the desktop that can read the SDF file on the device and sync it with the access database on my desktop. The reason for this is that the user must have options to select certain criteria of data to put onto the device. I may need to extract the selected data from my main Access database to a temporary database then push that to the device. Then when the user has used the device for the day, they just cradle it then run this application which syncs the data back to the temporary table. Then the application copies this data back to the main database as it needs to update certain fields, not just synchronize the entire database.
Does this sound possible? I have tried connection strings like below but it doesn't seem to work.
Dim conStringDevice As String = ("Data Source=Mobile Device\Test123.sdf")
m_connDevice.Open()
I just get an error: "The Path is not valid...Path = Mobile Device\Test123.sdf"
The device is connected and cradled and ActiveSync is running. I can use VS2005 to connect to this database no problems. Can i do this programmatically?
Thanks
|||"Mobile Device" is only for Designers to differentiate between device and desktop connection. The runtime bits does not really understand this. To simply say, try removing "Mobile Device" Prefix.
Thanks,
Laxmi
|||I tried removing "Mobile Device" and it still doesn't work. Surely there needs to be some reference to where the file is stored?
The ADS sample makes reference to the access database stored on "C:\" and the SQL database as just "Test123.sdf". But this is run from the Mobile device and picks up the local file. If run from the desktop, how can i reference to the mobile device file "Test123.sdf"?
|||Ok to put this simply....Can an ADS application be created to run on a dekstop to pull and push data from and to the mobile device? As i stated before the sample runs on the device itself, but i want to write an application to run on the desktop. Can someone please help?|||"The_Nod", did you ever find any resolution to this problem? I am in the exact same place you are/were right now? I'm trying to control the sync from a desktop app as well.
|||Hi mhawb,
No unfortunately. I was writing an application for Windows mobile 5.0 which was to synchronize with a desktop application that is still using an access database but i stopped doing it all together. I found problems with synchronizing as the field type were a lot different than the Access database and thought it couldn't be done that way.
I was going down the path of actually copying the SDF file from the mobile device to the desktop (as you cant read the SDF file directly from the desktop), then writing an application that manually updated the data to the Access Database. I found something called OpenNETCF which uses RAPI to copy the SQL databases to and from the desktop device. Here is a link to RAPI on MSDN: http://msdn2.microsoft.com/en-US/library/ms837846.aspx
Hope this helps. I stopped doing this project as i couldn't find the time. I may get back into it soon so if anyone has any better ideas and any ADS examples that would be great.
Tuesday, March 6, 2012
adp connection to msde and exclusive rights
I am attempting to create a View from MS Access 2000 SR-1 to an MSDE 2000
(SQL Server 2000 Desktop Engine) database . I accessed with exclusive rights
but I get the following error when trying to create a view:
"You do not have exclusive access to the database at this time. Your design
changes will not be saved."
how can I get ability to create views ?
I'd recommend not using Access as a development tool for SQL Server
objects. Use the Query Analyzer or Enterprise Manager instead. The
error message you're getting is an Access error, not a SQL Server
error.
--Mary
On Thu, 24 Jun 2004 01:08:50 -0700, "TJS" <nospam@.here.com> wrote:
>I am attempting to create a View from MS Access 2000 SR-1 to an MSDE 2000
>(SQL Server 2000 Desktop Engine) database . I accessed with exclusive rights
>but I get the following error when trying to create a view:
>"You do not have exclusive access to the database at this time. Your design
>changes will not be saved."
>how can I get ability to create views ?
>
|||Hi Mary:
Thanks for replying (I have your book, Developer's guide to SQL Server).
Unfortunately, I don't have the Query Analyzer or Enterprise Manager , so I
was hoping to use access as a front end.
is there any way access can be a front end to msde ?
"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
news:n74md01glum1t5l2c79ak6sjmj11sct03m@.4ax.com... [vbcol=seagreen]
> I'd recommend not using Access as a development tool for SQL Server
> objects. Use the Query Analyzer or Enterprise Manager instead. The
> error message you're getting is an Access error, not a SQL Server
> error.
> --Mary
> On Thu, 24 Jun 2004 01:08:50 -0700, "TJS" <nospam@.here.com> wrote:
rights[vbcol=seagreen]
design
>
|||MSDE is not a development platform, it's a deployment platform. I would
suggest that you spend the <$50 required to purchase SQL Server Developer
Edition. It contains everything you'll need to develop for the SQL Server
and MSDE platforms (really the same thing).
I'll even go out on a limb and predict that Mary will say the same thing.
:-)
Yes, you can use Access as a front end to MSDE, but you'll likely find the
tools she mentioned easier to use to create and manage your database.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"TJS" <nospam@.here.com> wrote in message
news:10dm523btum1rd5@.corp.supernews.com...
> Hi Mary:
> Thanks for replying (I have your book, Developer's guide to SQL Server).
> Unfortunately, I don't have the Query Analyzer or Enterprise Manager , so
I[vbcol=seagreen]
> was hoping to use access as a front end.
> is there any way access can be a front end to msde ?
>
>
> "Mary Chipman" <mchip@.online.microsoft.com> wrote in message
> news:n74md01glum1t5l2c79ak6sjmj11sct03m@.4ax.com...
2000
> rights
> design
>
|||I just want to create a view...
"Stephen Dybing [MSFT]" <stephd@.online.microsoft.com> wrote in message
news:OBa0OViWEHA.2844@.TK2MSFTNGP11.phx.gbl...
> MSDE is not a development platform, it's a deployment platform. I would
> suggest that you spend the <$50 required to purchase SQL Server Developer
> Edition. It contains everything you'll need to develop for the SQL Server
> and MSDE platforms (really the same thing).
> I'll even go out on a limb and predict that Mary will say the same thing.
> :-)
> Yes, you can use Access as a front end to MSDE, but you'll likely find the
> tools she mentioned easier to use to create and manage your database.
> --
> Sincerely,
> Stephen Dybing
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "TJS" <nospam@.here.com> wrote in message
> news:10dm523btum1rd5@.corp.supernews.com...
so
> I
> 2000
>
(SQL Server 2000 Desktop Engine) database . I accessed with exclusive rights
but I get the following error when trying to create a view:
"You do not have exclusive access to the database at this time. Your design
changes will not be saved."
how can I get ability to create views ?
I'd recommend not using Access as a development tool for SQL Server
objects. Use the Query Analyzer or Enterprise Manager instead. The
error message you're getting is an Access error, not a SQL Server
error.
--Mary
On Thu, 24 Jun 2004 01:08:50 -0700, "TJS" <nospam@.here.com> wrote:
>I am attempting to create a View from MS Access 2000 SR-1 to an MSDE 2000
>(SQL Server 2000 Desktop Engine) database . I accessed with exclusive rights
>but I get the following error when trying to create a view:
>"You do not have exclusive access to the database at this time. Your design
>changes will not be saved."
>how can I get ability to create views ?
>
|||Hi Mary:
Thanks for replying (I have your book, Developer's guide to SQL Server).
Unfortunately, I don't have the Query Analyzer or Enterprise Manager , so I
was hoping to use access as a front end.
is there any way access can be a front end to msde ?
"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
news:n74md01glum1t5l2c79ak6sjmj11sct03m@.4ax.com... [vbcol=seagreen]
> I'd recommend not using Access as a development tool for SQL Server
> objects. Use the Query Analyzer or Enterprise Manager instead. The
> error message you're getting is an Access error, not a SQL Server
> error.
> --Mary
> On Thu, 24 Jun 2004 01:08:50 -0700, "TJS" <nospam@.here.com> wrote:
rights[vbcol=seagreen]
design
>
|||MSDE is not a development platform, it's a deployment platform. I would
suggest that you spend the <$50 required to purchase SQL Server Developer
Edition. It contains everything you'll need to develop for the SQL Server
and MSDE platforms (really the same thing).
I'll even go out on a limb and predict that Mary will say the same thing.
:-)
Yes, you can use Access as a front end to MSDE, but you'll likely find the
tools she mentioned easier to use to create and manage your database.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"TJS" <nospam@.here.com> wrote in message
news:10dm523btum1rd5@.corp.supernews.com...
> Hi Mary:
> Thanks for replying (I have your book, Developer's guide to SQL Server).
> Unfortunately, I don't have the Query Analyzer or Enterprise Manager , so
I[vbcol=seagreen]
> was hoping to use access as a front end.
> is there any way access can be a front end to msde ?
>
>
> "Mary Chipman" <mchip@.online.microsoft.com> wrote in message
> news:n74md01glum1t5l2c79ak6sjmj11sct03m@.4ax.com...
2000
> rights
> design
>
|||I just want to create a view...
"Stephen Dybing [MSFT]" <stephd@.online.microsoft.com> wrote in message
news:OBa0OViWEHA.2844@.TK2MSFTNGP11.phx.gbl...
> MSDE is not a development platform, it's a deployment platform. I would
> suggest that you spend the <$50 required to purchase SQL Server Developer
> Edition. It contains everything you'll need to develop for the SQL Server
> and MSDE platforms (really the same thing).
> I'll even go out on a limb and predict that Mary will say the same thing.
> :-)
> Yes, you can use Access as a front end to MSDE, but you'll likely find the
> tools she mentioned easier to use to create and manage your database.
> --
> Sincerely,
> Stephen Dybing
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "TJS" <nospam@.here.com> wrote in message
> news:10dm523btum1rd5@.corp.supernews.com...
so
> I
> 2000
>
ADP and Sql server
hey
I m new to this and i dont know how to create adp project using access and sql server.
actually what i hv heard from my manager that we gonna create the front end in access and backend in sql server 2000.
I dont know from where to start reading abt it. if any body has good links or book plz send me.
thx in advance.It's not too difficult. Just open access and create a new database, selecting Access Data Project as the type.
An ADP file has all the forms, reports, macros, and modules that you would expect from an Access database, but all the data is stored on the server.|||To be fair, if you are new to relational databases, Access and SQL Server then it is a steep learning curve from now on in.
Step #1 before you do anything - get some sort of feel for relational database design by reading this closely:
http://www.r937.com/relational.html|||And just so you know, you don't have to create an ADP; an MDB will work fine with SQL Server. There are advantages and disadvantages to each. Personally, I continue to use MDB's with it, and Microsoft itself seems to have moved away from ADP's (or at least not pushed them forward).|||What? You would use linked tables over an ADP design?
I'm trying to think of any way that linked tables would be a better implementation than ADP, but I'm not coming up with much...
Also, please support your statement that MS is moving away from ADPs. I've not heard anything about this.|||Blindman, I have a great deal of respect for you and your opinion. I didn't say linked tables were better, I simply said that you did not HAVE to use an ADP with SQL Server, and I said there were advantages and disadvantages to each. As to your question about whether I would use them, I not only would, I do.
I can quote several Access MVP's on the topic (and perhaps should have disclosed that I am one myself), but here's the first I found from a highly respected MVP on another site, Pat Hartman:
the .adp is being phased out. I would recommend not creating new applications in the .adp format. Unlike the .adp, Access .mdb's can be used just as effectively as front ends to ANY back end database for which there is an ODBC driver. The limitations of the .adp are too restrictive and SQL Server centric. The format has not been widely adoped and so there is no big push to enhance the feature. The .adp will still be supported in Access 12 when it is released but no enhancements have been made. In fact, although existing .adp's will be able to use SQL Server 2005, they will not be able to create new databases through the Access GUI as they can with older versions of SQL Server.
If you like I can find others, but suffice it to say that there are many respected people who do not like ADP's. There are also many other respected people, such as yourself, who do. Again, my main point was simply to point out that it was not a requirement to use an ADP, but an option. I apologize if it came off otherwise.|||I can quote several Access MVP's on the topic (and perhaps should have disclosed that I am one myself)Paul - is this recent? I don't remember this from the first time I came across you a year or two ago - I checked you out then after a few very positive references and would have thought I would have noted it.|||FWIW I exclusively use MDB\Es too as I prefer the flexibility they allow. I can appreciate that ADPs might be easier and more intuitive (though not for me) and they do go some way to providing an all-in-one GUI. (and perhaps I should disclose that I am not an MVP myself :))|||pbaldy, thanks for supplying the quote from Pat Hartman. If what he says is true, I am most disappointed. I found ADPs to be much more convenient and much more efficient than linked tables. As a rapid application development platform for SQL Server, ADP files could not be beat.
By the way, I wan't being accusatory in my post. Sometimes, when I say "Please support your statement", I really am just looking for more information. The rude jerk persona I exude is really just an act. Like Paris Hilton's stupidity, you know...|||Paul - is this recent?
No...it's been over a week now! :p
Blindman, no offense taken here. If I can't come up with some backup for something I say, I shouldn't say it in the first place. By the way, just saw this post elsewhere this morning, from author/MVP John Viescas:
If you're using a Project file (.adp) the answer is no. For what it's
worth, Microsoft has given up on Project files. No enhancements in V12, and
I suspect the feature will be deprecated in VNext. Microsoft now admits
that the best front end to an SQL Server database in an mdb file using
linked tables. When you do that, you can have local tables and also link to
other databases - Oracle or MySQL.|||Please bare with me, what the finaly decision is that its better to link the tables and keep Access as an MDB rather then turn them into an ADP?? Is this correct??|||Well, personally, I would strongly disagree.
Linked tables are more difficult to manage and less efficient than using an ADP application.
With ADP, all the code is executed at the server. While with MDB linked tables they code may or may not be issued as a pass-through sql statement. If Access is unable to construct the pass-through statement it will suck the data from database over your network and perform the calculation locally. You can bet that will be slow.
With an ADP file you have one link to the source database, and that is it. And it is very easy to redirect that link to a different source. With MDB files every external object must be linked separately.|||MDB = more flexibility but requires more sophisticated coding (read expertise and coding hours) to be truly efficient.
ADP = easier to use (especially if you are more familiar with SQL Server) but is less flexible. Easier to leverage SSs power. I know an ADP cannot conntect to two instances simultaneously (hence less flexible). Is it also the same with SS dbs?
No...it's been over a week now! :p Congratulations Paul. I know you only flirt with posting over here but I have heard that you have built up a superb reputation over at ... er... the other site and your MVPship is well earned. :)|||I think asking which is better is like asking whether a sports car or pickup truck is better. They're different animals. They each have pros and cons, and which is better for you depends on your situation and needs.
Thanks pootle. I actually visit here a lot, it's just that there are a lot of very strong users here, and most of the questions are already answered by the time I see them. If you guys would leave me some scraps, I'd post more. :cool:|||If you guys would leave me some scraps, I'd post more. :cool:
Yeah, right..|||Yeah, right..
I'm not quite sure how to take that. Have I offended you in some way?|||No, not at all...it's more of a reference to the fact that the posts get answered to quickly..and that you would answer if they weren't already answered...hence my response
The core here are sort of addicted
And I doubt anyone in the Core would ever get offende...by anything
We usually get amused|||Well, I didn't say too quickly, I simply said they were answered by the time I saw them. It was meant to be a compliment to the other users here.|||Yup -
"quick response > accurate response" is my motto ;)
I sense a new sig on the horizon
I m new to this and i dont know how to create adp project using access and sql server.
actually what i hv heard from my manager that we gonna create the front end in access and backend in sql server 2000.
I dont know from where to start reading abt it. if any body has good links or book plz send me.
thx in advance.It's not too difficult. Just open access and create a new database, selecting Access Data Project as the type.
An ADP file has all the forms, reports, macros, and modules that you would expect from an Access database, but all the data is stored on the server.|||To be fair, if you are new to relational databases, Access and SQL Server then it is a steep learning curve from now on in.
Step #1 before you do anything - get some sort of feel for relational database design by reading this closely:
http://www.r937.com/relational.html|||And just so you know, you don't have to create an ADP; an MDB will work fine with SQL Server. There are advantages and disadvantages to each. Personally, I continue to use MDB's with it, and Microsoft itself seems to have moved away from ADP's (or at least not pushed them forward).|||What? You would use linked tables over an ADP design?
I'm trying to think of any way that linked tables would be a better implementation than ADP, but I'm not coming up with much...
Also, please support your statement that MS is moving away from ADPs. I've not heard anything about this.|||Blindman, I have a great deal of respect for you and your opinion. I didn't say linked tables were better, I simply said that you did not HAVE to use an ADP with SQL Server, and I said there were advantages and disadvantages to each. As to your question about whether I would use them, I not only would, I do.
I can quote several Access MVP's on the topic (and perhaps should have disclosed that I am one myself), but here's the first I found from a highly respected MVP on another site, Pat Hartman:
the .adp is being phased out. I would recommend not creating new applications in the .adp format. Unlike the .adp, Access .mdb's can be used just as effectively as front ends to ANY back end database for which there is an ODBC driver. The limitations of the .adp are too restrictive and SQL Server centric. The format has not been widely adoped and so there is no big push to enhance the feature. The .adp will still be supported in Access 12 when it is released but no enhancements have been made. In fact, although existing .adp's will be able to use SQL Server 2005, they will not be able to create new databases through the Access GUI as they can with older versions of SQL Server.
If you like I can find others, but suffice it to say that there are many respected people who do not like ADP's. There are also many other respected people, such as yourself, who do. Again, my main point was simply to point out that it was not a requirement to use an ADP, but an option. I apologize if it came off otherwise.|||I can quote several Access MVP's on the topic (and perhaps should have disclosed that I am one myself)Paul - is this recent? I don't remember this from the first time I came across you a year or two ago - I checked you out then after a few very positive references and would have thought I would have noted it.|||FWIW I exclusively use MDB\Es too as I prefer the flexibility they allow. I can appreciate that ADPs might be easier and more intuitive (though not for me) and they do go some way to providing an all-in-one GUI. (and perhaps I should disclose that I am not an MVP myself :))|||pbaldy, thanks for supplying the quote from Pat Hartman. If what he says is true, I am most disappointed. I found ADPs to be much more convenient and much more efficient than linked tables. As a rapid application development platform for SQL Server, ADP files could not be beat.
By the way, I wan't being accusatory in my post. Sometimes, when I say "Please support your statement", I really am just looking for more information. The rude jerk persona I exude is really just an act. Like Paris Hilton's stupidity, you know...|||Paul - is this recent?
No...it's been over a week now! :p
Blindman, no offense taken here. If I can't come up with some backup for something I say, I shouldn't say it in the first place. By the way, just saw this post elsewhere this morning, from author/MVP John Viescas:
If you're using a Project file (.adp) the answer is no. For what it's
worth, Microsoft has given up on Project files. No enhancements in V12, and
I suspect the feature will be deprecated in VNext. Microsoft now admits
that the best front end to an SQL Server database in an mdb file using
linked tables. When you do that, you can have local tables and also link to
other databases - Oracle or MySQL.|||Please bare with me, what the finaly decision is that its better to link the tables and keep Access as an MDB rather then turn them into an ADP?? Is this correct??|||Well, personally, I would strongly disagree.
Linked tables are more difficult to manage and less efficient than using an ADP application.
With ADP, all the code is executed at the server. While with MDB linked tables they code may or may not be issued as a pass-through sql statement. If Access is unable to construct the pass-through statement it will suck the data from database over your network and perform the calculation locally. You can bet that will be slow.
With an ADP file you have one link to the source database, and that is it. And it is very easy to redirect that link to a different source. With MDB files every external object must be linked separately.|||MDB = more flexibility but requires more sophisticated coding (read expertise and coding hours) to be truly efficient.
ADP = easier to use (especially if you are more familiar with SQL Server) but is less flexible. Easier to leverage SSs power. I know an ADP cannot conntect to two instances simultaneously (hence less flexible). Is it also the same with SS dbs?
No...it's been over a week now! :p Congratulations Paul. I know you only flirt with posting over here but I have heard that you have built up a superb reputation over at ... er... the other site and your MVPship is well earned. :)|||I think asking which is better is like asking whether a sports car or pickup truck is better. They're different animals. They each have pros and cons, and which is better for you depends on your situation and needs.
Thanks pootle. I actually visit here a lot, it's just that there are a lot of very strong users here, and most of the questions are already answered by the time I see them. If you guys would leave me some scraps, I'd post more. :cool:|||If you guys would leave me some scraps, I'd post more. :cool:
Yeah, right..|||Yeah, right..
I'm not quite sure how to take that. Have I offended you in some way?|||No, not at all...it's more of a reference to the fact that the posts get answered to quickly..and that you would answer if they weren't already answered...hence my response
The core here are sort of addicted
And I doubt anyone in the Core would ever get offende...by anything
We usually get amused|||Well, I didn't say too quickly, I simply said they were answered by the time I saw them. It was meant to be a compliment to the other users here.|||Yup -
"quick response > accurate response" is my motto ;)
I sense a new sig on the horizon
AdomdConnection via a UDL file
To connect to a database engine by using types such as OleDbConnection, I can create a UDL file to connect to the database, then just use that UDL file by a connection string like "File Name = \\My Documents\\my.udl".
It seems that I cannot use this way to connect to SSAS. I can create a UDL file to connect to a local SSAS without any problem. However, when I use connection string like "File Name = \\My Documents\\myssas.udl" for AdomdConnection, I get the following error:
"The 'File Name' property name is not formatted correctly."
Could anyone tell me what is the correct format?
Thanks,
hz
hello,
right now supplying conneciton information for an AdomdConnection in a UDL file is not supported in Adomd.Net.
hope this clarifies.
|||Thanks, Mary.
It is good to have this confirmed.
hz
AdomdConnection permission problem
I am developing a Windows Form application. On one of the forms I display the results of a query to an Analysis Services database. I create the MDX command text by building a string.
Everything works, but users who do not have permission to access the database encounter and access error. I don't want to give all of the users permission to access the database. I want the application to use the credentials in the connection string. However, when I execute the AdomdCommand, it seems to be using the credentials of the logged on user instead of the "User ID" in the connection string.
How can I get the command to use the credentials in the connection string?
Dim cmdText As String = ""
Dim BdSalesBacklog As Decimal = 0
Dim oAdomdConnection As New AdomdConnection("Data Source=server;Catalog=PortalAnalytics;User ID=user;password=password")
Dim oAdomdCommand As AdomdCommand = New AdomdCommand()
Dim oAdomdReader As AdomdDataReader
Dim period As String = ddlPeriod.Items(0)
oAdomdCommand.CommandType = CommandType.Text
cmdText = "" & _
"SELECT " & _
"{ BNBTime.[" & period & "] } ON COLUMNS , " & _
"{ Measures.[Total Backlog Snapshot] } ON ROWS " & _
"FROM BNB "
oAdomdCommand.CommandText = cmdText
Try
oAdomdConnection.Open()
oAdomdCommand.Connection = oAdomdConnection
oAdomdReader = oAdomdCommand.ExecuteReader()
Catch ex As Exception
MessageBox.Show(Err.Description)
End Try
Do While oAdomdReader.Read()
BdSalesBacklog = oAdomdReader.GetDecimal(1)
Loop
oAdomdReader.Close()
oAdomdConnection.Close()
I called a developer friend of mine and he explained to me that SSAS requires the logged on user to have permissions in a Role in the AS database.
I created an AD group containing the users to whom I want to allow access, and I gave that group membership in a new AS Role. I assigned the role the permissions I wanted the users to have.
My application now works for all the users who need to run it.
Thanks
AdomdConnection permission problem
I am developing a Windows Form application. On one of the forms I display the results of a query to an Analysis Services database. I create the MDX command text by building a string.
Everything works, but users who do not have permission to access the database encounter and access error. I don't want to give all of the users permission to access the database. I want the application to use the credentials in the connection string. However, when I execute the AdomdCommand, it seems to be using the credentials of the logged on user instead of the "User ID" in the connection string.
How can I get the command to use the credentials in the connection string?
Dim cmdText As String = ""
Dim BdSalesBacklog As Decimal = 0
Dim oAdomdConnection As New AdomdConnection("Data Source=server;Catalog=PortalAnalytics;User ID=user;password=password")
Dim oAdomdCommand As AdomdCommand = New AdomdCommand()
Dim oAdomdReader As AdomdDataReader
Dim period As String = ddlPeriod.Items(0)
oAdomdCommand.CommandType = CommandType.Text
cmdText = "" & _
"SELECT " & _
"{ BNBTime.[" & period & "] } ON COLUMNS , " & _
"{ Measures.[Total Backlog Snapshot] } ON ROWS " & _
"FROM BNB "
oAdomdCommand.CommandText = cmdText
Try
oAdomdConnection.Open()
oAdomdCommand.Connection = oAdomdConnection
oAdomdReader = oAdomdCommand.ExecuteReader()
Catch ex As Exception
MessageBox.Show(Err.Description)
End Try
Do While oAdomdReader.Read()
BdSalesBacklog = oAdomdReader.GetDecimal(1)
Loop
oAdomdReader.Close()
oAdomdConnection.Close()
I called a developer friend of mine and he explained to me that SSAS requires the logged on user to have permissions in a Role in the AS database.
I created an AD group containing the users to whom I want to allow access, and I gave that group membership in a new AS Role. I assigned the role the permissions I wanted the users to have.
My application now works for all the users who need to run it.
Thanks
Saturday, February 25, 2012
adodb recordset problem
I am trying to access a table that I know exists and has data. But, when I create a recordset and check for RecordCount, I get a result -1 (no records). When I access the same table (using the same program), it reports (and I can view in a dbgrid) 752580 records exist.
Here's some of the code:
The table is originally copied from another database; I use the following code to be sure the previous connection is closed before proceeding.
If Not adoRS Is Nothing Then
If adoRS.State = adStateOpen Then adoRS.Close
Set adoRS = Nothing
End If
If Not DbConn Is Nothing Then
If DbConn.State = adStateOpen Then DbConn.Close
Set DbConn = Nothing
End If
Then a new connection (it works) is opened to access the database with the copied table:
strDbConn = "Provider=SQLNCLI;Integrated Security=SSPI;" & _
"Persist Security Info=False;Database=" & strDbName & ";" & _
"AttachDBFileName=" & DbPath & ";Data Source=.\sqlexpress;" & _
"User Instance=True"
Next I tried to create the recordset:
Set adoNewRS = New ADODB.Recordset 'Set OHLC recordset
Set adoNewRS.ActiveConnection = DestDbConn
adoNewRS.Open TableName, DestDbConn, adOpenDynamic, adLockOptimistic
Next I try to get the RecordCount:
NumRecords = adoNewRS.RecordCount
At this point, NumRecords (and adoNewRS.RecordCount) = -1 (even tho I know there are 752580 records in the table).
In the adoNewRS.Open statement, I also tried using the following sql statement:
sSQL = "SELECT * FROM TableName ORDER BY [DateTime];"
It also returns a recordcount = -1.
Anybody have clue?
While debugging, I noticed that adoNewRS.Open was taking about as long as the SQL query in ssms. So, I added adoNewRS.MoveLast & msgbox adoNewRS!XHigh and it popped up with the correct value. Apparently, the recordset was created correctly but the RecordCount value was wrong (as noted in previous post).
Using this new info, I discovered a PRB:
http://support.microsoft.com/default.aspx?scid=kb;en-us;194973
Apparently, in some cases, CursorLocation = adUseServer returns -1 instead of the correct RecordCount. Changing the value to adUseClient resolved the issue.
adodb connections to a linked server
Hello,
I'd like to create an ado connection to my linked server called Prob_Schd on
a machine called pccn-nt.
When I use the code below I recieve an error of the type ' Invalid
connection string attribute'. Would someone show me how to create an ado
connection to a linked server?
Thanks,
Chieko
dim oConn, strConn
Set oConn = Server.CreateObject("ADODB.Connection")
strConn = "provider=SQLOLEDB; Datasource=Prob_Schd; Initial Catalog=null
trusted_Connection=yes "
oConn.Open strConn
set GetDataConnection = oConnADO doesn't know anything about SQL Server's linked servers. You could
connect locally, and then execute queries like adoConnObject.Execute("SELECT
* FROM Prob_Schd.pubs.dbo.authors") or, you could make an OLEDB connection
directly to the linked server.
The specific error you are getting is because the "Datasource" attribute
you've used should have a space, "Data Source"... but I'm pretty sure ADO
will still be unable to find that server unless it is in the hosts file on
the machine (in other words, it doesn't care if that's a linked server in
SQL Server).
"Chieko Kuroda" <ckuroda@.med.unc.edu> wrote in message
news:O2OVMHuzDHA.3520@.tk2msftngp13.phx.gbl...
on
Catalog=null
I'd like to create an ado connection to my linked server called Prob_Schd on
a machine called pccn-nt.
When I use the code below I recieve an error of the type ' Invalid
connection string attribute'. Would someone show me how to create an ado
connection to a linked server?
Thanks,
Chieko
dim oConn, strConn
Set oConn = Server.CreateObject("ADODB.Connection")
strConn = "provider=SQLOLEDB; Datasource=Prob_Schd; Initial Catalog=null
trusted_Connection=yes "
oConn.Open strConn
set GetDataConnection = oConnADO doesn't know anything about SQL Server's linked servers. You could
connect locally, and then execute queries like adoConnObject.Execute("SELECT
* FROM Prob_Schd.pubs.dbo.authors") or, you could make an OLEDB connection
directly to the linked server.
The specific error you are getting is because the "Datasource" attribute
you've used should have a space, "Data Source"... but I'm pretty sure ADO
will still be unable to find that server unless it is in the hosts file on
the machine (in other words, it doesn't care if that's a linked server in
SQL Server).
"Chieko Kuroda" <ckuroda@.med.unc.edu> wrote in message
news:O2OVMHuzDHA.3520@.tk2msftngp13.phx.gbl...
quote:
> Hello,
> I'd like to create an ado connection to my linked server called Prob_Schd
on
quote:
> a machine called pccn-nt.
> When I use the code below I recieve an error of the type ' Invalid
> connection string attribute'. Would someone show me how to create an ado
> connection to a linked server?
> Thanks,
> Chieko
>
> dim oConn, strConn
> Set oConn = Server.CreateObject("ADODB.Connection")
> strConn = "provider=SQLOLEDB; Datasource=Prob_Schd; Initial
Catalog=null
quote:
> trusted_Connection=yes "
> oConn.Open strConn
> set GetDataConnection = oConn
>
Subscribe to:
Posts (Atom)