Ability to schedule job across Domains

Situation: I have to copy data from a host SQL Server and place it onto my
SQL Server. We have an intermediary box with Console installed with both SQL
Servers registered. I have setup a DTS that performs the transfer.
Problem: I can run the DTS manually but when I schedule the job to run
automatically I get an error message that the job cannot be run because the
user is a nonSysAdmin.
That is because the context of the package is different when you run it as a
job, instead of you running it. See if this helps:
http://support.microsoft.com/default...269074&sd=tech
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"bssolutions" <bssolutions@.discussions.microsoft.com> wrote in message
news:D1FAB676-20A5-4DE1-A20E-90C5114A10BB@.microsoft.com...
Situation: I have to copy data from a host SQL Server and place it onto my
SQL Server. We have an intermediary box with Console installed with both
SQL
Servers registered. I have setup a DTS that performs the transfer.
Problem: I can run the DTS manually but when I schedule the job to run
automatically I get an error message that the job cannot be run because the
user is a nonSysAdmin.
|||Since the 1st box is accessed thru VPN, we have a 2nd box that acts as the
connecting device with console and is outside our domain, and the 3rd box is
inside our domain with SQL installed. I made the login, for test purposes a
member of sysadmin. This allowed me to schedule the job and it would run as
scheduled but now I get a message that it cannot find the server referenced
(a simply sql statement is my test to the 1st box).
Here is what the job history tells me:
Executed as user: TEST1\SYSTEM. DTSRun: Loading... DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnError:
DTSStep_DTSExecuteSQLTask_1, Error = -2147467259 (80004005) Error
string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
access denied. Error source: Microsoft OLE DB Provider for SQL Server
Help file: Help context: 0 Error Detail Records: Error:
-2147467259 (80004005); Provider Error: 17 (11) Error string:
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access
denied. Error source: Microsoft OLE DB Provider for SQL Server
Help file: Help context: 0 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_1 DTSRun: Package execution complete. Process
Exit Code 1. The step failed.
Thanks a bunch for you help on this.
"Narayana Vyas Kondreddi" wrote:

> That is because the context of the package is different when you run it as a
> job, instead of you running it. See if this helps:
> http://support.microsoft.com/default...269074&sd=tech
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "bssolutions" <bssolutions@.discussions.microsoft.com> wrote in message
> news:D1FAB676-20A5-4DE1-A20E-90C5114A10BB@.microsoft.com...
> Situation: I have to copy data from a host SQL Server and place it onto my
> SQL Server. We have an intermediary box with Console installed with both
> SQL
> Servers registered. I have setup a DTS that performs the transfer.
> Problem: I can run the DTS manually but when I schedule the job to run
> automatically I get an error message that the job cannot be run because the
> user is a nonSysAdmin.
>
>

Ability to schedule job across Domains

Situation: I have to copy data from a host SQL Server and place it onto my
SQL Server. We have an intermediary box with Console installed with both SQ
L
Servers registered. I have setup a DTS that performs the transfer.
Problem: I can run the DTS manually but when I schedule the job to run
automatically I get an error message that the job cannot be run because the
user is a nonSysAdmin.That is because the context of the package is different when you run it as a
job, instead of you running it. See if this helps:
http://support.microsoft.com/defaul...;269074&sd=tech
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"bssolutions" <bssolutions@.discussions.microsoft.com> wrote in message
news:D1FAB676-20A5-4DE1-A20E-90C5114A10BB@.microsoft.com...
Situation: I have to copy data from a host SQL Server and place it onto my
SQL Server. We have an intermediary box with Console installed with both
SQL
Servers registered. I have setup a DTS that performs the transfer.
Problem: I can run the DTS manually but when I schedule the job to run
automatically I get an error message that the job cannot be run because the
user is a nonSysAdmin.|||Since the 1st box is accessed thru VPN, we have a 2nd box that acts as the
connecting device with console and is outside our domain, and the 3rd box is
inside our domain with SQL installed. I made the login, for test purposes a
member of sysadmin. This allowed me to schedule the job and it would run as
scheduled but now I get a message that it cannot find the server referenced
(a simply sql statement is my test to the 1st box).
Here is what the job history tells me:
Executed as user: TEST1\SYSTEM. DTSRun: Loading... DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnError:
DTSStep_DTSExecuteSQLTask_1, Error = -2147467259 (80004005) Error
string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not
exist or
access denied. Error source: Microsoft OLE DB Provider for SQL Server
Help file: Help context: 0 Error Detail Records: Error:
-2147467259 (80004005); Provider Error: 17 (11) Error string:
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
access
denied. Error source: Microsoft OLE DB Provider for SQL Server
Help file: Help context: 0 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_1 DTSRun: Package execution complete. Process
Exit Code 1. The step failed.
Thanks a bunch for you help on this.
"Narayana Vyas Kondreddi" wrote:

> That is because the context of the package is different when you run it as
a
> job, instead of you running it. See if this helps:
> http://support.microsoft.com/defaul...;269074&sd=tech
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "bssolutions" <bssolutions@.discussions.microsoft.com> wrote in message
> news:D1FAB676-20A5-4DE1-A20E-90C5114A10BB@.microsoft.com...
> Situation: I have to copy data from a host SQL Server and place it onto my
> SQL Server. We have an intermediary box with Console installed with both
> SQL
> Servers registered. I have setup a DTS that performs the transfer.
> Problem: I can run the DTS manually but when I schedule the job to run
> automatically I get an error message that the job cannot be run because th
e
> user is a nonSysAdmin.
>
>

Ability to schedule job across Domains

Situation: I have to copy data from a host SQL Server and place it onto my
SQL Server. We have an intermediary box with Console installed with both SQL
Servers registered. I have setup a DTS that performs the transfer.
Problem: I can run the DTS manually but when I schedule the job to run
automatically I get an error message that the job cannot be run because the
user is a nonSysAdmin.That is because the context of the package is different when you run it as a
job, instead of you running it. See if this helps:
http://support.microsoft.com/default.aspx?scid=kb;en-us;269074&sd=tech
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"bssolutions" <bssolutions@.discussions.microsoft.com> wrote in message
news:D1FAB676-20A5-4DE1-A20E-90C5114A10BB@.microsoft.com...
Situation: I have to copy data from a host SQL Server and place it onto my
SQL Server. We have an intermediary box with Console installed with both
SQL
Servers registered. I have setup a DTS that performs the transfer.
Problem: I can run the DTS manually but when I schedule the job to run
automatically I get an error message that the job cannot be run because the
user is a nonSysAdmin.|||Since the 1st box is accessed thru VPN, we have a 2nd box that acts as the
connecting device with console and is outside our domain, and the 3rd box is
inside our domain with SQL installed. I made the login, for test purposes a
member of sysadmin. This allowed me to schedule the job and it would run as
scheduled but now I get a message that it cannot find the server referenced
(a simply sql statement is my test to the 1st box).
Here is what the job history tells me:
Executed as user: TEST1\SYSTEM. DTSRun: Loading... DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnError:
DTSStep_DTSExecuteSQLTask_1, Error = -2147467259 (80004005) Error
string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
access denied. Error source: Microsoft OLE DB Provider for SQL Server
Help file: Help context: 0 Error Detail Records: Error:
-2147467259 (80004005); Provider Error: 17 (11) Error string:
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access
denied. Error source: Microsoft OLE DB Provider for SQL Server
Help file: Help context: 0 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_1 DTSRun: Package execution complete. Process
Exit Code 1. The step failed.
Thanks a bunch for you help on this.
"Narayana Vyas Kondreddi" wrote:
> That is because the context of the package is different when you run it as a
> job, instead of you running it. See if this helps:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;269074&sd=tech
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "bssolutions" <bssolutions@.discussions.microsoft.com> wrote in message
> news:D1FAB676-20A5-4DE1-A20E-90C5114A10BB@.microsoft.com...
> Situation: I have to copy data from a host SQL Server and place it onto my
> SQL Server. We have an intermediary box with Console installed with both
> SQL
> Servers registered. I have setup a DTS that performs the transfer.
> Problem: I can run the DTS manually but when I schedule the job to run
> automatically I get an error message that the job cannot be run because the
> user is a nonSysAdmin.
>
>

