go to page x of y fast - not supported in sql server 2008?

I've been using full-text for quite some time, but I switched to another product for quite some time. The reason is that microsoft full-text doesn't offer the feature where one can get a resultset of page x out of y pages, i.e: give me top 100 matches of page 125 out of 5000 pages. I disappointedly waited for SQL Server 2005 and now it doesn't seem to support in sql server 2008 full-text search either. I had to get all 5000 pages of data, then jumped to page 125 to get just 100 records of that page--had to go get a coffee and came back for a 2+ millions rows table. Customers want the result in a split of a second.

Anyone knows if the final version of sql server 2008 will have better support for paging and getting top x rows? I scanned through 2008 BOL and didn't see any change from sql server 2005 or even sql server 2000 full-text search. (one improvement in sql server 2005 over sql server 2000 was the speed of populating large catalog and multiple languages support)

Thanks,

HH

As the final or even the CTP version with these functions is not out, you won′t get a defintite answer to your questions, but there are (even more than) plans to integrate the fulltext engine in the relational engine which will be able to do query optimization on the fulltext part as well. But you never know if it makes into the final version. :-)

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Go Through Each Row in Trigger

I was wondering if there was a go through each row in a updated/deleted table in a trigger. I have several booleans in a table and I want a final "complete" boolean to be updated depending on the values of the other booleans. Any easier way to do this?

Quote:

Originally Posted by nycninja

I was wondering if there was a go through each row in a updated/deleted table in a trigger. I have several booleans in a table and I want a final "complete" boolean to be updated depending on the values of the other booleans. Any easier way to do this?

Hi you can open a sql cursor from inserted tables or deleted table that contains the data you have inserted deleted or updated.

After you open a cursor you can loop wile cursor goto eof like a ado recordset cursor.

Look cursor transact-sql statement.

GO statement in SQL scripts

I have inherited a large script which uses DateFrom and DateTo parameters in 4 separate places in the script. I would like to DECLARE these parameters and put them into 2 variables to use in the 4 instances (instead of having to alter the script every time I run it). My problem is that the GO statement ends a batch of commands and the script loses the value of my variables.

Can anyone tell me exactly what the significance of the GO statement is in a SQL script, what it does and under what circumstances it is really necessary.

Thanks for your help.

itsmarkdavies@.hotmail.combasically you need a go any time you issue a:

CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE TRIGGER, or CREATE VIEW statment. Also a table can not be altered and then referenced in the same batch.

Check out Books On Line, on the index tab type in the keyword "GO". BOL has a very goo explination of the use of "GO" and batches in general.|||So Paul it sounds like 'GO' is like 'commit' when you create new objects in your script ?|||Not quite, transactions are used to group operations into a logical work unit which you commit or rollback based on some test. A "go" is syntacticaly required to execute some TSQL statments. Consider the following:

Code:
----------------------------
begin transaction
create procedure test_getstuff as select db_name(), user_id()
exec test_getstuff
rollback transaction
if object_id('test_getstuff') is not null
print 'test_getstuff exists'
else
print 'test_getstuff does not exist'
----------------------------
will generate an error, however

Code:
----------------------------
begin transaction
go
create procedure test_getstuff as select db_name(), user_id()
exec test_getstuff
rollback transaction
if object_id('test_getstuff') is not null
print 'test_getstuff exists'
else
print 'test_getstuff does not exist'
go
sp_helptext test_getstuff
go
----------------------------
will work because TSQL requires a create procedure to be the first command in a batch. Notice, however, that the stored procedure does a little more than expected. Lastly:

Code:
----------------------------
begin transaction
go
create procedure test_getstuff as select db_name(), user_id()
go
exec test_getstuff
sp_helptext test_getstuff
rollback transaction
if object_id('test_getstuff') is not null
print 'test_getstuff exists'
else
print 'test_getstuff does not exist'
go
----------------------------
The 2nd go is used to terminate the batch. Now the sp looks correct.

Notice that in all of this I destroyed all my work by wrapping everything in a transaction. I could just ahve easily saved all my work by using a commit.|||Hi,

Use the "GO" statement if you want to end the SQL Statements and want SQL Server to execute till that.

Mr. Paul and Mr.Youngman has explained in a nice way.

You can add these below basic info also along with that.

Basically SQL Server must understand where Your SQL script ends.
So, Microsoft uses "GO" to terminate/end SQL Statements. Oracle uses ";" to terminate/end SQL Statements.

As soon as SQL Server sees "Go" statement it understands that SQL Statement is ended and it can start executing the SQL Statements till that point.

I can put in this way, like a man looking for pebbles. as soon as he finds a pebble, he picks and puts in a bag and looks nearby whether there are some more and keep going. Similarly SQL Server looks for "Go" and as soon as it sees "GO" it thinks, Ok I found a "go" and I have to end there and pick all the SQL Statements I come across and start executing.

Thats it.

Have Fun :)
Varad01

GO Statement

