Data files from Database Object

The database object contains a member 'get_LogFiles' that returns a nice collection of the database's logfiles. I can't seem to find the comparable member to return the collection of datafiles. If someone can point me in the right direction, I would be most grateful

That's because log files do not reside in filegroups.

Check out the filegroup property, this contains a collection of data files.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

sql

data conversion - numeric to string loosing precision

Hi All,

i'm using a "data conversion" object to convert a numeric field to a string just before i save the record set to the database.

the problem is when this numeric field is > 0 it looses the precision on its decimal value.

example, if numeric value is 0.32

after converting this to a string, the new value will be : .32

it's lost the 0 infront of it. i can't do this converion in the query level because it's a derived field, so i need to convert it to a string before stroing it.

when converting to string i'm using the code page 1252 (ANSI - Latin I). i also tried with unicode string both looses this 0 infront.

can any one help on this?

Is your ultimate database target for the numeric data type a character based column, hence the need to retain the leading zeros?

If that is the case, retaining leading zeros can be accomplished with a derived column transform, rather than a data conversion transform. There you have access to the SSIS expression language, with a relatively standard, if meager, set of string functions, as well as type casts.

Another alternative for pretty much an data type conversion is a Script Transform, which will afford you the full power of the .NET framework, including in this case custom numeric formats for types destinated for strings.|||

Hi jaegd,

i tried that, but no luck. i use a derived table and this is my expression

ISNULL(investmentPercentage) ? 0.00 : investmentPercentage

here investmentPercentage is a numeric (15,2). i add the above expression as a new column (string) (8 chars). but still it looses the 0

.00 when it's null

.32 when it;s 0.32

any comments

AJ

|||

Use the Script Task, and follow the instructions in this link for numeric format specifiers for strings. You need to do something similar to this...

Dim number as float

number.ToString("D")

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconstandardnumericformatstrings.asp

|||

In the derived column, you can still acheive it with conditional logic The script as mentioned above is cleaner, but if you want to avoid adding that, you can try an expression like:

(investmentPercentage < 1 ? "0" : "") + (DT_WSTR, 20)investmentPercentage

This expression will add a "0" to the beginning of the string if the number is less than one (which is the only time the zero is dropped). When the number is greater than or equal to 1, an empty string is added.

NOTE: this does not take into account negative values. If investmentPercentage can be negative, you will need to tweak the expression a little (or post here, and I can help with that).

Thanks
Mark

|||

Hi Mark,

thanks for that, but it sort of half answer my question.

yes investmentPercentage can be negative or can even be null. i was wondering if there's an eaisier way. i've got a lot of cloumns like this, writing an expression for each of them this long can be tedious. is this how you would "tweak" it?

ISNULL(investmentPercentage) ? "0.00" : (investmentPercentage < 1 && investmentPercentage >-1 ? "0" : "") + (DT_WSTR, 8)investmentPercentage

|||

You might find it easier to use string formatting in the script component... I can't think of any other way that would be better.

