Showing posts with label statement. Show all posts
Showing posts with label statement. Show all posts

Tuesday, March 27, 2012

Filter by "all" or actual value issue

I have a string parameter bound to a dataset that contains a "UNION Select
'(All)' " statement to provide the user a way to return all values instead of
just a single value.
I have other integer parameters that use this exact syntax for filtering
(ALL) and they work fine. The syntax looks like this and it doesn't work and
I have no idea why:
Expression: =Iif( Parameters!PropCode.Label = "All", True,
Fields!PropertyCode.Value = Parameters!PropCode.Label)
Operation: = Value: =True
Maybe it has to do with the "(" character? Maybe it has to do with it being
a string? Please advise how I might get past this issue.Did you try:
Iif( Parameters!PropCode.Label = "(All)", True,
Fields!PropertyCode.Value = Parameters!PropCode.Label)
'|||Yes. That was a typo. That is actually what the filter looks like. This
doesn't work.
"Jihong.Liu" wrote:
> Did you try:
> Iif( Parameters!PropCode.Label = "(All)", True,
> Fields!PropertyCode.Value = Parameters!PropCode.Label)
> '
>sql

Filling out gaps in a date based select statement

Hi,
I have a query which simply returns a count of a particular field by w
number. My query as it works fine however for ws where there are no data
it obviously doesn't return a row. My problem is that is I wish for the
statement to return a zero for those ws with no data - I'm just not sure
how to do this.
I realise that I can achieve it by creating a table listing w numbers and
joining my query on that table. However I'd rather not create a table like
that. Is there another approach that will let me achieve the same thing?
Currently my query is:
SELECT datepart(wk, datein) AS Wno , COUNT(movementno) AS ContainersIn
STOCK stock
WHERE year(datein) = '2005'
GROUP BY datepart(wk, datein)
which returns:
W ContainersIn
29 3
31 1
34 5
35 4
36 8
37 6
38 3
39 1
40 12
Thanks,
Chris.See if this helps you
http://weblogs.sqlteam.com/jeffs/ar...09/12/7755.aspx
Madhivanan|||Why don't you want to create a table for this? I would recommend you
keep an auxiliary numbers table (a table integers from 0 to some
arbitrarily large number) for exactly such an application. A Calendar
table is also very useful to have around.
A SELECT statement can't create data out of nothing. Some other options
are: use a derived table in your query with a UNION of the numbers 1 -
52 ; create a view containing those numbers ; create a table-valued
function that returns a parameterized range of numbers ; extract the
numbers from system table(s) (not recommended). Creating the permanent
table would be my favourite - it's fast, easy, portable and much more
concise and transparent than any of the others.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1128332658.694445.44410@.g47g2000cwa.googlegroups.com...
> Why don't you want to create a table for this? I would recommend you
> keep an auxiliary numbers table (a table integers from 0 to some
> arbitrarily large number) for exactly such an application. A Calendar
> table is also very useful to have around.
> A SELECT statement can't create data out of nothing. Some other options
> are: use a derived table in your query with a UNION of the numbers 1 -
> 52 ; create a view containing those numbers ; create a table-valued
> function that returns a parameterized range of numbers ; extract the
> numbers from system table(s) (not recommended). Creating the permanent
> table would be my favourite - it's fast, easy, portable and much more
> concise and transparent than any of the others.
> --
> David Portas
> SQL Server MVP
> --
>
Thank your for your help David - most appreciated. I guess I find the idea
of creating a new table "messy". No idea why - just something that has
always struck me as something to avoid. A habit I will have to get out of!
thanks again
Chris.

Monday, March 26, 2012

Fillfactor

I have a very simple insert statement into a table containing about a
million rows.
It runs a fraction of a second for about 1000 times and then takes about 5
seconds, and then again fraction of a second for about 1000 inserts. There
are 3 indexes on the table with fillfactor 70.
Am i right by guessing that at the time when insert takes 5 seconds the page
splits? Should i decrese fillfactor to 50? Or could it be something else?
Thanks.
Perhaps there are other possible sources, but it would more likely be your
log files or data files growing. I suppose the quick and dirty check is to
simply monitor both files before and after to see if growth occured.
Autogrowth is evil in my mind.
A single page split is taking a 8K allocated space and splitting it in two
locations. While you don't want this to happen a ton, this is not a task
that would take 5 full seconds, or at least the stall would not be after
record 997 was inserted. I suppose if the records you are inserting are all
being inserted into the SAME part(page) of the index, this could be
problematic.
If the index is related to your performance issues, you could remove the
index prior to the insert, insert, and then rebuild the indexes? This is an
expensive operation too though.
Mark
"Michael Kansky" <mike@.zazasoftware.com> wrote in message
news:%23zHWWj$mIHA.5704@.TK2MSFTNGP05.phx.gbl...
>I have a very simple insert statement into a table containing about a
>million rows.
> It runs a fraction of a second for about 1000 times and then takes about 5
> seconds, and then again fraction of a second for about 1000 inserts. There
> are 3 indexes on the table with fillfactor 70.
> Am i right by guessing that at the time when insert takes 5 seconds the
> page splits? Should i decrese fillfactor to 50? Or could it be something
> else?
> Thanks.
>
|||1) you can monitor page splits using a perf mon counter. I doubt very much
this is the issue
2) the issue could be log flushes, checkpoints, blocking, something random
hitting the server hard. examine waiting tasks (not sure which version you
are on so can't specify exactly how to do that) when the delay occurs. also
run sp_who2 active to check for blocking.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Michael Kansky" <mike@.zazasoftware.com> wrote in message
news:%23zHWWj$mIHA.5704@.TK2MSFTNGP05.phx.gbl...
>I have a very simple insert statement into a table containing about a
>million rows.
> It runs a fraction of a second for about 1000 times and then takes about 5
> seconds, and then again fraction of a second for about 1000 inserts. There
> are 3 indexes on the table with fillfactor 70.
> Am i right by guessing that at the time when insert takes 5 seconds the
> page splits? Should i decrese fillfactor to 50? Or could it be something
> else?
> Thanks.
>
|||I agree with the others that it is probably not related to the fill factor.
I would guess blocking first, disk queues next (checkpoints etc.) and maybe
even file auto growths. Make sure there is plenty of free space in the data
and log files.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Michael Kansky" <mike@.zazasoftware.com> wrote in message
news:%23zHWWj$mIHA.5704@.TK2MSFTNGP05.phx.gbl...
>I have a very simple insert statement into a table containing about a
>million rows.
> It runs a fraction of a second for about 1000 times and then takes about 5
> seconds, and then again fraction of a second for about 1000 inserts. There
> are 3 indexes on the table with fillfactor 70.
> Am i right by guessing that at the time when insert takes 5 seconds the
> page splits? Should i decrese fillfactor to 50? Or could it be something
> else?
> Thanks.
>
|||I see that i have a lot of checkpoints by running a trace.
This might be my problem
How do i minimize checkpoints?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:u4zPw5AnIHA.4504@.TK2MSFTNGP06.phx.gbl...
>I agree with the others that it is probably not related to the fill factor.
>I would guess blocking first, disk queues next (checkpoints etc.) and maybe
>even file auto growths. Make sure there is plenty of free space in the data
>and log files.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Michael Kansky" <mike@.zazasoftware.com> wrote in message
> news:%23zHWWj$mIHA.5704@.TK2MSFTNGP05.phx.gbl...
>
|||Checkpoints are there for a reason which is to limit recovery times in the
event of a crash. There is a setting called the recovery interval that can
affect when checkpoints happen but that is not the solution. If checkpoints
hinder the activity that much you do not have proper disk configurations to
handle the load. It sounds like you probably have your tran log files on the
same drive array as the data files. To deal with lots of writes it is
imperative you separate the logs from the data files onto different physical
(not just logical) arrays. Also consider adding more write cache and check
the read / write ratio of the disk controller. If it is not 100% write back
then change it and you will most likely see improvements with checkpoints.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Michael Kansky" <mike@.zazasoftware.com> wrote in message
news:uo901MBnIHA.4712@.TK2MSFTNGP04.phx.gbl...
>I see that i have a lot of checkpoints by running a trace.
> This might be my problem
> How do i minimize checkpoints?
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:u4zPw5AnIHA.4504@.TK2MSFTNGP06.phx.gbl...
>

Fillfactor

I have a very simple insert statement into a table containing about a
million rows.
It runs a fraction of a second for about 1000 times and then takes about 5
seconds, and then again fraction of a second for about 1000 inserts. There
are 3 indexes on the table with fillfactor 70.
Am i right by guessing that at the time when insert takes 5 seconds the page
splits? Should i decrese fillfactor to 50? Or could it be something else?
Thanks.Perhaps there are other possible sources, but it would more likely be your
log files or data files growing. I suppose the quick and dirty check is to
simply monitor both files before and after to see if growth occured.
Autogrowth is evil in my mind.
A single page split is taking a 8K allocated space and splitting it in two
locations. While you don't want this to happen a ton, this is not a task
that would take 5 full seconds, or at least the stall would not be after
record 997 was inserted. I suppose if the records you are inserting are all
being inserted into the SAME part(page) of the index, this could be
problematic.
If the index is related to your performance issues, you could remove the
index prior to the insert, insert, and then rebuild the indexes? This is an
expensive operation too though.
Mark
"Michael Kansky" <mike@.zazasoftware.com> wrote in message
news:%23zHWWj$mIHA.5704@.TK2MSFTNGP05.phx.gbl...
>I have a very simple insert statement into a table containing about a
>million rows.
> It runs a fraction of a second for about 1000 times and then takes about 5
> seconds, and then again fraction of a second for about 1000 inserts. There
> are 3 indexes on the table with fillfactor 70.
> Am i right by guessing that at the time when insert takes 5 seconds the
> page splits? Should i decrese fillfactor to 50? Or could it be something
> else?
> Thanks.
>|||1) you can monitor page splits using a perf mon counter. I doubt very much
this is the issue
2) the issue could be log flushes, checkpoints, blocking, something random
hitting the server hard. examine waiting tasks (not sure which version you
are on so can't specify exactly how to do that) when the delay occurs. also
run sp_who2 active to check for blocking.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Michael Kansky" <mike@.zazasoftware.com> wrote in message
news:%23zHWWj$mIHA.5704@.TK2MSFTNGP05.phx.gbl...
>I have a very simple insert statement into a table containing about a
>million rows.
> It runs a fraction of a second for about 1000 times and then takes about 5
> seconds, and then again fraction of a second for about 1000 inserts. There
> are 3 indexes on the table with fillfactor 70.
> Am i right by guessing that at the time when insert takes 5 seconds the
> page splits? Should i decrese fillfactor to 50? Or could it be something
> else?
> Thanks.
>|||I agree with the others that it is probably not related to the fill factor.
I would guess blocking first, disk queues next (checkpoints etc.) and maybe
even file auto growths. Make sure there is plenty of free space in the data
and log files.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Michael Kansky" <mike@.zazasoftware.com> wrote in message
news:%23zHWWj$mIHA.5704@.TK2MSFTNGP05.phx.gbl...
>I have a very simple insert statement into a table containing about a
>million rows.
> It runs a fraction of a second for about 1000 times and then takes about 5
> seconds, and then again fraction of a second for about 1000 inserts. There
> are 3 indexes on the table with fillfactor 70.
> Am i right by guessing that at the time when insert takes 5 seconds the
> page splits? Should i decrese fillfactor to 50? Or could it be something
> else?
> Thanks.
>|||I see that i have a lot of checkpoints by running a trace.
This might be my problem
How do i minimize checkpoints?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:u4zPw5AnIHA.4504@.TK2MSFTNGP06.phx.gbl...
>I agree with the others that it is probably not related to the fill factor.
>I would guess blocking first, disk queues next (checkpoints etc.) and maybe
>even file auto growths. Make sure there is plenty of free space in the data
>and log files.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Michael Kansky" <mike@.zazasoftware.com> wrote in message
> news:%23zHWWj$mIHA.5704@.TK2MSFTNGP05.phx.gbl...
>>I have a very simple insert statement into a table containing about a
>>million rows.
>> It runs a fraction of a second for about 1000 times and then takes about
>> 5 seconds, and then again fraction of a second for about 1000 inserts.
>> There are 3 indexes on the table with fillfactor 70.
>> Am i right by guessing that at the time when insert takes 5 seconds the
>> page splits? Should i decrese fillfactor to 50? Or could it be something
>> else?
>> Thanks.
>|||Checkpoints are there for a reason which is to limit recovery times in the
event of a crash. There is a setting called the recovery interval that can
affect when checkpoints happen but that is not the solution. If checkpoints
hinder the activity that much you do not have proper disk configurations to
handle the load. It sounds like you probably have your tran log files on the
same drive array as the data files. To deal with lots of writes it is
imperative you separate the logs from the data files onto different physical
(not just logical) arrays. Also consider adding more write cache and check
the read / write ratio of the disk controller. If it is not 100% write back
then change it and you will most likely see improvements with checkpoints.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Michael Kansky" <mike@.zazasoftware.com> wrote in message
news:uo901MBnIHA.4712@.TK2MSFTNGP04.phx.gbl...
>I see that i have a lot of checkpoints by running a trace.
> This might be my problem
> How do i minimize checkpoints?
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:u4zPw5AnIHA.4504@.TK2MSFTNGP06.phx.gbl...
>>I agree with the others that it is probably not related to the fill
>>factor. I would guess blocking first, disk queues next (checkpoints etc.)
>>and maybe even file auto growths. Make sure there is plenty of free space
>>in the data and log files.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Michael Kansky" <mike@.zazasoftware.com> wrote in message
>> news:%23zHWWj$mIHA.5704@.TK2MSFTNGP05.phx.gbl...
>>I have a very simple insert statement into a table containing about a
>>million rows.
>> It runs a fraction of a second for about 1000 times and then takes about
>> 5 seconds, and then again fraction of a second for about 1000 inserts.
>> There are 3 indexes on the table with fillfactor 70.
>> Am i right by guessing that at the time when insert takes 5 seconds the
>> page splits? Should i decrese fillfactor to 50? Or could it be something
>> else?
>> Thanks.
>>
>

Wednesday, March 21, 2012

FILENAME option in CREATE DATABASE statement

Hi,

I have a problem with CREATE DATABASE statement, since it needs to specify the absolut path for a file in the FILENAME= option. I would need instead a relative path, i.e. only the name of the file because the script that launch this SQL command is dependant on the installation path decided by the user, during the installation.
Is there a way to pass to the FILENAME only the name of the file?
Hope to have been clear :-)

