Can I group stored procedures some way

Hi folks!
I'm working on a report that requires about 20 SPs to retrieve data.
My code would be better if I could somehow create some object
(package?) that would have all my 20 SPs in it.
This way if I have 100 reports it will be easy to manage the SPs in the
database.
I thought I read several years ago there was a way to do this. Is there
still a way?
Thanks in advance.Bob wrote:
> Hi folks!
> I'm working on a report that requires about 20 SPs to retrieve data.
> My code would be better if I could somehow create some object
> (package?) that would have all my 20 SPs in it.
> This way if I have 100 reports it will be easy to manage the SPs in
> the database.
> I thought I read several years ago there was a way to do this. Is
> there still a way?
> Thanks in advance.
I'm about you're actually trying to do here. Are you talking
about packaging your procedures in order to create them on another
database? Can you give an example of what you need?
David Gugick
Imceda Software
www.imceda.com|||"Bob" <Go1369@.Yahoo.Com> wrote in message
news:1109359684.432319.268490@.l41g2000cwc.googlegroups.com...
> Hi folks!
> I'm working on a report that requires about 20 SPs to retrieve data.
> My code would be better if I could somehow create some object
> (package?) that would have all my 20 SPs in it.
> This way if I have 100 reports it will be easy to manage the SPs in the
> database.
> I thought I read several years ago there was a way to do this. Is there
> still a way?
>
Are you refering to the stored procedure number?
CREATE PROC [ EDURE ] procedure_name [ ; number ]
. . .
;number
Is an optional integer used to group procedures of the same name so they can
be dropped together with a single DROP PROCEDURE statement. For example, the
procedures used with an application called orders may be named orderproc;1,
orderproc;2, and so on. The statement DROP PROCEDURE orderproc drops the
entire group. If the name contains delimited identifiers, the number should
not be included as part of the identifier; use the appropriate delimiter
around procedure_name only.
This can be used to group procedures, but it's an old and rarely used
feature, and you run the risk of confusing people. I would probably just
use a common name prefix to sort and identify the related procedures.
David

can I export tables so that existing tables in destination database will be modified?

I'm working on an ASP.Net project where I want to test code on a local
machine using a local database as a back-end, and then export it to
the production machine where it uses the hosting provider's SQL Server
database on the back-end. Is there a way to export tables from one
SQL Server database to another in such a way that if a table already
exists in the destination database, it will be updated to reflect the
changes to the local table, without existing data in the destination
table being lost? e.g. suppose I change some tables in my local
database by adding new fields. Can I "export" these changes to the
destination database so that the new fields will be added to the
destination tables (and filled in with default values), without losing
data in the destination tables?

If I run the DTS Import/Export Wizard that comes with SQL Server and
choose "Copy table(s) and view(s) from the source database" and choose
the tables I want to copy, there is apparently no option *not* to copy
the data, and since I don't want to copy the data, that choice doesn't
work. If instead of "Copy table(s) and view(s) from the source
database", I choose "Copy objects and data between SQL Server
databases", then on the following options I can uncheck the "Copy
Data" box to prevent data being copied. But for the "Create
Destination Objects" choices, I have to uncheck "Drop destination
objects first" since I don't want to lose the existing data. But when
I uncheck that and try to do the copy, I get collisions between the
properties of the local table and the existing destination table,
e.g.:
"Table 'wbuser' already has a primary key defined on it."

Is there no way to do what I want using the DTS Import/Export Wizard?
Can it be done some other way?

-Bennett"Bennett Haselton" <bennett@.peacefire.org> wrote in message
news:e614455c.0411160123.294dfd10@.posting.google.c om...
> I'm working on an ASP.Net project where I want to test code on a local
> machine using a local database as a back-end, and then export it to
> the production machine where it uses the hosting provider's SQL Server
> database on the back-end. Is there a way to export tables from one
> SQL Server database to another in such a way that if a table already
> exists in the destination database, it will be updated to reflect the
> changes to the local table, without existing data in the destination
> table being lost? e.g. suppose I change some tables in my local
> database by adding new fields. Can I "export" these changes to the
> destination database so that the new fields will be added to the
> destination tables (and filled in with default values), without losing
> data in the destination tables?

In a word, no.

I'd suggest take the same version controlled scripts (you are of course
using version control, right? :-) you used to make the changes in Dev and
run those against the Prod server.

