Convert Access query to sql

how to convert this access query to sql query

IIf([Total Bunches] > 0, Production * 1000 / [Total Bunches], 0) as Name2

SUM(IIf(BlockInYield = -1, [SIZE], 0)) as Name1

IIf(BlockInYield = TRUE, IIf(TC_M > 0, TC_M, TC_DENS *[SIZE]), 0) as Name

please......

case

when [Total Bunches] > 0 then Production * 1000 / [Total Bunches]

else 0

end

as Name2,

SUM(

case

when BlockInYield = -1 then [SIZE]

else 0

end

)

as Name1,

case when BlockInYield = 1 then (case when TC_M > 0 then TC_M else TC_DENS *[SIZE] end)

else 0

end

as Name

Thanks

Naras

|||There is no IFF operator in TSQL; as Naras is indicating the way to approach converting an IFF statement is to use an abstraction using CASE statements.sqlsql

Conversion MSDE to SQL Server 2000

Here is the scoop:

I have MSDE installed on a production machine together with a bunch of ASP.NET applications. I was asked to replace MSDE with SQL Server 2000 and move every application from MSDE to SQL Server 2000 with minimum downtime.

Did any of you go through a similar process?

What is the best way of doing this conversion?

How to move the backups from MSDE to SQL Server?

Is it possible to have MSDE and SQL Server 2000 coexists for a time while moving stuff around and redirecting every connection?

Any hints or suggestions would be greatly appreciated.

Thanks.You could install Sql Server 2000 & use transfer objects (with data) to move the data between the databases.
MSDE is a "lite" version of Sql Server 2000, so you shouldn't need any conversions or transformations.|||Thanks for the answer, I did some research and I found this also, perhaps it will help others as well

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q325023

conversion error after promoting to production box

Problem: data conversion component going from unicode DT_WSTR to DT_STR with 1252 codepage using a Source Provider=IBMDADB2.1;

So.. I developed the package on the testdb without any errors. However, when I promote the package to the production box, I get this conversion error. I thought that it was specific to one column, so I set the ignore truncation error in the data flow. The next column provided then caused the same error.

what settings can I look at changing to prevent this error?

what data is being truncated or is the unicode conversion map failing?

the max(length) is 270 on a varchar 300. I also read something about tabs in this type of conversions failing?

Why would this be specific to the box? I have checked the collation on both DB's and they are SQL_Latin1_General_CP1_CI_AS

what is the normal workaround for this? is AlwaysUseDefaultCodePage a part of the solution?

<Error>The "output column "STATUSRENEWAL" (4477)" failed because truncation occurred, and the truncation row disposition on "output column "STATUSRENEWAL" (4477)" specifies failure on truncation.

</Error>

thanks for your time.

To find out the rows causing this, redirect the failing rows to error output and save it e.g. to a file.
The problem could be caused by Unicode characters in DT_WSTR columns that can not be represented by codepage 1252. The workaround would depend on what you are going to do with this? Are you OK with loss of information - then ignore the error; if not - don't convert Unicode data to single-byte code page; or maybe you need to cleanup the source data to avoid international characters that can't be fit to codepage 1252?sqlsql

Controlling size of database log

Hi,
Our production database log size (9 GB) is 3 times greater than size of the
actual database itself (3GB)!
Is it normal? If not how can I bring the log size down? Can someone let me
know please.
Thanks in advance,
Harish Mohanbabuback it up.
BACKUP LOG databaseName
you can even back it up just to truncate it.
BACKUP LOG databaseName WITH TRUNCATE_ONLY
Greg Jackson
PDX, Oregon|||In addition to pdxjaxon,
you can run DBCC Shrinkfile, to reduce the physical log file size to the
desired target size.
Thanks
Yogish
"pdxJaxon" wrote:

> back it up.
> BACKUP LOG databaseName
>
> you can even back it up just to truncate it.
> BACKUP LOG databaseName WITH TRUNCATE_ONLY
>
> Greg Jackson
> PDX, Oregon
>
>|||And also check the recovery model for the database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Yogish" <yogishkamathg@.icqmail.com> wrote in message
news:DDB0D749-6F74-4673-993E-A5767020BFD6@.microsoft.com...[vbcol=seagreen]
> In addition to pdxjaxon,
> you can run DBCC Shrinkfile, to reduce the physical log file size to the
> desired target size.
> --
> Thanks
> Yogish
> "pdxJaxon" wrote:
>

Controlling size of database log

Hi,
Our production database log size (9 GB) is 3 times greater than size of the
actual database itself (3GB)!
Is it normal? If not how can I bring the log size down? Can someone let me
know please.
Thanks in advance,
Harish Mohanbabu
back it up.
BACKUP LOG databaseName
you can even back it up just to truncate it.
BACKUP LOG databaseName WITH TRUNCATE_ONLY
Greg Jackson
PDX, Oregon
|||In addition to pdxjaxon,
you can run DBCC Shrinkfile, to reduce the physical log file size to the
desired target size.
Thanks
Yogish
"pdxJaxon" wrote:

> back it up.
> BACKUP LOG databaseName
>
> you can even back it up just to truncate it.
> BACKUP LOG databaseName WITH TRUNCATE_ONLY
>
> Greg Jackson
> PDX, Oregon
>
>
|||And also check the recovery model for the database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Yogish" <yogishkamathg@.icqmail.com> wrote in message
news:DDB0D749-6F74-4673-993E-A5767020BFD6@.microsoft.com...[vbcol=seagreen]
> In addition to pdxjaxon,
> you can run DBCC Shrinkfile, to reduce the physical log file size to the
> desired target size.
> --
> Thanks
> Yogish
> "pdxJaxon" wrote:

Controlling size of database log

Hi,
Our production database log size (9 GB) is 3 times greater than size of the
actual database itself (3GB)!
Is it normal? If not how can I bring the log size down? Can someone let me
know please.
Thanks in advance,
Harish Mohanbabuback it up.
BACKUP LOG databaseName
you can even back it up just to truncate it.
BACKUP LOG databaseName WITH TRUNCATE_ONLY
Greg Jackson
PDX, Oregon|||In addition to pdxjaxon,
you can run DBCC Shrinkfile, to reduce the physical log file size to the
desired target size.
--
Thanks
Yogish
"pdxJaxon" wrote:
> back it up.
> BACKUP LOG databaseName
>
> you can even back it up just to truncate it.
> BACKUP LOG databaseName WITH TRUNCATE_ONLY
>
> Greg Jackson
> PDX, Oregon
>
>|||And also check the recovery model for the database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Yogish" <yogishkamathg@.icqmail.com> wrote in message
news:DDB0D749-6F74-4673-993E-A5767020BFD6@.microsoft.com...
> In addition to pdxjaxon,
> you can run DBCC Shrinkfile, to reduce the physical log file size to the
> desired target size.
> --
> Thanks
> Yogish
> "pdxJaxon" wrote:
>> back it up.
>> BACKUP LOG databaseName
>>
>> you can even back it up just to truncate it.
>> BACKUP LOG databaseName WITH TRUNCATE_ONLY
>>
>> Greg Jackson
>> PDX, Oregon
>>

Constructing a Query

Hello all,

I am fairly new to using SQL in a production environment. I've gotten the management studio pretty much figured out and now I am finding the need for some more advanced knowledge. I would like to adjust the database autogrowth settings for each of my databases; however I have no prior statistics to examine to tell me how much the past years worth of being in production has caused the databases to grow.

I have found that I can look at each database in turn and select a basic report of disk usage that happens to include records of the last few autogrowth periods and particulars about each growth. What I would like to do is create a report (The same disk usage report) that includes all of my databases (I have about 30 active databases for MS Dynamics Great Plains.) I figured that I can create a maintenance plan consisting of a transact-SQL function that will run the same query, or a similar query that will report to me: Autogrowth statistics for each database in turn since it's inception (less than a year ago).

Perhaps there is an easier way to achieve the same results. If there is a way to create / run the same disk usage report for multiple databases, and have the results returned in a consolidated form, I have not yet figured out how.

I hope that someone out there has a solution for this; what i'm sure is not a new issue but mearly a newbie issue.

Thank you all in advance for any advice you may have.

Normally, you don't want to use 'Autogrow' on production servers. Autogrow causes excessive file fragmentation, and often occurs at the most inopportune moments (Autogrow is a performance hit.)

You would be best served to determine how much new space will be required over a period of time (week, month, year, etc.) by examining the historic records you have. Then exand the database files sufficiently to allow for all activity for the next period of time.

|||Ok, I can accept that. I've got a maximum of about 8 users working on a very beefy sql box. I have no concern about overall preformance just yet. However I WILL take your advice and remove the autogrowth. I still, however need to collect some pervious growth statistics. My question still remains, is there a way to script a report that will just pull the autogrowth statistics for all of my databases from the time of their creation?|||No, as far as I am aware, that historical information is not available.|||

Ok, Then lets take one step further back.

Since there is already a builtin 'disk usage' report that can be run on a database. Can I use a Transact Sql query to pull that same report (which will contain autogrowth history) for all (or a select number) of databases and filter through it at my leasure?

If so, I'll probably need some help with scripting of said query.

Thank you for your time and advice,

Consolidation of server

We have two production servers and SQL is running on both servers. The
databses in two production srevers has same name. I am in process of
consolidating server. I have to move all the datbases to another sql server
in the other box. But now i have a problem. Since dtabase name is same i am
stuck can any body help me to sort this out.
Thanks ....
You can install a separate named instance on the server, therefore you can
keep the same dbname.
http://msdn.microsoft.com/library/de...stall_259u.asp
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Harsha" <Harsha@.discussions.microsoft.com> schrieb im Newsbeitrag
news:861FD285-C52D-4369-9FFE-419F99028232@.microsoft.com...
> We have two production servers and SQL is running on both servers. The
> databses in two production srevers has same name. I am in process of
> consolidating server. I have to move all the datbases to another sql
> server
> in the other box. But now i have a problem. Since dtabase name is same i
> am
> stuck can any body help me to sort this out.
> Thanks ....
|||Thanks so much jens. I installed it. But i do i switch over between 2
instances. Does the same enterprise manger works.
"Jens Sü?meyer" wrote:

> You can install a separate named instance on the server, therefore you can
> keep the same dbname.
>
> http://msdn.microsoft.com/library/de...stall_259u.asp
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Harsha" <Harsha@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:861FD285-C52D-4369-9FFE-419F99028232@.microsoft.com...
>
>
|||YOu habe to tregister the server within the Enterrise Manager, the
servername of a named instance is
Servername\InstanceName
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Harsha" <Harsha@.discussions.microsoft.com> schrieb im Newsbeitrag
news:509CBA13-244E-4C6C-BC15-1E9789FB2AAE@.microsoft.com...[vbcol=seagreen]
> Thanks so much jens. I installed it. But i do i switch over between 2
> instances. Does the same enterprise manger works.
> "Jens Smeyer" wrote:

Consolidation of server