A question about clustered indexes forcing rebuild of non-clustered indexes.

So I'm readinghttp://www.sql-server-performance.com/tips/clustered_indexes_p2.aspx and I come across this:

When selecting a column to base your clustered index on, try toavoid columns that are frequently updated. Every time that a column used for a clustered index is modified, all of the non-clustered indexes must also be updated, creating additional overhead. [6.5, 7.0, 2000, 2005]Updated 3-5-2004

Does this mean if I have say a table called Item with a clustered index on a column in it called itemaddeddate, and several non-clustered indexes associated with that table, that if a record gets modified and it's itemaddeddate value changes, that ALL my indexes on that table will get rebuilt? Or is it referring to the table structure changing?

If so does this "pseudocode" example also cause this to occur:

sqlstring="select * from item where itemid=12345"

rs.open sqlstring, etc, etc, etc

rs.Fields("ItemName")="My New Item Name"

rs.Fields("ItemPrice")=1.00

rs.Update

Note I didn't explicitly change the value of rs.fields("ItemAddedDate")...does rs.Fields("ItemAddedDate")=rs.Fields("ItemAddedDate") occur implicitly, which would force the rebuild of all the non-clustered indexes?

Since it's been a while and no one responded, I thought I'd throw my 2 cents in. I'm not sure the link you posted is correct, but it might be. It would depend on whether SQL Server attempts to maintain clustering when you update a clustered index, and I don't know the answer to that. For example, suppose you have a clustered index on name and update someone from "AAAA" to "ZZZZ". This would change his position in the table, which is to say it would change the page number of that row (since it would move from the beginning of the DB to the end), which would mean that all other indexes would have to be updated too.

This depends entirely on whether SQL actually tries to maintain the clustering in real time, and I don't know the answer to that. With other products I've worked with, the DB just lets the data get out of cluster and you have to rebuild them from time-to-time.

|||

I posted this question on sql-server-performance and got a reply:http://sql-server-performance.com/Community/forums/p/23274/132088.aspx#132088

|||

dbland07666:

I posted this question on sql-server-performance and got a reply:http://sql-server-performance.com/Community/forums/p/23274/132088.aspx#132088

And that would be me ..

|||

I figured!

A new one on me!

I came across a select statement that has me all ! There is a
reference to a table in a case clause which does not appear anywhere else in
the Select. In the example below, TableC is a regular database table which
does not appear in the From or the where or anywhere else. I had no idea
this can be done and I have no idea what it is really doing. Any suggestions
?
Perplexed!
Select
.
.
CASE WHEN TableC.SaleDate IS NOT NULL THEN 1 ELSE 0 END 'IsSold',
. .
From tableA, tableB
Where . . .Could you post a complete query rather than a fragment? DDL might help
too.
You can't reference a table name / alias unless A) that name is
included in a FROM / JOIN clause in the same query / subquery OR B) the
name Exists in an outer part of the query and is referenced in a
subquery. I can't think of any exceptions.
David Portas
SQL Server MVP
--

A Matter of Style

When creating SQL scripts, what methods do you use when working with them?

More specifically:
How do you split the SQL across files? For example, do you put things relating to each table into separate files (table creation, indexes for the table, etc.), or do you group similar things into the same file (all indexes in one file, all table creation in another file, etc.)? Maybe you just have ONE BIG file that contains all the code?

Thanks for your input!Hello,

in our projects we split the intitscripts per object into separate file.

f.e. one for sequence
one for tables
and so on

and a upper script that connect to the database with thw correct user and calls the single files.

Hope that helps ?

Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com|||Originally posted by alligatorsql.com
and a upper script that connect to the database with thw correct user and calls the single files.


Ahh... this sounds like a good solution! How exactly do you "call" the individual files? Could you post an example?

Thanks!|||Hello,

here is a small example of a batch like we use it. Start the main.sql
in sqlplus. The other script will be called from the main.sql

Hope that help ?

Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com|||Originally posted by alligatorsql.com
Hope that help ?


Thanks! I'll have a look... I appreciate you taking the time to post.

A line in a report

I would like to put a line across my report, for appearance.
How can I make the width of the line to be set to the same as the width of a
matrix or table below it when the report is rendered?
I tried LineWidth "=ReportItems!Matrix1.width", but I got
"Report item expressions can only refer to other report items within the
same grouping scope or a containing grouping scope."
Thanks
BillIn the toolbox collection select line object. Place the line on the report,
then click on the line to 'activate' it. Grab one of the handles at the end
of the line an drag it to the length you want.
NOTE: you can also place the line on the report then use the properties view
to increase/decrease the length and width of the line, as well as the color,
type (solid, dashed, dotted, etc)
X
"bill" wrote:
> I would like to put a line across my report, for appearance.
> How can I make the width of the line to be set to the same as the width of a
> matrix or table below it when the report is rendered?
> I tried LineWidth "=ReportItems!Matrix1.width", but I got
> "Report item expressions can only refer to other report items within the
> same grouping scope or a containing grouping scope."
> Thanks
> Bill
>
>|||Do you know if the width can be set dynamically to the width of a matrix or
table when the report is produced?
Thanks for your help!
-Bill
"Xfonhe" <Xfonhe@.discussions.microsoft.com> wrote in message
news:CF4A40AE-D3C9-49A5-8A11-76E5F96F2EA2@.microsoft.com...
> In the toolbox collection select line object. Place the line on the
report,
> then click on the line to 'activate' it. Grab one of the handles at the
end
> of the line an drag it to the length you want.
> NOTE: you can also place the line on the report then use the properties
view
> to increase/decrease the length and width of the line, as well as the
color,
> type (solid, dashed, dotted, etc)
> X
> "bill" wrote:
> > I would like to put a line across my report, for appearance.
> >
> > How can I make the width of the line to be set to the same as the width
of a
> > matrix or table below it when the report is rendered?
> >
> > I tried LineWidth "=ReportItems!Matrix1.width", but I got
> > "Report item expressions can only refer to other report items within the
> > same grouping scope or a containing grouping scope."
> >
> > Thanks
> > Bill
> >
> >
> >|||Well, you can set the width by expression in the properties view in Report
Designer. I don't know of a built-in function to allow dynamic scaling of an
object in proporation to a second object. You will probably need to write
code to support it, then use the the =CODE() in the expression.
NOTE: to write custom code & embed in the report, eright-click on the blank
section of the layout view, select properties, then the Code tab.
Good luck!
X
"bill" wrote:
> Do you know if the width can be set dynamically to the width of a matrix or
> table when the report is produced?
> Thanks for your help!
> -Bill
> "Xfonhe" <Xfonhe@.discussions.microsoft.com> wrote in message
> news:CF4A40AE-D3C9-49A5-8A11-76E5F96F2EA2@.microsoft.com...
> > In the toolbox collection select line object. Place the line on the
> report,
> > then click on the line to 'activate' it. Grab one of the handles at the
> end
> > of the line an drag it to the length you want.
> >
> > NOTE: you can also place the line on the report then use the properties
> view
> > to increase/decrease the length and width of the line, as well as the
> color,
> > type (solid, dashed, dotted, etc)
> >
> > X
> >
> > "bill" wrote:
> >
> > > I would like to put a line across my report, for appearance.
> > >
> > > How can I make the width of the line to be set to the same as the width
> of a
> > > matrix or table below it when the report is rendered?
> > >
> > > I tried LineWidth "=ReportItems!Matrix1.width", but I got
> > > "Report item expressions can only refer to other report items within the
> > > same grouping scope or a containing grouping scope."
> > >
> > > Thanks
> > > Bill
> > >
> > >
> > >
>
>|||The line itself is not associated with the matrix. So when your matrix grows
or shrinks, the line stays at the same place, and it will look very awkward.
Instead of drawing a line, try use the border control on the rows of the
matrix you want the line to show up. Just like Excel, you can apply just the
top border, or the bottom border, this way the "border" would appear as a
line, and it will move according to your matrix.
"bill" wrote:
> Do you know if the width can be set dynamically to the width of a matrix or
> table when the report is produced?
> Thanks for your help!
> -Bill
> "Xfonhe" <Xfonhe@.discussions.microsoft.com> wrote in message
> news:CF4A40AE-D3C9-49A5-8A11-76E5F96F2EA2@.microsoft.com...
> > In the toolbox collection select line object. Place the line on the
> report,
> > then click on the line to 'activate' it. Grab one of the handles at the
> end
> > of the line an drag it to the length you want.
> >
> > NOTE: you can also place the line on the report then use the properties
> view
> > to increase/decrease the length and width of the line, as well as the
> color,
> > type (solid, dashed, dotted, etc)
> >
> > X
> >
> > "bill" wrote:
> >
> > > I would like to put a line across my report, for appearance.
> > >
> > > How can I make the width of the line to be set to the same as the width
> of a
> > > matrix or table below it when the report is rendered?
> > >
> > > I tried LineWidth "=ReportItems!Matrix1.width", but I got
> > > "Report item expressions can only refer to other report items within the
> > > same grouping scope or a containing grouping scope."
> > >
> > > Thanks
> > > Bill
> > >
> > >
> > >
>
>