> If I run the DTS Import/Export Wizard that comes with SQL Server and
> choose "Copy table(s) and view(s) from the source database" and choose
> the tables I want to copy, there is apparently no option *not* to copy
> the data, and since I don't want to copy the data, that choice doesn't
> work. If instead of "Copy table(s) and view(s) from the source
> database", I choose "Copy objects and data between SQL Server
> databases", then on the following options I can uncheck the "Copy
> Data" box to prevent data being copied. But for the "Create
> Destination Objects" choices, I have to uncheck "Drop destination
> objects first" since I don't want to lose the existing data. But when
> I uncheck that and try to do the copy, I get collisions between the
> properties of the local table and the existing destination table,
> e.g.:
> "Table 'wbuser' already has a primary key defined on it."
> Is there no way to do what I want using the DTS Import/Export Wizard?
> Can it be done some other way?
> -Bennett|||"Greg D. Moore \(Strider\)" <mooregr_deleteth1s@.greenms.com> wrote in message news:<7Mnmd.16$1u.15@.twister.nyroc.rr.com>...
> "Bennett Haselton" <bennett@.peacefire.org> wrote in message
> news:e614455c.0411160123.294dfd10@.posting.google.c om...
> > I'm working on an ASP.Net project where I want to test code on a local
> > machine using a local database as a back-end, and then export it to
> > the production machine where it uses the hosting provider's SQL Server
> > database on the back-end. Is there a way to export tables from one
> > SQL Server database to another in such a way that if a table already
> > exists in the destination database, it will be updated to reflect the
> > changes to the local table, without existing data in the destination
> > table being lost? e.g. suppose I change some tables in my local
> > database by adding new fields. Can I "export" these changes to the
> > destination database so that the new fields will be added to the
> > destination tables (and filled in with default values), without losing
> > data in the destination tables?
> In a word, no.
> I'd suggest take the same version controlled scripts (you are of course
> using version control, right? :-) you used to make the changes in Dev and
> run those against the Prod server.

Uh, no... my "script" is that I move the mouse pointer over the
"wbuser" table in Server Explorer in VS .Net, right-click, pick
"Design Table", and add columns that way :)

So I guess I'll just have to remember to run that "script" against the
Prod database whenever I make any changes.

Still, I would have thought that what I was trying, would be a pretty
common thing for people to want to do... surprised it's not built-in.

-Bennett|||Bennett Haselton (bennett@.peacefire.org) writes:
> Uh, no... my "script" is that I move the mouse pointer over the
> "wbuser" table in Server Explorer in VS .Net, right-click, pick
> "Design Table", and add columns that way :)
> So I guess I'll just have to remember to run that "script" against the
> Prod database whenever I make any changes.
> Still, I would have thought that what I was trying, would be a pretty
> common thing for people to want to do... surprised it's not built-in.

There are third-party tools. Red Gate's SQL Compare is popular.

One reason that this is not built-in is that changes to tables is far
from always something that can be performed automatically. In our shop
we keep all table definitions under source control. To generate an update
script we have a tool that reads SourceSafe between two labels to load
all new and changed objects. For changed tables you get a script that
copies data from the old definition to the new, but this script is really
only a template. In many cases I need to change the script, because a
new column is supposed be initiated with some values derived from
other columns or tables. And there might be even wilder changes.

Point and click may be convenient, but for something which is to be
run several times needs to be automized to be sure that all are updates
are carried out the same way. Thus, you need a script.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Can I connect to remote SQL 2005 server through SQL Server Management Studio Express?

Can I connect to remote SQL 2005 server through SQL Server Management Studio Express?

I always get a error code 18456 when I try to connect to SQL 2005 server through SQL Server Management Studio Express. I'm sure I enter correct username and password!

The answer is yes its possible but it depends on the security configuration of the remote server.

Hope it helps,

Joe

|||oh wait. I don't have the Express version so I'm not sure if it is limited to local connections, it might be.|||

mycwcgr:

Can I connect to remote SQL 2005 server through SQL Server Management Studio Express?

I always get a error code 18456 when I try to connect to SQL 2005 server through SQL Server Management Studio Express. I'm sure I enter correct username and password!

Try the thread below everything you need and alternatives is covered in it. Post again if you still have questions. Hope this helps.

http://forums.asp.net/thread/1379211.aspx

Can I assign rowguid from the client code instead of using default newid()?

Do I have to use default newid() for the rowguid? I would prefer to pass a
sequential GUID from the client code instead.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server-replication/200701/1
Or can I at least use newsequentialid() instead of newid() as the ROWGUID
default?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server-replication/200701/1
|||Alex,
Yes,
CREATE TABLE dbo.Table1
(
test uniqueidentifier NOT NULL ROWGUIDCOL
) ON [PRIMARY]
ALTER TABLE dbo.Table1 ADD CONSTRAINT
DF_Table1_test DEFAULT (newid()) FOR test
-- Bill
"Alex via droptable.com" <no@.spam.pls> wrote in message
news:6bd9f60658969@.uwe...
> Or can I at least use newsequentialid() instead of newid() as the ROWGUID
> default?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server-replication/200701/1
>
|||Thank you for your response. It seems your code is still using newid(), but
anyway it is not what I was asking. The MS documentation says that one can
create replication ROWGUID column manually as long as it have the following
characteristics:
* The data type as UNIQUEIDENTIFIER.
* The default as NEWID().
* The ROWGUIDCOL property.
* A unique index on the column.
My questions are:
1. Can I use newsequentialid() instead of newid() ?
2. Can I supply GUID from my client code instead of using this default value?
I am interested in transactional replication model with updating subscribers.
AlterEgo wrote:[vbcol=seagreen]
>Alex,
>Yes,
>CREATE TABLE dbo.Table1
> (
> test uniqueidentifier NOT NULL ROWGUIDCOL
> ) ON [PRIMARY]
>ALTER TABLE dbo.Table1 ADD CONSTRAINT
> DF_Table1_test DEFAULT (newid()) FOR test
>-- Bill
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server-replication/200701/1

Can Code be used to define parameters in a subscription?

I am trying to create an email subscription that runs every Monday for the previous week. To do this I was trying to set the start date parameter to dateadd("d", -7, today()) and the end date parameter to dateadd("d", -1, today()). However, every time I change it to anything but a static date value, the screen refreshes and changes the code back to the default date.

I know I can set the defaults to the previous week in the report itself but I already have another subscription that runs the same report as MTD so the parameter defaults are set to that. The only other way I can think to accomplish this is to create a copy of the report with the only difference being parameter defaults (which I don't think is a good solution).

Any suggestions are welcome.

You said that you have the other report run for MTD. What do you mean by that?|||Sorry, Month To Date (MTD)|||Does anyone know if this is possible? I still have not found a solution.

Can anyone tell me why this code wont update my SQL Server database?

I am trying to perform a simple update to a SQL Server database table and I can't figure out why this simple UPDATE command doesn't work. I am performing the same thing in another page for owner information and it works just fine. It seems that some of my code won't work even though the syntax is correct and there is no reason for it not to work.

I have hardcoded text for some of the fields and the UPDATE code below works but when I try to use the information that may be in the Textbox, the code won't do the UPDATE. Please help me figure out what is going on.

Thanks

Sub btnUpdate_Click_1(sender As Object, e As EventArgs)
 Dim UserCode as String
 Dim PropertyCode as String
 Dim UnitCode as String
 Dim address1 as String
 Dim address2 as String
 Dim address3 as String
 Dim city as String
 Dim zip_Code as String
 Dim description as String
 Dim price_Range as String
 Dim state as String
 Dim type_Property as String
 Dim property_Status as String
 if chkChangeState.Checked = True then
 state = ddl_State.SelectedItem.Value
 else
 state = lblState.Text
 end if
 if chkPropertyType.Checked = True then
 type_Property = ddlPropertyType.SelectedItem.Value
 else
 type_Property = lblPropertyType.Text
 end if
 if chkPropertyStatus.Checked = True then
 property_Status = ddlPropertyStatus.SelectedItem.Value
 else
 property_Status = lblPropertyStatus.Text
 end if
 txtPropertyCode.ReadOnly = False
 txtUnitCode.ReadOnly = False
 'address1 = txtAddress1.Text
 'address2 = txtAddress2.Text
 'address3 = txtAddress3.Text
 'city = txtCity.Text
 'zip_Code = txtZipCode.Text
 'description = txtDescription.Text
 'price_Range = txtPriceRange.Text
 UserCode = Server.HtmlEncode(Request.Cookies("UCodeCookie")("Code"))
 PropertyCode = txtPropertyCode.Text
 UnitCode = txtUnitCode.Text
Dim connectionString As String = "server='(local)'; trusted_connection=true; database='master'"
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
 Dim queryString As String = "UPDATE [Property_db] SET [Address1]=@.Address1, [Address2]=@.Address2, [Address3]=@.Address3, [City]=@.City, [State]=@.State, [Zip_Code]=@.Zip_Code, [Type_Property]=@.Type_Property, [Description]=@.Description, [Property_Status]=@.Property_Status, [Price_Range]=@.Price_Range WHERE ([Property_db].[Code] = @.Code) AND ([Property_db].[Prop_Code] = @.Prop_Code) AND ([Property_db].[Unit_Code] = @.Unit_Code)"
 Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
 dbCommand.CommandText = queryString
 dbCommand.Connection = dbConnection
 Dim dbParam_code As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_code.ParameterName = "@.Code"
 dbParam_code.Value = UserCode
 dbParam_code.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_code)
 Dim dbParam_prop_Code As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_prop_Code.ParameterName = "@.Prop_Code"
 dbParam_prop_Code.Value = PropertyCode
 dbParam_prop_Code.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_prop_Code)
 Dim dbParam_unit_Code As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_unit_Code.ParameterName = "@.Unit_Code"
 dbParam_unit_Code.Value = UnitCode
 dbParam_unit_Code.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_unit_Code)
 Dim dbParam_address1 As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_address1.ParameterName = "@.Address1"
 dbParam_address1.Value = txtAddress1.Text
 dbParam_address1.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_address1)
 Dim dbParam_address2 As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_address2.ParameterName = "@.Address2"
 dbParam_address2.Value = txtAddress2.Text
 dbParam_address2.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_address2)
 Dim dbParam_address3 As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_address3.ParameterName = "@.Address3"
 dbParam_address3.Value = txtAddress3.Text
 dbParam_address3.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_address3)
 Dim dbParam_city As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_city.ParameterName = "@.City"
 dbParam_city.Value = txtCity.Text
 dbParam_city.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_city)
 Dim dbParam_state As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_state.ParameterName = "@.State"
 dbParam_state.Value = state
 dbParam_state.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_state)
 Dim dbParam_zip_Code As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_zip_Code.ParameterName = "@.Zip_Code"
 dbParam_zip_Code.Value = txtZipCode.Text
 dbParam_zip_Code.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_zip_Code)
 Dim dbParam_type_Property As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_type_Property.ParameterName = "@.Type_Property"
 dbParam_type_Property.Value = type_Property
 dbParam_type_Property.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_type_Property)
 Dim dbParam_description As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_description.ParameterName = "@.Description"
 dbParam_description.Value = txtDescription.Text
 dbParam_description.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_description)
 Dim dbParam_property_Status As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_property_Status.ParameterName = "@.Property_Status"
 dbParam_property_Status.Value = property_Status
 dbParam_property_Status.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_property_Status)
 Dim dbParam_price_Range As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_price_Range.ParameterName = "@.Price_Range"
 dbParam_price_Range.Value = txtPriceRange.Text
 dbParam_price_Range.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_price_Range)
 Dim rowsAffected As Integer = 0
 dbConnection.Open
 Try
 rowsAffected = dbCommand.ExecuteNonQuery
 Finally
 dbConnection.Close
 End Try
'if Not rowsAffected then
 Response.Redirect("./editproperty.aspx")
'end if
End Sub

Be sure to indicate parameter lengths for character-type data.

Terri

Can anyone help me with connecting to a database to search?

I'm trying to make this piece of code work, I get all of it except how I get the data from the database, what I wish to do is. On page load I wish to take the value of a querystring in the page URL called ProductID and search a database to see if it exists in a specifical table and column, if it exists I wish to make a button not visible. the code I am using is:

#############

protectedvoid Page_Load(object sender,EventArgs e)

{

// Get the querystring value

String inQueryString = Request.QueryString["ProductID"];

// Get the data from the database, using the above value

DataTable data = ??

The datasource I want to connect to is SqlDataSource1, and the table I want to search is "Reviews". I want to search in the column "ProductID" and search for the ProductID from the URL query string above.

// Did we find a product in the database?

if (data !=null &&

data.Rows.count > 0)

{

// Code to display the product's information

// We found a product, so we need to hide a button

Button5.Visible =false;

}

}

##########################

Please can somebody fill in the missing bit for me so i know how it should be, i've searched the quickstart tutorial on the menu above and I still don't understand it, as it doesn't show anything like what I want to do, and I have looked at numerous websites about the matter. If you don't have time that is okay, but If somebody does have the time to show me what I need, I would be very greatfull.

Regards

Daniel coates

anyone got any ideas? surely somebody must know what I need to do!

Regards

Dan

Can activation SP wake up a thread?

Newbie question, but is there a way for me to set up a thread in my c# code so as it sleeps until it gets woken up by an event fired by an activation SP?

As in queue sits idle, thread sleeps.
Queue receives a message, activation SP gets fired, activation SP raises an event which gets caught by event handler in code, which wakes up thread to do processing?

The activated procedure can be C# code (an assembly in the database). Once activated, your CLR code would be invoked in a thread inside SQL Server and can issue a RECEIVE (using SqlCommand) to retrive the messages from the queue.

If you want your C# code to be in an external application (an .EXE) then you should use external activation instead of internal activation. Have a look at the External Activator sample at http://www.codeplex.com/SQLSrvSrvcBrkr/

Can a TextBox Height and Width be set dynamically?

SSRS 2005
I have a custom code assembly which I am using to control my textbox font
settings at run time. That works fine.
However I would also like to set the Location Left and Top and Size Width
and Height properties of my textboxes using functions in my custom code
assembly.
However no matter what I try I get this error message:
====================================== Property value is not valid.
=...[my function name]...cannot be parsed as a unit because it does not
contain numeric values. Examples of valid unit strings are "1pt" and ".5in".
======================================
I tried returning a string as indicated, a double value, etc. Nothing works.
Is this just a case where these properties cannot be set dynamically? That
seems to be the indication in this old forum post:
10/15/2004
"Text Box... Width"
"The height and width of a textbox are fixed at design time and cannot use
the database table schema to set them at runtime."
--
Chris, SSSITry this, have a parameter point to the code call.
=@.ParameterName
=-Chris
"Chris G." <ChrisG@.nospam.nospam> wrote in message
news:30911811-2F6D-454C-B44F-9F2F69ED11B5@.microsoft.com...
> SSRS 2005
> I have a custom code assembly which I am using to control my textbox font
> settings at run time. That works fine.
> However I would also like to set the Location Left and Top and Size Width
> and Height properties of my textboxes using functions in my custom code
> assembly.
> However no matter what I try I get this error message:
> ======================================> Property value is not valid.
> =...[my function name]...cannot be parsed as a unit because it does not
> contain numeric values. Examples of valid unit strings are "1pt" and
> ".5in".
> ======================================> I tried returning a string as indicated, a double value, etc. Nothing
> works.
> Is this just a case where these properties cannot be set dynamically? That
> seems to be the indication in this old forum post:
> 10/15/2004
> "Text Box... Width"
> "The height and width of a textbox are fixed at design time and cannot use
> the database table schema to set them at runtime."
> --
> Chris, SSSI|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Wei,
I do not understand Chris Connor's suggestion.
Can you clarify what he is suggesting that I do?
What does the =@.Parameter syntax signify?
Is he suggesting that I can call my custom code assmebly function this way,
or is he saying to assign values to TextBox Height and Width using report
parameters instead?
Please read my original post and provide whatever information you can.
Thanks!
-- Chris
-- Chris
--
Chris, SSSI
"Wei Lu [MSFT]" wrote:
> Hi ,
> How is everything going? Please feel free to let me know if you need any
> assistance.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi Chris,
I have posted a reply in your post "Can I obtain a reference to a report
item?" and hope that will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights|||Hi Wei,
This was my question in my prior reply, but you did not answer it:
"What does the =@.Parameter syntax signify?"
Can you please answer that question?
--
Chris, SSSI
"Wei Lu [MSFT]" wrote:
> Hi Chris,
> I have posted a reply in your post "Can I obtain a reference to a report
> item?" and hope that will be helpful.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights
>|||Hello Chris,
I am not sure about what does Chris Conner means but I assume he just
suggest to you to pass the Report Parameter to your code to check whether
you could modify the textbox layout.
But from your scenario, it seems could not been implement. So I replied and
would like to know whether you feel it helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Wei Lu,
Did you confirm that Chris Connor's suggestion works?
It seems to me that textbox height and width must be hard coded at design
time with a static value and that they can not be set programatically (either
using a custom assembly or a report parameter) at run time. This seems to be
a limitation in the product. Can you please confirm that this is a limitation
in the product?
Or do you have a way to accomplish this dynamic behavior?
--
Chris, SSSI
"Wei Lu [MSFT]" wrote:
> Hello Chris,
> I am not sure about what does Chris Conner means but I assume he just
> suggest to you to pass the Report Parameter to your code to check whether
> you could modify the textbox layout.
> But from your scenario, it seems could not been implement. So I replied and
> would like to know whether you feel it helpful.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hello Chris,
Based on my research, since the Height and Width is readonly, you could not
set the value.
My suggestion is to use the XML web services.
You need to generate the RDL file by your self.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Wei Lu,
I understand what you are saying. Thank you for the reply.
--
Chris, SSSI
"Wei Lu [MSFT]" wrote:
> Hello Chris,
> Based on my research, since the Height and Width is readonly, you could not
> set the value.
> My suggestion is to use the XML web services.
> You need to generate the RDL file by your self.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>