We have two production servers and SQL is running on both servers. The
databses in two production srevers has same name. I am in process of
consolidating server. I have to move all the datbases to another sql server
in the other box. But now i have a problem. Since dtabase name is same i am
stuck can any body help me to sort this out.
Thanks ....You can install a separate named instance on the server, therefore you can
keep the same dbname.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_install_259u.asp
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Harsha" <Harsha@.discussions.microsoft.com> schrieb im Newsbeitrag
news:861FD285-C52D-4369-9FFE-419F99028232@.microsoft.com...
> We have two production servers and SQL is running on both servers. The
> databses in two production srevers has same name. I am in process of
> consolidating server. I have to move all the datbases to another sql
> server
> in the other box. But now i have a problem. Since dtabase name is same i
> am
> stuck can any body help me to sort this out.
> Thanks ....|||Thanks so much jens. I installed it. But i do i switch over between 2
instances. Does the same enterprise manger works.
"Jens Sü�meyer" wrote:
> You can install a separate named instance on the server, therefore you can
> keep the same dbname.
>
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_install_259u.asp
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Harsha" <Harsha@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:861FD285-C52D-4369-9FFE-419F99028232@.microsoft.com...
> > We have two production servers and SQL is running on both servers. The
> > databses in two production srevers has same name. I am in process of
> > consolidating server. I have to move all the datbases to another sql
> > server
> > in the other box. But now i have a problem. Since dtabase name is same i
> > am
> > stuck can any body help me to sort this out.
> >
> > Thanks ....
>
>|||YOu habe to tregister the server within the Enterrise Manager, the
servername of a named instance is
Servername\InstanceName
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Harsha" <Harsha@.discussions.microsoft.com> schrieb im Newsbeitrag
news:509CBA13-244E-4C6C-BC15-1E9789FB2AAE@.microsoft.com...
> Thanks so much jens. I installed it. But i do i switch over between 2
> instances. Does the same enterprise manger works.
> "Jens Süßmeyer" wrote:
>> You can install a separate named instance on the server, therefore you
>> can
>> keep the same dbname.
>>
>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_install_259u.asp
>> HTH, Jens Suessmeyer.
>> --
>> http://www.sqlserver2005.de
>> --
>>
>> "Harsha" <Harsha@.discussions.microsoft.com> schrieb im Newsbeitrag
>> news:861FD285-C52D-4369-9FFE-419F99028232@.microsoft.com...
>> > We have two production servers and SQL is running on both servers. The
>> > databses in two production srevers has same name. I am in process of
>> > consolidating server. I have to move all the datbases to another sql
>> > server
>> > in the other box. But now i have a problem. Since dtabase name is same
>> > i
>> > am
>> > stuck can any body help me to sort this out.
>> >
>> > Thanks ....
>>

Consolidation of server

We have two production servers and SQL is running on both servers. The
databses in two production srevers has same name. I am in process of
consolidating server. I have to move all the datbases to another sql server
in the other box. But now i have a problem. Since dtabase name is same i am
stuck can any body help me to sort this out.
Thanks ....You can install a separate named instance on the server, therefore you can
keep the same dbname.
ll_259u.asp" target="_blank">http://msdn.microsoft.com/library/d.../>
ll_259u.asp
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Harsha" <Harsha@.discussions.microsoft.com> schrieb im Newsbeitrag
news:861FD285-C52D-4369-9FFE-419F99028232@.microsoft.com...
> We have two production servers and SQL is running on both servers. The
> databses in two production srevers has same name. I am in process of
> consolidating server. I have to move all the datbases to another sql
> server
> in the other box. But now i have a problem. Since dtabase name is same i
> am
> stuck can any body help me to sort this out.
> Thanks ....|||Thanks so much jens. I installed it. But i do i switch over between 2
instances. Does the same enterprise manger works.
"Jens Sü?meyer" wrote:

> You can install a separate named instance on the server, therefore you can
> keep the same dbname.
>
> tall_259u.asp" target="_blank">http://msdn.microsoft.com/library/d...
tall_259u.asp
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Harsha" <Harsha@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:861FD285-C52D-4369-9FFE-419F99028232@.microsoft.com...
>
>|||YOu habe to tregister the server within the Enterrise Manager, the
servername of a named instance is
Servername\InstanceName
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Harsha" <Harsha@.discussions.microsoft.com> schrieb im Newsbeitrag
news:509CBA13-244E-4C6C-BC15-1E9789FB2AAE@.microsoft.com...[vbcol=seagreen]
> Thanks so much jens. I installed it. But i do i switch over between 2
> instances. Does the same enterprise manger works.
> "Jens Smeyer" wrote:
>