Advice on Simple Structured Sql Query (Search)

Hi All,
I have created a well structured cross relationship db.. To extract all
the details out is not a problem but i am trying to now include a
simple search to filter results.. Where is the best place to build the
query, within my .Net web app or within a sp itself..
My basic setup is a table with a list of jobs (Jobs_Jobs) and fields
(JobID, Title, Keywords) and then another table that lists the
locations of the jobs(Jobs_Locations) and fields(ID,JobID,LocationID)..
What i am then trying to do is include a keyword search and a location
search together, i need to make it boolean as well and split keywords
up and then search within these to make sure they are in the
locations.. In my head it seemed fairly approchable but cant seem to
grasp it and cant find any decent tutorials.. Could you please point me
in the right direction: Do i need to be using dynamic sql', Should i
be doing most of the work within .net
A typical search would be for:
Keywords: IT,Graduate
Within Location: 12,35,48
I can do one or the other but not combined.. I also dont know how to
split up the keywords to make them two seperate keywords..
If anyone can point me in the right direction as this is my last
hurdle.. On this project at least :)Have a look at
http://www.sommarskog.se/dyn-search.html and
http://www.sommarskog.se/arrays-in-sql.html
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"ponyoo" <anthonykallay@.hotmail.com> wrote in message
news:1133857887.366250.221060@.g14g2000cwa.googlegroups.com...
> Hi All,
> I have created a well structured cross relationship db.. To extract all
> the details out is not a problem but i am trying to now include a
> simple search to filter results.. Where is the best place to build the
> query, within my .Net web app or within a sp itself..
> My basic setup is a table with a list of jobs (Jobs_Jobs) and fields
> (JobID, Title, Keywords) and then another table that lists the
> locations of the jobs(Jobs_Locations) and fields(ID,JobID,LocationID)..
> What i am then trying to do is include a keyword search and a location
> search together, i need to make it boolean as well and split keywords
> up and then search within these to make sure they are in the
> locations.. In my head it seemed fairly approchable but cant seem to
> grasp it and cant find any decent tutorials.. Could you please point me
> in the right direction: Do i need to be using dynamic sql', Should i
> be doing most of the work within .net
> A typical search would be for:
> Keywords: IT,Graduate
> Within Location: 12,35,48
> I can do one or the other but not combined.. I also dont know how to
> split up the keywords to make them two seperate keywords..
> If anyone can point me in the right direction as this is my last
> hurdle.. On this project at least :)
>

Advice on how to handle backup files

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
>

Advice on how to handle backup files

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
>|||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 Schlter Persson
Databaseadministrator / Systemadministrator|||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 bac
kup is taken and also
remove files older than X days.
Do above with your own TSQL code. This opens the ability for further customi
zation.
Have several backups on same file. For instance, have a Monday backup file w
hich you backup to on
Mondays and overwrite each time. This will give you 7 days worth of backup g
enerations. 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
>

advice on creating a backup plan

Could someone give me some feedback on the backup plan that I have created
for my companies SQL Server. First a little background.
We have 1 win2k advance server running both IIS and our SQL Server. The
database held on the server is the backend to our e-commerce site. The db
needs to be up 24-7 and typically sees the most usage from 9am - 11pm EST.
Atleast half of the tables in the db are modified through the day. There are
no large bulk insertions or deletions done through the day. The MDF and LDF
files are on different hd's.
I would like to create a plan so that in case of massive hardware failur we
could have a complete up-to-date copy of the db back up in less than 4
hours. I would like to backup up my sql databases and the Win2k system
state. The database recovery method is set to FULL.
The backup cycle starts at 6:00 am EST with a complete backup of the
database. Every 2 hours after the complete backup the transaction log is
backed up. Every 6 hours after the complete backup a Differential DB backup
is made.
At 5:00 am the next day those files are copied to another drive and the
cycle starts again with the complete db backup file overwriting itself.
This way I will always have a complete backup of the following day. I also
copy the backed up backups offline to a different server for archiving.
The system db's are backedup every week. Integrity checks are done every
week as well.
Is there anything else I should do like re create indexes as part of my
plan? How often should I back up the system state? Rigth now I back it up
once a week.
Any comments would be appreciated as preventing the loss of data is of the
utmost importance. Thanks in advance.The big thing I see regards WHERE the backups are kept...If you are leaving
them on the SQL box all day, change the plan... backup things to a different
box, and get the backups off of the SQL Server...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"news.microsoft.com" <cramsundar@.mail.com> wrote in message
news:OBt4tPpjDHA.2244@.TK2MSFTNGP12.phx.gbl...
> Could someone give me some feedback on the backup plan that I have created
> for my companies SQL Server. First a little background.
> We have 1 win2k advance server running both IIS and our SQL Server. The
> database held on the server is the backend to our e-commerce site. The db
> needs to be up 24-7 and typically sees the most usage from 9am - 11pm EST.
> Atleast half of the tables in the db are modified through the day. There
are
> no large bulk insertions or deletions done through the day. The MDF and
LDF
> files are on different hd's.
> I would like to create a plan so that in case of massive hardware failur
we
> could have a complete up-to-date copy of the db back up in less than 4
> hours. I would like to backup up my sql databases and the Win2k system
> state. The database recovery method is set to FULL.
> The backup cycle starts at 6:00 am EST with a complete backup of the
> database. Every 2 hours after the complete backup the transaction log is
> backed up. Every 6 hours after the complete backup a Differential DB
backup
> is made.
> At 5:00 am the next day those files are copied to another drive and the
> cycle starts again with the complete db backup file overwriting itself.
> This way I will always have a complete backup of the following day. I
also
> copy the backed up backups offline to a different server for archiving.
> The system db's are backedup every week. Integrity checks are done every
> week as well.
> Is there anything else I should do like re create indexes as part of my
> plan? How often should I back up the system state? Rigth now I back it
up
> once a week.
> Any comments would be appreciated as preventing the loss of data is of the
> utmost importance. Thanks in advance.
>|||We do not have another box to back up the data too. We have a very small
operation. The backups reside on two different hds and will be copied off
the server.
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:Oh4WL2pjDHA.1284@.TK2MSFTNGP09.phx.gbl...
> The big thing I see regards WHERE the backups are kept...If you are
leaving
> them on the SQL box all day, change the plan... backup things to a
different
> box, and get the backups off of the SQL Server...
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "news.microsoft.com" <cramsundar@.mail.com> wrote in message
> news:OBt4tPpjDHA.2244@.TK2MSFTNGP12.phx.gbl...
> > Could someone give me some feedback on the backup plan that I have
created
> > for my companies SQL Server. First a little background.
> >
> > We have 1 win2k advance server running both IIS and our SQL Server. The
> > database held on the server is the backend to our e-commerce site. The
db
> > needs to be up 24-7 and typically sees the most usage from 9am - 11pm
EST.
> > Atleast half of the tables in the db are modified through the day. There
> are
> > no large bulk insertions or deletions done through the day. The MDF and
> LDF
> > files are on different hd's.
> >
> > I would like to create a plan so that in case of massive hardware failur
> we
> > could have a complete up-to-date copy of the db back up in less than 4
> > hours. I would like to backup up my sql databases and the Win2k system
> > state. The database recovery method is set to FULL.
> >
> > The backup cycle starts at 6:00 am EST with a complete backup of the
> > database. Every 2 hours after the complete backup the transaction log is
> > backed up. Every 6 hours after the complete backup a Differential DB
> backup
> > is made.
> >
> > At 5:00 am the next day those files are copied to another drive and the
> > cycle starts again with the complete db backup file overwriting itself.
> > This way I will always have a complete backup of the following day. I
> also
> > copy the backed up backups offline to a different server for archiving.
> >
> > The system db's are backedup every week. Integrity checks are done
every
> > week as well.
> >
> > Is there anything else I should do like re create indexes as part of my
> > plan? How often should I back up the system state? Rigth now I back it
> up
> > once a week.
> >
> > Any comments would be appreciated as preventing the loss of data is of
the
> > utmost importance. Thanks in advance.
> >
> >
>sql

AdventureWorks Install

I am using sqlserver 2005 express edition and have downloaded the
AdventureWorksDB.msi. I have installed it and it created 3 files:
AdventureWorks.mdf (~ 2MB), AdventureWorks_Data.mdf (~ 164MB) and
AdventureWorks_log.idf (~1MB). When I added the database to the sqlserver,
no tables appear under system tables. Is there something I am missing here?
No, it's normal in SQL Server 2005. System tables are hidden and you can't
see or modify them directly.
Regards
Pawel Potasinski
Uytkownik "Richard" <rich_a_wms@.yahoo.com> napisa w wiadomoci
news:u4xAciayHHA.748@.TK2MSFTNGP04.phx.gbl...
>I am using sqlserver 2005 express edition and have downloaded the
> AdventureWorksDB.msi. I have installed it and it created 3 files:
> AdventureWorks.mdf (~ 2MB), AdventureWorks_Data.mdf (~ 164MB) and
> AdventureWorks_log.idf (~1MB). When I added the database to the sqlserver,
> no tables appear under system tables. Is there something I am missing
> here?
>
>
|||I have another DB (storeDB) on the server and I am able to see all the
tables under Databases > System Databases > storeDB > Tables > System
Tables, but I am unable to see the System Tables under AdventureWorks. I am
going through the tutorial and it's asking me to create the following query:
SELECT * FROM Production.Product;
GO
and I receive this error message:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'Production.Product'.
I have a connection the AdventureWorks DB. I'm obviously new to SQL Server
2005.
Any help would be appreciated.
Regards, Richard
"Pawel Potasinski" <pawel.potasinski@.gmail.com> wrote in message
news:OLCmDdcyHHA.1776@.TK2MSFTNGP03.phx.gbl...

> No, it's normal in SQL Server 2005. System tables are hidden and you can't
> see or modify them directly.
> --
> Regards
> Pawel Potasinski
>
> Uytkownik "Richard" <rich_a_wms@.yahoo.com> napisa w wiadomoci
> news:u4xAciayHHA.748@.TK2MSFTNGP04.phx.gbl...
>
|||On Thu, 19 Jul 2007 12:53:50 -0700, Richard wrote:
[vbcol=seagreen]
> I have another DB (storeDB) on the server and I am able to see all the
> tables under Databases > System Databases > storeDB > Tables > System
> Tables, but I am unable to see the System Tables under AdventureWorks. I am
> going through the tutorial and it's asking me to create the following query:
> SELECT * FROM Production.Product;
> GO
> and I receive this error message:
> Msg 208, Level 16, State 1, Line 1
> Invalid object name 'Production.Product'.
> I have a connection the AdventureWorks DB. I'm obviously new to SQL Server
> 2005.
> Any help would be appreciated.
> Regards, Richard
> "Pawel Potasinski" <pawel.potasinski@.gmail.com> wrote in message
> news:OLCmDdcyHHA.1776@.TK2MSFTNGP03.phx.gbl...
Looks like a permissions issue. If you are just testing, make your account
you log in as a Server Role of Sysadmin (God-level rights).
Chuck Lathrope
www.sqlwebpedia.com
|||Thanks, I changed permissions, but I still can't see the tables. The strange
thing is that there are 3 files in the Data folder under the MSSQL folder
for AdventureWorks: AdventureWorks.mdf (~ 2MB), AdventureWorks_Data.mdf (~
164MB) and AdventureWorks_log.idf (~1MB). The other DB's on the server only
have 2 files (.mdf and .idf) for each database. There are no _Data.mdf
files.
Regards, Richard
"Chuck Lathrope" <computerguy_chuck@.fixmehotmail.com> wrote in message
news:rugaxfto4xox$.1xm17ldm6wy7j$.dlg@.40tude.net.. .
> On Thu, 19 Jul 2007 12:53:50 -0700, Richard wrote:
>
> Looks like a permissions issue. If you are just testing, make your account
> you log in as a Server Role of Sysadmin (God-level rights).
> Chuck Lathrope
> www.sqlwebpedia.com

AdventureWorks Install

I am using sqlserver 2005 express edition and have downloaded the
AdventureWorksDB.msi. I have installed it and it created 3 files:
AdventureWorks.mdf (~ 2MB), AdventureWorks_Data.mdf (~ 164MB) and
AdventureWorks_log.idf (~1MB). When I added the database to the sqlserver,
no tables appear under system tables. Is there something I am missing here?No, it's normal in SQL Server 2005. System tables are hidden and you can't
see or modify them directly.
--
Regards
Pawel Potasinski
U¿ytkownik "Richard" <rich_a_wms@.yahoo.com> napisa³ w wiadomo¶ci
news:u4xAciayHHA.748@.TK2MSFTNGP04.phx.gbl...
>I am using sqlserver 2005 express edition and have downloaded the
> AdventureWorksDB.msi. I have installed it and it created 3 files:
> AdventureWorks.mdf (~ 2MB), AdventureWorks_Data.mdf (~ 164MB) and
> AdventureWorks_log.idf (~1MB). When I added the database to the sqlserver,
> no tables appear under system tables. Is there something I am missing
> here?
>
>|||I have another DB (storeDB) on the server and I am able to see all the
tables under Databases > System Databases > storeDB > Tables > System
Tables, but I am unable to see the System Tables under AdventureWorks. I am
going through the tutorial and it's asking me to create the following query:
SELECT * FROM Production.Product;
GO
and I receive this error message:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'Production.Product'.
I have a connection the AdventureWorks DB. I'm obviously new to SQL Server
2005.
Any help would be appreciated.
Regards, Richard
"Pawel Potasinski" <pawel.potasinski@.gmail.com> wrote in message
news:OLCmDdcyHHA.1776@.TK2MSFTNGP03.phx.gbl...
> No, it's normal in SQL Server 2005. System tables are hidden and you can't
> see or modify them directly.
> --
> Regards
> Pawel Potasinski
>
> U¿ytkownik "Richard" <rich_a_wms@.yahoo.com> napisa³ w wiadomo¶ci
> news:u4xAciayHHA.748@.TK2MSFTNGP04.phx.gbl...
>>I am using sqlserver 2005 express edition and have downloaded the
>> AdventureWorksDB.msi. I have installed it and it created 3 files:
>> AdventureWorks.mdf (~ 2MB), AdventureWorks_Data.mdf (~ 164MB) and
>> AdventureWorks_log.idf (~1MB). When I added the database to the
>> sqlserver,
>> no tables appear under system tables. Is there something I am missing
>> here?
>>
>|||On Thu, 19 Jul 2007 12:53:50 -0700, Richard wrote:
> I have another DB (storeDB) on the server and I am able to see all the
> tables under Databases > System Databases > storeDB > Tables > System
> Tables, but I am unable to see the System Tables under AdventureWorks. I am
> going through the tutorial and it's asking me to create the following query:
> SELECT * FROM Production.Product;
> GO
> and I receive this error message:
> Msg 208, Level 16, State 1, Line 1
> Invalid object name 'Production.Product'.
> I have a connection the AdventureWorks DB. I'm obviously new to SQL Server
> 2005.
> Any help would be appreciated.
> Regards, Richard
> "Pawel Potasinski" <pawel.potasinski@.gmail.com> wrote in message
> news:OLCmDdcyHHA.1776@.TK2MSFTNGP03.phx.gbl...
>> No, it's normal in SQL Server 2005. System tables are hidden and you can't
>> see or modify them directly.
>> --
>> Regards
>> Pawel Potasinski
>>
>> U¿ytkownik "Richard" <rich_a_wms@.yahoo.com> napisa³ w wiadomo¶ci
>> news:u4xAciayHHA.748@.TK2MSFTNGP04.phx.gbl...
>>I am using sqlserver 2005 express edition and have downloaded the
>> AdventureWorksDB.msi. I have installed it and it created 3 files:
>> AdventureWorks.mdf (~ 2MB), AdventureWorks_Data.mdf (~ 164MB) and
>> AdventureWorks_log.idf (~1MB). When I added the database to the
>> sqlserver,
>> no tables appear under system tables. Is there something I am missing
>> here?
>>
>>
Looks like a permissions issue. If you are just testing, make your account
you log in as a Server Role of Sysadmin (God-level rights).
Chuck Lathrope
www.sqlwebpedia.com|||Thanks, I changed permissions, but I still can't see the tables. The strange
thing is that there are 3 files in the Data folder under the MSSQL folder
for AdventureWorks: AdventureWorks.mdf (~ 2MB), AdventureWorks_Data.mdf (~
164MB) and AdventureWorks_log.idf (~1MB). The other DB's on the server only
have 2 files (.mdf and .idf) for each database. There are no _Data.mdf
files.
Regards, Richard
"Chuck Lathrope" <computerguy_chuck@.fixmehotmail.com> wrote in message
news:rugaxfto4xox$.1xm17ldm6wy7j$.dlg@.40tude.net...
> On Thu, 19 Jul 2007 12:53:50 -0700, Richard wrote:
>> I have another DB (storeDB) on the server and I am able to see all the
>> tables under Databases > System Databases > storeDB > Tables > System
>> Tables, but I am unable to see the System Tables under AdventureWorks. I
>> am
>> going through the tutorial and it's asking me to create the following
>> query:
>> SELECT * FROM Production.Product;
>> GO
>> and I receive this error message:
>> Msg 208, Level 16, State 1, Line 1
>> Invalid object name 'Production.Product'.
>> I have a connection the AdventureWorks DB. I'm obviously new to SQL
>> Server
>> 2005.
>> Any help would be appreciated.
>> Regards, Richard
>> "Pawel Potasinski" <pawel.potasinski@.gmail.com> wrote in message
>> news:OLCmDdcyHHA.1776@.TK2MSFTNGP03.phx.gbl...
>> No, it's normal in SQL Server 2005. System tables are hidden and you
>> can't
>> see or modify them directly.
>> --
>> Regards
>> Pawel Potasinski
>>
>> U¿ytkownik "Richard" <rich_a_wms@.yahoo.com> napisa³ w wiadomo¶ci
>> news:u4xAciayHHA.748@.TK2MSFTNGP04.phx.gbl...
>>I am using sqlserver 2005 express edition and have downloaded the
>> AdventureWorksDB.msi. I have installed it and it created 3 files:
>> AdventureWorks.mdf (~ 2MB), AdventureWorks_Data.mdf (~ 164MB) and
>> AdventureWorks_log.idf (~1MB). When I added the database to the
>> sqlserver,
>> no tables appear under system tables. Is there something I am missing
>> here?
>>
>>
> Looks like a permissions issue. If you are just testing, make your account
> you log in as a Server Role of Sysadmin (God-level rights).
> Chuck Lathrope
> www.sqlwebpedia.com