As for tweaking the expression, you will have to do some extra things to take care of the negative sign (so the prepended zero doesn't end up before the negative sign). The following expression adds a case for values between -1 and 0 to handle that:

ISNULL(investmentPercentage) ? (DT_WSTR,8)"0.00" : investmentPercentage < 0 && investmentPercentage > -1 ? "-0" + SUBSTRING((DT_WSTR,8)investmentPercentage, 2, 8) : ((investmentPercentage >= 0 && investmentPercentage < 1 ? "0" : "") + (DT_WSTR,8)investmentPercentage

Let me know if this does the trick for you.

Thanks
Mark

Data changes in SQLExpress not committed?

Hello,

I am developing an application with a SQLExpress database. The database contains a very simple table, and I have added a Dataset object to the solution, and generated a plain Adapter object in the Dataset (using the standard VS wizard capabilities) to operate on one of the tables in de database. Just plain SELECT, INSERT operations, etc.

Strange thing is, the data changes caused by the adapter's generated Insert command (which will call the INSERT statement on the database) are only visible while the application runs (or so it seems). For example:

- I start off with the table containing 2 records, and start debugging
- perform a COUNT within the code: 2 records
- call the Insert function once from code
- perform a COUNT within the code: 3 records
- stop debugging
- inspect the table: the 3rd record doesn't exist, just the 2 records

So, it seems that the changes don't get committed, although I am not sure it is a transaction/commit problem. I haven't been able to figure out what I can do to fix this. I have used adapters before on a SQL database, no problems there. Any comments appreciated.

Cheers, JP

Try to call SqlDataAdapter.Update method after inserting records Please take a look at this link:

http://msdn2.microsoft.com/en-us/library/33y2221y(d=ide).aspx

DAC not supported.

SQL2K5
SP1
Howdy all. I'm trying to open up Dedicated Admins Connection in Management
Studio and getting "DAC's are not supported. (Object Explorer)"
Any idea's?
TIA, ChrisRYou may need to enable the sp_configure option 'remote admin connections'.
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:E3F6FEC3-CFEC-4494-9742-B6AC1BA02242@.microsoft.com...
> SQL2K5
> SP1
> Howdy all. I'm trying to open up Dedicated Admins Connection in Management
> Studio and getting "DAC's are not supported. (Object Explorer)"
> Any idea's?
> TIA, ChrisR|||DAC is not supported for the object explorer, only for the query window.
--
HTH
Kalen Delaney, SQL Server MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:E3F6FEC3-CFEC-4494-9742-B6AC1BA02242@.microsoft.com...
> SQL2K5
> SP1
> Howdy all. I'm trying to open up Dedicated Admins Connection in Management
> Studio and getting "DAC's are not supported. (Object Explorer)"
> Any idea's?
> TIA, ChrisR|||Hi,
you may check my screencasts on my site, one topic is about enabling
DAC.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||Thanks Kalen, that was it.
"Kalen Delaney" wrote:
> DAC is not supported for the object explorer, only for the query window.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:E3F6FEC3-CFEC-4494-9742-B6AC1BA02242@.microsoft.com...
> > SQL2K5
> > SP1
> >
> > Howdy all. I'm trying to open up Dedicated Admins Connection in Management
> > Studio and getting "DAC's are not supported. (Object Explorer)"
> >
> > Any idea's?
> >
> > TIA, ChrisR
>
>

DAC not supported.

SQL2K5
SP1
Howdy all. I'm trying to open up Dedicated Admins Connection in Management Studio and getting "DAC's are not supported. (Object Explorer)"
Any idea's?
TIA, cfrBy default, the DAC is only available via the SQLCMD command line utility. I think it can be opened up to other things, but MS figured that ocule be a security risk.|||Got it. It cant be used through MGMT Studio, only a query window.

Thanks.

DAC not supported.

SQL2K5
SP1
Howdy all. I'm trying to open up Dedicated Admins Connection in Management
Studio and getting "DAC's are not supported. (Object Explorer)"
Any idea's?
TIA, ChrisRYou may need to enable the sp_configure option 'remote admin connections'.
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:E3F6FEC3-CFEC-4494-9742-B6AC1BA02242@.microsoft.com...
> SQL2K5
> SP1
> Howdy all. I'm trying to open up Dedicated Admins Connection in Management
> Studio and getting "DAC's are not supported. (Object Explorer)"
> Any idea's?
> TIA, ChrisR|||DAC is not supported for the object explorer, only for the query window.
HTH
Kalen Delaney, SQL Server MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:E3F6FEC3-CFEC-4494-9742-B6AC1BA02242@.microsoft.com...
> SQL2K5
> SP1
> Howdy all. I'm trying to open up Dedicated Admins Connection in Management
> Studio and getting "DAC's are not supported. (Object Explorer)"
> Any idea's?
> TIA, ChrisR|||Hi,
you may check my screencasts on my site, one topic is about enabling
DAC.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Thanks Kalen, that was it.
"Kalen Delaney" wrote:

> DAC is not supported for the object explorer, only for the query window.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:E3F6FEC3-CFEC-4494-9742-B6AC1BA02242@.microsoft.com...
>
>