Can a SSIS package join an external transaction?

Hi there,

I'm trying to come up with the best way to build some C# Unit tests for an SSIS package I've built.

My C# code does the following

1. Creates a Transaction Scope using System.Transactions

2. Puts some source data for my package into a table that the package will read

3. Kicks off the Package using System.Diagnostics.ProcessStartInfo

Im getting a Transaction TimeOut execption which I beleive is caused because the Package can't read the source data I've inserted becuase the package has not joined the transaction.

So the question is - can you call a run a package and make it participate in a transaction that you have created outside of the package?

Thanks.

I don't believe you can do this, as having a look through the documentation there does not seem to be anywhere to pass in that existing transaction context as you load or execute a package.

Can a ControlParameter be used to supply a parameter for a stored procedure?

The code below is an attempt at using the value from a dropdownlist to feed into stored procedure outlined in the Select command. I have seen examples where the control parameter is used with a select command but nothing where the parameter has to be fed into a stored procedure.

<

asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString=".."
SelectCommand="procCAGetCustomerKPIs"SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameterName="iCustomerGroupId"ControlID="CustomerFilterList"PropertyName="SelectedValue"/>
</SelectParameters>
</asp:SqlDataSource>

cheers-jim.

Yes. Normally, I would use the designer, and hit "refresh parameters", it will then generate a list of all the parameters the stored procedure is expecting. From that point, change the parameters from type "None" to "Control", etc.|||Forgot to add autoPostBack=true to the dropdownlist control!!