AdventureWorks Install

I am using sqlserver 2005 express edition and have downloaded the
AdventureWorksDB.msi. I have installed it and it created 3 files:
AdventureWorks.mdf (~ 2MB), AdventureWorks_Data.mdf (~ 164MB) and
AdventureWorks_log.idf (~1MB). When I added the database to the sqlserver,
no tables appear under system tables. Is there something I am missing here?No, it's normal in SQL Server 2005. System tables are hidden and you can't
see or modify them directly.
Regards
Pawel Potasinski
Uytkownik "Richard" <rich_a_wms@.yahoo.com> napisa w wiadomoci
news:u4xAciayHHA.748@.TK2MSFTNGP04.phx.gbl...
>I am using sqlserver 2005 express edition and have downloaded the
> AdventureWorksDB.msi. I have installed it and it created 3 files:
> AdventureWorks.mdf (~ 2MB), AdventureWorks_Data.mdf (~ 164MB) and
> AdventureWorks_log.idf (~1MB). When I added the database to the sqlserver,
> no tables appear under system tables. Is there something I am missing
> here?
>
>|||I have another DB (storeDB) on the server and I am able to see all the
tables under Databases > System Databases > storeDB > Tables > System
Tables, but I am unable to see the System Tables under AdventureWorks. I am
going through the tutorial and it's asking me to create the following query:
SELECT * FROM Production.Product;
GO
and I receive this error message:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'Production.Product'.
I have a connection the AdventureWorks DB. I'm obviously new to SQL Server
2005.
Any help would be appreciated.
Regards, Richard
"Pawel Potasinski" <pawel.potasinski@.gmail.com> wrote in message
news:OLCmDdcyHHA.1776@.TK2MSFTNGP03.phx.gbl...

> No, it's normal in SQL Server 2005. System tables are hidden and you can't
> see or modify them directly.
> --
> Regards
> Pawel Potasinski
>
> Uytkownik "Richard" <rich_a_wms@.yahoo.com> napisa w wiadomoci
> news:u4xAciayHHA.748@.TK2MSFTNGP04.phx.gbl...
>|||On Thu, 19 Jul 2007 12:53:50 -0700, Richard wrote:
[vbcol=seagreen]
> I have another DB (storeDB) on the server and I am able to see all the
> tables under Databases > System Databases > storeDB > Tables > System
> Tables, but I am unable to see the System Tables under AdventureWorks. I a
m
> going through the tutorial and it's asking me to create the following quer
y:
> SELECT * FROM Production.Product;
> GO
> and I receive this error message:
> Msg 208, Level 16, State 1, Line 1
> Invalid object name 'Production.Product'.
> I have a connection the AdventureWorks DB. I'm obviously new to SQL Server
> 2005.
> Any help would be appreciated.
> Regards, Richard
> "Pawel Potasinski" <pawel.potasinski@.gmail.com> wrote in message
> news:OLCmDdcyHHA.1776@.TK2MSFTNGP03.phx.gbl...
>
Looks like a permissions issue. If you are just testing, make your account
you log in as a Server Role of Sysadmin (God-level rights).
Chuck Lathrope
www.sqlwebpedia.com|||Thanks, I changed permissions, but I still can't see the tables. The strange
thing is that there are 3 files in the Data folder under the MSSQL folder
for AdventureWorks: AdventureWorks.mdf (~ 2MB), AdventureWorks_Data.mdf (~
164MB) and AdventureWorks_log.idf (~1MB). The other DB's on the server only
have 2 files (.mdf and .idf) for each database. There are no _Data.mdf
files.
Regards, Richard
"Chuck Lathrope" <computerguy_chuck@.fixmehotmail.com> wrote in message
news:rugaxfto4xox$.1xm17ldm6wy7j$.dlg@.40tude.net...
> On Thu, 19 Jul 2007 12:53:50 -0700, Richard wrote:
>
> Looks like a permissions issue. If you are just testing, make your account
> you log in as a Server Role of Sysadmin (God-level rights).
> Chuck Lathrope
> www.sqlwebpedia.com

AdventureWorks inserting records to JobCandidates example

Hi
re: AdventureWorks example
Are there source code to how the
HumanResources.JobCandidate
table was created?
(I would like to look at the sample code for INSERTing)
thanks
joyceWhat exactly do you want to know about?
Thanks
Michael
PS: I don't think we have sample code for specifically this table...
"joyce chan" <joyceschan@.fastmail.fm> wrote in message
news:1169588888.525240.259000@.j27g2000cwj.googlegroups.com...
> Hi
> re: AdventureWorks example
> Are there source code to how the
> HumanResources.JobCandidate
> table was created?
> (I would like to look at the sample code for INSERTing)
> thanks
> joyce
>|||Actually, what I am really about is namespace...
So I thought I should take a look at how the JobCandidates table was
created in AdventureWorks.
I understand targetnamespace, and
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
I don't really understand default namespace such as
xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
"
What is the purpose of the def ns?
And also, what is the purpose of the schema collection? And how come a
schema collection can contain multiple schema.
And what is the advantage of typed vs untyped xml in sql server. Also,
does Sql server support any other types of schemas?
I'm still slowly going through the books online material, but it'd be
nice if all these questions are all answered at once.
Thank you, this is a great usenet group,
Joyce
On Jan 24, 8:05 pm, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
wrote:
> What exactly do you want to know about?
> Thanks
> Michael
> PS: I don't think we have sample code for specifically this table...
> "joyce chan" <joycesc...@.fastmail.fm> wrote in messagenews:1169588888.5252
40.259000@.j27g2000cwj.googlegroups.com...
>
>
>
>
>|||See inline below.
Best regards
Michael
"joyce chan" <joyceschan@.fastmail.fm> wrote in message
news:1169736748.697916.238950@.s48g2000cws.googlegroups.com...
> Actually, what I am really about is namespace...
> So I thought I should take a look at how the JobCandidates table was
> created in AdventureWorks.
> I understand targetnamespace, and
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> I don't really understand default namespace such as
> xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resu
me"
> What is the purpose of the def ns?
Default namespaces are just a syntactic convenience in that you do not have
to write a prefix (this is true for XML documents and XQuery, but not XPath
1.0 where no support for default namespace exists, you have to define a
prefix for the query).
Namespaces per se are just scoping mechanisms to scope a name to a space
that can have different semantics associated than the same name in a
different namespace... e.g., river:bank vs finance:bank.

