INFORMATION_SCHEMA.PARAMETERS

Is there any way to find out the stored procedure output paramters like I
can findout the input params through following query but I want to findout
the output params list...
SELECT Specific_Name, Parameter_Name FROM INFORMATION_SCHEMA.PARAMETERS
thanks in advance.
select
PARAMETER_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
PARAMETER_MODE,
NUMERIC_PRECISION,
NUMERIC_SCALE
from INFORMATION_SCHEMA.PARAMETERS
where specific_name = @.chvProcName
order by ordinal_position
look for PARAMETER_MODE it will have a value of OUT
http://sqlservercode.blogspot.com/
"Rogers" wrote:

> Is there any way to find out the stored procedure output paramters like I
> can findout the input params through following query but I want to findout
> the output params list...
> SELECT Specific_Name, Parameter_Name FROM INFORMATION_SCHEMA.PARAMETERS
> thanks in advance.
>
>
>
|||No, I mean ... let's assume this is the stored procedure right ... I need
the output like SiteID,Site,Median Wait Time, 90% Wait Time,Average Wait
Time...
CREATE PROCEDURE [dbo].[SPSelectTotalServices]
@.ModalityTypeID INT = NULL,
@.LHIN_Code INT = NULL,
@.Lan CHAR(2) = 'EN'
AS
IF (@.Lan = 'EN')
BEGIN
SELECT
MF_ModalityTypeID AS SiteID,
(
SELECT
MT_Alias
FROM tblModalityType
WHERE MT_ModalityTypeID = MF_ModalityTypeID
) AS Site,
CEILING(sum(MF_Median)) AS 'Median Wait Time',
CEILING(sum(MF_90Percentile)) AS '90% Wait Time',
CEILING(sum(MF_AveWaitTime)) AS 'Average Wait Time'
FROM tblModalityFacility
WHERE (@.ModalityTypeID IS NULL OR MF_ModalityTypeID = @.ModalityTypeID)
AND
(@.LHIN_Code IS NULL OR MF_LHIN_Code = @.LHIN_Code)
AND MF_ModalityTypeID IS NOT NULL
GROUP BY MF_ModalityTypeID
ORDER BY 2
END
GO
Is there any way ?
Thanks in advance.
"SQL" <SQL@.discussions.microsoft.com> wrote in message
news:679EA43E-3A28-4640-BD41-DAB0E4A4EDE1@.microsoft.com...[vbcol=seagreen]
> select
> PARAMETER_NAME,
> DATA_TYPE,
> CHARACTER_MAXIMUM_LENGTH,
> PARAMETER_MODE,
> NUMERIC_PRECISION,
> NUMERIC_SCALE
> from INFORMATION_SCHEMA.PARAMETERS
> where specific_name = @.chvProcName
> order by ordinal_position
> look for PARAMETER_MODE it will have a value of OUT
> http://sqlservercode.blogspot.com/
>
> "Rogers" wrote:
|||Thats not an output parameter but a resultset
You could use sp_helptext and parse thru that
http://sqlservercode.blogspot.com/
"Rogers" wrote:

> No, I mean ... let's assume this is the stored procedure right ... I need
> the output like SiteID,Site,Median Wait Time, 90% Wait Time,Average Wait
> Time...
> CREATE PROCEDURE [dbo].[SPSelectTotalServices]
> @.ModalityTypeID INT = NULL,
> @.LHIN_Code INT = NULL,
> @.Lan CHAR(2) = 'EN'
> AS
> IF (@.Lan = 'EN')
> BEGIN
> SELECT
> MF_ModalityTypeID AS SiteID,
> (
> SELECT
> MT_Alias
> FROM tblModalityType
> WHERE MT_ModalityTypeID = MF_ModalityTypeID
> ) AS Site,
> CEILING(sum(MF_Median)) AS 'Median Wait Time',
> CEILING(sum(MF_90Percentile)) AS '90% Wait Time',
> CEILING(sum(MF_AveWaitTime)) AS 'Average Wait Time'
> FROM tblModalityFacility
> WHERE (@.ModalityTypeID IS NULL OR MF_ModalityTypeID = @.ModalityTypeID)
> AND
> (@.LHIN_Code IS NULL OR MF_LHIN_Code = @.LHIN_Code)
> AND MF_ModalityTypeID IS NOT NULL
> GROUP BY MF_ModalityTypeID
> ORDER BY 2
> END
> GO
> Is there any way ?
> Thanks in advance.
> "SQL" <SQL@.discussions.microsoft.com> wrote in message
> news:679EA43E-3A28-4640-BD41-DAB0E4A4EDE1@.microsoft.com...
>
>
|||what are you trying to do with the output?
In any case, look up SET FRMONLY in books on line - that should get you there.
for instance:
SET FMTONLY ON
GO
SELECT *
FROM SPSelectTotalServices
GO
(remember to SET FMTONLY OFF when you're done!)
"Rogers" <Rogers@.mailstuff.com> wrote in message news:O7dV$StvFHA.2932@.TK2MSFTNGP10.phx.gbl...
> No, I mean ... let's assume this is the stored procedure right ... I need the output like SiteID,Site,Median Wait Time, 90% Wait
> Time,Average Wait Time...
> CREATE PROCEDURE [dbo].[SPSelectTotalServices]
> @.ModalityTypeID INT = NULL,
> @.LHIN_Code INT = NULL,
> @.Lan CHAR(2) = 'EN'
> AS
> IF (@.Lan = 'EN')
> BEGIN
> SELECT
> MF_ModalityTypeID AS SiteID,
> (
> SELECT
> MT_Alias
> FROM tblModalityType
> WHERE MT_ModalityTypeID = MF_ModalityTypeID
> ) AS Site,
> CEILING(sum(MF_Median)) AS 'Median Wait Time',
> CEILING(sum(MF_90Percentile)) AS '90% Wait Time',
> CEILING(sum(MF_AveWaitTime)) AS 'Average Wait Time'
> FROM tblModalityFacility
> WHERE (@.ModalityTypeID IS NULL OR MF_ModalityTypeID = @.ModalityTypeID) AND
> (@.LHIN_Code IS NULL OR MF_LHIN_Code = @.LHIN_Code)
> AND MF_ModalityTypeID IS NOT NULL
> GROUP BY MF_ModalityTypeID
> ORDER BY 2
> END
> GO
> Is there any way ?
> Thanks in advance.
> "SQL" <SQL@.discussions.microsoft.com> wrote in message news:679EA43E-3A28-4640-BD41-DAB0E4A4EDE1@.microsoft.com...
>
sql

INFORMATION_SCHEMA Views and Indexed Views

Hi,
I wanted to write some stored procedures to help me manage my indexed views
in SQL2000/2008. Since I wanted to follow the advice to use the
INFORMATION_SCHEMA views instead of system tables/catalog views. I have
this query that will run in both 2000 and 2008 and it correctly finds my
indexed views:
select *
from sysobjects
where type = 'V'
and id in (select id from sysindexes);
However, if I run this query in either 2000 or 2008 , it returns nothing:
select * from INFORMATION_SCHEMA.VIEWS
where TABLE_NAME in (
select TABLE_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
)
The SQL 2008 BOL states "Returns one row for each column that is constrained
as a key in the current database.", and SQL 2000 BOL state "Contains one row
for each column, in the current database, that is constrained as a key."
Does anyone have any sage advice on this topic, or should I post it as a
Connect issue for SQL 2008?
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org"Daniel Jameson" <danjam47@.newsgroup.nospam> wrote in message
news:OpZ6jOpEIHA.936@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I wanted to write some stored procedures to help me manage my indexed
> views in SQL2000/2008. Since I wanted to follow the advice to use the
> INFORMATION_SCHEMA views instead of system tables/catalog views. I have
> this query that will run in both 2000 and 2008 and it correctly finds my
> indexed views:
> select *
> from sysobjects
> where type = 'V'
> and id in (select id from sysindexes);
> However, if I run this query in either 2000 or 2008 , it returns nothing:
> select * from INFORMATION_SCHEMA.VIEWS
> where TABLE_NAME in (
> select TABLE_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
> )
> The SQL 2008 BOL states "Returns one row for each column that is
> constrained as a key in the current database.", and SQL 2000 BOL state
> "Contains one row for each column, in the current database, that is
> constrained as a key."
> Does anyone have any sage advice on this topic, or should I post it as a
> Connect issue for SQL 2008?
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
>
>
The INFORMATION_SCHEMA describes only the logical features of the database:
tables, columns, constraints. Not indexes because they are a physical
implementation construct and aren't part of standard SQL like the
INFORMATION_SCHEMA.
For index information you need sys.indexes and sys.index_columns, or
dbo.sysindexes and dbo.sysindexkeys. That's unless the index is one that
supports a constraint, in which case the same information will be in
INFORMATION_SCHEMA.
--
David Portas|||Someone will point out what's going on and for every issue you encounter
they'll tell you how you screwed up (though usually politely - unless you
get celko). I'll probably get blasted for this post. However, personally, I
think INFORMATION_SCHEMA sucks and that the SQL Server catalog tables are
far, far superior and thought out much better (esp. 2005).
While I'll blame Microsoft for the poor docs on it, I don't blame them for
the bulk of my issues with it as they just implimented the ANSI standard. I
just don't like it and don't think it was well thought out.
Jay
"Daniel Jameson" <danjam47@.newsgroup.nospam> wrote in message
news:OpZ6jOpEIHA.936@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I wanted to write some stored procedures to help me manage my indexed
> views in SQL2000/2008. Since I wanted to follow the advice to use the
> INFORMATION_SCHEMA views instead of system tables/catalog views. I have
> this query that will run in both 2000 and 2008 and it correctly finds my
> indexed views:
> select *
> from sysobjects
> where type = 'V'
> and id in (select id from sysindexes);
> However, if I run this query in either 2000 or 2008 , it returns nothing:
> select * from INFORMATION_SCHEMA.VIEWS
> where TABLE_NAME in (
> select TABLE_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
> )
> The SQL 2008 BOL states "Returns one row for each column that is
> constrained as a key in the current database.", and SQL 2000 BOL state
> "Contains one row for each column, in the current database, that is
> constrained as a key."
> Does anyone have any sage advice on this topic, or should I post it as a
> Connect issue for SQL 2008?
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
>
>