can {oj be used in query? gives syntax error

Hi all
i am using a query
SELECT DISTINCT lcactivityT.activitycategory_id, code, sort_order, description, lccategoryT.code_alias, lccategoryT.description_alias FROM
{oj ActivityCategory AS categoryT INNER JOIN LicensedClientActivities AS lcactivityT ON lcactivityT.activitycategory_id = categoryT.activitycategory_id LEFT OUTER JOIN LicensedClientCategories AS lccategoryT ON categoryT.activitycategory_id = lccategoryT.activitycategory_id AND lccategoryT.licensedclient_id = '1'}
WHERE lcactivityT.licensedclient_id = '1'

This works fine in mssql query analyser but when i use it in code, using mssql jdbc driver, i am getting following error

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Syntax error a
t token ON , line 0 offset 84.
at com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown Source
......

i removed the white spaces and i also have the correct driver, what can i do?

Thanku{oj ... what does it exactly mean ?|||as far as i know {oj is a way to tell JDBC driver that we are using OUTER JOIN in the following, we can remove it if we want to but, we have whole lot of queries using it, previously we were using different server and driver, and now different server and driver and so throwing me the error. Any way to get rid of that|||{oj is an ODBC escape sequence to support outer joins (either left, right or full). Perhaps this driver has more difficulty understanding what to do with it, so removing it
might help. As for the outer/inner joins: placing parenthesis around the search conditions may help as well.|||hi all

Thanx for the reply, the problem got solved i just downloaded new microsoft mssql jdbc driver, and it recognized the syntax and its working fine now

Can "CREATE DATABASE" or "CREATE TABLE" be wrapped in transactions?

I have some code that dynamically creates a database (name is @.FullName) and
then creates a table within that database. Is it possible to wrap these
things into a transaction such that if any one of the following fails, the
database "creation" is rolledback. Otherwise, I would try deleting on error
detection, but it could get messy.

IF @.Error = 0
BEGIN
SET @.ExecString = 'CREATE DATABASE ' + @.FullName
EXEC sp_executesql @.ExecString
SET @.Error = @.@.Error
END

IF @.Error = 0
BEGIN
SET @.ExecString = 'CREATE TABLE ' + @.FullName + '.[dbo].[Image] ( [ID]
[int] IDENTITY (1, 1) NOT NULL, [Blob] [image] NULL , [DateAdded] [datetime]
NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]'
EXEC sp_executesql @.ExecString
SET @.Error = @.@.Error
END

IF @.Error = 0
BEGIN
SET @.ExecString = 'ALTER TABLE ' + @.FullName + '.[dbo].[Image] WITH
NOCHECK ADD CONSTRAINT [PK_Image] PRIMARY KEY CLUSTERED ( [ID] ) ON
[PRIMARY]'
EXEC sp_executesql @.ExecString
SET @.Error = @.@.Error
END"Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:bkmskk$grt$1$8302bc10@.news.demon.co.uk...
> I have some code that dynamically creates a database (name is @.FullName)
and
> then creates a table within that database. Is it possible to wrap these
> things into a transaction such that if any one of the following fails, the
> database "creation" is rolledback. Otherwise, I would try deleting on
error
> detection, but it could get messy.
> IF @.Error = 0
> BEGIN
> SET @.ExecString = 'CREATE DATABASE ' + @.FullName
> EXEC sp_executesql @.ExecString
> SET @.Error = @.@.Error
> END
> IF @.Error = 0
> BEGIN
> SET @.ExecString = 'CREATE TABLE ' + @.FullName + '.[dbo].[Image] ( [ID]
> [int] IDENTITY (1, 1) NOT NULL, [Blob] [image] NULL , [DateAdded]
[datetime]
> NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]'
> EXEC sp_executesql @.ExecString
> SET @.Error = @.@.Error
> END
> IF @.Error = 0
> BEGIN
> SET @.ExecString = 'ALTER TABLE ' + @.FullName + '.[dbo].[Image] WITH
> NOCHECK ADD CONSTRAINT [PK_Image] PRIMARY KEY CLUSTERED ( [ID] ) ON
> [PRIMARY]'
> EXEC sp_executesql @.ExecString
> SET @.Error = @.@.Error
> END

CREATE DATABASE can't be inside a transaction; CREATE TABLE can be. Instead
of rolling back on an error, you could just drop the database and start
again, although if you have a lot of objects, that could be time-consuming.
One other possibility is to restore an empty database with all the objects
but no data, which would be a lot faster, but may not be suitable depending
on what exactly you're doing.

Simon|||That is exactly what I want to do I guess. So I need to look up "restore"?

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:3f6f48f2$1_2@.news.bluewin.ch...
> "Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
> message news:bkmskk$grt$1$8302bc10@.news.demon.co.uk...
> > I have some code that dynamically creates a database (name is @.FullName)
> and
> > then creates a table within that database. Is it possible to wrap these
> > things into a transaction such that if any one of the following fails,
the
> > database "creation" is rolledback. Otherwise, I would try deleting on
> error
> > detection, but it could get messy.
> > IF @.Error = 0
> > BEGIN
> > SET @.ExecString = 'CREATE DATABASE ' + @.FullName
> > EXEC sp_executesql @.ExecString
> > SET @.Error = @.@.Error
> > END
> > IF @.Error = 0
> > BEGIN
> > SET @.ExecString = 'CREATE TABLE ' + @.FullName + '.[dbo].[Image] (
[ID]
> > [int] IDENTITY (1, 1) NOT NULL, [Blob] [image] NULL , [DateAdded]
> [datetime]
> > NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]'
> > EXEC sp_executesql @.ExecString
> > SET @.Error = @.@.Error
> > END
> > IF @.Error = 0
> > BEGIN
> > SET @.ExecString = 'ALTER TABLE ' + @.FullName + '.[dbo].[Image] WITH
> > NOCHECK ADD CONSTRAINT [PK_Image] PRIMARY KEY CLUSTERED ( [ID] ) ON
> > [PRIMARY]'
> > EXEC sp_executesql @.ExecString
> > SET @.Error = @.@.Error
> > END
> CREATE DATABASE can't be inside a transaction; CREATE TABLE can be.
Instead
> of rolling back on an error, you could just drop the database and start
> again, although if you have a lot of objects, that could be
time-consuming.
> One other possibility is to restore an empty database with all the objects
> but no data, which would be a lot faster, but may not be suitable
depending
> on what exactly you're doing.
> Simon

Calling VB dll method: sp_OAMethod Error

I have VB dll with a few methods that I need to access from my database.
Here's the code w/in sql server:
DECLARE @.retVal INT
DECLARE @.comHandle INT
DECLARE @.errorSource VARCHAR(8000)
DECLARE @.errorDescription VARCHAR(8000)
DECLARE @.retString VARCHAR(180)
--
--INSTATIATE OBJECT--
--
EXEC @.retVal = sp_OACreate 'DB_Encryption.Convert_String', @.comHandle OUTPUT
IF (@.retVal <> 0)
BEGIN
-- Trap errors if any
EXEC sp_OAGetErrorInfo @.comHandle, @.errorSource OUTPUT, @.errorDescription
OUTPUT
SELECT [Error Source] = @.errorSource, [Description] = @.errorDescription
RETURN
END
--CALL METHOD--
--
EXEC @.retVal = sp_OAMethod @.comHandle, 'Create_newStr', @.retString OUTPUT,
@.param='254616'
IF (@.retVal <> 0)
BEGIN
-- Trap errors if any
EXEC sp_OAGetErrorInfo @.comHandle, @.errorSource OUTPUT, @.errorDescription
OUTPUT
SELECT [Error Source] = @.errorSource, [Description] = @.errorDescription
RETURN
END
SELECT @.retString
EXEC sp_OADestroy @.comHandle
I know the object is being instantiated because no errors are returned if I
only exec that piece. However, when I try to call my method, I received the
following error:
Error Source:
ODSOLE Extended Procedure
Description:
Unknown name.
I've verified the method's name (and tried calling other ones).Couple of thoughts:
Did you test your DLL from VBScript using late binding?
Did you check if the value of comHandle non zero or non NULL?
What is the function prototype of the VB function you are calling? Some
constructs are not supported, you are limited IDispatch (COM Automation) at
best and have to substract the SQL XP limitation on top of that, since not
all objects are supported, for example types as Object and Variant are not
understood.
Is your object STA or MTA?
GertD@.SQLDev.Net
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:4D97C5FB-4976-487B-9D39-98E261C4BCFA@.microsoft.com...
>I have VB dll with a few methods that I need to access from my database.
> Here's the code w/in sql server:
> DECLARE @.retVal INT
> DECLARE @.comHandle INT
> DECLARE @.errorSource VARCHAR(8000)
> DECLARE @.errorDescription VARCHAR(8000)
> DECLARE @.retString VARCHAR(180)
> --
> --INSTATIATE OBJECT--
> --
> EXEC @.retVal = sp_OACreate 'DB_Encryption.Convert_String', @.comHandle
> OUTPUT
> IF (@.retVal <> 0)
> BEGIN
> -- Trap errors if any
> EXEC sp_OAGetErrorInfo @.comHandle, @.errorSource OUTPUT, @.errorDescription
> OUTPUT
> SELECT [Error Source] = @.errorSource, [Description] = @.errorDescription
> RETURN
> END
> --
> --CALL METHOD--
> --
> EXEC @.retVal = sp_OAMethod @.comHandle, 'Create_newStr', @.retString OUTPUT,
> @.param='254616'
> IF (@.retVal <> 0)
> BEGIN
> -- Trap errors if any
> EXEC sp_OAGetErrorInfo @.comHandle, @.errorSource OUTPUT, @.errorDescription
> OUTPUT
> SELECT [Error Source] = @.errorSource, [Description] = @.errorDescription
> RETURN
> END
> SELECT @.retString
> EXEC sp_OADestroy @.comHandle
> I know the object is being instantiated because no errors are returned if
> I
> only exec that piece. However, when I try to call my method, I received
> the
> following error:
> Error Source:
> ODSOLE Extended Procedure
> Description:
> Unknown name.
> I've verified the method's name (and tried calling other ones).
>|||I'm already using late binding (set obj = CreatObject) and it works when
called from an ASP page.
The value of column handle is not null. I have no idea what you are
referring to in the next point (function prototype).
The threading model is set to Apartment Threaded.
"Gert E.R. Drapers" wrote:

> Couple of thoughts:
> Did you test your DLL from VBScript using late binding?
> Did you check if the value of comHandle non zero or non NULL?
> What is the function prototype of the VB function you are calling? Some
> constructs are not supported, you are limited IDispatch (COM Automation) a
t
> best and have to substract the SQL XP limitation on top of that, since not
> all objects are supported, for example types as Object and Variant are not
> understood.
> Is your object STA or MTA?
> GertD@.SQLDev.Net
> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:4D97C5FB-4976-487B-9D39-98E261C4BCFA@.microsoft.com...
>
>

Calling stored procs based on a code table

I need to run stored procs based on a list in a code table. In other words, it reads the name of a stored proc from a table and runs it and does this for all the rows in the table.I think the ForEach loop container will do what I need and there is an ADO enumerator option but the documentation does not tell you how to use this.From what I can tell, you need to get a dataset into an SSIS variable first and then you plug the variable name into the ForEach ADO enumerator.Is that correct? If so, can someone tell me how to get a dataset into a variable?

Thanks

Perhaps kirk's blog will help you:

http://sqljunkies.com/WebLog/knight_reign/archive/2005/03/25/9588.aspx