Best schema for replicate a DB without replicate Data

Hi,
We had log shipping from LA to New York for our mission critical databases.
However, the log shipping breaks all the time because some of these databases
or tran logs are huge.
Afer re-evaluation we found that some DBs we don't really need the data
replicated but we do need to keep the schema and objects up to date:
everything except the huge volume of data. It does not need to be real time,
we could do this once a day.
Could someone suggests us what are the better options of doing this? Some
way that is simple without a lot of maintenance issues and potential breaking
points.
Thanks
HuiSeems you simply want to generate schema at regular intervals?
http://www.karaszi.com/SQLServer/info_generate_script.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Christine C" <ChristineC@.discussions.microsoft.com> wrote in message
news:87C67A1A-52DF-4069-AD9B-A8637ADD3B35@.microsoft.com...
> Hi,
> We had log shipping from LA to New York for our mission critical databases.
> However, the log shipping breaks all the time because some of these databases
> or tran logs are huge.
> Afer re-evaluation we found that some DBs we don't really need the data
> replicated but we do need to keep the schema and objects up to date:
> everything except the huge volume of data. It does not need to be real time,
> we could do this once a day.
> Could someone suggests us what are the better options of doing this? Some
> way that is simple without a lot of maintenance issues and potential breaking
> points.
> Thanks
> Hui
>
>|||check out DB Ghost - http://www.dbghost.com
"Christine C" wrote:
> Hi,
> We had log shipping from LA to New York for our mission critical databases.
> However, the log shipping breaks all the time because some of these databases
> or tran logs are huge.
> Afer re-evaluation we found that some DBs we don't really need the data
> replicated but we do need to keep the schema and objects up to date:
> everything except the huge volume of data. It does not need to be real time,
> we could do this once a day.
> Could someone suggests us what are the better options of doing this? Some
> way that is simple without a lot of maintenance issues and potential breaking
> points.
> Thanks
> Hui
>
>|||Sorry, I did not describe the full requirement:
1. We need to copy all changes in the databases (including sysusers, stored
procedures, views, udf, ...etc) except the data. Of course sysusers we do
need the data because we need all the users replicated.
2. One challenges we have is that there are objects (views, stored proc)
that are cross databases. Therefore whatever tools we used need to be able to
not error out because of the dependencies.
3. One of our developers are trying the SQL-DMO APIs to do the
copyalltables. Here are the uncertains for us:
a. He is not sure if SQL-DMO allows him to copy the users, views, and
stored procedures separately as well.
b. Tthe entire extract for replicating three databases (without data) took
50 minutes. Which means the entire time the destination databases are going
to be unusable for a long time.
Common question to all tools in your link:
c. what we need is for the tool to take care of the delta of the source and
destination. The objects that no longer exist in the source, how do we remove
them in the destination if the scripts are driving from the source?
d. The tools suggested in your link, are they mostly scripting tools for
DDLs only (we don't care the data) or some of them can transfer users as
well?
e. Do they require manually adding or changing the script defination
everytime a DB has changed (schema or stored proc)? Can those tools also know
to remove all objects before it proceeds?
I know the list of questions are long, but these are critical processes we
want to setup in production. The problems that we encountered might not be
standards and having huge databases need to replicate to New York for DR
complicates the issue too.
Thank you for your patience in replying.
"Tibor Karaszi" wrote:
> Seems you simply want to generate schema at regular intervals?
> http://www.karaszi.com/SQLServer/info_generate_script.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Christine C" <ChristineC@.discussions.microsoft.com> wrote in message
> news:87C67A1A-52DF-4069-AD9B-A8637ADD3B35@.microsoft.com...
> > Hi,
> >
> > We had log shipping from LA to New York for our mission critical databases.
> > However, the log shipping breaks all the time because some of these databases
> > or tran logs are huge.
> >
> > Afer re-evaluation we found that some DBs we don't really need the data
> > replicated but we do need to keep the schema and objects up to date:
> > everything except the huge volume of data. It does not need to be real time,
> > we could do this once a day.
> > Could someone suggests us what are the better options of doing this? Some
> > way that is simple without a lot of maintenance issues and potential breaking
> > points.
> >
> > Thanks
> >
> > Hui
> >
> >
> >
> >
>
>sql

Best schema for replicate a DB without replicate Data

Hi,
We had log shipping from LA to New York for our mission critical databases.
However, the log shipping breaks all the time because some of these databases
or tran logs are huge.
Afer re-evaluation we found that some DBs we don't really need the data
replicated but we do need to keep the schema and objects up to date:
everything except the huge volume of data. It does not need to be real time,
we could do this once a day.
Could someone suggests us what are the better options of doing this? Some
way that is simple without a lot of maintenance issues and potential breaking
points.
Thanks
Hui
Seems you simply want to generate schema at regular intervals?
http://www.karaszi.com/SQLServer/inf...ate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Christine C" <ChristineC@.discussions.microsoft.com> wrote in message
news:87C67A1A-52DF-4069-AD9B-A8637ADD3B35@.microsoft.com...
> Hi,
> We had log shipping from LA to New York for our mission critical databases.
> However, the log shipping breaks all the time because some of these databases
> or tran logs are huge.
> Afer re-evaluation we found that some DBs we don't really need the data
> replicated but we do need to keep the schema and objects up to date:
> everything except the huge volume of data. It does not need to be real time,
> we could do this once a day.
> Could someone suggests us what are the better options of doing this? Some
> way that is simple without a lot of maintenance issues and potential breaking
> points.
> Thanks
> Hui
>
>
|||check out DB Ghost - http://www.dbghost.com
"Christine C" wrote:

> Hi,
> We had log shipping from LA to New York for our mission critical databases.
> However, the log shipping breaks all the time because some of these databases
> or tran logs are huge.
> Afer re-evaluation we found that some DBs we don't really need the data
> replicated but we do need to keep the schema and objects up to date:
> everything except the huge volume of data. It does not need to be real time,
> we could do this once a day.
> Could someone suggests us what are the better options of doing this? Some
> way that is simple without a lot of maintenance issues and potential breaking
> points.
> Thanks
> Hui
>
>
|||Sorry, I did not describe the full requirement:
1. We need to copy all changes in the databases (including sysusers, stored
procedures, views, udf, ...etc) except the data. Of course sysusers we do
need the data because we need all the users replicated.
2. One challenges we have is that there are objects (views, stored proc)
that are cross databases. Therefore whatever tools we used need to be able to
not error out because of the dependencies.
3. One of our developers are trying the SQL-DMO APIs to do the
copyalltables. Here are the uncertains for us:
a. He is not sure if SQL-DMO allows him to copy the users, views, and
stored procedures separately as well.
b. Tthe entire extract for replicating three databases (without data) took
50 minutes. Which means the entire time the destination databases are going
to be unusable for a long time.
Common question to all tools in your link:
c. what we need is for the tool to take care of the delta of the source and
destination. The objects that no longer exist in the source, how do we remove
them in the destination if the scripts are driving from the source?
d. The tools suggested in your link, are they mostly scripting tools for
DDLs only (we don't care the data) or some of them can transfer users as
well?
e. Do they require manually adding or changing the script defination
everytime a DB has changed (schema or stored proc)? Can those tools also know
to remove all objects before it proceeds?
I know the list of questions are long, but these are critical processes we
want to setup in production. The problems that we encountered might not be
standards and having huge databases need to replicate to New York for DR
complicates the issue too.
Thank you for your patience in replying.
"Tibor Karaszi" wrote:

> Seems you simply want to generate schema at regular intervals?
> http://www.karaszi.com/SQLServer/inf...ate_script.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Christine C" <ChristineC@.discussions.microsoft.com> wrote in message
> news:87C67A1A-52DF-4069-AD9B-A8637ADD3B35@.microsoft.com...
>
>

Best schema for replicate a DB without replicate Data

Hi,
We had log shipping from LA to New York for our mission critical databases.
However, the log shipping breaks all the time because some of these database
s
or tran logs are huge.
Afer re-evaluation we found that some DBs we don't really need the data
replicated but we do need to keep the schema and objects up to date:
everything except the huge volume of data. It does not need to be real time,
we could do this once a day.
Could someone suggests us what are the better options of doing this? Some
way that is simple without a lot of maintenance issues and potential breakin
g
points.
Thanks
HuiSeems you simply want to generate schema at regular intervals?
http://www.karaszi.com/SQLServer/in...rate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Christine C" <ChristineC@.discussions.microsoft.com> wrote in message
news:87C67A1A-52DF-4069-AD9B-A8637ADD3B35@.microsoft.com...
> Hi,
> We had log shipping from LA to New York for our mission critical databases
.
> However, the log shipping breaks all the time because some of these databa
ses
> or tran logs are huge.
> Afer re-evaluation we found that some DBs we don't really need the data
> replicated but we do need to keep the schema and objects up to date:
> everything except the huge volume of data. It does not need to be real tim
e,
> we could do this once a day.
> Could someone suggests us what are the better options of doing this? Some
> way that is simple without a lot of maintenance issues and potential break
ing
> points.
> Thanks
> Hui
>
>|||check out DB Ghost - http://www.dbghost.com
"Christine C" wrote:

> Hi,
> We had log shipping from LA to New York for our mission critical databases
.
> However, the log shipping breaks all the time because some of these databa
ses
> or tran logs are huge.
> Afer re-evaluation we found that some DBs we don't really need the data
> replicated but we do need to keep the schema and objects up to date:
> everything except the huge volume of data. It does not need to be real tim
e,
> we could do this once a day.
> Could someone suggests us what are the better options of doing this? Some
> way that is simple without a lot of maintenance issues and potential break
ing
> points.
> Thanks
> Hui
>
>|||Sorry, I did not describe the full requirement:
1. We need to copy all changes in the databases (including sysusers, stored
procedures, views, udf, ...etc) except the data. Of course sysusers we do
need the data because we need all the users replicated.
2. One challenges we have is that there are objects (views, stored proc)
that are cross databases. Therefore whatever tools we used need to be able t
o
not error out because of the dependencies.
3. One of our developers are trying the SQL-DMO APIs to do the
copyalltables. Here are the uncertains for us:
a. He is not sure if SQL-DMO allows him to copy the users, views, and
stored procedures separately as well.
b. Tthe entire extract for replicating three databases (without data) took
50 minutes. Which means the entire time the destination databases are going
to be unusable for a long time.
Common question to all tools in your link:
c. what we need is for the tool to take care of the delta of the source and
destination. The objects that no longer exist in the source, how do we remov
e
them in the destination if the scripts are driving from the source?
d. The tools suggested in your link, are they mostly scripting tools for
DDLs only (we don't care the data) or some of them can transfer users as
well?
e. Do they require manually adding or changing the script defination
everytime a DB has changed (schema or stored proc)? Can those tools also kno
w
to remove all objects before it proceeds?
I know the list of questions are long, but these are critical processes we
want to setup in production. The problems that we encountered might not be
standards and having huge databases need to replicate to New York for DR
complicates the issue too.
Thank you for your patience in replying.
"Tibor Karaszi" wrote:

> Seems you simply want to generate schema at regular intervals?
> http://www.karaszi.com/SQLServer/in...rate_script.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Christine C" <ChristineC@.discussions.microsoft.com> wrote in message
> news:87C67A1A-52DF-4069-AD9B-A8637ADD3B35@.microsoft.com...
>
>

Best Query Strategy

I am faced with the need to query the price of parts from a 3 table join.
The problem is the number of parts that need to be queried at one time; 10 t
o
100 parts. That would make for a very messy WHERE clause. I am wonder if
there is a better strategy?
If it matters, I am using VB.NET.
Thanks
--Rob
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200510/1Robin,
Are the parts PART OF a larger organizational unit i.e., a project or a
company or...?
HTH
Jerry
"Robin H via droptable.com" <u4108@.uwe> wrote in message
news:5655fd7d2e20a@.uwe...
>I am faced with the need to query the price of parts from a 3 table join.
> The problem is the number of parts that need to be queried at one time; 10
> to
> 100 parts. That would make for a very messy WHERE clause. I am wonder if
> there is a better strategy?
> If it matters, I am using VB.NET.
> Thanks
> --Rob
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200510/1|||Robin,
Is it possible you could do a VIEW for this as long as the query is not
dynamic?
Shahryar
Robin H via droptable.com wrote:

>I am faced with the need to query the price of parts from a 3 table join.
>The problem is the number of parts that need to be queried at one time; 10
to
>100 parts. That would make for a very messy WHERE clause. I am wonder if
>there is a better strategy?
>If it matters, I am using VB.NET.
>Thanks
>--Rob
>
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is
legally privileged. The information is solely for the use of the intended
recipient(s); any disclosure, copying, distribution, or other use of this in
formation is strictly prohi
bited. If you have received this e-mail in error, please notify the sender
by return e-mail and delete this message. Thank you.

Best Query Strategy

I am faced with the need to query the price of parts from a 3 table join.
The problem is the number of parts that need to be queried at one time; 10 to
100 parts. That would make for a very messy WHERE clause. I am wonder if
there is a better strategy?
If it matters, I am using VB.NET.
Thanks
--Rob
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200510/1
Robin,
Are the parts PART OF a larger organizational unit i.e., a project or a
company or...?
HTH
Jerry
"Robin H via droptable.com" <u4108@.uwe> wrote in message
news:5655fd7d2e20a@.uwe...
>I am faced with the need to query the price of parts from a 3 table join.
> The problem is the number of parts that need to be queried at one time; 10
> to
> 100 parts. That would make for a very messy WHERE clause. I am wonder if
> there is a better strategy?
> If it matters, I am using VB.NET.
> Thanks
> --Rob
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200510/1
|||Robin,
Is it possible you could do a VIEW for this as long as the query is not
dynamic?
Shahryar
Robin H via droptable.com wrote:

>I am faced with the need to query the price of parts from a 3 table join.
>The problem is the number of parts that need to be queried at one time; 10 to
>100 parts. That would make for a very messy WHERE clause. I am wonder if
>there is a better strategy?
>If it matters, I am using VB.NET.
>Thanks
>--Rob
>
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohi
bited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.

Best Query Strategy

I am faced with the need to query the price of parts from a 3 table join.
The problem is the number of parts that need to be queried at one time; 10 to
100 parts. That would make for a very messy WHERE clause. I am wonder if
there is a better strategy?
If it matters, I am using VB.NET.
Thanks
--Rob
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200510/1Robin,
Are the parts PART OF a larger organizational unit i.e., a project or a
company or...?
HTH
Jerry
"Robin H via SQLMonster.com" <u4108@.uwe> wrote in message
news:5655fd7d2e20a@.uwe...
>I am faced with the need to query the price of parts from a 3 table join.
> The problem is the number of parts that need to be queried at one time; 10
> to
> 100 parts. That would make for a very messy WHERE clause. I am wonder if
> there is a better strategy?
> If it matters, I am using VB.NET.
> Thanks
> --Rob
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200510/1|||Robin,
Is it possible you could do a VIEW for this as long as the query is not
dynamic?
Shahryar
Robin H via SQLMonster.com wrote:
>I am faced with the need to query the price of parts from a 3 table join.
>The problem is the number of parts that need to be queried at one time; 10 to
>100 parts. That would make for a very messy WHERE clause. I am wonder if
>there is a better strategy?
>If it matters, I am using VB.NET.
>Thanks
>--Rob
>
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohibited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.

Best practices: GROUP BY clause

I was wondering what the best way to write a GROUP BY clause when there are many (and time consuming) operations in the fields by grouped.

Fictious example:

SELECT DeptNo, AVG(Salary) FROM Department GROUP BY DeptNo;

This will give me the average salary per department. Let's say, however that
I had 10-15 fields being returned (along with the AVG(Salary)) and some fields even had operations being performed on them. Is it better to create a temporary table to calculate the sum per department (or a VIEW) and then
perform a JOIN with the rest of the data?

Fictious example:

SELECT DATENAME(y, StartDate), DATENAME(m, StartDate), DATEPART(d, StartDate), SUBSTR(DeptName, 1, 10), SomeFunction(SomeField), SomeFunction(SomeField), AVG(Salary)
GROUP BY DATENAME(y, StartDate), DATENAME(m, StartDate), DATEPART(d, StartDate), SUBSTR(DeptName, 1, 10), SomeFunction(SomeField), SomeFunction(SomeField);

Am I better off writing my query this way or using a JOIN on some temporary table or view?

ThanksWrite your query this way. One way to maximize the efficiency of a process is to reduce the number of times the server has to scan through the data. By putting all your aggregate functions in a single statement, the server only needs to run through the dataset one time.

But are all those datename and datepart functions necessary? That seems kind of wastefull. You could accomplish the same thing just by sorting by date.|||Bindman...That was just an example I made up. I am not asking this for a particular case right now, but I have in the past had queries that had many fields, and many of those fields had math/string/etc functions performed on them. Most of the time This was to provide formatting for a query that would be dumped into a report or out put to the user. For example, I might format an ID by Left padding with zeroes:
RIGHT(REPLICATE(MyPadChar, MyFieldWidth) + CAST(MyID AS VARCHAR), MyFieldWidth) AS [MyFormattedID]......

so all those fields would appear in my group by clause....I was wondering if this was a good practice.

Thanks|||It's acceptable in my opinion.

Anybody else want to comment on this?|||SELECT * FROM (
SELECT DATENAME(y, StartDate) AS Col1
, DATENAME(m, StartDate) AS Col2
, DATEPART(d, StartDate) AS Col3
, SUBSTRING(DeptName, 1, 10) AS Col4
, SomeFunction(SomeField) AS Col5
, SomeFunction(SomeField) AS Col6
, AVG(Salary) AS Col7
FROM myTable99) AS XXX
GROUP BY Col1, Col2, Col3, Col4, Col5, Col6|||Yeah, I thought about suggesting that. I've used it for clarity of coding before, but can you think of any reason it might or might not be more efficient? I guess the question is, when you include a formula in the output and also specify it in the GROUP BY clause, does the server calculate the formula twice, or is it smart enough to just calculate it once?|||Kaiser. Thanks for the hint...that'll clean things up a whole lot...
As for blindman, yeah, I'd like to know if evaluation takes place twice when the quesy is run.|||I guess the question is, when you include a formula in the output and also specify it in the GROUP BY clause, does the server calculate the formula twice, or is it smart enough to just calculate it once?

It had better be once, since it's a derived table...I never checked, but a SHOWPLAN should tell you what's up.

But again, this is M$, so you never know...

But since I'm a betting kinda guy...

$US1000.00 on Once to Win....|||Kaiser. Thanks for the hint....

Your welcome....Kaiser? Try Brett, x002548, or Ski (get it...)

Where are you in the world?|||Then would you agree that your subquery example would not be more efficient than coding formulas in the WHERE clause, though it scores points for clarity?|||No...would you agree that using formulas would cause non sargable predicates there by invalidating the use of any index?|||Yes, but the use of indexes is lost anyway when you filter on the results of forumulas in the subquery. I don't see how either of these methods would make efficient use of indexes.|||In the subquery, Query Analyser still complains about the subquery you mentioned. If it sees an aggregate function with other fields, you have to GROUP BY the other fields.
Sorry, I read it too fast the first time. I thought I saw:

SELECT ClientID AS Col1, OrderID AS Col2, SUM(Price) AS Col3
GROUP BY Col1, Col2;

I did not see the subquery and thought that what you gave me was similiar to
the ORDER BY 1, 2, 3... clause (instead of using actual column names)|||scores points for clarity? are you talking about post #5?

yes, assuming all the errors were fixed up!|||Yeah, I meant post #5. (the thread is getting a little long).

Um, the subquery won't run because of the aggregate missing a GROUP BY clause.

I also couldn't get the group clause to work on a column alias...|||select col1, col2, col3, col4, col5, col6, col7
from (
select datename(y, startdate) as col1
, datename(m, startdate) as col2
, datepart(d, startdate) as col3
, substring(deptname, 1, 10) as col4
, somefunction(somefield) as col5
, somefunction(somefield) as col6
, avg(salary) as col7
from mytable99
group
by datename(y, startdate)
, datename(m, startdate)
, datepart(d, startdate)
, substring(deptname, 1, 10)
, somefunction(somefield)
, somefunction(somefield)
) as xxx

Best Practices sFTPing files between servers

Hi-

I have a sql 2005 database that stores weekly time and attendance data for 500 users. I need to send the file on a scheduled basis.

I'd alo like to have programmatic control over sending the file via my asp,net application's admin pages. (I have roles and security set up on the site)

My question is two fold:

Can asp.net be scripted to manage a data extraction to a file, (I gotta HOPE so) THEN is there any sFTP functions built into asp.net?

I know I can do this in SQL2005, then have a scheduled event fire off the file via sFTP. but like I said, I'd like to have programmatic control over the file sending. (Of course they have to be sent on SUNDAY!)

Thanks in advance for any advise

You can have a button that when its cliked, you check on the day. If it is Sunday (for example) then call some stored procedures which basically will run a DTS/SSIS package which you can confugure it to take the needed data from the database and dump it in Excel file. Then you send this Excel file (e.g. Weekly Report) to the management though email (as attached file).

How to run the DTS/SSIS package from ASP.NET, please have a look on these links:

http://www.sqlteam.com/article/how-to-asynchronously-execute-a-dts-package-from-asp-or-aspnet

http://www.sqldev.net/dts/ExecutePackage.htm#Visual%20Csharp

Good luck.

|||

4Ever-

First, thank you for helping out so much in these forums.

Second, great idea, but I have a specific file format constraints as well as an sFTP directory my flat file needs to show up in.

So let me repackage my question.

Is there a way to (stored procedures?) script sql server 2005 to export and import data to and from a sFTP directory on another server? I gotta think I am not the only one that ever had this requirement!

Thanks again for your help.

Dan

|||

Harperator:

4Ever-

First, thank you for helping out so much in these forums.

Thanks Dan.

I think this is a good time to use BizTalk, but there might be a less expensive solutoions..

What about createing a C# windows services to do this or if you have a complex logic use VB script (not sure about it)?

So, to make things clear .. You have a sFTP which reside in diffrent machine that your SQL Server , right?

If this is right, so what is preventing from using the suggest solution above (my previous post)? If it is the file strcture .. can you have it in diffrent format (e.g. CSV) then organzing it in your special strcture?

|||

Hum...

here's the deal:

I have asp.net program running on iis on a win server running sql 2005.

I need to SFTP a file weekly, to and from a remote server.

I need to automate the process.

I can do this by creating a ssis job in sql, have it output the file, then write a batch file to send it via sftp using securefx by Vandike software.

I am just wondering if there is not a simple microsoft solution already built into sql, or iis, of asp.net or windows server.

|||

I do not know if this will help but let us see ..

In your "Transformation" you have a source which is SQL and the destination (e.g. Excel file .. select that path which indicate to your file which is already in another server).

Do your transformation -> Data will be in Excel.

Let your client get the file (e.g. Copy & Pase).

Have a batch which will basically copy data from an empty Excel file to your REAL file -> to have a new Excel file ready to receive the data from SQL Server.

I do not know if this will help .. I hope it is.

Good luck.

|||

Thanks!

Dan

(I broke down and bought sql 2005 unleashed)

Best Practice to Simulate Time

Hi,
We have the need to roll the time of the database server forward to perform
some time sensitive testing. The problem is that the database test server
is part of a production Windows 2000 environment. I suppose the optimal
thing to do would be to move the clock on the server forward X number of
hours, and then test our procedures. The problem is that Windows 2000 will
automatically sync up the time because of the Kerberos security. We can't
change the time on all of the servers. Also, when our SQL queries are
retrieving the date/time, it calls GetDate().
Does anybody have any ideas of what we could do to simulate time? I know
one quick and easy answer is to set up a completely separate environment
perhaps with one server. We could put Win2k and SQL Server on that box. It
would be its own domain, so we coould play with the time however we want. I
was wondering if there was a better way of doing this.
Thanks in advance,
cj
Pull the server out of domain and see if it works. You may need to
reconfigure the service account
Thanks
Ravi
"Curtis Justus" wrote:

> Hi,
> We have the need to roll the time of the database server forward to perform
> some time sensitive testing. The problem is that the database test server
> is part of a production Windows 2000 environment. I suppose the optimal
> thing to do would be to move the clock on the server forward X number of
> hours, and then test our procedures. The problem is that Windows 2000 will
> automatically sync up the time because of the Kerberos security. We can't
> change the time on all of the servers. Also, when our SQL queries are
> retrieving the date/time, it calls GetDate().
> Does anybody have any ideas of what we could do to simulate time? I know
> one quick and easy answer is to set up a completely separate environment
> perhaps with one server. We could put Win2k and SQL Server on that box. It
> would be its own domain, so we coould play with the time however we want. I
> was wondering if there was a better way of doing this.
> Thanks in advance,
> cj
>
>
|||Firstly and most importantly, why would you even consider doing this kind of
test on a production server?
I generally make it a rule to avoid writing time-sensitive code precisely
because of the obvious testing problems. If you need to reference the
current date and time then parameterize it or make your own class or
function to retrieve the clock information. That way you have a single point
at which you can interpose your own time value for testing purposes.
David Portas
SQL Server MVP
|||Ravi,
That is what I figured I would have to do. Thanks for the confirmation.
Take care,
cj
"Ravi" <Ravi@.discussions.microsoft.com> wrote in message
news:D4301F76-89AD-4554-9B8F-33EE8C686284@.microsoft.com...[vbcol=seagreen]
> Pull the server out of domain and see if it works. You may need to
> reconfigure the service account
> --
> Thanks
> Ravi
>
> "Curtis Justus" wrote:
|||David,
To answer your first question: there aren't any other production databases
on this "production" server. The only reason why I called it a production
server is because it is located in an active domain. They are converting
from a Netware network to the Win2K-based system and are slowly
transitioning over. I'm sorry for not sharing that information.
The date thing was something I had asked our database people about. However
it is too late in the game to change everything.
With that said, do you have any other suggestions? Perhaps going through
our stored procs and replacing GetDate with a call to a UDF might do the
trick.
Thanks,
cj
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:WpKdnYLmt7VwlV7fRVn-rQ@.giganews.com...
> Firstly and most importantly, why would you even consider doing this kind
> of test on a production server?
> I generally make it a rule to avoid writing time-sensitive code precisely
> because of the obvious testing problems. If you need to reference the
> current date and time then parameterize it or make your own class or
> function to retrieve the clock information. That way you have a single
> point at which you can interpose your own time value for testing purposes.
> --
> David Portas
> SQL Server MVP
> --
>
|||"Curtis Justus" <sure@.you.wont.spam.me.org> wrote in
news:uNp4NMSfFHA.2472@.TK2MSFTNGP15.phx.gbl:

> With that said, do you have any other suggestions? Perhaps going
> through our stored procs and replacing GetDate with a call to a UDF
> might do the trick.
Look out for CURRENT_TIMESTAMP as well.
Here's two crazy ideas:
- find or write a utility that traps all calls to the Windows API that get
the current time, returning a strange result. If necessary, have MSSQL.EXE
be spawned by the utility (instead of the normal service start).
- set the timezone offset to several hundred hours.

Best Practice to Simulate Time

Hi,
We have the need to roll the time of the database server forward to perform
some time sensitive testing. The problem is that the database test server
is part of a production Windows 2000 environment. I suppose the optimal
thing to do would be to move the clock on the server forward X number of
hours, and then test our procedures. The problem is that Windows 2000 will
automatically sync up the time because of the Kerberos security. We can't
change the time on all of the servers. Also, when our SQL queries are
retrieving the date/time, it calls GetDate().
Does anybody have any ideas of what we could do to simulate time? I know
one quick and easy answer is to set up a completely separate environment
perhaps with one server. We could put Win2k and SQL Server on that box. It
would be its own domain, so we coould play with the time however we want. I
was wondering if there was a better way of doing this.
Thanks in advance,
cjPull the server out of domain and see if it works. You may need to
reconfigure the service account
--
Thanks
Ravi
"Curtis Justus" wrote:

> Hi,
> We have the need to roll the time of the database server forward to perfor
m
> some time sensitive testing. The problem is that the database test server
> is part of a production Windows 2000 environment. I suppose the optimal
> thing to do would be to move the clock on the server forward X number of
> hours, and then test our procedures. The problem is that Windows 2000 wil
l
> automatically sync up the time because of the Kerberos security. We can't
> change the time on all of the servers. Also, when our SQL queries are
> retrieving the date/time, it calls GetDate().
> Does anybody have any ideas of what we could do to simulate time? I know
> one quick and easy answer is to set up a completely separate environment
> perhaps with one server. We could put Win2k and SQL Server on that box.
It
> would be its own domain, so we coould play with the time however we want.
I
> was wondering if there was a better way of doing this.
> Thanks in advance,
> cj
>
>|||Firstly and most importantly, why would you even consider doing this kind of
test on a production server?
I generally make it a rule to avoid writing time-sensitive code precisely
because of the obvious testing problems. If you need to reference the
current date and time then parameterize it or make your own class or
function to retrieve the clock information. That way you have a single point
at which you can interpose your own time value for testing purposes.
David Portas
SQL Server MVP
--|||Ravi,
That is what I figured I would have to do. Thanks for the confirmation.
Take care,
cj
"Ravi" <Ravi@.discussions.microsoft.com> wrote in message
news:D4301F76-89AD-4554-9B8F-33EE8C686284@.microsoft.com...[vbcol=seagreen]
> Pull the server out of domain and see if it works. You may need to
> reconfigure the service account
> --
> Thanks
> Ravi
>
> "Curtis Justus" wrote:
>|||David,
To answer your first question: there aren't any other production databases
on this "production" server. The only reason why I called it a production
server is because it is located in an active domain. They are converting
from a Netware network to the Win2K-based system and are slowly
transitioning over. I'm sorry for not sharing that information.
The date thing was something I had asked our database people about. However
it is too late in the game to change everything.
With that said, do you have any other suggestions? Perhaps going through
our stored procs and replacing GetDate with a call to a UDF might do the
trick.
Thanks,
cj
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:WpKdnYLmt7VwlV7fRVn-rQ@.giganews.com...
> Firstly and most importantly, why would you even consider doing this kind
> of test on a production server?
> I generally make it a rule to avoid writing time-sensitive code precisely
> because of the obvious testing problems. If you need to reference the
> current date and time then parameterize it or make your own class or
> function to retrieve the clock information. That way you have a single
> point at which you can interpose your own time value for testing purposes.
> --
> David Portas
> SQL Server MVP
> --
>|||"Curtis Justus" <sure@.you.wont.spam.me.org> wrote in
news:uNp4NMSfFHA.2472@.TK2MSFTNGP15.phx.gbl:

> With that said, do you have any other suggestions? Perhaps going
> through our stored procs and replacing GetDate with a call to a UDF
> might do the trick.
Look out for CURRENT_TIMESTAMP as well.
Here's two crazy ideas:
- find or write a utility that traps all calls to the Windows API that get
the current time, returning a strange result. If necessary, have MSSQL.EXE
be spawned by the utility (instead of the normal service start).
- set the timezone offset to several hundred hours.

Best Practice to Simulate Time

Hi,
We have the need to roll the time of the database server forward to perform
some time sensitive testing. The problem is that the database test server
is part of a production Windows 2000 environment. I suppose the optimal
thing to do would be to move the clock on the server forward X number of
hours, and then test our procedures. The problem is that Windows 2000 will
automatically sync up the time because of the Kerberos security. We can't
change the time on all of the servers. Also, when our SQL queries are
retrieving the date/time, it calls GetDate().
Does anybody have any ideas of what we could do to simulate time? I know
one quick and easy answer is to set up a completely separate environment
perhaps with one server. We could put Win2k and SQL Server on that box. It
would be its own domain, so we coould play with the time however we want. I
was wondering if there was a better way of doing this.
Thanks in advance,
cjPull the server out of domain and see if it works. You may need to
reconfigure the service account
--
Thanks
Ravi
"Curtis Justus" wrote:
> Hi,
> We have the need to roll the time of the database server forward to perform
> some time sensitive testing. The problem is that the database test server
> is part of a production Windows 2000 environment. I suppose the optimal
> thing to do would be to move the clock on the server forward X number of
> hours, and then test our procedures. The problem is that Windows 2000 will
> automatically sync up the time because of the Kerberos security. We can't
> change the time on all of the servers. Also, when our SQL queries are
> retrieving the date/time, it calls GetDate().
> Does anybody have any ideas of what we could do to simulate time? I know
> one quick and easy answer is to set up a completely separate environment
> perhaps with one server. We could put Win2k and SQL Server on that box. It
> would be its own domain, so we coould play with the time however we want. I
> was wondering if there was a better way of doing this.
> Thanks in advance,
> cj
>
>|||Firstly and most importantly, why would you even consider doing this kind of
test on a production server?
I generally make it a rule to avoid writing time-sensitive code precisely
because of the obvious testing problems. If you need to reference the
current date and time then parameterize it or make your own class or
function to retrieve the clock information. That way you have a single point
at which you can interpose your own time value for testing purposes.
--
David Portas
SQL Server MVP
--|||Ravi,
That is what I figured I would have to do. Thanks for the confirmation.
Take care,
cj
"Ravi" <Ravi@.discussions.microsoft.com> wrote in message
news:D4301F76-89AD-4554-9B8F-33EE8C686284@.microsoft.com...
> Pull the server out of domain and see if it works. You may need to
> reconfigure the service account
> --
> Thanks
> Ravi
>
> "Curtis Justus" wrote:
>> Hi,
>> We have the need to roll the time of the database server forward to
>> perform
>> some time sensitive testing. The problem is that the database test
>> server
>> is part of a production Windows 2000 environment. I suppose the optimal
>> thing to do would be to move the clock on the server forward X number of
>> hours, and then test our procedures. The problem is that Windows 2000
>> will
>> automatically sync up the time because of the Kerberos security. We
>> can't
>> change the time on all of the servers. Also, when our SQL queries are
>> retrieving the date/time, it calls GetDate().
>> Does anybody have any ideas of what we could do to simulate time? I know
>> one quick and easy answer is to set up a completely separate environment
>> perhaps with one server. We could put Win2k and SQL Server on that box.
>> It
>> would be its own domain, so we coould play with the time however we want.
>> I
>> was wondering if there was a better way of doing this.
>> Thanks in advance,
>> cj
>>|||David,
To answer your first question: there aren't any other production databases
on this "production" server. The only reason why I called it a production
server is because it is located in an active domain. They are converting
from a Netware network to the Win2K-based system and are slowly
transitioning over. I'm sorry for not sharing that information.
The date thing was something I had asked our database people about. However
it is too late in the game to change everything.
With that said, do you have any other suggestions? Perhaps going through
our stored procs and replacing GetDate with a call to a UDF might do the
trick.
Thanks,
cj
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:WpKdnYLmt7VwlV7fRVn-rQ@.giganews.com...
> Firstly and most importantly, why would you even consider doing this kind
> of test on a production server?
> I generally make it a rule to avoid writing time-sensitive code precisely
> because of the obvious testing problems. If you need to reference the
> current date and time then parameterize it or make your own class or
> function to retrieve the clock information. That way you have a single
> point at which you can interpose your own time value for testing purposes.
> --
> David Portas
> SQL Server MVP
> --
>|||"Curtis Justus" <sure@.you.wont.spam.me.org> wrote in
news:uNp4NMSfFHA.2472@.TK2MSFTNGP15.phx.gbl:
> With that said, do you have any other suggestions? Perhaps going
> through our stored procs and replacing GetDate with a call to a UDF
> might do the trick.
Look out for CURRENT_TIMESTAMP as well.
Here's two crazy ideas:
- find or write a utility that traps all calls to the Windows API that get
the current time, returning a strange result. If necessary, have MSSQL.EXE
be spawned by the utility (instead of the normal service start).
- set the timezone offset to several hundred hours.

Best Practice Analyzer for SQL Server 2005?

Hi everyone,

a long time I was using a nice tool called "Best Practice Analyzer Tool for SQL Server 2000". I would be interested whether this product is or will be available for SQL Server 2005.

Does anyone knows something about it?

Regards,

StSt

indeed that a nice tool.

for security best though you can also make use

of MBSA or MS baseline analyzer whichh supports sql server 2005

http://www.microsoft.com/technet/security/tools/mbsa2/default.mspx

|||

Hi,

no I was using the Best Practice Analyzer to check on certain SQL programming "styles" not for security reasons. I.e. I know very well how to optimize my procedures or which statement I should use and which statement I should avoid but it is difficult to make sure that these bpa rules has been respected over the whole database.

Trivial example:

Avoid using SET NOCOUNT OFF ... it might be set to ON in most procedures but there might still be some procedures where it is set to OFF or set by default.

So what I really need is a tool which checks on bpa rules and tells me if there is a violation or not.

Regards,

StSt

|||

i see..

i check mbsa.

sql 2k5 is not supported either.

lets wait....

|||

Hi,

Microsoft is currently working on a BPA for SQL Server 2005.
There is no release date known (at least not public).

Just a little patience ;-)

Regards

|||

That is correct, we have not released a hard date. We are aiming for second half of 2006. For more information you can visit my team's blog... http://blogs.msdn.com/sqlrem/.

Let me know if you have any questions.

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server
http://blogs.msdn.com/sqlrem/

|||

Here is the download link for SQL Server 2005 BPA February CTP

http://www.microsoft.com/downloads/details.aspx?FamilyId=DA0531E4-E94C-4991-82FA-F0E3FBD05E63&displaylang=en

Best Practice Analyzer for SQL Server 2005?

Hi everyone,

a long time I was using a nice tool called "Best Practice Analyzer Tool for SQL Server 2000". I would be interested whether this product is or will be available for SQL Server 2005.

Does anyone knows something about it?

Regards,

StSt

indeed that a nice tool.

for security best though you can also make use

of MBSA or MS baseline analyzer whichh supports sql server 2005

http://www.microsoft.com/technet/security/tools/mbsa2/default.mspx

|||

Hi,

no I was using the Best Practice Analyzer to check on certain SQL programming "styles" not for security reasons. I.e. I know very well how to optimize my procedures or which statement I should use and which statement I should avoid but it is difficult to make sure that these bpa rules has been respected over the whole database.

Trivial example:

Avoid using SET NOCOUNT OFF ... it might be set to ON in most procedures but there might still be some procedures where it is set to OFF or set by default.

So what I really need is a tool which checks on bpa rules and tells me if there is a violation or not.

Regards,

StSt

|||

i see..

i check mbsa.

sql 2k5 is not supported either.

lets wait....

|||

Hi,

Microsoft is currently working on a BPA for SQL Server 2005.
There is no release date known (at least not public).

Just a little patience ;-)

Regards

|||

That is correct, we have not released a hard date. We are aiming for second half of 2006. For more information you can visit my team's blog... http://blogs.msdn.com/sqlrem/.

Let me know if you have any questions.

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server
http://blogs.msdn.com/sqlrem/

|||

Here is the download link for SQL Server 2005 BPA February CTP

http://www.microsoft.com/downloads/details.aspx?FamilyId=DA0531E4-E94C-4991-82FA-F0E3FBD05E63&displaylang=en

Best practice

HI all,
I'll try one more time, if no answer then I gather no one has an opinion
either way.
I have a claims database, which has a header table, details table and
otherinfo table. The header table will contain claim header info, ie
claimnumber date of loss etc., the details would contain claim detail, as
details of loss, damage ammounts, etc, the otherinfo contains information
not common to all classes of claims, so I would have a motor table
conmtaining driver, motor type model etc, GPA, would contain empoyee name,
earnings medical etc.
Now this is just in pricipal, hence no ddl, but anyway, some one suggested
that for the otherinfo table I create a descriptor table that will enable us
to add numerous columns for this "otherinfo" information at will
his quote is:
"descriptor table that can hold the field names, the data type, an input
string regular expression, and a bit field for required or not. Field Start
Date, and Field End Date. Fields can then be added and removed at will"
Whyle this might sound good, my gut says its a bad idea, for starters, it
could be a nightmare trying to create stable reports, etc.
Any other thought here or suggestions
Thanks
RobertHey Robert,
Stay on topic. I'll respond to your question in the original thread;
don't hijack a post.
Stu|||my apologies, did not try and hijack this topic. Musta done something
unintentional
Robert
"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1144839162.136998.282480@.g10g2000cwb.googlegroups.com...
> Hey Robert,
> Stay on topic. I'll respond to your question in the original thread;
> don't hijack a post.
> Stu
>

Best Possible Solution

Hi folks, guidance required!
Clients have an MSDE installed and they need all of their data to be dowloaded for the first time from our website. 30 tables r involved.
Can i automate this? I suspect BCP allows only one table to be exported into a text file. Since it's the first time process i don't want to setup replication.

Howdy!What do you mean downloaded from you website?|||Hi, sir.
An offline scheduler type application that could be downloaded from our website. All the data of the clients is at the db server running our website. Now for the first time when the log on to the app they could be offered the facility to download all their data locally from the db. I suspect there could b a better way around to give the data to each provider in an automated way rather than selecting the data for each client; creating a database for each client and shipping it manually with the software.

Howdy!

best place to declare/load XSL for use in CLR StoredProc?

How to efficiently load XSL documents which will be used in a CLR SP. I want
to avoid loading it every time the SP is invoked.
Thanks,
ChrisHello ChrisHarrington" charrington-at-activeinterface.com,

> How to efficiently load XSL documents which will be used in a CLR SP.
> I want to avoid loading it every time the SP is invoked.
How about in an table, passing it as a parameter?
Thanks,
Kent Tegels, DevelopMentor
http://staff.develop.com/ktegels/|||Kent,
Thanks for responding. This CLR SP stuff in new to me. Could you elaborate
on your suggestion? Do you mean storing the XSL in an XML column in a table?
Thanks,
Chris
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad7452758c85c8a7518f6a0@.news.microsoft.com...
> Hello ChrisHarrington" charrington-at-activeinterface.com,
>
> How about in an table, passing it as a parameter?
> --
> Thanks,
> Kent Tegels, DevelopMentor
> http://staff.develop.com/ktegels/
>|||> Thanks for responding. This CLR SP stuff in new to me. Could you
> elaborate on your suggestion? Do you mean storing the XSL in an XML column
in a
> table?
I'll post an example in my blog shortly.
Thanks,
Kent Tegels, DevelopMentor
http://staff.develop.com/ktegels/|||Thanks - that would be very helpful.
Chris
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad7454618c85ce91388c3f0@.news.microsoft.com...
> in a
> I'll post an example in my blog shortly.
>
> --
> Thanks,
> Kent Tegels, DevelopMentor
> http://staff.develop.com/ktegels/
>|||Hello Chris,
Been a bit too busy to post, but here's the gist of it. First, we need a
SQLCLR function that actually does the transformation. Here's that:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Xml;
using System.Xml.Xsl;
using System.IO;
namespace DM.Examples
{
public partial class XmlLibrary
{
[SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic =
false, IsPrecise = false, SystemDataAccess = SystemDataAccessKind.None)]
[return: SqlFacet(IsFixedLength = false, IsNullable = true, MaxSize
= -1)]
public static SqlXml ApplyTransform(SqlXml Data, SqlXml StyleSheet)
{
// on null return null, just in case.
if (Data.IsNull || StyleSheet.IsNull)
return SqlXml.Null;
// Buffer the transformed xml
MemoryStream ms = new MemoryStream();
XmlWriter xw = XmlWriter.Create(ms);
// Load and transform
XslCompiledTransform ctx = new XslCompiledTransform(false);
ctx.Load(StyleSheet.CreateReader());
ctx.Transform(Data.CreateReader(), xw);
// return the result, assuming XML compliant output
return new SqlXml(ms);
}
}
}
here's some code I wrote to test that:
declare @.d xml, @.s xml
select @.d = (select productID as '@.dbid',ProductNumber as '@.productID',Name
as 'name',Color as 'color',ListPrice as 'listPrice',Size as 'Size',SizeUnitM
easureCode
as 'sizeCode',style as 'style' from adventureworks.production.product where
not(coalesce(discontinuedDate,'2999-12-31') = 1) and FinishedGoodsFlag =
1 for xml path('product'),root('products'),element
s xsinil,type)
select @.s = bulkcolumn from openrowset(bulk 'c:\simple.xslt',single_clob)
as p
select dbo.ApplyTransform(@.d,@.s)
the "c:\simple.xlst" is left as an excercise for the reader.
Cheers,
Kent Tegels, DevelopMentor
http://staff.develop.com/ktegels/|||Hi Kent,
Thanks for the code sample. But what I am really stumped on is how to have
the xsl available as a class static so that it doesn't have to be loaded and
compiled every time the SP is invoked. Any thoughts?
Chris
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad745b3b8c85df2965c9c40@.news.microsoft.com...
> Hello Chris,
> Been a bit too busy to post, but here's the gist of it. First, we need a
> SQLCLR function that actually does the transformation. Here's that:
> using System;
> using System.Data;
> using System.Data.SqlClient;
> using System.Data.SqlTypes;
> using Microsoft.SqlServer.Server;
> using System.Xml;
> using System.Xml.Xsl;
> using System.IO;
> namespace DM.Examples
> {
> public partial class XmlLibrary
> {
> [SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic =
> false, IsPrecise = false, SystemDataAccess = SystemDataAccessKind.None)]
> [return: SqlFacet(IsFixedLength = false, IsNullable = true, MaxSize
> = -1)]
> public static SqlXml ApplyTransform(SqlXml Data, SqlXml StyleSheet)
> {
> // on null return null, just in case.
> if (Data.IsNull || StyleSheet.IsNull)
> return SqlXml.Null;
> // Buffer the transformed xml
> MemoryStream ms = new MemoryStream();
> XmlWriter xw = XmlWriter.Create(ms);
> // Load and transform
> XslCompiledTransform ctx = new XslCompiledTransform(false);
> ctx.Load(StyleSheet.CreateReader());
> ctx.Transform(Data.CreateReader(), xw);
> // return the result, assuming XML compliant output
> return new SqlXml(ms);
> }
> }
> }
> here's some code I wrote to test that:
> declare @.d xml, @.s xml
> select @.d = (select productID as '@.dbid',ProductNumber as
> '@.productID',Name as 'name',Color as 'color',ListPrice as 'listPrice',Size
> as 'Size',SizeUnitMeasureCode as 'sizeCode',style as 'style' from
> adventureworks.production.product where
> not(coalesce(discontinuedDate,'2999-12-31') = 1) and FinishedGoodsFlag = 1
> for xml path('product'),root('products'),element
s xsinil,type)
> select @.s = bulkcolumn from openrowset(bulk 'c:\simple.xslt',single_clob)
> as p
> select dbo.ApplyTransform(@.d,@.s)
> the "c:\simple.xlst" is left as an excercise for the reader.
> Cheers,
> Kent Tegels, DevelopMentor
> http://staff.develop.com/ktegels/
>