A good Database Modelling Tool for Sql Server 2005

Hello i am looking for a good database modelling tool in order to design an
sql server database.
I have recently come across with DBDesigner 4. Can you suggest me a better
alternative?
Hello,
Go for microsoft Visio. Download a trial version from below URL:-
http://office.microsoft.com/en-us/visio/default.aspx
Thanks
Hari
"WoodenSWord" <WoodenSWord@.discussions.microsoft.com> wrote in message
news:2BE42E3C-457B-47F4-9B48-5909C70BF5FB@.microsoft.com...
> Hello i am looking for a good database modelling tool in order to design
> an
> sql server database.
> I have recently come across with DBDesigner 4. Can you suggest me a better
> alternative?
|||If you can persuade your company to fork out the cash, I'd recommend ERWIN :
http://www3.ca.com/solutions/Product.aspx?ID=260
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||I use Erwin but it is really expensive for most people. ER/Studio from
Embarcadero is also very good and much cheaper -
http://www.embarcadero.com/products/erstudio/index.html.
Roman
"WoodenSWord" wrote:

> Hello i am looking for a good database modelling tool in order to design an
> sql server database.
> I have recently come across with DBDesigner 4. Can you suggest me a better
> alternative?
|||We can 2nd that. We're a small agency and find er-studio almost
indespensible.
The developers are always after me for a new 'database map' ...
It's helpful to have a large format plotter around if you're going to be
creating systems with hundreds of tables and relations. Or current just
barely readable er-studio map for our water rights system measures roughly 3
x 4 feet.
Barry
in Oregon
"Roman Rehak" <RomanRehak@.discussions.microsoft.com> wrote in message
news:B79EFCE6-677E-4AD9-88A8-1579587F15BD@.microsoft.com...[vbcol=seagreen]
> I use Erwin but it is really expensive for most people. ER/Studio from
> Embarcadero is also very good and much cheaper -
> http://www.embarcadero.com/products/erstudio/index.html.
> Roman
> "WoodenSWord" wrote: