putting names of objects to control-flow loop creating objects

please help newbie

I need to create a lot of objects the same type (let's say: schemas)
I wish to use paramerized block in loop to do so.
- how to put names of my objects to such control-flow?

belss you for helpfireball wrote:

Quote:

Originally Posted by

please help newbie
>
I need to create a lot of objects the same type (let's say: schemas)
I wish to use paramerized block in loop to do so.
- how to put names of my objects to such control-flow?
>
>
>
belss you for help

Firstly the obvious question: Why? Where are the names from these
objects coming from? If you can write a query to extract the names then
you could just use Query Analyzer or Management Studio to paste those
names into an editable script and then run the script directly. That
way there is no need for a loop.

If you must do it programmatically then you'll have to do something
with dynamic SQL. See:

http://sommarskog.se/dynamic_sql.html
Personally I'd say that if you have so many schemas that you need a
loop to create them then you definitely have too many schemas... or you
are using them in a highly unconventional manner.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Uzytkownik "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.orgnapisal w

Quote:

Originally Posted by

Where are the names from these objects coming from

from text file I got

Quote:

Originally Posted by

if you have so many schemas that you need a loop

first idea is to build scripts in engineer maneer (reusing code blocks - not
to copy them - so managing any changes is better)

second reason - I got my analysis data model in RaRose, which doesn't really
support automatization of creating data model. I use creating
quasi-sqlserver scripts, so I have object names and so. It's not really
large amount of objects (a few schemas, about 100 tables) - but it does
change any time.
Sorry if it sounds a bit werid :-) I'm a newbie

third idea was to create that way descriptions to my obiects (tables,
attributes) in a loop (having descriptions in xls file, for example)

All hints will be appeciated.

ps:

Quote:

Originally Posted by

then you definitely have too many schemas...

you are definitely right.

Quote:

Originally Posted by

are using them in a highly unconventional manner.

like, let's say - to make some perfiormance tests/statistics? (I really
don't do so :-))|||fireball (fireball@.onet.kropka.eu) writes:

Quote:

Originally Posted by

Uzytkownik "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.orgnapisal

Quote:

Originally Posted by

>Where are the names from these objects coming from

>
from text file I got

But text file is not SQL, but you have to transform it to SQL?

Doing this from SQL is not really fun. If you are on SQL 2005, you
could do this through the CLR, but you would still go through hoops.
Do this from a client application: Perl, VBscript or whatever your
favourite may be.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Uzytkownik "Erland Sommarskog" <esquel@.sommarskog.senapisal w wiadomosci

Quote:

Originally Posted by

But text file is not SQL

well, the point is to put it into script.sql any way which I will be able to
fetch it into my loop|||fireball (fireball@.onet.kropka.eu) writes:

Quote:

Originally Posted by

Uzytkownik "Erland Sommarskog" <esquel@.sommarskog.senapisal w wiadomosci
>

Quote:

Originally Posted by

>But text file is not SQL

>
well, the point is to put it into script.sql any way which I will be
able to fetch it into my loop

Without having seen your file, it's difficult to tell, but it does not sound
as if trying to read it from SQL is a very good idea. You probably much
better off doing this in a client language.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

pulling multiple entry's with jscript

I am new to jscript and trying to learn how to pull multiple entry's from a table. I know with php you can use a while loop but that doesn't seem to work with jscript. Here is what I have so far.

var sSql = "select nIndex,sDescription from StatisticalDiskIdentification " +
"JOIN PivotStatisticalMonitorTypeToDevice ON " +
"StatisticalDiskIdentification.nPivotStatisticalMonitorTypeToDeviceID = " +
"PivotStatisticalMonitorTypeToDevice.nPivotStatisticalMonitorTypeToDeviceID " +
"where (PivotStatisticalMonitorTypeToDevice.nStatisticalMonitorTypeID='2') and " +
"PivotStatisticalMonitorTypeToDevice.nDeviceID = " + nDeviceID;

while (oRs = oDb.Execute(sSql)){

if ( !oRs.EOF )
{
// Display various columns in the debug log (Event Viewer).
var sDisplay;
nIndex = "" + oRs("nIndex");
Context.LogMessage("nIndex=" + nIndex);
sDesc = "" + oRs("sDescription");
Context.LogMessage("Description =" + sDesc);
}
Context.SetResult( 0, " Ok");
oRs.MoveNext();
}

Thanks for any help

You should move Execute outside of the loop if your intention is to iterate through the recordset.
!oRs.EOF should be the loop condition instead.

Pulling data from tables using ColumnID

My problem is I need to loop through columns in a table and refer to each column using its ColumnID, all this I can do, I can even pull back the Cloumn Name from the system tables, but I can't find a way of pulling the data from this into a variable...

The code below will give me the data I need, but I don't know how to put the result from the exec statement into a variable?

select @.sql = 'select ' + name + ' from Test'
from syscolumns
where colid = @.i and id = object_id('Test')
exec(@.sql)

Please help

thanks,

ConanHi,

you might use
exec sp_executesql <cmd>, <paramlist>, <parameters>

this will allow you to get the result from some cmd like
'select @.Data='+@.columname+' from '+@.tablename

have a look into bol|||Hi,

Unfortunately this didn't work...

It has the result of only returning the column name again, rather than its data, have been stumped now on this little thing for a couple of days. If you can help please do, thanks,

DECLARE @.i int
DECLARE @.colname nvarchar(200)
SET @.i = 5

--Gets the column name of the column we want using its Column ID
select @.colname = name from syscolumns where colid = @.i and id = object_id('Test')

--Why does the below merely return the column name rather than its data
select @.colname from test

--The below ruturns the correct value, but I can't/don't know how to store it.
DECLARE @.sql nvarchar(1000)
DECLARE @.data int
select @.sql = 'select ' + @.colname + ' from Test'
EXEC sp_executesql @.sql
go

thanks,

Conan|||Hi,

as i said before, you can use sp_executesql. read bol for details...
you have to distinguish between vars and their values. your "select @.colname from test" will select whatever is the value of colname, regardless the table you specify! even without a table name the cmd will show your var value.

declare @.Table sysname, @.ID int, @.ColName nvarchar(200), @.CMD nvarchar(1000), @.ColData varchar(20)

select @.Table='test', @.ID=5

-- get column name for table / colid
select @.ColName=name from syscolumns where colid=@.ID and id= object_id(@.Table)

-- build up cmd string for pulling data
select @.CMD='select @.Param = '+@.ColName+' from '+@.Table

-- show what we have so far
print '@.Table = >'+@.Table+'<'
print '@.COLNAME = >'+@.ColName+'<'
print '@.CMD= >'+@.CMD+'<'

-- execute the cmd string pulling data from @.colname into @.ColData via @.Param
exec sp_executesql @.CMD, N'@.Param varchar(20) output', @.Param = @.ColData output

-- here we are
print '@.COLDATA= >'+@.COLDATA+'<'

Publishing database hangs on first article

When adding a databases to publications, the process gets stuck / goes into
endless loop on the first article of the database. This only happens on
databases that were previously replicated, others run through without the
slightest problem. We also checked the transactions issued with sp_who2 /
trace, it looks like the transaction goes into a loop of selecting and
updating. We have checked all the replication related system tables in an
attempt to properly remove any traces of previous publication, but to no
avail. Can anyone help? Thanks in advance
What build of SQL Server are you on? run select @.@.version to determine
this. Is it a build greater than 8.00.818?
You can try manually removing and recreating your publication as per KB:
How to manually remove a replication in SQL Server 2000 - ID: 324401
(available at http://support.microsoft.com)
Fany Vargas
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their
Microsoft software to better protect against viruses and security
vulnerabilities. The easiest way to do this is to visit the following
websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx