There's no explaination of this error on MS or any of the documentation that
I can find. Yes, I know some of the cases that cause it, but that's a
different issue entirely.
I'm getting this error on a new SQL Server install. All of the permissions
are copied from the older server. About 2/3rds of the machines in the office
can log in fine, but the other 1/3rd return this error. There is no obvious
difference between the machines. We use Windows Authentication for everythin
g.
So, does this error mean "I cannot verify you with the domain controller"?
Or perhaps "your domain credentials do no allow you to log in"? Or something
else entirely? Depending on the exact nature of the error, the steps to
correct it will be very different.This part of the error message mean that there is no trust (or the trust
cannot be confirmed) between the account thatt is logging in and the SQL
Server machine. The first part of the message is the most important. What
is the complete message that the users are getting?
Rand
This posting is provided "as is" with no warranties and confers no rights.|||"Rand Boyd [MSFT]" wrote:
> Server machine. The first part of the message is the most important. What
> is the complete message that the users are getting?
SQLState '28000'
SQL Server Error 18452
[...] Login failed for user '(null)'. Reason: not associated with a trus
ted
SQL Server connection.
Thanks!|||Are you using a .NET application?
Sasan Saidi,
MSc in CS, MCSE (NT4), IBM Certified MQ Administrator
Senior DBA
Brascan Business Services
"Maury Markowitz" wrote:
> "Rand Boyd [MSFT]" wrote:
>
> SQLState '28000'
> SQL Server Error 18452
> [...] Login failed for user '(null)'. Reason: not associated with a tr
usted
> SQL Server connection.
> Thanks!
>|||"Sasan Saidi" wrote:
> Are you using a .NET application?
No. I'm simply trying to set up the DSN in the Data Sources (ODBC) control
panel.|||Has the user logged onto the domain? Does the user account have an
associated SQL login?
Jeff
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:F095B40F-78FB-4096-89CA-7E9E1F9E0580@.microsoft.com...
> "Sasan Saidi" wrote:
>
> No. I'm simply trying to set up the DSN in the Data Sources (ODBC) control
> panel.|||My guess is that the SQL Server is setup to allow only Windows logins and yo
u try to login using a
SQL server login.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in messag
e
news:BAD981FB-7BB8-46BB-A297-80DE6CF7B543@.microsoft.com...
> "Rand Boyd [MSFT]" wrote:
>
> SQLState '28000'
> SQL Server Error 18452
> [...] Login failed for user '(null)'. Reason: not associated with a tr
usted
> SQL Server connection.
> Thanks!
>
Saturday, February 25, 2012
"not a trusted connection", means what EXACTLY?
There's no explaination of this error on MS or any of the documentation that
I can find. Yes, I know some of the cases that cause it, but that's a
different issue entirely.
I'm getting this error on a new SQL Server install. All of the permissions
are copied from the older server. About 2/3rds of the machines in the office
can log in fine, but the other 1/3rd return this error. There is no obvious
difference between the machines. We use Windows Authentication for everything.
So, does this error mean "I cannot verify you with the domain controller"?
Or perhaps "your domain credentials do no allow you to log in"? Or something
else entirely? Depending on the exact nature of the error, the steps to
correct it will be very different.This part of the error message mean that there is no trust (or the trust
cannot be confirmed) between the account thatt is logging in and the SQL
Server machine. The first part of the message is the most important. What
is the complete message that the users are getting?
Rand
This posting is provided "as is" with no warranties and confers no rights.|||"Rand Boyd [MSFT]" wrote:
> Server machine. The first part of the message is the most important. What
> is the complete message that the users are getting?
SQLState '28000'
SQL Server Error 18452
[...] Login failed for user '(null)'. Reason: not associated with a trusted
SQL Server connection.
Thanks!|||Are you using a .NET application?
--
Sasan Saidi,
MSc in CS, MCSE (NT4), IBM Certified MQ Administrator
Senior DBA
Brascan Business Services
"Maury Markowitz" wrote:
> "Rand Boyd [MSFT]" wrote:
> > Server machine. The first part of the message is the most important. What
> > is the complete message that the users are getting?
> SQLState '28000'
> SQL Server Error 18452
> [...] Login failed for user '(null)'. Reason: not associated with a trusted
> SQL Server connection.
> Thanks!
>|||"Sasan Saidi" wrote:
> Are you using a .NET application?
No. I'm simply trying to set up the DSN in the Data Sources (ODBC) control
panel.|||Has the user logged onto the domain? Does the user account have an
associated SQL login?
Jeff
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:F095B40F-78FB-4096-89CA-7E9E1F9E0580@.microsoft.com...
> "Sasan Saidi" wrote:
> > Are you using a .NET application?
> No. I'm simply trying to set up the DSN in the Data Sources (ODBC) control
> panel.|||My guess is that the SQL Server is setup to allow only Windows logins and you try to login using a
SQL server login.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in message
news:BAD981FB-7BB8-46BB-A297-80DE6CF7B543@.microsoft.com...
> "Rand Boyd [MSFT]" wrote:
> > Server machine. The first part of the message is the most important. What
> > is the complete message that the users are getting?
> SQLState '28000'
> SQL Server Error 18452
> [...] Login failed for user '(null)'. Reason: not associated with a trusted
> SQL Server connection.
> Thanks!
>
I can find. Yes, I know some of the cases that cause it, but that's a
different issue entirely.
I'm getting this error on a new SQL Server install. All of the permissions
are copied from the older server. About 2/3rds of the machines in the office
can log in fine, but the other 1/3rd return this error. There is no obvious
difference between the machines. We use Windows Authentication for everything.
So, does this error mean "I cannot verify you with the domain controller"?
Or perhaps "your domain credentials do no allow you to log in"? Or something
else entirely? Depending on the exact nature of the error, the steps to
correct it will be very different.This part of the error message mean that there is no trust (or the trust
cannot be confirmed) between the account thatt is logging in and the SQL
Server machine. The first part of the message is the most important. What
is the complete message that the users are getting?
Rand
This posting is provided "as is" with no warranties and confers no rights.|||"Rand Boyd [MSFT]" wrote:
> Server machine. The first part of the message is the most important. What
> is the complete message that the users are getting?
SQLState '28000'
SQL Server Error 18452
[...] Login failed for user '(null)'. Reason: not associated with a trusted
SQL Server connection.
Thanks!|||Are you using a .NET application?
--
Sasan Saidi,
MSc in CS, MCSE (NT4), IBM Certified MQ Administrator
Senior DBA
Brascan Business Services
"Maury Markowitz" wrote:
> "Rand Boyd [MSFT]" wrote:
> > Server machine. The first part of the message is the most important. What
> > is the complete message that the users are getting?
> SQLState '28000'
> SQL Server Error 18452
> [...] Login failed for user '(null)'. Reason: not associated with a trusted
> SQL Server connection.
> Thanks!
>|||"Sasan Saidi" wrote:
> Are you using a .NET application?
No. I'm simply trying to set up the DSN in the Data Sources (ODBC) control
panel.|||Has the user logged onto the domain? Does the user account have an
associated SQL login?
Jeff
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:F095B40F-78FB-4096-89CA-7E9E1F9E0580@.microsoft.com...
> "Sasan Saidi" wrote:
> > Are you using a .NET application?
> No. I'm simply trying to set up the DSN in the Data Sources (ODBC) control
> panel.|||My guess is that the SQL Server is setup to allow only Windows logins and you try to login using a
SQL server login.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in message
news:BAD981FB-7BB8-46BB-A297-80DE6CF7B543@.microsoft.com...
> "Rand Boyd [MSFT]" wrote:
> > Server machine. The first part of the message is the most important. What
> > is the complete message that the users are getting?
> SQLState '28000'
> SQL Server Error 18452
> [...] Login failed for user '(null)'. Reason: not associated with a trusted
> SQL Server connection.
> Thanks!
>
"non-trusted connection" what does it mean?
What does it mean when a users is connected but it is a "non-trusted connection". This is showing up in the log file after my users connect. How does this effect the system/user? We have been experiencing speed issues and I was wondering if this might be a possible source of the problem.
Thanks in advance.A "trusted connection" is made using Windoze authentication. A "non-trusted connection" is made using SQL authentication. That is is the only difference that I know of between the two kinds of connections.
-PatP
Thanks in advance.A "trusted connection" is made using Windoze authentication. A "non-trusted connection" is made using SQL authentication. That is is the only difference that I know of between the two kinds of connections.
-PatP
"No Resultset produced" when using temp tables in stored proc
I am using microsofts jdbc driver and I am trying to call a stored
procedure to get a resultset from SQL Server.
Usually this works fine when I select something from an ordinary table
but when I am involving a temp table in the final select statement I
do not get any resultset in return?
Lars wrote:
> I am using microsofts jdbc driver and I am trying to call a stored
> procedure to get a resultset from SQL Server.
> Usually this works fine when I select something from an ordinary table
> but when I am involving a temp table in the final select statement I
> do not get any resultset in return?
I believe the result set *does* come back, but your other stuff in the
procedure before the final select will also be returning update counts etc,
so you need to loop to handle all the update counts as well, before
getResultSet() will find the returned data.
Joe Weinstein at BEA
procedure to get a resultset from SQL Server.
Usually this works fine when I select something from an ordinary table
but when I am involving a temp table in the final select statement I
do not get any resultset in return?
Lars wrote:
> I am using microsofts jdbc driver and I am trying to call a stored
> procedure to get a resultset from SQL Server.
> Usually this works fine when I select something from an ordinary table
> but when I am involving a temp table in the final select statement I
> do not get any resultset in return?
I believe the result set *does* come back, but your other stuff in the
procedure before the final select will also be returning update counts etc,
so you need to loop to handle all the update counts as well, before
getResultSet() will find the returned data.
Joe Weinstein at BEA
"No more data available to read" error
Hi All
"No more data available to read" error is thrown by database driver when
connections max out on SQL Server. Does any one know what this means and ho
w
can i resolve this issue? An application developed in Java uses SQL Server
for data storage. Thank you in advance.This is not a sql server error message. It is an error message that is
raised by the jdbc driver itself.
It could be caused by several things that I know of, but each is sort of
application dependent.
1) Thread contention (non-thread safe) code within the app. 2 thread
competing for the same data in memory on the server
2) BLOB types, I read a bit that having BLOB types within your result set
can lead to this type of problem
3) Trying to scroll backward, or through a forward-only result set for a
second time.
I'd recommend contacting the JDBC driver manufacturer, as they would have
more details. I would be surprised if it was really related to not enough d
b
connections.
Sorry I can't be of more help.
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"MittyKom" wrote:
> Hi All
> "No more data available to read" error is thrown by database driver when
> connections max out on SQL Server. Does any one know what this means and
how
> can i resolve this issue? An application developed in Java uses SQL Server
> for data storage. Thank you in advance.
>
"No more data available to read" error is thrown by database driver when
connections max out on SQL Server. Does any one know what this means and ho
w
can i resolve this issue? An application developed in Java uses SQL Server
for data storage. Thank you in advance.This is not a sql server error message. It is an error message that is
raised by the jdbc driver itself.
It could be caused by several things that I know of, but each is sort of
application dependent.
1) Thread contention (non-thread safe) code within the app. 2 thread
competing for the same data in memory on the server
2) BLOB types, I read a bit that having BLOB types within your result set
can lead to this type of problem
3) Trying to scroll backward, or through a forward-only result set for a
second time.
I'd recommend contacting the JDBC driver manufacturer, as they would have
more details. I would be surprised if it was really related to not enough d
b
connections.
Sorry I can't be of more help.
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"MittyKom" wrote:
> Hi All
> "No more data available to read" error is thrown by database driver when
> connections max out on SQL Server. Does any one know what this means and
how
> can i resolve this issue? An application developed in Java uses SQL Server
> for data storage. Thank you in advance.
>
"Next Page" is extremely slow - like it's requerying
I have a report with a single dataset, 3-levels of table grouping, and
returns 5 pages. When I preview the report in VStudio, it returns the 5
pages and I can navigate the pages real quickly. The problem is, when I run
it online via the RS front-end and click "Next Page," it takes a good 30
seconds to load the subsequent page or pages. The query itself runs in about
45 seconds, but why is the paging not near instantaneous?
Thanks,
jI hope you must have asked some questions yourself like
1. Is this report is slow or all the reports are slow?
2. Is your dev environment and production is different then how fast is your
production machine, is the machine slow (database server)?
3. Is Cache enabled/disabled ?
4. Are you doing the dev and online chking on the same machine ?
Probabily you can go to configuration from RS manager screen and check for
the cache options.
Amarnath
"Jordan" wrote:
> I have a report with a single dataset, 3-levels of table grouping, and
> returns 5 pages. When I preview the report in VStudio, it returns the 5
> pages and I can navigate the pages real quickly. The problem is, when I run
> it online via the RS front-end and click "Next Page," it takes a good 30
> seconds to load the subsequent page or pages. The query itself runs in about
> 45 seconds, but why is the paging not near instantaneous?
> Thanks,
> j
>
>|||Absolutely.
1. This report is slow. All the other reports paginate just fine, even those
that use the same datasources.
2. It's a production machine.
3. It's not caching a temporary copy, but neither is any of the other
reports. I set the caching (every 10 minutes) and it's 99% faster, but I
still don't understand. The report shouldn't refresh itself on every page,
only every time it is run.
4. Yes. Would this really matter?
Thanks,
j
"Amarnath" <Amarnath@.discussions.microsoft.com> wrote in message
news:DC42BC35-CD2A-42EB-BADA-E7FA77FCCA5F@.microsoft.com...
>I hope you must have asked some questions yourself like
> 1. Is this report is slow or all the reports are slow?
> 2. Is your dev environment and production is different then how fast is
> your
> production machine, is the machine slow (database server)?
> 3. Is Cache enabled/disabled ?
> 4. Are you doing the dev and online chking on the same machine ?
> Probabily you can go to configuration from RS manager screen and check for
> the cache options.
> Amarnath
> "Jordan" wrote:
>> I have a report with a single dataset, 3-levels of table grouping, and
>> returns 5 pages. When I preview the report in VStudio, it returns the 5
>> pages and I can navigate the pages real quickly. The problem is, when I
>> run
>> it online via the RS front-end and click "Next Page," it takes a good 30
>> seconds to load the subsequent page or pages. The query itself runs in
>> about
>> 45 seconds, but why is the paging not near instantaneous?
>> Thanks,
>> j
>>|||Absolutely.
1. This report is slow. All the other reports paginate just fine, even those
that use the same datasources.
2. It's a production machine.
3. It's not caching a temporary copy, but neither is any of the other
reports. I set the caching (every 10 minutes) and it's 99% faster, but I
still don't understand. The report shouldn't refresh itself on every page,
only every time it is run.
4. Yes. Would this really matter?
Thanks,
j
returns 5 pages. When I preview the report in VStudio, it returns the 5
pages and I can navigate the pages real quickly. The problem is, when I run
it online via the RS front-end and click "Next Page," it takes a good 30
seconds to load the subsequent page or pages. The query itself runs in about
45 seconds, but why is the paging not near instantaneous?
Thanks,
jI hope you must have asked some questions yourself like
1. Is this report is slow or all the reports are slow?
2. Is your dev environment and production is different then how fast is your
production machine, is the machine slow (database server)?
3. Is Cache enabled/disabled ?
4. Are you doing the dev and online chking on the same machine ?
Probabily you can go to configuration from RS manager screen and check for
the cache options.
Amarnath
"Jordan" wrote:
> I have a report with a single dataset, 3-levels of table grouping, and
> returns 5 pages. When I preview the report in VStudio, it returns the 5
> pages and I can navigate the pages real quickly. The problem is, when I run
> it online via the RS front-end and click "Next Page," it takes a good 30
> seconds to load the subsequent page or pages. The query itself runs in about
> 45 seconds, but why is the paging not near instantaneous?
> Thanks,
> j
>
>|||Absolutely.
1. This report is slow. All the other reports paginate just fine, even those
that use the same datasources.
2. It's a production machine.
3. It's not caching a temporary copy, but neither is any of the other
reports. I set the caching (every 10 minutes) and it's 99% faster, but I
still don't understand. The report shouldn't refresh itself on every page,
only every time it is run.
4. Yes. Would this really matter?
Thanks,
j
"Amarnath" <Amarnath@.discussions.microsoft.com> wrote in message
news:DC42BC35-CD2A-42EB-BADA-E7FA77FCCA5F@.microsoft.com...
>I hope you must have asked some questions yourself like
> 1. Is this report is slow or all the reports are slow?
> 2. Is your dev environment and production is different then how fast is
> your
> production machine, is the machine slow (database server)?
> 3. Is Cache enabled/disabled ?
> 4. Are you doing the dev and online chking on the same machine ?
> Probabily you can go to configuration from RS manager screen and check for
> the cache options.
> Amarnath
> "Jordan" wrote:
>> I have a report with a single dataset, 3-levels of table grouping, and
>> returns 5 pages. When I preview the report in VStudio, it returns the 5
>> pages and I can navigate the pages real quickly. The problem is, when I
>> run
>> it online via the RS front-end and click "Next Page," it takes a good 30
>> seconds to load the subsequent page or pages. The query itself runs in
>> about
>> 45 seconds, but why is the paging not near instantaneous?
>> Thanks,
>> j
>>|||Absolutely.
1. This report is slow. All the other reports paginate just fine, even those
that use the same datasources.
2. It's a production machine.
3. It's not caching a temporary copy, but neither is any of the other
reports. I set the caching (every 10 minutes) and it's 99% faster, but I
still don't understand. The report shouldn't refresh itself on every page,
only every time it is run.
4. Yes. Would this really matter?
Thanks,
j
"New transaction is not allowed because there are other threads running in the session." -
That is a SqlException I got at a...
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
Anyone an idea what THAT means? How do I cause it? How can I work around it?
I think you probably have an open connection somewhere. See if you can start the transaction after you close the connection.|||I have about 25 open connections. Because that is - quess what - an ASP.NET application.
Once connection per page.
And every page has - one thread. No multi threading at all.
Plus it is legal for a thread to have multiple open connections.
|||Have you enabled Multiple Active Resultsets (MARS)?
For connections with MARS enabled, manual transactions started via TSQL BEGIN TRAN must end (COMMIT or ROLLBACK) within the same scope that started the transaction
If your issue meets the the aboving conditions, you can try this work aroud:
To begin tansactions that are not required to be scoped under
MARS-enabled connections, use the client driver API constructs to begin and end transactions. If existing code relies on BEGIN TRAN operations without
corresponding COMMIT/ROLLBACK, MARS can be turned off until the application can be modified to use the client driver transaction APIs
"network name is no longer available"
Hi all,
I'm having a problem with backing up SQL to a network share, often I would
get the "network name is no longer available". Sometimes it works and
sometimes it doesn't. During the backup, both servers - source and
destination are at minimal cpu , memory and network utilization. Network
drivers are also up to date. OS has sp4. Has anyone come across this
before? Any suggestions would be appreciated. Thank you.
I ran into this myself sometimes an these are a few thing I would look for
answers in: What do the SQL Server Error logs say and what do the
application logs on the server say? Is the server you are backing up on a
NAS box or use IDE drives or SCSI drives? Is the server holding the backups
using a static IP address? Does this problem only occur on this one server
holding the backup or on any server you try?
Depending on these answers the problem should be isolated more.
Bryan Bitzer MCP
Senior Database Administrator
Marshall & Swift / Boeckh
www.msbinfo.com
"network name is no longer available" <"network name is no longer
available"@.discussions.microsoft.com> wrote in message
news:A3D0CDBF-0FF4-4FF3-8CCA-529E34AE7F5D@.microsoft.com...
> Hi all,
> I'm having a problem with backing up SQL to a network share, often I would
> get the "network name is no longer available". Sometimes it works and
> sometimes it doesn't. During the backup, both servers - source and
> destination are at minimal cpu , memory and network utilization. Network
> drivers are also up to date. OS has sp4. Has anyone come across this
> before? Any suggestions would be appreciated. Thank you.
>
|||If the network map is \\servername\share it can be a WINS Issue.
You might try \\servername.domain.com\share and see if that fixes the problem.
Tim S
|||Hi,
You seem to be running into the issue described in the Microsoft Knowledge
Base Article No 293403. Please go through the following link to review the
article.
http://support.microsoft.com/?kbid=293403
Hope this resolve the issue. Please keep me updated on the status.
Thanks
Vijay
I'm having a problem with backing up SQL to a network share, often I would
get the "network name is no longer available". Sometimes it works and
sometimes it doesn't. During the backup, both servers - source and
destination are at minimal cpu , memory and network utilization. Network
drivers are also up to date. OS has sp4. Has anyone come across this
before? Any suggestions would be appreciated. Thank you.
I ran into this myself sometimes an these are a few thing I would look for
answers in: What do the SQL Server Error logs say and what do the
application logs on the server say? Is the server you are backing up on a
NAS box or use IDE drives or SCSI drives? Is the server holding the backups
using a static IP address? Does this problem only occur on this one server
holding the backup or on any server you try?
Depending on these answers the problem should be isolated more.
Bryan Bitzer MCP
Senior Database Administrator
Marshall & Swift / Boeckh
www.msbinfo.com
"network name is no longer available" <"network name is no longer
available"@.discussions.microsoft.com> wrote in message
news:A3D0CDBF-0FF4-4FF3-8CCA-529E34AE7F5D@.microsoft.com...
> Hi all,
> I'm having a problem with backing up SQL to a network share, often I would
> get the "network name is no longer available". Sometimes it works and
> sometimes it doesn't. During the backup, both servers - source and
> destination are at minimal cpu , memory and network utilization. Network
> drivers are also up to date. OS has sp4. Has anyone come across this
> before? Any suggestions would be appreciated. Thank you.
>
|||If the network map is \\servername\share it can be a WINS Issue.
You might try \\servername.domain.com\share and see if that fixes the problem.
Tim S
|||Hi,
You seem to be running into the issue described in the Microsoft Knowledge
Base Article No 293403. Please go through the following link to review the
article.
http://support.microsoft.com/?kbid=293403
Hope this resolve the issue. Please keep me updated on the status.
Thanks
Vijay
"Net Send" from SQL2000 on Windows 2003.
Hi all.
The following command don't work on ours SQL server anymore. Not sure why,
but could it be that the command "Net Send" is removed for NT 2000?
Any ideas.
Thanks
Geir
DECLARE @.Message varchar(255)
Set @.Message = 'net send USER Message to OLNY from SAM : . TEST, TEST, Price
to low!. Order 6555 '
EXEC master.. xp_cmdshell @.Message, no_outputHi
Is your SQL Server Service Account a Domain account? If not, change it to a
domain account.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Geir Holme" <geir@.multicase.no> wrote in message
news:%23lrHkVOxFHA.3720@.TK2MSFTNGP11.phx.gbl...
> Hi all.
> The following command don't work on ours SQL server anymore. Not sure why,
> but could it be that the command "Net Send" is removed for NT 2000?
> Any ideas.
> Thanks
> Geir
> DECLARE @.Message varchar(255)
> Set @.Message = 'net send USER Message to OLNY from SAM : . TEST, TEST,
> Price to low!. Order 6555 '
> EXEC master.. xp_cmdshell @.Message, no_output
>|||Hi
See this example written by Tibor
USE master
GO
CREATE PROC sp_dbm_notify_users @.msg VARCHAR(255) AS
/ ****************************************
***********************************
****/
/* This procedure does a NET SEND to all connected computers.
*/
/* Requires that the messenger service is running on the client.
*/
/* The bat file is a sample showing how a message can be sent from the OS.
A */
/* shortcut to the bat file can be placed on the desktop, for instance
*/
/* The procedure takes the following parameter:
*/
/* @.msg VARCHAR(255) (required): The message to be sent
*/
/* Written by Tibor Karaszi and Brje Carlsson 1999. www.dbmaint.com
*/
/* Tested on verion 6.5, 7.0 and 8.0.
*/
/ ****************************************
***********************************
****/
SET NOCOUNT ON
--Get version number and verify supported version
DECLARE @.ver VARCHAR(7)
SELECT @.ver = CASE
WHEN CHARINDEX('6.50', @.@.VERSION) > 0 THEN '6.50'
WHEN CHARINDEX('7.00', @.@.VERSION) > 0 THEN '7.00'
WHEN CHARINDEX('8.00', @.@.VERSION) > 0 THEN '8.00'
ELSE 'Unknown'
END
IF @.ver = 'Unknown'
BEGIN
RAISERROR('Unsupported version of SQL Server.',16,1)
RETURN -101
END
--Declare variables section
DECLARE loop_name INSENSITIVE CURSOR FOR
SELECT DISTINCT LTRIM(RTRIM(hostname))
FROM master..sysprocesses
WHERE DATALENGTH(LTRIM(RTRIM(hostname))) > 0
OPEN loop_name
DECLARE @.host_name VARCHAR(30)
DECLARE @.exec_str VARCHAR(255)
FETCH NEXT FROM loop_name INTO @.host_name
WHILE (@.@.fetch_status = 0)
BEGIN
SELECT @.exec_str = 'master..xp_cmdshell "NET SEND ' + @.host_name + ' '
+ @.msg + '"'
EXEC( @.exec_str)
FETCH NEXT FROM loop_name INTO @.host_name
END
DEALLOCATE loop_name
GO
/* Sample Execution:
EXEC sp_dbm_notify_users 'SQL Server will shut down in 30 minutes!'
*/
"Geir Holme" <geir@.multicase.no> wrote in message
news:%23lrHkVOxFHA.3720@.TK2MSFTNGP11.phx.gbl...
> Hi all.
> The following command don't work on ours SQL server anymore. Not sure why,
> but could it be that the command "Net Send" is removed for NT 2000?
> Any ideas.
> Thanks
> Geir
> DECLARE @.Message varchar(255)
> Set @.Message = 'net send USER Message to OLNY from SAM : . TEST, TEST,
> Price to low!. Order 6555 '
> EXEC master.. xp_cmdshell @.Message, no_output
>|||it's possible that your messenger service may be shut down (check start ->
run-> services.msc -> messenger).
Regards,
Mary
"Geir Holme" <geir@.multicase.no> wrote in message
news:%23lrHkVOxFHA.3720@.TK2MSFTNGP11.phx.gbl...
> Hi all.
> The following command don't work on ours SQL server anymore. Not sure why,
> but could it be that the command "Net Send" is removed for NT 2000?
> Any ideas.
> Thanks
> Geir
> DECLARE @.Message varchar(255)
> Set @.Message = 'net send USER Message to OLNY from SAM : . TEST, TEST,
> Price to low!. Order 6555 '
> EXEC master.. xp_cmdshell @.Message, no_output
>
The following command don't work on ours SQL server anymore. Not sure why,
but could it be that the command "Net Send" is removed for NT 2000?
Any ideas.
Thanks
Geir
DECLARE @.Message varchar(255)
Set @.Message = 'net send USER Message to OLNY from SAM : . TEST, TEST, Price
to low!. Order 6555 '
EXEC master.. xp_cmdshell @.Message, no_outputHi
Is your SQL Server Service Account a Domain account? If not, change it to a
domain account.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Geir Holme" <geir@.multicase.no> wrote in message
news:%23lrHkVOxFHA.3720@.TK2MSFTNGP11.phx.gbl...
> Hi all.
> The following command don't work on ours SQL server anymore. Not sure why,
> but could it be that the command "Net Send" is removed for NT 2000?
> Any ideas.
> Thanks
> Geir
> DECLARE @.Message varchar(255)
> Set @.Message = 'net send USER Message to OLNY from SAM : . TEST, TEST,
> Price to low!. Order 6555 '
> EXEC master.. xp_cmdshell @.Message, no_output
>|||Hi
See this example written by Tibor
USE master
GO
CREATE PROC sp_dbm_notify_users @.msg VARCHAR(255) AS
/ ****************************************
***********************************
****/
/* This procedure does a NET SEND to all connected computers.
*/
/* Requires that the messenger service is running on the client.
*/
/* The bat file is a sample showing how a message can be sent from the OS.
A */
/* shortcut to the bat file can be placed on the desktop, for instance
*/
/* The procedure takes the following parameter:
*/
/* @.msg VARCHAR(255) (required): The message to be sent
*/
/* Written by Tibor Karaszi and Brje Carlsson 1999. www.dbmaint.com
*/
/* Tested on verion 6.5, 7.0 and 8.0.
*/
/ ****************************************
***********************************
****/
SET NOCOUNT ON
--Get version number and verify supported version
DECLARE @.ver VARCHAR(7)
SELECT @.ver = CASE
WHEN CHARINDEX('6.50', @.@.VERSION) > 0 THEN '6.50'
WHEN CHARINDEX('7.00', @.@.VERSION) > 0 THEN '7.00'
WHEN CHARINDEX('8.00', @.@.VERSION) > 0 THEN '8.00'
ELSE 'Unknown'
END
IF @.ver = 'Unknown'
BEGIN
RAISERROR('Unsupported version of SQL Server.',16,1)
RETURN -101
END
--Declare variables section
DECLARE loop_name INSENSITIVE CURSOR FOR
SELECT DISTINCT LTRIM(RTRIM(hostname))
FROM master..sysprocesses
WHERE DATALENGTH(LTRIM(RTRIM(hostname))) > 0
OPEN loop_name
DECLARE @.host_name VARCHAR(30)
DECLARE @.exec_str VARCHAR(255)
FETCH NEXT FROM loop_name INTO @.host_name
WHILE (@.@.fetch_status = 0)
BEGIN
SELECT @.exec_str = 'master..xp_cmdshell "NET SEND ' + @.host_name + ' '
+ @.msg + '"'
EXEC( @.exec_str)
FETCH NEXT FROM loop_name INTO @.host_name
END
DEALLOCATE loop_name
GO
/* Sample Execution:
EXEC sp_dbm_notify_users 'SQL Server will shut down in 30 minutes!'
*/
"Geir Holme" <geir@.multicase.no> wrote in message
news:%23lrHkVOxFHA.3720@.TK2MSFTNGP11.phx.gbl...
> Hi all.
> The following command don't work on ours SQL server anymore. Not sure why,
> but could it be that the command "Net Send" is removed for NT 2000?
> Any ideas.
> Thanks
> Geir
> DECLARE @.Message varchar(255)
> Set @.Message = 'net send USER Message to OLNY from SAM : . TEST, TEST,
> Price to low!. Order 6555 '
> EXEC master.. xp_cmdshell @.Message, no_output
>|||it's possible that your messenger service may be shut down (check start ->
run-> services.msc -> messenger).
Regards,
Mary
"Geir Holme" <geir@.multicase.no> wrote in message
news:%23lrHkVOxFHA.3720@.TK2MSFTNGP11.phx.gbl...
> Hi all.
> The following command don't work on ours SQL server anymore. Not sure why,
> but could it be that the command "Net Send" is removed for NT 2000?
> Any ideas.
> Thanks
> Geir
> DECLARE @.Message varchar(255)
> Set @.Message = 'net send USER Message to OLNY from SAM : . TEST, TEST,
> Price to low!. Order 6555 '
> EXEC master.. xp_cmdshell @.Message, no_output
>
"Name" Data Type?
Standard apologies for the extreme "newbie" nature of this question...
I've just seen a batch of sample code with the following declaration:
DECLARE @.MyVar Name
"@.MyVar", in this case, is there to receive some string data from an OPENXML
operation. The contents of the variable are subsequently used to feed other
functions that take varchar's as arguments. This works successfully.
The problem is, I've never heard of a "Name" before -- I can't CAST it to
anything useful, nor can I successfully concatenate it with a "+" operator t
o
other string data. I don't believe it is actually a data type, but I'm afrai
d
I don't know what it is, and because of what it's called it's darned hard to
look up in BOL!
Any insight would be greatly appreciated. Thanks!Dan,
A user-defined data type?
Try:
EXEC sp_datatype_info
GO
--and
EXEC sp_help
GO
HTH
Jerry
"Dan Costello" <DanCostello@.discussions.microsoft.com> wrote in message
news:C4A7366D-A8D9-4AE7-90D5-AF1E9CEDC847@.microsoft.com...
> Standard apologies for the extreme "newbie" nature of this question...
> I've just seen a batch of sample code with the following declaration:
> DECLARE @.MyVar Name
> "@.MyVar", in this case, is there to receive some string data from an
> OPENXML
> operation. The contents of the variable are subsequently used to feed
> other
> functions that take varchar's as arguments. This works successfully.
> The problem is, I've never heard of a "Name" before -- I can't CAST it to
> anything useful, nor can I successfully concatenate it with a "+" operator
> to
> other string data. I don't believe it is actually a data type, but I'm
> afraid
> I don't know what it is, and because of what it's called it's darned hard
> to
> look up in BOL!
> Any insight would be greatly appreciated. Thanks!|||Jerry,
Well, now I feel really stupid! Thanks, that's got it. For the sake of
completeness, the sample code uses the "AdventureWorks" sample database,
which does in fact have a user-defined type called "Name" already in it.
--Dan
"Jerry Spivey" wrote:
> Dan,
> A user-defined data type?
> Try:
> EXEC sp_datatype_info
> GO
> --and
> EXEC sp_help
> GO
> HTH
> Jerry
> "Dan Costello" <DanCostello@.discussions.microsoft.com> wrote in message
> news:C4A7366D-A8D9-4AE7-90D5-AF1E9CEDC847@.microsoft.com...
>
>
I've just seen a batch of sample code with the following declaration:
DECLARE @.MyVar Name
"@.MyVar", in this case, is there to receive some string data from an OPENXML
operation. The contents of the variable are subsequently used to feed other
functions that take varchar's as arguments. This works successfully.
The problem is, I've never heard of a "Name" before -- I can't CAST it to
anything useful, nor can I successfully concatenate it with a "+" operator t
o
other string data. I don't believe it is actually a data type, but I'm afrai
d
I don't know what it is, and because of what it's called it's darned hard to
look up in BOL!
Any insight would be greatly appreciated. Thanks!Dan,
A user-defined data type?
Try:
EXEC sp_datatype_info
GO
--and
EXEC sp_help
GO
HTH
Jerry
"Dan Costello" <DanCostello@.discussions.microsoft.com> wrote in message
news:C4A7366D-A8D9-4AE7-90D5-AF1E9CEDC847@.microsoft.com...
> Standard apologies for the extreme "newbie" nature of this question...
> I've just seen a batch of sample code with the following declaration:
> DECLARE @.MyVar Name
> "@.MyVar", in this case, is there to receive some string data from an
> OPENXML
> operation. The contents of the variable are subsequently used to feed
> other
> functions that take varchar's as arguments. This works successfully.
> The problem is, I've never heard of a "Name" before -- I can't CAST it to
> anything useful, nor can I successfully concatenate it with a "+" operator
> to
> other string data. I don't believe it is actually a data type, but I'm
> afraid
> I don't know what it is, and because of what it's called it's darned hard
> to
> look up in BOL!
> Any insight would be greatly appreciated. Thanks!|||Jerry,
Well, now I feel really stupid! Thanks, that's got it. For the sake of
completeness, the sample code uses the "AdventureWorks" sample database,
which does in fact have a user-defined type called "Name" already in it.
--Dan
"Jerry Spivey" wrote:
> Dan,
> A user-defined data type?
> Try:
> EXEC sp_datatype_info
> GO
> --and
> EXEC sp_help
> GO
> HTH
> Jerry
> "Dan Costello" <DanCostello@.discussions.microsoft.com> wrote in message
> news:C4A7366D-A8D9-4AE7-90D5-AF1E9CEDC847@.microsoft.com...
>
>
"Name" Data Type?
Standard apologies for the extreme "newbie" nature of this question...
I've just seen a batch of sample code with the following declaration:
DECLARE @.MyVar Name
"@.MyVar", in this case, is there to receive some string data from an OPENXML
operation. The contents of the variable are subsequently used to feed other
functions that take varchar's as arguments. This works successfully.
The problem is, I've never heard of a "Name" before -- I can't CAST it to
anything useful, nor can I successfully concatenate it with a "+" operator to
other string data. I don't believe it is actually a data type, but I'm afraid
I don't know what it is, and because of what it's called it's darned hard to
look up in BOL!
Any insight would be greatly appreciated. Thanks!Dan,
A user-defined data type?
Try:
EXEC sp_datatype_info
GO
--and
EXEC sp_help
GO
HTH
Jerry
"Dan Costello" <DanCostello@.discussions.microsoft.com> wrote in message
news:C4A7366D-A8D9-4AE7-90D5-AF1E9CEDC847@.microsoft.com...
> Standard apologies for the extreme "newbie" nature of this question...
> I've just seen a batch of sample code with the following declaration:
> DECLARE @.MyVar Name
> "@.MyVar", in this case, is there to receive some string data from an
> OPENXML
> operation. The contents of the variable are subsequently used to feed
> other
> functions that take varchar's as arguments. This works successfully.
> The problem is, I've never heard of a "Name" before -- I can't CAST it to
> anything useful, nor can I successfully concatenate it with a "+" operator
> to
> other string data. I don't believe it is actually a data type, but I'm
> afraid
> I don't know what it is, and because of what it's called it's darned hard
> to
> look up in BOL!
> Any insight would be greatly appreciated. Thanks!|||Jerry,
Well, now I feel really stupid! Thanks, that's got it. For the sake of
completeness, the sample code uses the "AdventureWorks" sample database,
which does in fact have a user-defined type called "Name" already in it.
--Dan
"Jerry Spivey" wrote:
> Dan,
> A user-defined data type?
> Try:
> EXEC sp_datatype_info
> GO
> --and
> EXEC sp_help
> GO
> HTH
> Jerry
> "Dan Costello" <DanCostello@.discussions.microsoft.com> wrote in message
> news:C4A7366D-A8D9-4AE7-90D5-AF1E9CEDC847@.microsoft.com...
> > Standard apologies for the extreme "newbie" nature of this question...
> >
> > I've just seen a batch of sample code with the following declaration:
> >
> > DECLARE @.MyVar Name
> >
> > "@.MyVar", in this case, is there to receive some string data from an
> > OPENXML
> > operation. The contents of the variable are subsequently used to feed
> > other
> > functions that take varchar's as arguments. This works successfully.
> >
> > The problem is, I've never heard of a "Name" before -- I can't CAST it to
> > anything useful, nor can I successfully concatenate it with a "+" operator
> > to
> > other string data. I don't believe it is actually a data type, but I'm
> > afraid
> > I don't know what it is, and because of what it's called it's darned hard
> > to
> > look up in BOL!
> >
> > Any insight would be greatly appreciated. Thanks!
>
>
I've just seen a batch of sample code with the following declaration:
DECLARE @.MyVar Name
"@.MyVar", in this case, is there to receive some string data from an OPENXML
operation. The contents of the variable are subsequently used to feed other
functions that take varchar's as arguments. This works successfully.
The problem is, I've never heard of a "Name" before -- I can't CAST it to
anything useful, nor can I successfully concatenate it with a "+" operator to
other string data. I don't believe it is actually a data type, but I'm afraid
I don't know what it is, and because of what it's called it's darned hard to
look up in BOL!
Any insight would be greatly appreciated. Thanks!Dan,
A user-defined data type?
Try:
EXEC sp_datatype_info
GO
--and
EXEC sp_help
GO
HTH
Jerry
"Dan Costello" <DanCostello@.discussions.microsoft.com> wrote in message
news:C4A7366D-A8D9-4AE7-90D5-AF1E9CEDC847@.microsoft.com...
> Standard apologies for the extreme "newbie" nature of this question...
> I've just seen a batch of sample code with the following declaration:
> DECLARE @.MyVar Name
> "@.MyVar", in this case, is there to receive some string data from an
> OPENXML
> operation. The contents of the variable are subsequently used to feed
> other
> functions that take varchar's as arguments. This works successfully.
> The problem is, I've never heard of a "Name" before -- I can't CAST it to
> anything useful, nor can I successfully concatenate it with a "+" operator
> to
> other string data. I don't believe it is actually a data type, but I'm
> afraid
> I don't know what it is, and because of what it's called it's darned hard
> to
> look up in BOL!
> Any insight would be greatly appreciated. Thanks!|||Jerry,
Well, now I feel really stupid! Thanks, that's got it. For the sake of
completeness, the sample code uses the "AdventureWorks" sample database,
which does in fact have a user-defined type called "Name" already in it.
--Dan
"Jerry Spivey" wrote:
> Dan,
> A user-defined data type?
> Try:
> EXEC sp_datatype_info
> GO
> --and
> EXEC sp_help
> GO
> HTH
> Jerry
> "Dan Costello" <DanCostello@.discussions.microsoft.com> wrote in message
> news:C4A7366D-A8D9-4AE7-90D5-AF1E9CEDC847@.microsoft.com...
> > Standard apologies for the extreme "newbie" nature of this question...
> >
> > I've just seen a batch of sample code with the following declaration:
> >
> > DECLARE @.MyVar Name
> >
> > "@.MyVar", in this case, is there to receive some string data from an
> > OPENXML
> > operation. The contents of the variable are subsequently used to feed
> > other
> > functions that take varchar's as arguments. This works successfully.
> >
> > The problem is, I've never heard of a "Name" before -- I can't CAST it to
> > anything useful, nor can I successfully concatenate it with a "+" operator
> > to
> > other string data. I don't believe it is actually a data type, but I'm
> > afraid
> > I don't know what it is, and because of what it's called it's darned hard
> > to
> > look up in BOL!
> >
> > Any insight would be greatly appreciated. Thanks!
>
>
"must declare variable" when variable has been declared!
Any idea why the following message is returned?
Server: Msg 137, Level 15, State 2, Line 5
Must declare the variable '@.table'.
The script is created to run through each column in the database and check it against cross referenced data in other table. It had to be built because of the database we own has not a single referntial constraint in it (long story but it's from the the age old "our code works better than a well designed database" school of thought).
excuse the poorly laid out SQL - it's hard to copy and paste into this thing...
Thanks
Yal
------------------------
SET NOCOUNT ON
DECLARE @.table VARCHAR(32)
,@.column VARCHAR(32)
,@.x_table VARCHAR(32)
,@.x_column VARCHAR(32)
,@.x_type VARCHAR(32)
,@.problem VARCHAR(32)
,@.count VARCHAR(32)
DECLARE xref_check CURSOR FOR
SELECT table_name, column_name, xref_table, xref_column, xref_type
FROM xref_check
OPEN xref_check
FETCH NEXT FROM xref_check INTO @.table, @.column, @.x_table, @.x_column, @.x_type
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.x_table = 'PS_XREF'
BEGIN
-- SELECT @.table, @.column, @.x_table, @.x_column, @.x_type
EXEC ('IF (SELECT COUNT('+ @.column +')
FROM '+ @.table +'
WHERE '+ @.column + ' NOT IN (SELECT code FROM PS_XREF WHERE type = '''+ @.x_type +''')) > 0
BEGIN
SELECT @.table, @.column, @.x_table, @.x_column, @.x_type, x.'+ @.column +'
FROM '+ @.table +' x
WHERE '+ @.column + ' NOT IN (SELECT code FROM PS_XREF WHERE type = '''+ @.x_type +''')
END'
)
END
ELSE
BEGIN
-- SELECT @.table, @.column, @.x_table, @.x_column, @.x_type
EXEC ('IF (SELECT COUNT('+ @.column +')
FROM '+ @.table +'
WHERE '+ @.column + ' NOT IN (SELECT '+ @.x_column +' FROM '+ @.x_table +')) > 0
BEGIN
SELECT @.table, @.column, @.x_table, @.x_column, @.x_type, x.'+ @.column +'
FROM '+ @.table +' x
WHERE '+ @.column + ' NOT IN (SELECT '+ @.x_column +' FROM '+ @.x_table +')
END')
END
FETCH NEXT FROM xref_check INTO @.table, @.column, @.x_table, @.x_column, @.x_type
END
CLOSE xref_check
DEALLOCATE xref_checkMake sure your references to these variables exist outside the string. For example:
@.x_type +''')) > 0
BEGIN
SELECT @.table, @.column, @.x_table, @.x_column, @.x_type, x.'
should have @.table, @.column ... outside the string like you did before this statement.
Server: Msg 137, Level 15, State 2, Line 5
Must declare the variable '@.table'.
The script is created to run through each column in the database and check it against cross referenced data in other table. It had to be built because of the database we own has not a single referntial constraint in it (long story but it's from the the age old "our code works better than a well designed database" school of thought).
excuse the poorly laid out SQL - it's hard to copy and paste into this thing...
Thanks
Yal
------------------------
SET NOCOUNT ON
DECLARE @.table VARCHAR(32)
,@.column VARCHAR(32)
,@.x_table VARCHAR(32)
,@.x_column VARCHAR(32)
,@.x_type VARCHAR(32)
,@.problem VARCHAR(32)
,@.count VARCHAR(32)
DECLARE xref_check CURSOR FOR
SELECT table_name, column_name, xref_table, xref_column, xref_type
FROM xref_check
OPEN xref_check
FETCH NEXT FROM xref_check INTO @.table, @.column, @.x_table, @.x_column, @.x_type
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.x_table = 'PS_XREF'
BEGIN
-- SELECT @.table, @.column, @.x_table, @.x_column, @.x_type
EXEC ('IF (SELECT COUNT('+ @.column +')
FROM '+ @.table +'
WHERE '+ @.column + ' NOT IN (SELECT code FROM PS_XREF WHERE type = '''+ @.x_type +''')) > 0
BEGIN
SELECT @.table, @.column, @.x_table, @.x_column, @.x_type, x.'+ @.column +'
FROM '+ @.table +' x
WHERE '+ @.column + ' NOT IN (SELECT code FROM PS_XREF WHERE type = '''+ @.x_type +''')
END'
)
END
ELSE
BEGIN
-- SELECT @.table, @.column, @.x_table, @.x_column, @.x_type
EXEC ('IF (SELECT COUNT('+ @.column +')
FROM '+ @.table +'
WHERE '+ @.column + ' NOT IN (SELECT '+ @.x_column +' FROM '+ @.x_table +')) > 0
BEGIN
SELECT @.table, @.column, @.x_table, @.x_column, @.x_type, x.'+ @.column +'
FROM '+ @.table +' x
WHERE '+ @.column + ' NOT IN (SELECT '+ @.x_column +' FROM '+ @.x_table +')
END')
END
FETCH NEXT FROM xref_check INTO @.table, @.column, @.x_table, @.x_column, @.x_type
END
CLOSE xref_check
DEALLOCATE xref_checkMake sure your references to these variables exist outside the string. For example:
@.x_type +''')) > 0
BEGIN
SELECT @.table, @.column, @.x_table, @.x_column, @.x_type, x.'
should have @.table, @.column ... outside the string like you did before this statement.
"Must declare variable" error caused by stored procedure
I'm having the most difficult time trying to generate a report that
first calls a stored procedure and then retrieves the data produced
from it.
I get the error, "An error has occurred during report
processing...query execution failed for data set dsOrgs...Must declare
the variable @.INum" when I try to run the report.
The dataset below (dsOrgs) first calls a stored procedure
(SV_GetSubordinates) that populates a table with hierarchical data.
The second part (the Select statement) then retrieves the data
produced by the stored procedure. I have no problem running this set
of SQL statements in the Data view of the Reporting Services Report
Designer.
EXEC SV_GetSubordinates @.INum,'Groups',@.OrgNum
SELECT v_Orgs.*
FROM v_Orgs, SVSiblings
WHERE
v_Orgs.INum = @.INum AND
v_Orgs.INum = SVSiblings.INum AND
v_Orgs.OrgNum = SVSiblings.Num
By the way, @.INum is a parameter that will be passed to the report in
a URL string eventually. But for now, I have to use both the Preview
capability of the Report Designer and the Report Manager rendering
engine to test out my report.
I have another dataset that gets the @.OrgNum parameter value from a
selection in a drop down in my report. Here is the query for that
data set...
SELECT
NULL AS OrgNum,
'-- ALL Orgs --' AS [Description]
FROM SVGroupDefs
WHERE
INum = @.INum
UNION
SELECT
OrgNum,
[Description]
FROM SVGroupDefs
WHERE
INum = @.INum
ORDER BY [Description]
As you can see, I'm using @.INum in this dataset first so I can
populate my drop down list. When the user selects an Organization
from the drop down list, the selection returns the value for the
parameter @.OrgNum, which is used in my dsOrgs dataset along with @.INum
to retrieve the hierarchical data for my report.
You may be asking why I need to get hierarchical data when the table
object in the report designer uses a parent-child relationship. The
reason why I'm going through all this pain is because I need to
recursively get all the children from a starting parent level, which
@.OrgNum supplies. SQL Server does not natively support a way to
recursively get all the children in a hierarchy. The only way to do
this is to run through my stored procedure, which recursively calls
itself and then populates a table with the child OrgNum values
(fortunately Yukon has solved this recursive nightmare).
Anyway, how can I generate my report when the error states I must
first declare @.INum?I guess thats because the SPs run independently.
BTW have you considered using cursors in your SP?
>--Original Message--
>I'm having the most difficult time trying to generate a
report that
>first calls a stored procedure and then retrieves the
data produced
>from it.
>I get the error, "An error has occurred during report
>processing...query execution failed for data set
dsOrgs...Must declare
>the variable @.INum" when I try to run the report.
>The dataset below (dsOrgs) first calls a stored procedure
>(SV_GetSubordinates) that populates a table with
hierarchical data.
>The second part (the Select statement) then retrieves the
data
>produced by the stored procedure. I have no problem
running this set
>of SQL statements in the Data view of the Reporting
Services Report
>Designer.
>EXEC SV_GetSubordinates @.INum,'Groups',@.OrgNum
>SELECT v_Orgs.*
>FROM v_Orgs, SVSiblings
>WHERE
> v_Orgs.INum = @.INum AND
> v_Orgs.INum = SVSiblings.INum AND
> v_Orgs.OrgNum = SVSiblings.Num
>By the way, @.INum is a parameter that will be passed to
the report in
>a URL string eventually. But for now, I have to use both
the Preview
>capability of the Report Designer and the Report Manager
rendering
>engine to test out my report.
>I have another dataset that gets the @.OrgNum parameter
value from a
>selection in a drop down in my report. Here is the query
for that
>data set...
>SELECT
> NULL AS OrgNum,
> '-- ALL Orgs --' AS [Description]
>FROM SVGroupDefs
>WHERE
> INum = @.INum
>UNION
>SELECT
> OrgNum,
> [Description]
>FROM SVGroupDefs
>WHERE
> INum = @.INum
>ORDER BY [Description]
>As you can see, I'm using @.INum in this dataset first so
I can
>populate my drop down list. When the user selects an
Organization
>from the drop down list, the selection returns the value
for the
>parameter @.OrgNum, which is used in my dsOrgs dataset
along with @.INum
>to retrieve the hierarchical data for my report.
>You may be asking why I need to get hierarchical data
when the table
>object in the report designer uses a parent-child
relationship. The
>reason why I'm going through all this pain is because I
need to
>recursively get all the children from a starting parent
level, which
>@.OrgNum supplies. SQL Server does not natively support a
way to
>recursively get all the children in a hierarchy. The
only way to do
>this is to run through my stored procedure, which
recursively calls
>itself and then populates a table with the child OrgNum
values
>(fortunately Yukon has solved this recursive nightmare).
>Anyway, how can I generate my report when the error
states I must
>first declare @.INum?
>.
>|||Ravi, the stored procedure must call itself recursively as it gets the
children for each parent. For example, here's my hierarchy
OrgNum ParentOrgNum
1001 NULL
1002 1001
1003 1002
1004 1002
1005 1001
1006 1005
1007 1006
1008 1006
If I want all the children for OrgNum 1005, the sproc first runs and
gets OrgNum 1006 as a child. Then 1006 becomes the parent and the
sproc calls itself to get all the children for 1006, which are 1007
and 1008. When the sproc tries to get their children, there are no
more, and the sproc terminates. So, I end up with 1006, 1007, and
1008 as children for 1005. During each iteration of the sproc, I
insert the child OrgNum values into a table.
So, with my dsOrgs dataset, I first run the sproc, which does the
stuff above. Then I run a select statement which retrieves all the
children from the table I populated from my sproc. All that works
fine when I run everything in the data area of Reporting Services'
Report Builder.
But that's not the problem. The problem is that I get an error
stating that I have to declare a variable, @.INum. That's what my
first message covers in detail, and the problem to which I'm seeking a
solution.
"Ravi" <ravikantkv@.rediffmail.com> wrote in message news:<549501c49175$e1e4dcd0$a601280a@.phx.gbl>...
> I guess thats because the SPs run independently.
> BTW have you considered using cursors in your SP?
> >--Original Message--
> >I'm having the most difficult time trying to generate a
> report that
> >first calls a stored procedure and then retrieves the
> data produced
> >from it.
> >
> >I get the error, "An error has occurred during report
> >processing...query execution failed for data set
> dsOrgs...Must declare
> >the variable @.INum" when I try to run the report.
> >
> >The dataset below (dsOrgs) first calls a stored procedure
> >(SV_GetSubordinates) that populates a table with
> hierarchical data.
> >The second part (the Select statement) then retrieves the
> data
> >produced by the stored procedure. I have no problem
> running this set
> >of SQL statements in the Data view of the Reporting
> Services Report
> >Designer.
> >
> >EXEC SV_GetSubordinates @.INum,'Groups',@.OrgNum
> >SELECT v_Orgs.*
> >FROM v_Orgs, SVSiblings
> >WHERE
> > v_Orgs.INum = @.INum AND
> > v_Orgs.INum = SVSiblings.INum AND
> > v_Orgs.OrgNum = SVSiblings.Num
> >
> >By the way, @.INum is a parameter that will be passed to
> the report in
> >a URL string eventually. But for now, I have to use both
> the Preview
> >capability of the Report Designer and the Report Manager
> rendering
> >engine to test out my report.
> >
> >I have another dataset that gets the @.OrgNum parameter
> value from a
> >selection in a drop down in my report. Here is the query
> for that
> >data set...
> >
> >SELECT
> > NULL AS OrgNum,
> > '-- ALL Orgs --' AS [Description]
> >FROM SVGroupDefs
> >WHERE
> > INum = @.INum
> >UNION
> >SELECT
> > OrgNum,
> > [Description]
> >FROM SVGroupDefs
> >WHERE
> > INum = @.INum
> >ORDER BY [Description]
> >
> >As you can see, I'm using @.INum in this dataset first so
> I can
> >populate my drop down list. When the user selects an
> Organization
> >from the drop down list, the selection returns the value
> for the
> >parameter @.OrgNum, which is used in my dsOrgs dataset
> along with @.INum
> >to retrieve the hierarchical data for my report.
> >
> >You may be asking why I need to get hierarchical data
> when the table
> >object in the report designer uses a parent-child
> relationship. The
> >reason why I'm going through all this pain is because I
> need to
> >recursively get all the children from a starting parent
> level, which
> >@.OrgNum supplies. SQL Server does not natively support a
> way to
> >recursively get all the children in a hierarchy. The
> only way to do
> >this is to run through my stored procedure, which
> recursively calls
> >itself and then populates a table with the child OrgNum
> values
> >(fortunately Yukon has solved this recursive nightmare).
> >
> >Anyway, how can I generate my report when the error
> states I must
> >first declare @.INum?
> >.
> >|||Hi Steve,
I am doing something sort of similar... I have created a
stored procedure that takes in a couple of parameters and
passes them to the database and creates a table. I then
have a query that selects the data from the table. I
created two seperate datasets one for the stored
procedure and one for the select statement and it seems
to be working. I'm not sure how you tell it what to
execute first but another report writer here is doing the
same but she had to increase the timeout to give the
stored procedure a chance to finish otherwise it was
throwing errors. Sorry I'm not much help but I'm curious
if you've tried to use the parent group within the group
that is suppose to recursively search in a parent-child
relationship? I have the same exact thing to do that you
are doing and I would love to hear any lessons learned.
Thanks!!
>--Original Message--
>I'm having the most difficult time trying to generate a
report that
>first calls a stored procedure and then retrieves the
data produced
>from it.
>I get the error, "An error has occurred during report
>processing...query execution failed for data set
dsOrgs...Must declare
>the variable @.INum" when I try to run the report.
>The dataset below (dsOrgs) first calls a stored procedure
>(SV_GetSubordinates) that populates a table with
hierarchical data.
>The second part (the Select statement) then retrieves
the data
>produced by the stored procedure. I have no problem
running this set
>of SQL statements in the Data view of the Reporting
Services Report
>Designer.
>EXEC SV_GetSubordinates @.INum,'Groups',@.OrgNum
>SELECT v_Orgs.*
>FROM v_Orgs, SVSiblings
>WHERE
> v_Orgs.INum = @.INum AND
> v_Orgs.INum = SVSiblings.INum AND
> v_Orgs.OrgNum = SVSiblings.Num
>By the way, @.INum is a parameter that will be passed to
the report in
>a URL string eventually. But for now, I have to use
both the Preview
>capability of the Report Designer and the Report Manager
rendering
>engine to test out my report.
>I have another dataset that gets the @.OrgNum parameter
value from a
>selection in a drop down in my report. Here is the
query for that
>data set...
>SELECT
> NULL AS OrgNum,
> '-- ALL Orgs --' AS [Description]
>FROM SVGroupDefs
>WHERE
> INum = @.INum
>UNION
>SELECT
> OrgNum,
> [Description]
>FROM SVGroupDefs
>WHERE
> INum = @.INum
>ORDER BY [Description]
>As you can see, I'm using @.INum in this dataset first so
I can
>populate my drop down list. When the user selects an
Organization
>from the drop down list, the selection returns the value
for the
>parameter @.OrgNum, which is used in my dsOrgs dataset
along with @.INum
>to retrieve the hierarchical data for my report.
>You may be asking why I need to get hierarchical data
when the table
>object in the report designer uses a parent-child
relationship. The
>reason why I'm going through all this pain is because I
need to
>recursively get all the children from a starting parent
level, which
>@.OrgNum supplies. SQL Server does not natively support
a way to
>recursively get all the children in a hierarchy. The
only way to do
>this is to run through my stored procedure, which
recursively calls
>itself and then populates a table with the child OrgNum
values
>(fortunately Yukon has solved this recursive nightmare).
>Anyway, how can I generate my report when the error
states I must
>first declare @.INum?
>.
>|||Melissa,
Reporting Services does a fine job handling parent-child sets of
data in tables and what not...but that's assuming you have a data set
with all the data you want. What I have is an entire hierarchy, of
which, only one part I might want to retrieve in the data set for the
report (e.g. pulling back a region and their branches vs. the whole
entire organization). So, the whole problem here is about retrieving
the data and not about how Reporting Services will handle it after the
data is retrieved.
With that being said, I've already tried to break out the stored
procedure that gets all the children vs. the SQL query that retrieves
the data produced by the stored procedure into two separate data sets.
But how would Reporting Services know when the stored procedure has
finished in order to run the second query, which retrieves the data?
By arbitrarily setting a timeout? I find that method too unreliable.
I've already broken out my stored procedure and select statement into
two data sets, but that doesn't work.
So, what else have I done? I've tried to do a recursive SQL
function, but to no avail (functions can't recursive do selects of
data), a recursive stored procedure with a varying output parameter
(Reporting Services allows only 1 value per parameter for the current
release), but to no avail, setting the output of my stored procedure
to a temp table (can't seem to get that to work), but to no avail,
using a global temp table within my stored procedure (doesn't work
because the stored procedure recursively calls itself and you can only
declare the global temp table once), but to no avail, and a bunch of
other techniques in order to recursively grab all the children for the
region I select for my data set. The Yukon release of SQL Server will
solve my problem, because I will be able to execute a single
expression and retrieve the recursive data I need in a single
operation...but I need something in the meantime. (By the way, Oracle
already supports recursion with their Connect method).
So, I ended up having to pair the stored procedure with my select in
the same dataset in order to 1) generate a list of child values and 2)
retrieve that list of values AFTER they are generated. The dataset
refreshes no problem, but I get that stupid "need to declare @.INum
first" error, which I can't get rid of...it's so frustrating. I wish
I could speak to one of the Reporting Services developers over the
phone and figure this out.
"Melissa" <anonymous@.discussions.microsoft.com> wrote in message news:<028801c491fd$3c204e00$a401280a@.phx.gbl>...
> Hi Steve,
> I am doing something sort of similar... I have created a
> stored procedure that takes in a couple of parameters and
> passes them to the database and creates a table. I then
> have a query that selects the data from the table. I
> created two seperate datasets one for the stored
> procedure and one for the select statement and it seems
> to be working. I'm not sure how you tell it what to
> execute first but another report writer here is doing the
> same but she had to increase the timeout to give the
> stored procedure a chance to finish otherwise it was
> throwing errors. Sorry I'm not much help but I'm curious
> if you've tried to use the parent group within the group
> that is suppose to recursively search in a parent-child
> relationship? I have the same exact thing to do that you
> are doing and I would love to hear any lessons learned.
> Thanks!!
> >--Original Message--
> >I'm having the most difficult time trying to generate a
> report that
> >first calls a stored procedure and then retrieves the
> data produced
> >from it.
> >
> >I get the error, "An error has occurred during report
> >processing...query execution failed for data set
> dsOrgs...Must declare
> >the variable @.INum" when I try to run the report.
> >
> >The dataset below (dsOrgs) first calls a stored procedure
> >(SV_GetSubordinates) that populates a table with
> hierarchical data.
> >The second part (the Select statement) then retrieves
> the data
> >produced by the stored procedure. I have no problem
> running this set
> >of SQL statements in the Data view of the Reporting
> Services Report
> >Designer.
> >
> >EXEC SV_GetSubordinates @.INum,'Groups',@.OrgNum
> >SELECT v_Orgs.*
> >FROM v_Orgs, SVSiblings
> >WHERE
> > v_Orgs.INum = @.INum AND
> > v_Orgs.INum = SVSiblings.INum AND
> > v_Orgs.OrgNum = SVSiblings.Num
> >
> >By the way, @.INum is a parameter that will be passed to
> the report in
> >a URL string eventually. But for now, I have to use
> both the Preview
> >capability of the Report Designer and the Report Manager
> rendering
> >engine to test out my report.
> >
> >I have another dataset that gets the @.OrgNum parameter
> value from a
> >selection in a drop down in my report. Here is the
> query for that
> >data set...
> >
> >SELECT
> > NULL AS OrgNum,
> > '-- ALL Orgs --' AS [Description]
> >FROM SVGroupDefs
> >WHERE
> > INum = @.INum
> >UNION
> >SELECT
> > OrgNum,
> > [Description]
> >FROM SVGroupDefs
> >WHERE
> > INum = @.INum
> >ORDER BY [Description]
> >
> >As you can see, I'm using @.INum in this dataset first so
> I can
> >populate my drop down list. When the user selects an
> Organization
> >from the drop down list, the selection returns the value
> for the
> >parameter @.OrgNum, which is used in my dsOrgs dataset
> along with @.INum
> >to retrieve the hierarchical data for my report.
> >
> >You may be asking why I need to get hierarchical data
> when the table
> >object in the report designer uses a parent-child
> relationship. The
> >reason why I'm going through all this pain is because I
> need to
> >recursively get all the children from a starting parent
> level, which
> >@.OrgNum supplies. SQL Server does not natively support
> a way to
> >recursively get all the children in a hierarchy. The
> only way to do
> >this is to run through my stored procedure, which
> recursively calls
> >itself and then populates a table with the child OrgNum
> values
> >(fortunately Yukon has solved this recursive nightmare).
> >
> >Anyway, how can I generate my report when the error
> states I must
> >first declare @.INum?
> >.
> >|||In case anyone cares, I solved this problem myself.
The problem was related to a dataset that first calls a stored
procedure to populate a table with recursive data and then runs a
select statement to retrieve a set of data filtered by the recursive
data. What was happening was that Reporting Services was erroring
because my select was trying to fire before my stored procedure
finished. I ended up gettting a "Must declare variable" error (among
other things).
The trick is to make the select statement "wait" for the stored
procedure. How do you do this, you ask? It's simple.
You have to declare a variable and then set the execution of the
stored procedure to the variable. The select statement that follows
has to wait for the variable above to get populated with a "0", which
means the stored procedure executed properly. Then the select will
fire.
Here's a sample dataset query that uses the variable wait method:
DECLARE @.ResultValue INT
EXEC @.ResultNum = SV_GetSubordinates @.INum,'Goals',@.GoalNum
SELECT *
FROM v_Goals
WHERE
GoalNum IN (
SELECT Num
FROM SVSiblings
WHERE
INum = @.INum AND
Type = 'Goals' AND
UserID = USER_ID())
See the part about "EXEC @.ResultNum = "? That's the key to avoiding
the "Must declare variable" error I encountered and the misfiring of
the select statement. The select part of the query waits for
@.ResultNum to be populated with a "0" before getting run.
steve.pantazis@.salesviz.com (Steve Pantazis) wrote in message news:<45c5a039.0409032037.1e978da4@.posting.google.com>...
> Melissa,
> Reporting Services does a fine job handling parent-child sets of
> data in tables and what not...but that's assuming you have a data set
> with all the data you want. What I have is an entire hierarchy, of
> which, only one part I might want to retrieve in the data set for the
> report (e.g. pulling back a region and their branches vs. the whole
> entire organization). So, the whole problem here is about retrieving
> the data and not about how Reporting Services will handle it after the
> data is retrieved.
> With that being said, I've already tried to break out the stored
> procedure that gets all the children vs. the SQL query that retrieves
> the data produced by the stored procedure into two separate data sets.
> But how would Reporting Services know when the stored procedure has
> finished in order to run the second query, which retrieves the data?
> By arbitrarily setting a timeout? I find that method too unreliable.
> I've already broken out my stored procedure and select statement into
> two data sets, but that doesn't work.
> So, what else have I done? I've tried to do a recursive SQL
> function, but to no avail (functions can't recursive do selects of
> data), a recursive stored procedure with a varying output parameter
> (Reporting Services allows only 1 value per parameter for the current
> release), but to no avail, setting the output of my stored procedure
> to a temp table (can't seem to get that to work), but to no avail,
> using a global temp table within my stored procedure (doesn't work
> because the stored procedure recursively calls itself and you can only
> declare the global temp table once), but to no avail, and a bunch of
> other techniques in order to recursively grab all the children for the
> region I select for my data set. The Yukon release of SQL Server will
> solve my problem, because I will be able to execute a single
> expression and retrieve the recursive data I need in a single
> operation...but I need something in the meantime. (By the way, Oracle
> already supports recursion with their Connect method).
> So, I ended up having to pair the stored procedure with my select in
> the same dataset in order to 1) generate a list of child values and 2)
> retrieve that list of values AFTER they are generated. The dataset
> refreshes no problem, but I get that stupid "need to declare @.INum
> first" error, which I can't get rid of...it's so frustrating. I wish
> I could speak to one of the Reporting Services developers over the
> phone and figure this out.
>
> "Melissa" <anonymous@.discussions.microsoft.com> wrote in message news:<028801c491fd$3c204e00$a401280a@.phx.gbl>...
> > Hi Steve,
> > I am doing something sort of similar... I have created a
> > stored procedure that takes in a couple of parameters and
> > passes them to the database and creates a table. I then
> > have a query that selects the data from the table. I
> > created two seperate datasets one for the stored
> > procedure and one for the select statement and it seems
> > to be working. I'm not sure how you tell it what to
> > execute first but another report writer here is doing the
> > same but she had to increase the timeout to give the
> > stored procedure a chance to finish otherwise it was
> > throwing errors. Sorry I'm not much help but I'm curious
> > if you've tried to use the parent group within the group
> > that is suppose to recursively search in a parent-child
> > relationship? I have the same exact thing to do that you
> > are doing and I would love to hear any lessons learned.
> > Thanks!!
> > >--Original Message--
> > >I'm having the most difficult time trying to generate a
> report that
> > >first calls a stored procedure and then retrieves the
> data produced
> > >from it.
> > >
> > >I get the error, "An error has occurred during report
> > >processing...query execution failed for data set
> dsOrgs...Must declare
> > >the variable @.INum" when I try to run the report.
> > >
> > >The dataset below (dsOrgs) first calls a stored procedure
> > >(SV_GetSubordinates) that populates a table with
> hierarchical data.
> > >The second part (the Select statement) then retrieves
> the data
> > >produced by the stored procedure. I have no problem
> running this set
> > >of SQL statements in the Data view of the Reporting
> Services Report
> > >Designer.
> > >
> > >EXEC SV_GetSubordinates @.INum,'Groups',@.OrgNum
> > >SELECT v_Orgs.*
> > >FROM v_Orgs, SVSiblings
> > >WHERE
> > > v_Orgs.INum = @.INum AND
> > > v_Orgs.INum = SVSiblings.INum AND
> > > v_Orgs.OrgNum = SVSiblings.Num
> > >
> > >By the way, @.INum is a parameter that will be passed to
> the report in
> > >a URL string eventually. But for now, I have to use
> both the Preview
> > >capability of the Report Designer and the Report Manager
> rendering
> > >engine to test out my report.
> > >
> > >I have another dataset that gets the @.OrgNum parameter
> value from a
> > >selection in a drop down in my report. Here is the
> query for that
> > >data set...
> > >
> > >SELECT
> > > NULL AS OrgNum,
> > > '-- ALL Orgs --' AS [Description]
> > >FROM SVGroupDefs
> > >WHERE
> > > INum = @.INum
> > >UNION
> > >SELECT
> > > OrgNum,
> > > [Description]
> > >FROM SVGroupDefs
> > >WHERE
> > > INum = @.INum
> > >ORDER BY [Description]
> > >
> > >As you can see, I'm using @.INum in this dataset first so
> I can
> > >populate my drop down list. When the user selects an
> Organization
> > >from the drop down list, the selection returns the value
> for the
> > >parameter @.OrgNum, which is used in my dsOrgs dataset
> along with @.INum
> > >to retrieve the hierarchical data for my report.
> > >
> > >You may be asking why I need to get hierarchical data
> when the table
> > >object in the report designer uses a parent-child
> relationship. The
> > >reason why I'm going through all this pain is because I
> need to
> > >recursively get all the children from a starting parent
> level, which
> > >@.OrgNum supplies. SQL Server does not natively support
> a way to
> > >recursively get all the children in a hierarchy. The
> only way to do
> > >this is to run through my stored procedure, which
> recursively calls
> > >itself and then populates a table with the child OrgNum
> values
> > >(fortunately Yukon has solved this recursive nightmare).
> > >
> > >Anyway, how can I generate my report when the error
> states I must
> > >first declare @.INum?
> > >.
> > >
first calls a stored procedure and then retrieves the data produced
from it.
I get the error, "An error has occurred during report
processing...query execution failed for data set dsOrgs...Must declare
the variable @.INum" when I try to run the report.
The dataset below (dsOrgs) first calls a stored procedure
(SV_GetSubordinates) that populates a table with hierarchical data.
The second part (the Select statement) then retrieves the data
produced by the stored procedure. I have no problem running this set
of SQL statements in the Data view of the Reporting Services Report
Designer.
EXEC SV_GetSubordinates @.INum,'Groups',@.OrgNum
SELECT v_Orgs.*
FROM v_Orgs, SVSiblings
WHERE
v_Orgs.INum = @.INum AND
v_Orgs.INum = SVSiblings.INum AND
v_Orgs.OrgNum = SVSiblings.Num
By the way, @.INum is a parameter that will be passed to the report in
a URL string eventually. But for now, I have to use both the Preview
capability of the Report Designer and the Report Manager rendering
engine to test out my report.
I have another dataset that gets the @.OrgNum parameter value from a
selection in a drop down in my report. Here is the query for that
data set...
SELECT
NULL AS OrgNum,
'-- ALL Orgs --' AS [Description]
FROM SVGroupDefs
WHERE
INum = @.INum
UNION
SELECT
OrgNum,
[Description]
FROM SVGroupDefs
WHERE
INum = @.INum
ORDER BY [Description]
As you can see, I'm using @.INum in this dataset first so I can
populate my drop down list. When the user selects an Organization
from the drop down list, the selection returns the value for the
parameter @.OrgNum, which is used in my dsOrgs dataset along with @.INum
to retrieve the hierarchical data for my report.
You may be asking why I need to get hierarchical data when the table
object in the report designer uses a parent-child relationship. The
reason why I'm going through all this pain is because I need to
recursively get all the children from a starting parent level, which
@.OrgNum supplies. SQL Server does not natively support a way to
recursively get all the children in a hierarchy. The only way to do
this is to run through my stored procedure, which recursively calls
itself and then populates a table with the child OrgNum values
(fortunately Yukon has solved this recursive nightmare).
Anyway, how can I generate my report when the error states I must
first declare @.INum?I guess thats because the SPs run independently.
BTW have you considered using cursors in your SP?
>--Original Message--
>I'm having the most difficult time trying to generate a
report that
>first calls a stored procedure and then retrieves the
data produced
>from it.
>I get the error, "An error has occurred during report
>processing...query execution failed for data set
dsOrgs...Must declare
>the variable @.INum" when I try to run the report.
>The dataset below (dsOrgs) first calls a stored procedure
>(SV_GetSubordinates) that populates a table with
hierarchical data.
>The second part (the Select statement) then retrieves the
data
>produced by the stored procedure. I have no problem
running this set
>of SQL statements in the Data view of the Reporting
Services Report
>Designer.
>EXEC SV_GetSubordinates @.INum,'Groups',@.OrgNum
>SELECT v_Orgs.*
>FROM v_Orgs, SVSiblings
>WHERE
> v_Orgs.INum = @.INum AND
> v_Orgs.INum = SVSiblings.INum AND
> v_Orgs.OrgNum = SVSiblings.Num
>By the way, @.INum is a parameter that will be passed to
the report in
>a URL string eventually. But for now, I have to use both
the Preview
>capability of the Report Designer and the Report Manager
rendering
>engine to test out my report.
>I have another dataset that gets the @.OrgNum parameter
value from a
>selection in a drop down in my report. Here is the query
for that
>data set...
>SELECT
> NULL AS OrgNum,
> '-- ALL Orgs --' AS [Description]
>FROM SVGroupDefs
>WHERE
> INum = @.INum
>UNION
>SELECT
> OrgNum,
> [Description]
>FROM SVGroupDefs
>WHERE
> INum = @.INum
>ORDER BY [Description]
>As you can see, I'm using @.INum in this dataset first so
I can
>populate my drop down list. When the user selects an
Organization
>from the drop down list, the selection returns the value
for the
>parameter @.OrgNum, which is used in my dsOrgs dataset
along with @.INum
>to retrieve the hierarchical data for my report.
>You may be asking why I need to get hierarchical data
when the table
>object in the report designer uses a parent-child
relationship. The
>reason why I'm going through all this pain is because I
need to
>recursively get all the children from a starting parent
level, which
>@.OrgNum supplies. SQL Server does not natively support a
way to
>recursively get all the children in a hierarchy. The
only way to do
>this is to run through my stored procedure, which
recursively calls
>itself and then populates a table with the child OrgNum
values
>(fortunately Yukon has solved this recursive nightmare).
>Anyway, how can I generate my report when the error
states I must
>first declare @.INum?
>.
>|||Ravi, the stored procedure must call itself recursively as it gets the
children for each parent. For example, here's my hierarchy
OrgNum ParentOrgNum
1001 NULL
1002 1001
1003 1002
1004 1002
1005 1001
1006 1005
1007 1006
1008 1006
If I want all the children for OrgNum 1005, the sproc first runs and
gets OrgNum 1006 as a child. Then 1006 becomes the parent and the
sproc calls itself to get all the children for 1006, which are 1007
and 1008. When the sproc tries to get their children, there are no
more, and the sproc terminates. So, I end up with 1006, 1007, and
1008 as children for 1005. During each iteration of the sproc, I
insert the child OrgNum values into a table.
So, with my dsOrgs dataset, I first run the sproc, which does the
stuff above. Then I run a select statement which retrieves all the
children from the table I populated from my sproc. All that works
fine when I run everything in the data area of Reporting Services'
Report Builder.
But that's not the problem. The problem is that I get an error
stating that I have to declare a variable, @.INum. That's what my
first message covers in detail, and the problem to which I'm seeking a
solution.
"Ravi" <ravikantkv@.rediffmail.com> wrote in message news:<549501c49175$e1e4dcd0$a601280a@.phx.gbl>...
> I guess thats because the SPs run independently.
> BTW have you considered using cursors in your SP?
> >--Original Message--
> >I'm having the most difficult time trying to generate a
> report that
> >first calls a stored procedure and then retrieves the
> data produced
> >from it.
> >
> >I get the error, "An error has occurred during report
> >processing...query execution failed for data set
> dsOrgs...Must declare
> >the variable @.INum" when I try to run the report.
> >
> >The dataset below (dsOrgs) first calls a stored procedure
> >(SV_GetSubordinates) that populates a table with
> hierarchical data.
> >The second part (the Select statement) then retrieves the
> data
> >produced by the stored procedure. I have no problem
> running this set
> >of SQL statements in the Data view of the Reporting
> Services Report
> >Designer.
> >
> >EXEC SV_GetSubordinates @.INum,'Groups',@.OrgNum
> >SELECT v_Orgs.*
> >FROM v_Orgs, SVSiblings
> >WHERE
> > v_Orgs.INum = @.INum AND
> > v_Orgs.INum = SVSiblings.INum AND
> > v_Orgs.OrgNum = SVSiblings.Num
> >
> >By the way, @.INum is a parameter that will be passed to
> the report in
> >a URL string eventually. But for now, I have to use both
> the Preview
> >capability of the Report Designer and the Report Manager
> rendering
> >engine to test out my report.
> >
> >I have another dataset that gets the @.OrgNum parameter
> value from a
> >selection in a drop down in my report. Here is the query
> for that
> >data set...
> >
> >SELECT
> > NULL AS OrgNum,
> > '-- ALL Orgs --' AS [Description]
> >FROM SVGroupDefs
> >WHERE
> > INum = @.INum
> >UNION
> >SELECT
> > OrgNum,
> > [Description]
> >FROM SVGroupDefs
> >WHERE
> > INum = @.INum
> >ORDER BY [Description]
> >
> >As you can see, I'm using @.INum in this dataset first so
> I can
> >populate my drop down list. When the user selects an
> Organization
> >from the drop down list, the selection returns the value
> for the
> >parameter @.OrgNum, which is used in my dsOrgs dataset
> along with @.INum
> >to retrieve the hierarchical data for my report.
> >
> >You may be asking why I need to get hierarchical data
> when the table
> >object in the report designer uses a parent-child
> relationship. The
> >reason why I'm going through all this pain is because I
> need to
> >recursively get all the children from a starting parent
> level, which
> >@.OrgNum supplies. SQL Server does not natively support a
> way to
> >recursively get all the children in a hierarchy. The
> only way to do
> >this is to run through my stored procedure, which
> recursively calls
> >itself and then populates a table with the child OrgNum
> values
> >(fortunately Yukon has solved this recursive nightmare).
> >
> >Anyway, how can I generate my report when the error
> states I must
> >first declare @.INum?
> >.
> >|||Hi Steve,
I am doing something sort of similar... I have created a
stored procedure that takes in a couple of parameters and
passes them to the database and creates a table. I then
have a query that selects the data from the table. I
created two seperate datasets one for the stored
procedure and one for the select statement and it seems
to be working. I'm not sure how you tell it what to
execute first but another report writer here is doing the
same but she had to increase the timeout to give the
stored procedure a chance to finish otherwise it was
throwing errors. Sorry I'm not much help but I'm curious
if you've tried to use the parent group within the group
that is suppose to recursively search in a parent-child
relationship? I have the same exact thing to do that you
are doing and I would love to hear any lessons learned.
Thanks!!
>--Original Message--
>I'm having the most difficult time trying to generate a
report that
>first calls a stored procedure and then retrieves the
data produced
>from it.
>I get the error, "An error has occurred during report
>processing...query execution failed for data set
dsOrgs...Must declare
>the variable @.INum" when I try to run the report.
>The dataset below (dsOrgs) first calls a stored procedure
>(SV_GetSubordinates) that populates a table with
hierarchical data.
>The second part (the Select statement) then retrieves
the data
>produced by the stored procedure. I have no problem
running this set
>of SQL statements in the Data view of the Reporting
Services Report
>Designer.
>EXEC SV_GetSubordinates @.INum,'Groups',@.OrgNum
>SELECT v_Orgs.*
>FROM v_Orgs, SVSiblings
>WHERE
> v_Orgs.INum = @.INum AND
> v_Orgs.INum = SVSiblings.INum AND
> v_Orgs.OrgNum = SVSiblings.Num
>By the way, @.INum is a parameter that will be passed to
the report in
>a URL string eventually. But for now, I have to use
both the Preview
>capability of the Report Designer and the Report Manager
rendering
>engine to test out my report.
>I have another dataset that gets the @.OrgNum parameter
value from a
>selection in a drop down in my report. Here is the
query for that
>data set...
>SELECT
> NULL AS OrgNum,
> '-- ALL Orgs --' AS [Description]
>FROM SVGroupDefs
>WHERE
> INum = @.INum
>UNION
>SELECT
> OrgNum,
> [Description]
>FROM SVGroupDefs
>WHERE
> INum = @.INum
>ORDER BY [Description]
>As you can see, I'm using @.INum in this dataset first so
I can
>populate my drop down list. When the user selects an
Organization
>from the drop down list, the selection returns the value
for the
>parameter @.OrgNum, which is used in my dsOrgs dataset
along with @.INum
>to retrieve the hierarchical data for my report.
>You may be asking why I need to get hierarchical data
when the table
>object in the report designer uses a parent-child
relationship. The
>reason why I'm going through all this pain is because I
need to
>recursively get all the children from a starting parent
level, which
>@.OrgNum supplies. SQL Server does not natively support
a way to
>recursively get all the children in a hierarchy. The
only way to do
>this is to run through my stored procedure, which
recursively calls
>itself and then populates a table with the child OrgNum
values
>(fortunately Yukon has solved this recursive nightmare).
>Anyway, how can I generate my report when the error
states I must
>first declare @.INum?
>.
>|||Melissa,
Reporting Services does a fine job handling parent-child sets of
data in tables and what not...but that's assuming you have a data set
with all the data you want. What I have is an entire hierarchy, of
which, only one part I might want to retrieve in the data set for the
report (e.g. pulling back a region and their branches vs. the whole
entire organization). So, the whole problem here is about retrieving
the data and not about how Reporting Services will handle it after the
data is retrieved.
With that being said, I've already tried to break out the stored
procedure that gets all the children vs. the SQL query that retrieves
the data produced by the stored procedure into two separate data sets.
But how would Reporting Services know when the stored procedure has
finished in order to run the second query, which retrieves the data?
By arbitrarily setting a timeout? I find that method too unreliable.
I've already broken out my stored procedure and select statement into
two data sets, but that doesn't work.
So, what else have I done? I've tried to do a recursive SQL
function, but to no avail (functions can't recursive do selects of
data), a recursive stored procedure with a varying output parameter
(Reporting Services allows only 1 value per parameter for the current
release), but to no avail, setting the output of my stored procedure
to a temp table (can't seem to get that to work), but to no avail,
using a global temp table within my stored procedure (doesn't work
because the stored procedure recursively calls itself and you can only
declare the global temp table once), but to no avail, and a bunch of
other techniques in order to recursively grab all the children for the
region I select for my data set. The Yukon release of SQL Server will
solve my problem, because I will be able to execute a single
expression and retrieve the recursive data I need in a single
operation...but I need something in the meantime. (By the way, Oracle
already supports recursion with their Connect method).
So, I ended up having to pair the stored procedure with my select in
the same dataset in order to 1) generate a list of child values and 2)
retrieve that list of values AFTER they are generated. The dataset
refreshes no problem, but I get that stupid "need to declare @.INum
first" error, which I can't get rid of...it's so frustrating. I wish
I could speak to one of the Reporting Services developers over the
phone and figure this out.
"Melissa" <anonymous@.discussions.microsoft.com> wrote in message news:<028801c491fd$3c204e00$a401280a@.phx.gbl>...
> Hi Steve,
> I am doing something sort of similar... I have created a
> stored procedure that takes in a couple of parameters and
> passes them to the database and creates a table. I then
> have a query that selects the data from the table. I
> created two seperate datasets one for the stored
> procedure and one for the select statement and it seems
> to be working. I'm not sure how you tell it what to
> execute first but another report writer here is doing the
> same but she had to increase the timeout to give the
> stored procedure a chance to finish otherwise it was
> throwing errors. Sorry I'm not much help but I'm curious
> if you've tried to use the parent group within the group
> that is suppose to recursively search in a parent-child
> relationship? I have the same exact thing to do that you
> are doing and I would love to hear any lessons learned.
> Thanks!!
> >--Original Message--
> >I'm having the most difficult time trying to generate a
> report that
> >first calls a stored procedure and then retrieves the
> data produced
> >from it.
> >
> >I get the error, "An error has occurred during report
> >processing...query execution failed for data set
> dsOrgs...Must declare
> >the variable @.INum" when I try to run the report.
> >
> >The dataset below (dsOrgs) first calls a stored procedure
> >(SV_GetSubordinates) that populates a table with
> hierarchical data.
> >The second part (the Select statement) then retrieves
> the data
> >produced by the stored procedure. I have no problem
> running this set
> >of SQL statements in the Data view of the Reporting
> Services Report
> >Designer.
> >
> >EXEC SV_GetSubordinates @.INum,'Groups',@.OrgNum
> >SELECT v_Orgs.*
> >FROM v_Orgs, SVSiblings
> >WHERE
> > v_Orgs.INum = @.INum AND
> > v_Orgs.INum = SVSiblings.INum AND
> > v_Orgs.OrgNum = SVSiblings.Num
> >
> >By the way, @.INum is a parameter that will be passed to
> the report in
> >a URL string eventually. But for now, I have to use
> both the Preview
> >capability of the Report Designer and the Report Manager
> rendering
> >engine to test out my report.
> >
> >I have another dataset that gets the @.OrgNum parameter
> value from a
> >selection in a drop down in my report. Here is the
> query for that
> >data set...
> >
> >SELECT
> > NULL AS OrgNum,
> > '-- ALL Orgs --' AS [Description]
> >FROM SVGroupDefs
> >WHERE
> > INum = @.INum
> >UNION
> >SELECT
> > OrgNum,
> > [Description]
> >FROM SVGroupDefs
> >WHERE
> > INum = @.INum
> >ORDER BY [Description]
> >
> >As you can see, I'm using @.INum in this dataset first so
> I can
> >populate my drop down list. When the user selects an
> Organization
> >from the drop down list, the selection returns the value
> for the
> >parameter @.OrgNum, which is used in my dsOrgs dataset
> along with @.INum
> >to retrieve the hierarchical data for my report.
> >
> >You may be asking why I need to get hierarchical data
> when the table
> >object in the report designer uses a parent-child
> relationship. The
> >reason why I'm going through all this pain is because I
> need to
> >recursively get all the children from a starting parent
> level, which
> >@.OrgNum supplies. SQL Server does not natively support
> a way to
> >recursively get all the children in a hierarchy. The
> only way to do
> >this is to run through my stored procedure, which
> recursively calls
> >itself and then populates a table with the child OrgNum
> values
> >(fortunately Yukon has solved this recursive nightmare).
> >
> >Anyway, how can I generate my report when the error
> states I must
> >first declare @.INum?
> >.
> >|||In case anyone cares, I solved this problem myself.
The problem was related to a dataset that first calls a stored
procedure to populate a table with recursive data and then runs a
select statement to retrieve a set of data filtered by the recursive
data. What was happening was that Reporting Services was erroring
because my select was trying to fire before my stored procedure
finished. I ended up gettting a "Must declare variable" error (among
other things).
The trick is to make the select statement "wait" for the stored
procedure. How do you do this, you ask? It's simple.
You have to declare a variable and then set the execution of the
stored procedure to the variable. The select statement that follows
has to wait for the variable above to get populated with a "0", which
means the stored procedure executed properly. Then the select will
fire.
Here's a sample dataset query that uses the variable wait method:
DECLARE @.ResultValue INT
EXEC @.ResultNum = SV_GetSubordinates @.INum,'Goals',@.GoalNum
SELECT *
FROM v_Goals
WHERE
GoalNum IN (
SELECT Num
FROM SVSiblings
WHERE
INum = @.INum AND
Type = 'Goals' AND
UserID = USER_ID())
See the part about "EXEC @.ResultNum = "? That's the key to avoiding
the "Must declare variable" error I encountered and the misfiring of
the select statement. The select part of the query waits for
@.ResultNum to be populated with a "0" before getting run.
steve.pantazis@.salesviz.com (Steve Pantazis) wrote in message news:<45c5a039.0409032037.1e978da4@.posting.google.com>...
> Melissa,
> Reporting Services does a fine job handling parent-child sets of
> data in tables and what not...but that's assuming you have a data set
> with all the data you want. What I have is an entire hierarchy, of
> which, only one part I might want to retrieve in the data set for the
> report (e.g. pulling back a region and their branches vs. the whole
> entire organization). So, the whole problem here is about retrieving
> the data and not about how Reporting Services will handle it after the
> data is retrieved.
> With that being said, I've already tried to break out the stored
> procedure that gets all the children vs. the SQL query that retrieves
> the data produced by the stored procedure into two separate data sets.
> But how would Reporting Services know when the stored procedure has
> finished in order to run the second query, which retrieves the data?
> By arbitrarily setting a timeout? I find that method too unreliable.
> I've already broken out my stored procedure and select statement into
> two data sets, but that doesn't work.
> So, what else have I done? I've tried to do a recursive SQL
> function, but to no avail (functions can't recursive do selects of
> data), a recursive stored procedure with a varying output parameter
> (Reporting Services allows only 1 value per parameter for the current
> release), but to no avail, setting the output of my stored procedure
> to a temp table (can't seem to get that to work), but to no avail,
> using a global temp table within my stored procedure (doesn't work
> because the stored procedure recursively calls itself and you can only
> declare the global temp table once), but to no avail, and a bunch of
> other techniques in order to recursively grab all the children for the
> region I select for my data set. The Yukon release of SQL Server will
> solve my problem, because I will be able to execute a single
> expression and retrieve the recursive data I need in a single
> operation...but I need something in the meantime. (By the way, Oracle
> already supports recursion with their Connect method).
> So, I ended up having to pair the stored procedure with my select in
> the same dataset in order to 1) generate a list of child values and 2)
> retrieve that list of values AFTER they are generated. The dataset
> refreshes no problem, but I get that stupid "need to declare @.INum
> first" error, which I can't get rid of...it's so frustrating. I wish
> I could speak to one of the Reporting Services developers over the
> phone and figure this out.
>
> "Melissa" <anonymous@.discussions.microsoft.com> wrote in message news:<028801c491fd$3c204e00$a401280a@.phx.gbl>...
> > Hi Steve,
> > I am doing something sort of similar... I have created a
> > stored procedure that takes in a couple of parameters and
> > passes them to the database and creates a table. I then
> > have a query that selects the data from the table. I
> > created two seperate datasets one for the stored
> > procedure and one for the select statement and it seems
> > to be working. I'm not sure how you tell it what to
> > execute first but another report writer here is doing the
> > same but she had to increase the timeout to give the
> > stored procedure a chance to finish otherwise it was
> > throwing errors. Sorry I'm not much help but I'm curious
> > if you've tried to use the parent group within the group
> > that is suppose to recursively search in a parent-child
> > relationship? I have the same exact thing to do that you
> > are doing and I would love to hear any lessons learned.
> > Thanks!!
> > >--Original Message--
> > >I'm having the most difficult time trying to generate a
> report that
> > >first calls a stored procedure and then retrieves the
> data produced
> > >from it.
> > >
> > >I get the error, "An error has occurred during report
> > >processing...query execution failed for data set
> dsOrgs...Must declare
> > >the variable @.INum" when I try to run the report.
> > >
> > >The dataset below (dsOrgs) first calls a stored procedure
> > >(SV_GetSubordinates) that populates a table with
> hierarchical data.
> > >The second part (the Select statement) then retrieves
> the data
> > >produced by the stored procedure. I have no problem
> running this set
> > >of SQL statements in the Data view of the Reporting
> Services Report
> > >Designer.
> > >
> > >EXEC SV_GetSubordinates @.INum,'Groups',@.OrgNum
> > >SELECT v_Orgs.*
> > >FROM v_Orgs, SVSiblings
> > >WHERE
> > > v_Orgs.INum = @.INum AND
> > > v_Orgs.INum = SVSiblings.INum AND
> > > v_Orgs.OrgNum = SVSiblings.Num
> > >
> > >By the way, @.INum is a parameter that will be passed to
> the report in
> > >a URL string eventually. But for now, I have to use
> both the Preview
> > >capability of the Report Designer and the Report Manager
> rendering
> > >engine to test out my report.
> > >
> > >I have another dataset that gets the @.OrgNum parameter
> value from a
> > >selection in a drop down in my report. Here is the
> query for that
> > >data set...
> > >
> > >SELECT
> > > NULL AS OrgNum,
> > > '-- ALL Orgs --' AS [Description]
> > >FROM SVGroupDefs
> > >WHERE
> > > INum = @.INum
> > >UNION
> > >SELECT
> > > OrgNum,
> > > [Description]
> > >FROM SVGroupDefs
> > >WHERE
> > > INum = @.INum
> > >ORDER BY [Description]
> > >
> > >As you can see, I'm using @.INum in this dataset first so
> I can
> > >populate my drop down list. When the user selects an
> Organization
> > >from the drop down list, the selection returns the value
> for the
> > >parameter @.OrgNum, which is used in my dsOrgs dataset
> along with @.INum
> > >to retrieve the hierarchical data for my report.
> > >
> > >You may be asking why I need to get hierarchical data
> when the table
> > >object in the report designer uses a parent-child
> relationship. The
> > >reason why I'm going through all this pain is because I
> need to
> > >recursively get all the children from a starting parent
> level, which
> > >@.OrgNum supplies. SQL Server does not natively support
> a way to
> > >recursively get all the children in a hierarchy. The
> only way to do
> > >this is to run through my stored procedure, which
> recursively calls
> > >itself and then populates a table with the child OrgNum
> values
> > >(fortunately Yukon has solved this recursive nightmare).
> > >
> > >Anyway, how can I generate my report when the error
> states I must
> > >first declare @.INum?
> > >.
> > >
"Multiple-Step operation cannot be generated Check each status value" Error
Hi All,
I have a field 'Rowguid' of type uniqueidentifier in a table. This field is the last field in the table. In this case if I update a record through the application I don't get any error. Suppose if there are additional fields after the field Rowguid I get the error "Multiple-Step operation cannot be generated Check each status value"
For your reference I have used the following statement to add the RowGuid field
Alter table <tablename>
Add RowGuid uniqueidentifier ROWGUIDCOL NOT NULL Default (newid())
Can anyone please help me.
Thanks
SatheshPost your update statement, and any trigger code on this table.|||Where's Brett when we need him?
Logically, columns don't have any order within a table. It so happens that the current implementation of Microsoft SQL Server has a physical order, but that's implementation dependant. Relying on a column order (logical or physical) is a bug waiting to happen!
The problem is that you are not specifying a column list for your INSERT statement, you are relying on an implied order. Once you add columns so that the rowid is no longer the last column, your assumed order bytes you. If you specify the columns via a column list, this won't be a problem.DROP TABLE BadExample
GO
CREATE TABLE BadExample (
comment NVARCHAR(25)
)
GO
INSERT INTO BadExample
SELECT 'Simple case'
SELECT * FROM BadExample
GO
ALTER TABLE BadExample
ADD RowGuid uniqueidentifier ROWGUIDCOL NOT NULL
DEFAULT (newid())
INSERT INTO BadExample (comment)
SELECT 'Not as simple example'
SELECT * FROM BadExample
GO
ALTER TABLE BadExample
ADD myDate DATETIME NOT NULL
DEFAULT GetDate()
GO
INSERT INTO BadExample (comment, myDate)
SELECT 'This works!', '1950-01-02'
SELECT * FROM BadExample
GO
INSERT INTO BadExample
SELECT 'This fails', '1960-03-04'
SELECT * FROM BadExample-PatP|||The problem is that you are not specifying a column list for your INSERT statement, you are relying on an implied order.How did you come up with that from his post!?
I have a field 'Rowguid' of type uniqueidentifier in a table. This field is the last field in the table. In this case if I update a record through the application I don't get any error. Suppose if there are additional fields after the field Rowguid I get the error "Multiple-Step operation cannot be generated Check each status value"
For your reference I have used the following statement to add the RowGuid field
Alter table <tablename>
Add RowGuid uniqueidentifier ROWGUIDCOL NOT NULL Default (newid())
Can anyone please help me.
Thanks
SatheshPost your update statement, and any trigger code on this table.|||Where's Brett when we need him?
Logically, columns don't have any order within a table. It so happens that the current implementation of Microsoft SQL Server has a physical order, but that's implementation dependant. Relying on a column order (logical or physical) is a bug waiting to happen!
The problem is that you are not specifying a column list for your INSERT statement, you are relying on an implied order. Once you add columns so that the rowid is no longer the last column, your assumed order bytes you. If you specify the columns via a column list, this won't be a problem.DROP TABLE BadExample
GO
CREATE TABLE BadExample (
comment NVARCHAR(25)
)
GO
INSERT INTO BadExample
SELECT 'Simple case'
SELECT * FROM BadExample
GO
ALTER TABLE BadExample
ADD RowGuid uniqueidentifier ROWGUIDCOL NOT NULL
DEFAULT (newid())
INSERT INTO BadExample (comment)
SELECT 'Not as simple example'
SELECT * FROM BadExample
GO
ALTER TABLE BadExample
ADD myDate DATETIME NOT NULL
DEFAULT GetDate()
GO
INSERT INTO BadExample (comment, myDate)
SELECT 'This works!', '1950-01-02'
SELECT * FROM BadExample
GO
INSERT INTO BadExample
SELECT 'This fails', '1960-03-04'
SELECT * FROM BadExample-PatP|||The problem is that you are not specifying a column list for your INSERT statement, you are relying on an implied order.How did you come up with that from his post!?
"Multiple IP address in one SQL server" and "authentication problem with AD"
Never use a multihomed server as a DC. So you need to either remove AD off
of this machine or remove one of the NIC's.
The link below has some info on this and I couldn't find the exact article
but I can guarantee you are going to have constant problems with this setup.
http://support.Microsoft.com/default.aspx?scid=kb;en-us;832478
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sergio Garcia" <sergio.garcia@.ds2.es> wrote in message
news:eQEPzDqUIHA.5980@.TK2MSFTNGP04.phx.gbl...
> Hi all.
> I really don't known where to post because I don't known what is my
> problem, so let me explain.
> I have a SQL Server with two ethernet cards, one used for me, IT
> Department, and other used by normal users.
> Every card is in a separated network (obviously):
> 192.168.1.100 with "db.domain.com" A record in DNS
> 172.16.10.100 with "db.public.domain.com" A record in DNS
> This server is an AD domain computer with "db.domain.com" name, and the DC
> is in 192.168.1.0/24 network.
> I am in troubles when I try to connect with MSrSQL Management Studio using
> db.public.domain.com... I doesn't works, I get an error similar to "Login
> failed for user ''. The user is not associated with a trusted SQL Server
> connection. [CLIENT: 172.16.10.101]"
> If I change db.public.domain.com to a CNAME record pointing to
> db.domain.com it works, but this is not desired because I want users to
> use their network, 172.16.10.0/24.
> Any issues? Any suggestions? Any idea? Please help me if you can
I
> tried to play with Service Principal Names (SPN's), but I can't manage to
> connect with an A record.
> Thanks in advance,
> Sergio
Sorry for the misunderstanding. I don't specifically see what the problem
is, but does this sql server allow SQL Server and AD authentication? If so
have you tried both methods? What about the Event Log what is the error you
are receiving?
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sergio Garcia" <sergio.garcia@.noreply.org> wrote in message
news:%23WIKzQtUIHA.1208@.TK2MSFTNGP03.phx.gbl...[vbcol=seagreen]
> Sorry, I didn't explained well... it is not a DC, it is only a domain
> member.
>
> Paul Bergson [MVP-DS] wrote:
|||Hi
Which domain is machine that you are connecting from in and have you tried
to change it?
Are you using SQL Authentication?
John
"Sergio Garcia" wrote:
> The SQL Server has the next configuration
> _________ _______
> | | | |
> NIC1 --| SQL |-- NIC2 --| DC |
> 10.10.10.1 | | 10.20.10.1 | DNS |
> host.pub.dom.com -- host.dom.com --
> In 10.20.10.0/24 network there is a DC of dom.com domain and a DNS wich
> has both A entries.
> My problem is that in the same SQL Server, I want to connect to
> host.pub.dom.com database server and I can't.
> If I use "host.pub.dom.com" can't connect. KO
> If I use "host.dom.com" I can connect. OK
> If I use "10.10.10.1" address, I can connect. OK
> If I use "10.20.10.1" address, I can connect. OK
> If I change the A record, "host.pub.dom.com", to a CNAME record pointing
> to "host.dom.com"... I can connect.
>
> Also, there is a router linking both networks
> Paul Bergson [MVP-DS] wrote:
>
|||Hi
Who are you logging into the machine as?
John
"Sergio Garcia" wrote:
> That machine is the same SQL Server. I can connect even to "localhost".
> If I use SQL Authentication it works, but with Windows authentication
> doesn't.
> Thanks very much.
> John Bell wrote:
>
|||Hi
If you can connect to host.pub.dom.com as a SQL User it is not the
resolution of the machine that has cause the problem.
If you can connect to host.dom.com as a Windows User host.dom.com\user it is
not the permissions for host.dom.com\user that is the issue.
If you can connect to host.pub.dom.com as a Windows User
host.pub.dom.com\user it is not the permissions for host.dom.com\user that is
the issue.
If you can connect to host.pub.dom.com as a Windows User host.dom.com\user I
suspect that it is something like the trust relationship between the two
domains that is the issue.
John
"Sergio Garcia" wrote:
> Ok, I will try to give you more information
> I am using a user who has permissions enough. It is a domain user, not a
> SQL user.
> Thanks for your help...
>
> John Bell wrote:
>
|||This sql box is not a router and the path host.pub.dom.com is not part of
your domain. The reason the cname works is you have it pointing to the
domain side.
I think you could get this to work if you loaded Routing and Remote Access,
but this is skewed.
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sergio Garcia" <sergio.garcia@.noreply.org> wrote in message
news:47863DDF.6090604@.noreply.org...[vbcol=seagreen]
> The SQL Server has the next configuration
> _________ _______
> | | | |
> NIC1 --| SQL |-- NIC2 --| DC |
> 10.10.10.1 | | 10.20.10.1 | DNS |
> host.pub.dom.com -- host.dom.com --
> In 10.20.10.0/24 network there is a DC of dom.com domain and a DNS wich
> has both A entries.
> My problem is that in the same SQL Server, I want to connect to
> host.pub.dom.com database server and I can't.
> If I use "host.pub.dom.com" can't connect. KO
> If I use "host.dom.com" I can connect. OK
> If I use "10.10.10.1" address, I can connect. OK
> If I use "10.20.10.1" address, I can connect. OK
> If I change the A record, "host.pub.dom.com", to a CNAME record pointing
> to "host.dom.com"... I can connect.
>
> Also, there is a router linking both networks
> Paul Bergson [MVP-DS] wrote:
of this machine or remove one of the NIC's.
The link below has some info on this and I couldn't find the exact article
but I can guarantee you are going to have constant problems with this setup.
http://support.Microsoft.com/default.aspx?scid=kb;en-us;832478
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sergio Garcia" <sergio.garcia@.ds2.es> wrote in message
news:eQEPzDqUIHA.5980@.TK2MSFTNGP04.phx.gbl...
> Hi all.
> I really don't known where to post because I don't known what is my
> problem, so let me explain.
> I have a SQL Server with two ethernet cards, one used for me, IT
> Department, and other used by normal users.
> Every card is in a separated network (obviously):
> 192.168.1.100 with "db.domain.com" A record in DNS
> 172.16.10.100 with "db.public.domain.com" A record in DNS
> This server is an AD domain computer with "db.domain.com" name, and the DC
> is in 192.168.1.0/24 network.
> I am in troubles when I try to connect with MSrSQL Management Studio using
> db.public.domain.com... I doesn't works, I get an error similar to "Login
> failed for user ''. The user is not associated with a trusted SQL Server
> connection. [CLIENT: 172.16.10.101]"
> If I change db.public.domain.com to a CNAME record pointing to
> db.domain.com it works, but this is not desired because I want users to
> use their network, 172.16.10.0/24.
> Any issues? Any suggestions? Any idea? Please help me if you can
I> tried to play with Service Principal Names (SPN's), but I can't manage to
> connect with an A record.
> Thanks in advance,
> Sergio
Sorry for the misunderstanding. I don't specifically see what the problem
is, but does this sql server allow SQL Server and AD authentication? If so
have you tried both methods? What about the Event Log what is the error you
are receiving?
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sergio Garcia" <sergio.garcia@.noreply.org> wrote in message
news:%23WIKzQtUIHA.1208@.TK2MSFTNGP03.phx.gbl...[vbcol=seagreen]
> Sorry, I didn't explained well... it is not a DC, it is only a domain
> member.
>
> Paul Bergson [MVP-DS] wrote:
|||Hi
Which domain is machine that you are connecting from in and have you tried
to change it?
Are you using SQL Authentication?
John
"Sergio Garcia" wrote:
> The SQL Server has the next configuration
> _________ _______
> | | | |
> NIC1 --| SQL |-- NIC2 --| DC |
> 10.10.10.1 | | 10.20.10.1 | DNS |
> host.pub.dom.com -- host.dom.com --
> In 10.20.10.0/24 network there is a DC of dom.com domain and a DNS wich
> has both A entries.
> My problem is that in the same SQL Server, I want to connect to
> host.pub.dom.com database server and I can't.
> If I use "host.pub.dom.com" can't connect. KO
> If I use "host.dom.com" I can connect. OK
> If I use "10.10.10.1" address, I can connect. OK
> If I use "10.20.10.1" address, I can connect. OK
> If I change the A record, "host.pub.dom.com", to a CNAME record pointing
> to "host.dom.com"... I can connect.
>
> Also, there is a router linking both networks
> Paul Bergson [MVP-DS] wrote:
>
|||Hi
Who are you logging into the machine as?
John
"Sergio Garcia" wrote:
> That machine is the same SQL Server. I can connect even to "localhost".
> If I use SQL Authentication it works, but with Windows authentication
> doesn't.
> Thanks very much.
> John Bell wrote:
>
|||Hi
If you can connect to host.pub.dom.com as a SQL User it is not the
resolution of the machine that has cause the problem.
If you can connect to host.dom.com as a Windows User host.dom.com\user it is
not the permissions for host.dom.com\user that is the issue.
If you can connect to host.pub.dom.com as a Windows User
host.pub.dom.com\user it is not the permissions for host.dom.com\user that is
the issue.
If you can connect to host.pub.dom.com as a Windows User host.dom.com\user I
suspect that it is something like the trust relationship between the two
domains that is the issue.
John
"Sergio Garcia" wrote:
> Ok, I will try to give you more information
> I am using a user who has permissions enough. It is a domain user, not a
> SQL user.
> Thanks for your help...
>
> John Bell wrote:
>
|||This sql box is not a router and the path host.pub.dom.com is not part of
your domain. The reason the cname works is you have it pointing to the
domain side.
I think you could get this to work if you loaded Routing and Remote Access,
but this is skewed.
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sergio Garcia" <sergio.garcia@.noreply.org> wrote in message
news:47863DDF.6090604@.noreply.org...[vbcol=seagreen]
> The SQL Server has the next configuration
> _________ _______
> | | | |
> NIC1 --| SQL |-- NIC2 --| DC |
> 10.10.10.1 | | 10.20.10.1 | DNS |
> host.pub.dom.com -- host.dom.com --
> In 10.20.10.0/24 network there is a DC of dom.com domain and a DNS wich
> has both A entries.
> My problem is that in the same SQL Server, I want to connect to
> host.pub.dom.com database server and I can't.
> If I use "host.pub.dom.com" can't connect. KO
> If I use "host.dom.com" I can connect. OK
> If I use "10.10.10.1" address, I can connect. OK
> If I use "10.20.10.1" address, I can connect. OK
> If I change the A record, "host.pub.dom.com", to a CNAME record pointing
> to "host.dom.com"... I can connect.
>
> Also, there is a router linking both networks
> Paul Bergson [MVP-DS] wrote:
"Multiple IP address in one SQL server" and "authentication problem
Hi all.
I really don't known where to post because I don't known what is my
problem, so let me explain.
I have a SQL Server with two ethernet cards, one used for me, IT
Department, and other used by normal users.
Every card is in a separated network (obviously):
192.168.1.100 with "db.domain.com" A record in DNS
172.16.10.100 with "db.public.domain.com" A record in DNS
This server is an AD domain computer with "db.domain.com" name, and the
DC is in 192.168.1.0/24 network.
I am in troubles when I try to connect with MSrSQL Management Studio
using db.public.domain.com... I doesn't works, I get an error similar to
"Login failed for user ''. The user is not associated with a trusted SQL
Server connection. [CLIENT: 172.16.10.101]"
If I change db.public.domain.com to a CNAME record pointing to
db.domain.com it works, but this is not desired because I want users to
use their network, 172.16.10.0/24.
Any issues? Any suggestions? Any idea? Please help me if you can :) I
tried to play with Service Principal Names (SPN's), but I can't manage
to connect with an A record.
Thanks in advance,
SergioNever use a multihomed server as a DC. So you need to either remove AD off
of this machine or remove one of the NIC's.
The link below has some info on this and I couldn't find the exact article
but I can guarantee you are going to have constant problems with this setup.
http://support.Microsoft.com/default.aspx?scid=kb;en-us;832478
--
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sergio Garcia" <sergio.garcia@.ds2.es> wrote in message
news:eQEPzDqUIHA.5980@.TK2MSFTNGP04.phx.gbl...
> Hi all.
> I really don't known where to post because I don't known what is my
> problem, so let me explain.
> I have a SQL Server with two ethernet cards, one used for me, IT
> Department, and other used by normal users.
> Every card is in a separated network (obviously):
> 192.168.1.100 with "db.domain.com" A record in DNS
> 172.16.10.100 with "db.public.domain.com" A record in DNS
> This server is an AD domain computer with "db.domain.com" name, and the DC
> is in 192.168.1.0/24 network.
> I am in troubles when I try to connect with MSrSQL Management Studio using
> db.public.domain.com... I doesn't works, I get an error similar to "Login
> failed for user ''. The user is not associated with a trusted SQL Server
> connection. [CLIENT: 172.16.10.101]"
> If I change db.public.domain.com to a CNAME record pointing to
> db.domain.com it works, but this is not desired because I want users to
> use their network, 172.16.10.0/24.
> Any issues? Any suggestions? Any idea? Please help me if you can :) I
> tried to play with Service Principal Names (SPN's), but I can't manage to
> connect with an A record.
> Thanks in advance,
> Sergio|||Sorry, I didn't explained well... it is not a DC, it is only a domain
member.
Paul Bergson [MVP-DS] wrote:
> Never use a multihomed server as a DC. So you need to either remove AD off
> of this machine or remove one of the NIC's.
> The link below has some info on this and I couldn't find the exact article
> but I can guarantee you are going to have constant problems with this setup.
> http://support.Microsoft.com/default.aspx?scid=kb;en-us;832478
>|||Sorry for the misunderstanding. I don't specifically see what the problem
is, but does this sql server allow SQL Server and AD authentication? If so
have you tried both methods? What about the Event Log what is the error you
are receiving?
--
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sergio Garcia" <sergio.garcia@.noreply.org> wrote in message
news:%23WIKzQtUIHA.1208@.TK2MSFTNGP03.phx.gbl...
> Sorry, I didn't explained well... it is not a DC, it is only a domain
> member.
>
> Paul Bergson [MVP-DS] wrote:
>> Never use a multihomed server as a DC. So you need to either remove AD
>> off of this machine or remove one of the NIC's.
>> The link below has some info on this and I couldn't find the exact
>> article but I can guarantee you are going to have constant problems with
>> this setup.
>> http://support.Microsoft.com/default.aspx?scid=kb;en-us;832478|||The SQL Server has the next configuration
_________ _______
| | | |
NIC1 --| SQL |-- NIC2 --| DC |
10.10.10.1 | | 10.20.10.1 | DNS |
host.pub.dom.com -- host.dom.com --
In 10.20.10.0/24 network there is a DC of dom.com domain and a DNS wich
has both A entries.
My problem is that in the same SQL Server, I want to connect to
host.pub.dom.com database server and I can't.
If I use "host.pub.dom.com" can't connect. KO
If I use "host.dom.com" I can connect. OK
If I use "10.10.10.1" address, I can connect. OK
If I use "10.20.10.1" address, I can connect. OK
If I change the A record, "host.pub.dom.com", to a CNAME record pointing
to "host.dom.com"... I can connect.
Also, there is a router linking both networks
Paul Bergson [MVP-DS] wrote:
> Sorry for the misunderstanding. I don't specifically see what the problem
> is, but does this sql server allow SQL Server and AD authentication? If so
> have you tried both methods? What about the Event Log what is the error you
> are receiving?
>|||The SQL Server has the next configuration
_________ _______
| | | |
NIC1 --| SQL |-- NIC2 --| DC |
10.10.10.1 | | 10.20.10.1 | DNS |
host.pub.dom.com -- host.dom.com --
In 10.20.10.0/24 network there is a DC of dom.com domain and a DNS wich
has both A entries.
My problem is that in the same SQL Server, I want to connect to
host.pub.dom.com database server and I can't.
If I use "host.pub.dom.com" can't connect. KO
If I use "host.dom.com" I can connect. OK
If I use "10.10.10.1" address, I can connect. OK
If I use "10.20.10.1" address, I can connect. OK
If I change the A record, "host.pub.dom.com", to a CNAME record pointing
to "host.dom.com"... I can connect.
Also, there is a router linking both networks
Paul Bergson [MVP-DS] wrote:
> Sorry for the misunderstanding. I don't specifically see what the problem
> is, but does this sql server allow SQL Server and AD authentication? If so
> have you tried both methods? What about the Event Log what is the error you
> are receiving?
>|||This sql box is not a router and the path host.pub.dom.com is not part of
your domain. The reason the cname works is you have it pointing to the
domain side.
I think you could get this to work if you loaded Routing and Remote Access,
but this is skewed.
--
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sergio Garcia" <sergio.garcia@.noreply.org> wrote in message
news:47863DDF.6090604@.noreply.org...
> The SQL Server has the next configuration
> _________ _______
> | | | |
> NIC1 --| SQL |-- NIC2 --| DC |
> 10.10.10.1 | | 10.20.10.1 | DNS |
> host.pub.dom.com -- host.dom.com --
> In 10.20.10.0/24 network there is a DC of dom.com domain and a DNS wich
> has both A entries.
> My problem is that in the same SQL Server, I want to connect to
> host.pub.dom.com database server and I can't.
> If I use "host.pub.dom.com" can't connect. KO
> If I use "host.dom.com" I can connect. OK
> If I use "10.10.10.1" address, I can connect. OK
> If I use "10.20.10.1" address, I can connect. OK
> If I change the A record, "host.pub.dom.com", to a CNAME record pointing
> to "host.dom.com"... I can connect.
>
> Also, there is a router linking both networks
> Paul Bergson [MVP-DS] wrote:
>> Sorry for the misunderstanding. I don't specifically see what the
>> problem is, but does this sql server allow SQL Server and AD
>> authentication? If so have you tried both methods? What about the Event
>> Log what is the error you are receiving?
I really don't known where to post because I don't known what is my
problem, so let me explain.
I have a SQL Server with two ethernet cards, one used for me, IT
Department, and other used by normal users.
Every card is in a separated network (obviously):
192.168.1.100 with "db.domain.com" A record in DNS
172.16.10.100 with "db.public.domain.com" A record in DNS
This server is an AD domain computer with "db.domain.com" name, and the
DC is in 192.168.1.0/24 network.
I am in troubles when I try to connect with MSrSQL Management Studio
using db.public.domain.com... I doesn't works, I get an error similar to
"Login failed for user ''. The user is not associated with a trusted SQL
Server connection. [CLIENT: 172.16.10.101]"
If I change db.public.domain.com to a CNAME record pointing to
db.domain.com it works, but this is not desired because I want users to
use their network, 172.16.10.0/24.
Any issues? Any suggestions? Any idea? Please help me if you can :) I
tried to play with Service Principal Names (SPN's), but I can't manage
to connect with an A record.
Thanks in advance,
SergioNever use a multihomed server as a DC. So you need to either remove AD off
of this machine or remove one of the NIC's.
The link below has some info on this and I couldn't find the exact article
but I can guarantee you are going to have constant problems with this setup.
http://support.Microsoft.com/default.aspx?scid=kb;en-us;832478
--
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sergio Garcia" <sergio.garcia@.ds2.es> wrote in message
news:eQEPzDqUIHA.5980@.TK2MSFTNGP04.phx.gbl...
> Hi all.
> I really don't known where to post because I don't known what is my
> problem, so let me explain.
> I have a SQL Server with two ethernet cards, one used for me, IT
> Department, and other used by normal users.
> Every card is in a separated network (obviously):
> 192.168.1.100 with "db.domain.com" A record in DNS
> 172.16.10.100 with "db.public.domain.com" A record in DNS
> This server is an AD domain computer with "db.domain.com" name, and the DC
> is in 192.168.1.0/24 network.
> I am in troubles when I try to connect with MSrSQL Management Studio using
> db.public.domain.com... I doesn't works, I get an error similar to "Login
> failed for user ''. The user is not associated with a trusted SQL Server
> connection. [CLIENT: 172.16.10.101]"
> If I change db.public.domain.com to a CNAME record pointing to
> db.domain.com it works, but this is not desired because I want users to
> use their network, 172.16.10.0/24.
> Any issues? Any suggestions? Any idea? Please help me if you can :) I
> tried to play with Service Principal Names (SPN's), but I can't manage to
> connect with an A record.
> Thanks in advance,
> Sergio|||Sorry, I didn't explained well... it is not a DC, it is only a domain
member.
Paul Bergson [MVP-DS] wrote:
> Never use a multihomed server as a DC. So you need to either remove AD off
> of this machine or remove one of the NIC's.
> The link below has some info on this and I couldn't find the exact article
> but I can guarantee you are going to have constant problems with this setup.
> http://support.Microsoft.com/default.aspx?scid=kb;en-us;832478
>|||Sorry for the misunderstanding. I don't specifically see what the problem
is, but does this sql server allow SQL Server and AD authentication? If so
have you tried both methods? What about the Event Log what is the error you
are receiving?
--
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sergio Garcia" <sergio.garcia@.noreply.org> wrote in message
news:%23WIKzQtUIHA.1208@.TK2MSFTNGP03.phx.gbl...
> Sorry, I didn't explained well... it is not a DC, it is only a domain
> member.
>
> Paul Bergson [MVP-DS] wrote:
>> Never use a multihomed server as a DC. So you need to either remove AD
>> off of this machine or remove one of the NIC's.
>> The link below has some info on this and I couldn't find the exact
>> article but I can guarantee you are going to have constant problems with
>> this setup.
>> http://support.Microsoft.com/default.aspx?scid=kb;en-us;832478|||The SQL Server has the next configuration
_________ _______
| | | |
NIC1 --| SQL |-- NIC2 --| DC |
10.10.10.1 | | 10.20.10.1 | DNS |
host.pub.dom.com -- host.dom.com --
In 10.20.10.0/24 network there is a DC of dom.com domain and a DNS wich
has both A entries.
My problem is that in the same SQL Server, I want to connect to
host.pub.dom.com database server and I can't.
If I use "host.pub.dom.com" can't connect. KO
If I use "host.dom.com" I can connect. OK
If I use "10.10.10.1" address, I can connect. OK
If I use "10.20.10.1" address, I can connect. OK
If I change the A record, "host.pub.dom.com", to a CNAME record pointing
to "host.dom.com"... I can connect.
Also, there is a router linking both networks
Paul Bergson [MVP-DS] wrote:
> Sorry for the misunderstanding. I don't specifically see what the problem
> is, but does this sql server allow SQL Server and AD authentication? If so
> have you tried both methods? What about the Event Log what is the error you
> are receiving?
>|||The SQL Server has the next configuration
_________ _______
| | | |
NIC1 --| SQL |-- NIC2 --| DC |
10.10.10.1 | | 10.20.10.1 | DNS |
host.pub.dom.com -- host.dom.com --
In 10.20.10.0/24 network there is a DC of dom.com domain and a DNS wich
has both A entries.
My problem is that in the same SQL Server, I want to connect to
host.pub.dom.com database server and I can't.
If I use "host.pub.dom.com" can't connect. KO
If I use "host.dom.com" I can connect. OK
If I use "10.10.10.1" address, I can connect. OK
If I use "10.20.10.1" address, I can connect. OK
If I change the A record, "host.pub.dom.com", to a CNAME record pointing
to "host.dom.com"... I can connect.
Also, there is a router linking both networks
Paul Bergson [MVP-DS] wrote:
> Sorry for the misunderstanding. I don't specifically see what the problem
> is, but does this sql server allow SQL Server and AD authentication? If so
> have you tried both methods? What about the Event Log what is the error you
> are receiving?
>|||This sql box is not a router and the path host.pub.dom.com is not part of
your domain. The reason the cname works is you have it pointing to the
domain side.
I think you could get this to work if you loaded Routing and Remote Access,
but this is skewed.
--
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sergio Garcia" <sergio.garcia@.noreply.org> wrote in message
news:47863DDF.6090604@.noreply.org...
> The SQL Server has the next configuration
> _________ _______
> | | | |
> NIC1 --| SQL |-- NIC2 --| DC |
> 10.10.10.1 | | 10.20.10.1 | DNS |
> host.pub.dom.com -- host.dom.com --
> In 10.20.10.0/24 network there is a DC of dom.com domain and a DNS wich
> has both A entries.
> My problem is that in the same SQL Server, I want to connect to
> host.pub.dom.com database server and I can't.
> If I use "host.pub.dom.com" can't connect. KO
> If I use "host.dom.com" I can connect. OK
> If I use "10.10.10.1" address, I can connect. OK
> If I use "10.20.10.1" address, I can connect. OK
> If I change the A record, "host.pub.dom.com", to a CNAME record pointing
> to "host.dom.com"... I can connect.
>
> Also, there is a router linking both networks
> Paul Bergson [MVP-DS] wrote:
>> Sorry for the misunderstanding. I don't specifically see what the
>> problem is, but does this sql server allow SQL Server and AD
>> authentication? If so have you tried both methods? What about the Event
>> Log what is the error you are receiving?
"Multi relational" table design question
Hi! Im working on a webapplication and has serious thoughts about howto optimize my table structure. To explain:
My tablestructure today
tbl_customers
cust_id
name
....
tbl_contacts
con_id
name
....
tbl_groups
grp_id
name
....
tbl_sub_phone
phone_id
parent_type
parent_id
phone_area
phone_nr
....
tbl_sub_email
mail_id
parent_type
parent_id
email
....
As seen above every contact, group and customer can be assigned an unlimited amount of phonenumbers or emailadresses.
For example when entering a new email or a customer following will be inserted in tbl_sub_email: parent_type = 'cst', parent_id= '2' (the cust_id from tbl_customers), email ='gwerg@.fe.com'
The problem is i am uncertain if this is a very unefficient way of handling it? i see two alternatives:
Alternative 2:
i create x subtables for each table for example tbl_customers will get its mailadresses and phonenumbers contained in tbl_customers_phone and tbl_customers_email
What i am uncertain of here is if this would make things alot more troublesome when searching p? example after a specific phonenumber.
Alternative 3:
tbl_customers
cust_id
name
....
tbl_contacts
con_id
name
....
tbl_groups
grp_id
name
....
tables connection objects to subobjects
tbl_customers_phone
id
cust_id
phone_id
tbl_contacts_phone
id
con_id
phone_id
tbl_customers_mail
id
cust_id
mail_id
tbl_sub_phone
phone_id
phone_area
phone_nr
....
tbl_sub_email
mail_id
email
....
Ranking these three models, wich would be the most efficient and most inefficient performanswise?
What i want to avoid is performanceproblems when listing the objects, my indexing skills are a bit limited although im doing alot of reading and testing regarding this.
So thats why im asking for advice so that i can minimize the need of rebuilding the table structure when the application already has been starting to get used.
I also have another general question.
I have alot of select querys when i need to fetch data from several different tables.
Most of them is that i for example get an application from tbl_applications table, and that tables contains the columns cat1, cat2 and cat3 (wich are categories and contain the primary key integer to the tbl_sub_categorys table)
With 3 joins i retrieve these 3 category names returning 1 result with all the info i need.
Since ive been getting som strange results from the query analyzer(i got results that using clustered indexing for the primary key resulted in a slower query (higher cost)) i actually have another question.
Can it generally be summed up that a single query(join or subquery) generaly ils faster than getting the data in separate selects?
In the example above this i have the options either of using joins = 1 query or doing 2 querys and sorting the categorys codewise in aspx pages or doing 4 querys, one for the app followed by 1 for every category.
Any input regarding this?
As i said earlier im looking for the most efficient way of doing the things abov, would greatly appriechiate any input!
Hi,
I don't know what tools come with SQL Server Express, but most full version RDBMS have tools that let you look at execution plans for queries and show the reletive cost of actions a given query plan will take. Look for terms like 'show plan', 'show query plan', 'query optimizer', or options for whatever 'query designer' tool you may have. You can also save a querry with an execution plan that works well as a stored procedure, which helps even more. Selective denormalization of a database (adding redundant data columns to a table), can improve performance, if the number of tables referenced by a single query exceeds a certain number (something like >7), depending on the RDBMS (you can look that up in the help). Doing this means you'll have to cover any data integrity issues on your own, though.
Indexing generally helps, but you'll want to test to see if it does in any given situation by testing. Also, consider the physical ordering of whatever tables you have. For any given table, there can be only one order in which the rows are stored. When a query needs to read through a table to find the rows it needs, the physical sort oder is the most effecient.
Doesn't sound like data integrity is a prime concern in your app. Keep in mind though, that in many cases, what you do to make queries more effecient can open up your database to data integrity issues. Optimally, you can copy a subset of a database where data integrity is the prime concern, to a database setup just for queries (maybe that's what you have already). This way, you can optimize without worrying about effects that might have on the data you need to keep safe. Also, you won't have people hitting and locking up a line-of-business database, if they don't need to.
Just some things to look into. In the end, you can chase optimization forever, so get an idea of what degree or performance is good enough, and then weigh that against the other issues you face. It could be that other parts of your app will show a better return on the time you spend than data structure optimization. Hope it helps. BRN..
Well as i understand the main thing is to avoid table scans since this can be a *** when u reach a certain number of querys. Have done some tests inserting approx 50 000 - 100 000 rows and then checking performance degradation, wich is quite significant. What i aim at is creating a table structure that can be optimised as easy as possible. I have some lack in indexing skills wich makes me wonder how much one can improve the table structure i have now without splitting the subtables or creating intermediate tables that are used when listning the objects.
|||Think i will split the tables for now and, if i see any reason to it, join them later(wich i dont think + if i would, it would be easier than the other way around).
And yeah, optimizing the code probably would be something worth spending time on as well. But will go with what i have now and read more into indexing and query optimization (The ladder wich i think is quite important to be able to chosse the right query/code structure).
"MOVE" and "FIND" in SQL Server 2000
Our code is made in asp
We are moving the data from access to sql-server.
The recordset methods "MOVE" and "FIND" is now returning errormessages.
Anyone who can tell us what to use instead - or where to look it up ?
Best Regards
Jens GjerloevWhat error message are you getting ?|||For instance:
RS.Find(name=) & name
RS.Move(25)
"Jensgjerloev" wrote:
> Our code is made in asp
> We are moving the data from access to sql-server.
> The recordset methods "MOVE" and "FIND" is now returning errormessages.
> Anyone who can tell us what to use instead - or where to look it up ?
> Best Regards
> Jens Gjerloev|||You need to post a piece of your code, as well as your error messages.
Jensgjerloev wrote:
> Our code is made in asp
> We are moving the data from access to sql-server.
> The recordset methods "MOVE" and "FIND" is now returning errormessages.
> Anyone who can tell us what to use instead - or where to look it up ?
> Best Regards
> Jens Gjerloev|||Hello to Dave & Jens
Thanks for your reply.
Let me be honest: we have been in a state of panic during migration
from access to sql - so the mentioned error had NO relation fo MOVE and/or
FIND.
Sorry -
best regards
Jens Gjerloev
"Dave Markle" <"dma[remove_ZZ]ZZrkle" wrote:
> You need to post a piece of your code, as well as your error messages.
> Jensgjerloev wrote:
>
We are moving the data from access to sql-server.
The recordset methods "MOVE" and "FIND" is now returning errormessages.
Anyone who can tell us what to use instead - or where to look it up ?
Best Regards
Jens GjerloevWhat error message are you getting ?|||For instance:
RS.Find(name=) & name
RS.Move(25)
"Jensgjerloev" wrote:
> Our code is made in asp
> We are moving the data from access to sql-server.
> The recordset methods "MOVE" and "FIND" is now returning errormessages.
> Anyone who can tell us what to use instead - or where to look it up ?
> Best Regards
> Jens Gjerloev|||You need to post a piece of your code, as well as your error messages.
Jensgjerloev wrote:
> Our code is made in asp
> We are moving the data from access to sql-server.
> The recordset methods "MOVE" and "FIND" is now returning errormessages.
> Anyone who can tell us what to use instead - or where to look it up ?
> Best Regards
> Jens Gjerloev|||Hello to Dave & Jens
Thanks for your reply.
Let me be honest: we have been in a state of panic during migration
from access to sql - so the mentioned error had NO relation fo MOVE and/or
FIND.
Sorry -
best regards
Jens Gjerloev
"Dave Markle" <"dma[remove_ZZ]ZZrkle" wrote:
> You need to post a piece of your code, as well as your error messages.
> Jensgjerloev wrote:
>
"model" system database transaction log
why would the transaction log of the model database have increased? this is a
template database what could be writing to it?
Obviously someone or some app has connected to the wrong db. Maybe someone
meant to use Tempdb and incorrectly chose Model instead.
Andrew J. Kelly SQL MVP
"Peter L." <PeterL@.discussions.microsoft.com> wrote in message
news:9B15D142-5FE8-4822-AAD2-57DDCE393B34@.microsoft.com...
> why would the transaction log of the model database have increased? this
> is a
> template database what could be writing to it?
>
template database what could be writing to it?
Obviously someone or some app has connected to the wrong db. Maybe someone
meant to use Tempdb and incorrectly chose Model instead.
Andrew J. Kelly SQL MVP
"Peter L." <PeterL@.discussions.microsoft.com> wrote in message
news:9B15D142-5FE8-4822-AAD2-57DDCE393B34@.microsoft.com...
> why would the transaction log of the model database have increased? this
> is a
> template database what could be writing to it?
>
"model" system database transaction log
why would the transaction log of the model database have increased? this is
a
template database what could be writing to it?Obviously someone or some app has connected to the wrong db. Maybe someone
meant to use Tempdb and incorrectly chose Model instead.
Andrew J. Kelly SQL MVP
"Peter L." <PeterL@.discussions.microsoft.com> wrote in message
news:9B15D142-5FE8-4822-AAD2-57DDCE393B34@.microsoft.com...
> why would the transaction log of the model database have increased? this
> is a
> template database what could be writing to it?
>
a
template database what could be writing to it?Obviously someone or some app has connected to the wrong db. Maybe someone
meant to use Tempdb and incorrectly chose Model instead.
Andrew J. Kelly SQL MVP
"Peter L." <PeterL@.discussions.microsoft.com> wrote in message
news:9B15D142-5FE8-4822-AAD2-57DDCE393B34@.microsoft.com...
> why would the transaction log of the model database have increased? this
> is a
> template database what could be writing to it?
>
"model" system database transaction log
why would the transaction log of the model database have increased? this is a
template database what could be writing to it?Obviously someone or some app has connected to the wrong db. Maybe someone
meant to use Tempdb and incorrectly chose Model instead.
--
Andrew J. Kelly SQL MVP
"Peter L." <PeterL@.discussions.microsoft.com> wrote in message
news:9B15D142-5FE8-4822-AAD2-57DDCE393B34@.microsoft.com...
> why would the transaction log of the model database have increased? this
> is a
> template database what could be writing to it?
>
template database what could be writing to it?Obviously someone or some app has connected to the wrong db. Maybe someone
meant to use Tempdb and incorrectly chose Model instead.
--
Andrew J. Kelly SQL MVP
"Peter L." <PeterL@.discussions.microsoft.com> wrote in message
news:9B15D142-5FE8-4822-AAD2-57DDCE393B34@.microsoft.com...
> why would the transaction log of the model database have increased? this
> is a
> template database what could be writing to it?
>
"Missing Parameter Values" Error shown when subReport includes in Main Report
Dear all,
There is an error,"Missing Parameter Values" shown when sub-report includes in the Main report.
(And there is no parameter setup in sub-report)
What i do is to setup the datasource into sub-report by coding..
I find that they runs fine when the main report, sub-report are separated.
CAn anyone give me some ideas about the solution?
P.S. The platform i am using is VS 2005.. Thanks much
michaelDid you link your subreport to your main?
GJ|||there is no link between the subreport and the main report. Maybe say, main report and subreport are totally 2 different kinds of report.
P.S. the subreport is located in report footer
Thx
There is an error,"Missing Parameter Values" shown when sub-report includes in the Main report.
(And there is no parameter setup in sub-report)
What i do is to setup the datasource into sub-report by coding..
I find that they runs fine when the main report, sub-report are separated.
CAn anyone give me some ideas about the solution?
P.S. The platform i am using is VS 2005.. Thanks much
michaelDid you link your subreport to your main?
GJ|||there is no link between the subreport and the main report. Maybe say, main report and subreport are totally 2 different kinds of report.
P.S. the subreport is located in report footer
Thx
"Microsoft SQL Servers" not showing in Computer Management.
Hi there,
Got a small tech support problem. We gave our manual writer an installer
that should install SQL Server (MSDE) and our software. Over the phone I've
managed to ascertain that SQL Server is installed (it is running as a
service, along with SQL Server Agent), but it does not show up in "Computer
Management -> Services and Applications". I need to set the permissions for
this user (over the phone!) in order to get our test software to work and
yet it does not show here. What am I doing wrong? (and/or why would the
MSDE instance not show up here?).
Thanks,
RobinI sorted it :)
"Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:cq6ak0$fkg$1$8300dec7@.news.demon.co.uk...
> Hi there,
>
> Got a small tech support problem. We gave our manual writer an installer
> that should install SQL Server (MSDE) and our software. Over the phone
> I've managed to ascertain that SQL Server is installed (it is running as a
> service, along with SQL Server Agent), but it does not show up in
> "Computer Management -> Services and Applications". I need to set the
> permissions for this user (over the phone!) in order to get our test
> software to work and yet it does not show here. What am I doing wrong?
> (and/or why would the MSDE instance not show up here?).
> Thanks,
>
> Robin
"mean" quesiton
Hi,
I am trying to find if there is already a function something like "SUM" that
can do a "MEAN". Average is too misleading for getting the "MEAN" order
amount.
Open for suggestions.
Thanks in advance.
George CollinsIsn't mean just a synonym for average? Are you referring to the median?
If so, you could try this post by Celko:
http://groups.google.com/groups?hl=en&lr=&c2coff=1&selm=c0d87ec0.0210170857.72734e7c%40posting.google.com
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"george collins" <george@.nospan.com> wrote in message
news:%23I%23aj1GuEHA.1048@.tk2msftngp13.phx.gbl...
> Hi,
> I am trying to find if there is already a function something like "SUM"
that
> can do a "MEAN". Average is too misleading for getting the "MEAN" order
> amount.
> Open for suggestions.
> Thanks in advance.
> George Collins
>|||AVG = mean. Are you referring to median?
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"george collins" <george@.nospan.com> wrote in message
news:%23I%23aj1GuEHA.1048@.tk2msftngp13.phx.gbl...
Hi,
I am trying to find if there is already a function something like "SUM" that
can do a "MEAN". Average is too misleading for getting the "MEAN" order
amount.
Open for suggestions.
Thanks in advance.
George Collins|||On the sql server 2000 book which is downloadable freely from
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp,
there is no functionality for "MEAN" under "Transact-SQL Reference".
You might have to devise a way to calucate the MEAN yourself.
"george collins" wrote:
> Hi,
> I am trying to find if there is already a function something like "SUM" that
> can do a "MEAN". Average is too misleading for getting the "MEAN" order
> amount.
> Open for suggestions.
> Thanks in advance.
> George Collins
>
>|||Thanks I thought I had lost it there for a few minutes... I even went and
found my old Statistics book from 1975. Mean is mean. Thanks for your
response, I have been digging for a few hours and everyone thinks that
playing with an average is what I am trying to do.
Thanks you so much I will research your link.
George Collins
"dance2die" <dance2die@.discussions.microsoft.com> wrote in message
news:E71E7205-66A4-4D9E-BE3F-EDF8DA833E3E@.microsoft.com...
> On the sql server 2000 book which is downloadable freely from
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp,
> there is no functionality for "MEAN" under "Transact-SQL Reference".
> You might have to devise a way to calucate the MEAN yourself.
> "george collins" wrote:
>> Hi,
>> I am trying to find if there is already a function something like "SUM"
>> that
>> can do a "MEAN". Average is too misleading for getting the "MEAN" order
>> amount.
>> Open for suggestions.
>> Thanks in advance.
>> George Collins
>>|||I AM WRONG!!!!
I found it all,
thanks everyone.
George
"george collins" <george@.nospan.com> wrote in message
news:%23I%23aj1GuEHA.1048@.tk2msftngp13.phx.gbl...
> Hi,
> I am trying to find if there is already a function something like "SUM"
> that can do a "MEAN". Average is too misleading for getting the "MEAN"
> order amount.
> Open for suggestions.
> Thanks in advance.
> George Collins
>
I am trying to find if there is already a function something like "SUM" that
can do a "MEAN". Average is too misleading for getting the "MEAN" order
amount.
Open for suggestions.
Thanks in advance.
George CollinsIsn't mean just a synonym for average? Are you referring to the median?
If so, you could try this post by Celko:
http://groups.google.com/groups?hl=en&lr=&c2coff=1&selm=c0d87ec0.0210170857.72734e7c%40posting.google.com
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"george collins" <george@.nospan.com> wrote in message
news:%23I%23aj1GuEHA.1048@.tk2msftngp13.phx.gbl...
> Hi,
> I am trying to find if there is already a function something like "SUM"
that
> can do a "MEAN". Average is too misleading for getting the "MEAN" order
> amount.
> Open for suggestions.
> Thanks in advance.
> George Collins
>|||AVG = mean. Are you referring to median?
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"george collins" <george@.nospan.com> wrote in message
news:%23I%23aj1GuEHA.1048@.tk2msftngp13.phx.gbl...
Hi,
I am trying to find if there is already a function something like "SUM" that
can do a "MEAN". Average is too misleading for getting the "MEAN" order
amount.
Open for suggestions.
Thanks in advance.
George Collins|||On the sql server 2000 book which is downloadable freely from
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp,
there is no functionality for "MEAN" under "Transact-SQL Reference".
You might have to devise a way to calucate the MEAN yourself.
"george collins" wrote:
> Hi,
> I am trying to find if there is already a function something like "SUM" that
> can do a "MEAN". Average is too misleading for getting the "MEAN" order
> amount.
> Open for suggestions.
> Thanks in advance.
> George Collins
>
>|||Thanks I thought I had lost it there for a few minutes... I even went and
found my old Statistics book from 1975. Mean is mean. Thanks for your
response, I have been digging for a few hours and everyone thinks that
playing with an average is what I am trying to do.
Thanks you so much I will research your link.
George Collins
"dance2die" <dance2die@.discussions.microsoft.com> wrote in message
news:E71E7205-66A4-4D9E-BE3F-EDF8DA833E3E@.microsoft.com...
> On the sql server 2000 book which is downloadable freely from
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp,
> there is no functionality for "MEAN" under "Transact-SQL Reference".
> You might have to devise a way to calucate the MEAN yourself.
> "george collins" wrote:
>> Hi,
>> I am trying to find if there is already a function something like "SUM"
>> that
>> can do a "MEAN". Average is too misleading for getting the "MEAN" order
>> amount.
>> Open for suggestions.
>> Thanks in advance.
>> George Collins
>>|||I AM WRONG!!!!
I found it all,
thanks everyone.
George
"george collins" <george@.nospan.com> wrote in message
news:%23I%23aj1GuEHA.1048@.tk2msftngp13.phx.gbl...
> Hi,
> I am trying to find if there is already a function something like "SUM"
> that can do a "MEAN". Average is too misleading for getting the "MEAN"
> order amount.
> Open for suggestions.
> Thanks in advance.
> George Collins
>
"mean" quesiton
Hi,
I am trying to find if there is already a function something like "SUM" that
can do a "MEAN". Average is too misleading for getting the "MEAN" order
amount.
Open for suggestions.
Thanks in advance.
George Collins
Isn't mean just a synonym for average? Are you referring to the median?
If so, you could try this post by Celko:
http://groups.google.com/groups?hl=e...ing.google.com
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"george collins" <george@.nospan.com> wrote in message
news:%23I%23aj1GuEHA.1048@.tk2msftngp13.phx.gbl...
> Hi,
> I am trying to find if there is already a function something like "SUM"
that
> can do a "MEAN". Average is too misleading for getting the "MEAN" order
> amount.
> Open for suggestions.
> Thanks in advance.
> George Collins
>
|||AVG = mean. Are you referring to median?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"george collins" <george@.nospan.com> wrote in message
news:%23I%23aj1GuEHA.1048@.tk2msftngp13.phx.gbl...
Hi,
I am trying to find if there is already a function something like "SUM" that
can do a "MEAN". Average is too misleading for getting the "MEAN" order
amount.
Open for suggestions.
Thanks in advance.
George Collins
|||On the sql server 2000 book which is downloadable freely from
http://www.microsoft.com/sql/techinf...000/books.asp,
there is no functionality for "MEAN" under "Transact-SQL Reference".
You might have to devise a way to calucate the MEAN yourself.
"george collins" wrote:
> Hi,
> I am trying to find if there is already a function something like "SUM" that
> can do a "MEAN". Average is too misleading for getting the "MEAN" order
> amount.
> Open for suggestions.
> Thanks in advance.
> George Collins
>
>
|||Thanks I thought I had lost it there for a few minutes... I even went and
found my old Statistics book from 1975. Mean is mean. Thanks for your
response, I have been digging for a few hours and everyone thinks that
playing with an average is what I am trying to do.
Thanks you so much I will research your link.
George Collins
"dance2die" <dance2die@.discussions.microsoft.com> wrote in message
news:E71E7205-66A4-4D9E-BE3F-EDF8DA833E3E@.microsoft.com...[vbcol=seagreen]
> On the sql server 2000 book which is downloadable freely from
> http://www.microsoft.com/sql/techinf...000/books.asp,
> there is no functionality for "MEAN" under "Transact-SQL Reference".
> You might have to devise a way to calucate the MEAN yourself.
> "george collins" wrote:
I am trying to find if there is already a function something like "SUM" that
can do a "MEAN". Average is too misleading for getting the "MEAN" order
amount.
Open for suggestions.
Thanks in advance.
George Collins
Isn't mean just a synonym for average? Are you referring to the median?
If so, you could try this post by Celko:
http://groups.google.com/groups?hl=e...ing.google.com
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"george collins" <george@.nospan.com> wrote in message
news:%23I%23aj1GuEHA.1048@.tk2msftngp13.phx.gbl...
> Hi,
> I am trying to find if there is already a function something like "SUM"
that
> can do a "MEAN". Average is too misleading for getting the "MEAN" order
> amount.
> Open for suggestions.
> Thanks in advance.
> George Collins
>
|||AVG = mean. Are you referring to median?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"george collins" <george@.nospan.com> wrote in message
news:%23I%23aj1GuEHA.1048@.tk2msftngp13.phx.gbl...
Hi,
I am trying to find if there is already a function something like "SUM" that
can do a "MEAN". Average is too misleading for getting the "MEAN" order
amount.
Open for suggestions.
Thanks in advance.
George Collins
|||On the sql server 2000 book which is downloadable freely from
http://www.microsoft.com/sql/techinf...000/books.asp,
there is no functionality for "MEAN" under "Transact-SQL Reference".
You might have to devise a way to calucate the MEAN yourself.
"george collins" wrote:
> Hi,
> I am trying to find if there is already a function something like "SUM" that
> can do a "MEAN". Average is too misleading for getting the "MEAN" order
> amount.
> Open for suggestions.
> Thanks in advance.
> George Collins
>
>
|||Thanks I thought I had lost it there for a few minutes... I even went and
found my old Statistics book from 1975. Mean is mean. Thanks for your
response, I have been digging for a few hours and everyone thinks that
playing with an average is what I am trying to do.
Thanks you so much I will research your link.
George Collins
"dance2die" <dance2die@.discussions.microsoft.com> wrote in message
news:E71E7205-66A4-4D9E-BE3F-EDF8DA833E3E@.microsoft.com...[vbcol=seagreen]
> On the sql server 2000 book which is downloadable freely from
> http://www.microsoft.com/sql/techinf...000/books.asp,
> there is no functionality for "MEAN" under "Transact-SQL Reference".
> You might have to devise a way to calucate the MEAN yourself.
> "george collins" wrote:
Subscribe to:
Posts (Atom)