Best hw recommendation for a sql server - server.

At this time, i have a server running sql server 2000 sp3 on a windows2000
server sp4. this server is running slow, so I need to recommend a new server
,
buy I don′t know how to estimate the best hw tha will help us.
Do some body know a tool that can help me to estimate the best hw for our
system requierements'
Thanks a lot for your help.> At this time, i have a server running sql server 2000 sp3 on a windows2000
> server sp4. this server is running slow, so I need to recommend a new
> server,
> buy I don′t know how to estimate the best hw tha will help us.
Why do you think the slowness is due to hardware problems?
The most monumental increases we have realized in the past performance over
the past year, were from:
(a) installing SQL Server 2000 SP4 (huge gain!)
(b) optimizing indexes, statistics and procedure code|||Have you already eliminated, deadlocking, poor indexing etc before deciding
to purchase new ...? If not throwing hardware at a problem will only be a
short term solution.
Find out the budget and work back from there...
HTH. Ryan
"Maria Guzman" <MariaGuzman@.discussions.microsoft.com> wrote in message
news:EDFA0CF8-51A1-4CC3-AA14-9F36D255DF2D@.microsoft.com...
> At this time, i have a server running sql server 2000 sp3 on a windows2000
> server sp4. this server is running slow, so I need to recommend a new
> server,
> buy I dont know how to estimate the best hw tha will help us.
> Do some body know a tool that can help me to estimate the best hw for our
> system requierements'
> Thanks a lot for your help.|||Have you used Performance Monitor to confirm that the bottleneck is your
server? Replacing the hardware may seem like a quick fix compared to
analyzing and re-programming the application, but if you shell out the
$$,$$$ and the problem is still not solved, then you end up looking really
bad.
Performance Monitor:
http://www.sql-server-performance.c...&seqNum=28&rl=1
How to Perform a SQL Server Performance Audit
http://www.sql-server-performance.c...mance_audit.asp
Checklist: SQL Server Performance
http://msdn.microsoft.com/library/d...
etcheck08.asp
There may still be an easy fix related to resolving deadlocks, logical or
disk defragmenting, or simply adding a crucially needed index.
http://support.microsoft.com/defaul...kb;en-us;832524
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
How To: Optimize SQL Indexes
http://msdn.microsoft.com/library/d...
etHowTo03.asp
"Maria Guzman" <MariaGuzman@.discussions.microsoft.com> wrote in message
news:EDFA0CF8-51A1-4CC3-AA14-9F36D255DF2D@.microsoft.com...
> At this time, i have a server running sql server 2000 sp3 on a windows2000
> server sp4. this server is running slow, so I need to recommend a new
> server,
> buy I dont know how to estimate the best hw tha will help us.
> Do some body know a tool that can help me to estimate the best hw for our
> system requierements'
> Thanks a lot for your help.|||Thanks a lot for your recommendatio. I applyed some of them. The other thing
that I need help to estimate the best hw for a sql server is that here in th
e
office they want to change the server because they want to implement a
Cluster. So I have to recommend a new hw requirements.
Do you know any formula that can help to to estimate that' or can you tell
me what issue I need to consider to analyze that.
Thanks a lot for your help.
"JT" wrote:

> Have you used Performance Monitor to confirm that the bottleneck is your
> server? Replacing the hardware may seem like a quick fix compared to
> analyzing and re-programming the application, but if you shell out the
> $$,$$$ and the problem is still not solved, then you end up looking really
> bad.
> Performance Monitor:
> http://www.sql-server-performance.c...ver.as
p
> Monitoring - Performance Monitor
> http://www.informit.com/guides/cont...&seqNum=28&rl=1
> How to Perform a SQL Server Performance Audit
> http://www.sql-server-performance.c...mance_audit.asp
> Checklist: SQL Server Performance
> http://msdn.microsoft.com/library/d...enetcheck08.asp
> There may still be an easy fix related to resolving deadlocks, logical or
> disk defragmenting, or simply adding a crucially needed index.
> http://support.microsoft.com/defaul...kb;en-us;832524
> Microsoft SQL Server 2000 Index Defragmentation Best Practices
> http://www.microsoft.com/technet/pr...eNetHowTo03.asp
> "Maria Guzman" <MariaGuzman@.discussions.microsoft.com> wrote in message
> news:EDFA0CF8-51A1-4CC3-AA14-9F36D255DF2D@.microsoft.com...
>
>

Best Design for reports over time

Hi everyone,
I am designing a reporting system for the internet using Reporting
Services. Is there a way to access the historical snapshots
programmatically? We are displaying the reports in the browser by
linking to them in the url, but that brings up the current month's
report. We want to be able to run the report monthly(different data)
and generate a snapshot, then link to the snapshots from the custom
ASP.Net application.
Can we do this? Is is there a better way?
Thanks in advance,
ShawnJust render a history snapshot and look at the generated URL. The URL will
look like this:
http://ServerName/Reports/Pages/Report.aspx?ItemPath=%SomeReport&HistoryID=2005-05-11T22:25:40
The HistoryID identifies the history snapshot in UTC time.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"sysdesigner" <sysdesigner@.discussions.microsoft.com> wrote in message
news:7B7A9AA3-2EBB-45D2-85B2-AD58FE3A5A09@.microsoft.com...
> Hi everyone,
> I am designing a reporting system for the internet using Reporting
> Services. Is there a way to access the historical snapshots
> programmatically? We are displaying the reports in the browser by
> linking to them in the url, but that brings up the current month's
> report. We want to be able to run the report monthly(different data)
> and generate a snapshot, then link to the snapshots from the custom
> ASP.Net application.
>
> Can we do this? Is is there a better way?
>
> Thanks in advance,
> Shawn
>
>

