easy way to mark all the fk's and triggers NFR?

Is there a script somehwhere which would alter all constraints and
recreate/alter triggers with NFR property set to 1?
or
would one of you guys know of an easy way to do it?
Thank you.
Tejas.
Following should help. PLEASE TEST IT BEFORE USING IT IN A REAL ENVIRONMENT.
You may uncomment the commented lines if trying to do the same for individual
tables.
SET NOCOUNT ON
GO
DECLARE@.tbl_namevarchar(50)
--SELECT@.tbl_name = 'tbl_name'
/*
Creating the command to drop constraints on the table
*/
SELECT'ALTER TABLE ' + object_name(fkeyid) + ' DROP CONSTRAINT ' +
object_name(constid) + CHAR(10) + 'GO' + CHAR(10)
FROMsysreferences
-- WHERE object_name(fkeyid) = @.tbl_name
-- ORobject_name(rkeyid) = @.tbl_name
ORDER BY object_name(fkeyid)
/*
Creating the command to re-create all the dropped constraints
*/
SELECT'ALTER TABLE ' + object_name(a.fkeyid) + ' ADD CONSTRAINT ' +
object_name(constid) + ' FOREIGN KEY (' + b.name + ') REFERENCES ' +
object_name(a.rkeyid) + '(' + c.name + ') NOT FOR REPLICATION' + CHAR(10) +
'GO' + CHAR(10)
FROM sysforeignkeys a,
syscolumns b,
syscolumns c
WHERE a.fkey = b.colid
AND a.fkeyid = b.id
AND a.rkey = c.colid
AND a.rkeyid = c.id
-- AND (object_name(a.rkeyid) = @.tbl_nameOR
-- object_name(a.fkeyid) = @.tbl_name)
ORDER BY object_name(a.fkeyid)
GO
"Tejas Parikh" wrote:

> Is there a script somehwhere which would alter all constraints and
> recreate/alter triggers with NFR property set to 1?
> or
> would one of you guys know of an easy way to do it?
> Thank you.
> Tejas.

Easy Stuff

hi.. guys this is simple for you guys how can i make ms access to display the current date in a col... when i go to insert a new line i how can i make it so that its allready there? i dont have to type it??I bet that you'll get a lot better response if you post this question in the MS-Access (http://www.dbforums.com/f84) forum.

-PatP|||thnx Pat...|||That didn't hurt much, now did it ?!?!

Anywho, while I'm sure that somebody here could have answered your question, why bother to ask here when there are oodles of folks that are readily available that can answer you? Better still, they can offer lots of insight because they actually USE MS-Access as their tool of choice.

-PatP

dynamically columns

Hi guys,
Is there any method to add/delete dynamically columns in a table, not in a
matrix?
thanks,
RaduYou can not really Dynamically add columns to a table..
But you can pre-create a number of extra columns, show/hide and assign their
values on the fly...
That's about the best we can do now.
--
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
"Radu" <Radu@.discussions.microsoft.com> wrote in message
news:FEA44080-476E-46AC-B2AC-7599FB6A6C06@.microsoft.com...
> Hi guys,
> Is there any method to add/delete dynamically columns in a table, not in a
> matrix?
> thanks,
> Radu|||Just another thought... ONe of the things we do that 'sort of' simluates
dynamically adding columns is that we will create a single column, then
populate it with varying multiple pieces of information concatenated
together as a single sql column...
--
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
"Radu" <Radu@.discussions.microsoft.com> wrote in message
news:FEA44080-476E-46AC-B2AC-7599FB6A6C06@.microsoft.com...
> Hi guys,
> Is there any method to add/delete dynamically columns in a table, not in a
> matrix?
> thanks,
> Radu|||You could build up the XML for the report programmatically. This would allow
you complete control over what columns get placed in your table. A possible
disadvantage is that you would have to deploy the report programmatically
once it had been built up.|||snyder,
i want to hide a whole column in a table if there is no data to be
displayed in the entire column, if there exists atleast one record in the
column then we need to show the column else hide it.
"Wayne Snyder" wrote:
> You can not really Dynamically add columns to a table..
> But you can pre-create a number of extra columns, show/hide and assign their
> values on the fly...
> That's about the best we can do now.
> --
> 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
> "Radu" <Radu@.discussions.microsoft.com> wrote in message
> news:FEA44080-476E-46AC-B2AC-7599FB6A6C06@.microsoft.com...
> > Hi guys,
> >
> > Is there any method to add/delete dynamically columns in a table, not in a
> > matrix?
> >
> > thanks,
> > Radu
>
>|||how do you create a single column in the matrix control and then populate it
with multiple pieces of information
I amtrying to add a Percent Change Col to Pivoted colmns in Matrix
"Wayne Snyder" wrote:
> Just another thought... ONe of the things we do that 'sort of' simluates
> dynamically adding columns is that we will create a single column, then
> populate it with varying multiple pieces of information concatenated
> together as a single sql column...
> --
> 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
> "Radu" <Radu@.discussions.microsoft.com> wrote in message
> news:FEA44080-476E-46AC-B2AC-7599FB6A6C06@.microsoft.com...
> > Hi guys,
> >
> > Is there any method to add/delete dynamically columns in a table, not in a
> > matrix?
> >
> > thanks,
> > Radu
>
>

