Backing up SQL Server: VDI or SQLDMO?

I am in the process of writing a backup software package, and I posted a
question here a while ago about how to best backup SQL Server databases, and
the preferred approach for implementing backup from a program written in C++
.
Louis Davidson recommended SQLDMO, but since then I've found documentation
for the "Virtual Device Interface (VDI) for Backup".
So now I'm wondering what the pros and cons are of these two approaches. To
judge by the documentation, it seems that VDI would be the preferred approac
h
since it provides the "the highest online backup throughput with minimal
degradation to the transaction workload".
Thanks,
PeterVDI and DMO are two completely different and are targeting two different
things
VDI is a virtual device interface for ISV's to develop SQL Server Backup
software so you can backup you SQL Server database and tlogs to for example
compressed files, remote locations by backing up over a pipe etc.
DMO is a management interface, abstracting programmers to learn or issue
TSQL commands directly.
Are you writing an application that tells SQL to perform a BACKUP or are you
planning on implementing a backup device?
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"jpstewart" <jpstewart@.discussions.microsoft.com> wrote in message
news:44B5C821-78C9-4C25-8951-9E4892825397@.microsoft.com...
>I am in the process of writing a backup software package, and I posted a
> question here a while ago about how to best backup SQL Server databases,
> and
> the preferred approach for implementing backup from a program written in
> C++.
> Louis Davidson recommended SQLDMO, but since then I've found documentation
> for the "Virtual Device Interface (VDI) for Backup".
> So now I'm wondering what the pros and cons are of these two approaches.
> To
> judge by the documentation, it seems that VDI would be the preferred
> approach
> since it provides the "the highest online backup throughput with minimal
> degradation to the transaction workload".
> Thanks,
> Peter
>|||Thanks for your reply. I'm writing an application that should be able to bac
k
up SQL server in the most convenient, automatic and efficient way possible.
In so far as the backup device resides on the internet and uses our
proprietary compressed and encrypted file format, I guess you could also say
that it's an implementation of a backup device.
You wouldn't happen to know the answer to my other question about SQL-DMO
and ATL style smart pointers?
Thanks,
Peter
"Gert E.R. Drapers" wrote:

> VDI and DMO are two completely different and are targeting two different
> things
> VDI is a virtual device interface for ISV's to develop SQL Server Backup
> software so you can backup you SQL Server database and tlogs to for exampl
e
> compressed files, remote locations by backing up over a pipe etc.
> DMO is a management interface, abstracting programmers to learn or issue
> TSQL commands directly.
> Are you writing an application that tells SQL to perform a BACKUP or are y
ou
> planning on implementing a backup device?
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> You assume all risk for your use.
> Copyright ? SQLDev.Net 1991-2005 All rights reserved.
> "jpstewart" <jpstewart@.discussions.microsoft.com> wrote in message
> news:44B5C821-78C9-4C25-8951-9E4892825397@.microsoft.com...
>
>|||Sounds like a VDI solution to me. I will dig up my ATL example, it is
possible, forgot how I did it though.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"jpstewart" <jpstewart@.discussions.microsoft.com> wrote in message
news:35978FC2-FB22-47DC-A3A7-0E90AC09741D@.microsoft.com...
> Thanks for your reply. I'm writing an application that should be able to
> back
> up SQL server in the most convenient, automatic and efficient way
> possible.
> In so far as the backup device resides on the internet and uses our
> proprietary compressed and encrypted file format, I guess you could also
> say
> that it's an implementation of a backup device.
> You wouldn't happen to know the answer to my other question about SQL-DMO
> and ATL style smart pointers?
> Thanks,
> Peter
> "Gert E.R. Drapers" wrote:
>|||Thanks, I would appreciate it!
-Peter
"Gert E.R. Drapers" wrote:

> Sounds like a VDI solution to me. I will dig up my ATL example, it is
> possible, forgot how I did it though.
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> You assume all risk for your use.
> Copyright ? SQLDev.Net 1991-2005 All rights reserved.
> "jpstewart" <jpstewart@.discussions.microsoft.com> wrote in message
> news:35978FC2-FB22-47DC-A3A7-0E90AC09741D@.microsoft.com...
>
>

Background Color Return:

I am writing a report that I need to return a background color for a cell in
a table. The background color depends on what color is named in another
cell. For example, if the cell (Quality_status.value) displays the word
Green then the cell (Quanity_status.value) background color needs to be
Green. I have tried several IIF expressions in the BackgroundColor
properties but none of them seem to work. Please help!!
--
Sincerely,
Debo
--
Sincerely,
DeboThe only supported property of a field is Value. Therefore you can't do it.
HTH
Charles Kangai, MCT, MCDBA
"Debo" wrote:
> I am writing a report that I need to return a background color for a cell in
> a table. The background color depends on what color is named in another
> cell. For example, if the cell (Quality_status.value) displays the word
> Green then the cell (Quanity_status.value) background color needs to be
> Green. I have tried several IIF expressions in the BackgroundColor
> properties but none of them seem to work. Please help!!
> --
> Sincerely,
> Debo
> --
> Sincerely,
> Debo|||I went to a textbox properties, background color, scrolled to the top to
add an expression. and did
=iif(fields!CompanyName.Value = "Alfred's","Red","Green") and it worked
have you tried
=(fields!Quality_status.Value)
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Debo" <debo@.discussions.microsoft.com> wrote in message
news:835302E4-20F1-4D56-A738-9954ED6B346E@.microsoft.com...
> I am writing a report that I need to return a background color for a cell
in
> a table. The background color depends on what color is named in another
> cell. For example, if the cell (Quality_status.value) displays the word
> Green then the cell (Quanity_status.value) background color needs to be
> Green. I have tried several IIF expressions in the BackgroundColor
> properties but none of them seem to work. Please help!!
> --
> Sincerely,
> Debo
> --
> Sincerely,
> Debo|||Hi Wayne,
I thought Debo was trying to read the BackColor property, and you cannot
read any property of a textbox except Value. Of course if you enter the name
of the color in words in a textbox, then you can do it, because you are
reading the Value property.
Cheers
Charles Kangai, MCT, MCDBA
ps - good to hear from you Wayne!
"Wayne Snyder" wrote:
> I went to a textbox properties, background color, scrolled to the top to
> add an expression. and did
> =iif(fields!CompanyName.Value = "Alfred's","Red","Green") and it worked
> have you tried
> =(fields!Quality_status.Value)
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
>
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Debo" <debo@.discussions.microsoft.com> wrote in message
> news:835302E4-20F1-4D56-A738-9954ED6B346E@.microsoft.com...
> > I am writing a report that I need to return a background color for a cell
> in
> > a table. The background color depends on what color is named in another
> > cell. For example, if the cell (Quality_status.value) displays the word
> > Green then the cell (Quanity_status.value) background color needs to be
> > Green. I have tried several IIF expressions in the BackgroundColor
> > properties but none of them seem to work. Please help!!
> > --
> > Sincerely,
> >
> > Debo
> > --
> > Sincerely,
> >
> > Debo
>
>

Avoiding Temporary Tables

Hello All,
I have a question about how I might avoid using a temporary table in a sql
query. In a query I am writing I am trying to extract data from a table o
n
an existing database via a set of identifier codes (These queries are writte
n
from the Matlab environment and the list of identifiers are easily accesible
in matlab).
The query I am currently using looks something like this...
select t.var1, t.code, t.var2
from pinf t
join
( SELECT distinct s.pcode as code
FROM prc s
WHERE s.code in (74156, 54471) ) -- identifiers 74156, 54471
as prctable on prctable.code = t.code
and '12/06/2005' >= t.dt1 and t.d2 <= '12/06/2005'
with the identifier being 74156, 54471. The problem is there is not a
convenient table with all of the identifiers in it. So the solution above
where I draw from an existing table is not sufficient.
I believe that I can create a temporary table with these identifiers in it,
but the performance of the query slows markedly.
Is there some other way to extract the data with these identifier from the
table pinf without creating a temporary table?
Any help would be greatly appreciated.
Cheers,
Lanny>> ... with the identifier being 74156, 54471.
Is this a set of two identifier values or a single comma separated
identifier value with two numbers in them?
I am not familiar with the matlab environment, but how exactly do you
extract the identifiers to the #temp table? The general alternatives ( which
in many cases may not be sufficient ) are using a view or even a base table
which can have all the required identifiers.
Anith|||It is a set of 2 distinct identifier, and conceptually I would want to look
for many more than 2 (perhaps on the order of thousands) of these distinct
identifiers.
I extract the identifiers via another query that I know functions properly
and will give me the desired identifier. I looked into using a base table
or a view, and did not believe they provided the functionality I needed.
Essentially, I have some list of numbers and I want to extract that list, bu
t
all of the elements of this list do not exist on any distinct table in the
database.
Thanks so much for the help.
"Anith Sen" wrote:

> Is this a set of two identifier values or a single comma separated
> identifier value with two numbers in them?
>
> I am not familiar with the matlab environment, but how exactly do you
> extract the identifiers to the #temp table? The general alternatives ( whi
ch
> in many cases may not be sufficient ) are using a view or even a base tabl
e
> which can have all the required identifiers.
> --
> Anith
>
>|||On Wed, 7 Dec 2005 10:18:03 -0800, Lanny wrote:

>It is a set of 2 distinct identifier, and conceptually I would want to look
>for many more than 2 (perhaps on the order of thousands) of these distinct
>identifiers.
>I extract the identifiers via another query that I know functions properly
>and will give me the desired identifier. I looked into using a base table
>or a view, and did not believe they provided the functionality I needed.
>Essentially, I have some list of numbers and I want to extract that list, b
ut
>all of the elements of this list do not exist on any distinct table in the
>database.
>Thanks so much for the help.
Hi Lanny,
I'm not sure if I understand your requirements completely, but based on
what I do understand, you might find what you need on Erlands site:
http://www.sommarskog.se/arrays-in-sql.html
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Avoid to Write the Schema Name in a Function Call in SQL 2005

Avoid Writing the Schema Name in a Function Call in SQL 2005

Hi, everybody, already I try to certificate my code in Oracle PL/SQL to T-SQL to SQL Server 2005, but I have a little trouble, When I Translate the code with SQL Migration Assistant for Oracle (SSMA), this tool, put the schema name before the function name, that is a good practice, but I can avoid to do that, because, The company have a lot of calls in hard-code, and when I execute the call without the schema name, SQL2005 send me a error message, like say, “is not recognize as built-in function name”.

Somebody knows what I can do to do that?

Thanks

KC:

If the function is a scalar function you must precede the function name with the schema; it is a requirement for the way Transact SQL Works.

Availability of SSIS Run-time support (Redistributable package)?

Can somebody please help answering this question?

I'm planning to consider writing a SSIS package for a new project that requires downloading large chunk of data and transform into the diverse databases such as MS SQL or Oracle depending on the Client's Datbase.

For the clients having SQL Server installed at their end, i had no issues in deploying this package on their server and run it in their licensed instance.

What should be the case for others having Oracle database? Wouldn't installing the SQL 2005 client tools install the necessary run-time services for running SSIS packages? What i understood from the MSDN library (http://msdn2.microsoft.com/en-us/library/ms403355.aspx) is that there's no run-time support available for running the SSIS packages (unlike DTS run-time support) in production environment!

Would that mean that it requires a SQL Standard edition, at minimum, (as Integration Services is OOTB from Standard Edition onwards) to be installed at the production site to run this package?

If so, the client wouldn't be ready (which is fair too) to buy the new license just to run this package. Is there any work-around/suggestions for this case?

If not, can somebody please point me to the right location where i can download the run-time support for running SSIS packages?

Any thoughts on this is highly appreciated.

Thanks for your time.

~Sagar

You're reading of the Help is correct, SSIS does not offer a redistributable like DTS did, and also the licensing has changed. SSIS is a server component, not client, so you need a Server license, not a CAL. Mind you for your Oracle shops, you would always have needed a server as you cannot have CALs without having purchased a server as well.

If you want to install/run SISS you need to but a "SQL Server" license. Choose the edition, Standard is normally fine for most people. I also think it is quite nice you get a relational engine, OLAP server and report server for free as well. Most ETL packages don't include that level of free software with them.

This is a big change from the DTS world, SSIS is a considered a Server now, and licensed as such.

|||

This is strange. Why would anybody (well, most of them at least) go for a full fledged database software with so many OOTB packages (database, SSIS, SSAS, OLAP Server, SSRS etc) for just running few ETL packages?

One hand we use SSIS package to transform the data to different databases and on the other hand we are asked to buy one MS SQL database license in order to execute the package. How justifiable it is to ask the Customer buy a SQL Server license (costing ~$3000 for a server setup) just to run this package? This not only a resource exhaustive but also a maintenace over head to tackle with the software which the customer might not want to..

Isn't it possible just to get a stand-alone install of SSIS (let alone the availability of run-time support for SSIS) if not now, may be in the future?

Regards,

Sagar

|||Take a look at the prices for other ETL software out there. $3000 a server isn't bad at all, even if the only thing you were getting is the ETL tool.|||

All happies If it's just done with 3000$.

But Who would like to share the Production server (on which Oracle database is installed) with MS Sql Server just to run the SSIS packages? It is a maintenance cost overrun to allocate a different server machine just to host the MS SQL license and hence a performance hit due to the cross-machine calls between SSIS Packages and the destination Server (i.e. Oracle database server).

With this background, does it not make sense having a redistributable package from MS (run-time support) for executing SSIS packages that can be installed on Oracle database server(on Production server)?

|||

VidyaSagarCh wrote:

All happies If it's just done with 3000$.

But Who would like to share the Production server (on which Oracle database is installed) with MS Sql Server just to run the SSIS packages? It is a maintenance cost overrun to allocate a different server machine just to host the MS SQL license and hence a performance hit due to the cross-machine calls between SSIS Packages and the destination Server (i.e. Oracle database server).

With this background, does it not make sense having a redistributable package from MS (run-time support) for executing SSIS packages that can be installed on Oracle database server(on Production server)?

IIRC, you do not need to install the SQL Server RDBMS to run SSIS. So the scenario you're describing should work just fine - if you only want to install SSIS on the Oracle box, you should be able to do that.

With that said, if your Oracle DBAs are anything like mine, you will have just as much trouble getting them to let you install SSIS as you would have getting them to let you install the full SQL Server stack.