Best database IDE around

Hi!
For some time I've been looking at alternative tools for query construction
that could replace the old good Query Analyzer.
I think QA is a great tool for its purpose but it has become outdated and
it's missing some of the features we have come to expect in modern IDEs, like
Intellisense support for database objects, a Visual Studio-like Server
Explorer, some kind of project management (meaning the possibility to
organize my SQL files in projects) and a good debugger for stored procedures,
among others.
I've used Visual Studio .NET for a while for debugging stored procedures and
I think it was good but what I'm looking for is a tool especially dedicated
to the purpose of building queries and (possibly) managing database objects.
I've looked at the new Management Studio for SQL Server 2005 and I think it's
the closest thing to that kind of tools but I'm also interested in free/open
source alternatives.
Does anyone know of such a tool? I'm interested in hearing about what other
database administrators/developers' favourite tools are.
Thanks in advance
/Enrico
You can have a look at foxySQL
http://www.casestudio.com/enu/databa...l_foxysql.aspx
It's free and has some features like intellisense, but to be honest
I've gone back to QA.
M
|||Enrico Campidoglio wrote:

> the closest thing to that kind of tools but I'm also interested in free/open
> source alternatives.
Visit http://www.sqlmanager.net - they offer both free and commercial
utilities for many popular RDBMS (MSSQL, IB/FB, MySQL,...)
Christmas Carol
|||If you are looking to use QA or SQL Server 2005 Management Studio, and
want to add SQL Intellisense to these tools then you might want to
check out PromptSQL: http://www.promptsql.com/
Its not free, but at US$25/user its not too expensive either. One
month evaluation download.

being able to stop parallelism in a C++ Program calling SQL Server

Hi All,

I have written ETL software that runs on SQL Server. We are running it for the first time on a 4cpu (2 x dual core) machine on sql server 2005.

One of the things this software does is perform a 'select * from tablename' to validate that the tables passed to it as parameters exist. This has worked fine on previous releases and on single cpu machines because what the optimiser decides to do is to return just the first page of data and then fetch more. I guess it even works in 2005 standard edition.

However, 2005 enterprise edition allows parallelism. And what the optimiser is deciding to do with such a query is to parallelise it and fetch all rows and then give the result back to the program. So, instead of seeing a fraction of a second to return the first page of data we are seeing up to 90 seconds and the database goes and fetches 15M rows in parallel.

Obviously, what we would like to do is to somehow tell the optimiser that this set of programs should not perform any parallel queries. Or, we would like to turn parallelism off on the specific tables we are dealing with for the period of running these ETL programs....they have no need of parallel processing at the database level for virtually all the calls that are performed.

Would someone please be so kind as to advise us if we can do something like pass a parameter to ODBC to stop parallelism or if we can issue commands against specific tables to stop parallelism for a period and then turn it back on?

Thanks in Advance.

Peter Nolan

www.peternolan.com

select * from tablename OPTION (MAXDOP 1)