Thanks in advanceTry KB article 307283:How To Create a SQL Server Database Programmatically by Using ADO.NET and Visual C# .NET

Terri|||Hi, Terry

I have created a batch file that run the script sql for the creating the database, but in the option FILENAME if the path does not exist on the clients PC - the script will fail.

Any suggestions appreciated.
Thanks.
Fabio.|||I'm really sorry, I missed the "relative path" part of your question. As far as I know SQL Server cannot use a relative path for the filename.

I wonder if you could make use of one of the system or extended stored procedures sp_helpfile, master..xp_getfiledetails, sp_helpdevice?? Or perhaps use the undocumented stored procedure XP_REGREAD to read the installation path out of the registry?

Terri|||Hello,

I have exactly the same question.

I've got a ASP.NET application, where I want to automate the creation of the database for the Webmaster (once they fill in the appropriate SQL Server/MSDE Database Server location and login info, etc...).

My dynamically written CREATE DATABASE script wants a full absolute filepath for the Data and Log files.

And obviously I need to take into account the fact the Database Server may not be local to the Webserver.

I can't seem to find any easy way to find this information, other than to do a query of the "Master" database, like so:

--------
USE [Master]
SELECT filename
FROM sysfiles
WHERE (name = 'master')
--------

The above returns a value like "C:\Data\MSSQL\data\master.mdf".

From this I could easily extract just the directory path portion.

Am I crazy? Is this reliable?

Or is there a better way to get this information?

Any insight would be appreciated....

Friday, March 9, 2012

FileGroup

i understand that the filegroup for a table can be specified in the CREATE Table statement. just out of curiosity, is there any way to move a table to a different filegroup, that too, if the table contains data.i understand that the filegroup for a table can be specified in the CREATE Table statement. just out of curiosity, is there any way to move a table to a different filegroup, that too, if the table contains data.

The only way that I know how to do this is to create a new table, move the data from your existing table to the new table, drop the old table and then rename the new table.

Regards,

hmscott|||Create (or recreate) a clustered index on the new filegroup. Once you've done that, recreate any indicies for that table on the new filegroup too. If you've created a clustered index where none existed (just to move the data), you should then drop that index.

-PatP

Wednesday, March 7, 2012

File system error in created local cubes

Hi everyone,

I have created a stored procedure in SQL 2005 which will create (sliced) local cubes using the CREATE GLOBAL CUBE (MDX) statement. This procedure will be started from a SSIS package which is deployed on the same server. A SQL server login with sysadmin permissions can start this SSIS package and create multiple (sliced) off line cubes on the file system.

Everything seems to work fine and no errors where raised during execution. However when I try to open the .cub file in ProClarity 6.2, or even Excel, I get the following message. "File System Error: An error occurred while opening the <file path and name> local cube file."

The strange thing is...., whenever I exectue this package using a domain account with admin permission, the cube file turns out fine.

Furthermore the same package works just fine in our test enviroment. (32 bit virtual server)

The machine that is causing these symptoms is a 64 bit server. Logically I suspected the difference between these enviroments could be a cause. I have tried to narrow the possibilities down to just the creation of off line cubes (and SSAS).

Could security be the cause of this problem? If so, what to do?

Have you checked the file permissions on the .cub file? Try explicitly granting access to the user account that you are running ProClarity or Excel under.|||

Also make sure when you try and open local cube you got correct version of AS OLEDB provider installed.

You can get it from http://www.microsoft.com/downloads/details.aspx?familyid=50b97994-8453-4998-8226-fa42ec403d17&displaylang=en

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi everyone,

Thanks a lot for your replies. I have found the cause of the problem. As stated before we use a stored procedure to execute a package. However on a 64 bit server command "dtexec" has two versions. One for 32 bit which is installes on the folder "Program Files (x86)" and a 64 bit version which is installed on folder "Program Files". Is T-SQL adresses dtexec from a stored procedure it will by default use the 64 bit version. This was the cause of the problem and not security or Analysis Services.

To solve this, please execute the "dtexec" by using the full path ("Program Files (x86)") and referring to the version you want to use.

For this reason, this post is not directly related to Analysis Services. Appologies for any inconvenience.

With kind regards,

Wan Chi