Dynamically changing Web Service Task parameters at runtime?...

Okay, this one might stump you guys.

I am trying to access a web service that has parameters (three of them, all strings). But I want to access this service repeatedly, and drive the thing from a table.

I've got the Foreach Loop correctly getting variables from the recordset I populate from the table. That works and I can run a script with msgbox showing all the correct values being read from the table.

My problem is taking these variables and sticking them in the Name / Type / Value entries in the Input tab of the Web Service Task. There doesn't seem to be any mechanism for dynamically updating these at runtime, and if there isn't, that is a HUGE hole in the product. Who accesses Web Services with the same parameters every time?!?
No doubt you know this by now Lazamataz but this can't be done. you're right, it IS a hole in the product!

-Jamie|||

I'm trying to do this to...

Is there a way of accessing web service parameters through a script and the runtime / tasks?

|||

Absolutely, yes. Buy Donald Farmer's book (available at the usual places). That has a downloadable chapter that shows you how.

-Jamie

Dynamically changing Web Service Task parameters at runtime?...

Okay, this one might stump you guys.

I am trying to access a web service that has parameters (three of them, all strings). But I want to access this service repeatedly, and drive the thing from a table.

I've got the Foreach Loop correctly getting variables from the recordset I populate from the table. That works and I can run a script with msgbox showing all the correct values being read from the table.

My problem is taking these variables and sticking them in the Name / Type / Value entries in the Input tab of the Web Service Task. There doesn't seem to be any mechanism for dynamically updating these at runtime, and if there isn't, that is a HUGE hole in the product. Who accesses Web Services with the same parameters every time?!?
No doubt you know this by now Lazamataz but this can't be done. you're right, it IS a hole in the product!

-Jamie|||

I'm trying to do this to...

Is there a way of accessing web service parameters through a script and the runtime / tasks?

|||

Absolutely, yes. Buy Donald Farmer's book (available at the usual places). That has a downloadable chapter that shows you how.

-Jamie

Dynamically change the DataFlow Queries

Hi Guys,

This is Ravi. I'm working on SSIS 2005 version. I have created the DTSX file from the SQL Server and executed it successfully from my .NET 2005 code.

Now I have a requirement that I need to dynamically change the Source database query. ie., based on the user selection I need to get the data from different tables of SQL and put it into an Excel file.

Can anyone help me in this..

Regards,
Ravi K. Kalyan
Mascon Global Limited.

You can only change the query if the metadata of the data-flow is unchanged thereafter.

If this is the case then read this: http://blogs.conchango.com/jamiethomson/archive/2006/03/11/3063.aspx

It tells you how to dynamically alter your SQL queries.

-Jamie

|||

Thanks for the reply.

I tried to modify my code using the dataflow, but cud't do it exactly the way I wanted.

I have a requirement of exporting the data into excel file using the SSIS. For that I have used Application & Package class of DTS Namespace.

I can able to load and execute the package. Now according to the user selection I need to export the data from different tables. Can I pass the source query to the package object from my .NET 2005 code? If Yes, can u please give me some sample code or any reference links.

--Kalyan

Dynamic Text Parser?

Hi Guys,

I have a script task that is supposed to read and parse a fixed width source file.

Basically, I want to make the FieldWidths dynamic so that I'll be able to reuse this package with different files. So Instead of hardcoding the field widths directly into my script task, I want it to be stored somewhere that the package can get when executions starts. Is there a way of doing this?

The code looks like this:

Using Reader As New TextFieldParser(mTempFilePAth)

Reader.TextFieldType = FieldType.FixedWidth

Reader.SetFieldWidths(1, 8, 8, 8, 4, 8) <-- I want to change this to handle dynamic widths.

Hi,

The best way to make your package re-usable is to use a "Integration Services Variable", see this link for help on variables http://msdn2.microsoft.com/en-us/library/ms141085.aspx and this one for accessing from a script http://msdn2.microsoft.com/en-us/library/aa337079.aspx.

The main gotcha is you need to be aware of is this bit from the second link "You can make existing variables available for read-only or read/write

access by your custom script by entering comma-delimited lists of

variables in the ReadOnlyVariables and ReadWriteVariables fields on the Script page of the Script Transformation Editor."

Also does SetFieldWidths take a variable number of arguments? If so you may need to define a string SSIS variable with the arguments comma seperated, then parse them into the seperate arguments for SetFieldWidths

Dave

Dynamic Sql Intermittenetly Slow - Losing Hair Fast

Hi guys,
I've been staring at this stored proc for hours and I just can't see
what wrong with it. It will run fine 40+ times in a row and then one
time it it will take up-to 15 seconds to complete. Am baffled.
Anyway here's the query. It basically says, select all the hotel rates
where the option is in the OptionList parameter and the agentid = x and
the AgentPassword is equal to x. There is an added complication in that
I have to filter out any HotelRate that is zero for the selected room
types (single, double etc)
Any help appreciated!
Cheers, Pete (lad4bear)
CREATE PROCEDURE [dbo].[SelectMatchingHotelRates]
@.AgentId nvarchar(50),
@.AgentPassword nvarchar(50),
@.OptionList nvarchar(3900),
@.Single int,
@.Double int,
@.Twin int,
@.Triple int
AS
BEGIN
DECLARE @.dynamicSql nvarchar(4000)
SET @.dynamicSql =
'SELECT *
FROM
HotelsOptionRates
WHERE
[HotelOptionRates_OptionCode] IN ( ' + @.OptionList + ')
AND [HotelOptionRates_AgentId] = ''' + @.AgentId + '''
AND [HotelOptionRates_AgentPassword] = ''' + @.AgentPassword + ''''
IF (@.Single > 0)
SET @.DynamicSql = @.DynamicSql + ' AND [HotelOptionRates_SingleRate]
<> 0'
IF (@.Double > 0)
SET @.DynamicSql = @.DynamicSql + ' AND [HotelOptionRates_DoubleRate]
<> 0'
IF (@.Twin > 0)
SET @.DynamicSql = @.DynamicSql + ' AND [HotelOptionRates_TwinRate]
<> 0'
IF (@.Triple > 0)
SET @.DynamicSql = @.DynamicSql + ' AND [HotelOptionRates_TripleRate]
<> 0'
EXECUTE(@.dynamicSql)
END
GO<lad4bear@.gmail.com> wrote in message
news:1132944029.175652.67830@.f14g2000cwb.googlegroups.com...
> Hi guys,
> I've been staring at this stored proc for hours and I just can't see
> what wrong with it. It will run fine 40+ times in a row and then one
> time it it will take up-to 15 seconds to complete. Am baffled.
>
What is the execution plan when it takes 15 seconds to complete?
David|||I'm pretty sure that you can write the query without using dynamic SQL.
A lot of information at this site:
http://www.sommarskog.se/
Read "Dynamic Seach Conditions" first.
"Arrays and Lists in SQL server" will help you with this part:
> [HotelOptionRates_OptionCode] IN ( ' + @.OptionList + ')
<lad4bear@.gmail.com> wrote in message
news:1132944029.175652.67830@.f14g2000cwb.googlegroups.com...
> Hi guys,
> I've been staring at this stored proc for hours and I just can't see
> what wrong with it. It will run fine 40+ times in a row and then one
> time it it will take up-to 15 seconds to complete. Am baffled.
> Anyway here's the query. It basically says, select all the hotel rates
> where the option is in the OptionList parameter and the agentid = x and
> the AgentPassword is equal to x. There is an added complication in that
> I have to filter out any HotelRate that is zero for the selected room
> types (single, double etc)
> Any help appreciated!
> Cheers, Pete (lad4bear)
>
> CREATE PROCEDURE [dbo].[SelectMatchingHotelRates]
> @.AgentId nvarchar(50),
> @.AgentPassword nvarchar(50),
> @.OptionList nvarchar(3900),
> @.Single int,
> @.Double int,
> @.Twin int,
> @.Triple int
> AS
> BEGIN
> DECLARE @.dynamicSql nvarchar(4000)
> SET @.dynamicSql =
> 'SELECT *
> FROM
> HotelsOptionRates
> WHERE
> [HotelOptionRates_OptionCode] IN ( ' + @.OptionList + ')
> AND [HotelOptionRates_AgentId] = ''' + @.AgentId + '''
> AND [HotelOptionRates_AgentPassword] = ''' + @.AgentPassword + ''''
> IF (@.Single > 0)
> SET @.DynamicSql = @.DynamicSql + ' AND [HotelOptionRates_SingleRate]
> <> 0'
> IF (@.Double > 0)
> SET @.DynamicSql = @.DynamicSql + ' AND [HotelOptionRates_DoubleRate]
> <> 0'
> IF (@.Twin > 0)
> SET @.DynamicSql = @.DynamicSql + ' AND [HotelOptionRates_TwinRate]
> <> 0'
> IF (@.Triple > 0)
> SET @.DynamicSql = @.DynamicSql + ' AND [HotelOptionRates_TripleRate]
> <> 0'
> EXECUTE(@.dynamicSql)
> END
> GO
>|||To test a hypothesis I replaced the dynamic sql with similar (although not
the same) non-dynamic sql. Every forty or so attempts it takes 15 seconds to
complete.
Now currently I don't know if the stored proc itself is taking 15 seconds or
if the line of code I use to call the stored proc is taking 15 seconds. It's
one line of thoroughly tested code so I see no reason to that it should be
causing this problem.
As the database is remote I cannot use sql profiler to see how long the
stored proc is actually taking. Is there another way to get timing
information?
Thanks for your help
Pete (aka lad4bear)
"David Browne" wrote:

> <lad4bear@.gmail.com> wrote in message
> news:1132944029.175652.67830@.f14g2000cwb.googlegroups.com...
> What is the execution plan when it takes 15 seconds to complete?
> David
>
>|||Figured it out. I forgot I had scheduled batch update set to run every 3
minutes which was updating the table. Looks like I was running into some
locking issues. Thanks for the advice and the links
Cheers Pete (aka lad4bear)
"lad4bear" wrote:
> To test a hypothesis I replaced the dynamic sql with similar (although not
> the same) non-dynamic sql. Every forty or so attempts it takes 15 seconds
to
> complete.
> Now currently I don't know if the stored proc itself is taking 15 seconds
or
> if the line of code I use to call the stored proc is taking 15 seconds. It
's
> one line of thoroughly tested code so I see no reason to that it should be
> causing this problem.
> As the database is remote I cannot use sql profiler to see how long the
> stored proc is actually taking. Is there another way to get timing
> information?
> Thanks for your help
> Pete (aka lad4bear)
>
> "David Browne" wrote:
>