That should do it. MAXDOP stands for Max Degree of Parallelism, and allows you to limit the number of parallel execution threads for a query. Setting it to 1 essentially disables parallelism for that query.
|||

Hi David,

thanks very much for that....I did not know the syntax of how to do this at query level....

We turned off parallelism at the server level and it turns out the optimiser will still perform a table scan....we have many logical tables in on physical table and even when we query the underlying table with a select * from with a where clause it is scanning the table and taking about 40 seconds to do so....even when we say 'top 1' to make it get the first row and put an index on the field that logically partitions the table.

It looks like we will have to introduce partitioning so that the optimiser can know to scan just the partition in question. We also had a feature we planned to introduce which was to turn off table validation....if an etl subsystem is in production there is little need to validate tables....we never put it in...and now there seems like a need to do so.

Again, thanks for the tip. Much appreciated.

Peter

www.peternolan.com

|||

Hi All,

one of our guys here came up with a better idea....

What the tool is doing is validating the existence of the taget table/views passed to the program....so in the case of a typo a specific message saying the table/view not found is issued.

However the optimiser is thinking the program really wants all the rows.

One of the guys here suggested if the constraint where 1 = 0 is added to the select * then all optimisers will be smart enough to know no rows will be returned yet the database will still check that the table/view exists.

So we will also implement this test and that will make sure all programs test for existence of a table quickly.

If we feel industrious one day we can actually ask ODBC if the table exists.

One reason why I like the select * from tablename as a test for existence of a table over just asking ODBC is that it forces the preparation of the plan and if the view has been invalidated then it will also be caught at this stage of processing where ODBC will return a positive answer to does the view exist...

Again, thanks for your responses...it is much appreciated...

Just by the way....we installed the new performance dashboards just released and it was these that managed to show us the statement that was in a wait state and how we detected the problem...so if you have not installed the new performance dashboards yet....you might want to do so...

Best Regards

Peter