I have a report that we give to our Doctors to fill out that has patient
information on it. One of the things we are supposed to have on the report
is a checkbox for symptoms found. I would like it if I could get a check box
to align with its associated text. If you have over 3 rows of this on your
report they all start to get out of alignment. The more rows you have the
more out of whack it becomes, until it looks like the texbox has no
association with its associated check box.
This is very frustrating. I just spent about four hours tweaking a report
to get them to look okay but yet they still are not aligned exactly as they
should be.
To create the check box I am using the wingdings font lower case 'o'
character. Of course I cannot put the wingdings font in the same text box as
the symptoms textbox as then the symptoms would be gibberish.
Any thoughts on how to make this process easier, more accurate? I am using
a listbox as my container.
--
Thank you,
Johnone thing I liked about crystal reports is you can change formatting of
individual characters in a text box... cant do this in reporting server
unfortinuatly...
"John A" <i-code4food@.newsgroups.nospam> wrote in message
news:D8DC91FB-52E7-41B4-8C83-6A8F03EF98E5@.microsoft.com...
>I have a report that we give to our Doctors to fill out that has patient
> information on it. One of the things we are supposed to have on the
> report
> is a checkbox for symptoms found. I would like it if I could get a check
> box
> to align with its associated text. If you have over 3 rows of this on
> your
> report they all start to get out of alignment. The more rows you have the
> more out of whack it becomes, until it looks like the texbox has no
> association with its associated check box.
> This is very frustrating. I just spent about four hours tweaking a report
> to get them to look okay but yet they still are not aligned exactly as
> they
> should be.
> To create the check box I am using the wingdings font lower case 'o'
> character. Of course I cannot put the wingdings font in the same text box
> as
> the symptoms textbox as then the symptoms would be gibberish.
> Any thoughts on how to make this process easier, more accurate? I am
> using
> a listbox as my container.
> --
> Thank you,
> John|||John,
Do you have to use a listbox as your container?
If you use a two column table with your check boxes in the first
column and your symptoms in the second, they should stay lined up.
Otherwise maybe use open and closed brackets or parens (e.g. [ ] or
{ } or ( ) ) as your "checkbox" inside the symptom's text boxes.
Good luck and happy reporting!!
Thursday, March 29, 2012
Aligning 2 charts
I have a report with 2 charts as subreports. The X axis is time and
the charts show different data over the same time period.
I would like to position the charts so that corresponding X axis
values are aligned vertically. This seems to work if the Y axis scales
are the same ie. the labels are the same length, but if the scales are
different the charts don't quite align. The chart types might also be
causing problems i.e. Area vs. Line - it looks like they may be drawn
with different margins as well as different values on the Y scale.
Essentially, I need to get the left hand edges of the charts to line
up.
Is there a way to align these charts the way I want?
--
Andrew RowleyOn Aug 21, 8:50 pm, Andrew Rowley <aj...@.newsgroup.nospam> wrote:
> I have a report with 2 charts as subreports. The X axis is time and
> the charts show different data over the same time period.
> I would like to position the charts so that corresponding X axis
> values are aligned vertically. This seems to work if the Y axis scales
> are the same ie. the labels are the same length, but if the scales are
> different the charts don't quite align. The chart types might also be
> causing problems i.e. Area vs. Line - it looks like they may be drawn
> with different margins as well as different values on the Y scale.
> Essentially, I need to get the left hand edges of the charts to line
> up.
> Is there a way to align these charts the way I want?
> --
> Andrew Rowley
I'm not sure if this will help, but you might try putting the 2 charts
in the same subreport or in the main report and/or inside a rectangle
control. Normally, subreports are a little difficult to align. Hope
this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||You could also try setting the scale (interval) of the y axis yourself.
"EMartinez" wrote:
> On Aug 21, 8:50 pm, Andrew Rowley <aj...@.newsgroup.nospam> wrote:
> > I have a report with 2 charts as subreports. The X axis is time and
> > the charts show different data over the same time period.
> >
> > I would like to position the charts so that corresponding X axis
> > values are aligned vertically. This seems to work if the Y axis scales
> > are the same ie. the labels are the same length, but if the scales are
> > different the charts don't quite align. The chart types might also be
> > causing problems i.e. Area vs. Line - it looks like they may be drawn
> > with different margins as well as different values on the Y scale.
> >
> > Essentially, I need to get the left hand edges of the charts to line
> > up.
> >
> > Is there a way to align these charts the way I want?
> > --
> > Andrew Rowley
>
> I'm not sure if this will help, but you might try putting the 2 charts
> in the same subreport or in the main report and/or inside a rectangle
> control. Normally, subreports are a little difficult to align. Hope
> this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||David <David@.discussions.microsoft.com> wrote:
>You could also try setting the scale (interval) of the y axis yourself.
The problem is the scales need to be different, e.g. one is 0-100 and
the other is 0-5.
--
Andrew Rowley|||Hello Andrew,
You need to get the same font size for your X Axis label of both Charts.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.
the charts show different data over the same time period.
I would like to position the charts so that corresponding X axis
values are aligned vertically. This seems to work if the Y axis scales
are the same ie. the labels are the same length, but if the scales are
different the charts don't quite align. The chart types might also be
causing problems i.e. Area vs. Line - it looks like they may be drawn
with different margins as well as different values on the Y scale.
Essentially, I need to get the left hand edges of the charts to line
up.
Is there a way to align these charts the way I want?
--
Andrew RowleyOn Aug 21, 8:50 pm, Andrew Rowley <aj...@.newsgroup.nospam> wrote:
> I have a report with 2 charts as subreports. The X axis is time and
> the charts show different data over the same time period.
> I would like to position the charts so that corresponding X axis
> values are aligned vertically. This seems to work if the Y axis scales
> are the same ie. the labels are the same length, but if the scales are
> different the charts don't quite align. The chart types might also be
> causing problems i.e. Area vs. Line - it looks like they may be drawn
> with different margins as well as different values on the Y scale.
> Essentially, I need to get the left hand edges of the charts to line
> up.
> Is there a way to align these charts the way I want?
> --
> Andrew Rowley
I'm not sure if this will help, but you might try putting the 2 charts
in the same subreport or in the main report and/or inside a rectangle
control. Normally, subreports are a little difficult to align. Hope
this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||You could also try setting the scale (interval) of the y axis yourself.
"EMartinez" wrote:
> On Aug 21, 8:50 pm, Andrew Rowley <aj...@.newsgroup.nospam> wrote:
> > I have a report with 2 charts as subreports. The X axis is time and
> > the charts show different data over the same time period.
> >
> > I would like to position the charts so that corresponding X axis
> > values are aligned vertically. This seems to work if the Y axis scales
> > are the same ie. the labels are the same length, but if the scales are
> > different the charts don't quite align. The chart types might also be
> > causing problems i.e. Area vs. Line - it looks like they may be drawn
> > with different margins as well as different values on the Y scale.
> >
> > Essentially, I need to get the left hand edges of the charts to line
> > up.
> >
> > Is there a way to align these charts the way I want?
> > --
> > Andrew Rowley
>
> I'm not sure if this will help, but you might try putting the 2 charts
> in the same subreport or in the main report and/or inside a rectangle
> control. Normally, subreports are a little difficult to align. Hope
> this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||David <David@.discussions.microsoft.com> wrote:
>You could also try setting the scale (interval) of the y axis yourself.
The problem is the scales need to be different, e.g. one is 0-100 and
the other is 0-5.
--
Andrew Rowley|||Hello Andrew,
You need to get the same font size for your X Axis label of both Charts.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.
alignement data
My application has production server and backup sever for data reporting .
There is some tools in SQL 2K (or third part tool) for alignement data at
run time between two servers?
Thanks in advance
StperAre you talking about transforming data? Have you explored DTS? See SQL
Server Books Online for more information.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"stefano perab" <stefano.perabo@.speedautomazione.it> wrote in message
news:%23wzR3DxbEHA.556@.tk2msftngp13.phx.gbl...
My application has production server and backup sever for data reporting .
There is some tools in SQL 2K (or third part tool) for alignement data at
run time between two servers?
Thanks in advance
Stper|||Hi Vyas,
It seems Stper need to syncronize the report server with production server
online.
Stper,
If above is the case you can go for either of the below approach
1. Transactional Replication
2. Log Shipping
This method will sync. the Report server database with report server.
Thanks
Hari
MCDBA
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:uDRYaMxbEHA.3988@.tk2msftngp13.phx.gbl...
> Are you talking about transforming data? Have you explored DTS? See SQL
> Server Books Online for more information.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "stefano perab" <stefano.perabo@.speedautomazione.it> wrote in message
> news:%23wzR3DxbEHA.556@.tk2msftngp13.phx.gbl...
> My application has production server and backup sever for data reporting
.
> There is some tools in SQL 2K (or third part tool) for alignement data at
> run time between two servers?
> Thanks in advance
> Stper
>
>|||In that case, I'd strongly recommend transactional replication. It is meant
to satisfy reporting requirements.
Log shipping is ideal for maintaining standby servers, but not really for
reporting purposes.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23AoYEqxbEHA.3480@.TK2MSFTNGP11.phx.gbl...
Hi Vyas,
It seems Stper need to syncronize the report server with production server
online.
Stper,
If above is the case you can go for either of the below approach
1. Transactional Replication
2. Log Shipping
This method will sync. the Report server database with report server.
Thanks
Hari
MCDBA
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:uDRYaMxbEHA.3988@.tk2msftngp13.phx.gbl...
> Are you talking about transforming data? Have you explored DTS? See SQL
> Server Books Online for more information.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "stefano perab" <stefano.perabo@.speedautomazione.it> wrote in message
> news:%23wzR3DxbEHA.556@.tk2msftngp13.phx.gbl...
> My application has production server and backup sever for data reporting
.
> There is some tools in SQL 2K (or third part tool) for alignement data at
> run time between two servers?
> Thanks in advance
> Stper
>
>|||Thanks Hari, i think the Transactional Replication is the solution for my
problem. Now i must study the configuration
I found this an article in
www.awprofessional.com/articles/article.asp?p=170234
Do you have some suggestion for examples and/or configuration
Thanks
Stper
"Hari Prasad" <hari_prasad_k@.hotmail.com> ha scritto nel messaggio
news:#AoYEqxbEHA.3480@.TK2MSFTNGP11.phx.gbl...
> Hi Vyas,
> It seems Stper need to syncronize the report server with production server
> online.
> Stper,
> If above is the case you can go for either of the below approach
> 1. Transactional Replication
> 2. Log Shipping
> This method will sync. the Report server database with report server.
> Thanks
> Hari
> MCDBA
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:uDRYaMxbEHA.3988@.tk2msftngp13.phx.gbl...
reporting[vbcol=seagreen]
> .
at[vbcol=seagreen]
>|||stper,
Books Online is a very valuable reference.
http://www.mssqlserver.com/replication/primer_p1.asp has a nice primer
to replication. There is a book coming soon on Snapshot and
Transactional replication from Hilary Cotter which is excellent, see the
link in my sig.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
stper wrote:
> Thanks Hari, i think the Transactional Replication is the solution for my
> problem. Now i must study the configuration
> I found this an article in
> www.awprofessional.com/articles/article.asp?p=170234
> Do you have some suggestion for examples and/or configuration
> Thanks
> Stper
> "Hari Prasad" <hari_prasad_k@.hotmail.com> ha scritto nel messaggio
> news:#AoYEqxbEHA.3480@.TK2MSFTNGP11.phx.gbl...
>
> reporting
>
> at
>
>|||i made the configuration and now i testing my first "PULL SUbscription".
Unfortunately i have my first error message : "Unexpected EOF encountered in
BCP data-file" when i run with alignement.
Do yuo have some suggestion ?
stper
"Mark Allison" <marka@.no.tinned.meat.mvps.org> ha scritto nel messaggio
news:#7aQdhybEHA.4092@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> stper,
> Books Online is a very valuable reference.
> http://www.mssqlserver.com/replication/primer_p1.asp has a nice primer
> to replication. There is a book coming soon on Snapshot and
> Transactional replication from Hilary Cotter which is excellent, see the
> link in my sig.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> stper wrote:
my[vbcol=seagreen]
server[vbcol=seagreen]|||After testing and testying i can say (like Pitagora) : EUREKA !!!
now i must tuning the task but the functionality work very well.
Thanks
Stper
"stper" <stefano.perabo@.speedautomazione.it> ha scritto nel messaggio
news:eZICqZzbEHA.596@.TK2MSFTNGP11.phx.gbl...
> i made the configuration and now i testing my first "PULL SUbscription".
> Unfortunately i have my first error message : "Unexpected EOF encountered
in
> BCP data-file" when i run with alignement.
> Do yuo have some suggestion ?
> stper
> "Mark Allison" <marka@.no.tinned.meat.mvps.org> ha scritto nel messaggio
> news:#7aQdhybEHA.4092@.TK2MSFTNGP11.phx.gbl...
> my
> server
SQL[vbcol=seagreen]
message[vbcol=seagreen]
data[vbcol=seagreen]
>
There is some tools in SQL 2K (or third part tool) for alignement data at
run time between two servers?
Thanks in advance
StperAre you talking about transforming data? Have you explored DTS? See SQL
Server Books Online for more information.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"stefano perab" <stefano.perabo@.speedautomazione.it> wrote in message
news:%23wzR3DxbEHA.556@.tk2msftngp13.phx.gbl...
My application has production server and backup sever for data reporting .
There is some tools in SQL 2K (or third part tool) for alignement data at
run time between two servers?
Thanks in advance
Stper|||Hi Vyas,
It seems Stper need to syncronize the report server with production server
online.
Stper,
If above is the case you can go for either of the below approach
1. Transactional Replication
2. Log Shipping
This method will sync. the Report server database with report server.
Thanks
Hari
MCDBA
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:uDRYaMxbEHA.3988@.tk2msftngp13.phx.gbl...
> Are you talking about transforming data? Have you explored DTS? See SQL
> Server Books Online for more information.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "stefano perab" <stefano.perabo@.speedautomazione.it> wrote in message
> news:%23wzR3DxbEHA.556@.tk2msftngp13.phx.gbl...
> My application has production server and backup sever for data reporting
.
> There is some tools in SQL 2K (or third part tool) for alignement data at
> run time between two servers?
> Thanks in advance
> Stper
>
>|||In that case, I'd strongly recommend transactional replication. It is meant
to satisfy reporting requirements.
Log shipping is ideal for maintaining standby servers, but not really for
reporting purposes.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23AoYEqxbEHA.3480@.TK2MSFTNGP11.phx.gbl...
Hi Vyas,
It seems Stper need to syncronize the report server with production server
online.
Stper,
If above is the case you can go for either of the below approach
1. Transactional Replication
2. Log Shipping
This method will sync. the Report server database with report server.
Thanks
Hari
MCDBA
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:uDRYaMxbEHA.3988@.tk2msftngp13.phx.gbl...
> Are you talking about transforming data? Have you explored DTS? See SQL
> Server Books Online for more information.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "stefano perab" <stefano.perabo@.speedautomazione.it> wrote in message
> news:%23wzR3DxbEHA.556@.tk2msftngp13.phx.gbl...
> My application has production server and backup sever for data reporting
.
> There is some tools in SQL 2K (or third part tool) for alignement data at
> run time between two servers?
> Thanks in advance
> Stper
>
>|||Thanks Hari, i think the Transactional Replication is the solution for my
problem. Now i must study the configuration
I found this an article in
www.awprofessional.com/articles/article.asp?p=170234
Do you have some suggestion for examples and/or configuration
Thanks
Stper
"Hari Prasad" <hari_prasad_k@.hotmail.com> ha scritto nel messaggio
news:#AoYEqxbEHA.3480@.TK2MSFTNGP11.phx.gbl...
> Hi Vyas,
> It seems Stper need to syncronize the report server with production server
> online.
> Stper,
> If above is the case you can go for either of the below approach
> 1. Transactional Replication
> 2. Log Shipping
> This method will sync. the Report server database with report server.
> Thanks
> Hari
> MCDBA
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:uDRYaMxbEHA.3988@.tk2msftngp13.phx.gbl...
reporting[vbcol=seagreen]
> .
at[vbcol=seagreen]
>|||stper,
Books Online is a very valuable reference.
http://www.mssqlserver.com/replication/primer_p1.asp has a nice primer
to replication. There is a book coming soon on Snapshot and
Transactional replication from Hilary Cotter which is excellent, see the
link in my sig.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
stper wrote:
> Thanks Hari, i think the Transactional Replication is the solution for my
> problem. Now i must study the configuration
> I found this an article in
> www.awprofessional.com/articles/article.asp?p=170234
> Do you have some suggestion for examples and/or configuration
> Thanks
> Stper
> "Hari Prasad" <hari_prasad_k@.hotmail.com> ha scritto nel messaggio
> news:#AoYEqxbEHA.3480@.TK2MSFTNGP11.phx.gbl...
>
> reporting
>
> at
>
>|||i made the configuration and now i testing my first "PULL SUbscription".
Unfortunately i have my first error message : "Unexpected EOF encountered in
BCP data-file" when i run with alignement.
Do yuo have some suggestion ?
stper
"Mark Allison" <marka@.no.tinned.meat.mvps.org> ha scritto nel messaggio
news:#7aQdhybEHA.4092@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> stper,
> Books Online is a very valuable reference.
> http://www.mssqlserver.com/replication/primer_p1.asp has a nice primer
> to replication. There is a book coming soon on Snapshot and
> Transactional replication from Hilary Cotter which is excellent, see the
> link in my sig.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> stper wrote:
my[vbcol=seagreen]
server[vbcol=seagreen]|||After testing and testying i can say (like Pitagora) : EUREKA !!!
now i must tuning the task but the functionality work very well.
Thanks
Stper
"stper" <stefano.perabo@.speedautomazione.it> ha scritto nel messaggio
news:eZICqZzbEHA.596@.TK2MSFTNGP11.phx.gbl...
> i made the configuration and now i testing my first "PULL SUbscription".
> Unfortunately i have my first error message : "Unexpected EOF encountered
in
> BCP data-file" when i run with alignement.
> Do yuo have some suggestion ?
> stper
> "Mark Allison" <marka@.no.tinned.meat.mvps.org> ha scritto nel messaggio
> news:#7aQdhybEHA.4092@.TK2MSFTNGP11.phx.gbl...
> my
> server
SQL[vbcol=seagreen]
message[vbcol=seagreen]
data[vbcol=seagreen]
>
alignement data
My application has production server and backup sever for data reporting .
There is some tools in SQL 2K (or third part tool) for alignement data at
run time between two servers?
Thanks in advance
Stper
Are you talking about transforming data? Have you explored DTS? See SQL
Server Books Online for more information.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"stefano perab" <stefano.perabo@.speedautomazione.it> wrote in message
news:%23wzR3DxbEHA.556@.tk2msftngp13.phx.gbl...
My application has production server and backup sever for data reporting .
There is some tools in SQL 2K (or third part tool) for alignement data at
run time between two servers?
Thanks in advance
Stper
|||Hi Vyas,
It seems Stper need to syncronize the report server with production server
online.
Stper,
If above is the case you can go for either of the below approach
1. Transactional Replication
2. Log Shipping
This method will sync. the Report server database with report server.
Thanks
Hari
MCDBA
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:uDRYaMxbEHA.3988@.tk2msftngp13.phx.gbl...
> Are you talking about transforming data? Have you explored DTS? See SQL
> Server Books Online for more information.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "stefano perab" <stefano.perabo@.speedautomazione.it> wrote in message
> news:%23wzR3DxbEHA.556@.tk2msftngp13.phx.gbl...
> My application has production server and backup sever for data reporting
..
> There is some tools in SQL 2K (or third part tool) for alignement data at
> run time between two servers?
> Thanks in advance
> Stper
>
>
|||In that case, I'd strongly recommend transactional replication. It is meant
to satisfy reporting requirements.
Log shipping is ideal for maintaining standby servers, but not really for
reporting purposes.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23AoYEqxbEHA.3480@.TK2MSFTNGP11.phx.gbl...
Hi Vyas,
It seems Stper need to syncronize the report server with production server
online.
Stper,
If above is the case you can go for either of the below approach
1. Transactional Replication
2. Log Shipping
This method will sync. the Report server database with report server.
Thanks
Hari
MCDBA
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:uDRYaMxbEHA.3988@.tk2msftngp13.phx.gbl...
> Are you talking about transforming data? Have you explored DTS? See SQL
> Server Books Online for more information.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "stefano perab" <stefano.perabo@.speedautomazione.it> wrote in message
> news:%23wzR3DxbEHA.556@.tk2msftngp13.phx.gbl...
> My application has production server and backup sever for data reporting
..
> There is some tools in SQL 2K (or third part tool) for alignement data at
> run time between two servers?
> Thanks in advance
> Stper
>
>
|||Thanks Hari, i think the Transactional Replication is the solution for my
problem. Now i must study the configuration
I found this an article in
http://www.awprofessional.com/articl...e.asp?p=170234
Do you have some suggestion for examples and/or configuration
Thanks
Stper
"Hari Prasad" <hari_prasad_k@.hotmail.com> ha scritto nel messaggio
news:#AoYEqxbEHA.3480@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Hi Vyas,
> It seems Stper need to syncronize the report server with production server
> online.
> Stper,
> If above is the case you can go for either of the below approach
> 1. Transactional Replication
> 2. Log Shipping
> This method will sync. the Report server database with report server.
> Thanks
> Hari
> MCDBA
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:uDRYaMxbEHA.3988@.tk2msftngp13.phx.gbl...
reporting[vbcol=seagreen]
> .
at
>
|||stper,
Books Online is a very valuable reference.
http://www.mssqlserver.com/replication/primer_p1.asp has a nice primer
to replication. There is a book coming soon on Snapshot and
Transactional replication from Hilary Cotter which is excellent, see the
link in my sig.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
stper wrote:
> Thanks Hari, i think the Transactional Replication is the solution for my
> problem. Now i must study the configuration
> I found this an article in
> http://www.awprofessional.com/articl...e.asp?p=170234
> Do you have some suggestion for examples and/or configuration
> Thanks
> Stper
> "Hari Prasad" <hari_prasad_k@.hotmail.com> ha scritto nel messaggio
> news:#AoYEqxbEHA.3480@.TK2MSFTNGP11.phx.gbl...
>
> reporting
>
> at
>
>
|||i made the configuration and now i testing my first "PULL SUbscription".
Unfortunately i have my first error message : "Unexpected EOF encountered in
BCP data-file" when i run with alignement.
Do yuo have some suggestion ?
stper
"Mark Allison" <marka@.no.tinned.meat.mvps.org> ha scritto nel messaggio
news:#7aQdhybEHA.4092@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> stper,
> Books Online is a very valuable reference.
> http://www.mssqlserver.com/replication/primer_p1.asp has a nice primer
> to replication. There is a book coming soon on Snapshot and
> Transactional replication from Hilary Cotter which is excellent, see the
> link in my sig.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> stper wrote:
my[vbcol=seagreen]
server[vbcol=seagreen]
|||After testing and testying i can say (like Pitagora) : EUREKA !!!
now i must tuning the task but the functionality work very well.
Thanks
Stper
"stper" <stefano.perabo@.speedautomazione.it> ha scritto nel messaggio
news:eZICqZzbEHA.596@.TK2MSFTNGP11.phx.gbl...
> i made the configuration and now i testing my first "PULL SUbscription".
> Unfortunately i have my first error message : "Unexpected EOF encountered
in[vbcol=seagreen]
> BCP data-file" when i run with alignement.
> Do yuo have some suggestion ?
> stper
> "Mark Allison" <marka@.no.tinned.meat.mvps.org> ha scritto nel messaggio
> news:#7aQdhybEHA.4092@.TK2MSFTNGP11.phx.gbl...
> my
> server
SQL[vbcol=seagreen]
message[vbcol=seagreen]
data
>
There is some tools in SQL 2K (or third part tool) for alignement data at
run time between two servers?
Thanks in advance
Stper
Are you talking about transforming data? Have you explored DTS? See SQL
Server Books Online for more information.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"stefano perab" <stefano.perabo@.speedautomazione.it> wrote in message
news:%23wzR3DxbEHA.556@.tk2msftngp13.phx.gbl...
My application has production server and backup sever for data reporting .
There is some tools in SQL 2K (or third part tool) for alignement data at
run time between two servers?
Thanks in advance
Stper
|||Hi Vyas,
It seems Stper need to syncronize the report server with production server
online.
Stper,
If above is the case you can go for either of the below approach
1. Transactional Replication
2. Log Shipping
This method will sync. the Report server database with report server.
Thanks
Hari
MCDBA
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:uDRYaMxbEHA.3988@.tk2msftngp13.phx.gbl...
> Are you talking about transforming data? Have you explored DTS? See SQL
> Server Books Online for more information.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "stefano perab" <stefano.perabo@.speedautomazione.it> wrote in message
> news:%23wzR3DxbEHA.556@.tk2msftngp13.phx.gbl...
> My application has production server and backup sever for data reporting
..
> There is some tools in SQL 2K (or third part tool) for alignement data at
> run time between two servers?
> Thanks in advance
> Stper
>
>
|||In that case, I'd strongly recommend transactional replication. It is meant
to satisfy reporting requirements.
Log shipping is ideal for maintaining standby servers, but not really for
reporting purposes.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23AoYEqxbEHA.3480@.TK2MSFTNGP11.phx.gbl...
Hi Vyas,
It seems Stper need to syncronize the report server with production server
online.
Stper,
If above is the case you can go for either of the below approach
1. Transactional Replication
2. Log Shipping
This method will sync. the Report server database with report server.
Thanks
Hari
MCDBA
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:uDRYaMxbEHA.3988@.tk2msftngp13.phx.gbl...
> Are you talking about transforming data? Have you explored DTS? See SQL
> Server Books Online for more information.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "stefano perab" <stefano.perabo@.speedautomazione.it> wrote in message
> news:%23wzR3DxbEHA.556@.tk2msftngp13.phx.gbl...
> My application has production server and backup sever for data reporting
..
> There is some tools in SQL 2K (or third part tool) for alignement data at
> run time between two servers?
> Thanks in advance
> Stper
>
>
|||Thanks Hari, i think the Transactional Replication is the solution for my
problem. Now i must study the configuration
I found this an article in
http://www.awprofessional.com/articl...e.asp?p=170234
Do you have some suggestion for examples and/or configuration
Thanks
Stper
"Hari Prasad" <hari_prasad_k@.hotmail.com> ha scritto nel messaggio
news:#AoYEqxbEHA.3480@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Hi Vyas,
> It seems Stper need to syncronize the report server with production server
> online.
> Stper,
> If above is the case you can go for either of the below approach
> 1. Transactional Replication
> 2. Log Shipping
> This method will sync. the Report server database with report server.
> Thanks
> Hari
> MCDBA
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:uDRYaMxbEHA.3988@.tk2msftngp13.phx.gbl...
reporting[vbcol=seagreen]
> .
at
>
|||stper,
Books Online is a very valuable reference.
http://www.mssqlserver.com/replication/primer_p1.asp has a nice primer
to replication. There is a book coming soon on Snapshot and
Transactional replication from Hilary Cotter which is excellent, see the
link in my sig.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
stper wrote:
> Thanks Hari, i think the Transactional Replication is the solution for my
> problem. Now i must study the configuration
> I found this an article in
> http://www.awprofessional.com/articl...e.asp?p=170234
> Do you have some suggestion for examples and/or configuration
> Thanks
> Stper
> "Hari Prasad" <hari_prasad_k@.hotmail.com> ha scritto nel messaggio
> news:#AoYEqxbEHA.3480@.TK2MSFTNGP11.phx.gbl...
>
> reporting
>
> at
>
>
|||i made the configuration and now i testing my first "PULL SUbscription".
Unfortunately i have my first error message : "Unexpected EOF encountered in
BCP data-file" when i run with alignement.
Do yuo have some suggestion ?
stper
"Mark Allison" <marka@.no.tinned.meat.mvps.org> ha scritto nel messaggio
news:#7aQdhybEHA.4092@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> stper,
> Books Online is a very valuable reference.
> http://www.mssqlserver.com/replication/primer_p1.asp has a nice primer
> to replication. There is a book coming soon on Snapshot and
> Transactional replication from Hilary Cotter which is excellent, see the
> link in my sig.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> stper wrote:
my[vbcol=seagreen]
server[vbcol=seagreen]
|||After testing and testying i can say (like Pitagora) : EUREKA !!!
now i must tuning the task but the functionality work very well.
Thanks
Stper
"stper" <stefano.perabo@.speedautomazione.it> ha scritto nel messaggio
news:eZICqZzbEHA.596@.TK2MSFTNGP11.phx.gbl...
> i made the configuration and now i testing my first "PULL SUbscription".
> Unfortunately i have my first error message : "Unexpected EOF encountered
in[vbcol=seagreen]
> BCP data-file" when i run with alignement.
> Do yuo have some suggestion ?
> stper
> "Mark Allison" <marka@.no.tinned.meat.mvps.org> ha scritto nel messaggio
> news:#7aQdhybEHA.4092@.TK2MSFTNGP11.phx.gbl...
> my
> server
SQL[vbcol=seagreen]
message[vbcol=seagreen]
data
>
alignement data
My application has production server and backup sever for data reporting .
There is some tools in SQL 2K (or third part tool) for alignement data at
run time between two servers?
Thanks in advance
StperAre you talking about transforming data? Have you explored DTS? See SQL
Server Books Online for more information.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"stefano perabò" <stefano.perabo@.speedautomazione.it> wrote in message
news:%23wzR3DxbEHA.556@.tk2msftngp13.phx.gbl...
My application has production server and backup sever for data reporting .
There is some tools in SQL 2K (or third part tool) for alignement data at
run time between two servers?
Thanks in advance
Stper|||Hi Vyas,
It seems Stper need to syncronize the report server with production server
online.
Stper,
If above is the case you can go for either of the below approach
1. Transactional Replication
2. Log Shipping
This method will sync. the Report server database with report server.
Thanks
Hari
MCDBA
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:uDRYaMxbEHA.3988@.tk2msftngp13.phx.gbl...
> Are you talking about transforming data? Have you explored DTS? See SQL
> Server Books Online for more information.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "stefano perabò" <stefano.perabo@.speedautomazione.it> wrote in message
> news:%23wzR3DxbEHA.556@.tk2msftngp13.phx.gbl...
> My application has production server and backup sever for data reporting
.
> There is some tools in SQL 2K (or third part tool) for alignement data at
> run time between two servers?
> Thanks in advance
> Stper
>
>|||In that case, I'd strongly recommend transactional replication. It is meant
to satisfy reporting requirements.
Log shipping is ideal for maintaining standby servers, but not really for
reporting purposes.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23AoYEqxbEHA.3480@.TK2MSFTNGP11.phx.gbl...
Hi Vyas,
It seems Stper need to syncronize the report server with production server
online.
Stper,
If above is the case you can go for either of the below approach
1. Transactional Replication
2. Log Shipping
This method will sync. the Report server database with report server.
Thanks
Hari
MCDBA
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:uDRYaMxbEHA.3988@.tk2msftngp13.phx.gbl...
> Are you talking about transforming data? Have you explored DTS? See SQL
> Server Books Online for more information.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "stefano perabò" <stefano.perabo@.speedautomazione.it> wrote in message
> news:%23wzR3DxbEHA.556@.tk2msftngp13.phx.gbl...
> My application has production server and backup sever for data reporting
.
> There is some tools in SQL 2K (or third part tool) for alignement data at
> run time between two servers?
> Thanks in advance
> Stper
>
>|||Thanks Hari, i think the Transactional Replication is the solution for my
problem. Now i must study the configuration
I found this an article in
www.awprofessional.com/articles/article.asp?p=170234
Do you have some suggestion for examples and/or configuration
Thanks
Stper
"Hari Prasad" <hari_prasad_k@.hotmail.com> ha scritto nel messaggio
news:#AoYEqxbEHA.3480@.TK2MSFTNGP11.phx.gbl...
> Hi Vyas,
> It seems Stper need to syncronize the report server with production server
> online.
> Stper,
> If above is the case you can go for either of the below approach
> 1. Transactional Replication
> 2. Log Shipping
> This method will sync. the Report server database with report server.
> Thanks
> Hari
> MCDBA
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:uDRYaMxbEHA.3988@.tk2msftngp13.phx.gbl...
> > Are you talking about transforming data? Have you explored DTS? See SQL
> > Server Books Online for more information.
> > --
> > HTH,
> > Vyas, MVP (SQL Server)
> > http://vyaskn.tripod.com/
> >
> >
> > "stefano perabò" <stefano.perabo@.speedautomazione.it> wrote in message
> > news:%23wzR3DxbEHA.556@.tk2msftngp13.phx.gbl...
> > My application has production server and backup sever for data
reporting
> .
> > There is some tools in SQL 2K (or third part tool) for alignement data
at
> > run time between two servers?
> > Thanks in advance
> >
> > Stper
> >
> >
> >
>|||stper,
Books Online is a very valuable reference.
http://www.mssqlserver.com/replication/primer_p1.asp has a nice primer
to replication. There is a book coming soon on Snapshot and
Transactional replication from Hilary Cotter which is excellent, see the
link in my sig.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
stper wrote:
> Thanks Hari, i think the Transactional Replication is the solution for my
> problem. Now i must study the configuration
> I found this an article in
> www.awprofessional.com/articles/article.asp?p=170234
> Do you have some suggestion for examples and/or configuration
> Thanks
> Stper
> "Hari Prasad" <hari_prasad_k@.hotmail.com> ha scritto nel messaggio
> news:#AoYEqxbEHA.3480@.TK2MSFTNGP11.phx.gbl...
>>Hi Vyas,
>>It seems Stper need to syncronize the report server with production server
>>online.
>>Stper,
>>If above is the case you can go for either of the below approach
>>1. Transactional Replication
>>2. Log Shipping
>>This method will sync. the Report server database with report server.
>>Thanks
>>Hari
>>MCDBA
>>"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
>>news:uDRYaMxbEHA.3988@.tk2msftngp13.phx.gbl...
>>Are you talking about transforming data? Have you explored DTS? See SQL
>>Server Books Online for more information.
>>--
>>HTH,
>>Vyas, MVP (SQL Server)
>>http://vyaskn.tripod.com/
>>
>>"stefano perabò" <stefano.perabo@.speedautomazione.it> wrote in message
>>news:%23wzR3DxbEHA.556@.tk2msftngp13.phx.gbl...
>>My application has production server and backup sever for data
> reporting
>>.
>>There is some tools in SQL 2K (or third part tool) for alignement data
> at
>>run time between two servers?
>>Thanks in advance
>>Stper
>>
>>
>|||i made the configuration and now i testing my first "PULL SUbscription".
Unfortunately i have my first error message : "Unexpected EOF encountered in
BCP data-file" when i run with alignement.
Do yuo have some suggestion ?
stper
"Mark Allison" <marka@.no.tinned.meat.mvps.org> ha scritto nel messaggio
news:#7aQdhybEHA.4092@.TK2MSFTNGP11.phx.gbl...
> stper,
> Books Online is a very valuable reference.
> http://www.mssqlserver.com/replication/primer_p1.asp has a nice primer
> to replication. There is a book coming soon on Snapshot and
> Transactional replication from Hilary Cotter which is excellent, see the
> link in my sig.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> stper wrote:
> > Thanks Hari, i think the Transactional Replication is the solution for
my
> > problem. Now i must study the configuration
> > I found this an article in
> > www.awprofessional.com/articles/article.asp?p=170234
> > Do you have some suggestion for examples and/or configuration
> >
> > Thanks
> > Stper
> >
> > "Hari Prasad" <hari_prasad_k@.hotmail.com> ha scritto nel messaggio
> > news:#AoYEqxbEHA.3480@.TK2MSFTNGP11.phx.gbl...
> >
> >>Hi Vyas,
> >>
> >>It seems Stper need to syncronize the report server with production
server
> >>online.
> >>
> >>Stper,
> >>
> >>If above is the case you can go for either of the below approach
> >>
> >>1. Transactional Replication
> >>
> >>2. Log Shipping
> >>
> >>This method will sync. the Report server database with report server.
> >>
> >>Thanks
> >>Hari
> >>MCDBA
> >>
> >>"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> >>news:uDRYaMxbEHA.3988@.tk2msftngp13.phx.gbl...
> >>
> >>Are you talking about transforming data? Have you explored DTS? See SQL
> >>Server Books Online for more information.
> >>--
> >>HTH,
> >>Vyas, MVP (SQL Server)
> >>http://vyaskn.tripod.com/
> >>
> >>
> >>"stefano perabò" <stefano.perabo@.speedautomazione.it> wrote in message
> >>news:%23wzR3DxbEHA.556@.tk2msftngp13.phx.gbl...
> >>My application has production server and backup sever for data
> >
> > reporting
> >
> >>.
> >>
> >>There is some tools in SQL 2K (or third part tool) for alignement data
> >
> > at
> >
> >>run time between two servers?
> >>Thanks in advance
> >>
> >>Stper
> >>
> >>
> >>
> >>
> >>
> >
> >|||After testing and testying i can say (like Pitagora) : EUREKA !!!
now i must tuning the task but the functionality work very well.
Thanks
Stper
"stper" <stefano.perabo@.speedautomazione.it> ha scritto nel messaggio
news:eZICqZzbEHA.596@.TK2MSFTNGP11.phx.gbl...
> i made the configuration and now i testing my first "PULL SUbscription".
> Unfortunately i have my first error message : "Unexpected EOF encountered
in
> BCP data-file" when i run with alignement.
> Do yuo have some suggestion ?
> stper
> "Mark Allison" <marka@.no.tinned.meat.mvps.org> ha scritto nel messaggio
> news:#7aQdhybEHA.4092@.TK2MSFTNGP11.phx.gbl...
> > stper,
> >
> > Books Online is a very valuable reference.
> > http://www.mssqlserver.com/replication/primer_p1.asp has a nice primer
> > to replication. There is a book coming soon on Snapshot and
> > Transactional replication from Hilary Cotter which is excellent, see the
> > link in my sig.
> >
> > --
> > Mark Allison, SQL Server MVP
> > http://www.markallison.co.uk
> >
> > Looking for a SQL Server replication book?
> > http://www.nwsu.com/0974973602.html
> >
> >
> >
> > stper wrote:
> > > Thanks Hari, i think the Transactional Replication is the solution for
> my
> > > problem. Now i must study the configuration
> > > I found this an article in
> > > www.awprofessional.com/articles/article.asp?p=170234
> > > Do you have some suggestion for examples and/or configuration
> > >
> > > Thanks
> > > Stper
> > >
> > > "Hari Prasad" <hari_prasad_k@.hotmail.com> ha scritto nel messaggio
> > > news:#AoYEqxbEHA.3480@.TK2MSFTNGP11.phx.gbl...
> > >
> > >>Hi Vyas,
> > >>
> > >>It seems Stper need to syncronize the report server with production
> server
> > >>online.
> > >>
> > >>Stper,
> > >>
> > >>If above is the case you can go for either of the below approach
> > >>
> > >>1. Transactional Replication
> > >>
> > >>2. Log Shipping
> > >>
> > >>This method will sync. the Report server database with report server.
> > >>
> > >>Thanks
> > >>Hari
> > >>MCDBA
> > >>
> > >>"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> > >>news:uDRYaMxbEHA.3988@.tk2msftngp13.phx.gbl...
> > >>
> > >>Are you talking about transforming data? Have you explored DTS? See
SQL
> > >>Server Books Online for more information.
> > >>--
> > >>HTH,
> > >>Vyas, MVP (SQL Server)
> > >>http://vyaskn.tripod.com/
> > >>
> > >>
> > >>"stefano perabò" <stefano.perabo@.speedautomazione.it> wrote in
message
> > >>news:%23wzR3DxbEHA.556@.tk2msftngp13.phx.gbl...
> > >>My application has production server and backup sever for data
> > >
> > > reporting
> > >
> > >>.
> > >>
> > >>There is some tools in SQL 2K (or third part tool) for alignement
data
> > >
> > > at
> > >
> > >>run time between two servers?
> > >>Thanks in advance
> > >>
> > >>Stper
> > >>
> > >>
> > >>
> > >>
> > >>
> > >
> > >
>sql
There is some tools in SQL 2K (or third part tool) for alignement data at
run time between two servers?
Thanks in advance
StperAre you talking about transforming data? Have you explored DTS? See SQL
Server Books Online for more information.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"stefano perabò" <stefano.perabo@.speedautomazione.it> wrote in message
news:%23wzR3DxbEHA.556@.tk2msftngp13.phx.gbl...
My application has production server and backup sever for data reporting .
There is some tools in SQL 2K (or third part tool) for alignement data at
run time between two servers?
Thanks in advance
Stper|||Hi Vyas,
It seems Stper need to syncronize the report server with production server
online.
Stper,
If above is the case you can go for either of the below approach
1. Transactional Replication
2. Log Shipping
This method will sync. the Report server database with report server.
Thanks
Hari
MCDBA
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:uDRYaMxbEHA.3988@.tk2msftngp13.phx.gbl...
> Are you talking about transforming data? Have you explored DTS? See SQL
> Server Books Online for more information.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "stefano perabò" <stefano.perabo@.speedautomazione.it> wrote in message
> news:%23wzR3DxbEHA.556@.tk2msftngp13.phx.gbl...
> My application has production server and backup sever for data reporting
.
> There is some tools in SQL 2K (or third part tool) for alignement data at
> run time between two servers?
> Thanks in advance
> Stper
>
>|||In that case, I'd strongly recommend transactional replication. It is meant
to satisfy reporting requirements.
Log shipping is ideal for maintaining standby servers, but not really for
reporting purposes.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23AoYEqxbEHA.3480@.TK2MSFTNGP11.phx.gbl...
Hi Vyas,
It seems Stper need to syncronize the report server with production server
online.
Stper,
If above is the case you can go for either of the below approach
1. Transactional Replication
2. Log Shipping
This method will sync. the Report server database with report server.
Thanks
Hari
MCDBA
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:uDRYaMxbEHA.3988@.tk2msftngp13.phx.gbl...
> Are you talking about transforming data? Have you explored DTS? See SQL
> Server Books Online for more information.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "stefano perabò" <stefano.perabo@.speedautomazione.it> wrote in message
> news:%23wzR3DxbEHA.556@.tk2msftngp13.phx.gbl...
> My application has production server and backup sever for data reporting
.
> There is some tools in SQL 2K (or third part tool) for alignement data at
> run time between two servers?
> Thanks in advance
> Stper
>
>|||Thanks Hari, i think the Transactional Replication is the solution for my
problem. Now i must study the configuration
I found this an article in
www.awprofessional.com/articles/article.asp?p=170234
Do you have some suggestion for examples and/or configuration
Thanks
Stper
"Hari Prasad" <hari_prasad_k@.hotmail.com> ha scritto nel messaggio
news:#AoYEqxbEHA.3480@.TK2MSFTNGP11.phx.gbl...
> Hi Vyas,
> It seems Stper need to syncronize the report server with production server
> online.
> Stper,
> If above is the case you can go for either of the below approach
> 1. Transactional Replication
> 2. Log Shipping
> This method will sync. the Report server database with report server.
> Thanks
> Hari
> MCDBA
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:uDRYaMxbEHA.3988@.tk2msftngp13.phx.gbl...
> > Are you talking about transforming data? Have you explored DTS? See SQL
> > Server Books Online for more information.
> > --
> > HTH,
> > Vyas, MVP (SQL Server)
> > http://vyaskn.tripod.com/
> >
> >
> > "stefano perabò" <stefano.perabo@.speedautomazione.it> wrote in message
> > news:%23wzR3DxbEHA.556@.tk2msftngp13.phx.gbl...
> > My application has production server and backup sever for data
reporting
> .
> > There is some tools in SQL 2K (or third part tool) for alignement data
at
> > run time between two servers?
> > Thanks in advance
> >
> > Stper
> >
> >
> >
>|||stper,
Books Online is a very valuable reference.
http://www.mssqlserver.com/replication/primer_p1.asp has a nice primer
to replication. There is a book coming soon on Snapshot and
Transactional replication from Hilary Cotter which is excellent, see the
link in my sig.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
stper wrote:
> Thanks Hari, i think the Transactional Replication is the solution for my
> problem. Now i must study the configuration
> I found this an article in
> www.awprofessional.com/articles/article.asp?p=170234
> Do you have some suggestion for examples and/or configuration
> Thanks
> Stper
> "Hari Prasad" <hari_prasad_k@.hotmail.com> ha scritto nel messaggio
> news:#AoYEqxbEHA.3480@.TK2MSFTNGP11.phx.gbl...
>>Hi Vyas,
>>It seems Stper need to syncronize the report server with production server
>>online.
>>Stper,
>>If above is the case you can go for either of the below approach
>>1. Transactional Replication
>>2. Log Shipping
>>This method will sync. the Report server database with report server.
>>Thanks
>>Hari
>>MCDBA
>>"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
>>news:uDRYaMxbEHA.3988@.tk2msftngp13.phx.gbl...
>>Are you talking about transforming data? Have you explored DTS? See SQL
>>Server Books Online for more information.
>>--
>>HTH,
>>Vyas, MVP (SQL Server)
>>http://vyaskn.tripod.com/
>>
>>"stefano perabò" <stefano.perabo@.speedautomazione.it> wrote in message
>>news:%23wzR3DxbEHA.556@.tk2msftngp13.phx.gbl...
>>My application has production server and backup sever for data
> reporting
>>.
>>There is some tools in SQL 2K (or third part tool) for alignement data
> at
>>run time between two servers?
>>Thanks in advance
>>Stper
>>
>>
>|||i made the configuration and now i testing my first "PULL SUbscription".
Unfortunately i have my first error message : "Unexpected EOF encountered in
BCP data-file" when i run with alignement.
Do yuo have some suggestion ?
stper
"Mark Allison" <marka@.no.tinned.meat.mvps.org> ha scritto nel messaggio
news:#7aQdhybEHA.4092@.TK2MSFTNGP11.phx.gbl...
> stper,
> Books Online is a very valuable reference.
> http://www.mssqlserver.com/replication/primer_p1.asp has a nice primer
> to replication. There is a book coming soon on Snapshot and
> Transactional replication from Hilary Cotter which is excellent, see the
> link in my sig.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> stper wrote:
> > Thanks Hari, i think the Transactional Replication is the solution for
my
> > problem. Now i must study the configuration
> > I found this an article in
> > www.awprofessional.com/articles/article.asp?p=170234
> > Do you have some suggestion for examples and/or configuration
> >
> > Thanks
> > Stper
> >
> > "Hari Prasad" <hari_prasad_k@.hotmail.com> ha scritto nel messaggio
> > news:#AoYEqxbEHA.3480@.TK2MSFTNGP11.phx.gbl...
> >
> >>Hi Vyas,
> >>
> >>It seems Stper need to syncronize the report server with production
server
> >>online.
> >>
> >>Stper,
> >>
> >>If above is the case you can go for either of the below approach
> >>
> >>1. Transactional Replication
> >>
> >>2. Log Shipping
> >>
> >>This method will sync. the Report server database with report server.
> >>
> >>Thanks
> >>Hari
> >>MCDBA
> >>
> >>"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> >>news:uDRYaMxbEHA.3988@.tk2msftngp13.phx.gbl...
> >>
> >>Are you talking about transforming data? Have you explored DTS? See SQL
> >>Server Books Online for more information.
> >>--
> >>HTH,
> >>Vyas, MVP (SQL Server)
> >>http://vyaskn.tripod.com/
> >>
> >>
> >>"stefano perabò" <stefano.perabo@.speedautomazione.it> wrote in message
> >>news:%23wzR3DxbEHA.556@.tk2msftngp13.phx.gbl...
> >>My application has production server and backup sever for data
> >
> > reporting
> >
> >>.
> >>
> >>There is some tools in SQL 2K (or third part tool) for alignement data
> >
> > at
> >
> >>run time between two servers?
> >>Thanks in advance
> >>
> >>Stper
> >>
> >>
> >>
> >>
> >>
> >
> >|||After testing and testying i can say (like Pitagora) : EUREKA !!!
now i must tuning the task but the functionality work very well.
Thanks
Stper
"stper" <stefano.perabo@.speedautomazione.it> ha scritto nel messaggio
news:eZICqZzbEHA.596@.TK2MSFTNGP11.phx.gbl...
> i made the configuration and now i testing my first "PULL SUbscription".
> Unfortunately i have my first error message : "Unexpected EOF encountered
in
> BCP data-file" when i run with alignement.
> Do yuo have some suggestion ?
> stper
> "Mark Allison" <marka@.no.tinned.meat.mvps.org> ha scritto nel messaggio
> news:#7aQdhybEHA.4092@.TK2MSFTNGP11.phx.gbl...
> > stper,
> >
> > Books Online is a very valuable reference.
> > http://www.mssqlserver.com/replication/primer_p1.asp has a nice primer
> > to replication. There is a book coming soon on Snapshot and
> > Transactional replication from Hilary Cotter which is excellent, see the
> > link in my sig.
> >
> > --
> > Mark Allison, SQL Server MVP
> > http://www.markallison.co.uk
> >
> > Looking for a SQL Server replication book?
> > http://www.nwsu.com/0974973602.html
> >
> >
> >
> > stper wrote:
> > > Thanks Hari, i think the Transactional Replication is the solution for
> my
> > > problem. Now i must study the configuration
> > > I found this an article in
> > > www.awprofessional.com/articles/article.asp?p=170234
> > > Do you have some suggestion for examples and/or configuration
> > >
> > > Thanks
> > > Stper
> > >
> > > "Hari Prasad" <hari_prasad_k@.hotmail.com> ha scritto nel messaggio
> > > news:#AoYEqxbEHA.3480@.TK2MSFTNGP11.phx.gbl...
> > >
> > >>Hi Vyas,
> > >>
> > >>It seems Stper need to syncronize the report server with production
> server
> > >>online.
> > >>
> > >>Stper,
> > >>
> > >>If above is the case you can go for either of the below approach
> > >>
> > >>1. Transactional Replication
> > >>
> > >>2. Log Shipping
> > >>
> > >>This method will sync. the Report server database with report server.
> > >>
> > >>Thanks
> > >>Hari
> > >>MCDBA
> > >>
> > >>"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> > >>news:uDRYaMxbEHA.3988@.tk2msftngp13.phx.gbl...
> > >>
> > >>Are you talking about transforming data? Have you explored DTS? See
SQL
> > >>Server Books Online for more information.
> > >>--
> > >>HTH,
> > >>Vyas, MVP (SQL Server)
> > >>http://vyaskn.tripod.com/
> > >>
> > >>
> > >>"stefano perabò" <stefano.perabo@.speedautomazione.it> wrote in
message
> > >>news:%23wzR3DxbEHA.556@.tk2msftngp13.phx.gbl...
> > >>My application has production server and backup sever for data
> > >
> > > reporting
> > >
> > >>.
> > >>
> > >>There is some tools in SQL 2K (or third part tool) for alignement
data
> > >
> > > at
> > >
> > >>run time between two servers?
> > >>Thanks in advance
> > >>
> > >>Stper
> > >>
> > >>
> > >>
> > >>
> > >>
> > >
> > >
>sql
Tuesday, March 27, 2012
align the parameters in reports
IS it possible to design the Parameters Layout in Reporting Service
reports
miniYou can modify styles in HTMLViewer.css style sheet present in Reporting
Services\ReportServer\Styles folder but you will only affect appearance of
the elements, not the distribution or type of controls you want to show.
If you want to fully customize that layout, you need to use reporting
services API, as I have done. For example, I have used radio buttons to
choose report parameters instead of the default combobox.
Jaime
"mini" wrote:
> IS it possible to design the Parameters Layout in Reporting Service
> reports
> mini
>|||thanks Jaime
can u explain how you use reporting
services API?
Can u reduce the Textbox width displayed in parameter layout?
thanks
mini
reports
miniYou can modify styles in HTMLViewer.css style sheet present in Reporting
Services\ReportServer\Styles folder but you will only affect appearance of
the elements, not the distribution or type of controls you want to show.
If you want to fully customize that layout, you need to use reporting
services API, as I have done. For example, I have used radio buttons to
choose report parameters instead of the default combobox.
Jaime
"mini" wrote:
> IS it possible to design the Parameters Layout in Reporting Service
> reports
> mini
>|||thanks Jaime
can u explain how you use reporting
services API?
Can u reduce the Textbox width displayed in parameter layout?
thanks
mini
Align image
I have an image in a table cell in my report. Naturally not all images fit 100% in the cell which is why I'd like to align the images in the middle of the cell, horisontally and vertically.
I've been unable to figure out how to do this in Reporting Services for SQL Server 2000. Am I missing something or is this simply not possible? Does anyone know whether it can be done in 2005?
try manipulating the padding values
|||I would but we're talking an entire column of images of varying sizes here thus I would have to calculate the correct padding size based on the size of the column and of the image itself. It seems like a little much for such a simple thing.|||I have not seen any built in functionality for this in reporting services so I guess your options are pretty limited. Padding values are a sure way to get this done but you could have the cell fixed sized and have your image fit proportionally|||http://blogs.msdn.com/ChrisHays/this link tells you what i been saying all along. paddings are the only way to go.
and the working sample is here
Align Calculated Members and Measures
Hi
I created two calculated member:
CREATEMEMBERCURRENTCUBE.[MEASURES].[Penetracion]
ASIIf([Measures].[Surtimientos] = 0,0,[Measures].[Seguros]/[Measures].[Surtimientos]),
FORMAT_STRING = "Percent",
VISIBLE = 1;
CREATEMEMBERCURRENTCUBE.[MEASURES].[Calidad]
ASIIf([Measures].[Seguros] = 0,0,[Measures].[Prima Seguros]/[Measures].[Seguros]),
FORMAT_STRING = "$#,##0.00",
VISIBLE = 1 ;
I have two measures called [Measures].[Surtimientos] and [Measures].[Seguros] and both have its FormatString property #,#.
When I browse the cube the measure and calculated meber values ared formated but they have left alignment. If I take away the FormatString they have right alignment.
Is there a way to align the values to right when I use FormatString?
Regards
If you change FORMAT_STRING = "$#,##0.00", to FORMAT_STRING = "$# ##0.00"?
Regards
Thomas Ivarsson
aliasing two columns in SQL
Hi,
Here is my original query:
select rosterid, lastname, firstname from table
order by lastname
I would like to use column aliasing to display
lastname, firstname in a column entitled name.
I tried the following syntax, but it's not working:
select rosterid, lastname+', '+firstname as name
from table
order by name
This results in a 2 column table with the headings "ROSTERID" and
"NAME". However, NAME contains th last name only, rather than "lastname,
firstname".
Any help greatly appreciated.
Thanks,
Google Jeny
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!One thing worth looking at is not using reserved words such as Name.
You need to change this to something different.
R
Google Jenny <michiamo@.yahoo.com> wrote in message news:<412cfc29$0$14430$c397aba@.news.newsgroups.ws>...
> Hi,
> Here is my original query:
> select rosterid, lastname, firstname from table
> order by lastname
>
> I would like to use column aliasing to display
> lastname, firstname in a column entitled name.
> I tried the following syntax, but it's not working:
> select rosterid, lastname+', '+firstname as name
> from table
> order by name
> This results in a 2 column table with the headings "ROSTERID" and
> "NAME". However, NAME contains th last name only, rather than "lastname,
> firstname".
> Any help greatly appreciated.
> Thanks,
> Google Jeny
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Google Jenny <michiamo@.yahoo.com> wrote in message news:<412cfc29$0$14430$c397aba@.news.newsgroups.ws>...
> Hi,
> Here is my original query:
> select rosterid, lastname, firstname from table
> order by lastname
>
> I would like to use column aliasing to display
> lastname, firstname in a column entitled name.
> I tried the following syntax, but it's not working:
> select rosterid, lastname+', '+firstname as name
> from table
> order by name
> This results in a 2 column table with the headings "ROSTERID" and
> "NAME". However, NAME contains th last name only, rather than "lastname,
> firstname".
> Any help greatly appreciated.
> Thanks,
> Google Jeny
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
This query works for me in pubs:
select au_id, au_lname + ', ' + au_fname as 'name'
from authors
order by 'name'
Perhaps you could post your CREATE TABLE statement and some sample data?
Simon|||You may want to add the following as well to remove any additional
spaces in the name fields:
select rosterid, rtrim(lastname) +', ' + rtrim(firstname) as Name
from table|||Thanks so much. The rtrim did the trick.
Google Jenny
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Aliasing columns for a DMX subquery
I require the column of a nested table (KOL s) as part of the output of my DMX query, which needs to be written out to a relational table. Hence, I flatten the <select_list> of the SELECT DMX query as below:
SELECT FLATTENED
([Speciality].[SPECIALITY ID]) as [Speciality_Id],
(0) as [Bool_NameInAuthors],
(0) as [Bool_EmailInAbstract],
(0) as [Bool_AffiliationInAbstract],
(SELECT ([KOL ID]) as [Id], ([FIRST NAME]) as [FirstName], ([MIDDLE NAME]) as [MiddleName], ([LAST NAME]) as [LastName], ([AFFILIATION]) as [Affiliation], ([EMAILADDRESS]) as [EmailAddress] FROM [Speciality].[KO Ls]),
(SELECT ([Speciality Term DESCRIPTION]) as [Term] FROM [Speciality].[SPECIALITYTERMS]) AS Spec
From
[Speciality]
PREDICTION JOIN
OPENQUERY([ETL Profiler DB],
'SELECT
[SPECIALITY_ID]
FROM
[dbo].[KOLs]
') AS t
ON
[Speciality].[SPECIALITY ID] = t.[SPECIALITY_ID]
However, this causes the subquery columns (ID, FirstName, ...) to be aliased as Expression.ID, Expression.FirstName...
How do I alias these flattened columns properly?
I tried to alias the subquery to a derived table (as follows), but it just replaces the Expression word by the derived table alias (KOL in this case). So, does not solve my problem.
(SELECT ([KOL ID]) as [Id], ([FIRST NAME]) as [FirstName], ([MIDDLE NAME]) as [MiddleName], ([LAST NAME]) as [LastName], ([AFFILIATION]) as [Affiliation], ([EMAILADDRESS]) as [EmailAddress] FROM [Speciality].[KO Ls]) AS KOL
You can enclose the entire query in another SELECT where you alias the nested table columns -
SELECT [KOL.Id] as KOL_Id, [KOL.FirstName] as KOL_FirstName, ....
FROM
(SELECT FLATTENED ....) AS TT
sqlAliasing a column name
Hi,
I'm trying to write a SQL event (view or storied proceedure). I have a table that is meant for reporting....the data is arranged verticle. I deal in Fiscal Years ie 2006/2007, 2007/2008, ect. The table in question has generic column lables ie FY1, FY2. I'm writing a report off the table and I want to dynamically turn FY1 into the current FiscalYear, FY2 into current FiscalYear + 1. I tried:
SELECT dbo.tblBudgetConfig.CurrentBudgetYear, dbo.tblBudgetProjectedCurrent.FY4 AS Left ([dbo.tblbudgetConfig.CurrentBudgetYear],4)+3 & "/" & Right([dbo.tblbudgetConfig.CurrentBudgetYear],4)+3
FROM dbo.tblBudgetConfig INNER JOIN
dbo.tblBudgetProjectedCurrent ON dbo.tblBudgetConfig.CurrentBudgetYear = dbo.tblBudgetProjectedCurrent.CurrentBudgetYear
But SQL squawks everytime I try this and tells me that there is something a miss near 'Left'.
Any help will be appreciated.
Nope, you cannot dynamically alias columns. Columns are part of the definition of the query and have to be there at the end of compile phase, not execution. To do this you would need to use dynamic SQL like EXEC ('query string'). You would have to build up the AS in a prior query .
A couple of things to note:
& does not work in SQL. You have to use + for concatenation (and you have to cast everything to the proper datatype_
"value" does not mean a literal, it means a column name. Use single quotes 'value'
Consider using the user interface to manage such things. This wouldn't be likely possible query anyhow because the data could change row by row, so you could then have a variable column name (it may not in your case, but SQL doesn't know that.) Instead of using the column names, add a column named fiscal year and store your value in there. Then let the UI put it in the right places and make it look all pretty for the user.
|||AFAIK, you can′t compose the Aliases on the fly.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Aliasing a column name
Hi,
I'm trying to write a SQL event (view or storied proceedure). I have a table that is meant for reporting....the data is arranged verticle. I deal in Fiscal Years ie 2006/2007, 2007/2008, ect. The table in question has generic column lables ie FY1, FY2. I'm writing a report off the table and I want to dynamically turn FY1 into the current FiscalYear, FY2 into current FiscalYear + 1. I tried:
SELECT dbo.tblBudgetConfig.CurrentBudgetYear, dbo.tblBudgetProjectedCurrent.FY4 AS Left ([dbo.tblbudgetConfig.CurrentBudgetYear],4)+3 & "/" & Right([dbo.tblbudgetConfig.CurrentBudgetYear],4)+3
FROM dbo.tblBudgetConfig INNER JOIN
dbo.tblBudgetProjectedCurrent ON dbo.tblBudgetConfig.CurrentBudgetYear = dbo.tblBudgetProjectedCurrent.CurrentBudgetYear
But SQL squawks everytime I try this and tells me that there is something a miss near 'Left'.
Any help will be appreciated.
Nope, you cannot dynamically alias columns. Columns are part of the definition of the query and have to be there at the end of compile phase, not execution. To do this you would need to use dynamic SQL like EXEC ('query string'). You would have to build up the AS in a prior query .
A couple of things to note:
& does not work in SQL. You have to use + for concatenation (and you have to cast everything to the proper datatype_
"value" does not mean a literal, it means a column name. Use single quotes 'value'
Consider using the user interface to manage such things. This wouldn't be likely possible query anyhow because the data could change row by row, so you could then have a variable column name (it may not in your case, but SQL doesn't know that.) Instead of using the column names, add a column named fiscal year and store your value in there. Then let the UI put it in the right places and make it look all pretty for the user.
|||AFAIK, you can′t compose the Aliases on the fly.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Aliases not found on log in
When loggining into SSMS there does not appear to be a way to connect to the
ALIAS remote servers names like you could with SQL Server 2000. Does any one
know how to use the Alias names at the SQL Server 2005 connection dialog?D. Haber (DHaber@.discussions.microsoft.com) writes:
> When loggining into SSMS there does not appear to be a way to connect to
> the ALIAS remote servers names like you could with SQL Server 2000. Does
> any one know how to use the Alias names at the SQL Server 2005
> connection dialog?
What aliases? Those you defined in the Client Network Utility? I guess you
need to use the SQL Configuration Manager to set up aliaes for the SQL 2005
tools. It's under SQL Native Client configuration, which is a little
misleading, but I did a very quick test, and it seemed to work.
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
ALIAS remote servers names like you could with SQL Server 2000. Does any one
know how to use the Alias names at the SQL Server 2005 connection dialog?D. Haber (DHaber@.discussions.microsoft.com) writes:
> When loggining into SSMS there does not appear to be a way to connect to
> the ALIAS remote servers names like you could with SQL Server 2000. Does
> any one know how to use the Alias names at the SQL Server 2005
> connection dialog?
What aliases? Those you defined in the Client Network Utility? I guess you
need to use the SQL Configuration Manager to set up aliaes for the SQL 2005
tools. It's under SQL Native Client configuration, which is a little
misleading, but I did a very quick test, and it seemed to work.
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
Aliases & Columns Name w/ Spaces
Formatting question. The query below is failing on the columns with spaces in the name. I've tried brackets and single quotes with no luck. How should this be formatted?
-
SELECT x.trkuniq, s.mstuniq, t.meetuniq,
c.coursec AS Course_Code,
c.descript AS Course_Name,
[q.cactus #] AS Cactus_#
s.sectionn AS Section,
RTRIM(f.lastname) + ', ' + RTRIM(f.firstname) AS Teacher, f.funiq,
t.termc AS Term_Code, zd.cycldayc AS Day,
zp.periodc AS Period, zp.periodn,
p.schoolc AS School
FROM mstmeet t INNER JOIN
mstsched s ON t.mstuniq = s.mstuniq INNER JOIN
trkcrs x ON s.trkcrsuniq = x.trkcrsuniq INNER JOIN
course c ON x.crsuniq = c.crsuniq INNER JOIN
track p ON p.trkuniq = x.trkuniq INNER JOIN
facdemo f ON s.funiq = f.funiq INNER JOIN
courses q ON c.coursec = [q.course number] INNER JOIN
trkper zp ON t.periodn = zp.periodn AND
x.trkuniq = zp.trkuniq INNER JOIN
trkcycle zd ON t.cycldayn = zd.cycldayn AND
x.trkuniq = zd.trkuniq
Kinny:
Try changing your reference from [q.cactus #] to q.[cactus #]
|||Dave
The brackets are use to encapsulate a name of a table, schema, etc, not the entire messed up name :)
You can even include dots in the name:
create table [dbo].[bad.idea]
(
[bad.idea.id] int
)
select *
from [bad.idea]
And yes, very very bad idea.
Aliases
I need to know where an alias is stored. I am new to SQL and need to
be sure I am able to recover from a server failure with all components
and jobs intact.
Thanks, JohnIn the registry under:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\Client\ConnectTo
-Sue
On 30 Mar 2004 14:38:55 -0800, news13579@.hotmail.com
(Needing help as usual) wrote:
>I need to know where an alias is stored. I am new to SQL and need to
>be sure I am able to recover from a server failure with all components
>and jobs intact.
>Thanks, Johnsql
be sure I am able to recover from a server failure with all components
and jobs intact.
Thanks, JohnIn the registry under:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\Client\ConnectTo
-Sue
On 30 Mar 2004 14:38:55 -0800, news13579@.hotmail.com
(Needing help as usual) wrote:
>I need to know where an alias is stored. I am new to SQL and need to
>be sure I am able to recover from a server failure with all components
>and jobs intact.
>Thanks, Johnsql
Aliases
I need to know where an alias is stored. I am new to SQL and need to
be sure I am able to recover from a server failure with all components
and jobs intact.
Thanks, John
In the registry under:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Client\ConnectTo
-Sue
On 30 Mar 2004 14:38:55 -0800, news13579@.hotmail.com
(Needing help as usual) wrote:
>I need to know where an alias is stored. I am new to SQL and need to
>be sure I am able to recover from a server failure with all components
>and jobs intact.
>Thanks, John
be sure I am able to recover from a server failure with all components
and jobs intact.
Thanks, John
In the registry under:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Client\ConnectTo
-Sue
On 30 Mar 2004 14:38:55 -0800, news13579@.hotmail.com
(Needing help as usual) wrote:
>I need to know where an alias is stored. I am new to SQL and need to
>be sure I am able to recover from a server failure with all components
>and jobs intact.
>Thanks, John
Aliased column in stored procedure not seen by datagrid
i have an aliased column in an sql statement that works fine when
displaying its output in a datagrid, but when I transfer the sql
statement into a stored procedure , the datagrid can't see it. I get an
error "{"DataBinder.Eval: 'System.Data.DataRowView' does not contain a
property with the name myaliasedcolumn." }Hi
displaying its output in a datagrid, but when I transfer the sql
statement into a stored procedure , the datagrid can't see it. I get an
error "{"DataBinder.Eval: 'System.Data.DataRowView' does not contain a
property with the name myaliasedcolumn." }Hi
You may want to post the DDL for this. If you are using exactly the same SQL
Statement then it should be the same. You may want make sure that you SET
NOCOUNT ON at the start of the procedure.
John
".Net Sports" <ballz2wall@.cox.net> wrote in message
news:1126914453.889098.79790@.g47g2000cwa.googlegro ups.com...
>i have an aliased column in an sql statement that works fine when
> displaying its output in a datagrid, but when I transfer the sql
> statement into a stored procedure , the datagrid can't see it. I get an
> error "{"DataBinder.Eval: 'System.Data.DataRowView' does not contain a
> property with the name myaliasedcolumn." }
Aliased Column and Where Clauses
I want to use an aliased field with a where caluse as below
select member_ID,
CASE
WHEN (status_id & (16 | 128)) = (16 | 128) OR (status_id & 16 ) = 0
THEN CAST(1 AS bit) ELSE CAST(0 AS bit)
END AS IsMigrated
FROM member
LEFT OUTER JOIN language ON language.language_id = member.country_id
WHERE language = 'Deutsch'
-- AND IsMigrated = 1
However i keep getting "Invalid column name 'IsMigrated'" when i uncomment the AND IsMigrated = 1 line.
That construct is not available in TSQL; you will need to either need to convert that into a function or "spell it out fully" in the where clause.
Here are a few past threads that had related discussions:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=89097&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1137002&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=803299&SiteID=1
You could try using a derived table, see below.
Chris
Code Snippet
SELECT t.member_ID,
t.IsMigrated
FROM (
SELECT member_ID,
CASE WHEN (status_id & (16 | 128)) = (16 | 128)
OR (status_id & 16) = 0 THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT)
END AS IsMigrated
FROM member
LEFT OUTER JOIN language ON language.language_id = member.country_id
WHERE language = 'Deutsch'
) t
WHERE t.IsMigrated = 1
|||The data behind an 'aliased' column is not known by that alias during the data retreival. You cannot use an 'aliased' column name in the SELECT, JOIN conditions, or WHERE criteria of a query.
However, since the data from the query is 'pulled' into a derived table and then sorted, the derived table will know the 'aliased' data by the column name aliases, and you can use the alias in the ORDER BY
|||I just keep running into this one...
A question to the developement team of SQL: is there any reason why this hasn't been implemented?
The simplest implementation would be to sunbstitue any reference to a derived column with it's definition, by doing a search & replace on the code before feeding it to the interpreter. I can't figure out why it would be difficult to implement or how it would break existing code.
Simple thing like
Col1+Col2 as Sub1,
col3 + col4 as Sub2,
Sub1 + Sub2 as Total
Shouldn't be too hard for the interpreter to figure out? (Access can do it...)
Also something like
select Col1 + '.' + Col2 as Concat,
... bla bla
Group by Concat
Order by Concat
This would really help to keep your 'eye on the ball' instead of on the technical implementation. It would also help maintainability.
So..... next CTP of Katmai it is then? ;-)
Regards,
Gert-Jan
|||Post your suggestions at:
Suggestions for SQL Server
http://connect.microsoft.com/sqlserver
Aliased Column and Where Clauses
I want to use an aliased field with a where caluse as below
select member_ID,
CASE
WHEN (status_id & (16 | 128)) = (16 | 128) OR (status_id & 16 ) = 0
THEN CAST(1 AS bit) ELSE CAST(0 AS bit)
END AS IsMigrated
FROM member
LEFT OUTER JOIN language ON language.language_id = member.country_id
WHERE language = 'Deutsch'
-- AND IsMigrated = 1
However i keep getting "Invalid column name 'IsMigrated'" when i uncomment the AND IsMigrated = 1 line.
That construct is not available in TSQL; you will need to either need to convert that into a function or "spell it out fully" in the where clause.
Here are a few past threads that had related discussions:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=89097&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1137002&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=803299&SiteID=1
You could try using a derived table, see below.
Chris
Code Snippet
SELECT t.member_ID,
t.IsMigrated
FROM (
SELECT member_ID,
CASE WHEN (status_id & (16 | 128)) = (16 | 128)
OR (status_id & 16) = 0 THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT)
END AS IsMigrated
FROM member
LEFT OUTER JOIN language ON language.language_id = member.country_id
WHERE language = 'Deutsch'
) t
WHERE t.IsMigrated = 1
|||The data behind an 'aliased' column is not known by that alias during the data retreival. You cannot use an 'aliased' column name in the SELECT, JOIN conditions, or WHERE criteria of a query.
However, since the data from the query is 'pulled' into a derived table and then sorted, the derived table will know the 'aliased' data by the column name aliases, and you can use the alias in the ORDER BY
|||I just keep running into this one...
A question to the developement team of SQL: is there any reason why this hasn't been implemented?
The simplest implementation would be to sunbstitue any reference to a derived column with it's definition, by doing a search & replace on the code before feeding it to the interpreter. I can't figure out why it would be difficult to implement or how it would break existing code.
Simple thing like
Col1+Col2 as Sub1,
col3 + col4 as Sub2,
Sub1 + Sub2 as Total
Shouldn't be too hard for the interpreter to figure out? (Access can do it...)
Also something like
select Col1 + '.' + Col2 as Concat,
... bla bla
Group by Concat
Order by Concat
This would really help to keep your 'eye on the ball' instead of on the technical implementation. It would also help maintainability.
So..... next CTP of Katmai it is then? ;-)
Regards,
Gert-Jan
|||Post your suggestions at:
Suggestions for SQL Server
http://connect.microsoft.com/sqlserver
Alias with XP
Why is it necessary to create an alias in the Client Network Utility to be able to connect to SQL Server if you are running Windows XP?To clarify a little, it seems it is only necessary in XP. We don't have to create an alias when the client is running NT or 2000, only XP. Also, if the network connection goes down, we occasionally lose the alias and have to re-create it. Any ideas?sql
alias w/IN operator
running this now, but need the names, & not emp ID's.
select ticketid, acct, date, createuser
from ticket
where createuser in ('k1001', 'k1002', 'k1003', 'k1004')
group by createuser
will the 'as' work in the IN operator?
where createuser in ('k1001' as Dave, 'k1002' as Sue, 'k1003' as Santa,
'k1004' as Elf) ?
broski wrote:
>running this now, but need the names, & not emp ID's.
>select ticketid, acct, date, createuser
>from ticket
>where createuser in ('k1001', 'k1002', 'k1003', 'k1004')
>group by createuser
>will the 'as' work in the IN operator?
>where createuser in ('k1001' as Dave, 'k1002' as Sue, 'k1003' as Santa,
>'k1004' as Elf) ?
sorry...just signed up & realized i posted in connectivity....meant to be
under reporting.
thank you.
select ticketid, acct, date, createuser
from ticket
where createuser in ('k1001', 'k1002', 'k1003', 'k1004')
group by createuser
will the 'as' work in the IN operator?
where createuser in ('k1001' as Dave, 'k1002' as Sue, 'k1003' as Santa,
'k1004' as Elf) ?
broski wrote:
>running this now, but need the names, & not emp ID's.
>select ticketid, acct, date, createuser
>from ticket
>where createuser in ('k1001', 'k1002', 'k1003', 'k1004')
>group by createuser
>will the 'as' work in the IN operator?
>where createuser in ('k1001' as Dave, 'k1002' as Sue, 'k1003' as Santa,
>'k1004' as Elf) ?
sorry...just signed up & realized i posted in connectivity....meant to be
under reporting.
thank you.
alias w/IN operator
running this now, but need the names, & not emp ID's.
select ticketid, acct, date, createuser
from ticket
where createuser in ('k1001', 'k1002', 'k1003', 'k1004')
group by createuser
will the 'as' work in the IN operator?
where createuser in ('k1001' as Dave, 'k1002' as Sue, 'k1003' as Santa,
'k1004' as Elf) ?broski wrote:
>running this now, but need the names, & not emp ID's.
>select ticketid, acct, date, createuser
>from ticket
>where createuser in ('k1001', 'k1002', 'k1003', 'k1004')
>group by createuser
>will the 'as' work in the IN operator?
>where createuser in ('k1001' as Dave, 'k1002' as Sue, 'k1003' as Santa,
>'k1004' as Elf) ?
sorry...just signed up & realized i posted in connectivity....meant to be
under reporting.
thank you.
select ticketid, acct, date, createuser
from ticket
where createuser in ('k1001', 'k1002', 'k1003', 'k1004')
group by createuser
will the 'as' work in the IN operator?
where createuser in ('k1001' as Dave, 'k1002' as Sue, 'k1003' as Santa,
'k1004' as Elf) ?broski wrote:
>running this now, but need the names, & not emp ID's.
>select ticketid, acct, date, createuser
>from ticket
>where createuser in ('k1001', 'k1002', 'k1003', 'k1004')
>group by createuser
>will the 'as' work in the IN operator?
>where createuser in ('k1001' as Dave, 'k1002' as Sue, 'k1003' as Santa,
>'k1004' as Elf) ?
sorry...just signed up & realized i posted in connectivity....meant to be
under reporting.
thank you.
Alias question
The following is not working:
SELECT [Total Calls] / [Conversion Rate] AS [Customers], [Customers] *
[Customer Value] AS [Sales], [Sales] * [Profit Margin] AS Profit FROM [Table]
SQL doesn't recognize the aliased column names ([Customers] and [Sales] in
the above example) when I try to use them in later calculations. Is there a
way to do this without actually having to do all the calculations for each
successive column? I've got a lot more calculations to do than just the ones
I'm showing here, so I'd like to limit the amount of SQL code to sift throug
h
if at all possible.Your alternatives are views/derived tables or reusing the entire expression.
So you can have:
SELECT "Total Calls" / "Conversion Rate" AS "Customers",
( "Total Calls" / "Conversion Rate" )
* "Customer Value" AS "Sales",
( "Total Calls" / "Conversion Rate" )
* "Customer Value" * "Profit Margin" AS "Profit"
FROM Table ;
-- or
SELECT Customers,
Customers * Customer_value AS Sales,
Customers * Customer_value * Profit_margin AS profit
FROM (
SELECT "Total Calls" / "Conversion Rate",
"Customer Value", "Profit Margin"
FROM table
) Derived_tbl ( Customers, Customer_value, Profit_margin ) ;
Anith|||Hi,
You can not use alias for this. The approaches are:-
1. As you mentioned use the calculations for each columns
2. Declare variables and use the variables in select statement
Eg:-
Declare @.customers int,
@.Sales int,
@.profit int
SELECT @.Customers = [Total Calls] / [Conversion Rate] , @.Sales= @.Customers
*
[Customer Value] , @.Profit = @.Sales * [Profit Margin] FROM [Table]
Select @.customers,@.sales,@.Profit
Thanks
Hari
SQL Server MVP
"mike" <mike@.discussions.microsoft.com> wrote in message
news:F7718D7F-2203-44A3-B664-4FCD4E9CFACE@.microsoft.com...
> The following is not working:
> SELECT [Total Calls] / [Conversion Rate] AS [Customers], [Customers] *
> [Customer Value] AS [Sales], [Sales] * [Profit Margin] AS Profit FROM
> [Table]
> SQL doesn't recognize the aliased column names ([Customers] and [Sales] in
> the above example) when I try to use them in later calculations. Is there
> a
> way to do this without actually having to do all the calculations for each
> successive column? I've got a lot more calculations to do than just the
> ones
> I'm showing here, so I'd like to limit the amount of SQL code to sift
> through
> if at all possible.
>
SELECT [Total Calls] / [Conversion Rate] AS [Customers], [Customers] *
[Customer Value] AS [Sales], [Sales] * [Profit Margin] AS Profit FROM [Table]
SQL doesn't recognize the aliased column names ([Customers] and [Sales] in
the above example) when I try to use them in later calculations. Is there a
way to do this without actually having to do all the calculations for each
successive column? I've got a lot more calculations to do than just the ones
I'm showing here, so I'd like to limit the amount of SQL code to sift throug
h
if at all possible.Your alternatives are views/derived tables or reusing the entire expression.
So you can have:
SELECT "Total Calls" / "Conversion Rate" AS "Customers",
( "Total Calls" / "Conversion Rate" )
* "Customer Value" AS "Sales",
( "Total Calls" / "Conversion Rate" )
* "Customer Value" * "Profit Margin" AS "Profit"
FROM Table ;
-- or
SELECT Customers,
Customers * Customer_value AS Sales,
Customers * Customer_value * Profit_margin AS profit
FROM (
SELECT "Total Calls" / "Conversion Rate",
"Customer Value", "Profit Margin"
FROM table
) Derived_tbl ( Customers, Customer_value, Profit_margin ) ;
Anith|||Hi,
You can not use alias for this. The approaches are:-
1. As you mentioned use the calculations for each columns
2. Declare variables and use the variables in select statement
Eg:-
Declare @.customers int,
@.Sales int,
@.profit int
SELECT @.Customers = [Total Calls] / [Conversion Rate] , @.Sales= @.Customers
*
[Customer Value] , @.Profit = @.Sales * [Profit Margin] FROM [Table]
Select @.customers,@.sales,@.Profit
Thanks
Hari
SQL Server MVP
"mike" <mike@.discussions.microsoft.com> wrote in message
news:F7718D7F-2203-44A3-B664-4FCD4E9CFACE@.microsoft.com...
> The following is not working:
> SELECT [Total Calls] / [Conversion Rate] AS [Customers], [Customers] *
> [Customer Value] AS [Sales], [Sales] * [Profit Margin] AS Profit FROM
> [Table]
> SQL doesn't recognize the aliased column names ([Customers] and [Sales] in
> the above example) when I try to use them in later calculations. Is there
> a
> way to do this without actually having to do all the calculations for each
> successive column? I've got a lot more calculations to do than just the
> ones
> I'm showing here, so I'd like to limit the amount of SQL code to sift
> through
> if at all possible.
>
Alias or Group SSAS Dimension at Query time.
In an MDX Query i am trying to alias (or group ) the returned dimension as shown below but i am getting the wrong result.I believe the issue is in the case statement logic.
Is there a way to alias (or group dynamically) dimension without creating a named column in DSV?
Any help will be appreciated.
WITH MEMBER [Measures].[Long] AS
IIF(
[Measures].[Risk Value]<0,
[Measures].[Risk Value],
null)
SET [GroupedRatings] AS
CASE
WHEN [Curve Family].[SP Rating].&[AA-] THEN [Curve Family].[SP Rating].&[AA]
WHEN [Curve Family].[SP Rating].&[AA+] THEN [Curve Family].[SP Rating].&[AA]
WHEN [Curve Family].[SP Rating].&[AAA+] THEN [Curve Family].[SP Rating].&[AAA]
WHEN [Curve Family].[SP Rating].&[AAA+] THEN [Curve Family].[SP Rating].&[AAA]
WHEN [Curve Family].[SP Rating].&[BB-] THEN [Curve Family].[SP Rating].&[BB]
WHEN [Curve Family].[SP Rating].&[BB+] THEN [Curve Family].[SP Rating].&[BB]
WHEN [Curve Family].[SP Rating].&[BBB+] THEN [Curve Family].[SP Rating].&[BBB]
ELSE NULL
END
SELECT { [Measures].[Long]} ON COLUMNS,
{ ([GroupedRatings]*[Book].[Desk].[Desk].Members) } --cross join grouped rating and desk members
ON ROWS
FROM [DM]
This is where the similarities between MDX and SQL can be confusing. What you really want to do is to create some calculated members to do your grouping and then create a set of these members.
eg.
WITH MEMBER [Measures].[Long] AS
IIF(
[Measures].[Risk Value]<0,
[Measures].[Risk Value],
null)
MEMBER [Curve Family].[SP Rating].&[AA] AS Aggregate({[Curve Family].[SP Rating].&[AA-],[Curve Family].[SP Rating].&[AA+]})
MEMBER [Curve Family].[SP Rating].&[AAA] AS Aggregate({[Curve Family].[SP Rating].&[AAA-],[Curve Family].[SP Rating].&[AAA+]}
MEMBER [Curve Family].[SP Rating].&[BB] AS Aggregate({[Curve Family].[SP Rating].&[BB-], [Curve Family].[SP Rating].&[BB+]})
MEMBER [Curve Family].[SP Rating].&[BBB] AS Aggregate({[Curve Family].[SP Rating].&[BBB+]})
SET [GroupedRatings] AS
{[Curve Family].[SP Rating].&[AA]
,[Curve Family].[SP Rating].&[AAA]
,[Curve Family].[SP Rating].&[BB]
,[Curve Family].[SP Rating].&[BBB]}
SELECT { [Measures].[Long]} ON COLUMNS,
{ ([GroupedRatings]*[Book].[Desk].[Desk].Members) } --cross join grouped rating and desk members
ON ROWS
FROM [DM]
|||The case statement won't create new members dynamically, which it looks like you're trying to do. You could declare each member explicitly, like:
WITH MEMBER [Measures].[Long] AS
IIF(
[Measures].[Risk Value]<0,
[Measures].[Risk Value],
null)
Member [Curve Family].[SP Rating].[AA] as
Sum({[Curve Family].[SP Rating].&[AA-], [Curve Family].[SP Rating].&[AA+]}),
SOLVE_ORDER = 10
Member [Curve Family].[SP Rating].[AAA] as
Sum({[Curve Family].[SP Rating].&[AAA-], [Curve Family].[SP Rating].&[AAA+]}),
SOLVE_ORDER = 10
Member [Curve Family].[SP Rating].[BB] as
Sum({[Curve Family].[SP Rating].&[BB-], [Curve Family].[SP Rating].&[BB+]}),
SOLVE_ORDER = 10
Member [Curve Family].[SP Rating].[BBB] as
Sum({[Curve Family].[SP Rating].&[BBB-], [Curve Family].[SP Rating].&[BBB+]}),
SOLVE_ORDER = 10
SET [GroupedRatings] AS
{[Curve Family].[SP Rating].[AA], [Curve Family].[SP Rating].[AAA],
[Curve Family].[SP Rating].[BB], [Curve Family].[SP Rating].[BBB]}
SELECT { [Measures].[Long]} ON COLUMNS,{ ([GroupedRatings]*[Book].[Desk].[Desk].Members) } --cross join grouped rating and desk members
ON ROWS
FROM [DM]
|||Thanks Darren for pointing me in the right direction.I changed the code to the sample below to make it work properly.
WITH
MEMBER [Curve Family].[SP Rating].[AA] AS
Aggregate({FILTER([Curve Family].[SP Rating].&[AA-],([Measures].[Risk Value])<0),FILTER([Curve Family].[SP Rating].&[AA+],([Measures].[Risk Value])<0)})
MEMBER [Curve Family].[SP Rating].[AAA] AS
Aggregate({FILTER([Curve Family].[SP Rating].&[AAA-],([Measures].[Risk Value])<0),FILTER([Curve Family].[SP Rating].&[AAA+],([Measures].[Risk Value])<0)})
MEMBER [Curve Family].[SP Rating].[BB] AS
Aggregate({FILTER([Curve Family].[SP Rating].&[BB-],([Measures].[Risk Value])<0),FILTER([Curve Family].[SP Rating].&[BB+],([Measures].[Risk Value])<0)})
MEMBER [Curve Family].[SP Rating].[BBB]AS
Aggregate({FILTER([Curve Family].[SP Rating].&[BBB-],([Measures].[Risk Value])<0),FILTER([Curve Family].[SP Rating].&[BBB+],([Measures].[Risk Value])<0)})
SET [GroupedRatings] AS
{
[Curve Family].[SP Rating].[AA]
,[Curve Family].[SP Rating].[AAA]
,[Curve Family].[SP Rating].[BB]
,[Curve Family].[SP Rating].[BBB]
}
SELECT
NON EMPTY { [Measures].[Risk Value]} ON COLUMNS,
NON EMPTY {([GroupedRatings]*[Vdim Book].[Desk].[Desk].Members)} ON ROWS
FROM
[DM]
Alias on Update query
How can I put an alias on the table in an Update query
Update T64PE as Person
Where Person.ID = 5
(PS This works with Sybase)OK
Found this !
Update T64PE
Set NAME='CHIRAC'
From T64PE as Person
Where Person.ID = 5|||Originally posted by Karolyn
OK
Found this !
Update T64PE
Set NAME='CHIRAC'
From T64PE as Person
Where Person.ID = 5
Even more :
Update Person
Set NAME='CHIRAC'
From T64PE as Person
Where Person.ID = 5|||noted !
(thks)sql
Update T64PE as Person
Where Person.ID = 5
(PS This works with Sybase)OK
Found this !
Update T64PE
Set NAME='CHIRAC'
From T64PE as Person
Where Person.ID = 5|||Originally posted by Karolyn
OK
Found this !
Update T64PE
Set NAME='CHIRAC'
From T64PE as Person
Where Person.ID = 5
Even more :
Update Person
Set NAME='CHIRAC'
From T64PE as Person
Where Person.ID = 5|||noted !
(thks)sql
Alias OK, IP not
Hi all,
I was able to get my mirroring setup to work only when I use Alias instead of IP address. Any idea why it is so?
Thanks,
Avi
I am trying to set up using IPS, someone told me you can as long as u do not use a witness server.
I am having trouble setting it up. What did you do to get it working? when you say Alias, what do you mean ( can u walk me using your steps ).
Thanks
|||
Alias is basically associating a name to an IP address for that you use
MS SQL 2005-> Configuration tools ->SQL Server Configuration Manager
Once you set up an alias you can use it instead of the IP. However, my problem was that with an alias my scripts works well but with IP it does not.
Any ideas?
Avi
|||Might be a problem with WINS in this case, have you checked with your Network admin in this case.
Ensure both the servers has similar version of tools in this case.
Alias of a Linked Server
I am using linked server with an IP address then want to user this server in query but using IP address gives error and I want to define an Alias for that linked server to use in query, Please help me.
Thanks in advance
Muhammad Hanif
Have you tried with the hostname?|||You can create an alias using the Client Network Utility (on SQL 2000) or Configuration Manager (on SQL 2005). You can create whatever alias name and enter the IP address for the server.
-Sue
Alias not working on some machines
SQL 2005 SP2 on Windows Server 2003 SP2
SQL 2000 SP4 on Windows Server 2003 SP2, with SQL 2005 Native Client installed
Some of the servers running SQL 2000 are using a 64-bit OS, some are usiing
32-bit. All use 32-bit SQL 2000.
Trying to create an Alias on a SQL 2000 machine to point to a named instance
on a SQL 2005 machine through TCP/IP (for use in replication).
When the alias is created on those SQL 2000 servers running a 32-bit OS, it
works.
When the alias is created on those SQL 2000 servers running a 64-bit OS, it
doesn't.
Instead, when I try to test using isql, I get:
DB-Library: Unable to connect: SQL Server is unavailable or does not exist.
Una
ble to connect: SQL Server does not exist or network access denied.
Net-Library error 53: ConnectionOpen (Connect()).
Any ideas?
Thanks,
Tim C
There seems to be a subtle incompatibility issue. We have started moving our
SQL 2000 servers to 32-bit OS's on virtual machines to avoid the issue.
But if anyone knows of a simpler fix we could implement, I would love to
hear it.
Thanks,
Tim C
"Tim C" wrote:
> SQL 2005 SP2 on Windows Server 2003 SP2
> SQL 2000 SP4 on Windows Server 2003 SP2, with SQL 2005 Native Client installed
> Some of the servers running SQL 2000 are using a 64-bit OS, some are usiing
> 32-bit. All use 32-bit SQL 2000.
> Trying to create an Alias on a SQL 2000 machine to point to a named instance
> on a SQL 2005 machine through TCP/IP (for use in replication).
> When the alias is created on those SQL 2000 servers running a 32-bit OS, it
> works.
> When the alias is created on those SQL 2000 servers running a 64-bit OS, it
> doesn't.
> Instead, when I try to test using isql, I get:
> DB-Library: Unable to connect: SQL Server is unavailable or does not exist.
> Una
> ble to connect: SQL Server does not exist or network access denied.
> Net-Library error 53: ConnectionOpen (Connect()).
> Any ideas?
> Thanks,
> Tim C
SQL 2000 SP4 on Windows Server 2003 SP2, with SQL 2005 Native Client installed
Some of the servers running SQL 2000 are using a 64-bit OS, some are usiing
32-bit. All use 32-bit SQL 2000.
Trying to create an Alias on a SQL 2000 machine to point to a named instance
on a SQL 2005 machine through TCP/IP (for use in replication).
When the alias is created on those SQL 2000 servers running a 32-bit OS, it
works.
When the alias is created on those SQL 2000 servers running a 64-bit OS, it
doesn't.
Instead, when I try to test using isql, I get:
DB-Library: Unable to connect: SQL Server is unavailable or does not exist.
Una
ble to connect: SQL Server does not exist or network access denied.
Net-Library error 53: ConnectionOpen (Connect()).
Any ideas?
Thanks,
Tim C
There seems to be a subtle incompatibility issue. We have started moving our
SQL 2000 servers to 32-bit OS's on virtual machines to avoid the issue.
But if anyone knows of a simpler fix we could implement, I would love to
hear it.
Thanks,
Tim C
"Tim C" wrote:
> SQL 2005 SP2 on Windows Server 2003 SP2
> SQL 2000 SP4 on Windows Server 2003 SP2, with SQL 2005 Native Client installed
> Some of the servers running SQL 2000 are using a 64-bit OS, some are usiing
> 32-bit. All use 32-bit SQL 2000.
> Trying to create an Alias on a SQL 2000 machine to point to a named instance
> on a SQL 2005 machine through TCP/IP (for use in replication).
> When the alias is created on those SQL 2000 servers running a 32-bit OS, it
> works.
> When the alias is created on those SQL 2000 servers running a 64-bit OS, it
> doesn't.
> Instead, when I try to test using isql, I get:
> DB-Library: Unable to connect: SQL Server is unavailable or does not exist.
> Una
> ble to connect: SQL Server does not exist or network access denied.
> Net-Library error 53: ConnectionOpen (Connect()).
> Any ideas?
> Thanks,
> Tim C
Alias not working on some machines
SQL 2005 SP2 on Windows Server 2003 SP2
SQL 2000 SP4 on Windows Server 2003 SP2, with SQL 2005 Native Client installed
Some of the servers running SQL 2000 are using a 64-bit OS, some are usiing
32-bit. All use 32-bit SQL 2000.
Trying to create an Alias on a SQL 2000 machine to point to a named instance
on a SQL 2005 machine through TCP/IP (for use in replication).
When the alias is created on those SQL 2000 servers running a 32-bit OS, it
works.
When the alias is created on those SQL 2000 servers running a 64-bit OS, it
doesn't.
Instead, when I try to test using isql, I get:
DB-Library: Unable to connect: SQL Server is unavailable or does not exist.
Una
ble to connect: SQL Server does not exist or network access denied.
Net-Library error 53: ConnectionOpen (Connect()).
Any ideas?
Thanks,
Tim CThere seems to be a subtle incompatibility issue. We have started moving our
SQL 2000 servers to 32-bit OS's on virtual machines to avoid the issue.
But if anyone knows of a simpler fix we could implement, I would love to
hear it.
Thanks,
Tim C
"Tim C" wrote:
> SQL 2005 SP2 on Windows Server 2003 SP2
> SQL 2000 SP4 on Windows Server 2003 SP2, with SQL 2005 Native Client installed
> Some of the servers running SQL 2000 are using a 64-bit OS, some are usiing
> 32-bit. All use 32-bit SQL 2000.
> Trying to create an Alias on a SQL 2000 machine to point to a named instance
> on a SQL 2005 machine through TCP/IP (for use in replication).
> When the alias is created on those SQL 2000 servers running a 32-bit OS, it
> works.
> When the alias is created on those SQL 2000 servers running a 64-bit OS, it
> doesn't.
> Instead, when I try to test using isql, I get:
> DB-Library: Unable to connect: SQL Server is unavailable or does not exist.
> Una
> ble to connect: SQL Server does not exist or network access denied.
> Net-Library error 53: ConnectionOpen (Connect()).
> Any ideas?
> Thanks,
> Tim C|||I recently had a discussion in .tools about this. Paul O'kasick was nice enough to share his
findings. Aparently there's both a 32 and a 64 bit version of cliconfg.exe and these modify
different registry keys. Here's a quote from Paul most recent reply:
"The alias is working on our test machine. It turns out there is a 64 bit
version of cliconfg.exe (C:\WINDOWS\SysWOW64\cliconfg.exe). As soon as I
created the alias with that version, the application started working. I
removed the alias created with the 32 bit version.
Each version maintains a separate list of aliases. The registry key for the
32 bit vs. 64 bit is
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo &
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo,
respectively."
So, my suggestion is that you try to create the alias with both 32 and 64 bit version of
cliconfg.exe to see which it is that is required in your particular case (probably depends on
whether the client app is 32 or 64 bit - meguess).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Tim C" <TimC@.discussions.microsoft.com> wrote in message
news:098FFFB2-D352-4B16-A32D-CEA42461787C@.microsoft.com...
> There seems to be a subtle incompatibility issue. We have started moving our
> SQL 2000 servers to 32-bit OS's on virtual machines to avoid the issue.
> But if anyone knows of a simpler fix we could implement, I would love to
> hear it.
> Thanks,
> Tim C
> "Tim C" wrote:
>> SQL 2005 SP2 on Windows Server 2003 SP2
>> SQL 2000 SP4 on Windows Server 2003 SP2, with SQL 2005 Native Client installed
>> Some of the servers running SQL 2000 are using a 64-bit OS, some are usiing
>> 32-bit. All use 32-bit SQL 2000.
>> Trying to create an Alias on a SQL 2000 machine to point to a named instance
>> on a SQL 2005 machine through TCP/IP (for use in replication).
>> When the alias is created on those SQL 2000 servers running a 32-bit OS, it
>> works.
>> When the alias is created on those SQL 2000 servers running a 64-bit OS, it
>> doesn't.
>> Instead, when I try to test using isql, I get:
>> DB-Library: Unable to connect: SQL Server is unavailable or does not exist.
>> Una
>> ble to connect: SQL Server does not exist or network access denied.
>> Net-Library error 53: ConnectionOpen (Connect()).
>> Any ideas?
>> Thanks,
>> Tim C
SQL 2000 SP4 on Windows Server 2003 SP2, with SQL 2005 Native Client installed
Some of the servers running SQL 2000 are using a 64-bit OS, some are usiing
32-bit. All use 32-bit SQL 2000.
Trying to create an Alias on a SQL 2000 machine to point to a named instance
on a SQL 2005 machine through TCP/IP (for use in replication).
When the alias is created on those SQL 2000 servers running a 32-bit OS, it
works.
When the alias is created on those SQL 2000 servers running a 64-bit OS, it
doesn't.
Instead, when I try to test using isql, I get:
DB-Library: Unable to connect: SQL Server is unavailable or does not exist.
Una
ble to connect: SQL Server does not exist or network access denied.
Net-Library error 53: ConnectionOpen (Connect()).
Any ideas?
Thanks,
Tim CThere seems to be a subtle incompatibility issue. We have started moving our
SQL 2000 servers to 32-bit OS's on virtual machines to avoid the issue.
But if anyone knows of a simpler fix we could implement, I would love to
hear it.
Thanks,
Tim C
"Tim C" wrote:
> SQL 2005 SP2 on Windows Server 2003 SP2
> SQL 2000 SP4 on Windows Server 2003 SP2, with SQL 2005 Native Client installed
> Some of the servers running SQL 2000 are using a 64-bit OS, some are usiing
> 32-bit. All use 32-bit SQL 2000.
> Trying to create an Alias on a SQL 2000 machine to point to a named instance
> on a SQL 2005 machine through TCP/IP (for use in replication).
> When the alias is created on those SQL 2000 servers running a 32-bit OS, it
> works.
> When the alias is created on those SQL 2000 servers running a 64-bit OS, it
> doesn't.
> Instead, when I try to test using isql, I get:
> DB-Library: Unable to connect: SQL Server is unavailable or does not exist.
> Una
> ble to connect: SQL Server does not exist or network access denied.
> Net-Library error 53: ConnectionOpen (Connect()).
> Any ideas?
> Thanks,
> Tim C|||I recently had a discussion in .tools about this. Paul O'kasick was nice enough to share his
findings. Aparently there's both a 32 and a 64 bit version of cliconfg.exe and these modify
different registry keys. Here's a quote from Paul most recent reply:
"The alias is working on our test machine. It turns out there is a 64 bit
version of cliconfg.exe (C:\WINDOWS\SysWOW64\cliconfg.exe). As soon as I
created the alias with that version, the application started working. I
removed the alias created with the 32 bit version.
Each version maintains a separate list of aliases. The registry key for the
32 bit vs. 64 bit is
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo &
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo,
respectively."
So, my suggestion is that you try to create the alias with both 32 and 64 bit version of
cliconfg.exe to see which it is that is required in your particular case (probably depends on
whether the client app is 32 or 64 bit - meguess).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Tim C" <TimC@.discussions.microsoft.com> wrote in message
news:098FFFB2-D352-4B16-A32D-CEA42461787C@.microsoft.com...
> There seems to be a subtle incompatibility issue. We have started moving our
> SQL 2000 servers to 32-bit OS's on virtual machines to avoid the issue.
> But if anyone knows of a simpler fix we could implement, I would love to
> hear it.
> Thanks,
> Tim C
> "Tim C" wrote:
>> SQL 2005 SP2 on Windows Server 2003 SP2
>> SQL 2000 SP4 on Windows Server 2003 SP2, with SQL 2005 Native Client installed
>> Some of the servers running SQL 2000 are using a 64-bit OS, some are usiing
>> 32-bit. All use 32-bit SQL 2000.
>> Trying to create an Alias on a SQL 2000 machine to point to a named instance
>> on a SQL 2005 machine through TCP/IP (for use in replication).
>> When the alias is created on those SQL 2000 servers running a 32-bit OS, it
>> works.
>> When the alias is created on those SQL 2000 servers running a 64-bit OS, it
>> doesn't.
>> Instead, when I try to test using isql, I get:
>> DB-Library: Unable to connect: SQL Server is unavailable or does not exist.
>> Una
>> ble to connect: SQL Server does not exist or network access denied.
>> Net-Library error 53: ConnectionOpen (Connect()).
>> Any ideas?
>> Thanks,
>> Tim C
Alias not recognized
I'm attempting to refer to an alias in my SELECT clause (within a stored
procedure).
Basically I'm building a string which I will eventually execute by calling
the "exec" statement on my string.
Within my string, I have 3 columns for my SELECT clause. Here is an
extremely watered down example of what I'm referring to.
e.g.
SELECT (X.price * Y.units) AS alias_one, (W.price * Z.units) AS alias_two,
alias_one - alias_two
FROM W,X,Y,Z
The issue is that my select clause does NOT recognize "alias_one" and
"alias_two" as aliases when I call exec(myString).
Can I possibly refer to these columns by index within the sql or possibly
declare these aliases at the beginning of the procedure so that they will be
recognized?
Any help is appreciated.
PK9How do you refer the to alias in your stored procedure. If you post the
code, we might be able to help.
-oj
"PK9" <PK9@.discussions.microsoft.com> wrote in message
news:98AB1E92-EB82-4155-9DE5-51DEA95CAEEC@.microsoft.com...
> I'm attempting to refer to an alias in my SELECT clause (within a stored
> procedure).
> Basically I'm building a string which I will eventually execute by calling
> the "exec" statement on my string.
> Within my string, I have 3 columns for my SELECT clause. Here is an
> extremely watered down example of what I'm referring to.
> e.g.
> SELECT (X.price * Y.units) AS alias_one, (W.price * Z.units) AS alias_two,
> alias_one - alias_two
> FROM W,X,Y,Z
> The issue is that my select clause does NOT recognize "alias_one" and
> "alias_two" as aliases when I call exec(myString).
> Can I possibly refer to these columns by index within the sql or possibly
> declare these aliases at the beginning of the procedure so that they will
> be
> recognized?
> Any help is appreciated.
> --
> PK9|||This is dynamic SQL creation that uses a cross-tab/pivot, so I'm afraid
posting it may just confuse the issue.
What I end up with at the end of the SQL creation is the following:
'FY 1999' | 'FY 1999 CMP' as two separate columns that are given those
alias' in the stored procedure. Now, I want to say as another column' FY
1999 minus FY 1999 CMP' to give me the difference between the two columns.
Does that help?
"oj" wrote:
> How do you refer the to alias in your stored procedure. If you post the
> code, we might be able to help.
>
> --
> -oj
>
> "PK9" <PK9@.discussions.microsoft.com> wrote in message
> news:98AB1E92-EB82-4155-9DE5-51DEA95CAEEC@.microsoft.com...
>
>|||Can Anyone help me with this?
I'm really stuck right now.
"oj" wrote:
> How do you refer the to alias in your stored procedure. If you post the
> code, we might be able to help.
>
> --
> -oj
>
> "PK9" <PK9@.discussions.microsoft.com> wrote in message
> news:98AB1E92-EB82-4155-9DE5-51DEA95CAEEC@.microsoft.com...
>
>|||You are trying to do something like this:
SELECT (X.price * Y.units) AS alias_one, (W.price * Z.units) AS alias_two,
alias_one - alias_two
FROM W,X,Y,Z
This isn't possible in the SQL language. The whole select list happens at th
e same time, logically.
Here's one way with which you don't have to repeat the expressions:
SELECT alias_one, alias_two, alias_one - alias_two
FROM
(
SELECT (X.price * Y.units) AS alias_one, (W.price * Z.units) AS alias_two,
FROM W,X,Y,Z
) AS d
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"PK9" <PK9@.discussions.microsoft.com> wrote in message
news:98AB1E92-EB82-4155-9DE5-51DEA95CAEEC@.microsoft.com...
> I'm attempting to refer to an alias in my SELECT clause (within a stored
> procedure).
> Basically I'm building a string which I will eventually execute by calling
> the "exec" statement on my string.
> Within my string, I have 3 columns for my SELECT clause. Here is an
> extremely watered down example of what I'm referring to.
> e.g.
> SELECT (X.price * Y.units) AS alias_one, (W.price * Z.units) AS alias_two,
> alias_one - alias_two
> FROM W,X,Y,Z
> The issue is that my select clause does NOT recognize "alias_one" and
> "alias_two" as aliases when I call exec(myString).
> Can I possibly refer to these columns by index within the sql or possibly
> declare these aliases at the beginning of the procedure so that they will
be
> recognized?
> Any help is appreciated.
> --
> PK9|||dynamic sql for xtab. hmmm...that sounds quite familiar. wait, we have such
a commercial solution (http://rac4sql.net) :-)
anyway, you cannot just add the two aliases because the aliases are
calculated at runtime. what you can do is to derive the first query then use
the aliases. take a look at tibor's comment.
-oj
"PK9" <PK9@.discussions.microsoft.com> wrote in message
news:FD1A0770-2736-49B8-B684-16AE1927EF92@.microsoft.com...
> This is dynamic SQL creation that uses a cross-tab/pivot, so I'm afraid
> posting it may just confuse the issue.
> What I end up with at the end of the SQL creation is the following:
> 'FY 1999' | 'FY 1999 CMP' as two separate columns that are given those
> alias' in the stored procedure. Now, I want to say as another column' FY
> 1999 minus FY 1999 CMP' to give me the difference between the two columns.
> Does that help?
> "oj" wrote:
>|||Yoiu missed some basic ideas in SQL. Here is how a SELECT works in SQL
... at least in theory. Real products will optimize things, but the
code has to produce the same results.
a) Start in the FROM clause and build a working table from all of the
joins, unions, intersections, and whatever other table constructors are
there. The table expression> AS <correlation name> option allows you
give a name to this working table which you then have to use for the
rest of the containing query.
b) Go to the WHERE clause and remove rows that do not pass criteria;
that is, that do not test to TRUE (i.e. reject UNKNOWN and FALSE). The
WHERE clause is applied to the working set in the FROM clause.
c) Go to the optional GROUP BY clause, make groups and reduce each
group to a single row, replacing the original working table with the
new grouped table. The rows of a grouped table must be group
characteristics: (1) a grouping column (2) a statistic about the group
(i.e. aggregate functions) (3) a function or (4) an expression made up
those three items.
d) Go to the optional HAVING clause and apply it against the grouped
working table; if there was no GROUP BY clause, treat the entire table
as one group.
e) Go to the SELECT clause and construct the expressions in the list.
This means that the scalar subqueries, function calls and expressions
in the SELECT are done after all the other clauses are done. The
"AS" operator can also give names to expressions in the SELECT
list. These new names come into existence all at once, but after the
WHERE clause, GROUP BY clause and HAVING clause has been executed; you
cannot use them in the SELECT list or the WHERE clause for that reason.
If there is a SELECT DISTINCT, then redundant duplicate rows are
removed. For purposes of defining a duplicate row, NULLs are treated
as matching (just like in the GROUP BY).
f) Nested query expressions follow the usual scoping rules you would
expect from a block structured language like C, Pascal, Algol, etc.
Namely, the innermost queries can reference columns and tables in the
queries in which they are contained.
g) The ORDER BY clause is part of a cursor, not a query. The result
set is passed to the cursor, which can only see the names in the SELECT
clause list, and the sorting is done there. The ORDER BY clause cannot
have expression in it, or references to other columns because the
result set has been converted into a sequential file structure and that
is what is being sorted.
As you can see, things happen "all at once" in SQL, not "from left to
right" as they would in a sequential file/procedural language model. In
those languages, these two statements produce different results:
READ (a, b, c) FROM File_X;
READ (c, a, b) FROM File_X;
while these two statements return the same data:
SELECT a, b, c FROM Table_X;
SELECT c, a, b FROM Table_X;
Think about what a
mess this statement is in the SQL model.
SELECT f(c2) AS c1, f(c1) AS c2 FROM Foobar;
That is why such nonsense is illegal syntax.
And dynamic SQL is considered bad programming, not quite as bad as
cursors, but still not the way to do it.|||Thanks, that's exactly what I was looking for!
"Tibor Karaszi" wrote:
> You are trying to do something like this:
> SELECT (X.price * Y.units) AS alias_one, (W.price * Z.units) AS alias_two,
> alias_one - alias_two
> FROM W,X,Y,Z
>
> This isn't possible in the SQL language. The whole select list happens at
the same time, logically.
> Here's one way with which you don't have to repeat the expressions:
> SELECT alias_one, alias_two, alias_one - alias_two
> FROM
> (
> SELECT (X.price * Y.units) AS alias_one, (W.price * Z.units) AS alias_two,
> FROM W,X,Y,Z
> ) AS d
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "PK9" <PK9@.discussions.microsoft.com> wrote in message
> news:98AB1E92-EB82-4155-9DE5-51DEA95CAEEC@.microsoft.com...
>
>sql
procedure).
Basically I'm building a string which I will eventually execute by calling
the "exec" statement on my string.
Within my string, I have 3 columns for my SELECT clause. Here is an
extremely watered down example of what I'm referring to.
e.g.
SELECT (X.price * Y.units) AS alias_one, (W.price * Z.units) AS alias_two,
alias_one - alias_two
FROM W,X,Y,Z
The issue is that my select clause does NOT recognize "alias_one" and
"alias_two" as aliases when I call exec(myString).
Can I possibly refer to these columns by index within the sql or possibly
declare these aliases at the beginning of the procedure so that they will be
recognized?
Any help is appreciated.
PK9How do you refer the to alias in your stored procedure. If you post the
code, we might be able to help.
-oj
"PK9" <PK9@.discussions.microsoft.com> wrote in message
news:98AB1E92-EB82-4155-9DE5-51DEA95CAEEC@.microsoft.com...
> I'm attempting to refer to an alias in my SELECT clause (within a stored
> procedure).
> Basically I'm building a string which I will eventually execute by calling
> the "exec" statement on my string.
> Within my string, I have 3 columns for my SELECT clause. Here is an
> extremely watered down example of what I'm referring to.
> e.g.
> SELECT (X.price * Y.units) AS alias_one, (W.price * Z.units) AS alias_two,
> alias_one - alias_two
> FROM W,X,Y,Z
> The issue is that my select clause does NOT recognize "alias_one" and
> "alias_two" as aliases when I call exec(myString).
> Can I possibly refer to these columns by index within the sql or possibly
> declare these aliases at the beginning of the procedure so that they will
> be
> recognized?
> Any help is appreciated.
> --
> PK9|||This is dynamic SQL creation that uses a cross-tab/pivot, so I'm afraid
posting it may just confuse the issue.
What I end up with at the end of the SQL creation is the following:
'FY 1999' | 'FY 1999 CMP' as two separate columns that are given those
alias' in the stored procedure. Now, I want to say as another column' FY
1999 minus FY 1999 CMP' to give me the difference between the two columns.
Does that help?
"oj" wrote:
> How do you refer the to alias in your stored procedure. If you post the
> code, we might be able to help.
>
> --
> -oj
>
> "PK9" <PK9@.discussions.microsoft.com> wrote in message
> news:98AB1E92-EB82-4155-9DE5-51DEA95CAEEC@.microsoft.com...
>
>|||Can Anyone help me with this?
I'm really stuck right now.
"oj" wrote:
> How do you refer the to alias in your stored procedure. If you post the
> code, we might be able to help.
>
> --
> -oj
>
> "PK9" <PK9@.discussions.microsoft.com> wrote in message
> news:98AB1E92-EB82-4155-9DE5-51DEA95CAEEC@.microsoft.com...
>
>|||You are trying to do something like this:
SELECT (X.price * Y.units) AS alias_one, (W.price * Z.units) AS alias_two,
alias_one - alias_two
FROM W,X,Y,Z
This isn't possible in the SQL language. The whole select list happens at th
e same time, logically.
Here's one way with which you don't have to repeat the expressions:
SELECT alias_one, alias_two, alias_one - alias_two
FROM
(
SELECT (X.price * Y.units) AS alias_one, (W.price * Z.units) AS alias_two,
FROM W,X,Y,Z
) AS d
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"PK9" <PK9@.discussions.microsoft.com> wrote in message
news:98AB1E92-EB82-4155-9DE5-51DEA95CAEEC@.microsoft.com...
> I'm attempting to refer to an alias in my SELECT clause (within a stored
> procedure).
> Basically I'm building a string which I will eventually execute by calling
> the "exec" statement on my string.
> Within my string, I have 3 columns for my SELECT clause. Here is an
> extremely watered down example of what I'm referring to.
> e.g.
> SELECT (X.price * Y.units) AS alias_one, (W.price * Z.units) AS alias_two,
> alias_one - alias_two
> FROM W,X,Y,Z
> The issue is that my select clause does NOT recognize "alias_one" and
> "alias_two" as aliases when I call exec(myString).
> Can I possibly refer to these columns by index within the sql or possibly
> declare these aliases at the beginning of the procedure so that they will
be
> recognized?
> Any help is appreciated.
> --
> PK9|||dynamic sql for xtab. hmmm...that sounds quite familiar. wait, we have such
a commercial solution (http://rac4sql.net) :-)
anyway, you cannot just add the two aliases because the aliases are
calculated at runtime. what you can do is to derive the first query then use
the aliases. take a look at tibor's comment.
-oj
"PK9" <PK9@.discussions.microsoft.com> wrote in message
news:FD1A0770-2736-49B8-B684-16AE1927EF92@.microsoft.com...
> This is dynamic SQL creation that uses a cross-tab/pivot, so I'm afraid
> posting it may just confuse the issue.
> What I end up with at the end of the SQL creation is the following:
> 'FY 1999' | 'FY 1999 CMP' as two separate columns that are given those
> alias' in the stored procedure. Now, I want to say as another column' FY
> 1999 minus FY 1999 CMP' to give me the difference between the two columns.
> Does that help?
> "oj" wrote:
>|||Yoiu missed some basic ideas in SQL. Here is how a SELECT works in SQL
... at least in theory. Real products will optimize things, but the
code has to produce the same results.
a) Start in the FROM clause and build a working table from all of the
joins, unions, intersections, and whatever other table constructors are
there. The table expression> AS <correlation name> option allows you
give a name to this working table which you then have to use for the
rest of the containing query.
b) Go to the WHERE clause and remove rows that do not pass criteria;
that is, that do not test to TRUE (i.e. reject UNKNOWN and FALSE). The
WHERE clause is applied to the working set in the FROM clause.
c) Go to the optional GROUP BY clause, make groups and reduce each
group to a single row, replacing the original working table with the
new grouped table. The rows of a grouped table must be group
characteristics: (1) a grouping column (2) a statistic about the group
(i.e. aggregate functions) (3) a function or (4) an expression made up
those three items.
d) Go to the optional HAVING clause and apply it against the grouped
working table; if there was no GROUP BY clause, treat the entire table
as one group.
e) Go to the SELECT clause and construct the expressions in the list.
This means that the scalar subqueries, function calls and expressions
in the SELECT are done after all the other clauses are done. The
"AS" operator can also give names to expressions in the SELECT
list. These new names come into existence all at once, but after the
WHERE clause, GROUP BY clause and HAVING clause has been executed; you
cannot use them in the SELECT list or the WHERE clause for that reason.
If there is a SELECT DISTINCT, then redundant duplicate rows are
removed. For purposes of defining a duplicate row, NULLs are treated
as matching (just like in the GROUP BY).
f) Nested query expressions follow the usual scoping rules you would
expect from a block structured language like C, Pascal, Algol, etc.
Namely, the innermost queries can reference columns and tables in the
queries in which they are contained.
g) The ORDER BY clause is part of a cursor, not a query. The result
set is passed to the cursor, which can only see the names in the SELECT
clause list, and the sorting is done there. The ORDER BY clause cannot
have expression in it, or references to other columns because the
result set has been converted into a sequential file structure and that
is what is being sorted.
As you can see, things happen "all at once" in SQL, not "from left to
right" as they would in a sequential file/procedural language model. In
those languages, these two statements produce different results:
READ (a, b, c) FROM File_X;
READ (c, a, b) FROM File_X;
while these two statements return the same data:
SELECT a, b, c FROM Table_X;
SELECT c, a, b FROM Table_X;
Think about what a
mess this statement is in the SQL model.SELECT f(c2) AS c1, f(c1) AS c2 FROM Foobar;
That is why such nonsense is illegal syntax.
And dynamic SQL is considered bad programming, not quite as bad as
cursors, but still not the way to do it.|||Thanks, that's exactly what I was looking for!
"Tibor Karaszi" wrote:
> You are trying to do something like this:
> SELECT (X.price * Y.units) AS alias_one, (W.price * Z.units) AS alias_two,
> alias_one - alias_two
> FROM W,X,Y,Z
>
> This isn't possible in the SQL language. The whole select list happens at
the same time, logically.
> Here's one way with which you don't have to repeat the expressions:
> SELECT alias_one, alias_two, alias_one - alias_two
> FROM
> (
> SELECT (X.price * Y.units) AS alias_one, (W.price * Z.units) AS alias_two,
> FROM W,X,Y,Z
> ) AS d
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "PK9" <PK9@.discussions.microsoft.com> wrote in message
> news:98AB1E92-EB82-4155-9DE5-51DEA95CAEEC@.microsoft.com...
>
>sql
alias naming
Hi,
I want help regarding the following scenario...
My scenario is ...
I want to give description for each field somewhere in the database..
such that i should use that description as alias name in the SQL queries or Stored procedures...
For example...
Table : 'Table1'
Clumns Description
Field1 xxx
Field2 yyy
Query : Select Field1 as xxx , Field2 as yyy from Table1.
Mr requirement...
1.I want to specify tag or description for each field
2. if i change the description 'xxx' as 'x1x1x1',it should be automatically updated in the Query,Views,Storedproc.. wherever the table and the fields are referred.
can any one help me?.If you are storing alias name for columns,
you have to use dynamic sql, to get the results.
I think, your requirement is so complicated to implement. :D
What advantages you will get, if you implement it..? :rolleyes:
Regards,
Selva Balaji. B
Originally posted by durgadevi_n
Hi,
I want help regarding the following scenario...
My scenario is ...
I want to give description for each field somewhere in the database..
such that i should use that description as alias name in the SQL queries or Stored procedures...
For example...
Table : 'Table1'
Clumns Description
Field1 xxx
Field2 yyy
Query : Select Field1 as xxx , Field2 as yyy from Table1.
Mr requirement...
1.I want to specify tag or description for each field
2. if i change the description 'xxx' as 'x1x1x1',it should be automatically updated in the Query,Views,Storedproc.. wherever the table and the fields are referred.
can any one help me?. :D :D :D|||There isn't such a functionality within the database. Using the standard tools like Enterprise Manager or Query Analyzer, you have to use the physical names, and to assign the logical names every time again.
You may consider to make a view for each table assigning your logical names.
Client tools, however, can replace the physical names by logical ones in the user interface. Look, for example, the DB Explorer (http://www.DB-Explorer.com).|||Hi,
Actually in my application...
I am creating more stored procedures based on a single table...(database already used by another application).
Since i can't change the field names, i am making use of alias name for the required fields in Stored procedures and binding tha data in the front end where the alias name gets displayed.
I've more than 500 stored procedures in my database.
If I want to change a field caption ,
I cannot change the existing field's name since it is already used by other application.
Also It is very hard to find out and change the alias name in each and every stored procedure wherever it is referenced.
So i am trying to look in other chances...to reflect the change in every stored procedure with a single move.
Is it possible?...
plz help me...
bye
by
durga|||And what about using views? You may consider not to assign your alias within every stored proc, but once in a view definition. If you change aliases in a view, your stored proc will return the changed name, assuming that you are working with SELECT * statements. This is consistent for all stored proc based on a particular view.|||You may want to have a look at extended properties
(sp_addextendedproperty, sp_updateextendedproperty, sp_dropextendedproperty).
It will not let you use the alias'es directly, but you do not have to come up with tables/functions etc to utilize them.
To use them you must (by code if you can, use syscomments or sqldmo) regenerate alll ddl's wher ethey are referenced (sysdepends, sysreferences).
not a small task...
Originally posted by durgadevi_n
Hi,
I want help regarding the following scenario...
My scenario is ...
I want to give description for each field somewhere in the database..
such that i should use that description as alias name in the SQL queries or Stored procedures...
For example...
Table : 'Table1'
Clumns Description
Field1 xxx
Field2 yyy
Query : Select Field1 as xxx , Field2 as yyy from Table1.
Mr requirement...
1.I want to specify tag or description for each field
2. if i change the description 'xxx' as 'x1x1x1',it should be automatically updated in the Query,Views,Storedproc.. wherever the table and the fields are referred.
can any one help me?.
I want help regarding the following scenario...
My scenario is ...
I want to give description for each field somewhere in the database..
such that i should use that description as alias name in the SQL queries or Stored procedures...
For example...
Table : 'Table1'
Clumns Description
Field1 xxx
Field2 yyy
Query : Select Field1 as xxx , Field2 as yyy from Table1.
Mr requirement...
1.I want to specify tag or description for each field
2. if i change the description 'xxx' as 'x1x1x1',it should be automatically updated in the Query,Views,Storedproc.. wherever the table and the fields are referred.
can any one help me?.If you are storing alias name for columns,
you have to use dynamic sql, to get the results.
I think, your requirement is so complicated to implement. :D
What advantages you will get, if you implement it..? :rolleyes:
Regards,
Selva Balaji. B
Originally posted by durgadevi_n
Hi,
I want help regarding the following scenario...
My scenario is ...
I want to give description for each field somewhere in the database..
such that i should use that description as alias name in the SQL queries or Stored procedures...
For example...
Table : 'Table1'
Clumns Description
Field1 xxx
Field2 yyy
Query : Select Field1 as xxx , Field2 as yyy from Table1.
Mr requirement...
1.I want to specify tag or description for each field
2. if i change the description 'xxx' as 'x1x1x1',it should be automatically updated in the Query,Views,Storedproc.. wherever the table and the fields are referred.
can any one help me?. :D :D :D|||There isn't such a functionality within the database. Using the standard tools like Enterprise Manager or Query Analyzer, you have to use the physical names, and to assign the logical names every time again.
You may consider to make a view for each table assigning your logical names.
Client tools, however, can replace the physical names by logical ones in the user interface. Look, for example, the DB Explorer (http://www.DB-Explorer.com).|||Hi,
Actually in my application...
I am creating more stored procedures based on a single table...(database already used by another application).
Since i can't change the field names, i am making use of alias name for the required fields in Stored procedures and binding tha data in the front end where the alias name gets displayed.
I've more than 500 stored procedures in my database.
If I want to change a field caption ,
I cannot change the existing field's name since it is already used by other application.
Also It is very hard to find out and change the alias name in each and every stored procedure wherever it is referenced.
So i am trying to look in other chances...to reflect the change in every stored procedure with a single move.
Is it possible?...
plz help me...
bye
by
durga|||And what about using views? You may consider not to assign your alias within every stored proc, but once in a view definition. If you change aliases in a view, your stored proc will return the changed name, assuming that you are working with SELECT * statements. This is consistent for all stored proc based on a particular view.|||You may want to have a look at extended properties
(sp_addextendedproperty, sp_updateextendedproperty, sp_dropextendedproperty).
It will not let you use the alias'es directly, but you do not have to come up with tables/functions etc to utilize them.
To use them you must (by code if you can, use syscomments or sqldmo) regenerate alll ddl's wher ethey are referenced (sysdepends, sysreferences).
not a small task...
Originally posted by durgadevi_n
Hi,
I want help regarding the following scenario...
My scenario is ...
I want to give description for each field somewhere in the database..
such that i should use that description as alias name in the SQL queries or Stored procedures...
For example...
Table : 'Table1'
Clumns Description
Field1 xxx
Field2 yyy
Query : Select Field1 as xxx , Field2 as yyy from Table1.
Mr requirement...
1.I want to specify tag or description for each field
2. if i change the description 'xxx' as 'x1x1x1',it should be automatically updated in the Query,Views,Storedproc.. wherever the table and the fields are referred.
can any one help me?.
Alias name for addressing SQL databases
I have a number of MS Access apps that link to SQL Sever tables. If
the SQL Sever database is moved to another server, or the server is
replaced, the unc address to the database changes as the server name
has changed. All my apps fall over as the linked tables are addressed
using the server name.
the SQL Sever database is moved to another server, or the server is
replaced, the unc address to the database changes as the server name
has changed. All my apps fall over as the linked tables are addressed
using the server name.
Is there a way to set up an alias name for an SQL database so that you
can link to the tables through an ODBC connection without using the
server name.
Thanks in advanceYou can setup an alias for the server using the SQL Server Client Network
Utility. You can also do this while setting up an ODBC DSN.
I'm not sure I fully understand your situation, though. If you've linked
SQL Server tables using an ODBC DSN, only the DSN is stored in your
connection string. Moving/renaming the server shouldn't require changes to
your app but you'll still need to change the server name on each client
using the Client Network Utility or ODBC DSN Administrator.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Grant Hammond" <techworks@.clear.net.nz> wrote in message
news:5e784f92.0405111506.2672be22@.posting.google.c om...
> I have a number of MS Access apps that link to SQL Sever tables. If
> the SQL Sever database is moved to another server, or the server is
> replaced, the unc address to the database changes as the server name
> has changed. All my apps fall over as the linked tables are addressed
> using the server name.
> Is there a way to set up an alias name for an SQL database so that you
> can link to the tables through an ODBC connection without using the
> server name.
> Thanks in advance
Alias in the DNS.
Hi,
I have two different server with the same SQL instance
with the same databases on
SERVER1\NODE1 and SERVER2\NODE1, the goal is to be able
to switch from SERVER1 to SERVER2 without causing users
problems.
We thought to add in the DNS server the IP adresses of
the server1 and giving the TEST ...but that's not work
because the ip adress alone is not realy associate with
the SQL Instance. And we can't put in DSN an IP adress
with the \NODE1.
So, someone can help me to put on this full system
availability strategy '
Thanks...The client software can specify the IP address and instance name
instead of the computer name and instance name. Ex:
192.x.x.x\<ABCInstance>.
Lou Arnold
Ottawa.
On Thu, 22 Jul 2004 06:29:12 -0700, "Pierre"
<anonymous@.discussions.microsoft.com> wrote:
>Hi,
>I have two different server with the same SQL instance
>with the same databases on
>SERVER1\NODE1 and SERVER2\NODE1, the goal is to be able
>to switch from SERVER1 to SERVER2 without causing users
>problems.
>We thought to add in the DNS server the IP adresses of
>the server1 and giving the TEST ...but that's not work
>because the ip adress alone is not realy associate with
>the SQL Instance. And we can't put in DSN an IP adress
>with the \NODE1.
>So, someone can help me to put on this full system
>availability strategy '
>Thanks...|||Yes i know that Lou but it's not our goal to past all the
300 user's computers to change the client network alias
ip adresse.
We use actualy an alias name for a server with a default
installation of SQL Server .. and that's work good on
because the ip adress in the DNS is the same name than
the SQL Server name. And we can easyly change the ip
adress into the DNS Server and we don't have to change
300 odbc connections...
>--Original Message--
>The client software can specify the IP address and
instance name
>instead of the computer name and instance name. Ex:
>192.x.x.x\<ABCInstance>.
>Lou Arnold
>Ottawa.
>On Thu, 22 Jul 2004 06:29:12 -0700, "Pierre"
><anonymous@.discussions.microsoft.com> wrote:
>
with[vbcol=seagreen]
>.
>|||Ok. You're right. Sorry I can't help further.
Lou.
On Thu, 22 Jul 2004 11:03:25 -0700,
<anonymous@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Yes i know that Lou but it's not our goal to past all the
>300 user's computers to change the client network alias
>ip adresse.
>We use actualy an alias name for a server with a default
>installation of SQL Server .. and that's work good on
>because the ip adress in the DNS is the same name than
>the SQL Server name. And we can easyly change the ip
>adress into the DNS Server and we don't have to change
>300 odbc connections...
>
>instance name
>with|||Hi Pierre,
Are the instances in your example on a SQL Cluster or StandAlone?
Are you using 1433 as the port or other?
When your clients connect to the Server do the specify Server\InstancName
or just Server?
The reason I ask is that MDAC has some rules in connections to Named
Instances.
You must use either :
1. Server\InstanceName
or
2. Server, port
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Hi Kevin,
That's a standalone servers. and actually the clients are
using SERVER1\NODE1 ( port:1435 ) ... and I want to be
able to switch easily to SERVER2\NODE1 ( port:1435 ).
Actually we use an alias in th DNS for a SQL Server (
just server like you wrote ) and that's work very well
because ... the ODBC's client are configured with an
alias named ALIAS1 whose got the SERVER1 IP Adress in the
DNS ... When we want to switch, we just change the IP
Adress of the alias ALIAS1 in DNS and that's it ... the
clients use the SERVER2 without causing connectivity
problem ...
But with Instancename we can't use this strategy but we
would like because we think it's a good way to keep alive
an application 24 hours a days ...
Thanks Again ..
>--Original Message--
>Hi Pierre,
>Are the instances in your example on a SQL Cluster or
StandAlone?
>Are you using 1433 as the port or other?
>When your clients connect to the Server do the specify
Server\InstancName
>or just Server?
>The reason I ask is that MDAC has some rules in
connections to Named
>Instances.
>You must use either :
> 1. Server\InstanceName
> or
> 2. Server, port
>
>Thanks,
>Kevin McDonnell
>Microsoft Corporation
>This posting is provided AS IS with no warranties, and
confers no rights.
>
>.
>|||OK. So, if the client has an alias configure to use Server1,1435 (Using
the SQL Client Network Util) then does this scenario work?
I think it will as long as you've created an alias on each client to
reference the Servername and port. Port being an
important factor.
Since DNS knows nothing about ports or instances, this is the only way i
think it will work. Otherwise, the traffic will
default to 1433 and the clients fail to connect.
Let me know if this makes sense.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
I have two different server with the same SQL instance
with the same databases on
SERVER1\NODE1 and SERVER2\NODE1, the goal is to be able
to switch from SERVER1 to SERVER2 without causing users
problems.
We thought to add in the DNS server the IP adresses of
the server1 and giving the TEST ...but that's not work
because the ip adress alone is not realy associate with
the SQL Instance. And we can't put in DSN an IP adress
with the \NODE1.
So, someone can help me to put on this full system
availability strategy '
Thanks...The client software can specify the IP address and instance name
instead of the computer name and instance name. Ex:
192.x.x.x\<ABCInstance>.
Lou Arnold
Ottawa.
On Thu, 22 Jul 2004 06:29:12 -0700, "Pierre"
<anonymous@.discussions.microsoft.com> wrote:
>Hi,
>I have two different server with the same SQL instance
>with the same databases on
>SERVER1\NODE1 and SERVER2\NODE1, the goal is to be able
>to switch from SERVER1 to SERVER2 without causing users
>problems.
>We thought to add in the DNS server the IP adresses of
>the server1 and giving the TEST ...but that's not work
>because the ip adress alone is not realy associate with
>the SQL Instance. And we can't put in DSN an IP adress
>with the \NODE1.
>So, someone can help me to put on this full system
>availability strategy '
>Thanks...|||Yes i know that Lou but it's not our goal to past all the
300 user's computers to change the client network alias
ip adresse.
We use actualy an alias name for a server with a default
installation of SQL Server .. and that's work good on
because the ip adress in the DNS is the same name than
the SQL Server name. And we can easyly change the ip
adress into the DNS Server and we don't have to change
300 odbc connections...
>--Original Message--
>The client software can specify the IP address and
instance name
>instead of the computer name and instance name. Ex:
>192.x.x.x\<ABCInstance>.
>Lou Arnold
>Ottawa.
>On Thu, 22 Jul 2004 06:29:12 -0700, "Pierre"
><anonymous@.discussions.microsoft.com> wrote:
>
with[vbcol=seagreen]
>.
>|||Ok. You're right. Sorry I can't help further.
Lou.
On Thu, 22 Jul 2004 11:03:25 -0700,
<anonymous@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Yes i know that Lou but it's not our goal to past all the
>300 user's computers to change the client network alias
>ip adresse.
>We use actualy an alias name for a server with a default
>installation of SQL Server .. and that's work good on
>because the ip adress in the DNS is the same name than
>the SQL Server name. And we can easyly change the ip
>adress into the DNS Server and we don't have to change
>300 odbc connections...
>
>instance name
>with|||Hi Pierre,
Are the instances in your example on a SQL Cluster or StandAlone?
Are you using 1433 as the port or other?
When your clients connect to the Server do the specify Server\InstancName
or just Server?
The reason I ask is that MDAC has some rules in connections to Named
Instances.
You must use either :
1. Server\InstanceName
or
2. Server, port
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Hi Kevin,
That's a standalone servers. and actually the clients are
using SERVER1\NODE1 ( port:1435 ) ... and I want to be
able to switch easily to SERVER2\NODE1 ( port:1435 ).
Actually we use an alias in th DNS for a SQL Server (
just server like you wrote ) and that's work very well
because ... the ODBC's client are configured with an
alias named ALIAS1 whose got the SERVER1 IP Adress in the
DNS ... When we want to switch, we just change the IP
Adress of the alias ALIAS1 in DNS and that's it ... the
clients use the SERVER2 without causing connectivity
problem ...
But with Instancename we can't use this strategy but we
would like because we think it's a good way to keep alive
an application 24 hours a days ...
Thanks Again ..
>--Original Message--
>Hi Pierre,
>Are the instances in your example on a SQL Cluster or
StandAlone?
>Are you using 1433 as the port or other?
>When your clients connect to the Server do the specify
Server\InstancName
>or just Server?
>The reason I ask is that MDAC has some rules in
connections to Named
>Instances.
>You must use either :
> 1. Server\InstanceName
> or
> 2. Server, port
>
>Thanks,
>Kevin McDonnell
>Microsoft Corporation
>This posting is provided AS IS with no warranties, and
confers no rights.
>
>.
>|||OK. So, if the client has an alias configure to use Server1,1435 (Using
the SQL Client Network Util) then does this scenario work?
I think it will as long as you've created an alias on each client to
reference the Servername and port. Port being an
important factor.
Since DNS knows nothing about ports or instances, this is the only way i
think it will work. Otherwise, the traffic will
default to 1433 and the clients fail to connect.
Let me know if this makes sense.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
Subscribe to:
Posts (Atom)