Hi,
I've notied when I execute SQL Transact statements I can do it without
GO Statement. But when I refer to a lot of online documents, I see GO
statements. So from the practice/good coding perspective, when shall I
put GO statement? Why?
Thanks a lot!!
Michael
Michael,
Did you look into BOL before posting the question?
GO (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms188037.aspx
AMB
"Michael" wrote:

> Hi,
> I've notied when I execute SQL Transact statements I can do it without
> GO Statement. But when I refer to a lot of online documents, I see GO
> statements. So from the practice/good coding perspective, when shall I
> put GO statement? Why?
> Thanks a lot!!
> Michael
>

GO Statement

Hi,
I've notied when I execute SQL Transact statements I can do it without
GO Statement. But when I refer to a lot of online documents, I see GO
statements. So from the practice/good coding perspective, when shall I
put GO statement? Why?
Thanks a lot!!
MichaelMichael,
Did you look into BOL before posting the question?
GO (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms188037.aspx
AMB
"Michael" wrote:

> Hi,
> I've notied when I execute SQL Transact statements I can do it without
> GO Statement. But when I refer to a lot of online documents, I see GO
> statements. So from the practice/good coding perspective, when shall I
> put GO statement? Why?
> Thanks a lot!!
> Michael
>

GO Statement

Hi,
I've notied when I execute SQL Transact statements I can do it without
GO Statement. But when I refer to a lot of online documents, I see GO
statements. So from the practice/good coding perspective, when shall I
put GO statement? Why?
Thanks a lot!!
MichaelMichael,
Did you look into BOL before posting the question?
GO (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms188037.aspx
AMB
"Michael" wrote:
> Hi,
> I've notied when I execute SQL Transact statements I can do it without
> GO Statement. But when I refer to a lot of online documents, I see GO
> statements. So from the practice/good coding perspective, when shall I
> put GO statement? Why?
> Thanks a lot!!
> Michael
>

Go and goto in one sql script gives error label not declared

Hi,

I have a problem:
I am writing an update script for a database and want to check for the
version and Goto the wright update script.

So I read the version from a table and if it match I want to "Goto
Versionxxx"

Where Versionxxx: is set in the script with the right update script.

Whenever I have some script which need Go commands I get error in the
output that

A GOTO statement references the label 'Versionxxx' but the label has
not been declared.

But the label is set in the script by 'Versionxxx:'

Is there a way I can solve this easily?

Thanks in advanceHere's the trick with "GO":

It's not actually a part of the T-SQL language. It's a batch
separator. (Don't believe me? Try running "exec('GO')" in Query
Analyzer.)

Think of it like this: Cut up your script into multiple files,
separated by the "GO" statement. Run each of these files individually,
but use the same connection. That's all "GO" does.

So you need to remove the "GO" batch separators in between your
statements that need to be run in the same batch.

-Dave Markle
http://www.markleconsulting.com/blog
BF wrote:

Quote:

Originally Posted by

Hi,
>
I have a problem:
I am writing an update script for a database and want to check for the
version and Goto the wright update script.
>
So I read the version from a table and if it match I want to "Goto
Versionxxx"
>
Where Versionxxx: is set in the script with the right update script.
>
Whenever I have some script which need Go commands I get error in the
output that
>
A GOTO statement references the label 'Versionxxx' but the label has
not been declared.
>
But the label is set in the script by 'Versionxxx:'
>
Is there a way I can solve this easily?
>
Thanks in advance

|||Thanks for the quick respond.

The solution is not quite what I was hoping for.

For each new version I create an update script, We have an app which
does that and there are lots of Go commands.

I want to have one update script for all versions of the app so we have
2.00 to 2.01 to 2.02 to 2.03 etc.

For each version I have a script and I want to lookup the version, if
version is 2.03 I can start updating from 2.03 to 2.04 with the goto I
can jump over all other updates because they are already done in the
past.

When I use different files I cannot easy control which files to
execute, or I have to run them from the main script.

Grtx Bob

dmarkle schreef:

Quote:

Originally Posted by

Here's the trick with "GO":
>
It's not actually a part of the T-SQL language. It's a batch
separator. (Don't believe me? Try running "exec('GO')" in Query
Analyzer.)
>
Think of it like this: Cut up your script into multiple files,
separated by the "GO" statement. Run each of these files individually,
but use the same connection. That's all "GO" does.
>
So you need to remove the "GO" batch separators in between your
statements that need to be run in the same batch.
>
-Dave Markle
http://www.markleconsulting.com/blog
>

|||To be totally honest with you, I think the easiest/best way to solve
this would be to write a batch file that calls OSQL or SQLCMD against
the proper version of the file. Put your version-switching logic in
the batch file, and simply run OSQL on the appropriate files.

Some people execute their batches using sp_executesql, but it's really
messy and I don't really recommend it. Basically, using this method,
you'd be doing things like:

EXEC sp_executesql 'CREATE TABLE dbo.foo'
EXEC sp_executesql 'CREATE INDEX IX_xxx ON dbo.foo'
...

instead of:

CREATE TABLE dbo.foo
GO
CREATE INDEX IX_xxx ON dbo.foo
...

AFAIK, that's the only way to do what you want to do in 100% pure
T-SQL.

-Dave

BF wrote:

Quote:

Originally Posted by

Thanks for the quick respond.
>
The solution is not quite what I was hoping for.
>
For each new version I create an update script, We have an app which
does that and there are lots of Go commands.
>
I want to have one update script for all versions of the app so we have
2.00 to 2.01 to 2.02 to 2.03 etc.
>
For each version I have a script and I want to lookup the version, if
version is 2.03 I can start updating from 2.03 to 2.04 with the goto I
can jump over all other updates because they are already done in the
past.
>
When I use different files I cannot easy control which files to
execute, or I have to run them from the main script.
>
Grtx Bob
>
dmarkle schreef:

Quote:

Originally Posted by

Here's the trick with "GO":

It's not actually a part of the T-SQL language. It's a batch
separator. (Don't believe me? Try running "exec('GO')" in Query
Analyzer.)

Think of it like this: Cut up your script into multiple files,
separated by the "GO" statement. Run each of these files individually,
but use the same connection. That's all "GO" does.

So you need to remove the "GO" batch separators in between your
statements that need to be run in the same batch.

-Dave Markle
http://www.markleconsulting.com/blog

|||BF (bob@.faessen.net) writes:

Quote:

Originally Posted by

For each new version I create an update script, We have an app which
does that and there are lots of Go commands.

No there isn't. There are a lot of GO separators.

Quote:

Originally Posted by

I want to have one update script for all versions of the app so we have
2.00 to 2.01 to 2.02 to 2.03 etc.
>
For each version I have a script and I want to lookup the version, if
version is 2.03 I can start updating from 2.03 to 2.04 with the goto I
can jump over all other updates because they are already done in the
past.
>
When I use different files I cannot easy control which files to
execute, or I have to run them from the main script.

Right. The best way is to solve this is to write a little script runner that
reads a suite of files, and from the file names decudes which version the
file applies to, and then runs the file if needed. Your script would have to
break the script apart on the "go" separator, but this is trivial stuff.
(Hint: don't worry about "go" being entwined in comments ot string literals.
The standard query tools don't do that either. But care about leading and
trailing blanks, and inconsistent use of upper/lowercase.)

You can write this simple script runner in about any language - except for
T-SQK.

--
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|||Ok, great answers:

I build the updates with installshield 12 and I am no programmer so I
will go and try which will fit for me.

Probably I will place all scripts in the support dir from installshield
12 and run the from a vbscript with sqlcmd based on some tests.

I will try some things this week.

Thanks for the replies.

Grtx Bob

Erland Sommarskog schreef:

Quote:

Originally Posted by

BF (bob@.faessen.net) writes:

Quote:

Originally Posted by

For each new version I create an update script, We have an app which
does that and there are lots of Go commands.

>
No there isn't. There are a lot of GO separators.
>

Quote:

Originally Posted by

I want to have one update script for all versions of the app so we have
2.00 to 2.01 to 2.02 to 2.03 etc.

For each version I have a script and I want to lookup the version, if
version is 2.03 I can start updating from 2.03 to 2.04 with the goto I
can jump over all other updates because they are already done in the
past.

When I use different files I cannot easy control which files to
execute, or I have to run them from the main script.

>
Right. The best way is to solve this is to write a little script runner that
reads a suite of files, and from the file names decudes which version the
file applies to, and then runs the file if needed. Your script would have to
break the script apart on the "go" separator, but this is trivial stuff.
(Hint: don't worry about "go" being entwined in comments ot string literals.
The standard query tools don't do that either. But care about leading and
trailing blanks, and inconsistent use of upper/lowercase.)
>
You can write this simple script runner in about any language - except for
T-SQK.
>
--
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

Go ahead

Spam me! I'm trying a new spam filter
La2828@.sympatico.ca
LOL...you left your gmail address in as well...you may get more spam than
you were hoping for
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
<remifaucher420@.gmail.com> wrote in message
news:1164659717.840247.151170@.n67g2000cwd.googlegr oups.com...
> Spam me! I'm trying a new spam filter
> La2828@.sympatico.ca
>
|||I don't mind, either one!
But especially La2828@.sympatico.ca !!!
please
Kevin3NF wrote:[vbcol=seagreen]
> LOL...you left your gmail address in as well...you may get more spam than
> you were hoping for
> --
> Kevin Hill
> 3NF Consulting
> http://www.3nf-inc.com/NewsGroups.htm
> Real-world stuff I run across with SQL Server:
> http://kevin3nf.blogspot.com
>
> <remifaucher420@.gmail.com> wrote in message
> news:1164659717.840247.151170@.n67g2000cwd.googlegr oups.com...

Go ahead

Spam me! I'm trying a new spam filter
La2828@.sympatico.caLOL...you left your gmail address in as well...you may get more spam than
you were hoping for :)
--
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
<remifaucher420@.gmail.com> wrote in message
news:1164659717.840247.151170@.n67g2000cwd.googlegroups.com...
> Spam me! I'm trying a new spam filter
> La2828@.sympatico.ca
>|||I don't mind, either one!
But especially La2828@.sympatico.ca !!!
please
Kevin3NF wrote:
> LOL...you left your gmail address in as well...you may get more spam than
> you were hoping for :)
> --
> Kevin Hill
> 3NF Consulting
> http://www.3nf-inc.com/NewsGroups.htm
> Real-world stuff I run across with SQL Server:
> http://kevin3nf.blogspot.com
>
> <remifaucher420@.gmail.com> wrote in message
> news:1164659717.840247.151170@.n67g2000cwd.googlegroups.com...
> > Spam me! I'm trying a new spam filter
> >
> > La2828@.sympatico.ca
> >

Go ahead

Spam me! I'm trying a new spam filter
La2828@.sympatico.caLOL...you left your gmail address in as well...you may get more spam than
you were hoping for
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
<remifaucher420@.gmail.com> wrote in message
news:1164659717.840247.151170@.n67g2000cwd.googlegroups.com...
> Spam me! I'm trying a new spam filter
> La2828@.sympatico.ca
>|||I don't mind, either one!
But especially La2828@.sympatico.ca !!!
please
Kevin3NF wrote:[vbcol=seagreen]
> LOL...you left your gmail address in as well...you may get more spam tha
n
> you were hoping for
> --
> Kevin Hill
> 3NF Consulting
> http://www.3nf-inc.com/NewsGroups.htm
> Real-world stuff I run across with SQL Server:
> http://kevin3nf.blogspot.com
>
> <remifaucher420@.gmail.com> wrote in message
> news:1164659717.840247.151170@.n67g2000cwd.googlegroups.com...

Go

CAN NE ONE SPECIFY THE USE OF GO IN SCRIPTS ?Check SQL Server books online.|||Collect $200.00

Gnerating SQL scripts for database creation

In Enterprise Manager there exists the ability to have SQL scripts
generated for the objects of each database. I do not see the ability to
have an an SQL script generated for the database itself. Does such an
ability exist ? I am using SQL Server 7, so maybe this ability does not
exist in that version but does in a later version.
I believe that it was added in 2000, but make sure you study the option ins the "Generate script"
dialog to make sure (I don't have a 7.0 to test on). Also, you might find something useful here:
http://www.karaszi.com/SQLServer/inf...ate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Edward Diener" <eddielee_no_spam_here@.tropicsoft.com> wrote in message
news:edm%23EKWWFHA.3840@.tk2msftngp13.phx.gbl...
> In Enterprise Manager there exists the ability to have SQL scripts generated for the objects of
> each database. I do not see the ability to have an an SQL script generated for the database
> itself. Does such an ability exist ? I am using SQL Server 7, so maybe this ability does not exist
> in that version but does in a later version.

GMT time to CST with daylight savings

I have the database on a GMT server and a logdate that is in GMT too. How do I convert this into CST (GMT -6) , during daylight savings (GMT-5) when passing it back to the webpage.

I can use DATEADD(hh,-6,LogDate) but how do I know if the daylight savings period has started to do DATEADD(hh,-5,LogDate). I also want to solve this at the database level without altering the front-end.

Thanks.

Is your database server in CST? And really, the conversion SHOULD be done on the front end.|||

database is on GMT.

Ok if on frontend how do i do it. I have around 7000 rows to be retrieved and all need to be converted to CST from GMT. Is there a quick way to do this. I have everything in the dataset/datatable.

|||Also the server now is on CST but can be moved to EST anyday. Can I have a key in web.config to be changed when this is done. Daylight savings is also an important issue here.

GMT Time

I have got GMT Time Data on my database.
It is possible to used the user's time on a chart or in a table ?You can convert GMT (now called UTC) time to local time using
DateTime.ToLocalTime() method. Check
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatetimeclasstolocaltimetopic.asp
for details.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Karine" <Karine@.discussions.microsoft.com> wrote in message
news:280C890D-4DE7-4DF1-9FD0-D6DD7C3E1191@.microsoft.com...
> I have got GMT Time Data on my database.
> It is possible to used the user's time on a chart or in a table ?

GMT date problems

hi,

my web site and sql server are hosted in USA whereas I live in Iceland. When I blog all dates are saved from the USA server's date using the GETDATE() function. Can I change some sql server settings to make it save for my local (icelandic) time?

The best thing you can do s to change the lcid.|||Try this link for some options. Hope this helps.
http://www.karaszi.com/SQLServer/info_datetime.asp

GML Support in 2008

BOL for SQL Server 2008 says that the geometry and geography data types
support a subset of GML functionality. Does anyone know if MS has a
shortened version of the GML XML Schema available that reflects the features
that are actually implemented in 2008?
Thanks
M> BOL for SQL Server 2008 says that the geometry and geography data
M> types support a subset of GML functionality. Does anyone know if MS
M> has a shortened version of the GML XML Schema available that reflects
M> the features that are actually implemented in 2008?
Please ping me off-line about this.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/
|||"Kent Tegels" <ktegels@.develop.com> wrote in message
news:57694768e15e8ca0965d6b9a114@.news.microsoft.co m...
>M> BOL for SQL Server 2008 says that the geometry and geography data
> M> types support a subset of GML functionality. Does anyone know if MS
> M> has a shortened version of the GML XML Schema available that reflects
> M> the features that are actually implemented in 2008?
> Please ping me off-line about this.
Hi Kent,
I went through the entire SQL Features spec. and tested everything in it to
see what worked and what didn't. Now I think I have a handle on what's
implemented and what's not. Thanks!

GML Support in 2008

BOL for SQL Server 2008 says that the geometry and geography data types
support a subset of GML functionality. Does anyone know if MS has a
shortened version of the GML XML Schema available that reflects the features
that are actually implemented in 2008?
ThanksM> BOL for SQL Server 2008 says that the geometry and geography data
M> types support a subset of GML functionality. Does anyone know if MS
M> has a shortened version of the GML XML Schema available that reflects
M> the features that are actually implemented in 2008?
Please ping me off-line about this.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/|||"Kent Tegels" <ktegels@.develop.com> wrote in message
news:57694768e15e8ca0965d6b9a114@.news.microsoft.com...
>M> BOL for SQL Server 2008 says that the geometry and geography data
> M> types support a subset of GML functionality. Does anyone know if MS
> M> has a shortened version of the GML XML Schema available that reflects
> M> the features that are actually implemented in 2008?
> Please ping me off-line about this.
Hi Kent,
I went through the entire SQL Features spec. and tested everything in it to
see what worked and what didn't. Now I think I have a handle on what's
implemented and what's not. Thanks!

GML Support in 2008

BOL for SQL Server 2008 says that the geometry and geography data types
support a subset of GML functionality. Does anyone know if MS has a
shortened version of the GML XML Schema available that reflects the features
that are actually implemented in 2008?
Thanks
M> BOL for SQL Server 2008 says that the geometry and geography data
M> types support a subset of GML functionality. Does anyone know if MS
M> has a shortened version of the GML XML Schema available that reflects
M> the features that are actually implemented in 2008?
Please ping me off-line about this.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/
|||"Kent Tegels" <ktegels@.develop.com> wrote in message
news:57694768e15e8ca0965d6b9a114@.news.microsoft.co m...
>M> BOL for SQL Server 2008 says that the geometry and geography data
> M> types support a subset of GML functionality. Does anyone know if MS
> M> has a shortened version of the GML XML Schema available that reflects
> M> the features that are actually implemented in 2008?
> Please ping me off-line about this.
Hi Kent,
I went through the entire SQL Features spec. and tested everything in it to
see what worked and what didn't. Now I think I have a handle on what's
implemented and what's not. Thanks!

GML Support in 2008

BOL for SQL Server 2008 says that the geometry and geography data types
support a subset of GML functionality. Does anyone know if MS has a
shortened version of the GML XML Schema available that reflects the features
that are actually implemented in 2008?
ThanksM> BOL for SQL Server 2008 says that the geometry and geography data
M> types support a subset of GML functionality. Does anyone know if MS
M> has a shortened version of the GML XML Schema available that reflects
M> the features that are actually implemented in 2008?
Please ping me off-line about this.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/|||"Kent Tegels" <ktegels@.develop.com> wrote in message
news:57694768e15e8ca0965d6b9a114@.news.microsoft.com...
>M> BOL for SQL Server 2008 says that the geometry and geography data
> M> types support a subset of GML functionality. Does anyone know if MS
> M> has a shortened version of the GML XML Schema available that reflects
> M> the features that are actually implemented in 2008?
> Please ping me off-line about this.
Hi Kent,
I went through the entire SQL Features spec. and tested everything in it to
see what worked and what didn't. Now I think I have a handle on what's
implemented and what's not. Thanks!

GML Support in 2008

BOL for SQL Server 2008 says that the geometry and geography data types
support a subset of GML functionality. Does anyone know if MS has a
shortened version of the GML XML Schema available that reflects the features
that are actually implemented in 2008?
ThanksM> BOL for SQL Server 2008 says that the geometry and geography data
M> types support a subset of GML functionality. Does anyone know if MS
M> has a shortened version of the GML XML Schema available that reflects
M> the features that are actually implemented in 2008?
Please ping me off-line about this.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/|||"Kent Tegels" <ktegels@.develop.com> wrote in message
news:57694768e15e8ca0965d6b9a114@.news.microsoft.com...
>M> BOL for SQL Server 2008 says that the geometry and geography data
> M> types support a subset of GML functionality. Does anyone know if MS
> M> has a shortened version of the GML XML Schema available that reflects
> M> the features that are actually implemented in 2008?
> Please ping me off-line about this.
Hi Kent,
I went through the entire SQL Features spec. and tested everything in it to
see what worked and what didn't. Now I think I have a handle on what's
implemented and what's not. Thanks!

gmaniz service failed

Dear all,
please help me to find error about 'the gmaniz service failed to start
due to the following error'
Thanks
This is not a SQL Server service. In fact, I didn't get one single hit (except this post) on Google.
My guess is that this is some in-house developed software, so you need to find what it is and talk
to the people responsible for it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ekodian" <ekodian@.gmail.com> wrote in message news:e8beptSUFHA.3944@.tk2msftngp13.phx.gbl...
> Dear all,
> please help me to find error about 'the gmaniz service failed to start due to the following error'
> Thanks

gmaniz service failed

Dear all,
please help me to find error about 'the gmaniz service failed to start
due to the following error'
ThanksThis is not a SQL Server service. In fact, I didn't get one single hit (except this post) on Google.
My guess is that this is some in-house developed software, so you need to find what it is and talk
to the people responsible for it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ekodian" <ekodian@.gmail.com> wrote in message news:e8beptSUFHA.3944@.tk2msftngp13.phx.gbl...
> Dear all,
> please help me to find error about 'the gmaniz service failed to start due to the following error'
> Thanks

gmaniz service failed

Dear all,
please help me to find error about 'the gmaniz service failed to start
due to the following error'
ThanksThis is not a SQL Server service. In fact, I didn't get one single hit (exce
pt this post) on Google.
My guess is that this is some in-house developed software, so you need to fi
nd what it is and talk
to the people responsible for it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ekodian" <ekodian@.gmail.com> wrote in message news:e8beptSUFHA.3944@.tk2msftngp13.phx.gbl...

> Dear all,
> please help me to find error about 'the gmaniz service failed to start due
to the following error'
> Thanks

glyphs

When I export reports in pdf, I have glyphs in some textbox, I use Arial
normal 8, and I checked, the font is installed on the server and the client.
I only have the problem in acrobat 5. Any solution?Adobe 5.00 and 5.05 do not render symbols correctly when the Arial font is
used.
This table outlines some testing I did to determine how various Acrobat
versions behave when attempting to render the euro symbol. The results
should apply to symbols in general.
Version Font Handle Euro Notes
5.00 Arial No The text is also trashed when
the euro symbol is in the string, The text renders
5.05 correctly when the euro
symbol is removed.
5.10 Arial Yes The text and euro symbol render
correctly.
6.00
5.00 Tahoma Yes When this font is used the string and
euro symbol
5.50 rendered correctly.
5.10
6.00
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"raph" <raph@.discussions.microsoft.com> wrote in message
news:04488328-BAC7-4DCE-85D4-83A88D11A8E9@.microsoft.com...
> When I export reports in pdf, I have glyphs in some textbox, I use Arial
> normal 8, and I checked, the font is installed on the server and the
> client.
> I only have the problem in acrobat 5. Any solution?|||Thanks for your answer, but I am not using euro symbol, here is the text
"Anal. production, Syst. Décisionnels.â' Consultant". I think the problem is
"-".
Do you thing that changing font will resolve the problem?
"Bruce Johnson [MSFT]" wrote:
> Adobe 5.00 and 5.05 do not render symbols correctly when the Arial font is
> used.
> This table outlines some testing I did to determine how various Acrobat
> versions behave when attempting to render the euro symbol. The results
> should apply to symbols in general.
> Version Font Handle Euro Notes
> 5.00 Arial No The text is also trashed when
> the euro symbol is in the string, The text renders
> 5.05 correctly when the euro
> symbol is removed.
> 5.10 Arial Yes The text and euro symbol render
> correctly.
> 6.00
> 5.00 Tahoma Yes When this font is used the string and
> euro symbol
> 5.50 rendered correctly.
> 5.10
> 6.00
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "raph" <raph@.discussions.microsoft.com> wrote in message
> news:04488328-BAC7-4DCE-85D4-83A88D11A8E9@.microsoft.com...
> > When I export reports in pdf, I have glyphs in some textbox, I use Arial
> > normal 8, and I checked, the font is installed on the server and the
> > client.
> > I only have the problem in acrobat 5. Any solution?
>
>|||Everything is perfect with the font Microsoft Sans Sherif!!
"raph" wrote:
> When I export reports in pdf, I have glyphs in some textbox, I use Arial
> normal 8, and I checked, the font is installed on the server and the client.
> I only have the problem in acrobat 5. Any solution?

Globals.Referrer?

Regards,
We need to setup a report so that it only runs if the referrer is a certain
URL. This is so that end users cannot directly navigate to the report (or
if they know the URL, plop it into the address bar and navigate there).
Instead, we'd like the report to check if they're coming from a particular
application and run ONLY if it sees that it's coming from that application.
Any ideas?
Any help would be greatly appreciated!
Benjamin PierceI would
Try to write some code to override the on_init method of the report and
check for the referrer
--
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
"Benjamin Pierce" <bpierce@.opentext.com> wrote in message
news:uWIxXNyWFHA.3240@.TK2MSFTNGP10.phx.gbl...
> Regards,
> We need to setup a report so that it only runs if the referrer is a
> certain
> URL. This is so that end users cannot directly navigate to the report (or
> if they know the URL, plop it into the address bar and navigate there).
> Instead, we'd like the report to check if they're coming from a particular
> application and run ONLY if it sees that it's coming from that
> application.
> Any ideas?
> Any help would be greatly appreciated!
>
> Benjamin Pierce
>|||Is the On_Imit method part of SQL 2005 Reporting Services? I wasn't aware
of report methods in SQL 2000.
Let me know.
Thanks!
Ben
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%237pUQr4WFHA.2796@.TK2MSFTNGP09.phx.gbl...
> I would
> Try to write some code to override the on_init method of the report and
> check for the referrer
> --
> 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
> "Benjamin Pierce" <bpierce@.opentext.com> wrote in message
> news:uWIxXNyWFHA.3240@.TK2MSFTNGP10.phx.gbl...
> > Regards,
> >
> > We need to setup a report so that it only runs if the referrer is a
> > certain
> > URL. This is so that end users cannot directly navigate to the report
(or
> > if they know the URL, plop it into the address bar and navigate there).
> > Instead, we'd like the report to check if they're coming from a
particular
> > application and run ONLY if it sees that it's coming from that
> > application.
> >
> > Any ideas?
> >
> > Any help would be greatly appreciated!
> >
> >
> > Benjamin Pierce
> >
> >
>

Globals!ReportServerUrl equivalent when using Sharepoint mode?

I use the javascript method to launch most of my drilldowns into their
own IE window:
="javascript:void(window.open('" & Globals!ReportServerUrl &
"?/Sales/Dashboard Drilldown - By Stage" & ...
But now that we just switched to Sharepoint Integrated mode with my
reports now residing in a document library, not on the Report Server,
what is the replacement to the Globals!ReportServerUrl variable?
Right now all my drilldowns say "The path of the item '/Dashboard
Drilldown - By Stage' is not valid..."Try giving the full URL if your server or most of the values are fixed, eg.
server URL, folder etc..
Amarnath
"Dave" wrote:
> I use the javascript method to launch most of my drilldowns into their
> own IE window:
> ="javascript:void(window.open('" & Globals!ReportServerUrl &
> "?/Sales/Dashboard Drilldown - By Stage" & ...
> But now that we just switched to Sharepoint Integrated mode with my
> reports now residing in a document library, not on the Report Server,
> what is the replacement to the Globals!ReportServerUrl variable?
> Right now all my drilldowns say "The path of the item '/Dashboard
> Drilldown - By Stage' is not valid..."
>|||On Apr 30, 3:50 am, Amarnath <Amarn...@.discussions.microsoft.com>
wrote:
> Try giving the full URL if your server or most of the values are fixed, eg.
That could work, but not very useful for portability when deploying to
test servers and then different production servers. I'd have to
manually change dozens of references to a specifc hard coded URL
string in dozens of reports. That's what made the built-in Globals!
ReportServerUrl so useful. It automatically retrieved the current
server URL so the report author doesn't manually hard code a server
URL inside a report. Any other ideas?
THANKS!

Globals!ReportName behaves differently when SSRS integrated with M

I have a report where I use Globals!ReportName in the header of the report
for the report title. In Development and on SSRS stand alone the value for
Globals!ReportName is in mixed case and the file extension is omitted. When
the report is published to a MOSS server integrated with SSRS the value for
Globals!ReportName is all in lower case and the file extension is included.
Is there any reason for this change in behavior and is there a way I can put
back the mixed case and omit the file extension?I just noticed this is also affecting the root level of the Document Map with
one exception. The case is all in lower case but the file extension is
omitted when rendered on MOSS. It works fine on SSRS.
"Ben Holcombe" wrote:
> I have a report where I use Globals!ReportName in the header of the report
> for the report title. In Development and on SSRS stand alone the value for
> Globals!ReportName is in mixed case and the file extension is omitted. When
> the report is published to a MOSS server integrated with SSRS the value for
> Globals!ReportName is all in lower case and the file extension is included.
> Is there any reason for this change in behavior and is there a way I can put
> back the mixed case and omit the file extension?

Globals!FileName.Value?

Does anyone know if there is a Global that stores the file name? I am
looking to put something in the footer that lists the path and filename
of the report.
=Globals!ReportFolder + Globals!ReportName
Doesn't seem to work...any suggestions'
thnx
-benScratch that...it works ;-)
sullins602 wrote:
> Does anyone know if there is a Global that stores the file name? I am
> looking to put something in the footer that lists the path and filename
> of the report.
> =Globals!ReportFolder + Globals!ReportName
> Doesn't seem to work...any suggestions'
> thnx
> -ben

Globally Change SQL Svc Account passwords

Hello. Is there any batch method to do a mass change of
multiple server's SQL Server Service account and/or
passwords? Just looking for any tips on changing MANY SQL
boxes' Service startup accounts. Is there any batch
commands to set a service account logon? THanks, BruceBelow is a VBScript to change service passwords. It will change the
password for all of the services on the specified servers running under
the account.
'begin script
Option Explicit
Dim oWin32_Services, oWin32_Service
Dim ServerName, StartName, NewPassword, Messages
' *** specify Windows account and new password here ***
StartName = "Domain\Account" 'Windows service account name
NewPassword = "NewAccountPassword"
' **************************
' *** specify server list here ***
Call ChangeServerServicePasswords("ServerName1", _
StartName, _
NewPassword, _
Messages)
Call ChangeServerServicePasswords("ServerName2", _
StartName, _
NewPassword, _
Messages)
' **************************
WScript.Echo Messages
Sub ChangeServerServicePasswords(ServerName, _
StartName, _
NewPassword, _
Messages)
Dim SQL
'select all services running under this account
SQL = "SELECT * FROM Win32_Service WHERE StartName = '" & _
Replace(StartName, "\", "\\") & "'"
Set oWin32_Services =GetObject("winmgmts:{impersonationLevel=impersonate}!//" & _
ServerName & _
"/root/cimv2").ExecQuery(SQL, , 48)
For Each oWin32_Service In oWin32_Services
Call ChangeServicePassword(oWin32_Service, _
NewPassword, _
Messages)
Next
End Sub
Sub ChangeServicePassword(oWin32_Service, _
NewPassword, _
Messages)
Dim intResult
intResult = oWin32_Service.Change(,,,,,,,NewPassword)
If intResult = 0 Then
Messages = Messages & _
oWin32_Service.SystemName & " " & _
oWin32_Service.Caption & _
" service account password changed for account " & _
oWin32_Service.StartName & vbcrlf
Else
Messages = Messages & _
oWin32_Service.SystemName & " " & _
oWin32_Service.Caption & _
" service account password change failed for account " & _
oWin32_Service.StartName & _
". Win32_Service.Change result is " & _
CStr(intResult) & vbcrlf
End If
End Sub
'end scrpt
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:056a01c3addf$98198f30$a001280a@.phx.gbl...
> Hello. Is there any batch method to do a mass change of
> multiple server's SQL Server Service account and/or
> passwords? Just looking for any tips on changing MANY SQL
> boxes' Service startup accounts. Is there any batch
> commands to set a service account logon? THanks, Bruce|||Maybe if you code something through WMI or something
similar, but there is no way I know of.
And if you're on a cluster, you have to do it through
Enterprise Manager anyway, so it wouldn't take care of
failover clustering installations of SQL Server.
>--Original Message--
>Hello. Is there any batch method to do a mass change of
>multiple server's SQL Server Service account and/or
>passwords? Just looking for any tips on changing MANY
SQL
>boxes' Service startup accounts. Is there any batch
>commands to set a service account logon? THanks, Bruce
>.
>|||wow... Great stuff Dan, thanks! Bruce
>--Original Message--
>Below is a VBScript to change service passwords. It will
change the
>password for all of the services on the specified servers
running under
>the account.
>
>'begin script
>Option Explicit
>Dim oWin32_Services, oWin32_Service
>Dim ServerName, StartName, NewPassword, Messages
>' *** specify Windows account and new password here ***
>StartName = "Domain\Account" 'Windows service account
name
>NewPassword = "NewAccountPassword"
>' **************************
>' *** specify server list here ***
>Call ChangeServerServicePasswords("ServerName1", _
> StartName, _
> NewPassword, _
> Messages)
>Call ChangeServerServicePasswords("ServerName2", _
> StartName, _
> NewPassword, _
> Messages)
>' **************************
>WScript.Echo Messages
>Sub ChangeServerServicePasswords(ServerName, _
> StartName, _
> NewPassword, _
> Messages)
> Dim SQL
> 'select all services running under this account
> SQL = "SELECT * FROM Win32_Service WHERE StartName
= '" & _
> Replace(StartName, "\", "\\") & "'"
> Set oWin32_Services =>GetObject("winmgmts:{impersonationLevel=impersonate}!//"
& _
> ServerName & _
> "/root/cimv2").ExecQuery(SQL, , 48)
> For Each oWin32_Service In oWin32_Services
> Call ChangeServicePassword(oWin32_Service, _
> NewPassword, _
> Messages)
> Next
>End Sub
>Sub ChangeServicePassword(oWin32_Service, _
> NewPassword, _
> Messages)
> Dim intResult
> intResult = oWin32_Service.Change(,,,,,,,NewPassword)
> If intResult = 0 Then
> Messages = Messages & _
> oWin32_Service.SystemName & " " & _
> oWin32_Service.Caption & _
> " service account password changed for
account " & _
> oWin32_Service.StartName & vbcrlf
> Else
> Messages = Messages & _
> oWin32_Service.SystemName & " " & _
> oWin32_Service.Caption & _
> " service account password change failed for
account " & _
> oWin32_Service.StartName & _
> ". Win32_Service.Change result is " & _
> CStr(intResult) & vbcrlf
> End If
>End Sub
>'end scrpt
>
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>--
>SQL FAQ links (courtesy Neil Pike):
>http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
>http://www.sqlserverfaq.com
>http://www.mssqlserver.com/faq
>--
>"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
>news:056a01c3addf$98198f30$a001280a@.phx.gbl...
>> Hello. Is there any batch method to do a mass change of
>> multiple server's SQL Server Service account and/or
>> passwords? Just looking for any tips on changing MANY
SQL
>> boxes' Service startup accounts. Is there any batch
>> commands to set a service account logon? THanks, Bruce
>
>.
>

Globalization Support for MSAS 2000

Hi

Currentely i am using analysis Services with SQL SERVER 2000

OS:- Windows 2000 Server

Datasource:-Oracle

We need to show the data in the cubes for multiple countries.

But we are not able to show the data for diferent languges at the same time becoz

in windows 2000 default language on the server can be set to only one language

and the data in the cube can be shown in same language

Ex Let say we wud liket see the data in the cube for Russia and turkey on the same server.If we set the default language of the server as "Rusia" or "Cyrlic"

Then we are able to see data for Special Russian chars but then we are not able to see special turkey characters.

Please let me know if anybody has the answer for this.

Pl also let me know if same thing can be achieved using sql server 2005

You can reply me on email :- pankaj.bidwai@.uy-tcs.com

Thx and regards

Pankaj

Analysis Services 2000 allows you to serve up different translations from the same server, however they will all use the same coallation (used for sorting and comparison) which is I think the issue you are dealing with. The ability to display characters is detemined by the characterset of the client application.

In Analysis Services 2005, you can set the coalation for each translation of members.

Globalization & Localization in sql server reporting services.

Hi All,

I am new to sql server reporting services. I am planning to use sql server reporting services for one of my project. But i have some doubts. Please any one help me.

1) Is it possible to localize sql server reporting services?

2) Is it possible to read and display the search labels and other column headers from resource file?

3) Will it display numeric, date and sort based on culture?

4) If it is possible then where we have to make the setting for that? I mean in the sql server or in the asp.net code behind file?

5) Is there any samples available in the net.

Thanks in advance.

I'm new to reporting services as well but i'll try to answer a few of your questions.

3. it will display the data based on the language setting on the computer that is hosting the file I believe

Check out this article for some more infohttp://www.sqljunkies.com/WebLog/tpagel/archive/2005/07/24/16197.aspx

4. see the article above (just a heads up but there are no code behind files in reporting services)

5. there are some samples located on the msdn site (herehttp://msdn.microsoft.com/sql/bi/reporting/default.aspx also a good place to start for info )

|||Thanks saiham|||

Hi Vinoth, I am also new to this reporting service and now struggling with the globalization issue. Have you found out the answer to your second question "Is it possible to read and display search label and other column headrs from resource file"?

The report designer has a section for developer to create custom function or refernce to function from external assemblies. I wonder whether we can use this section to read the resource file. Any idea? I have zero knowledge on the Resource File. I'll highly appreciate any input. Thanks

Best regards,
Jess

|||

Hi jess, I posted this in many forums but not found any useful answers. I will get back to you if i found any.

|||

Hi, I wanna share with you guys, what I done on localization by making use of resource file.
I am new on this programming world, feel free to correct me.

Given the fact that we can add custom assembly to our report, my idea is to create a function (can be in any language) outside the report, which will return locale value for particular key. Then, create an assembly of the code, and add reference to the assembly from the report designer

The function I used as this:

Public shared Function ReturnString (ByVal myValue As String, ByVal myCI as Stirng) As String
Dim ci As New CultureInfo (myCI)
Dim rm As ResourceManager
rm = ResourceManager.CreateFileBasedResourceManager ("MyStrings", "e:\resources\", Nothing)
Return rm.GetString(myValue, ci)
End Function

Where MyStrings is my resource file name and "e:\resources" is the location where i stored all my resource files.

One thing to take note when adding custom assembly to report. The assembly we are referencing to, must be copied to report designer and report server. For SSRS 2005, copy the assembly to these 2 locations:

C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies
C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\bin

Hope this can be input to anyone who is looking for using resource file for localization for SSRS. If you have a better solution, please share with me.

Globalization

What is the the best for the globalization of server, severals instance or one instance and severals database on one server ?I would use separate instances if I had separate "processes" that critically can not be impacted by th other. One instance can fail, while the others remain unaffected.

That said, I have not seen 2000 fail over the last 2 years.

It really depends on what you are doing and what your server looks like.

For example, many large web presences have several servers where the data is replicated across to each.

What are you trying to do?

Globale Function

Hi !

Is it possible to declare a gloable function in reportServer that all reports could access? For each reports that use the same function i've declared it in the code property of the rapport but it will be easier if i could share the function for all reports.

Thanks !

You could move the functions into a custom assembly. Check the "custom assembly" section of this BOL page: http://msdn2.microsoft.com/en-us/library/ms155798.aspx

-- Robert

Global.asa for MS SQL 2000

Hello,
I'd like to use global.asa to connect a database in MS SQL 2000, running on
the same MS Server 2003. I wondered if any of you know about the code for
the two functions:
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Sub Application_OnStart
.....
End Sub
</SCRIPT>
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Sub Session_OnStart
......
End Sub
</SCRIPT>
Thanks a lot,
Hank
Why would you connect to the database in global.asa? Connect to the
database in the code of each page. You don't want to create a connection
object for the application or session scope. http://www.aspfaq.com/2053
http://www.aspfaq.com/
(Reverse address to reply.)
"Hank Leigh" <hairong@.msu.edu> wrote in message
news:e8DrD7z$EHA.4004@.tk2msftngp13.phx.gbl...
> Hello,
> I'd like to use global.asa to connect a database in MS SQL 2000, running
on
> the same MS Server 2003. I wondered if any of you know about the code for
> the two functions:
> <SCRIPT LANGUAGE=VBScript RUNAT=Server>
> Sub Application_OnStart
> ....
> End Sub
> </SCRIPT>
> <SCRIPT LANGUAGE=VBScript RUNAT=Server>
> Sub Session_OnStart
> .....
> End Sub
> </SCRIPT>
> Thanks a lot,
> Hank
>
|||Thank you. That makes a lot of sense. My database file is at
C:\Program Files\Microsoft SQL Server\MSSQL\Data
Would you mind sharing a code to use in each of the ASP page?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ewtt5k0$EHA.1604@.TK2MSFTNGP12.phx.gbl...
> Why would you connect to the database in global.asa? Connect to the
> database in the code of each page. You don't want to create a connection
> object for the application or session scope. http://www.aspfaq.com/2053
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Hank Leigh" <hairong@.msu.edu> wrote in message
> news:e8DrD7z$EHA.4004@.tk2msftngp13.phx.gbl...
> on
>
|||Thank you again. Instead of using global.asa, I embedded a connection string
in each page and it works really fast! Below is the code I modified from a
message at http://www.aspfaq.com, an excellent site:
cst = "Provider=SQLOLEDB;Data Source=(local);" & _
"Initial Catalog=signmeup;Network=DBMSSOCN;"& _
"User Id=sa;Password="
set signmeup = CreateObject("ADODB.Connection")
signmeup.open cst
All the best,
Hank
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ewtt5k0$EHA.1604@.TK2MSFTNGP12.phx.gbl...
> Why would you connect to the database in global.asa? Connect to the
> database in the code of each page. You don't want to create a connection
> object for the application or session scope. http://www.aspfaq.com/2053
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Hank Leigh" <hairong@.msu.edu> wrote in message
> news:e8DrD7z$EHA.4004@.tk2msftngp13.phx.gbl...
> on
>

Global.asa for MS SQL 2000

Hello,
I'd like to use global.asa to connect a database in MS SQL 2000, running on
the same MS Server 2003. I wondered if any of you know about the code for
the two functions:
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Sub Application_OnStart
....
End Sub
</SCRIPT>
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Sub Session_OnStart
.....
End Sub
</SCRIPT>
Thanks a lot,
HankWhy would you connect to the database in global.asa? Connect to the
database in the code of each page. You don't want to create a connection
object for the application or session scope. http://www.aspfaq.com/2053
http://www.aspfaq.com/
(Reverse address to reply.)
"Hank Leigh" <hairong@.msu.edu> wrote in message
news:e8DrD7z$EHA.4004@.tk2msftngp13.phx.gbl...
> Hello,
> I'd like to use global.asa to connect a database in MS SQL 2000, running
on
> the same MS Server 2003. I wondered if any of you know about the code for
> the two functions:
> <SCRIPT LANGUAGE=VBScript RUNAT=Server>
> Sub Application_OnStart
> ....
> End Sub
> </SCRIPT>
> <SCRIPT LANGUAGE=VBScript RUNAT=Server>
> Sub Session_OnStart
> .....
> End Sub
> </SCRIPT>
> Thanks a lot,
> Hank
>|||Thank you. That makes a lot of sense. My database file is at
C:\Program Files\Microsoft SQL Server\MSSQL\Data
Would you mind sharing a code to use in each of the ASP page?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ewtt5k0$EHA.1604@.TK2MSFTNGP12.phx.gbl...
> Why would you connect to the database in global.asa? Connect to the
> database in the code of each page. You don't want to create a connection
> object for the application or session scope. http://www.aspfaq.com/2053
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Hank Leigh" <hairong@.msu.edu> wrote in message
> news:e8DrD7z$EHA.4004@.tk2msftngp13.phx.gbl...
> on
>|||Thank you again. Instead of using global.asa, I embedded a connection string
in each page and it works really fast! Below is the code I modified from a
message at http://www.aspfaq.com, an excellent site:
cst = "Provider=SQLOLEDB;Data Source=(local);" & _
"Initial Catalog=signmeup;Network=DBMSSOCN;"& _
"User Id=sa;Password="
set signmeup = CreateObject("ADODB.Connection")
signmeup.open cst
All the best,
Hank
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ewtt5k0$EHA.1604@.TK2MSFTNGP12.phx.gbl...
> Why would you connect to the database in global.asa? Connect to the
> database in the code of each page. You don't want to create a connection
> object for the application or session scope. http://www.aspfaq.com/2053
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Hank Leigh" <hairong@.msu.edu> wrote in message
> news:e8DrD7z$EHA.4004@.tk2msftngp13.phx.gbl...
> on
>

Global.asa for MS SQL 2000

Hello,
I'd like to use global.asa to connect a database in MS SQL 2000, running on
the same MS Server 2003. I wondered if any of you know about the code for
the two functions:
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Sub Application_OnStart
....
End Sub
</SCRIPT>
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Sub Session_OnStart
.....
End Sub
</SCRIPT>
Thanks a lot,
HankWhy would you connect to the database in global.asa? Connect to the
database in the code of each page. You don't want to create a connection
object for the application or session scope. http://www.aspfaq.com/2053
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Hank Leigh" <hairong@.msu.edu> wrote in message
news:e8DrD7z$EHA.4004@.tk2msftngp13.phx.gbl...
> Hello,
> I'd like to use global.asa to connect a database in MS SQL 2000, running
on
> the same MS Server 2003. I wondered if any of you know about the code for
> the two functions:
> <SCRIPT LANGUAGE=VBScript RUNAT=Server>
> Sub Application_OnStart
> ....
> End Sub
> </SCRIPT>
> <SCRIPT LANGUAGE=VBScript RUNAT=Server>
> Sub Session_OnStart
> .....
> End Sub
> </SCRIPT>
> Thanks a lot,
> Hank
>|||Thank you. That makes a lot of sense. My database file is at
C:\Program Files\Microsoft SQL Server\MSSQL\Data
Would you mind sharing a code to use in each of the ASP page?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ewtt5k0$EHA.1604@.TK2MSFTNGP12.phx.gbl...
> Why would you connect to the database in global.asa? Connect to the
> database in the code of each page. You don't want to create a connection
> object for the application or session scope. http://www.aspfaq.com/2053
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Hank Leigh" <hairong@.msu.edu> wrote in message
> news:e8DrD7z$EHA.4004@.tk2msftngp13.phx.gbl...
>> Hello,
>> I'd like to use global.asa to connect a database in MS SQL 2000, running
> on
>> the same MS Server 2003. I wondered if any of you know about the code for
>> the two functions:
>> <SCRIPT LANGUAGE=VBScript RUNAT=Server>
>> Sub Application_OnStart
>> ....
>> End Sub
>> </SCRIPT>
>> <SCRIPT LANGUAGE=VBScript RUNAT=Server>
>> Sub Session_OnStart
>> .....
>> End Sub
>> </SCRIPT>
>> Thanks a lot,
>> Hank
>>
>|||Thank you again. Instead of using global.asa, I embedded a connection string
in each page and it works really fast! Below is the code I modified from a
message at http://www.aspfaq.com, an excellent site:
cst = "Provider=SQLOLEDB;Data Source=(local);" & _
"Initial Catalog=signmeup;Network=DBMSSOCN;"& _
"User Id=sa;Password="
set signmeup = CreateObject("ADODB.Connection")
signmeup.open cst
All the best,
Hank
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ewtt5k0$EHA.1604@.TK2MSFTNGP12.phx.gbl...
> Why would you connect to the database in global.asa? Connect to the
> database in the code of each page. You don't want to create a connection
> object for the application or session scope. http://www.aspfaq.com/2053
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Hank Leigh" <hairong@.msu.edu> wrote in message
> news:e8DrD7z$EHA.4004@.tk2msftngp13.phx.gbl...
>> Hello,
>> I'd like to use global.asa to connect a database in MS SQL 2000, running
> on
>> the same MS Server 2003. I wondered if any of you know about the code for
>> the two functions:
>> <SCRIPT LANGUAGE=VBScript RUNAT=Server>
>> Sub Application_OnStart
>> ....
>> End Sub
>> </SCRIPT>
>> <SCRIPT LANGUAGE=VBScript RUNAT=Server>
>> Sub Session_OnStart
>> .....
>> End Sub
>> </SCRIPT>
>> Thanks a lot,
>> Hank
>>
>

global vars in script files ?

Is it possible to declare a global variable in a SQL script ... or some
workaround ?
[ContentDB].[dbo].[PageTypes].[ptId] IDENTITY(int, 1,1)
In the following script, the local var @.ptId is lost once a "GO" is
executed.
USE [ContentDB]
GO
INSERT INTO [dbo].[PageTypes]
([ptName]
,[ptPath]
,[ptParamName])
VALUES
('unused'
,'/redirect.aspx'
,'url')
DECLARE @.ptId int
SET @.ptId = @.@.IDENTITY
.
.
.
.
<lots and lots of other SQL>
.
.
.
.
GO
.
.
.
.
<lots and lots of other SQL>
.
.
.There are no global variables in TSQL. You can use a temp table for this, or
check out SET
CONTEXT_INFO.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
news:eYtWs8JRGHA.1688@.TK2MSFTNGP11.phx.gbl...
> Is it possible to declare a global variable in a SQL script ... or some wo
rkaround ?
> [ContentDB].[dbo].[PageTypes].[ptId] IDENTITY(int, 1,1)
> In the following script, the local var @.ptId is lost once a "GO" is execut
ed.
> USE [ContentDB]
> GO
> INSERT INTO [dbo].[PageTypes]
> ([ptName]
> ,[ptPath]
> ,[ptParamName])
> VALUES
> ('unused'
> ,'/redirect.aspx'
> ,'url')
> DECLARE @.ptId int
> SET @.ptId = @.@.IDENTITY
> .
> .
> .
> .
> <lots and lots of other SQL>
> .
> .
> .
> .
> GO
> .
> .
> .
> .
> <lots and lots of other SQL>
> .
> .
> .
>

global variables urgent,

hey
i declare 3 global variables inside a formula.but can not refer those variables in another formula in the same report.
tell me how & where to declare global variables.
thanx.hi
try using this way :

formula @.x
numbervar a;
a:=10+20;

formula @.y
numbervar b;
numbervar a;
b:= 20+30;
b:=a+b;

as many variable as you want decalre in a formula, but the each formula should declare the variable.

This will give an idea about the global varialbles.
please try and let me know.|||Use shared variable also. Read it more in help file

global variables in dtspump task

I have two connections, an oracle and sqlserver. the dtspump task is pulling data from oracle to sqlserver. I am trying to automate this package by substituting the where clause values to variables since they are based on year and month. My question is - how does one use global variables in a dtspump task? How can I use variables to do this? Can it be done or do I need to use an Active X script for it?

thanks for any help.
novice userMay check with SQL DTS (http://www.sqldts.com) website.

Global Variables And the Script Component in DataFlow

I can't find anything on how to get to a global variable in a script component in the dataflow. I can get to it in a script task with no problem by using dts.variables but i doesn't appear you can do the dts variables in the script component.

I did add it to the readwrite variable list but I haven't been able to access it.

Type "Me.Variables." and upon typing the second period, intellisense will kick in and give you a alist of all the vars that you have put into ReadOnlyVariables/ReadWriteVariables.

-Jamie

Global Variables

Hello,
Im having a little problem(i hope) with global variables.
Im working with the DTS of the SQL SERVER 2000! Does someone knows how can i load global variables using an SQL Task!?
Thank you all!!
Kind Regards,
LULUin package properties, add global variable x with correct type
create execute sql task , select the value you want to
populate your global variable,click on parameters in the exec sql task properties,
click on output parameters,set type (row,rowset), map the output to the correct global variable.
To use the variable, in a exec sql task, click on parameters,
get your global variable, map it to parameter 1
then in query use a ? to reference the global variable.
-des|||Originally posted by DesmondX
in package properties, add global variable x with correct type
create execute sql task , select the value you want to
populate your global variable,click on parameters in the exec sql task properties,
click on output parameters,set type (row,rowset), map the output to the correct global variable.
To use the variable, in a exec sql task, click on parameters,
get your global variable, map it to parameter 1
then in query use a ? to reference the global variable.
-des

Thanks.... DESMONDX

Global Variables

Hi,

Is there a way to declare a persistent global variable in SQL Server?

I'd like my stored procs to fetch data in a different source depending on a debug (or development) variable.

For example, I'd like to be able to set a variable to either 0 or 1 (true or false) and have a static SP defined as:

IF @.MYVARIABLE = 1
SELECT * FROM Openquery(Server1, 'SELECT * FROM Table1")
ELSE
SELECT * FROM Openquery(Server2, 'SELECT * FROM Table2')

What do you think? Since these SPs should be called a lot, I don't want to store the info in a table, I want it as a global variableso it will be as fast as possible.

Any other suggestions are also welcomed.

Thanks,

Skip.Wouldn't you just put this into a table? Then add the statement:

DECLARE
@.Environment bit

SELECT @.Environment = Environment FROM tblFlags

IF @.Environment = 1
SELECT * FROM Openquery(Server1, 'SELECT * FROM Table1")
ELSE
SELECT * FROM Openquery(Server2, 'SELECT * FROM Table2')

But wouldn't it be better to have a dedicated test server with data refreshed as you require? Sorry, may not always be realistic, but just a thought.

Regards,

hmscott|||First of all, yes having a dev server makes way more sense. Actually, this is what we have here, the switch (the if...else) will be used to create the linked server to point to the correct server instance. The example above was only a reference and did not represent an actual situation.

Second, what I have now is a variable in a "commands" table. It works fine but the function is called so many times that it slows my system. I'd like to have a global variable in memory to speed up the process of reading its value.

Any other suggestions?

Thanks,

Skip.|||Nothing comes to mind. You can use the PINTABLE command to put that table into memory, but if it is called so frequently, it's probably already there.

You said you have a "commands" table, but that the function (what function?) is called so often that it slows the system. Just to check, is the table very large? Is it properly indexed? What do you consider to be heavy usage?

We have a particular sp that is called more or less each time a user hits a page on our site (to pull back configuration and web settings). We have about 300 users and about 2000 page hits per day (frequently more). I've never had any complaints about this particular sp running slowly.

Regards,

hmscott

Global variable value lost during insertion in a table

Hi,

This problem is connected with the query i posted yesterday regarding insertion of global variables. I was able to insert the variable in a table to check its value.

This value is mapped to the global variable in a previous Execute SQL Task. But when I use the same global variable to insert in a table, default value 0 is inserted.

My query is does the global variable declared at the package level does not store the value mapped across multiple tasks in control flow?

How can i insert the value stored in a variable in a table from previous SQL Task.

Can anyone suggest some solution,links to try a workaround?

Thanks in advance.

Regards,

Aman

Hello,

Im not sure i understand your question, but even so, i think that you want a variable to exist during all the execution of your DTS, so If you variable has a dts scope not a function or a package scope, should fix your problem.

Or you are telling that you have a dts that calls several packages and in one package you do your sql insert task and then you will call another package, inside the same dts?

Regards,

|||

Hi,

I had declared my variables with global scope(at package level). In my 1st 2 Execute SQL Tasks i was saving those variables as input(which was wrong!). When I changed the variable property as Output things worked fine. In my 3rd SQL Task I wanted to insert those variables.

Moreover I was working on a single package. Hopefully you got my requirement now?

anyway thanks for your reply.

Regards,

Aman

|||

Hello Aman,

I assume that you still have the problem altough you marked the thread as awnsered.

In the variables tab activate the show user variables button, check the information for that.

Afterwards check if the sql task has, in the properties, the result set to single row. If you are expecting to have more than 1 line, then you would need a foreach loop to read every value of the variable.

If all that im saying isnt helping you, use the breakpoints and check the locals tab for the value of the variables.

Because, the variables, shouldn't loose the value, something is wrong.

Hope you can get your problem sorted out.

Good luck

Global variable value lost during insertion in a table

Hi,

This problem is connected with the query i posted yesterday regarding insertion of global variables. I was able to insert the variable in a table to check its value.

This value is mapped to the global variable in a previous Execute SQL Task. But when I use the same global variable to insert in a table, default value 0 is inserted.

My query is does the global variable declared at the package level does not store the value mapped across multiple tasks in control flow?

How can i insert the value stored in a variable in a table from previous SQL Task.

Can anyone suggest some solution,links to try a workaround?

Thanks in advance.

Regards,

Aman

Hello,

Im not sure i understand your question, but even so, i think that you want a variable to exist during all the execution of your DTS, so If you variable has a dts scope not a function or a package scope, should fix your problem.

Or you are telling that you have a dts that calls several packages and in one package you do your sql insert task and then you will call another package, inside the same dts?

Regards,

|||

Hi,

I had declared my variables with global scope(at package level). In my 1st 2 Execute SQL Tasks i was saving those variables as input(which was wrong!). When I changed the variable property as Output things worked fine. In my 3rd SQL Task I wanted to insert those variables.

Moreover I was working on a single package. Hopefully you got my requirement now?

anyway thanks for your reply.

Regards,

Aman

|||

Hello Aman,

I assume that you still have the problem altough you marked the thread as awnsered.

In the variables tab activate the show user variables button, check the information for that.

Afterwards check if the sql task has, in the properties, the result set to single row. If you are expecting to have more than 1 line, then you would need a foreach loop to read every value of the variable.

If all that im saying isnt helping you, use the breakpoints and check the locals tab for the value of the variables.

Because, the variables, shouldn't loose the value, something is wrong.

Hope you can get your problem sorted out.

Good luck

Global variable scope - across package

Hi,

I have a Main.dtsx file which is having executing some 10 .dtsx packages. Can I declare a variable in Main.dtsx and use it in all the other packages which it is executing?

Note: I am using Yukon April CTP

Thanks
HariniYes you can. This post explains how: http://blogs.conchango.com/jamiethomson/archive/2005/09/01/2096.aspx

-Jamie|||Thanks. This is what exactly I want.

Global Variable question

Anyone out there know how to bring in the global variable value for where the
report was run from, whether it's the core report or a linked report? There
already is a global variable for Report Folder but that displays where the
core object sits so if you have a linked report sitting in a different
folder, you get misleading data.
It would be nice to be able to display the actual folder from where the
report was run.
Any ideas out there? Any help appreciated.
Thanks
rjsehOne thing you might try is to create a parameter, and have its data set query
the reporting server database to follow the link back to its parent...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"rjseh2001" wrote:
> Anyone out there know how to bring in the global variable value for where the
> report was run from, whether it's the core report or a linked report? There
> already is a global variable for Report Folder but that displays where the
> core object sits so if you have a linked report sitting in a different
> folder, you get misleading data.
> It would be nice to be able to display the actual folder from where the
> report was run.
> Any ideas out there? Any help appreciated.
> Thanks
> rjseh

Global variable in stored procedure

Hi I'm having problems using a global variable that has been declared as a varchar in a stored procedure. The stored procedure is recursive, and after calling itself I'm no longer able to access the global variable, see the sp bellow.

Any help would be great

Thanks

Dan

CREATE PROCEDURE dbo.kb_c_getChildren
(
@.CategoryID uniqueidentifier
)
AS
SET NOCOUNT ON

/* Global variables */
if @.@.nestlevel = 1
begin
declare @.@.CatList varchar (8000)
set @.@.CatList = ''
end

/* Find children */

declare @.child uniqueidentifier
declare children cursor local for
select CatID from Category where Parent_CatID = @.CategoryID

open children

fetch next from children
into @.child

while @.@.fetch_status = 0
begin
set @.@.CatList = @.@.CatList + '{' + cast(@.child as varchar(38)) + '},'
print cast(@.@.nestlevel as varchar(3)) + ' ' + cast(@.child as varchar(38))
if exists(select CatID from Category where Parent_CatID = @.child)
begin
/* If the child category has children, find them */
exec kb_c_getChildren @.child
end

fetch next from children
into @.child

end

close children
deallocate children

print @.@.CatList

RETURN 1When I looked in Books On Line for Global variables I didn't find anyhting to support what you are trying to do. Would this work?

CREATE PROCEDURE dbo.kb_c_getChildren(
@.CategoryID uniqueidentifier,
@.CatList varchar(8000) = Null OUTPUT)
AS
SET NOCOUNT ON

/* Find children */

declare @.child uniqueidentifier
select @.child = min(CatID)
from Category
where PArent_CatID = @.CategoryID

while (@.child is not null) begin
set @.CatList = @.CatList + '{' + cast(@.child as varchar(38)) + '},'
print cast(@.@.nestlevel as varchar(3)) + ' ' + cast(@.child as varchar(38))
if exists(select CatID from Category where Parent_CatID = @.child) begin
/* If the child category has children, find them */
exec kb_c_getChildren @.child, @.CatList OUTPUT
end

select @.child = min(CatID)
from Category
where Parent_CatID = @.CategoryID
and CatID > @.child
end

if (@.@.nestlevel = 1)
print @.CatList

RETURN 1

I changed from using a cursor to a simple select and test, this is just a personnal thing for me, and changed your catlist to be an optional output parameter. On the 2nd and subsequent calls to the SP you will past your populated catlist to kb_c_getChildren, modify the contents, and return it to the calling sp. on the last itiration you should fall out of the while loop print the results.|||Hi Paul,

Thanks for the info, couldn't use the select statement as I'm using uniqueidentifers, but the output parameter works a treat.

Thanks again

Dan

Global Variable in SQL Server

Hi,

I have a question on SQL Server.

How do I have a value that passing from application to Stored Proc. Then the variable will pass from stored proc to the trigger without storing into any table.

I have done by using declaring #TempTable on StoredProc and use it in Triggers but it doesn't work. Anyone know any alternative?

Please help.

Thanks

If those passed parameters are already inserted/updated on your table then you can access those values from the trigger using INSERTED table. (Note Inserted table only accessable from the Trigger Scope & it will have the same table structure as the main table).

Inside Your Trigger:

Code Snippet

Declare @.SomeValue as Varchar(100);

Select @.SomeValue = SomeColumn From Inserted;

It is not good idea to use Gloabal Variable / Temp Table on triggers. You can't say the values always inserted from your SP.

|||

No. the passed parameter I do not want to stored in the table due to some reason. How can I pass the value from stored proc to triggers without storing into table?

|||

You cannot

pass the value from stored proc to triggers

without storing the data in the table.

All input data for the TRIGGER MUST exist within the TABLE that the TRIGGER fires on.

Of course, if you do not wish that the data be kept in the database for concern about security/visibility, you could have the TRIGGER set the field to NULL -thereby obliterating the data that was initially input.

|||Can we do something on tempdb? I mean can I stored the value into tempdb in stored proc then retrieve it from tempdb in triggers?|||

According to you,

I have done by using declaring #TempTable on StoredProc and use it in Triggers but it doesn't work.

Did it work?

|||

The following example may help you...

Code Snippet

Create Table ThisIsIt (
[Id] int,
[Value] varchar(100))

go

Create Table ThisIsLog (
[Users] varchar(100),
[When] datetime,
[Id] int,
[Operation] int,
[ValuesAffected] Varchar(8000))

go

Create Trigger trg_ThisIsIt_logger on ThisIsIt For Insert
as
Begin
Declare @.ValuesAffected as Varchar(8000);
Declare @.User as varchar(100);
Declare @.Id as Int;

Select @.ValuesAffected = '"?1";"?2"'
Select @.Id = Id, @.ValuesAffected = Replace(Replace(@.ValuesAffected,'?1', Id),'?2',Value)
From Inserted
If Exists(Select ID From tempdb..Sysobjects Where id = Object_id('tempdb..#Info'))
Select @.User = [User] from #Info;

Insert Into ThisIsLog Values
(@.User, getdate(), @.Id, 1, @.ValuesAffected)
End

go

Create proc InsertThisIsIt
(
@.Id int,
@.Value varchar(100),
@.User varchar(100)
)
as
Begin
Create table #Info
(
[User] varchar(100)
)

Insert Into #Info Values(@.User);

Insert Into ThisIsIt Values(@.Id, @.Value);
End

Global variable for hyperlinks in reports

We are using Reporting Services 2000. We have several reports that all
contain an "Edit" hyperlink that goes to a URL not related to the report
server url. We cannot directly enter this URL into the "Jump To" section,
because the url is different depending on the environment (dev, test,
production). So, because the reports uploaded in dev, test, and production
need to be identical, we need to get to this url some other way.
Ideally, we could upload a text file or enter some type of global variable
in the Report Manager that all the reports could access. The contents of
that variable/file would then be used to get the value for the "Jump To"
field of the hyperlink.
Does anyone have any ideas on the best way to accomplish this? Thanks!First off, you would want to use Jump to URL, not Jump to Report. The URL
can be an expression.
You could have a Report Parameter but that gets a little cumbersome. What I
suggest is having a dataset that has a single value that determines whether
it is test, dev, production. It could query a table that returns this value.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"David" <dilworth@.newsgroups.nospam> wrote in message
news:F8AD4F01-FB63-44EE-9900-E72F8A2845BD@.microsoft.com...
> We are using Reporting Services 2000. We have several reports that all
> contain an "Edit" hyperlink that goes to a URL not related to the report
> server url. We cannot directly enter this URL into the "Jump To" section,
> because the url is different depending on the environment (dev, test,
> production). So, because the reports uploaded in dev, test, and
> production
> need to be identical, we need to get to this url some other way.
> Ideally, we could upload a text file or enter some type of global variable
> in the Report Manager that all the reports could access. The contents of
> that variable/file would then be used to get the value for the "Jump To"
> field of the hyperlink.
> Does anyone have any ideas on the best way to accomplish this? Thanks!|||That worked great - thanks!
"Bruce L-C [MVP]" wrote:
> First off, you would want to use Jump to URL, not Jump to Report. The URL
> can be an expression.
> You could have a Report Parameter but that gets a little cumbersome. What I
> suggest is having a dataset that has a single value that determines whether
> it is test, dev, production. It could query a table that returns this value.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "David" <dilworth@.newsgroups.nospam> wrote in message
> news:F8AD4F01-FB63-44EE-9900-E72F8A2845BD@.microsoft.com...
> > We are using Reporting Services 2000. We have several reports that all
> > contain an "Edit" hyperlink that goes to a URL not related to the report
> > server url. We cannot directly enter this URL into the "Jump To" section,
> > because the url is different depending on the environment (dev, test,
> > production). So, because the reports uploaded in dev, test, and
> > production
> > need to be identical, we need to get to this url some other way.
> >
> > Ideally, we could upload a text file or enter some type of global variable
> > in the Report Manager that all the reports could access. The contents of
> > that variable/file would then be used to get the value for the "Jump To"
> > field of the hyperlink.
> >
> > Does anyone have any ideas on the best way to accomplish this? Thanks!
>
>

Global Variable

Hi All,

I tried to get a global variable in my task scritp by using "Dts.Variables("myVar").Value", every time I've got an error

The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.

I've seen some examples online to get global varaibles in task script and all of them display the same code

Any idea

Franck

To access variable this way it has to be included in the list of ReadOnlyVariables or ReadWriteVariables on your script task properties SCRIPT properties section. Remember also that variables names are case sensitive.

Global Variable

Dear All!
I want to know that how can i declare a global variable in database, assign some value to it, then using it in multiple triggers and procedure then deallocating that.
Please provide a smal example.
Regards,
Shabber.Create a User-Defined Function that returns the value?|||And deallocate it?

What the heck are you doing?

UDF is the only way to simulate a global variable, but if you then wipe out your UDF it will break your sprocs.|||I'd assume Shabber has no big need for deallocating. It's probably just that if there would have been such a thing as a global variable, then it would have been a good habit to deallocate once it wasn't needed anymore.|||Thanks for replying.

But how function will provide the functionality of Global Variables. A bit confusing.

Regards,
Shabber.|||You call the function, which will return the value you're after.|||Thanks for replying.

But how function will provide the functionality of Global Variables. A bit confusing.

Regards,
Shabber.

CREATE TABLE MyParameter (
SiteID int IDENTITY (1,1) NOT NULL,
SiteName varchar(255) NOT NULL
)

CREATE FUNCTION udfMyFunction
(@.p1 int)
RETURNS varchar(255)
AS
BEGIN
DECLARE @.sTemp varchar(255)

SELECT @.sTemp = SiteName FROM MyParameter WHERE SiteID = @.p1
RETURN @.sTemp
END

Example Data and Usage:

insert into MyParameter(SiteName) Values ('Foo')
insert into MyParameter(SiteName) Values ('Bar')

SELECT dbo.udfMyFunction (2)

I don't know that this example is all that useful, but maybe it will give you some ideas.

Regards,

hmscott

Global update of multiple tables

Hi
I need to globally update certain tables over multiple databases. I have
about 300 tables in each database and only a handfull will be the same.
Can I do this with replication? Can you setup replication for only certain
tables?
Jaco,
replication is seldom all the tables in a database - mostly a publication
contains a small subset of the tables. I'd recommend having a good look a
BOL (books on line) and the book in my signature below.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks Paul
"Paul Ibison" wrote:

> Jaco,
> replication is seldom all the tables in a database - mostly a publication
> contains a small subset of the tables. I'd recommend having a good look a
> BOL (books on line) and the book in my signature below.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>

global update

Hi all, I need to update some data in a table, based on some criteria.
In this case we are talking about the stamping of a price against a job.
The update table holds the jobs, and the update_details table holds the
activities performed on each job and the cost for each activity. If i
pull back this information using the following code

select t1.reference,t1.update_id, t2.*
from update t1, update_details t2
where left(t1.reference,2) in ('EA','ND','SD','ST')
and t1.update_id = t2.update_id

I get something like

EA 1883 Act1 4.20
EA 1883 Act2 3.00
EA 1883 Act3 7.50
EA 2444 Act1 4.20
SD 5433 Act1 5.60

I need to update the cost for everything pulled back using the above
sql, to a price determined in another table (activities)

the activities table would look something like

Activity_Code Cost_London Cost_Roc
Act1 5.60 4.20
Act2 4.00 3.00
Act3 6.20 5.60

in a nutshell i need to update the cost in the update details from
Cost_roc to Cost_london for all activities for all jobs in the update
table that have a referance starting with specific letters. The new
prices need to be obtained from the activities table.

Would be very gratefull for any help on this matter

Regards,

Ian Selby

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Ian Selby (ian.selby@.lojics.co.uk) writes:
> Hi all, I need to update some data in a table, based on some criteria.
> In this case we are talking about the stamping of a price against a job.
> The update table holds the jobs, and the update_details table holds the
> activities performed on each job and the cost for each activity. If i
> pull back this information using the following code
>...

Your question seems to have been left unanswered, and unfortunately I
cannot provide any answer to you. The reason for this, is that I cannot
understand how the values in the Cost_London and Cost_Roc column
maps to the rows in the first result set.

The standard recommendation for getting help with a query is to post:

o CREATE TABLE scripts of the tables involved. (It helps to include
PRIMARY KEY and FOREIGN KEY references.
o INSERT statements with sample data.
o The result you want given the sample data.

This makes it easier to understand what you after, and also it makes it
possible to post a tested solution.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp