DSN Problems

I have a SQL Server 2000 system dsn that uses SQL Server authentication.
The DSN does not retain the password field entry I make. I enter the
password (the sql login is already there), click next, next then ok.
When I reopen the DSN, the password table is blank again. Help
appreciated.

Thanks,
Frank

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!"Frank Py" <fpy@.proactnet.com> wrote in message
news:3fd614b8$0$196$75868355@.news.frii.net...
> I have a SQL Server 2000 system dsn that uses SQL Server authentication.
> The DSN does not retain the password field entry I make. I enter the
> password (the sql login is already there), click next, next then ok.
> When I reopen the DSN, the password table is blank again. Help
> appreciated.
> Thanks,
> Frank
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Passwords are not stored in DSNs, as it would be very insecure. You will
need to specify the password (and perhaps username - I'm not sure) at
connection time. This is one good argument for using Windows authentication,
if that's possible in your case. If not, you will need to investigate how to
modify your application to request a username/password from the user.

One other possibility would be to use some sort of middle tier, which would
use a single DSN for all connections to SQL Server, but this may not be
practical or desirable, depending on your application setup.

Simon|||Yes, I can use Windows Authentication. The person connecting to the DSN
is an administrator. So if I use Windows Authentication instead on this
DSN, would that be more transparent to the user? The user, who is an
administrator needs to run a Crystal Report that is connected to the DSN
without being prompted for a username and password each time. Help
appreciated. Thanks.

Frank

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Frank Py <fpy@.proactnet.com> wrote in message news:<3fd635b0$0$198$75868355@.news.frii.net>...
> Yes, I can use Windows Authentication. The person connecting to the DSN
> is an administrator. So if I use Windows Authentication instead on this
> DSN, would that be more transparent to the user? The user, who is an
> administrator needs to run a Crystal Report that is connected to the DSN
> without being prompted for a username and password each time. Help
> appreciated. Thanks.
> Frank
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Yes - using Windows authentication means that no username and password
should be required, in the same way as connecting with Query Analyzer
or OSQL, for example.

Simon

Dropping and re-creating an index kills performance

Hello all,
I'm new to some of the index operations, so hopefully this is a trivial
question. I attempted to increase the length of a database field and
got the message that an index that included the field would go above
900 bytes, and it failed. That's a large index to begin with, so my
boss thought we should delete the index and see if we took a
performance hit. The first execution of a relevant stored procedure
after the delete actually yielded faster results. However, every
attempt thereafter tanked. No big deal, right? Just re-add the index. I
did, just as it existed previously, and there was no change in the poor
performance. I get the same results with or without the index. Can
anyone tell me what might be causing this and what I can do to get the
index to behave as before?
Thanks in advance,
Shannon
in the QA run the Estimated Execution Plan and see if your query is
using the index. if its not you can always manually tell it to.
|||Shannon Cayze wrote:
> so my boss thought we should delete the index and see if we
> took a performance hit.
This was the first mistake. I hope you made the case that blindly
removing a production index and hoping for the best was not the proper
protocol. At the very least an audit of all queries that used that
column in the index should have been undertaken to see if it's even
used, and if so, how. And all this should have been performed on a test
server, not in production. Scary.
Secondly, it would really help here to see the before and after
execution plans for those queries that were affected by the the index
removal and subsequent re-add. That would require that you first
examined existing queries to see how they were using the index and
compare that to how the queries are using the index now. Since you have
no baseline, there's no way to tell what changed. Are you sure the index
was re-created with the same parameters (clustered, non-clustered,
unique, fill-factor, etc.)?
It's possible the procedure needs to be recompiled. You can use
sp_recompile [ @.objname = ] 'object' to do this.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||First of all, I never said it was production. In fact, it was the test
environment. Secondly, I do have the baseline execution plan and the
results of subsequent queries after altering the index. I didn't
include them because I wanted to keep the posting short and all I
wanted was a few general ideas on how to optimize an index when deleted
and re-added. However, thank you for the sp_recompile hint. Hopefully,
it will help.
|||Shannon Cayze wrote:
> First of all, I never said it was production. In fact, it was the test
> environment. Secondly, I do have the baseline execution plan and the
> results of subsequent queries after altering the index. I didn't
> include them because I wanted to keep the posting short and all I
> wanted was a few general ideas on how to optimize an index when
> deleted and re-added. However, thank you for the sp_recompile hint.
> Hopefully, it will help.
Post the baseline and new execution plans so we can see the differences.
Also post the query in question. I'm guessing a recompile will do it,
but if not, post all the related information and we'll see if we can
find a solution for you.
David Gugick
Quest Software
www.imceda.com
www.quest.com

DropDownList instead of Textbox in Reporting Services

Hello,
I have a report that uses a stored procedure. The parameter from the stored procedure is a name field defined nvarchar(50). But I have a list of values for this field in another table; is there anyway to use the values from the other table to populate a dropdownlist of these values, instead of prompting the user for a textbox?
Thanks.Hi ,
for populating drop down list instead of default text box, you have to create another dataset which will fetch fields from another table.
while in layout, go to Report --> Report Parameters..
click on the parameter on left and on right side, select radio btn From query and specify dataset and select fields.
you can also select default value also. i think this is what u want right?
Thanks
Rohit
|||

Hey,

Yes, that is exactly what I was looking to do. Thanks a lot.

|||That's good for designing reports on the server (RDL). I'm using Visual Web Developer Express and cliking on Report-Report Parameters it seems there's no option for configuring parameters to come from a dataset. Is there a way around this?|||No takers so far. So let me re-phrase the question. Is there any way by which I can move parametrized reports from the server (RDL) to the client (RDLC)?|||Sure you can. You need to use the parameter objects to pass parameters to the report.

DropDownList inside CreateUserWizard

I am using the createuserwizard, and have set up my own profile table which hold first name, last etc. In my CreateUserWizard, the field for Vendor is a dropdownlist that is to be populated from a list in a database. I set up the form without the createuserwizard, and everything went skippy, but when I placed it into the wizard, I now cannot access the control of the dropdownlist.

I have tried doing..

Using SqlConnectionAsNew SqlConnection(WebConfigurationManager.ConnectionStrings("Personal").ConnectionString)
Dim VendorID As DropDownList
VendorID = CType(CreateUserWizard1.FindControl("VendorName"), DropDownList)

Dim MyReaderAs SqlDataReader
Dim selAsString =String.Format("SELECT VendorID, Name FROM Vendors")
Dim MyCommandAs SqlCommand
MyCommand =New SqlCommand
MyCommand.CommandText = sel
MyCommand.CommandType = CommandType.Text
MyCommand.Connection = SqlConnection
MyCommand.Connection.Open()
MyReader = MyCommand.ExecuteReader(CommandBehavior.CloseConnection)
But when I go to databind, it says:
Object reference not set to an instance of an object.
VendorID.DataSource = MyReader
Line 30: VendorID.DataValueField = "VendorID"
Line 31: VendorID.DataTextField = "Name"

You actually have not instantiated your data reader. Try this:

Dim MyReaderAsNewSqlDataReader|||

Sorry, I left that line out of my post, but it was in there. I found the solution to my problem though.

I was originally putting in CreateUserWizard1.FindControl("VendorID"), DropDownList)
THe actual code that worked is...
CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("VendorID"), DropDownList)
Works a charm.
It's always a bugger not knowing these little "tricks"!!
Mick

|||

mickyjtwin:

Sorry, I left that line out of my post, but it was in there. I found the solution to my problem though.

I'm glad you found the solution. In the future, please be sure to copy-and-paste your exact code, not an approximation of it. Otherwise others (such as myself) will spend needless time trying to help you to debug problems that don't exist.

Drop-down in DTS Package cut off

When working in DTS, specifically in the Transform Data Properties window,
when I click on the drop-down in the Table/View field the path will only
extend for the width of the field, even if the path name is too long to fit
in that field. As a result, I often can't see the full path displayed.
Thanks.JKKUS wrote:
> When working in DTS, specifically in the Transform Data Properties window,
> when I click on the drop-down in the Table/View field the path will only
> extend for the width of the field, even if the path name is too long to fi
t
> in that field. As a result, I often can't see the full path displayed.
> Thanks.
Hi,
Is it possible to place the cursor in the field and then use the 'End'
and 'Home' buttons to navigate?
James|||No, that will only scroll vertically up and down the drop-down list, but not
horizontally.
"James Wilson" wrote:

> JKKUS wrote:
> Hi,
> Is it possible to place the cursor in the field and then use the 'End'
> and 'Home' buttons to navigate?
> James
>|||This did not yet resolve my issue.
"James Wilson" wrote:

> JKKUS wrote:
> Hi,
> Is it possible to place the cursor in the field and then use the 'End'
> and 'Home' buttons to navigate?
> James
>sql

Drop-down in DTS Package cut off

When working in DTS, specifically in the Transform Data Properties window,
when I click on the drop-down in the Table/View field the path will only
extend for the width of the field, even if the path name is too long to fit
in that field. As a result, I often can't see the full path displayed.
Thanks.JKKUS wrote:
> When working in DTS, specifically in the Transform Data Properties window,
> when I click on the drop-down in the Table/View field the path will only
> extend for the width of the field, even if the path name is too long to fit
> in that field. As a result, I often can't see the full path displayed.
> Thanks.
Hi,
Is it possible to place the cursor in the field and then use the 'End'
and 'Home' buttons to navigate?
James|||No, that will only scroll vertically up and down the drop-down list, but not
horizontally.
"James Wilson" wrote:
> JKKUS wrote:
> > When working in DTS, specifically in the Transform Data Properties window,
> > when I click on the drop-down in the Table/View field the path will only
> > extend for the width of the field, even if the path name is too long to fit
> > in that field. As a result, I often can't see the full path displayed.
> >
> > Thanks.
> Hi,
> Is it possible to place the cursor in the field and then use the 'End'
> and 'Home' buttons to navigate?
> James
>|||This did not yet resolve my issue.
"James Wilson" wrote:
> JKKUS wrote:
> > When working in DTS, specifically in the Transform Data Properties window,
> > when I click on the drop-down in the Table/View field the path will only
> > extend for the width of the field, even if the path name is too long to fit
> > in that field. As a result, I often can't see the full path displayed.
> >
> > Thanks.
> Hi,
> Is it possible to place the cursor in the field and then use the 'End'
> and 'Home' buttons to navigate?
> James
>

Drop-down in DTS Package cut off

When working in DTS, specifically in the Transform Data Properties window,
when I click on the drop-down in the Table/View field the path will only
extend for the width of the field, even if the path name is too long to fit
in that field. As a result, I often can't see the full path displayed.
Thanks.
JKKUS wrote:
> When working in DTS, specifically in the Transform Data Properties window,
> when I click on the drop-down in the Table/View field the path will only
> extend for the width of the field, even if the path name is too long to fit
> in that field. As a result, I often can't see the full path displayed.
> Thanks.
Hi,
Is it possible to place the cursor in the field and then use the 'End'
and 'Home' buttons to navigate?
James
|||No, that will only scroll vertically up and down the drop-down list, but not
horizontally.
"James Wilson" wrote:

> JKKUS wrote:
> Hi,
> Is it possible to place the cursor in the field and then use the 'End'
> and 'Home' buttons to navigate?
> James
>
|||This did not yet resolve my issue.
"James Wilson" wrote:

> JKKUS wrote:
> Hi,
> Is it possible to place the cursor in the field and then use the 'End'
> and 'Home' buttons to navigate?
> James
>

Drop the time portion of a smalldatetime field

In VBA I'd use Format(myDateField,"Short Date") to display 1/31/2004
instead of 1/31/2004 10:30:25 AM
How can I do this in a stored procedure?
lqI'm not sure if this is the *cleanest* way, but you could do
CONVERT(SMALLDATETIME, myDateField, [100-1??]). If you look at the
online help for "CONVERT", you'll be able to see the optional paramater
at the end that allows you to format the data however you want. It is a
number that corresponds to a format. I use 102 which equals
'yyyy.mm.dd', but there are many others. Hope that helps.

Lauren Quantrell wrote:
> In VBA I'd use Format(myDateField,"Short Date") to display 1/31/2004
> instead of 1/31/2004 10:30:25 AM
> How can I do this in a stored procedure?
> lq|||laurenquantrell@.hotmail.com (Lauren Quantrell) wrote in message news:<47e5bd72.0401271927.585c8a97@.posting.google.com>...
> In VBA I'd use Format(myDateField,"Short Date") to display 1/31/2004
> instead of 1/31/2004 10:30:25 AM
> How can I do this in a stored procedure?
> lq

See the CONVERT() function in Books Online:

select convert(char(10), MyColumn, 101)
from dbo.MyTable

But in general you should format dates in your client application if
possible, as it's easier to handle different client regional settings
etc.

Simon

drop the time

I have a field in my table that is set as DATETIME
The data that gets imported to it is a text file and has teh data with teh
time.
for example, 1/1/2006 4:57:12 PM
I want to get rid of teh time part
How can I make it so the data that I already have in the table will drop teh
time?
Hi
In a SQL Server datetime field you will always have a time part even if you
don't specify the time it will default to midnight (00:00.000).
You don't say how the data is imported and if that is the only way the
column gets populated?
If you use DTS to load the data, you could use an activeX transform to
truncate the datetime field before it is inserted. If you use BCP/BULKINSERT
you can specify a format file that splits off the time and ignores it.
Another method is to load the data into a staging table and manipulate it
from there whilst it is being inserted into the final destination. You could
also use an instead of trigger (but you would need to make sure that it fires
for your bulk insert), or possibly have a computed column that truncates the
datetime.
John
"Johnfli" wrote:

> I have a field in my table that is set as DATETIME
> The data that gets imported to it is a text file and has teh data with teh
> time.
> for example, 1/1/2006 4:57:12 PM
> I want to get rid of teh time part
> How can I make it so the data that I already have in the table will drop teh
> time?
>
>
|||ummm, hmmmm, ok, so how do I do any of teh items you mentioned?
I have DTS setup as teh text file is ftp'd to us. Teh time is included in
the text file and teh people sending it to us are not interested in dropping
off teh time portion.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:CCF11FA2-4E88-4642-8242-AEA7213D590A@.microsoft.com...[vbcol=seagreen]
> Hi
> In a SQL Server datetime field you will always have a time part even if
> you
> don't specify the time it will default to midnight (00:00.000).
> You don't say how the data is imported and if that is the only way the
> column gets populated?
> If you use DTS to load the data, you could use an activeX transform to
> truncate the datetime field before it is inserted. If you use
> BCP/BULKINSERT
> you can specify a format file that splits off the time and ignores it.
> Another method is to load the data into a staging table and manipulate it
> from there whilst it is being inserted into the final destination. You
> could
> also use an instead of trigger (but you would need to make sure that it
> fires
> for your bulk insert), or possibly have a computed column that truncates
> the
> datetime.
> John
> "Johnfli" wrote:
|||Hi
I assume it is the same DTS package that will load the file?
Check out http://www.sqldts.com/default.aspx?279,4 on how to use the
ActiveX transform. You will need to use the left function on the source
column to just insert the date part.
John
Johnfli wrote:[vbcol=seagreen]
> ummm, hmmmm, ok, so how do I do any of teh items you mentioned?
> I have DTS setup as teh text file is ftp'd to us. Teh time is included in
> the text file and teh people sending it to us are not interested in dropping
> off teh time portion.
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:CCF11FA2-4E88-4642-8242-AEA7213D590A@.microsoft.com...
|||cool, I will give it a shot.
Thank you
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1143826579.603455.255570@.g10g2000cwb.googlegr oups.com...
> Hi
> I assume it is the same DTS package that will load the file?
> Check out http://www.sqldts.com/default.aspx?279,4 on how to use the
> ActiveX transform. You will need to use the left function on the source
> column to just insert the date part.
> John
> Johnfli wrote:
>

drop the time

I have a field in my table that is set as DATETIME
The data that gets imported to it is a text file and has teh data with teh
time.
for example, 1/1/2006 4:57:12 PM
I want to get rid of teh time part
How can I make it so the data that I already have in the table will drop teh
time'Hi
In a SQL Server datetime field you will always have a time part even if you
don't specify the time it will default to midnight (00:00.000).
You don't say how the data is imported and if that is the only way the
column gets populated?
If you use DTS to load the data, you could use an activeX transform to
truncate the datetime field before it is inserted. If you use BCP/BULKINSERT
you can specify a format file that splits off the time and ignores it.
Another method is to load the data into a staging table and manipulate it
from there whilst it is being inserted into the final destination. You could
also use an instead of trigger (but you would need to make sure that it fires
for your bulk insert), or possibly have a computed column that truncates the
datetime.
John
"Johnfli" wrote:
> I have a field in my table that is set as DATETIME
> The data that gets imported to it is a text file and has teh data with teh
> time.
> for example, 1/1/2006 4:57:12 PM
> I want to get rid of teh time part
> How can I make it so the data that I already have in the table will drop teh
> time'
>
>|||ummm, hmmmm, ok, so how do I do any of teh items you mentioned? :)
I have DTS setup as teh text file is ftp'd to us. Teh time is included in
the text file and teh people sending it to us are not interested in dropping
off teh time portion.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:CCF11FA2-4E88-4642-8242-AEA7213D590A@.microsoft.com...
> Hi
> In a SQL Server datetime field you will always have a time part even if
> you
> don't specify the time it will default to midnight (00:00.000).
> You don't say how the data is imported and if that is the only way the
> column gets populated?
> If you use DTS to load the data, you could use an activeX transform to
> truncate the datetime field before it is inserted. If you use
> BCP/BULKINSERT
> you can specify a format file that splits off the time and ignores it.
> Another method is to load the data into a staging table and manipulate it
> from there whilst it is being inserted into the final destination. You
> could
> also use an instead of trigger (but you would need to make sure that it
> fires
> for your bulk insert), or possibly have a computed column that truncates
> the
> datetime.
> John
> "Johnfli" wrote:
>> I have a field in my table that is set as DATETIME
>> The data that gets imported to it is a text file and has teh data with
>> teh
>> time.
>> for example, 1/1/2006 4:57:12 PM
>> I want to get rid of teh time part
>> How can I make it so the data that I already have in the table will drop
>> teh
>> time'
>>|||Hi
I assume it is the same DTS package that will load the file?
Check out http://www.sqldts.com/default.aspx?279,4 on how to use the
ActiveX transform. You will need to use the left function on the source
column to just insert the date part.
John
Johnfli wrote:
> ummm, hmmmm, ok, so how do I do any of teh items you mentioned? :)
> I have DTS setup as teh text file is ftp'd to us. Teh time is included in
> the text file and teh people sending it to us are not interested in dropping
> off teh time portion.
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:CCF11FA2-4E88-4642-8242-AEA7213D590A@.microsoft.com...
> > Hi
> >
> > In a SQL Server datetime field you will always have a time part even if
> > you
> > don't specify the time it will default to midnight (00:00.000).
> >
> > You don't say how the data is imported and if that is the only way the
> > column gets populated?
> >
> > If you use DTS to load the data, you could use an activeX transform to
> > truncate the datetime field before it is inserted. If you use
> > BCP/BULKINSERT
> > you can specify a format file that splits off the time and ignores it.
> > Another method is to load the data into a staging table and manipulate it
> > from there whilst it is being inserted into the final destination. You
> > could
> > also use an instead of trigger (but you would need to make sure that it
> > fires
> > for your bulk insert), or possibly have a computed column that truncates
> > the
> > datetime.
> >
> > John
> >
> > "Johnfli" wrote:
> >
> >> I have a field in my table that is set as DATETIME
> >>
> >> The data that gets imported to it is a text file and has teh data with
> >> teh
> >> time.
> >> for example, 1/1/2006 4:57:12 PM
> >>
> >> I want to get rid of teh time part
> >>
> >> How can I make it so the data that I already have in the table will drop
> >> teh
> >> time'
> >>
> >>
> >>|||cool, I will give it a shot.
Thank you
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1143826579.603455.255570@.g10g2000cwb.googlegroups.com...
> Hi
> I assume it is the same DTS package that will load the file?
> Check out http://www.sqldts.com/default.aspx?279,4 on how to use the
> ActiveX transform. You will need to use the left function on the source
> column to just insert the date part.
> John
> Johnfli wrote:
>> ummm, hmmmm, ok, so how do I do any of teh items you mentioned? :)
>> I have DTS setup as teh text file is ftp'd to us. Teh time is included
>> in
>> the text file and teh people sending it to us are not interested in
>> dropping
>> off teh time portion.
>>
>>
>> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> news:CCF11FA2-4E88-4642-8242-AEA7213D590A@.microsoft.com...
>> > Hi
>> >
>> > In a SQL Server datetime field you will always have a time part even if
>> > you
>> > don't specify the time it will default to midnight (00:00.000).
>> >
>> > You don't say how the data is imported and if that is the only way the
>> > column gets populated?
>> >
>> > If you use DTS to load the data, you could use an activeX transform to
>> > truncate the datetime field before it is inserted. If you use
>> > BCP/BULKINSERT
>> > you can specify a format file that splits off the time and ignores it.
>> > Another method is to load the data into a staging table and manipulate
>> > it
>> > from there whilst it is being inserted into the final destination. You
>> > could
>> > also use an instead of trigger (but you would need to make sure that it
>> > fires
>> > for your bulk insert), or possibly have a computed column that
>> > truncates
>> > the
>> > datetime.
>> >
>> > John
>> >
>> > "Johnfli" wrote:
>> >
>> >> I have a field in my table that is set as DATETIME
>> >>
>> >> The data that gets imported to it is a text file and has teh data with
>> >> teh
>> >> time.
>> >> for example, 1/1/2006 4:57:12 PM
>> >>
>> >> I want to get rid of teh time part
>> >>
>> >> How can I make it so the data that I already have in the table will
>> >> drop
>> >> teh
>> >> time'
>> >>
>> >>
>> >>
>

drop the time

I have a field in my table that is set as DATETIME
The data that gets imported to it is a text file and has teh data with teh
time.
for example, 1/1/2006 4:57:12 PM
I want to get rid of teh time part
How can I make it so the data that I already have in the table will drop teh
time'Hi
In a SQL Server datetime field you will always have a time part even if you
don't specify the time it will default to midnight (00:00.000).
You don't say how the data is imported and if that is the only way the
column gets populated?
If you use DTS to load the data, you could use an activeX transform to
truncate the datetime field before it is inserted. If you use BCP/BULKINSERT
you can specify a format file that splits off the time and ignores it.
Another method is to load the data into a staging table and manipulate it
from there whilst it is being inserted into the final destination. You could
also use an instead of trigger (but you would need to make sure that it fire
s
for your bulk insert), or possibly have a computed column that truncates the
datetime.
John
"Johnfli" wrote:

> I have a field in my table that is set as DATETIME
> The data that gets imported to it is a text file and has teh data with teh
> time.
> for example, 1/1/2006 4:57:12 PM
> I want to get rid of teh time part
> How can I make it so the data that I already have in the table will drop t
eh
> time'
>
>|||ummm, hmmmm, ok, so how do I do any of teh items you mentioned?
I have DTS setup as teh text file is ftp'd to us. Teh time is included in
the text file and teh people sending it to us are not interested in dropping
off teh time portion.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:CCF11FA2-4E88-4642-8242-AEA7213D590A@.microsoft.com...[vbcol=seagreen]
> Hi
> In a SQL Server datetime field you will always have a time part even if
> you
> don't specify the time it will default to midnight (00:00.000).
> You don't say how the data is imported and if that is the only way the
> column gets populated?
> If you use DTS to load the data, you could use an activeX transform to
> truncate the datetime field before it is inserted. If you use
> BCP/BULKINSERT
> you can specify a format file that splits off the time and ignores it.
> Another method is to load the data into a staging table and manipulate it
> from there whilst it is being inserted into the final destination. You
> could
> also use an instead of trigger (but you would need to make sure that it
> fires
> for your bulk insert), or possibly have a computed column that truncates
> the
> datetime.
> John
> "Johnfli" wrote:
>|||Hi
I assume it is the same DTS package that will load the file?
Check out http://www.sqldts.com/default.aspx?279,4 on how to use the
ActiveX transform. You will need to use the left function on the source
column to just insert the date part.
John
Johnfli wrote:[vbcol=seagreen]
> ummm, hmmmm, ok, so how do I do any of teh items you mentioned?
> I have DTS setup as teh text file is ftp'd to us. Teh time is included
in
> the text file and teh people sending it to us are not interested in droppi
ng
> off teh time portion.
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:CCF11FA2-4E88-4642-8242-AEA7213D590A@.microsoft.com...|||cool, I will give it a shot.
Thank you
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1143826579.603455.255570@.g10g2000cwb.googlegroups.com...
> Hi
> I assume it is the same DTS package that will load the file?
> Check out http://www.sqldts.com/default.aspx?279,4 on how to use the
> ActiveX transform. You will need to use the left function on the source
> column to just insert the date part.
> John
> Johnfli wrote:
>

Drop publication to change table structure?

The title pretty much sums up the question: when I try to edit the table
structure, i.e., add a field or change type from int to uniqueidentifier,
the change is rejected with "cannot drop table" error.
Earl,
are you using SQL 2005 Beta 2? If so you get this message if you do the
change in the GUI but not by code. If this is SQL 2000, then you'll need to
use sp_repladdcolumn and sp_repldropcolun to make schema changes. Alering a
column is not supported directly.
rgds,
Paul Ibison, SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks for the advice Paul. It is SQL2k.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23zKN5BDyEHA.2804@.TK2MSFTNGP15.phx.gbl...
> Earl,
> are you using SQL 2005 Beta 2? If so you get this message if you do the
> change in the GUI but not by code. If this is SQL 2000, then you'll need
> to
> use sp_repladdcolumn and sp_repldropcolun to make schema changes. Alering
> a
> column is not supported directly.
> rgds,
> Paul Ibison, SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>

Drop List problem

Hi,

When I'm creating a dropdown list prompt from a field which has large number of distinct values (OrgUnit codes etc.) I get an empty list.

Can anyone advice on this issue ?

Is your drop down list bound to a dataset.

Thanks,

vnswathi.

|||

Hi,

We are using Cognos right now and moving to SSRS. In Cognos we don't have this problem and this problem occur in SSRS with all kind of field types if it has large amount of distinct values. Maybe it's a restriction in SSRS ? and if so, maybe you know the upper limit ?

Thanks,

Yuval

|||

hi

i dont think there would be any problem with the fields size

u need to create a separate dataset for the drop down list prompt.

and then choose the from query parameter...

hope it solves your issue.

regards,

www.snktheone.com

Drop Identity

Is there anyway to drop the Identity characteristic from a field? I have a database where I need to remove some Identity characteristics from the primary keys.

I have been able to work around this situation by deleting the primary key column, and creating a new primary key without the Identity... but that seems the long way around.Use enterprise manager. Right-click the table and choose design table. Once in design view you can remove the identity.|||Thanks for your reply. I should have qualified my initial problem. I knew I could remove the Identity with the Enterprise Manager, but I wanted to script the change so that it could be run on a couple of different remote servers by individuals other than myself.

Any idea how to do that?|||You could make the changes as suggested above and then save them to a script file (instead of executing them). That's an option in SQL EM when you are in table design mode. I think in this case (though I'm not positive), that the script EM will generate will create a temp table (without the identity fields), copy all the records into the temp table, drop the original table and then rename the new temp table with the name of the original table.

I could be wrong on the sequence, but I think that is what is required when eliminating the identity property from a column.

Regards,

Hugh Scott

Originally posted by acg_ray
Thanks for your reply. I should have qualified my initial problem. I knew I could remove the Identity with the Enterprise Manager, but I wanted to script the change so that it could be run on a couple of different remote servers by individuals other than myself.

Any idea how to do that?|||Use EM the same way as if you remove identity in design table, but click SCRIPT instead of SAVE.
Identity cannot be dropped without dropping column with identity. To free this dropped fixed column used space, you must drop table. So without dropping table you cannot remove identity and keep fast table. You need too many opperations to be done...|||This is what ss does - your method is probably faster:

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_Test
(
ID int NOT NULL,
Code varchar(10) NULL,
Name varchar(255) NOT NULL,
Conference int NOT NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.Divisions)
EXEC('INSERT INTO dbo.Tmp_Test(ID, Code, Name, Conference)
SELECT ID, Code, Name, Conference FROM dbo.Test TABLOCKX')
GO
DROP TABLE dbo.Test
GO
EXECUTE sp_rename 'dbo.Tmp_Test', 'Test'
GO
COMMIT|||But you must drop and recreate all referenced objects, all those PK,FK,DF,CK,IX and other schema bound objects.|||Thanks... this will be of great help.

- Ray

Originally posted by hmscott
You could make the changes as suggested above and then save them to a script file (instead of executing them). That's an option in SQL EM when you are in table design mode. I think in this case (though I'm not positive), that the script EM will generate will create a temp table (without the identity fields), copy all the records into the temp table, drop the original table and then rename the new temp table with the name of the original table.

I could be wrong on the sequence, but I think that is what is required when eliminating the identity property from a column.

Regards,

Hugh Scott

drop down list

hi all !

i'm kind of new to MYSQL so am still learning basics.
my question is how do you create a drop down list in a field in an SQL
database ?
thanks
brinoPresentation elements like drop-down lists are not really part of a
database. However, some products (e.g. Access) blur this distinction by
combining both database and GUI functionality.

BTW, this is a Microsoft SQL Server group. For MySQL questions, you are
better off posting to a MySQL forum.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"brino" <bdsolutions2002@.yahoo.com.auwrote in message
news:1159662604.815246.169630@.b28g2000cwb.googlegr oups.com...

Quote:

Originally Posted by

hi all !
>
i'm kind of new to MYSQL so am still learning basics.
my question is how do you create a drop down list in a field in an SQL
database ?
thanks
brino
>

Drillthrough to report gives 'read-only' error on parameters

I have indicated the field and set it to call the next report where I need to
pass 4 parameters. I also set the fields parameters to the values to be
passed (2 of these are parameters in the calling report and 2 are fields on
the calling report) I set them up on next report's parameters to not have a
prompt and a set default value. When I click on the field to drilldown I get
an error message about the parameters value being 'read-only' and no report.
Can anyone help me ?
--
SusanCPromptless parameters are read only.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Susanc" <Susanc@.discussions.microsoft.com> wrote in message
news:2FFEEE63-E798-423B-863E-663035594429@.microsoft.com...
>I have indicated the field and set it to call the next report where I need
>to
> pass 4 parameters. I also set the fields parameters to the values to be
> passed (2 of these are parameters in the calling report and 2 are fields
> on
> the calling report) I set them up on next report's parameters to not
> have a
> prompt and a set default value. When I click on the field to drilldown I
> get
> an error message about the parameters value being 'read-only' and no
> report.
> Can anyone help me ?
> --
> SusanC|||Okay. Then how do you suppress the second reports parameters at the top and
provide them via the link. Sorry, I am somewhat of a beginner with Reporting
Services. Thanks much for the help.
"Lev Semenets [MSFT]" wrote:
> Promptless parameters are read only.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Susanc" <Susanc@.discussions.microsoft.com> wrote in message
> news:2FFEEE63-E798-423B-863E-663035594429@.microsoft.com...
> >I have indicated the field and set it to call the next report where I need
> >to
> > pass 4 parameters. I also set the fields parameters to the values to be
> > passed (2 of these are parameters in the calling report and 2 are fields
> > on
> > the calling report) I set them up on next report's parameters to not
> > have a
> > prompt and a set default value. When I click on the field to drilldown I
> > get
> > an error message about the parameters value being 'read-only' and no
> > report.
> > Can anyone help me ?
> >
> > --
> > SusanC
>
>|||It does work with the prompts, thanks. I was hoping to be able to suppress
the parameters on the 2nd report.
"Lev Semenets [MSFT]" wrote:
> Promptless parameters are read only.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Susanc" <Susanc@.discussions.microsoft.com> wrote in message
> news:2FFEEE63-E798-423B-863E-663035594429@.microsoft.com...
> >I have indicated the field and set it to call the next report where I need
> >to
> > pass 4 parameters. I also set the fields parameters to the values to be
> > passed (2 of these are parameters in the calling report and 2 are fields
> > on
> > the calling report) I set them up on next report's parameters to not
> > have a
> > prompt and a set default value. When I click on the field to drilldown I
> > get
> > an error message about the parameters value being 'read-only' and no
> > report.
> > Can anyone help me ?
> >
> > --
> > SusanC
>
>|||Starting with SP1 it is possible to create hidden parameters. Please look
into SRS SP1 readme file for more information
(
http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm#_report_manager_parameters )
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Susanc" <Susanc@.discussions.microsoft.com> wrote in message
news:C4A4ABC5-EA9E-498E-838D-A13F6E887045@.microsoft.com...
> It does work with the prompts, thanks. I was hoping to be able to
> suppress
> the parameters on the 2nd report.
> "Lev Semenets [MSFT]" wrote:
>> Promptless parameters are read only.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Susanc" <Susanc@.discussions.microsoft.com> wrote in message
>> news:2FFEEE63-E798-423B-863E-663035594429@.microsoft.com...
>> >I have indicated the field and set it to call the next report where I
>> >need
>> >to
>> > pass 4 parameters. I also set the fields parameters to the values to
>> > be
>> > passed (2 of these are parameters in the calling report and 2 are
>> > fields
>> > on
>> > the calling report) I set them up on next report's parameters to not
>> > have a
>> > prompt and a set default value. When I click on the field to drilldown
>> > I
>> > get
>> > an error message about the parameters value being 'read-only' and no
>> > report.
>> > Can anyone help me ?
>> >
>> > --
>> > SusanC
>>|||Susanc wrote:
> It does work with the prompts, thanks. I was hoping to be able to
> suppress the parameters on the 2nd report.
>
Hi Susan,
publish your report to the server, open Report Manager and then do the
following:
check "Has Default Value"
check "NULL"
check "Prompt for User"
AND
clear "Prompt String" -Textbox
that worked for me. I've tried to figure it out within visual studio. It
doesn't work.
But if the report is published to the server and manipulated in report
manager with the steps above.. it works....seems to be a MS-feature ;-)
and If you call your report in the following URL-Type
http://sql/ReportServer?/KKM/MYREPORT&Month=Jan+2004&KTR=KTR&rs:Command=Render&rs:Format=HTML4.0
the user can not expand the toolbar
Hope that helps
regards
Frank
www.xax.de

