how to remove reference of a table from all stored procs

Hello All
I am working on SQL SERVER 2000 DATABASE.
Is there any quick way to Drop the table say TABLE1 from the Database and
remove its reference from all stored procs(about 100 Stored Proc's)
Pls help me asap.
Regards,
EktaI'm afraid not. You need to work through your procedures and see which referenced the table and
adjust the proc code for each to your liking. You can get some help in finding which procedures
referencing your tables, for instance:
SELECT object_name(id) FROM syscomments WHERE text like '%procname%'
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"ekta" <enahar@.hotmail.com> wrote in message news:e02qRhY2HHA.5980@.TK2MSFTNGP04.phx.gbl...
> Hello All
> I am working on SQL SERVER 2000 DATABASE.
> Is there any quick way to Drop the table say TABLE1 from the Database and remove its reference
> from all stored procs(about 100 Stored Proc's)
> Pls help me asap.
> Regards,
> Ekta
>

how to remove reference of a table from all stored procs

Hello All
I am working on SQL SERVER 2000 DATABASE.
Is there any quick way to Drop the table say TABLE1 from the Database and
remove its reference from all stored procs(about 100 Stored Proc's)
Pls help me asap.
Regards,
EktaI'm afraid not. You need to work through your procedures and see which refer
enced the table and
adjust the proc code for each to your liking. You can get some help in findi
ng which procedures
referencing your tables, for instance:
SELECT object_name(id) FROM syscomments WHERE text like '%procname%'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"ekta" <enahar@.hotmail.com> wrote in message news:e02qRhY2HHA.5980@.TK2MSFTNGP04.phx.gbl...[v
bcol=seagreen]
> Hello All
> I am working on SQL SERVER 2000 DATABASE.
> Is there any quick way to Drop the table say TABLE1 from the Database and
remove its reference
> from all stored procs(about 100 Stored Proc's)
> Pls help me asap.
> Regards,
> Ekta
>[/vbcol]

how to reference/add a custom assembly in a report?

I created a custom assembly in VB2003 -- testClassLib. I gave it a namespace
of testNameSpace, class name is testLib with one public function that just
adds 10 to a number argument
NameSpace testNameSpace
Public Class testLib
Public addNum(n As Integer) As Integer
Return n + 10
End Class
End NameSpace
I tested this assembly in a VB2003 app, and it works fine.
In RS2003 I go to Report Properties to References and browse for my custom
assembly which would be testClassLib.dll. I select that in the Assembly name
box which will now display:
testClassLib, Version=1.0.2511.22986, Culture=neutral, PublicKeyToken=null
and then select testClassLib.testNameSpace.testLib and then use an
instance name of test1. This does not work. So I modified the classname to
testNameSpace.testLib -- still no go when I try to rebuild the report. When
I remove the reference and rebuild the report - it rebuilds fine without any
errors.
What do I need to do to add this custom assembly to my report? How to do
this?
Thanks,
RichIf you didn't already do so, you need to move the .dll to the following
directory for VS to recognize it:
Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies
For sql server to recognize it (once you deploy the report) you need to
add it to the following directory on the report server:
Program Files\Microsoft SQL Server\MSSQL.3\Reporting
Services\ReportServer\bin
Hope that helps.
On Nov 16, 4:10 pm, Rich <R...@.discussions.microsoft.com> wrote:
> I created a custom assembly in VB2003 -- testClassLib. I gave it a namespace
> of testNameSpace, class name is testLib with one public function that just
> adds 10 to a number argument
> NameSpace testNameSpace
> Public Class testLib
> Public addNum(n As Integer) As Integer
> Return n + 10
> End Class
> End NameSpace
> I tested this assembly in a VB2003 app, and it works fine.
> In RS2003 I go to Report Properties to References and browse for my custom
> assembly which would be testClassLib.dll. I select that in the Assembly name
> box which will now display:
> testClassLib, Version=1.0.2511.22986, Culture=neutral, PublicKeyToken=null
> and then select testClassLib.testNameSpace.testLib and then use an
> instance name of test1. This does not work. So I modified the classname to
> testNameSpace.testLib -- still no go when I try to rebuild the report. When
> I remove the reference and rebuild the report - it rebuilds fine without any
> errors.
> What do I need to do to add this custom assembly to my report? How to do
> this?
> Thanks,
> Rich|||Thank you very much. Yes, I pulled out my RS book, and it said the same
thing. But on the dev workstation I actually copied the dll to
C:\Program Files\Microsoft Sql Server\80\Tools\Report Designer
That is where I dropped the dll and where I reference it from the RS
designer, and now the desinger can see it and use it.
But the book was consistent with you on where you place the dll on the
server.
Thanks again for your reply.
Rich
"chris.teter@.gmail.com" wrote:
> If you didn't already do so, you need to move the .dll to the following
> directory for VS to recognize it:
> Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies
> For sql server to recognize it (once you deploy the report) you need to
> add it to the following directory on the report server:
> Program Files\Microsoft SQL Server\MSSQL.3\Reporting
> Services\ReportServer\bin
> Hope that helps.
> On Nov 16, 4:10 pm, Rich <R...@.discussions.microsoft.com> wrote:
> > I created a custom assembly in VB2003 -- testClassLib. I gave it a namespace
> > of testNameSpace, class name is testLib with one public function that just
> > adds 10 to a number argument
> >
> > NameSpace testNameSpace
> > Public Class testLib
> > Public addNum(n As Integer) As Integer
> > Return n + 10
> > End Class
> > End NameSpace
> >
> > I tested this assembly in a VB2003 app, and it works fine.
> >
> > In RS2003 I go to Report Properties to References and browse for my custom
> > assembly which would be testClassLib.dll. I select that in the Assembly name
> > box which will now display:
> >
> > testClassLib, Version=1.0.2511.22986, Culture=neutral, PublicKeyToken=null
> >
> > and then select testClassLib.testNameSpace.testLib and then use an
> > instance name of test1. This does not work. So I modified the classname to
> > testNameSpace.testLib -- still no go when I try to rebuild the report. When
> > I remove the reference and rebuild the report - it rebuilds fine without any
> > errors.
> >
> > What do I need to do to add this custom assembly to my report? How to do
> > this?
> >
> > Thanks,
> > Rich
>

How to reference to an image control

Hi,
I have a report. In one column i want to show a certain image depending the
value of another column. For example:
if text1 =1 than text10 = image1
elseif text1 =2 than text10 = image2
else text1 =3 than text10 = image3
end if
Is this possible? Also what i do recall is that in access if you set an
image for one row, the image goes for all rows (in a form). Does this applies
for SQL Reports also?
In general, how can i refence to a control (like a list, a rectangle, etc)
on a report?
Thnx,
StanleyIn the value of your image control, put the following:
=IIF(Fields!Sales.Value = True, "checkedbox-big.bmp", "blankbox.bmp")
in my solution, my bmp's are stored as part of the solution, so they are
deployed to the server as well.
"Stanley" wrote:
> Hi,
> I have a report. In one column i want to show a certain image depending the
> value of another column. For example:
> if text1 =1 than text10 = image1
> elseif text1 =2 than text10 = image2
> else text1 =3 than text10 = image3
> end if
> Is this possible? Also what i do recall is that in access if you set an
> image for one row, the image goes for all rows (in a form). Does this applies
> for SQL Reports also?
> In general, how can i refence to a control (like a list, a rectangle, etc)
> on a report?
> Thnx,
> Stanley
>|||Hi Pinolian,
Thnx for your response. I have added the pictures to the solution and
deployed it. But instead of seeing the images i get to see a 'red cross' like
it cannot find the image. Do you know why?
When the solution is deployed should i see the images in the report manager
also?
Thnx,
Stanley
"Pinolian" wrote:
> In the value of your image control, put the following:
> =IIF(Fields!Sales.Value = True, "checkedbox-big.bmp", "blankbox.bmp")
> in my solution, my bmp's are stored as part of the solution, so they are
> deployed to the server as well.
>
> "Stanley" wrote:
> > Hi,
> >
> > I have a report. In one column i want to show a certain image depending the
> > value of another column. For example:
> >
> > if text1 =1 than text10 = image1
> > elseif text1 =2 than text10 = image2
> > else text1 =3 than text10 = image3
> > end if
> >
> > Is this possible? Also what i do recall is that in access if you set an
> > image for one row, the image goes for all rows (in a form). Does this applies
> > for SQL Reports also?
> >
> > In general, how can i refence to a control (like a list, a rectangle, etc)
> > on a report?
> >
> > Thnx,
> >
> > Stanley
> >
> >|||Problem Solved with the image not showing. The picture name should be
mentioned without the extension, like:
=IIF(Fields!Sales.Value = True, "checkedbox-big", "blankbox")
"Pinolian" wrote:
> In the value of your image control, put the following:
> =IIF(Fields!Sales.Value = True, "checkedbox-big.bmp", "blankbox.bmp")
> in my solution, my bmp's are stored as part of the solution, so they are
> deployed to the server as well.
>
> "Stanley" wrote:
> > Hi,
> >
> > I have a report. In one column i want to show a certain image depending the
> > value of another column. For example:
> >
> > if text1 =1 than text10 = image1
> > elseif text1 =2 than text10 = image2
> > else text1 =3 than text10 = image3
> > end if
> >
> > Is this possible? Also what i do recall is that in access if you set an
> > image for one row, the image goes for all rows (in a form). Does this applies
> > for SQL Reports also?
> >
> > In general, how can i refence to a control (like a list, a rectangle, etc)
> > on a report?
> >
> > Thnx,
> >
> > Stanley
> >
> >

How to reference the primary key of a newly added record in trigger?

Please help me somebody solve my problem with my first :o trigger:

ALTER TRIGGER partner_update
ON dbo.partner
FOR UPDATE
AS
INSERT INTO partner (name) SELECT name FROM deleted
UPDATE invoice SET id_partner= *** WHERE id_partner = (SELECT id_partner FROM deleted)


*** - here I want to add a "reference" to the newly added record's automatically generated primary key (not to the updated!) Is it possible?I'd use:ALTER TRIGGER partner_update
ON dbo.partner
FOR UPDATE
AS
BEGIN
INSERT INTO partner (name)
SELECT name FROM deleted

UPDATE o
SET id_partner= i.id_partner
FROM dbo.invoice AS o
INNER JOIN deleted AS d
ON (d.id_partner = o.id_partner)
LEFT OUTER JOIN inserted AS i
ON (i.id_partner = o.id_partner)
ENDThis trigger takes a rather perverse view of the universe, allowing for the possiblity that some low-down, high-smelling, bundle-of-dung might possibly change the value of the id_partner column. The invoice table might or might not like that, since the trigger would then NULL out the invoice.id_partner column!

-PatP

How to reference the "Subtotal" cell in a mtraix report?

In a matrix report, column grouping, I added a "subtotal" to the column grouping by right-clicking on that cell and selected "Subtotal". Everything is working fine except when I tried to pass the parameter value to the drill-through report I need to set the parameter value if it is the "Subtotal" cell users click on it and I am not sure how to reference the "Subtotal' cell.

I have tried

=IIF(Fields!Cancer_Centre.Value="Subtotal",Parameters!DIMCancerCentreCancerCentre.Value,"[DIM_Cancer_Centre].[Cancer Centre].&[" & Fields!Cancer_Centre.Value & "]")

Thanks

I got it work afetr so many attempts. I wrote an expression to check the value if the cell is the "Subtotal" value or one of the value in the column grouping. Need to think outside the box to solve this.

Thanks.

How to reference items from different database?

Hi all,

I have two databases DB_External and DB_Internal.

I am writing some stored procedures (in DB_Internal) that reads from
the tables in DB_External. I execute my stored procedures from
DB_Internal.

Everytime I want to read the tables in DB_External, I have to refer to
the table as DB_External.dbo.tableName.

Is there a better way of declaring the database DB_External up front
in the stored procedure so I don't have to pre-fix "DB_External.dbo."
in all the table names?

Thanks in advance,
June Moore."June Moore" <jungewum@.yahoo.com.au> wrote in message
news:e5dfaf21.0310100030.3e0067a5@.posting.google.c om...
> Hi all,
> I have two databases DB_External and DB_Internal.
> I am writing some stored procedures (in DB_Internal) that reads from
> the tables in DB_External. I execute my stored procedures from
> DB_Internal.
> Everytime I want to read the tables in DB_External, I have to refer to
> the table as DB_External.dbo.tableName.
> Is there a better way of declaring the database DB_External up front
> in the stored procedure so I don't have to pre-fix "DB_External.dbo."
> in all the table names?
> Thanks in advance,
> June Moore.

You always have to fully qualify the name - there's no way around that. And
it's good practice to include the dbo owner prefix, even in the 'local'
database.

If you're concerned about moving code between servers where the DB names may
be different, you could consider a pre-processor script to 'fill in' or
find/replace the DB names in a code template, then create the procedure.

Simonsql

How to reference in an expression a cell in a table

Want to reference from a cell on one table to cell in another table.
What is the specific code to do this?=ReportItems!<TextboxName>.Value
<TextboxName> is case-sensitive!
Charles Kangai, MCDBA, MCT
"Smartbiz" wrote:
> Want to reference from a cell on one table to cell in another table.
> What is the specific code to do this?

How to reference different databases on different servers in one query

Hi,

I'm a novice to SQL Server, though I have some experience in Access.

My problem is this:

I have a table on my local database that I want to insert into a remove database on a different server

What are the steps neccessary?

My local server name is 'HOME\SQLEXPRESS' Local database: 'MyDBTest', table name: 'dbo.Courses' (the fields are: ID, CourseName, TeacherID)|||Take a look in Books On Line at a topic called Linked Servers. Although I am not 100% sure that they are supported in Express|||Using linked servers (first create it using te linked server procedures for that) you can issue this query:

SELECT *
FROM [Linkedservername].[ku.univ].dbo.Courses

HTH, Jens Suessmeyer,

http://www.sqlserver2005.de
|||

I think I'm not using the right syntax

I've created the linked server by:

sp_addlinkedserver @.server='REMOTESRV', @.provider='SQLOLEDB',@.srvproduct='',@.datasrc='129.113.271.58\ku.univ'

The log was: Command(s) completed successfully.

I then tried to issue an SQL Statement as you have adviced:

SELECT *

FROM [REMOTESRV].[ku.univ].dbo.Courses

but the log was:

OLE DB provider "SQLNCLI" for linked server "REMOTESRV" returned message "Login timeout expired".

OLE DB provider "SQLNCLI" for linked server "REMOTESRV" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".

Msg 65535, Level 16, State 1, Line 0

SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].

I know that the server allows remote connections: when I connect to it using Object browser I can issue INSERT queries - no problem

Maybe I have a problem with syntax

Thanx a lot

|||Check out the 'Linked Servers' topic in the Books Online|||

Hi,

this here:

,@.datasrc='129.113.271.58\ku.univ'

Should be the instance to connect to, so normally this has to be written in the syntax of:

Servername\Instancename,Portnumber (where as the servername can be exchanged by the ip adress and the portnumber (can be left out if you didn′t choose another one that the default one [or you have SQl Server 2005 at the other end using dynamic port and installed the SQL Server Browser]) and the instancename (which can be left out if you choose the default instance on the server.

So in your case for the default port and the default instance, this should be just:

,@.datasrc='129.113.271.58'

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Hi, Jens

Thanx a lot for helping, I've changed the @.datasrc as you wrote but got a different error this time:

OLE DB provider "SQLNCLI" for linked server "remotesrv2" returned message "Login timeout expired".

OLE DB provider "SQLNCLI" for linked server "remotesrv2" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".

Msg 53, Level 16, State 1, Line 0

Named Pipes Provider: Could not open a connection to SQL Server [53].

I'd really appreciate your help on this one.

Thanx in advance

|||

Other Ideas, anybody?

I'm pretty stuck here, I've read the documentation, did as it said but still grt an error.

Thanx!

|||

http://blogs.msdn.com/sql_protocols/archive/2005/10/22/483684.aspx

HTH

How to reference different databases on different servers in one query

Hi,

I'm a novice to SQL Server, though I have some experience in Access.

My problem is this:

I have a table on my local database that I want to insert into a remove database on a different server

What are the steps neccessary?

My local server name is 'HOME\SQLEXPRESS' Local database: 'MyDBTest', table name: 'dbo.Courses' (the fields are: ID, CourseName, TeacherID)

The remote SQL Server IP is: 129.113.271.58 (I've changed the real IP) The name of the database on this remote server is: 'ku.univ', and the remote table name I would like to update is 'dbo.Courses' (the table has the same fields as the local one).

I use SQL Server Express Edition

Please help!!

Thank you in advance

Michael

Any ideas?

I'm really stuck here - I am able to connect to the remote server and browse the database, but how do I make an INSERT query to the remote table from my local table?

In Access you could just put any table (local and linked) in one query - there has to be a way to do it in SQL Server

Thanx

|||

There are a couple of options for you. You could use BCP to export the table on the local server to a text file, then use the same utility to insert into the remote server. You can also use a linked server.

To setup a linked server run these commands from sqlcmd, replace the appropriate parameters:

exec sp_addlinkedserver 'RemoteServerName', N'SQL Server'
exec sp_addlinkedsrvlogin 'RemoteServerName',@.locallogin=NULL /*any login*/, @.rmtuser = 'remoteusername', @.rmtpassword='rmtpassword'

Then you will be able to run a query such as this when connected to your local server:

insert [remoteserver].[ku.univ].[dbo].[Tableneme] (columns...)

select columns... from localtable

I'm assuming here that the remote server is also SQL Server. If not you'll need to modify the sp_addlinkedserver call to specify the proper connection string.

How to reference datasets in custom code

Can datasets associated with a report be referenced in custom code? If so,
how? I've tried referencing them like one does in expressions in the report,
e.g., Fields!Personal_Amt_Paid.Value, but this does not work. I get the
error
"There is an error on line 6 of custom code: [BC30469] Reference to a
non-shared member requires an object reference."I doubt you can do it... The way you reference a parameter in code is
Report.Parameters!whatever...
So try Report.Dataset.something... How would it know which row?... probably
will not work...
you'll just have to pass the values in as parameters.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Bob Kilmer" <Bob.Kilmer@.S.com> wrote in message
news:uODIYYrnFHA.1048@.tk2msftngp13.phx.gbl...
> Can datasets associated with a report be referenced in custom code? If so,
> how? I've tried referencing them like one does in expressions in the
> report,
> e.g., Fields!Personal_Amt_Paid.Value, but this does not work. I get the
> error
> "There is an error on line 6 of custom code: [BC30469] Reference to a
> non-shared member requires an object reference."
>

How to reference a text box in an insert statement

I am trying to take a value from at textbox and insert into a database. I think I need to convert the text to an integer - but I keep getting an error message saying I'm not allowed to use column names. Basically, I need to figure out how to take user input from these tet boxes and insert them into a database. Any ideas would be welcomed. the exact error is at the bottom the code.

Here is the code:

Imports System.Data
Imports System.Data.SqlClient
Partial Class County_ConversionTest
Inherits System.Web.UI.Page

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim v1 As Integer
Dim v2 As Integer
'Dim v3 As Integer
v1 = Integer.Parse(TextBox1.Text)
v2 = Integer.Parse(TextBox2.Text)
TextBox3.Text = (v1 + v2)

End Sub

Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim strconn As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Inetpub\wwwroot\HCBS\App_Data\DivAging_Data.MDF;Integrated Security=True;Connect Timeout=30;User Instance=True"
Dim cnnDivAging As SqlConnection = New SqlConnection(strconn)
Dim v1 As Integer
Dim v2 As Integer
Dim v3 As Integer
v1 = CInt(TextBox1.Text)
v2 = CInt(TextBox2.Text)
v3 = CInt(TextBox3.Text)

Dim strsql As String = "INSERT INTO tblTest (Name, fv1,fv2,fv3)" + _
"Values ('George',v1,v2,v3)"

Dim cmdUpdates As SqlCommand = New SqlCommand(strSQL, cnnDivAging)
cmdUpdates.Connection = cnnDivAging
cnnDivAging.Open()
cmdUpdates.CommandType = CommandType.Text
cmdUpdates.ExecuteNonQuery()
cnnDivAging.Close()
End Sub
End Class

Here is the error message -

The name "v1" is not permitted in this context. Valid expressions areconstants, constant expressions, and (in some contexts) variables.Column names are not permitted.hey jcahill,
try this:

Dim strsql As String = "INSERT INTO tblTest (Name, fv1,fv2,fv3)" + _
"Values ('George'," & v1 & "," & v2 & "," v3 & ")"

v1, v2, v3 are not there in sql, you just want the values in the sql.
Hope it helps.|||

You should get in the habit of doing it the right way:

Dim strsql AS String="INSERT INTO tblTest (Name, fv1,fv2,fv3)" + _
"Values ('George',@.v1,@.v2,@.v3)"
Dim cmdUpdates As SqlCommand = New SqlCommand(strSQL, cnnDivAging)
' cmdUpdates.Connection = cnnDivAging -- Redundant
cmdUpdates.Parameters.Add("@.v1",sqldbtype.int32).value=v1
cmdUpdates.Parameters.Add("@.v2",sqldbtype.int32).value=v2
cmdUpdates.Parameters.Add("@.v3",sqldbtype.int32).value=v3
cnnDivAging.Open()
' cmdUpdates.CommandType = CommandType.Text -- Redundant and should be done before the open to minimize the amount of time the connection is "open"
cmdUpdates.ExecuteNonQuery()
cnnDivAging.Close()

sql

How to reference a shared schedule by Name ?

I'm generating a rss script for a report that will be depolyed to a new
server. If the report uses a shared schedule for its history snapshot then
in the script I need to pass a ScheduleReference to SetReportHistoryOptions.
What's the easiest way of interogating the new server to see if a Shared
Schedule with the same name exists and get a reference to it.
GetScheduleProperties needs a ScheduleID but this will be different between
the 2 servers. Since Shared Schedule names have to be unique why can't we
create a ScheduleReference based on the name of the Shared schedule? The
only other way I can think of is to call ListSchedules and then loop through
looking for one with the same name but this seems inefficient on a per
report basis.
--
Cheers,
JasYes, it is inefficient to have to loop through all the schedules,
unfortunately it is the only way to do it. By the way, how do you guarantee
that schedules with the same name will have the same reoccurrence pattern?
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:%230j1zQEFFHA.1836@.tk2msftngp13.phx.gbl...
> I'm generating a rss script for a report that will be depolyed to a new
> server. If the report uses a shared schedule for its history snapshot then
> in the script I need to pass a ScheduleReference to
> SetReportHistoryOptions. What's the easiest way of interogating the new
> server to see if a Shared Schedule with the same name exists and get a
> reference to it. GetScheduleProperties needs a ScheduleID but this will be
> different between the 2 servers. Since Shared Schedule names have to be
> unique why can't we create a ScheduleReference based on the name of the
> Shared schedule? The only other way I can think of is to call
> ListSchedules and then loop through looking for one with the same name but
> this seems inefficient on a per report basis.
> --
> Cheers,
> Jas
>|||By having standards and trying to stick to them :-) Shared schedules would
be set up by a DBA so as a project moves throught the environments, if a
need for a schedule is identified, the DBA should check to see if a suitable
shared schedule exists and get the developers to use that. If it doesn't
then create one and include it as part of the deployment package.
If I want to be ultra careful then when scripting a report on Server A that
reference a shared schedule I'd include the shared schedule definition in
the script so that if I do find one on Server B with the same name (which I
always should - in theory!) I can check the definitions are the same or
issue a warning/abort report creation. I could also optionally create the
schedule if it doesn't exist (but I'd rather not do that in the report
script)
At the simplest level i.e. just the report it's fairly easy to transport
across environments but as I keep adding all the various options and
properties (especially the scheduling stuff) to my scripting tool it's
getting more and more complex :-)
--
Cheers,
Jas
"Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
news:eR24d$TFFHA.2600@.TK2MSFTNGP09.phx.gbl...
> Yes, it is inefficient to have to loop through all the schedules,
> unfortunately it is the only way to do it. By the way, how do you
> guarantee that schedules with the same name will have the same
> reoccurrence pattern?
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:%230j1zQEFFHA.1836@.tk2msftngp13.phx.gbl...
>> I'm generating a rss script for a report that will be depolyed to a new
>> server. If the report uses a shared schedule for its history snapshot
>> then in the script I need to pass a ScheduleReference to
>> SetReportHistoryOptions. What's the easiest way of interogating the new
>> server to see if a Shared Schedule with the same name exists and get a
>> reference to it. GetScheduleProperties needs a ScheduleID but this will
>> be different between the 2 servers. Since Shared Schedule names have to
>> be unique why can't we create a ScheduleReference based on the name of
>> the Shared schedule? The only other way I can think of is to call
>> ListSchedules and then loop through looking for one with the same name
>> but this seems inefficient on a per report basis.
>> --
>> Cheers,
>> Jas
>>
>

How to reference a shared datasource already on report server for data driven subscription

I see there's MS documentaion on creating a data driven subscription.
The example shows to create a data source for the data retrieval plan.
see
http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.createdatadrivensubscription.aspx
I would like to set the data source to a share data source on the
server. How should I implement that?
I have tried to use the following but failed:
...
delivery.Item = rs.GetDataSourceContents("/Data Sources/
testDataSource");
...I have worked this out: By creating a DataSourceReference object:
DataSourceReference reference = new
DataSourceReference();
reference.Reference = "/Data Sources/testDataSource";
delivery.Item = reference;
But then I got error like:
The current action cannot be completed because the user data source
credentials that are required to execute this report are not stored in
the report server database.
My credential information is stored with data source. If I manually
create the data-driven subscription from Report Manager, and use the
same shared data source, I wouldn't get this error. Anyone knows why?
Thanks

How to reference a previous field Alias in TSQL as in Jet SQL?

My question is simple, I'd like to do something I do in Jet ANSI-89 SQL. Mind you I'm just adding numbers here - they are not actual columns in 'SomeTable'

SELECT 1 AS A, 2 AS B, A+B AS C
FROM SomeTable

The Jet engine evaluates and does arithmetic on the Aliased column names - handy when they contain their own functions. The resultset would show:

A B C
1 2 3

However from what I can tell SQL Server 2005 is not picking this up. Is their an equivalent?

The Jet syntax is non-standard SQL syntax so it will not work in SQL Server. You will have to use a derived table or CTE (in SQL Server 2005) or use a view or computed column in the table (if the expression involves columns from a single table).

select A, B, A+B as C

from (select 1 as A, 2 as B

from sometable

) as t

with t as (

select 1 as A, 2 as B

from sometable

)

select A, B, A+B as C

from t

How to reference a local temporary table in the @query parm of xp_sendmail

Hi,
Can anyone tell me if a local temporary table can be referenced in the @.quer
y
parameter of the extended procedure xp_sendmail and if so, how?
When I execute the following statements in Query Analyzer, I get the error
message provided below.
CREATE TABLE #TestEmailFile (TestEmailRow varchar(500) null)
INSERT INTO #TestEmailFile VALUES( 'Hello')
EXEC master.dbo.xp_sendmail @.recipients = 'EmailAddress',
@.query = 'SELECT * FROM #TestEmailFile',
@.subject = 'You have mail',
@.message = 'See attachment',
@.attach_results = 'TRUE',
@.width = 500
ODBC error 208 (42S02) Invalid object name '#TestEmailFile'.
Please help!
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200602/1The sendmail executes in a different execution context than the other
connection. If you can guarantee > 1 connection will execute the statement,
then you can use a global temp table (## prefix). You could implement a
locking mechanism using app locks or a semaphore table if you had too, but a
better way to go would be to create a permanent table instead of a temp
table, include the spid in the primary key and everything should work better
(assuming the structure of the table is stable:
use db
go
create table dbo.testEmailFile
(
spid int,
key int,
otherCols int
)
Then
delete from dbo.testEmailFile where spid = @.@.spid --just in case something
fails
--and second delete doesnt happen
insert into dbo.testEmailFile
select @.@.spid, etc
EXEC master.dbo.xp_sendmail @.recipients = 'EmailAddress',
@.query = 'SELECT * FROM db.dbo.TestEmailFile',
@.subject = 'You have mail',
@.message = 'See attachment',
@.attach_results = 'TRUE',
@.width = 500
delete from dbo.testEmailFile where spid = @.@.spid --good data steward
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Cismail via webservertalk.com" <u14416@.uwe> wrote in message
news:5c5275146e3b0@.uwe...
> Hi,
> Can anyone tell me if a local temporary table can be referenced in the
> @.query
> parameter of the extended procedure xp_sendmail and if so, how?
> When I execute the following statements in Query Analyzer, I get the error
> message provided below.
> CREATE TABLE #TestEmailFile (TestEmailRow varchar(500) null)
> INSERT INTO #TestEmailFile VALUES( 'Hello')
> EXEC master.dbo.xp_sendmail @.recipients = 'EmailAddress',
> @.query = 'SELECT * FROM #TestEmailFile',
> @.subject = 'You have mail',
> @.message = 'See attachment',
> @.attach_results = 'TRUE',
> @.width = 500
>
> ODBC error 208 (42S02) Invalid object name '#TestEmailFile'.
> Please help!
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200602/1|||Very helpful. Thanks indeed!
Louis Davidson wrote:
>The sendmail executes in a different execution context than the other
>connection. If you can guarantee > 1 connection will execute the statement
,
>then you can use a global temp table (## prefix). You could implement a
>locking mechanism using app locks or a semaphore table if you had too, but
a
>better way to go would be to create a permanent table instead of a temp
>table, include the spid in the primary key and everything should work bette
r
>(assuming the structure of the table is stable:
>use db
>go
>create table dbo.testEmailFile
>(
> spid int,
> key int,
> otherCols int
> )
>Then
>delete from dbo.testEmailFile where spid = @.@.spid --just in case something
>fails
> --and second delete doesnt happen
>insert into dbo.testEmailFile
>select @.@.spid, etc
>EXEC master.dbo.xp_sendmail @.recipients = 'EmailAddress',
> @.query = 'SELECT * FROM db.dbo.TestEmailFile',
> @.subject = 'You have mail',
> @.message = 'See attachment',
> @.attach_results = 'TRUE',
> @.width = 500
>delete from dbo.testEmailFile where spid = @.@.spid --good data steward
>----
-
>Louis Davidson - http://spaces.msn.com/members/drsql/
>SQL Server MVP
>"Arguments are to be avoided: they are always vulgar and often convincing."
>(Oscar Wilde)
>
>[quoted text clipped - 17 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200602/1

How to reference a dataset passed to script

Hi, I have a dataset created via the Execute SQL Statement control and wish to pass it into a script control that within the vb.net script I wish to perform different functions to it.

The main goal is to take the dataset loop through it and record values into an array then take that array and loop through that so I can send an email containing the information within the array.

Can anyone help?

If inside a script component the common method is to use row

So you can do row.xxx where xxx is the field name... then using row.next row etc you can loop and such.

|||Ok, how do we reference the dataset? I appreciate your answer Dave but its very vague.

So I have the execute SQL statement create a dataset named "dataset1" using an ado.net object type.

Then within the script component how to we initialize, and work with "dataset1"? I wish to loop through "dataset1" and perform various functions on it. Sure the row access sounds great for the looping part but what of the other parts?|||Use a foreach loop to open that ADO recordset and loop through it. There are plenty of examples in this forum.|||I know that method and that won't work for what i'm trying to do, or at least i don't think so.

All I want to know is how to reference a dataset from an execute sql statement within a script component. I wish to reference this dataset within the script component and loop through and create an array with specific information. Then I want to use the array as loop through that to create a body of an email. So PLEASE answer the question of ->>>>>

How do I reference a dataset after it being populated via an Execute SQL Statement?

<<<<<--
I tried
dim results as dataset
dim reader as datatablereader
reader = results.tables(0).createdatareaders
do while reader.read
execute statements
loop

I get the error that reader is referenced to no table.|||

I misunderstood your initial question, I thought you needed basic access. I was thinking you used the execute sql command component to create the data set. So if you execute the sql, and then have the output of that component connect to a script component you can just use the Row call I mentioned, because it automatically ties to the "input" dataset from the sql execute component.

|||Here is my code. I have OBJ_NULL_SET as the result set object from the execute sql components output.

Public Sub Main()
'Assume success

Try
Dim results As DataSet

results = CType(ReadVariable("OBJ_NULL_SET"), DataSet)
For Each tbl As DataTable In results.Tables
System.Windows.Forms.MessageBox.Show(tbl.TableName)
Next

Catch ex As Exception
Dts.Events.FireError(-1, "N/A", ex.ToString(), "", 0)
End Try
Dts.TaskResult = Dts.Results.Success

End Sub

Private Function ReadVariable(ByVal varName As String) As Object
Dim result As Object

Try
Dim vars As Variables
Dts.VariableDispenser.LockForRead(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
result = vars(varName).Value
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try

Return result
End Function

When the msgbox pops up it says Table with nothing else. It does not display the name of the dataset so i'm sure that i've done something wrong where the dataset is not being referenced properly.|||

You are already referencing the dataset by iterating through the DataTables. The first table's name so happens to be "Table", and that's what the message box call shows.

So, for the only DataTable of the DataSet, iterate through the rows using standard ADO.NET. For example, call this function right after you're first MsgBox() call, and it will display the column names and stringify the data. You're refencing the data from the Execute SQL task at this point, and what is done beyond that is up to your imagination.

Private Sub DisplayTable(ByVal tbl As DataTable)

Dim dr As DataRow, dc As DataColumn

Dim sb As System.Text.StringBuilder = New System.Text.StringBuilder()

For colNum As Integer = 0 To tbl.Columns.Count - 1

dc = tbl.Columns(colNum)

sb.Append(dc.ColumnName).Append(",")

Next

Dts.Events.FireInformation(1, "Columns", sb.ToString(), "", 0, True)

For rowNum As Integer = 0 To tbl.Rows.Count - 1

sb.Remove(0, sb.Length)

dr = tbl.Rows(rowNum)

For itemOffset As Integer = 0 To dr.ItemArray.Length - 1

sb.Append(dr.ItemArray(itemOffset)).Append(",")

Next

Dts.Events.FireInformation(1, "Data", sb.ToString(), "", 0, True)

Next

End Sub

sql

How to re-establish subscriptions DB using Backup of Subscriber Db?

BOL 2000 makes reference to recovering a subscriber DB using a backup of the subscriber DB but does not appear to say how to go about doing it. What I need is enough info on how to set-up a backup and recovery strategy that can be implemented in a production environment. Snapshot recovery is no longer acceptable and there are lots of identity columns. Push publication.

It should be rather cook-book and I am hoping you can point me to a doc that will allow me to achieve this. It should be rather cook-book and I am hoping you can point me to a doc that will allow me to achieve this.

Thanks

Michael

Hi,

This KB article lays out the steps required to setup subscription using backup: http://support.microsoft.com/kb/320499.

Peng

|||

The article you referenced does not address using Subscriber backups. Only Publisher backups. A nice idea, but not what I asked for.

Thanks

|||

OK. So I assume you are looking for a recovery plan for your subscription database. You can take a look at the documents in SQL 2005 BOL http://msdn2.microsoft.com/en-us/library/ms152560.aspx. I know it is not exactly what you ask for because the document it is for SQL 2005. But they are very close. Basically, the steps are as following:

1) check if the backup is older than the retention period. If yes, reinit the subscription. Otherwise, goto next step

2) restore the backup

3) If you have pull subscrption, check it. You may need to reinit if the subscription information is not up-to-date

4) restart the distribution agent

Peng

sql