can't see DB on SQL 2005 with SQL management studio

I am having a strange issue with SQL Management studio express. I can
log via the front end, but I do not see the DB for which this user is
"owner"
This is not the odd part. The odd part is that if I log in through a
3rd party front end such as EMS sql manager lite I do see the database
I own, and can manipulate it as I please. Can anyone suggest a reason
for this. Here are details of the setup just in case. The DB server
only has one DB other then the default system DB's. The owner of the db
I am having issue with has full permissions on the DB, but is not a
member of ANY server roles. There is a firewall on the machine but it
is allowing tcp connections on 1433. I have not set any connection
restrictions. The only major security change done on the server was to
use:
use master
Revoke View any database from public
go
AND
use *db I am working with*
Exec Sp_changedbowner ' *user that will own the db I am working with* '
Hi
Have you logged out/in since changing the owner of the database? Have you
tried to create a new database as that login, and can you see it? Have you
applied SP1?
John
"pr0f1t" wrote:

> I am having a strange issue with SQL Management studio express. I can
> log via the front end, but I do not see the DB for which this user is
> "owner"
> This is not the odd part. The odd part is that if I log in through a
> 3rd party front end such as EMS sql manager lite I do see the database
> I own, and can manipulate it as I please. Can anyone suggest a reason
> for this. Here are details of the setup just in case. The DB server
> only has one DB other then the default system DB's. The owner of the db
> I am having issue with has full permissions on the DB, but is not a
> member of ANY server roles. There is a firewall on the machine but it
> is allowing tcp connections on 1433. I have not set any connection
> restrictions. The only major security change done on the server was to
> use:
> use master
> Revoke View any database from public
> go
> AND
> use *db I am working with*
> Exec Sp_changedbowner ' *user that will own the db I am working with* '
>
|||that is a fantastic suggestion. I don't knwo why I did not try this
first. Yes the new DB is visible if I make this user owner. So why is
the origional DB not visible if this user is owner of that as well? Any
thoughts
John Bell wrote:[vbcol=seagreen]
> Hi
> Have you logged out/in since changing the owner of the database? Have you
> tried to create a new database as that login, and can you see it? Have you
> applied SP1?
> John
> "pr0f1t" wrote:
|||that is a fantastic suggestion. I don't knwo why I did not try this
first. Yes the new DB is visible if I make this user owner. So why is
the origional DB not visible if this user is owner of that as well? Any
thoughts
|||Hi
That sounds like the change of ownership has not been reflected in the other
database. For the new database did you create the database as the login or
change the database to be owned by the login?
If you log in as a sysadm you should be able to see that database?
Try changing the owner again to a different login and then change it back.
If you run SELECT * FROM master.sys.databases what does it return?
Try regranting the VIEW ALL DATABASES to public and then just deny it to
specific logins.
What version of SQL 2005 are you using?
John
"pr0f1t" wrote:

> that is a fantastic suggestion. I don't knwo why I did not try this
> first. Yes the new DB is visible if I make this user owner. So why is
> the origional DB not visible if this user is owner of that as well? Any
> thoughts
>
> John Bell wrote:
>
|||*dbname*5NULL0xC1F615C4866765479D8B0AD5FD9FDEAA2006-10-31
14:53:43.06080SQL_Latin1_General_CP1_CI_AS0MULTI_USER0000ONLINE0000OFF03SIMPLE1TORN_PAGE_DETECTION1100000000000000000000009063F0BE-B2B1-4A45-9036-8775AAB1B47A00NOTHING0
is the result of SELECT * FROM master.sys.databases
I am running SQL Server 9.0.2047. I tried to change the ownership of
the DB but it only results in errors pretaining to object and schema
ownershipo, but I cannot seem to change the ownership to another user.
Ifs there a way to do this with TSQL? I have one user that essentially
needs to hve ownership of the DB, and access via a front end
porefereably Microsoft SQL managment studio express.
John Bell wrote:
|||Hi
Can you post the exact error number and message that occurs when you try to
change the owner? I assume you are using sp_changedbowner to do this?
Try creating a new user and changing the schema ownership to them
John
"pr0f1t" wrote:

> *dbname*5NULL0xC1F615C4866765479D8B0AD5FD9FDEAA2006-10-31
> 14:53:43.06080SQL_Latin1_General_CP1_CI_AS0MULTI_USER0000ONLINE0000OFF03SIMPLE1TORN_PAGE_DETECTION1100000000000000000000009063F0BE-B2B1-4A45-9036-8775AAB1B47A00NOTHING0
>
> is the result of SELECT * FROM master.sys.databases
> I am running SQL Server 9.0.2047. I tried to change the ownership of
> the DB but it only results in errors pretaining to object and schema
> ownershipo, but I cannot seem to change the ownership to another user.
> Ifs there a way to do this with TSQL? I have one user that essentially
> needs to hve ownership of the DB, and access via a front end
> porefereably Microsoft SQL managment studio express.
>
> John Bell wrote:
>

can't see DB on SQL 2005 with SQL management studio

I am having a strange issue with SQL Management studio express. I can
log via the front end, but I do not see the DB for which this user is
"owner"
This is not the odd part. The odd part is that if I log in through a
3rd party front end such as EMS sql manager lite I do see the database
I own, and can manipulate it as I please. Can anyone suggest a reason
for this. Here are details of the setup just in case. The DB server
only has one DB other then the default system DB's. The owner of the db
I am having issue with has full permissions on the DB, but is not a
member of ANY server roles. There is a firewall on the machine but it
is allowing tcp connections on 1433. I have not set any connection
restrictions. The only major security change done on the server was to
use:
use master
Revoke View any database from public
go
AND
use *db I am working with*
Exec Sp_changedbowner ' *user that will own the db I am working with* 'Hi
Have you logged out/in since changing the owner of the database? Have you
tried to create a new database as that login, and can you see it? Have you
applied SP1?
John
"pr0f1t" wrote:

> I am having a strange issue with SQL Management studio express. I can
> log via the front end, but I do not see the DB for which this user is
> "owner"
> This is not the odd part. The odd part is that if I log in through a
> 3rd party front end such as EMS sql manager lite I do see the database
> I own, and can manipulate it as I please. Can anyone suggest a reason
> for this. Here are details of the setup just in case. The DB server
> only has one DB other then the default system DB's. The owner of the db
> I am having issue with has full permissions on the DB, but is not a
> member of ANY server roles. There is a firewall on the machine but it
> is allowing tcp connections on 1433. I have not set any connection
> restrictions. The only major security change done on the server was to
> use:
> use master
> Revoke View any database from public
> go
> AND
> use *db I am working with*
> Exec Sp_changedbowner ' *user that will own the db I am working with* '
>|||that is a fantastic suggestion. I don't knwo why I did not try this
first. Yes the new DB is visible if I make this user owner. So why is
the origional DB not visible if this user is owner of that as well? Any
thoughts
John Bell wrote:[vbcol=seagreen]
> Hi
> Have you logged out/in since changing the owner of the database? Have you
> tried to create a new database as that login, and can you see it? Have you
> applied SP1?
> John
> "pr0f1t" wrote:
>|||that is a fantastic suggestion. I don't knwo why I did not try this
first. Yes the new DB is visible if I make this user owner. So why is
the origional DB not visible if this user is owner of that as well? Any
thoughts|||Hi
That sounds like the change of ownership has not been reflected in the other
database. For the new database did you create the database as the login or
change the database to be owned by the login?
If you log in as a sysadm you should be able to see that database?
Try changing the owner again to a different login and then change it back.
If you run SELECT * FROM master.sys.databases what does it return?
Try regranting the VIEW ALL DATABASES to public and then just deny it to
specific logins.
What version of SQL 2005 are you using?
John
"pr0f1t" wrote:

> that is a fantastic suggestion. I don't knwo why I did not try this
> first. Yes the new DB is visible if I make this user owner. So why is
> the origional DB not visible if this user is owner of that as well? Any
> thoughts
>
> John Bell wrote:
>|||*dbname* 5 NULL 0xC1F615C4866765479D8B0A
D5FD9FDEAA 2006-10-31
14:53:43. 060 80 SQL_Latin1_General_CP1_CI_AS 0 MU
LTI_USER 0 0 0 0 ONLINE 0 0
0 0 OFF 0 3 SIMPLE 1 TORN_PAGE_DETECTIO
N 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 9063F0BE-B2B1-4A45-9036-8775AAB1B47A 0 0 NOTHING 0
is the result of SELECT * FROM master.sys.databases
I am running SQL Server 9.0.2047. I tried to change the ownership of
the DB but it only results in errors pretaining to object and schema
ownershipo, but I cannot seem to change the ownership to another user.
Ifs there a way to do this with TSQL? I have one user that essentially
needs to hve ownership of the DB, and access via a front end
porefereably Microsoft SQL managment studio express.
John Bell wrote:|||Hi
Can you post the exact error number and message that occurs when you try to
change the owner? I assume you are using sp_changedbowner to do this?
Try creating a new user and changing the schema ownership to them
John
"pr0f1t" wrote:

> *dbname* 5 NULL 0xC1F615C4866765479D8B0A
D5FD9FDEAA 2006-10-31
> 14:53:43. 060 80 SQL_Latin1_General_CP1_CI_AS 0 MU
LTI_USER 0 0 0 0 ONLINE 0
0 0 0 OFF 0 3 SIMPLE 1 TORN_PAGE_DETECT
ION 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 9063F0BE-B2B1-4A45-9036-8775AAB1B47A 0 0 NOTHING 0
>
> is the result of SELECT * FROM master.sys.databases
> I am running SQL Server 9.0.2047. I tried to change the ownership of
> the DB but it only results in errors pretaining to object and schema
> ownershipo, but I cannot seem to change the ownership to another user.
> Ifs there a way to do this with TSQL? I have one user that essentially
> needs to hve ownership of the DB, and access via a front end
> porefereably Microsoft SQL managment studio express.
>
> John Bell wrote:
>

can't see DB on SQL 2005 with SQL management studio

I am having a strange issue with SQL Management studio express. I can
log via the front end, but I do not see the DB for which this user is
"owner"
This is not the odd part. The odd part is that if I log in through a
3rd party front end such as EMS sql manager lite I do see the database
I own, and can manipulate it as I please. Can anyone suggest a reason
for this. Here are details of the setup just in case. The DB server
only has one DB other then the default system DB's. The owner of the db
I am having issue with has full permissions on the DB, but is not a
member of ANY server roles. There is a firewall on the machine but it
is allowing tcp connections on 1433. I have not set any connection
restrictions. The only major security change done on the server was to
use:
use master
Revoke View any database from public
go
AND
use *db I am working with*
Exec Sp_changedbowner ' *user that will own the db I am working with* 'Hi
Have you logged out/in since changing the owner of the database? Have you
tried to create a new database as that login, and can you see it? Have you
applied SP1?
John
"pr0f1t" wrote:
> I am having a strange issue with SQL Management studio express. I can
> log via the front end, but I do not see the DB for which this user is
> "owner"
> This is not the odd part. The odd part is that if I log in through a
> 3rd party front end such as EMS sql manager lite I do see the database
> I own, and can manipulate it as I please. Can anyone suggest a reason
> for this. Here are details of the setup just in case. The DB server
> only has one DB other then the default system DB's. The owner of the db
> I am having issue with has full permissions on the DB, but is not a
> member of ANY server roles. There is a firewall on the machine but it
> is allowing tcp connections on 1433. I have not set any connection
> restrictions. The only major security change done on the server was to
> use:
> use master
> Revoke View any database from public
> go
> AND
> use *db I am working with*
> Exec Sp_changedbowner ' *user that will own the db I am working with* '
>|||that is a fantastic suggestion. I don't knwo why I did not try this
first. Yes the new DB is visible if I make this user owner. So why is
the origional DB not visible if this user is owner of that as well? Any
thoughts
John Bell wrote:
> Hi
> Have you logged out/in since changing the owner of the database? Have you
> tried to create a new database as that login, and can you see it? Have you
> applied SP1?
> John
> "pr0f1t" wrote:
> > I am having a strange issue with SQL Management studio express. I can
> > log via the front end, but I do not see the DB for which this user is
> > "owner"
> > This is not the odd part. The odd part is that if I log in through a
> > 3rd party front end such as EMS sql manager lite I do see the database
> > I own, and can manipulate it as I please. Can anyone suggest a reason
> > for this. Here are details of the setup just in case. The DB server
> > only has one DB other then the default system DB's. The owner of the db
> > I am having issue with has full permissions on the DB, but is not a
> > member of ANY server roles. There is a firewall on the machine but it
> > is allowing tcp connections on 1433. I have not set any connection
> > restrictions. The only major security change done on the server was to
> > use:
> >
> > use master
> > Revoke View any database from public
> > go
> >
> > AND
> >
> > use *db I am working with*
> > Exec Sp_changedbowner ' *user that will own the db I am working with* '
> >
> >|||that is a fantastic suggestion. I don't knwo why I did not try this
first. Yes the new DB is visible if I make this user owner. So why is
the origional DB not visible if this user is owner of that as well? Any
thoughts|||Hi
That sounds like the change of ownership has not been reflected in the other
database. For the new database did you create the database as the login or
change the database to be owned by the login?
If you log in as a sysadm you should be able to see that database?
Try changing the owner again to a different login and then change it back.
If you run SELECT * FROM master.sys.databases what does it return?
Try regranting the VIEW ALL DATABASES to public and then just deny it to
specific logins.
What version of SQL 2005 are you using?
John
"pr0f1t" wrote:
> that is a fantastic suggestion. I don't knwo why I did not try this
> first. Yes the new DB is visible if I make this user owner. So why is
> the origional DB not visible if this user is owner of that as well? Any
> thoughts
>
> John Bell wrote:
> > Hi
> >
> > Have you logged out/in since changing the owner of the database? Have you
> > tried to create a new database as that login, and can you see it? Have you
> > applied SP1?
> >
> > John
> >
> > "pr0f1t" wrote:
> >
> > > I am having a strange issue with SQL Management studio express. I can
> > > log via the front end, but I do not see the DB for which this user is
> > > "owner"
> > > This is not the odd part. The odd part is that if I log in through a
> > > 3rd party front end such as EMS sql manager lite I do see the database
> > > I own, and can manipulate it as I please. Can anyone suggest a reason
> > > for this. Here are details of the setup just in case. The DB server
> > > only has one DB other then the default system DB's. The owner of the db
> > > I am having issue with has full permissions on the DB, but is not a
> > > member of ANY server roles. There is a firewall on the machine but it
> > > is allowing tcp connections on 1433. I have not set any connection
> > > restrictions. The only major security change done on the server was to
> > > use:
> > >
> > > use master
> > > Revoke View any database from public
> > > go
> > >
> > > AND
> > >
> > > use *db I am working with*
> > > Exec Sp_changedbowner ' *user that will own the db I am working with* '
> > >
> > >
>|||*dbname* 5 NULL 0xC1F615C4866765479D8B0AD5FD9FDEAA 2006-10-31
14:53:43.060 80 SQL_Latin1_General_CP1_CI_AS 0 MULTI_USER 0 0 0 0 ONLINE 0 0 0 0 OFF 0 3 SIMPLE 1 TORN_PAGE_DETECTION 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 9063F0BE-B2B1-4A45-9036-8775AAB1B47A 0 0 NOTHING 0
is the result of SELECT * FROM master.sys.databases
I am running SQL Server 9.0.2047. I tried to change the ownership of
the DB but it only results in errors pretaining to object and schema
ownershipo, but I cannot seem to change the ownership to another user.
Ifs there a way to do this with TSQL? I have one user that essentially
needs to hve ownership of the DB, and access via a front end
porefereably Microsoft SQL managment studio express.
John Bell wrote:|||Hi
Can you post the exact error number and message that occurs when you try to
change the owner? I assume you are using sp_changedbowner to do this?
Try creating a new user and changing the schema ownership to them
John
"pr0f1t" wrote:
> *dbname* 5 NULL 0xC1F615C4866765479D8B0AD5FD9FDEAA 2006-10-31
> 14:53:43.060 80 SQL_Latin1_General_CP1_CI_AS 0 MULTI_USER 0 0 0 0 ONLINE 0 0 0 0 OFF 0 3 SIMPLE 1 TORN_PAGE_DETECTION 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 9063F0BE-B2B1-4A45-9036-8775AAB1B47A 0 0 NOTHING 0
>
> is the result of SELECT * FROM master.sys.databases
> I am running SQL Server 9.0.2047. I tried to change the ownership of
> the DB but it only results in errors pretaining to object and schema
> ownershipo, but I cannot seem to change the ownership to another user.
> Ifs there a way to do this with TSQL? I have one user that essentially
> needs to hve ownership of the DB, and access via a front end
> porefereably Microsoft SQL managment studio express.
>
> John Bell wrote:
>

Can't see a particular table in a database

Hello,
I am trying to get info from a SQL7 database via ODBC. I can connect and
view the vast majority of tables, but one particular table (ceninfo_data)
does not appear.
In SQL Enterprise manager, I have taken a look at the permissions on the
table and public can select from it.
What am I missing?
Cheers
CQMMAN
"See, free nations are peaceful nations. Free nations don't attack each
other. Free nations don't develop weapons of mass destruction." George W
Bush -Milwaukee, Wis., Oct. 3, 2003Doh!
Fixed it. I was replicating a database and had changed the permissions on
the original, but not on the replicated database.
Cheers
"CQMMAN" <cqmman@.yahoo.co.uk> wrote in message
news:bq2jmp$1uhsi3$1@.ID-215193.news.uni-berlin.de...

quote:

---

> Hello,
> I am trying to get info from a SQL7 database via ODBC. I can connect and
> view the vast majority of tables, but one particular table (ceninfo_data)
> does not appear.
> In SQL Enterprise manager, I have taken a look at the permissions on the
> table and public can select from it.
> What am I missing?
> Cheers
> CQMMAN
> "See, free nations are peaceful nations. Free nations don't attack each
> other. Free nations don't develop weapons of mass destruction." George W
> Bush -Milwaukee, Wis., Oct. 3, 2003
>

---

Can''t run SSIS package via Agent

I've seen many postings on various forums on how to get SQL agent to execute an SSIS package. I have one that was originally created via the import wizard in SSMS, later modified in VS2005, and then re-imported in SSMS using the Object Explorer interface to load the dtsx file into SQL storage. I've tried several package protection options when importing the package: "Don't save sensitive data" (the package has no passwords in it), server storage and roles, and a specified package password (which I entered as a /DECRYPT command line parameter in the Agent job step). Agent is running using a domain admin account. No matter what I've tried, I still get an error during job execution that it failed to decrypt the password XML node. The package runs just fine when executed manually in the SSIS Object Explorer.

How can I resolve this?

Are you sure you have been talking about the same packge? You have clearly tried the options, including those which mean there is no encryption to decrypt, so it makes no sense. I have often got confused between versions or a similar mix up when frustated and trying to debug an issue like this.

|||

And by this, I believe that darren means versions of the same package. I have had similar problems in the past where I will continue to bang my head against the wall knowing that something should be working before remembering that I didn't copy the latest version of the package over to the release folder, or from the release folder to the sql package storage etc...

Anywho, I would try pushing it out with encrypt sensitive with password and if it asks you for a password when you are setting up the package in the job step you know that you have the correct version... If you use the sql package store on your ssis server, I would use the serverstorage method of encryption.

|||

Bear with me -- this is my first SSIS project; I've only used DTS in the past. I have only one version of the dtsx file. I've been setting the protection options when I import the file into SSIS via the object explorer. When you say "push it out with 'encrypt sensitive with password'", do you mean there is some way to set that option in the VS designer IDE? When I tried the pw option on import to SSIS, I did not get prompted for the pw when I created the Agent job step. Instead, I put a /DECRYPT switch in the job step's command line options. Please explain.

(And thanks very much for your help!)

|||yes there is a way to set this in the visual studios designer. Right click on the background of the control flow task of your package and select properties. About half way down the list there is a property for "protection level" where there is a drop down for the different types of encryption. If you choose encrypt sensitive w/ password it will prompt you for a password and a confirm password to encrypt the package with. After you have filled this out, any time that you go to the job step maintenance screen it will prompt you for the password (to verify your right to see the connection strings and whatnot).|||

That worked! I found the property setting in the designer and set it as you suggested. When I re-imported the dtsx file into SSIS on the production server, I also set the same protection option and entered the package pw set in the designer. I then deleted the old Agent job step and created a new one. It did not prompt for the package password, but it did execute the SSIS package this time. I saw no place in the various settings tabs for a package pw, nor did it prompt me for one, and I did not alter the command line defaults. Should it have prompted for a pw?

At any rate, the Agent job now works, and I am most grateful!

edit: BTW, when I set the 'Encrypt sensitive with password' option in the designer, it did not prompt for a package pw. There was a line in the property sheet a few lines up for entering a password.

Can''t run SSIS package via Agent

I've seen many postings on various forums on how to get SQL agent to execute an SSIS package. I have one that was originally created via the import wizard in SSMS, later modified in VS2005, and then re-imported in SSMS using the Object Explorer interface to load the dtsx file into SQL storage. I've tried several package protection options when importing the package: "Don't save sensitive data" (the package has no passwords in it), server storage and roles, and a specified package password (which I entered as a /DECRYPT command line parameter in the Agent job step). Agent is running using a domain admin account. No matter what I've tried, I still get an error during job execution that it failed to decrypt the password XML node. The package runs just fine when executed manually in the SSIS Object Explorer.

How can I resolve this?

Are you sure you have been talking about the same packge? You have clearly tried the options, including those which mean there is no encryption to decrypt, so it makes no sense. I have often got confused between versions or a similar mix up when frustated and trying to debug an issue like this.

|||

And by this, I believe that darren means versions of the same package. I have had similar problems in the past where I will continue to bang my head against the wall knowing that something should be working before remembering that I didn't copy the latest version of the package over to the release folder, or from the release folder to the sql package storage etc...

Anywho, I would try pushing it out with encrypt sensitive with password and if it asks you for a password when you are setting up the package in the job step you know that you have the correct version... If you use the sql package store on your ssis server, I would use the serverstorage method of encryption.

|||

Bear with me -- this is my first SSIS project; I've only used DTS in the past. I have only one version of the dtsx file. I've been setting the protection options when I import the file into SSIS via the object explorer. When you say "push it out with 'encrypt sensitive with password'", do you mean there is some way to set that option in the VS designer IDE? When I tried the pw option on import to SSIS, I did not get prompted for the pw when I created the Agent job step. Instead, I put a /DECRYPT switch in the job step's command line options. Please explain.

(And thanks very much for your help!)

|||yes there is a way to set this in the visual studios designer. Right click on the background of the control flow task of your package and select properties. About half way down the list there is a property for "protection level" where there is a drop down for the different types of encryption. If you choose encrypt sensitive w/ password it will prompt you for a password and a confirm password to encrypt the package with. After you have filled this out, any time that you go to the job step maintenance screen it will prompt you for the password (to verify your right to see the connection strings and whatnot).|||

That worked! I found the property setting in the designer and set it as you suggested. When I re-imported the dtsx file into SSIS on the production server, I also set the same protection option and entered the package pw set in the designer. I then deleted the old Agent job step and created a new one. It did not prompt for the package password, but it did execute the SSIS package this time. I saw no place in the various settings tabs for a package pw, nor did it prompt me for one, and I did not alter the command line defaults. Should it have prompted for a pw?

At any rate, the Agent job now works, and I am most grateful!

edit: BTW, when I set the 'Encrypt sensitive with password' option in the designer, it did not prompt for a package pw. There was a line in the property sheet a few lines up for entering a password.

Can''t run SSIS package via Agent

I've seen many postings on various forums on how to get SQL agent to execute an SSIS package. I have one that was originally created via the import wizard in SSMS, later modified in VS2005, and then re-imported in SSMS using the Object Explorer interface to load the dtsx file into SQL storage. I've tried several package protection options when importing the package: "Don't save sensitive data" (the package has no passwords in it), server storage and roles, and a specified package password (which I entered as a /DECRYPT command line parameter in the Agent job step). Agent is running using a domain admin account. No matter what I've tried, I still get an error during job execution that it failed to decrypt the password XML node. The package runs just fine when executed manually in the SSIS Object Explorer.

How can I resolve this?

Are you sure you have been talking about the same packge? You have clearly tried the options, including those which mean there is no encryption to decrypt, so it makes no sense. I have often got confused between versions or a similar mix up when frustated and trying to debug an issue like this.

|||

And by this, I believe that darren means versions of the same package. I have had similar problems in the past where I will continue to bang my head against the wall knowing that something should be working before remembering that I didn't copy the latest version of the package over to the release folder, or from the release folder to the sql package storage etc...

Anywho, I would try pushing it out with encrypt sensitive with password and if it asks you for a password when you are setting up the package in the job step you know that you have the correct version... If you use the sql package store on your ssis server, I would use the serverstorage method of encryption.

|||

Bear with me -- this is my first SSIS project; I've only used DTS in the past. I have only one version of the dtsx file. I've been setting the protection options when I import the file into SSIS via the object explorer. When you say "push it out with 'encrypt sensitive with password'", do you mean there is some way to set that option in the VS designer IDE? When I tried the pw option on import to SSIS, I did not get prompted for the pw when I created the Agent job step. Instead, I put a /DECRYPT switch in the job step's command line options. Please explain.

(And thanks very much for your help!)

|||yes there is a way to set this in the visual studios designer. Right click on the background of the control flow task of your package and select properties. About half way down the list there is a property for "protection level" where there is a drop down for the different types of encryption. If you choose encrypt sensitive w/ password it will prompt you for a password and a confirm password to encrypt the package with. After you have filled this out, any time that you go to the job step maintenance screen it will prompt you for the password (to verify your right to see the connection strings and whatnot).|||

That worked! I found the property setting in the designer and set it as you suggested. When I re-imported the dtsx file into SSIS on the production server, I also set the same protection option and entered the package pw set in the designer. I then deleted the old Agent job step and created a new one. It did not prompt for the package password, but it did execute the SSIS package this time. I saw no place in the various settings tabs for a package pw, nor did it prompt me for one, and I did not alter the command line defaults. Should it have prompted for a pw?

At any rate, the Agent job now works, and I am most grateful!

edit: BTW, when I set the 'Encrypt sensitive with password' option in the designer, it did not prompt for a package pw. There was a line in the property sheet a few lines up for entering a password.

Can''t run SSIS package via Agent

