Dynamic script

Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:546732
I want to write a script for a table 'A'. I
1) want to drop all the foreign key constraints which are created in another
table(s) and in which table 'A' is being used. I dont know those foregin key
constraints while executing the script.
2) want to drop all foreign key constraints which are created in table 'A'.
I dont know those foregin key constraints while executing the script.
Is it possible?
ThanksThere is no builtin tool/script. However, you could use this as a starting
point to construct your 'drop constraint' statements.
if object_id('usp_findreferences','p') is not null
drop proc usp_findreferences
go
create procedure usp_findreferences
/ ****************************************
***********************************
*/
/* Purpose: A quick & dirty way to find ref. objects for a[ll] table[s] */
/* Author: OJ Ngo */
/* Date: 02/28/2002 */
/ ****************************************
***********************************
*/
@.tbname sysname=null
as
set nocount on
Print 'Referenced:'
select c1.table_name,
c1.column_name,
fkey=r.constraint_name,
referenced_parent_table=c2.table_name,
c2.column_name
from information_schema.constraint_column_usage c1 join
information_schema.referential_constraints r on
c1.constraint_name=r.constraint_name
join information_schema.constraint_column_usage c2 on
r.unique_constraint_name=c2.constraint_name
where c1.table_name=coalesce(@.tbname,c1.table_name)
order by case when @.tbname is null then c1.table_name else c2.table_name end
print ''
print 'Referencing:'
select c1.table_name,
c1.column_name,
fkey=r.constraint_name,
referencing_child_table=c2.table_name,
c2.column_name
from information_schema.constraint_column_usage c1 join
information_schema.referential_constraints r on
c1.constraint_name=r.unique_constraint_name
join information_schema.constraint_column_usage c2 on
r.constraint_name=c2.constraint_name
where c1.table_name=coalesce(@.tbname,c1.table_name)
order by case when @.tbname is null then c1.table_name else c2.table_name end
go
exec usp_findreferences
-oj
"Rizwan" <hussains@.pendylum.com> wrote in message
news:LYuMe.7394$7R.474488@.news20.bellglobal.com...
>I want to write a script for a table 'A'. I
> 1) want to drop all the foreign key constraints which are created in
> another table(s) and in which table 'A' is being used. I dont know those
> foregin key constraints while executing the script.
> 2) want to drop all foreign key constraints which are created in table
> 'A'. I dont know those foregin key constraints while executing the script.
> Is it possible?
> Thanks
>

Dynamic Index Drop and Create

I am working on a data warehouse that is SQL Server 2000 based and what
I need to do is create two stored procs that do the following psuedo
code. I can write it myself but if someone has already written it then
why write it again or if you know of solutions that are close to what I
want that would be a good start. The reason that these need to be in
two seperate procs is that I want to seperate out the Drop and Creates
from the stored procedure that is loading the table. Instead of
hardcoding the drops and creates I want this procedure to be more
dynamic so that as we add indexes for tunning we don't have to maintain
our stored procedures.
proc_DropIndexes @.DatabaseName, @.TableName
- Write all necessary current index information to a work table for
@.DatabaseName and @.TableName to allow for recreation
- Drop all current indexes for @.DatabaseName and @.TableName
proc_CreateIndexes @.DatabaseName, @.TableName
- Read all necessary current index information from a work table for
@.DatabaseName and @.TableName to allow for recreation
- Create all indexes for @.DatabaseName and @.TableName
Thanks,
SpencerThe following appear to be good starts...
http://www.code-magazine.com/articl...b9cc0d4526e1913

Dynamic Index Drop and Create

I am working on a data warehouse that is SQL Server 2000 based and what
I need to do is create two stored procs that do the following psuedo
code. I can write it myself but if someone has already written it then
why write it again or if you know of solutions that are close to what I
want that would be a good start. The reason that these need to be in
two seperate procs is that I want to seperate out the Drop and Creates
from the stored procedure that is loading the table. Instead of
hardcoding the drops and creates I want this procedure to be more
dynamic so that as we add indexes for tunning we don't have to maintain
our stored procedures.
proc_DropIndexes @.DatabaseName, @.TableName
- Write all necessary current index information to a work table for
@.DatabaseName and @.TableName to allow for recreation
- Drop all current indexes for @.DatabaseName and @.TableName
proc_CreateIndexes @.DatabaseName, @.TableName
- Read all necessary current index information from a work table for
@.DatabaseName and @.TableName to allow for recreation
- Create all indexes for @.DatabaseName and @.TableName
Thanks,
SpencerThe following appear to be good starts...
http://www.code-magazine.com/articleprint.aspx?quickid=0301101&printmode=true
http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/1f4dd406fa18d676/8b9cc0d4526e1913?lnk=st&q=scripting+sql+server+indexes+create+drop&rnum=16&hl=en#8b9cc0d4526e1913

dynamic creation of select statement

hello,

My basic requirement is i have five drop down list where data is selected and based on the selection the no. of colums to be selected have to be designed. ex : say i have following drop down lists:
1. community
2. gender
3. level
4. term
5. year

at runtime is it possible to design the crystal reports columns.

Pl. help out in directing to url or given sample source to dynamic creation of columns in the crystal reports.

thanks in advance
karunau need to add the formulas and the field object formatting.
u can do this by declaring an array -->
IFormulaFieldDefinitionPtr formulas[10];

then when adding formulas-->
ISectionPtr pSection = GetReportSection(sectionNum);
IFormulaFieldDefinitionsPtr pFormulaFields = 0;
m_Report->get_FormulaFields((IFormulaFieldDefinitions**) &pFormulaFields);

CString Recur = CString(L"WhileReadingRecords;" +
(CString)__toascii(13) +
(CString)__toascii(10));

// Add the formulas to the formula fields collection
CString CStrText = Recur + CString(L"Space(10)");
BSTR strText = CStrText.AllocSysString();
BSTR objName;
int i=0;
////assume u have stored the field names somewhere like a list or an array.

for(iter=lstRecs.begin();iter!=lstRecs.end();iter++)
{

CString str= *iter;
if(i==0)
{
objName = str.AllocSysString();
formulas[i]=pFormulaFields->Add(objName, strText);


}
else
{
SysReAllocString(&objName, str.AllocSysString());
formulas[i]=pFormulaFields->Add(objName, strText);

}

i++;
}

////////////////////////////////////////////////////////////////////////////////
then add the field objects-->

ISectionPtr pSection = GetReportSection(3);
IFieldObjectPtr pFieldObj = 0;

LocX = 10; // Horizontal offset
VARIANT var;
VariantInit (&var);
var.vt = VT_DISPATCH;
BSTR objName = NULL;

int i=0;

for(iter=lstRecs.begin();iter!=lstRecs.end();iter++)
{

CString str= *iter;
var.pdispVal = formulas[i];
pFieldObj = pSection->AddFieldObject(var, LocX, 0);
SysReAllocString(&objName,str.AllocSysString());
pFieldObj->put_Name(objName);
pFieldObj->put_Width(1600);
LocX += 1500;
}

////////////////////////////////////////////////////////////////////////////////////////////
thats it! hope this helps...

Dynamic controls in report prompt

What I would like to do is have a drop down that would basically have a list of countries. When the user selects the USA, another drop down in the report prompt would have a filtered list of states in the USA. Finally, the user would select a state and a third drop down would have a list of cities. Obviously this would need to be done via post backs. Bottomline is can this be done. Can the report prompt be more dynamic. If not I will need to develope some aspx report prompt and pass the data, which I want to avoid. PLEASE HELP!That can be done with cascading parameters.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_interactive_v1_50fn.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"AG" <AG@.discussions.microsoft.com> wrote in message
news:3F535CAF-8B50-4566-BE35-8BE4DC8762C4@.microsoft.com...
> What I would like to do is have a drop down that would basically have a
> list of countries. When the user selects the USA, another drop down in
> the report prompt would have a filtered list of states in the USA.
> Finally, the user would select a state and a third drop down would have a
> list of cities. Obviously this would need to be done via post backs.
> Bottomline is can this be done. Can the report prompt be more dynamic. If
> not I will need to develope some aspx report prompt and pass the data,
> which I want to avoid. PLEASE HELP!|||"Lev Semenets [MSFT]" <levs@.microsoft.com> schrieb im Newsbeitrag
news:eyMx04nVEHA.2520@.TK2MSFTNGP12.phx.gbl...
> That can be done with cascading parameters.
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_interactive_v1_50fn.asp
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
Could you please clarify on what database the description is based? Neither
in Northwind nor in Advantureworks is a table named Divisions.
r.|||Yes, this uses an imaginary database example. :) The documentation folks
have fixed this for next release.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Roland" <nomail@.spamprotect.com> wrote in message
news:uHB10GtVEHA.1952@.TK2MSFTNGP12.phx.gbl...
> "Lev Semenets [MSFT]" <levs@.microsoft.com> schrieb im Newsbeitrag
> news:eyMx04nVEHA.2520@.TK2MSFTNGP12.phx.gbl...
> > That can be done with cascading parameters.
> >
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_interactive_v1_50fn.asp
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Could you please clarify on what database the description is based?
Neither
> in Northwind nor in Advantureworks is a table named Divisions.
> r.
>|||Actually the easy way is the postback method.
But you can develop your own javascript code to do this job, but I think its
harder to code and to debug.
Why the postback cause a problem for you?
"AG" <AG@.discussions.microsoft.com> a écrit dans le message de
news:3F535CAF-8B50-4566-BE35-8BE4DC8762C4@.microsoft.com...
> What I would like to do is have a drop down that would basically have a
list of countries. When the user selects the USA, another drop down in the
report prompt would have a filtered list of states in the USA. Finally, the
user would select a state and a third drop down would have a list of cities.
Obviously this would need to be done via post backs. Bottomline is can this
be done. Can the report prompt be more dynamic. If not I will need to
develope some aspx report prompt and pass the data, which I want to avoid.
PLEASE HELP!

Dynamic change of number of series on the chart

Depending on the result of my stored procedure logic I need to display
different number of lines on the chart in my report. I can not drop the data
fileds into the chart design as suggested in the Help, because at design time
I do not know their names and how many of them I will have when report is
called. Is it possible? What is involved?
Thanks.Not sure I understand what you are trying to do. It sounds like you want a
dynamic series grouping expression which is basically just based on the
field that comes from the stored procedure.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Simon Gold" <SimonGold@.discussions.microsoft.com> wrote in message
news:F24071B8-92C6-4483-A4E6-CE91FD121D3D@.microsoft.com...
> Depending on the result of my stored procedure logic I need to display
> different number of lines on the chart in my report. I can not drop the
> data
> fileds into the chart design as suggested in the Help, because at design
> time
> I do not know their names and how many of them I will have when report is
> called. Is it possible? What is involved?
> Thanks.|||Thank you Robert,
You indirectly answered my question. I just could not make sense of grouping
capabilities of the chart. The Help on this topic is not very "helpful".
"Robert Bruckner [MSFT]" wrote:
> Not sure I understand what you are trying to do. It sounds like you want a
> dynamic series grouping expression which is basically just based on the
> field that comes from the stored procedure.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Simon Gold" <SimonGold@.discussions.microsoft.com> wrote in message
> news:F24071B8-92C6-4483-A4E6-CE91FD121D3D@.microsoft.com...
> > Depending on the result of my stored procedure logic I need to display
> > different number of lines on the chart in my report. I can not drop the
> > data
> > fileds into the chart design as suggested in the Help, because at design
> > time
> > I do not know their names and how many of them I will have when report is
> > called. Is it possible? What is involved?
> > Thanks.
>
>|||I am having to do the same thing like dynamically having series elements
depending on the query results... is this possible to do?
"Simon Gold" wrote:
> Thank you Robert,
> You indirectly answered my question. I just could not make sense of grouping
> capabilities of the chart. The Help on this topic is not very "helpful".
> "Robert Bruckner [MSFT]" wrote:
> > Not sure I understand what you are trying to do. It sounds like you want a
> > dynamic series grouping expression which is basically just based on the
> > field that comes from the stored procedure.
> >
> > -- Robert
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> > "Simon Gold" <SimonGold@.discussions.microsoft.com> wrote in message
> > news:F24071B8-92C6-4483-A4E6-CE91FD121D3D@.microsoft.com...
> > > Depending on the result of my stored procedure logic I need to display
> > > different number of lines on the chart in my report. I can not drop the
> > > data
> > > fileds into the chart design as suggested in the Help, because at design
> > > time
> > > I do not know their names and how many of them I will have when report is
> > > called. Is it possible? What is involved?
> > > Thanks.
> >
> >
> >|||Yes, It is possible to do that. The "Help" does not have a good wordage on
it, but here how you do it.
Imagine that you want to display stock prices for several different
companies for a say 10 days period. Create a table 'Prices' that would have
at least three columns: 'DayNumber', 'StockPrice' and 'CompanyName'. Populate
that table with your data. Create a dataset for your chart where run a
"SELECT DayNumber,StockPrice, CompanyName FROM Prices".
Om the chart drug and drop 'DayNumber field under the horizontal axis
(Category fields), drop StockPrice field above the chart into 'data fields'
and drop 'Companyname' filed to the right of the chart into 'Series fileds'.
This will create a chart that will have as many series as many company names
you have in your table.
Good luck...
Simon.
StockPrice
"Mathi" wrote:
> I am having to do the same thing like dynamically having series elements
> depending on the query results... is this possible to do?
> "Simon Gold" wrote:
> > Thank you Robert,
> > You indirectly answered my question. I just could not make sense of grouping
> > capabilities of the chart. The Help on this topic is not very "helpful".
> >
> > "Robert Bruckner [MSFT]" wrote:
> >
> > > Not sure I understand what you are trying to do. It sounds like you want a
> > > dynamic series grouping expression which is basically just based on the
> > > field that comes from the stored procedure.
> > >
> > > -- Robert
> > > This posting is provided "AS IS" with no warranties, and confers no rights.
> > >
> > > "Simon Gold" <SimonGold@.discussions.microsoft.com> wrote in message
> > > news:F24071B8-92C6-4483-A4E6-CE91FD121D3D@.microsoft.com...
> > > > Depending on the result of my stored procedure logic I need to display
> > > > different number of lines on the chart in my report. I can not drop the
> > > > data
> > > > fileds into the chart design as suggested in the Help, because at design
> > > > time
> > > > I do not know their names and how many of them I will have when report is
> > > > called. Is it possible? What is involved?
> > > > Thanks.
> > >
> > >
> > >