> And also, what is the purpose of the schema collection? And how come a
> schema collection can contain multiple schema.
A schema collection is a meta data object in SQL Server that allows you to
combine multiple schemas in a logical unit to constrain and type an XML data
type instance with. It also introduces a way that allows you to have more
than one version of the same schema within your database. Note that a single
schema defines structural and some semantic constraints for names within a
namespace (including the non-namespace). However not every document having
data with namespaces needs to have a schema.

> And what is the advantage of typed vs untyped xml in sql server. Also,
> does Sql server support any other types of schemas?
Untyped XML gives you the advantage of complete flexibity of your structure
and no additional validation cost.
Typed XML gives you semantical assurances over the structure of the data,
provides error detection at compile time in your queries (using static
typing), and can be used for some storage and query optimizations.
SQL Server has only limited support for DTDs and other wise does not support
other types of schemas.

> I'm still slowly going through the books online material, but it'd be
> nice if all these questions are all answered at once.
> Thank you, this is a great usenet group,
> Joyce
> On Jan 24, 8:05 pm, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
> wrote:
>

AdventureWorks inserting records to JobCandidates example

Hi
re: AdventureWorks example
Are there source code to how the
HumanResources.JobCandidate
table was created?
(I would like to look at the sample code for INSERTing)
thanks
joyce
What exactly do you want to know about?
Thanks
Michael
PS: I don't think we have sample code for specifically this table...
"joyce chan" <joyceschan@.fastmail.fm> wrote in message
news:1169588888.525240.259000@.j27g2000cwj.googlegr oups.com...
> Hi
> re: AdventureWorks example
> Are there source code to how the
> HumanResources.JobCandidate
> table was created?
> (I would like to look at the sample code for INSERTing)
> thanks
> joyce
>
|||Actually, what I am really confused about is namespace...
So I thought I should take a look at how the JobCandidates table was
created in AdventureWorks.
I understand targetnamespace, and
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
I don't really understand default namespace such as
xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"
What is the purpose of the def ns?
And also, what is the purpose of the schema collection? And how come a
schema collection can contain multiple schema.
And what is the advantage of typed vs untyped xml in sql server. Also,
does Sql server support any other types of schemas?
I'm still slowly going through the books online material, but it'd be
nice if all these questions are all answered at once.
Thank you, this is a great usenet group,
Joyce
On Jan 24, 8:05 pm, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
wrote:[vbcol=seagreen]
> What exactly do you want to know about?
> Thanks
> Michael
> PS: I don't think we have sample code for specifically this table...
> "joyce chan" <joycesc...@.fastmail.fm> wrote in messagenews:1169588888.525240.259000@.j27g2000cwj.g ooglegroups.com...
>
>
|||See inline below.
Best regards
Michael
"joyce chan" <joyceschan@.fastmail.fm> wrote in message
news:1169736748.697916.238950@.s48g2000cws.googlegr oups.com...
> Actually, what I am really confused about is namespace...
> So I thought I should take a look at how the JobCandidates table was
> created in AdventureWorks.
> I understand targetnamespace, and
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> I don't really understand default namespace such as
> xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume"
> What is the purpose of the def ns?
Default namespaces are just a syntactic convenience in that you do not have
to write a prefix (this is true for XML documents and XQuery, but not XPath
1.0 where no support for default namespace exists, you have to define a
prefix for the query).
Namespaces per se are just scoping mechanisms to scope a name to a space
that can have different semantics associated than the same name in a
different namespace... e.g., river:bank vs finance:bank.

> And also, what is the purpose of the schema collection? And how come a
> schema collection can contain multiple schema.
A schema collection is a meta data object in SQL Server that allows you to
combine multiple schemas in a logical unit to constrain and type an XML data
type instance with. It also introduces a way that allows you to have more
than one version of the same schema within your database. Note that a single
schema defines structural and some semantic constraints for names within a
namespace (including the non-namespace). However not every document having
data with namespaces needs to have a schema.

> And what is the advantage of typed vs untyped xml in sql server. Also,
> does Sql server support any other types of schemas?
Untyped XML gives you the advantage of complete flexibity of your structure
and no additional validation cost.
Typed XML gives you semantical assurances over the structure of the data,
provides error detection at compile time in your queries (using static
typing), and can be used for some storage and query optimizations.
SQL Server has only limited support for DTDs and other wise does not support
other types of schemas.

> I'm still slowly going through the books online material, but it'd be
> nice if all these questions are all answered at once.
> Thank you, this is a great usenet group,
> Joyce
> On Jan 24, 8:05 pm, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
> wrote:
>
sql

Advanced Editor for DataReader Source / SQLServer 2005

I want to import data from a remote MySql Database

So, I created an New Connection (ADO.Net), connection Test is successful but when I try to set the sql string in the advanced editor i get the following error:

Error at Data Flow Task [DataReader Source [2182]]: System.Data.Odbc.OdbcExeption: ERROR [HY010][MySQL][ODBC 3.51 Driver][mysqld-4.1.10a]
ERROR [HY010][MySQL][ODBC 3.51 Driver][mysqld-4.1.10a]
ERROR [HY010][MySQL][ODBC 3.51 Driver][mysqld-4.1.10a]
ERROR [HY010][MySQL][ODBC 3.51 Driver][mysqld-4.1.10a]
......
bei System.Data.Odbc.OdbcDataReader.NextResult(Boolean.disposing, Boolean allresults)
bei System.Data.Odbc.OdbcDataReader.Close(Boolean Disposing)
bei System.Data.Odbc.OdbcDataReader.Close()
bei Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.ReinitializeMetaData()
bei Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.ReinitializeMetaData(IDTSManagedComponentWrapper90 wrapper)