InfoPath 2007 and endpoint yielding HTTP authentication failed.

I created a HelloWorld like example using InforPath 2007 and endpoint with SQL Server 2005. Very simple example.

I created a stored procedure that took in one parameter, a datetime field. This parameter is then inserted into a db table that has two columns, an id column and a datetime column.

I took the store procedure just discribed to create an endpoint. Cool. The WSDL is showing, I'm feeling good.

I go to InfoPath and make it so that submit a datetime to the webmethod associated with the endpoint. The data table shows one more row has been added and it is the datetime I sent from InfoPath. YEA!! I go to the Event View and see that I have an error message. The message is HTTP authenticaiton failed. [CLIENT: 10.8.2.26]. Bummer!!

This does not make sense to me. I am able to update the targeted database table but I get an exception. I do not like exceptions nor warnings without an explanation. Does anyone have an idea why this is happening?

The endpoint code looks like: <code snippet>

CREATE ENDPOINT [GeraldTesting_endpoint]
AUTHORIZATION
STATE=STARTED
AS HTTP (PATH=N'/testEndPoint',
PORTS = (CLEAR),
AUTHENTICATION = (INTEGRATED),
SITE=N'*',
CLEAR_PORT = 8080,
COMPRESSION=DISABLED)
FOR SOAP (
WEBMETHOD 'testEndPoint'
( NAME=N'[geraldstest].[dbo].[Gerald_Test_Proc]'
, SCHEMA=STANDARD,
FORMAT=ALL_RESULTS),
BATCHES=ENABLED, WSDL=N'[master].[sys].[sp_http_generate_wsdl_defaultcomplexorsimple]',
SESSIONS=DISABLED, SESSION_TIMEOUT=60,
DATABASE=N'geraldstest', NAMESPACE=N'http://tempuri.org', SCHEMA=STANDARD, CHARACTER_SET=XML)

</code snippet>

I had to give up for the project I am currently working because of time constraints. But in the future, for out-of-the-box functionality for proof-of-concept/prototyping, I would like to use endpoints.

Hi Gerald,

If I understand your situation correctly, your InfoPath is able to connect to the SQL SOAP endpoint and submit the data correctly, but then you are seeing a HTTP authentication failure in the Event View. It sounds like InfoPath itself is not raising any errors or exceptions. Is this Event View part of InfoPath? Or, is this the SQL Errorlog?

I am assuming you've verified that the timestamp of the HTTP authentication failure entry is the same time when you submitted the data with InfoPath. Also, I am assuming that the connection setting to SQL Server in the InfoPath form is done through Web Services instead of a direct SQL connection.

If you are able to retrieve the WSDL document from SQL using InfoPath, the same user will have access to submit data to the endpoint. If the InfoPath form as opened by another user, then you will need to grant the enduser access.

It would be really helpful if you can provide the complete error message and the whether the user has been granted access to the endpoint.

From what you've mentioned, the insert did work, so there's either a major bug in the SQL product where we are incorrectly logging bad messages or else there is some other configuration issue.

Jimmy

Infopath & SSRS

Hi,
As reporting in InfoPath 2003 is not fully open, I was wondering if we could
use SQL Reporting 2000 on top of InfoPath Forms (Stored in Form Lib -
SharePoint)
ThxHello Hans,
What do you mean not fully open? I know for a fact that you can use SRS
2003 for reporting on infopath form libraries (to do so you need to
make your own data processing extension that pulls data from something
like a webservice). I believe you can do something similar for SRS
2000.
Lance M.
Hans Hantson wrote:
> Hi,
> As reporting in InfoPath 2003 is not fully open, I was wondering if we could
> use SQL Reporting 2000 on top of InfoPath Forms (Stored in Form Lib -
> SharePoint)
> Thx

info..._schema.routines no Triggers listed - how to find scripts?

Hello,
when I look through Informatin_Schema.Routines I can find listings and
scripts for stored procedures and user-defined functions. Besides the
Trigger Manager, where can I find a collective listing of all Triggers in a
DB and the corresponding scripts? What I really want to do is to find a
Trigger that is using a particular UDF. In pseudo code I was hoping to do
something like this:
Select Routine_Name From Information_Schema.TriggerRoutines Where
Routine_Definition Like %dbo.f_someUDF%
Any suggestions appreciated how I can accomplish this.
Thanks,
Rich>What I really want to do is to find a
> Trigger that is using a particular UDF. In pseudo code I was hoping to do
> something like this:
If that's all that you really want to do, assuming that the trigger and the
function are in the same database
SQL 2000:
select DISTINCT OBJECT_NAME([id]) FROM sysdepends
WHERE OBJECT_NAME([depid]) = 'YourFunction'
SQL 2005
SELECT DISTINCT OBJECT_NAME([id]) FROM sys.sql_dependencies
WHERE OBJECT_NAME([referenced_major_id]) = 'YourFunction'
If you posted to this forum through TechNet, and you found my answers
helpful, please mark them as answers.

Indexing Results of Stored Proc (or new table created by one)

Hi,

I am using data from multiple databases and/or queries. It would greatly
simplify and speed things up if I could use CONTAINS in processing the
results. However, "CONTAINS" requires the data to be indexed. Due to the
amount of processing, I think it would be faster even if I had to re-index
every time.

For example, I would like to do something like this (simplified to
illustrate the desired functionality... This should show all of the words
from one table that are not contained in their current or inflectional forms
within another table):

SELECT W1.Content
FROM
(SELECT Word AS Content
FROM MyTable) W1
LEFT OUTER JOIN
(SELECT Phrase AS Content
FROM MyOtherTable) W2
ON W2.Content CONTAINS(INFLECTIONAL, W1.Content)
WHERE W2.Content IS NULL

Can the results of a procedure be indexed? If not, can I drop the results
into a new table and trigger an automatic index of it, pausing the procedure
until the indexing is done?

Or, it there another way?

Thanks!"HumanJHawkins" <JHawkins@.HumanitiesSoftware.Com> wrote in message
news:sRhdc.1459$k05.510@.newsread2.news.pas.earthli nk.net...
> Hi,
> I am using data from multiple databases and/or queries. It would greatly
> simplify and speed things up if I could use CONTAINS in processing the
> results. However, "CONTAINS" requires the data to be indexed. Due to the
> amount of processing, I think it would be faster even if I had to re-index
> every time.
> For example, I would like to do something like this (simplified to
> illustrate the desired functionality... This should show all of the words
> from one table that are not contained in their current or inflectional
forms
> within another table):
> SELECT W1.Content
> FROM
> (SELECT Word AS Content
> FROM MyTable) W1
> LEFT OUTER JOIN
> (SELECT Phrase AS Content
> FROM MyOtherTable) W2
> ON W2.Content CONTAINS(INFLECTIONAL, W1.Content)
> WHERE W2.Content IS NULL
> Can the results of a procedure be indexed? If not, can I drop the results
> into a new table and trigger an automatic index of it, pausing the
procedure
> until the indexing is done?
> Or, it there another way?
> Thanks!

You may be able to use CONTAINSTABLE() instead, as it returns a table, which
you can then join on. But I'm not really familiar with it -
microsoft.public.sqlserver.fulltext may be a better place to ask.

As a general answer, you could start fulltext indexing on a table from
within a stored procedure, but that's likely to be very slow and there may
be security implications as well.

Simon|||"HumanJHawkins" <JHawkins@.HumanitiesSoftware.Com> wrote in message
news:sRhdc.1459$k05.510@.newsread2.news.pas.earthli nk.net...
> Hi,
> I am using data from multiple databases and/or queries. It would greatly
> simplify and speed things up if I could use CONTAINS in processing the
> results. However, "CONTAINS" requires the data to be indexed. Due to the
> amount of processing, I think it would be faster even if I had to re-index
> every time.
> For example, I would like to do something like this (simplified to
> illustrate the desired functionality... This should show all of the words
> from one table that are not contained in their current or inflectional
forms
> within another table):
> SELECT W1.Content
> FROM
> (SELECT Word AS Content
> FROM MyTable) W1
> LEFT OUTER JOIN
> (SELECT Phrase AS Content
> FROM MyOtherTable) W2
> ON W2.Content CONTAINS(INFLECTIONAL, W1.Content)
> WHERE W2.Content IS NULL
> Can the results of a procedure be indexed? If not, can I drop the results
> into a new table and trigger an automatic index of it, pausing the
procedure
> until the indexing is done?
> Or, it there another way?
> Thanks!

You may be able to use CONTAINSTABLE() instead, as it returns a table, which
you can then join on. But I'm not really familiar with it -
microsoft.public.sqlserver.fulltext may be a better place to ask.

As a general answer, you could start fulltext indexing on a table from
within a stored procedure, but that's likely to be very slow and there may
be security implications as well.

Simonsql

Indexing on Decryption Views

1) We have SQL Server 2005 on windows 2003 server.
2) In some columns of some tables, data is encrypted before being stored and indexes
have no meaning against these columns.
3) We have decryption views to select all columns of each & every table while decrypting
the encrypted ones.
4) All our code i.e. only queries is in stored procs. We use only decryption views in these
queries.
5) When encryted columns are involved in join or filter, query runs very slow and time outs
are occuring.
6) Indexes on decrypted columns will not work. Can indexes on decryption views against these
columns help? If so, how?
7) If so, please give an example creation of such an index on a view and its column OR post
a good link?

There are indexed views but, even if you could create an index on a view that decrypted the data, the decrypted data would be materialized and there would be no point in encrypting it in the first place.

You can create a variety of temporary tables into which you insert decrypted data which you could then index but SLOW gererally applies.

Query timeouts can be adjusted.

indexing document stored in IMAGE fields

Hi guys,
in the Full Text Retrieval documentation of MS SQLserver 2000 it's said that the document stored in IMAGE
fields are "filtered" using the Microsoft provided filters (for these file extensions: .doc, .xls, .ppt, .txt
and .htm) or third party filters (e.g. Adobe for .pdf).
Little after there's a note stating that "For full-text indexing, a document must be less than 16 megabytes (MB)
in size and must not contain more than 256 kilobytes (KB) of filtered text.".
While I can check if a file is larger than the max supported size, how can I check if a document contains more
than 256 KB of filtered text ? Is this information "exported" in some way by the filter applied to the document ?
If I store a document that does not satisfy the MSSearch requirements (size > 16MB or "filtered size" > 256 KB), which
actions are made by MSSearch ? Does indexing simply ignore it ?
Many THXS for your kind reply
MadMax
The best way to do this is to get filtdump from the platform sdk and do this
filtdump -b mydoc.doc >c:\out.out and then measure the size of the output.
In other versions of Microsoft Search products there were limits of the amount fo text per document that would be indexed. You could adjust this with a registry key setting. Any bytes over this interval would not be retrieved or indexed.
There are settings within MSSearch which allows you to control the maximum raw size of a document you are indexing but AFAIK there is no setting to allow you to increase the maximum amount of textual data it will index.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||THX very much Hilary.
Do you know what happen if the indexed document is bigger than the MSSearch limits (size 16MB, filtered size 256KB) ?
Is the document indexed (may be only for the part <16MB / filtered size < 256) or the indexing process fails completely ?
Max
|||My understanding is that only the first 16 M is extracted and only the first 256 k of text indexed.
The rest is ingnored.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||Max, Hilary,
I posted this reply in Nov. 2003, on this same subject... "I believe that
this is a DOC bug, i.e., a bug or incorrect information in
Books Online (BOL). To the best of my knowledge, no public KB exists for
this bug, however a related KB article - 308771 (Q308771) "PRB: A Full-Text
Search May Not Return Any Hits If It Fails to Index a File" at
http://support.microsoft.com/default...b;en-us;308771 has
information on the use of the Registry key: FilterProcessMemoryQuota.
You can control the size of the FT Index doc via Registry Key
FilterProcessMemoryQuota and setting it's value. Specifically,
HKLM\Software\Microsoft\Search\1.0\gathering
manager\filterProcessMemoryQuota, (DWORD).
It should default to 25MB and you can make it larger as it only affects the
limit for memory usage in the daemon [MSSdmn] process. In addition to the
memory allocated to SQL Server, it is recommended that a minimum of 15 MB of
RAM be reserved for the Microsoft Search service and a maximum of 512 MB of
RAM be allocated for the Microsoft Search service. If you plan on FT
Indexing large documents, you will need to set aside more memory for the
"Microsoft Search" (mssearch.exe) service and TEST the performance FT
Indexing very large documents as well as ensure that you have enough free
disk space on you system drive and the drive where your FT Catalogs reside
at all times."
Regards,
John
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:32335399-ADC7-45AD-B179-8D97C46CBC73@.microsoft.com...
> My understanding is that only the first 16 M is extracted and only the
first 256 k of text indexed.
> The rest is ingnored.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>

Indexing And Physical Storage Of Data

1> How is the data stored physically when there is now primary key as well as any index defined in the table.....?
2> How is the data stored physically when there is just a primary key defined in one of the column of the table? No INDEX defined.
Thanks,
Rahul JhaHi Rahul

As per Tom's answer in the last thread again you need to read up on this subject. You can't get an understanding of indexes by asking questions like this on a forum - or at least not without asking hundreds of questions.

Read up on this and you'll learn that indexes <> primary keys but SQL Server (and most RDMSs) enforce primary keys through unique, non-null indexes. As such, senario 2 is impossible. Also - a primary key does not determine the physical characteristics of a table. The nature of (or lack of) the indexes does.

Indexes: changing/adding with Publication enabled

Are there any stored procedures available to alter indexes while Publication
is enabled?
What is sp_addscriptexec used for?
thank you,
bob
Dropping and creating indexes on the publisher is permited but not
replicated. To propagate this to the subscriber, creating a TSQL script and
running sp_addscriptexec is the way to go. The same thing could be achieved
by using linked servers if they were all online but using sp_addscriptexec
is easier and will make sure the script is applied to all subscribers when
they synchronize.
hth,
Paul Ibison
|||If I wanted to take the "long" way without utilizing sp_addscriptexec, would
this approach be valid:
- exec TSQL script on the publisher
- remote connect to subscribers and execute TSQL script
(2) I would not have to delete subscriptions or publications or disable
publishing in the above scenario?
Thanks Paul.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eV$$6kAdEHA.3616@.TK2MSFTNGP10.phx.gbl...
> Dropping and creating indexes on the publisher is permited but not
> replicated. To propagate this to the subscriber, creating a TSQL script
and
> running sp_addscriptexec is the way to go. The same thing could be
achieved
> by using linked servers if they were all online but using sp_addscriptexec
> is easier and will make sure the script is applied to all subscribers when
> they synchronize.
> hth,
> Paul Ibison
>
|||Robert,
yes - this works OK. Adding an index isn't treated as a table change in the
same way as adding a column, and is permitted on the publisher and
subscriber without affecting the replication setup.
Regards,
Paul Ibison

Indexes on temp tables in stored procs

I've learned that, to help prevent/minimize proc recompiles, it is best to create all needed temp tables first, before doing anything else in the sproc.

What about indexes on those temp tables? Is it better to create the index immediately after each temp table is created, or is it better to create all the temp tables first, then create all the indexes?

Since you don't have data in your temp tables at either point, it really doesn't matter that much. You'll have some metadata operations to do if you convert from a heap to a clustered index later on, but the cost will be miniscule, and virtually undetectable.

Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine

Indexes

I have a SQL Server 2000 database with numerous cluster and non-cluster
indexes.
I have a stored procedure that has several queries. This stored procedure
has query that uses index B3 first and then another query that uses B4 index
.
What is the best practice to have two separate indexes or one index (B3/B4)
that used by this stored procedure?
Thank You,
Table A
B1 : Cluster
B2
B3 :Non-Cluster
B4 :Non-ClusterThere is no best practice in that regard. Use whatever indexes improve your
performance, but try not to create too many indexes or you will end up
hurting data modification performance.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:B66C6D12-3E2E-4171-AABB-6FA620517275@.microsoft.com...
> I have a SQL Server 2000 database with numerous cluster and non-cluster
> indexes.
> I have a stored procedure that has several queries. This stored procedure
> has query that uses index B3 first and then another query that uses B4
> index.
> What is the best practice to have two separate indexes or one index
> (B3/B4)
> that used by this stored procedure?
> Thank You,
>
> Table A
> B1 : Cluster
> B2
> B3 :Non-Cluster
> B4 :Non-Cluster
>
>

Indexes

I have a SQL Server 2000 database with numerous cluster and non-cluster
indexes.
I have a stored procedure that has several queries. This stored procedure
has query that uses index B3 first and then another query that uses B4 index.
What is the best practice to have two separate indexes or one index (B3/B4)
that used by this stored procedure?
Thank You,
Table A
B1 : Cluster
B2
B3 :Non-Cluster
B4 :Non-ClusterThere is no best practice in that regard. Use whatever indexes improve your
performance, but try not to create too many indexes or you will end up
hurting data modification performance.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:B66C6D12-3E2E-4171-AABB-6FA620517275@.microsoft.com...
> I have a SQL Server 2000 database with numerous cluster and non-cluster
> indexes.
> I have a stored procedure that has several queries. This stored procedure
> has query that uses index B3 first and then another query that uses B4
> index.
> What is the best practice to have two separate indexes or one index
> (B3/B4)
> that used by this stored procedure?
> Thank You,
>
> Table A
> B1 : Cluster
> B2
> B3 :Non-Cluster
> B4 :Non-Cluster
>
>

Indexes

I have a SQL Server 2000 database with numerous cluster and non-cluster
indexes.
I have a stored procedure that has several queries. This stored procedure
has query that uses index B3 first and then another query that uses B4 index.
What is the best practice to have two separate indexes or one index (B3/B4)
that used by this stored procedure?
Thank You,
Table A
B1 : Cluster
B2
B3 :Non-Cluster
B4 :Non-Cluster
There is no best practice in that regard. Use whatever indexes improve your
performance, but try not to create too many indexes or you will end up
hurting data modification performance.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:B66C6D12-3E2E-4171-AABB-6FA620517275@.microsoft.com...
> I have a SQL Server 2000 database with numerous cluster and non-cluster
> indexes.
> I have a stored procedure that has several queries. This stored procedure
> has query that uses index B3 first and then another query that uses B4
> index.
> What is the best practice to have two separate indexes or one index
> (B3/B4)
> that used by this stored procedure?
> Thank You,
>
> Table A
> B1 : Cluster
> B2
> B3 :Non-Cluster
> B4 :Non-Cluster
>
>

Indexed Views and ARITHABORT

Hi All,
I'm attempting to use an indexed view from within a stored procedure that's
called by RS. However, to use the Indexed View, ARITHABORT must be set to ON
for the session. Within RS as a whole, within a shared datasource, or within
a specific RS report, how do I do this?
I need to run the command "SET ARITHABORT ON" before my stored procedure
executes.
Has anybody else gotten this to work?
(BTW I've already been able to set it Arithabort as the db default so this
is no longer a huge issue, but I'm curious how I'd get around it if I wasn't
allowed to set that.)
Thx, JoelI'm having the same issue and unfortunately, I'm unable to set the db default
in my situation.
Does anyone know how to do this... help!!!!
"Joel Rumerman" wrote:
> Hi All,
> I'm attempting to use an indexed view from within a stored procedure that's
> called by RS. However, to use the Indexed View, ARITHABORT must be set to ON
> for the session. Within RS as a whole, within a shared datasource, or within
> a specific RS report, how do I do this?
> I need to run the command "SET ARITHABORT ON" before my stored procedure
> executes.
> Has anybody else gotten this to work?
> (BTW I've already been able to set it Arithabort as the db default so this
> is no longer a huge issue, but I'm curious how I'd get around it if I wasn't
> allowed to set that.)
> Thx, Joel
>
>|||Go to generic query designer (button is to the right of the ...). You can
use T-SQL from here. I haven't tried this but it should work.
set arithabort on
exec myproc @.Param1, @.Param2
Give it a try and let me know if it works.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Anonymous" <Anonymous@.discussions.microsoft.com> wrote in message
news:19C0F946-34C2-473C-9EE9-57C4BACB6FFE@.microsoft.com...
> I'm having the same issue and unfortunately, I'm unable to set the db
> default
> in my situation.
> Does anyone know how to do this... help!!!!
>
> "Joel Rumerman" wrote:
>> Hi All,
>> I'm attempting to use an indexed view from within a stored procedure
>> that's
>> called by RS. However, to use the Indexed View, ARITHABORT must be set to
>> ON
>> for the session. Within RS as a whole, within a shared datasource, or
>> within
>> a specific RS report, how do I do this?
>> I need to run the command "SET ARITHABORT ON" before my stored procedure
>> executes.
>> Has anybody else gotten this to work?
>> (BTW I've already been able to set it Arithabort as the db default so
>> this
>> is no longer a huge issue, but I'm curious how I'd get around it if I
>> wasn't
>> allowed to set that.)
>> Thx, Joel
>>|||Thanks!
I was able to get past the ARITHABORT message now, but now I get a message
that says the QUOTED_IDENTIFIER is not set correctly.
I tried adding SET QUOTED_IDENTIFIER ON to the beginning and redeployed the
report okay, but that still doesn't seem to work... it still gives the same
message about QUOTED_IDENTIFIER is not set correctly.
I should mention that in the stored procedure I am using a "WITH (noexpand)"
hint.
I'm going to keep trying different things and I'll post something
when I can.
Thanks,
Kirk
"Bruce L-C [MVP]" wrote:
> Go to generic query designer (button is to the right of the ...). You can
> use T-SQL from here. I haven't tried this but it should work.
> set arithabort on
> exec myproc @.Param1, @.Param2
> Give it a try and let me know if it works.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Anonymous" <Anonymous@.discussions.microsoft.com> wrote in message
> news:19C0F946-34C2-473C-9EE9-57C4BACB6FFE@.microsoft.com...
> > I'm having the same issue and unfortunately, I'm unable to set the db
> > default
> > in my situation.
> >
> > Does anyone know how to do this... help!!!!
> >
> >
> > "Joel Rumerman" wrote:
> >
> >> Hi All,
> >>
> >> I'm attempting to use an indexed view from within a stored procedure
> >> that's
> >> called by RS. However, to use the Indexed View, ARITHABORT must be set to
> >> ON
> >> for the session. Within RS as a whole, within a shared datasource, or
> >> within
> >> a specific RS report, how do I do this?
> >>
> >> I need to run the command "SET ARITHABORT ON" before my stored procedure
> >> executes.
> >>
> >> Has anybody else gotten this to work?
> >>
> >> (BTW I've already been able to set it Arithabort as the db default so
> >> this
> >> is no longer a huge issue, but I'm curious how I'd get around it if I
> >> wasn't
> >> allowed to set that.)
> >>
> >> Thx, Joel
> >>
> >>
> >>
>
>