I've seen many postings on various forums on how to get SQL agent to execute an SSIS package. I have one that was originally created via the import wizard in SSMS, later modified in VS2005, and then re-imported in SSMS using the Object Explorer interface to load the dtsx file into SQL storage. I've tried several package protection options when importing the package: "Don't save sensitive data" (the package has no passwords in it), server storage and roles, and a specified package password (which I entered as a /DECRYPT command line parameter in the Agent job step). Agent is running using a domain admin account. No matter what I've tried, I still get an error during job execution that it failed to decrypt the password XML node. The package runs just fine when executed manually in the SSIS Object Explorer.

How can I resolve this?

Are you sure you have been talking about the same packge? You have clearly tried the options, including those which mean there is no encryption to decrypt, so it makes no sense. I have often got confused between versions or a similar mix up when frustated and trying to debug an issue like this.

|||

And by this, I believe that darren means versions of the same package. I have had similar problems in the past where I will continue to bang my head against the wall knowing that something should be working before remembering that I didn't copy the latest version of the package over to the release folder, or from the release folder to the sql package storage etc...

Anywho, I would try pushing it out with encrypt sensitive with password and if it asks you for a password when you are setting up the package in the job step you know that you have the correct version... If you use the sql package store on your ssis server, I would use the serverstorage method of encryption.

|||

Bear with me -- this is my first SSIS project; I've only used DTS in the past. I have only one version of the dtsx file. I've been setting the protection options when I import the file into SSIS via the object explorer. When you say "push it out with 'encrypt sensitive with password'", do you mean there is some way to set that option in the VS designer IDE? When I tried the pw option on import to SSIS, I did not get prompted for the pw when I created the Agent job step. Instead, I put a /DECRYPT switch in the job step's command line options. Please explain.

(And thanks very much for your help!)

|||yes there is a way to set this in the visual studios designer. Right click on the background of the control flow task of your package and select properties. About half way down the list there is a property for "protection level" where there is a drop down for the different types of encryption. If you choose encrypt sensitive w/ password it will prompt you for a password and a confirm password to encrypt the package with. After you have filled this out, any time that you go to the job step maintenance screen it will prompt you for the password (to verify your right to see the connection strings and whatnot).|||

That worked! I found the property setting in the designer and set it as you suggested. When I re-imported the dtsx file into SSIS on the production server, I also set the same protection option and entered the package pw set in the designer. I then deleted the old Agent job step and created a new one. It did not prompt for the package password, but it did execute the SSIS package this time. I saw no place in the various settings tabs for a package pw, nor did it prompt me for one, and I did not alter the command line defaults. Should it have prompted for a pw?

At any rate, the Agent job now works, and I am most grateful!

edit: BTW, when I set the 'Encrypt sensitive with password' option in the designer, it did not prompt for a package pw. There was a line in the property sheet a few lines up for entering a password.

Can''t run SSIS package via Agent

I've seen many postings on various forums on how to get SQL agent to execute an SSIS package. I have one that was originally created via the import wizard in SSMS, later modified in VS2005, and then re-imported in SSMS using the Object Explorer interface to load the dtsx file into SQL storage. I've tried several package protection options when importing the package: "Don't save sensitive data" (the package has no passwords in it), server storage and roles, and a specified package password (which I entered as a /DECRYPT command line parameter in the Agent job step). Agent is running using a domain admin account. No matter what I've tried, I still get an error during job execution that it failed to decrypt the password XML node. The package runs just fine when executed manually in the SSIS Object Explorer.

How can I resolve this?

Are you sure you have been talking about the same packge? You have clearly tried the options, including those which mean there is no encryption to decrypt, so it makes no sense. I have often got confused between versions or a similar mix up when frustated and trying to debug an issue like this.

|||

And by this, I believe that darren means versions of the same package. I have had similar problems in the past where I will continue to bang my head against the wall knowing that something should be working before remembering that I didn't copy the latest version of the package over to the release folder, or from the release folder to the sql package storage etc...

Anywho, I would try pushing it out with encrypt sensitive with password and if it asks you for a password when you are setting up the package in the job step you know that you have the correct version... If you use the sql package store on your ssis server, I would use the serverstorage method of encryption.

|||

Bear with me -- this is my first SSIS project; I've only used DTS in the past. I have only one version of the dtsx file. I've been setting the protection options when I import the file into SSIS via the object explorer. When you say "push it out with 'encrypt sensitive with password'", do you mean there is some way to set that option in the VS designer IDE? When I tried the pw option on import to SSIS, I did not get prompted for the pw when I created the Agent job step. Instead, I put a /DECRYPT switch in the job step's command line options. Please explain.

(And thanks very much for your help!)

|||yes there is a way to set this in the visual studios designer. Right click on the background of the control flow task of your package and select properties. About half way down the list there is a property for "protection level" where there is a drop down for the different types of encryption. If you choose encrypt sensitive w/ password it will prompt you for a password and a confirm password to encrypt the package with. After you have filled this out, any time that you go to the job step maintenance screen it will prompt you for the password (to verify your right to see the connection strings and whatnot).|||

That worked! I found the property setting in the designer and set it as you suggested. When I re-imported the dtsx file into SSIS on the production server, I also set the same protection option and entered the package pw set in the designer. I then deleted the old Agent job step and created a new one. It did not prompt for the package password, but it did execute the SSIS package this time. I saw no place in the various settings tabs for a package pw, nor did it prompt me for one, and I did not alter the command line defaults. Should it have prompted for a pw?

At any rate, the Agent job now works, and I am most grateful!

edit: BTW, when I set the 'Encrypt sensitive with password' option in the designer, it did not prompt for a package pw. There was a line in the property sheet a few lines up for entering a password.

Can''t run SSIS package via Agent

I've seen many postings on various forums on how to get SQL agent to execute an SSIS package. I have one that was originally created via the import wizard in SSMS, later modified in VS2005, and then re-imported in SSMS using the Object Explorer interface to load the dtsx file into SQL storage. I've tried several package protection options when importing the package: "Don't save sensitive data" (the package has no passwords in it), server storage and roles, and a specified package password (which I entered as a /DECRYPT command line parameter in the Agent job step). Agent is running using a domain admin account. No matter what I've tried, I still get an error during job execution that it failed to decrypt the password XML node. The package runs just fine when executed manually in the SSIS Object Explorer.

How can I resolve this?

Are you sure you have been talking about the same packge? You have clearly tried the options, including those which mean there is no encryption to decrypt, so it makes no sense. I have often got confused between versions or a similar mix up when frustated and trying to debug an issue like this.

|||

And by this, I believe that darren means versions of the same package. I have had similar problems in the past where I will continue to bang my head against the wall knowing that something should be working before remembering that I didn't copy the latest version of the package over to the release folder, or from the release folder to the sql package storage etc...

Anywho, I would try pushing it out with encrypt sensitive with password and if it asks you for a password when you are setting up the package in the job step you know that you have the correct version... If you use the sql package store on your ssis server, I would use the serverstorage method of encryption.

|||

Bear with me -- this is my first SSIS project; I've only used DTS in the past. I have only one version of the dtsx file. I've been setting the protection options when I import the file into SSIS via the object explorer. When you say "push it out with 'encrypt sensitive with password'", do you mean there is some way to set that option in the VS designer IDE? When I tried the pw option on import to SSIS, I did not get prompted for the pw when I created the Agent job step. Instead, I put a /DECRYPT switch in the job step's command line options. Please explain.

(And thanks very much for your help!)

|||yes there is a way to set this in the visual studios designer. Right click on the background of the control flow task of your package and select properties. About half way down the list there is a property for "protection level" where there is a drop down for the different types of encryption. If you choose encrypt sensitive w/ password it will prompt you for a password and a confirm password to encrypt the package with. After you have filled this out, any time that you go to the job step maintenance screen it will prompt you for the password (to verify your right to see the connection strings and whatnot).|||

That worked! I found the property setting in the designer and set it as you suggested. When I re-imported the dtsx file into SSIS on the production server, I also set the same protection option and entered the package pw set in the designer. I then deleted the old Agent job step and created a new one. It did not prompt for the package password, but it did execute the SSIS package this time. I saw no place in the various settings tabs for a package pw, nor did it prompt me for one, and I did not alter the command line defaults. Should it have prompted for a pw?

At any rate, the Agent job now works, and I am most grateful!

edit: BTW, when I set the 'Encrypt sensitive with password' option in the designer, it did not prompt for a package pw. There was a line in the property sheet a few lines up for entering a password.

can't retrieve data using app

Hi,
I have an application that can only sometimes retrieve data via the
application for a certain process. This option has been working correctly for
a few years and no changes have been made to it. I ran a DBCC INDEXDEFRAG
against the tables in question a few weeks back and the problem disappeared.
However, the problem has now reoccured.
Does anyone have any ideas regarding the nature of the problem and how it
can be resolved?
Extremely difficult to give suggestions without more information even hands-on. Perhaps the
application has a time-out? Did you get an error message or incorrect information? Perhaps there is
a time-out and no error is presented back to the user?
Another thing you can do is to capture the SQL statement submitted by the application with Profiler
and submit that same query using Query Analyzer. If the query work fine from Query Analyzer the
problem is in the application and you need to talk to the app vendor/developer.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:CBA13530-4144-4325-9E3A-4D0651BC0B4D@.microsoft.com...
> Hi,
> I have an application that can only sometimes retrieve data via the
> application for a certain process. This option has been working correctly for
> a few years and no changes have been made to it. I ran a DBCC INDEXDEFRAG
> against the tables in question a few weeks back and the problem disappeared.
> However, the problem has now reoccured.
> Does anyone have any ideas regarding the nature of the problem and how it
> can be resolved?
>
|||The error doesn't occur using sql analyzer.
I'm not sure that this would be a time out issue, as other reports
retrieving a lot more data are working correctly.
Please find the error below:
System.Data.SqlClient.SqlException: Timeout expired. The timeout period
elapsed prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior behavior)
at
System.Data.SqlClient.SqlCommand.System.Data.IDbCo mmand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(O bject data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable, IDbCommand
command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at expert.ExpertProc.PhysicalDataRead()
"Tibor Karaszi" wrote:

> Extremely difficult to give suggestions without more information even hands-on. Perhaps the
> application has a time-out? Did you get an error message or incorrect information? Perhaps there is
> a time-out and no error is presented back to the user?
> Another thing you can do is to capture the SQL statement submitted by the application with Profiler
> and submit that same query using Query Analyzer. If the query work fine from Query Analyzer the
> problem is in the application and you need to talk to the app vendor/developer.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:CBA13530-4144-4325-9E3A-4D0651BC0B4D@.microsoft.com...
>
|||The error message clearly states:
"The timeout period elapsed prior to completion of the operation or the server is not responding."
You have to investigate why it takes a long time sometimes and sometimes not. It can be blocking
issues (use Current Activity, sp_who, sp_who2, sp_lock etc to investigate), or it might be different
execution plans (use Profiler to catch the execution plans so you can compare).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:79597EDE-D40F-44F3-AD2A-FB4C4D2C4098@.microsoft.com...[vbcol=seagreen]
> The error doesn't occur using sql analyzer.
> I'm not sure that this would be a time out issue, as other reports
> retrieving a lot more data are working correctly.
>
> Please find the error below:
> System.Data.SqlClient.SqlException: Timeout expired. The timeout period
> elapsed prior to completion of the operation or the server is not responding.
> at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior behavior)
> at
> System.Data.SqlClient.SqlCommand.System.Data.IDbCo mmand.ExecuteReader(CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.FillFromCommand(O bject data, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable, IDbCommand
> command, CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
> at expert.ExpertProc.PhysicalDataRead()
> "Tibor Karaszi" wrote:
|||The error message clearly indicates a timeout exception. The default
SqlCommand timeout is 30 seconds but this can be overridden by the
application. The default Query Analyzer query timeout is unlimited so,
assuming the query runs over 30 seconds, that explains why you don't get the
error in Query Analyzer.
A number of factors can contribute to a timeout. These include sub-optimal
query plan, blocking and server workload. If you've ruled out blocking and
an overtaxed server, I suggest you examine the plan of the problem query.
Perhaps additional indexes may help or existing indexes need to be rebuilt
using DBCC DBREINDEX. If you still have the problem after tuning, you'll
need to increase the application command timeout.
Hope this helps.
Dan Guzman
SQL Server MVP
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:79597EDE-D40F-44F3-AD2A-FB4C4D2C4098@.microsoft.com...[vbcol=seagreen]
> The error doesn't occur using sql analyzer.
> I'm not sure that this would be a time out issue, as other reports
> retrieving a lot more data are working correctly.
>
> Please find the error below:
> System.Data.SqlClient.SqlException: Timeout expired. The timeout period
> elapsed prior to completion of the operation or the server is not
> responding.
> at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior
> behavior)
> at
> System.Data.SqlClient.SqlCommand.System.Data.IDbCo mmand.ExecuteReader(CommandBehavior
> behavior)
> at System.Data.Common.DbDataAdapter.FillFromCommand(O bject data, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable, IDbCommand
> command, CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
> at expert.ExpertProc.PhysicalDataRead()
> "Tibor Karaszi" wrote:

can't retrieve data using app

Hi,
I have an application that can only sometimes retrieve data via the
application for a certain process. This option has been working correctly fo
r
a few years and no changes have been made to it. I ran a DBCC INDEXDEFRAG
against the tables in question a few weeks back and the problem disappeared.
However, the problem has now reoccured.
Does anyone have any ideas regarding the nature of the problem and how it
can be resolved?Extremely difficult to give suggestions without more information even hands-
on. Perhaps the
application has a time-out? Did you get an error message or incorrect inform
ation? Perhaps there is
a time-out and no error is presented back to the user?
Another thing you can do is to capture the SQL statement submitted by the ap
plication with Profiler
and submit that same query using Query Analyzer. If the query work fine from
Query Analyzer the
problem is in the application and you need to talk to the app vendor/develop
er.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:CBA13530-4144-4325-9E3A-4D0651BC0B4D@.microsoft.com...
> Hi,
> I have an application that can only sometimes retrieve data via the
> application for a certain process. This option has been working correctly
for
> a few years and no changes have been made to it. I ran a DBCC INDEXDEFRAG
> against the tables in question a few weeks back and the problem disappeare
d.
> However, the problem has now reoccured.
> Does anyone have any ideas regarding the nature of the problem and how it
> can be resolved?
>|||The error doesn't occur using sql analyzer.
I'm not sure that this would be a time out issue, as other reports
retrieving a lot more data are working correctly.
Please find the error below:
System.Data.SqlClient.SqlException: Timeout expired. The timeout period
elapsed prior to completion of the operation or the server is not responding
.
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at
System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(Comman
dBehavior behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable, IDbCommand
command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at expert.ExpertProc.PhysicalDataRead()
"Tibor Karaszi" wrote:

> Extremely difficult to give suggestions without more information even hand
s-on. Perhaps the
> application has a time-out? Did you get an error message or incorrect info
rmation? Perhaps there is
> a time-out and no error is presented back to the user?
> Another thing you can do is to capture the SQL statement submitted by the
application with Profiler
> and submit that same query using Query Analyzer. If the query work fine fr
om Query Analyzer the
> problem is in the application and you need to talk to the app vendor/devel
oper.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:CBA13530-4144-4325-9E3A-4D0651BC0B4D@.microsoft.com...
>|||The error message clearly states:
"The timeout period elapsed prior to completion of the operation or the ser
ver is not responding."
You have to investigate why it takes a long time sometimes and sometimes not
. It can be blocking
issues (use Current Activity, sp_who, sp_who2, sp_lock etc to investigate),
or it might be different
execution plans (use Profiler to catch the execution plans so you can compar
e).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:79597EDE-D40F-44F3-AD2A-FB4C4D2C4098@.microsoft.com...[vbcol=seagreen]
> The error doesn't occur using sql analyzer.
> I'm not sure that this would be a time out issue, as other reports
> retrieving a lot more data are working correctly.
>
> Please find the error below:
> System.Data.SqlClient.SqlException: Timeout expired. The timeout period
> elapsed prior to completion of the operation or the server is not respondi
ng.
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavi
or)
> at
> System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(Comm
andBehavior behavior)
> at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable, IDbCommand
> command, CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
> at expert.ExpertProc.PhysicalDataRead()
> "Tibor Karaszi" wrote:
>|||The error message clearly indicates a timeout exception. The default
SqlCommand timeout is 30 seconds but this can be overridden by the
application. The default Query Analyzer query timeout is unlimited so,
assuming the query runs over 30 seconds, that explains why you don't get the
error in Query Analyzer.
A number of factors can contribute to a timeout. These include sub-optimal
query plan, blocking and server workload. If you've ruled out blocking and
an overtaxed server, I suggest you examine the plan of the problem query.
Perhaps additional indexes may help or existing indexes need to be rebuilt
using DBCC DBREINDEX. If you still have the problem after tuning, you'll
need to increase the application command timeout.
Hope this helps.
Dan Guzman
SQL Server MVP
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:79597EDE-D40F-44F3-AD2A-FB4C4D2C4098@.microsoft.com...[vbcol=seagreen]
> The error doesn't occur using sql analyzer.
> I'm not sure that this would be a time out issue, as other reports
> retrieving a lot more data are working correctly.
>
> Please find the error below:
> System.Data.SqlClient.SqlException: Timeout expired. The timeout period
> elapsed prior to completion of the operation or the server is not
> responding.
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> behavior)
> at
> System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(Comm
andBehavior
> behavior)
> at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable, IDbCommand
> command, CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
> at expert.ExpertProc.PhysicalDataRead()
> "Tibor Karaszi" wrote:
>

can't retrieve data using app

Hi,
I have an application that can only sometimes retrieve data via the
application for a certain process. This option has been working correctly for
a few years and no changes have been made to it. I ran a DBCC INDEXDEFRAG
against the tables in question a few weeks back and the problem disappeared.
However, the problem has now reoccured.
Does anyone have any ideas regarding the nature of the problem and how it
can be resolved?Extremely difficult to give suggestions without more information even hands-on. Perhaps the
application has a time-out? Did you get an error message or incorrect information? Perhaps there is
a time-out and no error is presented back to the user?
Another thing you can do is to capture the SQL statement submitted by the application with Profiler
and submit that same query using Query Analyzer. If the query work fine from Query Analyzer the
problem is in the application and you need to talk to the app vendor/developer.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:CBA13530-4144-4325-9E3A-4D0651BC0B4D@.microsoft.com...
> Hi,
> I have an application that can only sometimes retrieve data via the
> application for a certain process. This option has been working correctly for
> a few years and no changes have been made to it. I ran a DBCC INDEXDEFRAG
> against the tables in question a few weeks back and the problem disappeared.
> However, the problem has now reoccured.
> Does anyone have any ideas regarding the nature of the problem and how it
> can be resolved?
>|||The error doesn't occur using sql analyzer.
I'm not sure that this would be a time out issue, as other reports
retrieving a lot more data are working correctly.
Please find the error below:
System.Data.SqlClient.SqlException: Timeout expired. The timeout period
elapsed prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at
System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable, IDbCommand
command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at expert.ExpertProc.PhysicalDataRead()
"Tibor Karaszi" wrote:
> Extremely difficult to give suggestions without more information even hands-on. Perhaps the
> application has a time-out? Did you get an error message or incorrect information? Perhaps there is
> a time-out and no error is presented back to the user?
> Another thing you can do is to capture the SQL statement submitted by the application with Profiler
> and submit that same query using Query Analyzer. If the query work fine from Query Analyzer the
> problem is in the application and you need to talk to the app vendor/developer.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:CBA13530-4144-4325-9E3A-4D0651BC0B4D@.microsoft.com...
> > Hi,
> >
> > I have an application that can only sometimes retrieve data via the
> > application for a certain process. This option has been working correctly for
> > a few years and no changes have been made to it. I ran a DBCC INDEXDEFRAG
> > against the tables in question a few weeks back and the problem disappeared.
> > However, the problem has now reoccured.
> >
> > Does anyone have any ideas regarding the nature of the problem and how it
> > can be resolved?
> >
>|||The error message clearly states:
"The timeout period elapsed prior to completion of the operation or the server is not responding."
You have to investigate why it takes a long time sometimes and sometimes not. It can be blocking
issues (use Current Activity, sp_who, sp_who2, sp_lock etc to investigate), or it might be different
execution plans (use Profiler to catch the execution plans so you can compare).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:79597EDE-D40F-44F3-AD2A-FB4C4D2C4098@.microsoft.com...
> The error doesn't occur using sql analyzer.
> I'm not sure that this would be a time out issue, as other reports
> retrieving a lot more data are working correctly.
>
> Please find the error below:
> System.Data.SqlClient.SqlException: Timeout expired. The timeout period
> elapsed prior to completion of the operation or the server is not responding.
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
> at
> System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable, IDbCommand
> command, CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
> at expert.ExpertProc.PhysicalDataRead()
> "Tibor Karaszi" wrote:
>> Extremely difficult to give suggestions without more information even hands-on. Perhaps the
>> application has a time-out? Did you get an error message or incorrect information? Perhaps there
>> is
>> a time-out and no error is presented back to the user?
>> Another thing you can do is to capture the SQL statement submitted by the application with
>> Profiler
>> and submit that same query using Query Analyzer. If the query work fine from Query Analyzer the
>> problem is in the application and you need to talk to the app vendor/developer.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "gracie" <gracie@.discussions.microsoft.com> wrote in message
>> news:CBA13530-4144-4325-9E3A-4D0651BC0B4D@.microsoft.com...
>> > Hi,
>> >
>> > I have an application that can only sometimes retrieve data via the
>> > application for a certain process. This option has been working correctly for
>> > a few years and no changes have been made to it. I ran a DBCC INDEXDEFRAG
>> > against the tables in question a few weeks back and the problem disappeared.
>> > However, the problem has now reoccured.
>> >
>> > Does anyone have any ideas regarding the nature of the problem and how it
>> > can be resolved?
>> >
>>|||The error message clearly indicates a timeout exception. The default
SqlCommand timeout is 30 seconds but this can be overridden by the
application. The default Query Analyzer query timeout is unlimited so,
assuming the query runs over 30 seconds, that explains why you don't get the
error in Query Analyzer.
A number of factors can contribute to a timeout. These include sub-optimal
query plan, blocking and server workload. If you've ruled out blocking and
an overtaxed server, I suggest you examine the plan of the problem query.
Perhaps additional indexes may help or existing indexes need to be rebuilt
using DBCC DBREINDEX. If you still have the problem after tuning, you'll
need to increase the application command timeout.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:79597EDE-D40F-44F3-AD2A-FB4C4D2C4098@.microsoft.com...
> The error doesn't occur using sql analyzer.
> I'm not sure that this would be a time out issue, as other reports
> retrieving a lot more data are working correctly.
>
> Please find the error below:
> System.Data.SqlClient.SqlException: Timeout expired. The timeout period
> elapsed prior to completion of the operation or the server is not
> responding.
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> behavior)
> at
> System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior
> behavior)
> at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable, IDbCommand
> command, CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
> at expert.ExpertProc.PhysicalDataRead()
> "Tibor Karaszi" wrote:
>> Extremely difficult to give suggestions without more information even
>> hands-on. Perhaps the
>> application has a time-out? Did you get an error message or incorrect
>> information? Perhaps there is
>> a time-out and no error is presented back to the user?
>> Another thing you can do is to capture the SQL statement submitted by the
>> application with Profiler
>> and submit that same query using Query Analyzer. If the query work fine
>> from Query Analyzer the
>> problem is in the application and you need to talk to the app
>> vendor/developer.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "gracie" <gracie@.discussions.microsoft.com> wrote in message
>> news:CBA13530-4144-4325-9E3A-4D0651BC0B4D@.microsoft.com...
>> > Hi,
>> >
>> > I have an application that can only sometimes retrieve data via the
>> > application for a certain process. This option has been working
>> > correctly for
>> > a few years and no changes have been made to it. I ran a DBCC
>> > INDEXDEFRAG
>> > against the tables in question a few weeks back and the problem
>> > disappeared.
>> > However, the problem has now reoccured.
>> >
>> > Does anyone have any ideas regarding the nature of the problem and how
>> > it
>> > can be resolved?
>> >
>>

can't rebuild msdb

Hello,
After running dbcc checkdb i'm getting:
"..54 consistency errors in database 'msdb'"
i want to rebuild the msdb database via
instmsdb.sql but i can't delete the database...
Which is the right way to do it avoiding rebuild the
master database?
Many Thank in Advance,
Galit.Galit
Do you have a last backup of msdb database?
If you do so try to restote it.
"Galit" <galitm@.ladpc.co.il> wrote in message
news:059101c3b7e1$3cd6f1b0$a301280a@.phx.gbl...
> Hello,
> After running dbcc checkdb i'm getting:
> "..54 consistency errors in database 'msdb'"
> i want to rebuild the msdb database via
> instmsdb.sql but i can't delete the database...
> Which is the right way to do it avoiding rebuild the
> master database?
> Many Thank in Advance,
> Galit.
>|||There's a section in below KB article on how you can detach the msdb database:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;224071
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Galit" <galitm@.ladpc.co.il> wrote in message news:059101c3b7e1$3cd6f1b0$a301280a@.phx.gbl...
> Hello,
> After running dbcc checkdb i'm getting:
> "..54 consistency errors in database 'msdb'"
> i want to rebuild the msdb database via
> instmsdb.sql but i can't delete the database...
> Which is the right way to do it avoiding rebuild the
> master database?
> Many Thank in Advance,
> Galit.
>|||Tibor Karaszi wrote:
> There's a section in below KB article on how you can detach the msdb
> database:
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;224071
following tibors link, after you have detached msdb, if you want to use
a blank msdb, look in the msde sp3 sql2ksp3\MSDE\Setup directory. You
will see a cabinet file sqlrun.cab. From this extract the two files
msdbdata.mdfXXXXXX and msdblog.ldfXXXXXX (both of these files are named
with a string of characters -which I think is a checksum, you can
simply rename them both to their correct respective names msdbdata.mdf
and msdblog.ldfXXXXXX)
Dont overwrite your existing msdb (just in case) rename them to *.old
for them time being till you are happy.
--
Br,
Mark Broadbent
mcdba , mcse+i
=============|||Mark,
Wouldn't running instmsdb.sql also be an option at this stage? (Instead of attaching, as there will
be a problem if the template file doesn't have the same collation as the other system databases...)
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mark Broadbent" <no-spam-please_mark.broadbent@.virgin.net> wrote in message
news:ecPhp5BuDHA.1680@.TK2MSFTNGP12.phx.gbl...
> Tibor Karaszi wrote:
> > There's a section in below KB article on how you can detach the msdb
> > database:
> >
> > http://support.microsoft.com/default.aspx?scid=kb;EN-US;224071
> following tibors link, after you have detached msdb, if you want to use
> a blank msdb, look in the msde sp3 sql2ksp3\MSDE\Setup directory. You
> will see a cabinet file sqlrun.cab. From this extract the two files
> msdbdata.mdfXXXXXX and msdblog.ldfXXXXXX (both of these files are named
> with a string of characters -which I think is a checksum, you can
> simply rename them both to their correct respective names msdbdata.mdf
> and msdblog.ldfXXXXXX)
> Dont overwrite your existing msdb (just in case) rename them to *.old
> for them time being till you are happy.
> --
> Br,
> Mark Broadbent
> mcdba , mcse+i
> =============|||Tibor Karaszi wrote:
> Mark,
> Wouldn't running instmsdb.sql also be an option at this stage?
> (Instead of attaching, as there will be a problem if the template
> file doesn't have the same collation as the other system databases...)
Yes very good point. Been staring at my monitor for too long #:-)
--
Br,
Mark Broadbent
mcdba , mcse+i
=============|||That's what i did.
Many many thanks,
Galit.
>--Original Message--
>Tibor Karaszi wrote:
>> Mark,
>> Wouldn't running instmsdb.sql also be an option at this
stage?
>> (Instead of attaching, as there will be a problem if
the template
>> file doesn't have the same collation as the other
system databases...)
>Yes very good point. Been staring at my monitor for too
long #:-)
>--
>Br,
>Mark Broadbent
>mcdba , mcse+i
>=============>.
>

cant open crystal report on client

guys, i've probem with opening cr report via vb on client. The error msg is " server has not yet been opened ", i try to opened manually from crystall report and its working ( on client too ) but when i try to open on my vb an error comes again.

Unfortunelly when i run this application on server, everything is okay.

how ?

erickmaybe u should post ur code to figure it out..

thanks,
erick (same name, same location with u :0)

Can't login to Enterprise Manager or Query Analyzer

Hello,
We are running SQL Server 7.0 databases on 3 separate
systems. We have found that recently we are unable to log
into these databases via Enterprise Manager (Enterprise
Manager will run by when you attempt to connect to the
database it will lock up). We were able to work around
that by just using Query Analyzer, but now that has
stopped working. The same problem: after being prompted
for user name/password (we are using SQL Server Security)
it hangs while displaying the "Connecting to..." message.
Has anyone encountered this before? We initially copied
the Query Analyzer executable (isqlw.exe) from the CD to
the server and that fixed it for a short time. Any ideas
on how to fix this permenantly and what is causing the
problem?
Thanks.Did you remove the default database for the logins? If so, you can change
it using sp_defaultdb via QA.
"Todd Biggar" <tbiggar@.nelcomail.com> wrote in message
news:03cb01c38dd0$3f9e7890$a301280a@.phx.gbl...
> Hello,
> We are running SQL Server 7.0 databases on 3 separate
> systems. We have found that recently we are unable to log
> into these databases via Enterprise Manager (Enterprise
> Manager will run by when you attempt to connect to the
> database it will lock up). We were able to work around
> that by just using Query Analyzer, but now that has
> stopped working. The same problem: after being prompted
> for user name/password (we are using SQL Server Security)
> it hangs while displaying the "Connecting to..." message.
> Has anyone encountered this before? We initially copied
> the Query Analyzer executable (isqlw.exe) from the CD to
> the server and that fixed it for a short time. Any ideas
> on how to fix this permenantly and what is causing the
> problem?
> Thanks.
>|||Unfortunately we can't log in with QA either; it hangs on
us too. The actual applications we wrote to hit the
database seem to be connecting without any problems, but
neither Enterprise Manager nor Query Analyzer will respond
when we try to connect.
>--Original Message--
>Did you remove the default database for the logins? If
so, you can change
>it using sp_defaultdb via QA.
>"Todd Biggar" <tbiggar@.nelcomail.com> wrote in message
>news:03cb01c38dd0$3f9e7890$a301280a@.phx.gbl...
>> Hello,
>> We are running SQL Server 7.0 databases on 3 separate
>> systems. We have found that recently we are unable to
log
>> into these databases via Enterprise Manager (Enterprise
>> Manager will run by when you attempt to connect to the
>> database it will lock up). We were able to work around
>> that by just using Query Analyzer, but now that has
>> stopped working. The same problem: after being prompted
>> for user name/password (we are using SQL Server
Security)
>> it hangs while displaying the "Connecting to..."
message.
>> Has anyone encountered this before? We initially copied
>> the Query Analyzer executable (isqlw.exe) from the CD to
>> the server and that fixed it for a short time. Any
ideas
>> on how to fix this permenantly and what is causing the
>> problem?
>> Thanks.
>
>.
>|||I think what Scott was getting at was to try to log on using
osql from the command line and change the default database
if that's what the issue is. You can at least try to log in
using osql from the command line. If you need to change the
default database, you can use something along the lines of:
OSQL -S YourServer -E -Q "EXEC sp_defaultdb 'YourLogin',
'master'"
-Sue
On Wed, 8 Oct 2003 13:30:30 -0700, "Todd Biggar"
<tbiggar@.nelcomail.com> wrote:
>Unfortunately we can't log in with QA either; it hangs on
>us too. The actual applications we wrote to hit the
>database seem to be connecting without any problems, but
>neither Enterprise Manager nor Query Analyzer will respond
>when we try to connect.
>>--Original Message--
>>Did you remove the default database for the logins? If
>so, you can change
>>it using sp_defaultdb via QA.
>>"Todd Biggar" <tbiggar@.nelcomail.com> wrote in message
>>news:03cb01c38dd0$3f9e7890$a301280a@.phx.gbl...
>> Hello,
>> We are running SQL Server 7.0 databases on 3 separate
>> systems. We have found that recently we are unable to
>log
>> into these databases via Enterprise Manager (Enterprise
>> Manager will run by when you attempt to connect to the
>> database it will lock up). We were able to work around
>> that by just using Query Analyzer, but now that has
>> stopped working. The same problem: after being prompted
>> for user name/password (we are using SQL Server
>Security)
>> it hangs while displaying the "Connecting to..."
>message.
>> Has anyone encountered this before? We initially copied
>> the Query Analyzer executable (isqlw.exe) from the CD to
>> the server and that fixed it for a short time. Any
>ideas
>> on how to fix this permenantly and what is causing the
>> problem?
>> Thanks.
>>
>>.|||Unfortunately this doesn't work either; I've tried various combinations of
usernames, database names and login names but even running osql is
non-responsive. It will just sit there at the prompt until I break out.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:8r09ovouoej90ecvvgtn3taunct8t8rohi@.4ax.com...
> I think what Scott was getting at was to try to log on using
> osql from the command line and change the default database
> if that's what the issue is. You can at least try to log in
> using osql from the command line. If you need to change the
> default database, you can use something along the lines of:
> OSQL -S YourServer -E -Q "EXEC sp_defaultdb 'YourLogin',
> 'master'"
> -Sue
> On Wed, 8 Oct 2003 13:30:30 -0700, "Todd Biggar"
> <tbiggar@.nelcomail.com> wrote:
> >Unfortunately we can't log in with QA either; it hangs on
> >us too. The actual applications we wrote to hit the
> >database seem to be connecting without any problems, but
> >neither Enterprise Manager nor Query Analyzer will respond
> >when we try to connect.
> >
> >>--Original Message--
> >>Did you remove the default database for the logins? If
> >so, you can change
> >>it using sp_defaultdb via QA.
> >>
> >>"Todd Biggar" <tbiggar@.nelcomail.com> wrote in message
> >>news:03cb01c38dd0$3f9e7890$a301280a@.phx.gbl...
> >> Hello,
> >>
> >> We are running SQL Server 7.0 databases on 3 separate
> >> systems. We have found that recently we are unable to
> >log
> >> into these databases via Enterprise Manager (Enterprise
> >> Manager will run by when you attempt to connect to the
> >> database it will lock up). We were able to work around
> >> that by just using Query Analyzer, but now that has
> >> stopped working. The same problem: after being prompted
> >> for user name/password (we are using SQL Server
> >Security)
> >> it hangs while displaying the "Connecting to..."
> >message.
> >>
> >> Has anyone encountered this before? We initially copied
> >> the Query Analyzer executable (isqlw.exe) from the CD to
> >> the server and that fixed it for a short time. Any
> >ideas
> >> on how to fix this permenantly and what is causing the
> >> problem?
> >>
> >> Thanks.
> >>
> >>
> >>
> >>.
> >>
>|||You'll probably want to go through your SQL Server error
logs and try to find out what the problem is. Also, check
the status of the service in the services applet - I've seen
something similar when the service was hung up in a
"Starting" status (it should say "Started" not starting).
-Sue
On Thu, 9 Oct 2003 10:44:21 -0500, "Todd Biggar"
<tbiggar@.new.rr.com> wrote:
>Unfortunately this doesn't work either; I've tried various combinations of
>usernames, database names and login names but even running osql is
>non-responsive. It will just sit there at the prompt until I break out.
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:8r09ovouoej90ecvvgtn3taunct8t8rohi@.4ax.com...
>> I think what Scott was getting at was to try to log on using
>> osql from the command line and change the default database
>> if that's what the issue is. You can at least try to log in
>> using osql from the command line. If you need to change the
>> default database, you can use something along the lines of:
>> OSQL -S YourServer -E -Q "EXEC sp_defaultdb 'YourLogin',
>> 'master'"
>> -Sue
>> On Wed, 8 Oct 2003 13:30:30 -0700, "Todd Biggar"
>> <tbiggar@.nelcomail.com> wrote:
>> >Unfortunately we can't log in with QA either; it hangs on
>> >us too. The actual applications we wrote to hit the
>> >database seem to be connecting without any problems, but
>> >neither Enterprise Manager nor Query Analyzer will respond
>> >when we try to connect.
>> >
>> >>--Original Message--
>> >>Did you remove the default database for the logins? If
>> >so, you can change
>> >>it using sp_defaultdb via QA.
>> >>
>> >>"Todd Biggar" <tbiggar@.nelcomail.com> wrote in message
>> >>news:03cb01c38dd0$3f9e7890$a301280a@.phx.gbl...
>> >> Hello,
>> >>
>> >> We are running SQL Server 7.0 databases on 3 separate
>> >> systems. We have found that recently we are unable to
>> >log
>> >> into these databases via Enterprise Manager (Enterprise
>> >> Manager will run by when you attempt to connect to the
>> >> database it will lock up). We were able to work around
>> >> that by just using Query Analyzer, but now that has
>> >> stopped working. The same problem: after being prompted
>> >> for user name/password (we are using SQL Server
>> >Security)
>> >> it hangs while displaying the "Connecting to..."
>> >message.
>> >>
>> >> Has anyone encountered this before? We initially copied
>> >> the Query Analyzer executable (isqlw.exe) from the CD to
>> >> the server and that fixed it for a short time. Any
>> >ideas
>> >> on how to fix this permenantly and what is causing the
>> >> problem?
>> >>
>> >> Thanks.
>> >>
>> >>
>> >>
>> >>.
>> >>
>|||Turns out the default network libary got changed to Named Pipes somehow. I
removed all the entries in the Client Utility and readded them and now it
works.
Thanks for all the help!
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:8r09ovouoej90ecvvgtn3taunct8t8rohi@.4ax.com...
> I think what Scott was getting at was to try to log on using
> osql from the command line and change the default database
> if that's what the issue is. You can at least try to log in
> using osql from the command line. If you need to change the
> default database, you can use something along the lines of:
> OSQL -S YourServer -E -Q "EXEC sp_defaultdb 'YourLogin',
> 'master'"
> -Sue
> On Wed, 8 Oct 2003 13:30:30 -0700, "Todd Biggar"
> <tbiggar@.nelcomail.com> wrote:
> >Unfortunately we can't log in with QA either; it hangs on
> >us too. The actual applications we wrote to hit the
> >database seem to be connecting without any problems, but
> >neither Enterprise Manager nor Query Analyzer will respond
> >when we try to connect.
> >
> >>--Original Message--
> >>Did you remove the default database for the logins? If
> >so, you can change
> >>it using sp_defaultdb via QA.
> >>
> >>"Todd Biggar" <tbiggar@.nelcomail.com> wrote in message
> >>news:03cb01c38dd0$3f9e7890$a301280a@.phx.gbl...
> >> Hello,
> >>
> >> We are running SQL Server 7.0 databases on 3 separate
> >> systems. We have found that recently we are unable to
> >log
> >> into these databases via Enterprise Manager (Enterprise
> >> Manager will run by when you attempt to connect to the
> >> database it will lock up). We were able to work around
> >> that by just using Query Analyzer, but now that has
> >> stopped working. The same problem: after being prompted
> >> for user name/password (we are using SQL Server
> >Security)
> >> it hangs while displaying the "Connecting to..."
> >message.
> >>
> >> Has anyone encountered this before? We initially copied
> >> the Query Analyzer executable (isqlw.exe) from the CD to
> >> the server and that fixed it for a short time. Any
> >ideas
> >> on how to fix this permenantly and what is causing the
> >> problem?
> >>
> >> Thanks.
> >>
> >>
> >>
> >>.
> >>
>

Can't log in to reporting services in my browser

I've installed the Developer's edition of SQL05 on my PC at home. I am able to open the reporting services via the Manager Studio and Windows login under my username and password, however I cannot open it in my browser when I enter the

http://computername/reports address. I get a login dialog and enter "computername/myloginID" for the user and my local login password. It will not accept the login and reprompt until I exceed 3 tries.

I also tried logging in with the PC's admin id and password as well as the sa account name and password.

Any ideas on what I missed in the setup or security?

Thanks for any assistance anyone can provide

mike...

Hi,

Did you installed a named SQL Server named instance? If so, you need to add the name of your instance to the url.

Are you using a proxy server?

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Thank you for responding. The answer to both of your questions is no, I used the defaults through the entire install process. The service is running locally on my pc.

I've tried to log in via http://localhost/reports as well as http://<pcname>/reports. In both instances a dialog box with "Connect to <pcname>" with two fields one for my user name and one for a password. My normal password and ID does is not accepted.

mike...

|||

This is usually an indication that the service is having some sort of fatal error. Can you look in the log files and see if there are any errors in there?

-Daniel

|||

Hi Daniel, thanks for your suggestion. I found logs in C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\LogFiles, I assume this is the logs folder you mean.

I looked through some of the 50 or so log files there and saw nothing that looked like an error log, the all seem to be process tracking logs. Can you be more specific on which log I should look at?

Thanks

|||

Hello Mike,

I believe this is due to the security settings in IIS. One thing to try is to enable anonymous access from the 'Reports' and 'ReportServer' directories in your IIS websites and see if it works. I had this same problem before I got the security settings ironed out in IIS.

In my setup, I have disabled anonymous access on the 'Default Web Site', 'Reports', and 'ReportServer' items. I have also enabled 'Integrated Windows authentication' on all 3. The DefaultAppPool runs as the Network Service.

From Reporting Services Configuration Manager, Report Server and Report Manager, both run in the DefaultAppPool application pool.

Hope this helps.

Jarret

|||

Hi Jarret,

Thank you for your tip, it did the trick! I enabled anonymous access access on the two folders and got into the Report Manager.

I guess this leads to another question, anonymous access is fine for me on my local machine here at home, but not exactly what I want on the production system at work. We're using Windows authentication at work, (with none of the problems I've had here at home that I am aware of), but I'm thinking I need to educate myself more on how the security settings between SQL05, Reporting Services, and IIS interact.

You spoke of "ironing out IIS security", can you recommend any resources from Microsoft which will help me learn how to manage security for SQL, etc.?

To all, thanks for your replies to my questions here!

|||

That is correct, I wouldn't set up my servers with anonymous access enabled either. I actually had one of my developers help me initially, but eventually I figured out how to have it work without anonymous access.

Sorry I don't have any links to any articles, I just haven't searched. Researching how the security between SQL2005, RS, and IIS work with each other is on my list of things to do as well.

Jarret

Can't Insert Binary Data Into Sql Mobile

Hi guys, I'm having a problem inserting a file into a SQL Mobile table.

I'm updating the table via a tableadapter designed through the Dataset designer in VS2k5.

The SQL used to insert is "Insert into TestTable (itemid,itemdata) values (@.itemid,@.itemdata)"

Where ItemID is a GUID, and ItemData is an Image field. The tableadapter has typed the parameters as GUID and Binary properly already.

Only problem is when I actually call the command with a byte array containing my file, I get a "Byte truncation" error message.

I remember this working properly in SQL CE 2.0 but so far I don't seem to get it to work in SQL mobile, anyone got any pointers?

How big is the array? If you try to insert array of, say, one byte in size, does that work?

|||

Here is a sample of what you need to do to insert binary data into an image column in SQL Mobile:

MemoryStream ms = new MemoryStream();
pictureBox1.Image.Save(ms, ImageFormat.Jpeg);
byte[] imageBytes = ms.ToArray();

SqlCeConnection cn = DatabaseManager.GetInstance().DBConnection;
string sql = "INSERT INTO Photos (ID, Photo) VALUES (NewID(), ?)";
SqlCeCommand command = new SqlCeCommand(sql, cn);

command.Parameters.Add("@.image", SqlDbType.Image, imageBytes.Length);

command.Parameters["@.image"].Value = imageBytes;

command.ExecuteNonQuery();

-Darren