the sql string is very simple, this should not be the problem:

SELECT Objektnr FROM m02a_tblObjektstamm

Thank you very much for your support

Martin

Could you try to run this query through this provider using any other client?

Thanks.

|||I has the same problem!
if you found the solutions,please tell me.Thanks for you.|||As Bob was asking above, do you get the same error when running your query through a different client, such as a C# application or a query tool? This information would help narrow down whether this is an issue related to the query, to ADO.NET or to SSIS.|||I am having this exact problem as well. The query I've entered in the SqlCommand field in the DataReader Source Edit dialog works fine from the MySQL command-line on other platforms, other boxen. It would then have to be related to .NET Provider for ODBC or SSIS.
|||I should be more clear: I'm using MySQL ODBC 3.51.15, the latest version. It comes with a command line utility (myodbc3m) I can use to manually verify the ODBC connection/configuration that SSIS is using to connect; and it works fine. This seems to exonerate the ODBC driver, ODBC configuration, and database server itself, I would think.
|||Downgrading the MySQL ODBC driver from 3.51.15 to 3.51.12 solved this problem for me. Doesn't make any sense to me.
|||Thanks for the feedback, and for going through this prosess of elimination. I'm not sure what the difference is here either (I don't do much with MySQL) but at least you've gotten past the error.

Advanced Editor for DataReader Source / SQLServer 2005

I want to import data from a remote MySql Database

So, I created an New Connection (ADO.Net), connection Test is successful but when I try to set the sql string in the advanced editor i get the following error:

Error at Data Flow Task [DataReader Source [2182]]: System.Data.Odbc.OdbcExeption: ERROR [HY010][MySQL][ODBC 3.51 Driver][mysqld-4.1.10a]
ERROR [HY010][MySQL][ODBC 3.51 Driver][mysqld-4.1.10a]
ERROR [HY010][MySQL][ODBC 3.51 Driver][mysqld-4.1.10a]
ERROR [HY010][MySQL][ODBC 3.51 Driver][mysqld-4.1.10a]
......
bei System.Data.Odbc.OdbcDataReader.NextResult(Boolean.disposing, Boolean allresults)
bei System.Data.Odbc.OdbcDataReader.Close(Boolean Disposing)
bei System.Data.Odbc.OdbcDataReader.Close()
bei Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.ReinitializeMetaData()
bei Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.ReinitializeMetaData(IDTSManagedComponentWrapper90 wrapper)

the sql string is very simple, this should not be the problem:

SELECT Objektnr FROM m02a_tblObjektstamm

Thank you very much for your support

Martin

Could you try to run this query through this provider using any other client?

Thanks.

|||I has the same problem!
if you found the solutions,please tell me.Thanks for you.|||As Bob was asking above, do you get the same error when running your query through a different client, such as a C# application or a query tool? This information would help narrow down whether this is an issue related to the query, to ADO.NET or to SSIS.|||I am having this exact problem as well. The query I've entered in the SqlCommand field in the DataReader Source Edit dialog works fine from the MySQL command-line on other platforms, other boxen. It would then have to be related to .NET Provider for ODBC or SSIS.
|||I should be more clear: I'm using MySQL ODBC 3.51.15, the latest version. It comes with a command line utility (myodbc3m) I can use to manually verify the ODBC connection/configuration that SSIS is using to connect; and it works fine. This seems to exonerate the ODBC driver, ODBC configuration, and database server itself, I would think.
|||Downgrading the MySQL ODBC driver from 3.51.15 to 3.51.12 solved this problem for me. Doesn't make any sense to me.
|||Thanks for the feedback, and for going through this prosess of elimination. I'm not sure what the difference is here either (I don't do much with MySQL) but at least you've gotten past the error.

ADSI and Reporting Services Permission issue.

I have a stored proc that calls on a view in sql server. The view is
actually my Active Directory LDAP query. I have already created a
linked server and account for my user name jkim.
I have a report that polls data from this stored proc. After I set the
datasource which uses an account "sqluser", I try to the run the report
and I get this:
An error has occurred during report processing. (rsProcessingAborted)
Get Online Help
Query execution failed for data set 'DispatchActivity'.
(rsErrorExecutingCommand) Get Online Help
OLE DB provider 'ADSDSOObject' reported an error. The provider
indicates that the user did not have the permission to perform the
operation.
The data source uses the account "sqluser" which is an account that's
stored in the report server. I'm pretty sure this is what's causing
the error. Does 'sqluser' have to be an actual Active Directory user
for it to have access to the ADSDSOObject?
I don't know if I'm making any sense.nevermind, i figured it out.
i didn't check the "use as windows authentication" checkbox in the
datasource.

ADS user and sql 2005

I wish to use something other than sql's SA account user to connect to
my data warehouse, so I created a user in our active directory user.
Ill use dw as the new user as example.
after I created the user, dw, in ADS, I added the user via Management
Studio in SecurityLogins.
I grant ower of ads\dw to my datawarehouse.
I try to connect to the database engine using SQL Servier
Authentication, Login: ads\dw.
I get Cannot connect to xxxx, Login failed for user 'ads\dw' (Microsoft
SQL Server, Error: 18456).
Next, I add this user to the local server's administrators group (the
server is in admin mode) and login.
Now I can connect to the database as user dw. ( i suspect the users
memebership of administrator is the reason).
I dont wish to have the dw user part of administrator, but I want it to
have control over just the datawarehouse database.
What am I doing wroing?
TIA
RobMore Info:
I checked the server log and the error is state 6. I found a blog on
MSN and it says state 6 is 'Attempt to use a Windows login name with
SQL Authentication'.
Right, exactly what I thought I wanted to do.
I thought that when I added a windows user to a sql servers security
and login, that windows user can access the sql server??|||You need to give this user explicit credentials, typically make hime a
member of a role which has the right you need.

In SQL 2000 you do this under security. It's quite simple.

Regards,
Henrik

*** Sent via Developersdex http://www.developersdex.com ***|||rcamarda (robc390@.hotmail.com) writes:

Quote:

Originally Posted by

I wish to use something other than sql's SA account user to connect to
my data warehouse, so I created a user in our active directory user.
Ill use dw as the new user as example.
after I created the user, dw, in ADS, I added the user via Management
Studio in SecurityLogins.
I grant ower of ads\dw to my datawarehouse.
I try to connect to the database engine using SQL Servier
Authentication, Login: ads\dw.
I get Cannot connect to xxxx, Login failed for user 'ads\dw' (Microsoft
SQL Server, Error: 18456).

Mixing apples and oranges, I see. To log into SQL Server as ADS\dw,
you need to be logged into Windows as ADS\dw. That's what integrated
security is all about. By already being authenticated by Windows,
there is no need for SQL Server to authenticate you again. But you
cannot log into SQL Server with another Windows login than the one
you are logged into Windows with. You can only log into SQL Server
with an explicit username/password with an SQL login.

Quote:

Originally Posted by

Next, I add this user to the local server's administrators group (the
server is in admin mode) and login.

And dw now has sysadmin rights in the server, unless you remove
BUILTIN\Administrators.

Quote:

Originally Posted by

Now I can connect to the database as user dw. ( i suspect the users
memebership of administrator is the reason).
I dont wish to have the dw user part of administrator, but I want it to
have control over just the datawarehouse database.
What am I doing wroing?

First descide whether it's a Windows login or an SQL Login you want.
Next grant this user access to the server and database. Next you grant
him CONTROL on the database. (You are on SQL 2005, right?)

--
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|||Erland,
Yes I am on sql 2005. I am using Cognos' ReportNet (Now Cognos8) to
connect to its Content Store, a database. I need to provide a user and
password. I thought I would set up a user on ADS and provide the
account and password.
Out of confusion/frustration/ignorance I created a local user within
SQL server and it works just fine.
(I have several SQL servers for the database, and I thought using ADS
for user logins and authentication would be better).
So, was my problem more to do with trying to connect as another windows
user with the SQL Management tool? (I did not try to configure Cognos
since I could not connect via the SQL Studio)
Thanks for your help and any other pointers
Rob

ADP dont shows table from MSDE

Hi,

I created one blank ADP to connect default tempdb database on MSDE on server ASHFAQUEPC (name of my msde server). Tested the connection was successful.

I created a table called Testtable from command prompt (MSSQL/Binn) in default tempdb of MSDE using following lines:

Binn>osql/U sa mypassword

1> use tempdb
1> go
1> Create table Testtable (Cnum integer NOT NULL PRIMARY KEY, City Varchar(15))
1> go

Table Created successfully then I added 2-3 records using following lines;

1> INSERT INTO testable (cnum, city)
1> values (1, abc)
1> go

1 Record affected and I did same to add 2-3 more records. Then I used following lines to see all the records I entered.

1> select * from testable
1> go

And a list of records displayed at command prompt. After exit from command prompt I found a Testtable appeared in the above ADP.

But next day when I opened my pc and opened ADP, I did not found the Testtable even though I re-connected to tempdb on MSDE from File menu. I reached then to command prompt to see whether the table itself is existing. Strange it was not there.

Can somebody tell me how can I get connected to testable automatically when I open my ADP? Where might have gone wrong? Do I need to create table again and input records?
I want to make sure before I do anything with my real dbs.

Any help would be highly appreciated.

With kind regards,
Ashfaque :confused:tempdb is recreated everytime sql server restarts. msde restarts when the last connection is closed. create a database with the create database command and create your table in there.|||Thanks Thrasymachus,

IC,
I will create my own db from scratch.

Thanks again....

Wiith kind regards,
Ashfaque|||You should definitely not be mucking around in TEMPDB, MASTER, or MSDB. Even MODEL should not be modified without good cause. Leave the system databases alone, and I'd advise you to read the manual BEFORE trying to fly the plane.|||What the blind dude is saying (with his usual grace and elegance), is that you really need to create a datbase for your purposes, and create all of you objects in there.

tempdb is a critical part of the sql server architecture that allows it to manage your "temporary" objects you can create. Temp tables, derived tables, etc. You should not interfere with it's operation.

btw, have you had a look at SQL Server Express and XM Manager?

It's still beta, but it should be coming out full time soon.

http://download.microsoft.com/download/a/6/3/a63ec922-913e-4228-93e3-970abff3fd66/ReadmeSQLExpMgr.htm|||Thanks blindman & Brett Kaiser for your advice.

I was not aware of behavior of these 4 default models on MSDE.

I upsized my MDB and it successfully created ADP and found steady. I can now go on to take further actions over it. I am close to mid-level carrier in handling MDBs in form of FE and BE. But as a little advance step, I used MSDE and I hope I could deal with it successfully with the help you Genius guys.
btw, have you had a look at SQL Server Express and XM Manager?
No, I did not take a look at SQL Server Express and XM Manager but I will do so.

Thanks again for you people..

With kind regards,
Ashfaque|||One more question...

I upsized the tables on MSDE successfully keeping the current db as MDB.

Why I dont know , the record in my subform is appearing but I can not enter new records any more..(Be is on MSDE). If my tables are on local (Not on MSDE server) it works fine.

Any idea ...?

With kind regards,

Ashfaque|||Could be a permissions issue with your login. Do you get any error messages?

AdomdConnection Exception ........

Hi

I had created a dataminig model using "Asociattion Rules " , as the model is now working i want to get the rules out of it , so I try the following query in SQL 2005 Managanment Studio and its working fine

SELECT NODE_RULE FROM [Credit Card Table].CONTENT

Now I have to do this through code so used following code from book "DataMining using SQL Server 2005" , but the code gives error in making connection and says

"Either VMWINXP/Adminsittrator dont have right on the database SmartRulesApps or database not exsists" , now i use same credentials to run query in SQL 2005 Managmnet Studio and its working ! can you guys give me some hint whats i done wrong . One more point I check the database secuirty folder for my database SmartRulesApps and it contains dbo but how can i add VMWINXP/Adminsittrator(which is already my system admin ) to this security users ? i tried but its not working. Please let me know any thing you find that can help , you can also send me email at razi_rais@.yahoo.com

private void OpenConnection()
{
AdomdConnection con = new AdomdConnection("location=localhost;Initial Catalog=SmartRulesEngine;Integrated Security=SSPI");
// AdomdConnection("location=VMWINXP;Effective UserName=VMWINXP\\Administrator;Initial Catalog=SmartRulesEngine;");

try
{
con.Open();

AdomdCommand cmd = new AdomdCommand();

cmd.CommandText = "SELECT NODE_RULE FROM [Credit Card Table].CONTENT";
cmd.Connection = con;
AdomdDataReader reader;

reader = cmd.ExecuteReader();
System.Collections.ArrayList lst = new System.Collections.ArrayList();
int i = 0;
while (reader.Read())
{
lst.Add(reader.GetValue(i++).ToString());
}
reader.Close();
}
catch (System.Exception exp)
{

}
finally
{
con.Close();
}
}

This looks really strange, given that your error message mentions the SmartRulesApps catalog, while your code seems to use the SmartRulesEngine catalog.

What is the actual catalog name?

AdomdConnection Exception ........

Hi

I had created a dataminig model using "Asociattion Rules " , as the model is now working i want to get the rules out of it , so I try the following query in SQL 2005 Managanment Studio and its working fine

SELECT NODE_RULE FROM [Credit Card Table].CONTENT

Now I have to do this through code so used following code from book "DataMining using SQL Server 2005" , but the code gives error in making connection and says

"Either VMWINXP/Adminsittrator dont have right on the database SmartRulesApps or database not exsists" , now i use same credentials to run query in SQL 2005 Managmnet Studio and its working ! can you guys give me some hint whats i done wrong . One more point I check the database secuirty folder for my database SmartRulesApps and it contains dbo but how can i add VMWINXP/Adminsittrator(which is already my system admin ) to this security users ? i tried but its not working. Please let me know any thing you find that can help , you can also send me email at razi_rais@.yahoo.com

private void OpenConnection()
{
AdomdConnection con = new AdomdConnection("location=localhost;Initial Catalog=SmartRulesEngine;Integrated Security=SSPI");
// AdomdConnection("location=VMWINXP;Effective UserName=VMWINXP\\Administrator;Initial Catalog=SmartRulesEngine;");

try
{
con.Open();

AdomdCommand cmd = new AdomdCommand();

cmd.CommandText = "SELECT NODE_RULE FROM [Credit Card Table].CONTENT";
cmd.Connection = con;
AdomdDataReader reader;

reader = cmd.ExecuteReader();
System.Collections.ArrayList lst = new System.Collections.ArrayList();
int i = 0;
while (reader.Read())
{
lst.Add(reader.GetValue(i++).ToString());
}
reader.Close();
}
catch (System.Exception exp)
{

}
finally
{
con.Close();
}
}

This looks really strange, given that your error message mentions the SmartRulesApps catalog, while your code seems to use the SmartRulesEngine catalog.

What is the actual catalog name?

ADO with SQLXMLOLEDB

A command is created on a connection with SQLXMLOLEDB provider(SQLXML4.0).

1. Where do you find information for the [cmd.dialect]

2. With you can provide an InputFile and get an outputFile. What are the formats of these files?

3. What are the appropraite argumetns for the cmd's execute method?

Can you provide a link for SQLXML4.0 download?

Appreciate even part answers.

Thanks.

Jayaram Krishnaswamy

my web site: http://www.angelfire.com/vt2/hodentek

I blog at: http://hodentek.blogspot.com

The inputFile is really a template file. The structure of thsi file seems to have changed from version 3.0 to version 4.0.

If the inputFile(template) sent a SQL statement, it shound be executed on the server and the outFile should have the result in XML.

So far the only thing that has happnned is that the inpuTemplate file is read, the outpuFile is produced, but alas! it is the exact reproduction of input file.

inputFile: "Select * from Employees for xml raw"

outputFile: "Select * from Employees for xml raw"

Conclusions at this point:

The query never reached the server. so there appears to be a feed forward from inputFile to outFile bypassing the server.

Action Itemsleft:

find documenttion!

ADO Recordset result cannot display Chinese

Hi all,
I have created an application that get Chinese data from SQL Server 2000.
I am using ActiveX Data Object 2.5 via ODBC as connection. Most PC work
perfectly. However, there is 1 client PC that cannot display Chinese. It
always show ?. Can anyone give me some tips?
Regards,
Edward HuiIs this a unicode capable client (what version of windows?) with recent
versions of ODBC drivers?
"Edward Hui" <edhui@.asl.com.hk> wrote in message
news:OaUWhmz2DHA.1760@.TK2MSFTNGP10.phx.gbl...

quote:

---

> Hi all,
> I have created an application that get Chinese data from SQL Server

---

2000.

quote:

---

> I am using ActiveX Data Object 2.5 via ODBC as connection. Most PC work
> perfectly. However, there is 1 client PC that cannot display Chinese. It
> always show ?. Can anyone give me some tips?
> Regards,
> Edward Hui
>

---

AdjustTokenPrivileges Error?

Hi,

I have created two packages.. a child package and a main package that is responsable by executing all his childs in a specific order...

What start happening is that the child when executed alone is ok, but the the main package executes it... then i get a DOS Windows saying "AdjustTokenPrivileges () Failed (00000514)"

What can be causing this? I haven't changed anything and the package was executing right some days ago...

Best Regards,

Luis Sim?es

I have one package that is causing this error as well. Anyone know why? It only generates the error when I try to insert into a database. otherwise it does not generate this error and runs fine.

AdjustTokenPrivileges Error?

Hi,

I have created two packages.. a child package and a main package that is responsable by executing all his childs in a specific order...

What start happening is that the child when executed alone is ok, but the the main package executes it... then i get a DOS Windows saying "AdjustTokenPrivileges () Failed (00000514)"

What can be causing this? I haven't changed anything and the package was executing right some days ago...

Best Regards,

Luis Sim?es

I have one package that is causing this error as well. Anyone know why? It only generates the error when I try to insert into a database. otherwise it does not generate this error and runs fine.