Drill-Down Context

I'm wanting to set the background color of a field. The field is part of a
drill-down row group (Office -> Transaction Type (visibility toggled by
Office) --> Field!TranCount.Value).
I would like to set the background color based on 2 conditions:
1) Field!x.Value (which I know) - this works currently
2) The context of the TranCount field
By context I mean...
If the drilldown is "open" or "closed".
Since the expression language in SRS is, apparently, very limited (and not
very well documented) I'm assuming that I'll have to live with Just setting
the background color based on the known field values.. but I can always ask!!!
TIADetermining the state of the drilldown toggle is not supported.
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Glenn" <Glenn@.discussions.microsoft.com> wrote in message
news:82E55B4E-8153-4456-B0B9-9F45AE8AE797@.microsoft.com...
> I'm wanting to set the background color of a field. The field is part of a
> drill-down row group (Office -> Transaction Type (visibility toggled by
> Office) --> Field!TranCount.Value).
> I would like to set the background color based on 2 conditions:
> 1) Field!x.Value (which I know) - this works currently
> 2) The context of the TranCount field
> By context I mean...
> If the drilldown is "open" or "closed".
> Since the expression language in SRS is, apparently, very limited (and not
> very well documented) I'm assuming that I'll have to live with Just
> setting
> the background color based on the known field values.. but I can always
> ask!!!
> TIA

drill through report

Hi All,

I have one dotnet applciation in that i am viewing the report in report viewer. In the same report if the user clicks on one field another report will open. I got a problem with this second report. the image placed on the second report is not coming and if the user clicks on the print button which is on the report viewer the first report is only getting printed. How can I print the second report.

Thanks In Advance

Sorry, to answer my own question just found the option linked report in report manager!

Drill Through bringing back too many results

I have created a Matrix Report through the Report Builder and specified a filter on a Date field so it only shows the current months work. This works for the initial Totals within the Matrix but when you click on the Totals to show the Drill Down it will show all the results regardless of the Parameter.

How do I make the results in the Drill Through look at the Parameter I am passing to the Report?

Many Thanks

I'm not sure I understand your scenario, and I can't repro the behavior you describe using my best guess. Can you clarify:

1. What fields are in your report?

2. Did you actually create a report parameter by setting Prompt on the filter condition, or did you just create a normal report filter?

Any other info you can provide to further clarify your scenario would be helpful. Thanks!

Drill down on table fields?

I remember that one of the cool things you could do in SSRS 2005 was drill down. I have a summed field in my SSRS Report table which I'd like to see the records behind that sum. How do I enable this feature where I can double click that field?Set up groups then edit the group and set the visibility to depend on the group above.