Help with connectivity - SQL Server does not exist or access denied

am using VS2003 asp.net 1.1 on Windows 2003 sever to access another network server SQL Db.

This is the error message...

SQL Server does not exist or access denied.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: SQL Server does not exist or access denied.

Source Error:

Line 34: 'execute query and return data readerLine 35: TryLine 36: objHelperConnection.Open()Line 37: RunSPReturnDR = objHelperCommand.ExecuteReader(CommandBehavior.CloseConnection)Line 38: 'clean up

Source File:c:\inetpub\wwwroot\Protraxx\Scripts\Includes\dbhelper.vb Line:36

Stack Trace:

[SqlException: SQL Server does not exist or access denied.] System.Data.SqlClient.ConnectionPool.CreateConnection() System.Data.SqlClient.ConnectionPool.UserCreateRequest() System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) System.Data.SqlClient.SqlConnection.Open() chef.dbhelper.DBTools.RunSPReturnDR(String strSP, SqlParameter[] params) in c:\inetpub\wwwroot\Protraxx\Scripts\Includes\dbhelper.vb:36 chef.Login.Authenticate(String sUserName, String sPassWord) in c:\inetpub\wwwroot\Protraxx\Login.aspx.vb:168 chef.Login.btnSubmit_Click(Object sender, EventArgs e) in c:\inetpub\wwwroot\Protraxx\Login.aspx.vb:90 System.Web.UI.WebControls.Button.OnClick(EventArgs e) System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) System.Web.UI.Page.ProcessRequestMain()

Version Information: Microsoft .NET Framework Version:1.1.4322.2300; ASP.NET Version:1.1.4322.2300

Ps : This app works on Windows XP and W2k

Thanks for any suggestions.

This can be caused by a couple of things. Microsoft has a comprehensive article on their site descibing the causes and fixes of your error:http://url123.com/nwsp4

sql

Help with an error message

I keep getting this error message when I try to restore a database backup that I have made.

System.Data.SqlClient.SqlError: Directory lookup for the file "D:\Databases\GTT_Data.MDF" failed with the operating system error 5(error not found). (Microsoft.SqlServer.Express.Smo)

What I have done is created a backup of my sql sever database and I am now trying to restore it into sql server express for someone to play around with without it affecting my actual database. This is where the error messages are coming from.

Please help

Are you getting this during the restore ? If the machines are not the same for backup and restore you might not have the drives / folders available as they are on the original system. If so you either have to create those folders or Restore the database with the WITH MOVE option to specify a new destination. (thats because the original location is tored in the backup file).

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Yes its during the restore. Yes the machines are not the same. Please could you give me further details on how to restore the databse with the WITH MOVE option.

Thanks for your help

|||Hi,

look in your BOL (Books online, the Online help of SQL Server)

There is a point Restore a database and move Files which will help you

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/877ecd57-3f2e-4237-890a-08f16e944ef1.htm

RESTORE DATABASE MyAdvWorks
FROM MyAdvWorks_1
WITH NORECOVERY,
MOVE 'MyAdvWorks' TO
'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewAdvWorks.mdf',
MOVE 'MyAdvWorksLog1'
TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewAdvWorks.ldf'
RESTORE LOG MyAdvWorks
FROM MyAdvWorksLog1
WITH RECOVERY

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

help with activating rs

Hello,
We installed RS and got the message that we needed to activate the
reporserver. We ran the following:
c:\program files\Microsoft SQL Server\80\Tools\Binn\rsactivate -c
"C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\RSReportServer.config"
Now when the site comes up, but it is missing the Nav Bar with
contents/properties so we cannot upload any files. Is there something else
we need to run?Mayby you're loged on with a user that hasn't got any rights. Use
administrator login to give the initial access-rigths.
"Jake Smythe" wrote:
> Hello,
> We installed RS and got the message that we needed to activate the
> reporserver. We ran the following:
> c:\program files\Microsoft SQL Server\80\Tools\Binn\rsactivate -c
> "C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> Services\ReportServer\RSReportServer.config"
> Now when the site comes up, but it is missing the Nav Bar with
> contents/properties so we cannot upload any files. Is there something else
> we need to run?
>
>|||Antoon,
We are logged into a user that is part of the administrators group. Any
other ideas?
Jake
"Antoon" <Antoon@.discussions.microsoft.com> wrote in message
news:CBAF4E9E-AD80-49AE-9E6C-209E977BC4EA@.microsoft.com...
> Mayby you're loged on with a user that hasn't got any rights. Use
> administrator login to give the initial access-rigths.
> "Jake Smythe" wrote:
>> Hello,
>> We installed RS and got the message that we needed to activate the
>> reporserver. We ran the following:
>> c:\program files\Microsoft SQL Server\80\Tools\Binn\rsactivate -c
>> "C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
>> Services\ReportServer\RSReportServer.config"
>> Now when the site comes up, but it is missing the Nav Bar with
>> contents/properties so we cannot upload any files. Is there something
>> else
>> we need to run?
>>

Help with a query

I'm not sure if this is the right place to post this message. I
didn't see a sqlserver.sql newsgroup.
Anyway, we are porting our site from using MySQL to using
SQLServer 2000 and we've run across a problem. In MySQL,
you can have a query that looks like this (using Northwind DB
as an example as that is ubiquitous):
USE [Northwind];
SELECT
ProductName,
IF( UnitPrice < 20, 'Cheap', 'Expensive' ) AS ExpenseLevel
FROM
Products;
Such that it would print out the product name in one column
and either the word 'Cheap' or 'Expensive' on the other column
depending on the unitprice. This doesn't seem to work in
SQLServer 2000 as it's giving me a syntax error at the IF. Is
this possible using SQLServer? If so, how so?
thnx,
ChristophOn Thu, 19 Aug 2004 08:37:50 -0500, Christoph Boget wrote:
>I'm not sure if this is the right place to post this message. I
>didn't see a sqlserver.sql newsgroup.
>Anyway, we are porting our site from using MySQL to using
>SQLServer 2000 and we've run across a problem. In MySQL,
>you can have a query that looks like this (using Northwind DB
>as an example as that is ubiquitous):
>USE [Northwind];
>SELECT
> ProductName,
> IF( UnitPrice < 20, 'Cheap', 'Expensive' ) AS ExpenseLevel
>FROM
> Products;
>Such that it would print out the product name in one column
>and either the word 'Cheap' or 'Expensive' on the other column
>depending on the unitprice. This doesn't seem to work in
>SQLServer 2000 as it's giving me a syntax error at the IF. Is
>this possible using SQLServer? If so, how so?
>thnx,
>Christoph
>
Hi Christoph,
Check out CASE in Books Online. Your example would read:
SELECT ProductName,
CASE
WHEN UnitPrice < 20
THEN 'Cheap'
ELSE 'Expensive'
END AS ExpenseLevel
FROM Products
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Yep:
USE [Northwind];
SELECT
ProductName,
CASE WHEN UnitPrice < 20 THEN 'Cheap' ELSE 'Expensive' END AS ExpenseLevel
FROM
BTW, there is a .programming newsgroup.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Christoph Boget" <jcboget@.yahoo.com> wrote in message
news:uylq2GfhEHA.4064@.TK2MSFTNGP12.phx.gbl...
I'm not sure if this is the right place to post this message. I
didn't see a sqlserver.sql newsgroup.
Anyway, we are porting our site from using MySQL to using
SQLServer 2000 and we've run across a problem. In MySQL,
you can have a query that looks like this (using Northwind DB
as an example as that is ubiquitous):
USE [Northwind];
SELECT
ProductName,
IF( UnitPrice < 20, 'Cheap', 'Expensive' ) AS ExpenseLevel
FROM
Products;
Such that it would print out the product name in one column
and either the word 'Cheap' or 'Expensive' on the other column
depending on the unitprice. This doesn't seem to work in
SQLServer 2000 as it's giving me a syntax error at the IF. Is
this possible using SQLServer? If so, how so?
thnx,
Christoph|||Hi
USE [Northwind];
SELECT
ProductName,
CASE WHEN UnitPrice < 20 THEN 'Cheap'ELSE 'Expensive' END AS ExpenseLevel
FROM
Products;
"Christoph Boget" <jcboget@.yahoo.com> wrote in message
news:uylq2GfhEHA.4064@.TK2MSFTNGP12.phx.gbl...
> I'm not sure if this is the right place to post this message. I
> didn't see a sqlserver.sql newsgroup.
> Anyway, we are porting our site from using MySQL to using
> SQLServer 2000 and we've run across a problem. In MySQL,
> you can have a query that looks like this (using Northwind DB
> as an example as that is ubiquitous):
> USE [Northwind];
> SELECT
> ProductName,
> IF( UnitPrice < 20, 'Cheap', 'Expensive' ) AS ExpenseLevel
> FROM
> Products;
> Such that it would print out the product name in one column
> and either the word 'Cheap' or 'Expensive' on the other column
> depending on the unitprice. This doesn't seem to work in
> SQLServer 2000 as it's giving me a syntax error at the IF. Is
> this possible using SQLServer? If so, how so?
> thnx,
> Christoph
>

Help with a query

I'm not sure if this is the right place to post this message. I
didn't see a sqlserver.sql newsgroup.
Anyway, we are porting our site from using MySQL to using
SQLServer 2000 and we've run across a problem. In MySQL,
you can have a query that looks like this (using Northwind DB
as an example as that is ubiquitous):
USE [Northwind];
SELECT
ProductName,
IF( UnitPrice < 20, 'Cheap', 'Expensive' ) AS ExpenseLevel
FROM
Products;
Such that it would print out the product name in one column
and either the word 'Cheap' or 'Expensive' on the other column
depending on the unitprice. This doesn't seem to work in
SQLServer 2000 as it's giving me a syntax error at the IF. Is
this possible using SQLServer? If so, how so?
thnx,
Christoph
On Thu, 19 Aug 2004 08:37:50 -0500, Christoph Boget wrote:

>I'm not sure if this is the right place to post this message. I
>didn't see a sqlserver.sql newsgroup.
>Anyway, we are porting our site from using MySQL to using
>SQLServer 2000 and we've run across a problem. In MySQL,
>you can have a query that looks like this (using Northwind DB
>as an example as that is ubiquitous):
>USE [Northwind];
>SELECT
> ProductName,
> IF( UnitPrice < 20, 'Cheap', 'Expensive' ) AS ExpenseLevel
>FROM
> Products;
>Such that it would print out the product name in one column
>and either the word 'Cheap' or 'Expensive' on the other column
>depending on the unitprice. This doesn't seem to work in
>SQLServer 2000 as it's giving me a syntax error at the IF. Is
>this possible using SQLServer? If so, how so?
>thnx,
>Christoph
>
Hi Christoph,
Check out CASE in Books Online. Your example would read:
SELECT ProductName,
CASE
WHEN UnitPrice < 20
THEN 'Cheap'
ELSE 'Expensive'
END AS ExpenseLevel
FROM Products
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Yep:
USE [Northwind];
SELECT
ProductName,
CASE WHEN UnitPrice < 20 THEN 'Cheap' ELSE 'Expensive' END AS ExpenseLevel
FROM
BTW, there is a .programming newsgroup.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Christoph Boget" <jcboget@.yahoo.com> wrote in message
news:uylq2GfhEHA.4064@.TK2MSFTNGP12.phx.gbl...
I'm not sure if this is the right place to post this message. I
didn't see a sqlserver.sql newsgroup.
Anyway, we are porting our site from using MySQL to using
SQLServer 2000 and we've run across a problem. In MySQL,
you can have a query that looks like this (using Northwind DB
as an example as that is ubiquitous):
USE [Northwind];
SELECT
ProductName,
IF( UnitPrice < 20, 'Cheap', 'Expensive' ) AS ExpenseLevel
FROM
Products;
Such that it would print out the product name in one column
and either the word 'Cheap' or 'Expensive' on the other column
depending on the unitprice. This doesn't seem to work in
SQLServer 2000 as it's giving me a syntax error at the IF. Is
this possible using SQLServer? If so, how so?
thnx,
Christoph
|||Hi
USE [Northwind];
SELECT
ProductName,
CASE WHEN UnitPrice < 20 THEN 'Cheap'ELSE 'Expensive' END AS ExpenseLevel
FROM
Products;
"Christoph Boget" <jcboget@.yahoo.com> wrote in message
news:uylq2GfhEHA.4064@.TK2MSFTNGP12.phx.gbl...
> I'm not sure if this is the right place to post this message. I
> didn't see a sqlserver.sql newsgroup.
> Anyway, we are porting our site from using MySQL to using
> SQLServer 2000 and we've run across a problem. In MySQL,
> you can have a query that looks like this (using Northwind DB
> as an example as that is ubiquitous):
> USE [Northwind];
> SELECT
> ProductName,
> IF( UnitPrice < 20, 'Cheap', 'Expensive' ) AS ExpenseLevel
> FROM
> Products;
> Such that it would print out the product name in one column
> and either the word 'Cheap' or 'Expensive' on the other column
> depending on the unitprice. This doesn't seem to work in
> SQLServer 2000 as it's giving me a syntax error at the IF. Is
> this possible using SQLServer? If so, how so?
> thnx,
> Christoph
>

Help with a query

I'm not sure if this is the right place to post this message. I
didn't see a sqlserver.sql newsgroup.
Anyway, we are porting our site from using mysql to using
SQLServer 2000 and we've run across a problem. In MySQL,
you can have a query that looks like this (using Northwind DB
as an example as that is ubiquitous):
USE [Northwind];
SELECT
ProductName,
IF( UnitPrice < 20, 'Cheap', 'Expensive' ) AS ExpenseLevel
FROM
Products;
Such that it would print out the product name in one column
and either the word 'Cheap' or 'Expensive' on the other column
depending on the unitprice. This doesn't seem to work in
SQLServer 2000 as it's giving me a syntax error at the IF. Is
this possible using SQLServer? If so, how so?
thnx,
ChristophOn Thu, 19 Aug 2004 08:37:50 -0500, Christoph Boget wrote:

>I'm not sure if this is the right place to post this message. I
>didn't see a sqlserver.sql newsgroup.
>Anyway, we are porting our site from using mysql to using
>SQLServer 2000 and we've run across a problem. In MySQL,
>you can have a query that looks like this (using Northwind DB
>as an example as that is ubiquitous):
>USE [Northwind];
>SELECT
> ProductName,
> IF( UnitPrice < 20, 'Cheap', 'Expensive' ) AS ExpenseLevel
>FROM
> Products;
>Such that it would print out the product name in one column
>and either the word 'Cheap' or 'Expensive' on the other column
>depending on the unitprice. This doesn't seem to work in
>SQLServer 2000 as it's giving me a syntax error at the IF. Is
>this possible using SQLServer? If so, how so?
>thnx,
>Christoph
>
Hi Christoph,
Check out CASE in Books Online. Your example would read:
SELECT ProductName,
CASE
WHEN UnitPrice < 20
THEN 'Cheap'
ELSE 'Expensive'
END AS ExpenseLevel
FROM Products
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Yep:
USE [Northwind];
SELECT
ProductName,
CASE WHEN UnitPrice < 20 THEN 'Cheap' ELSE 'Expensive' END AS ExpenseLevel
FROM
BTW, there is a .programming newsgroup.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Christoph Boget" <jcboget@.yahoo.com> wrote in message
news:uylq2GfhEHA.4064@.TK2MSFTNGP12.phx.gbl...
I'm not sure if this is the right place to post this message. I
didn't see a sqlserver.sql newsgroup.
Anyway, we are porting our site from using mysql to using
SQLServer 2000 and we've run across a problem. In MySQL,
you can have a query that looks like this (using Northwind DB
as an example as that is ubiquitous):
USE [Northwind];
SELECT
ProductName,
IF( UnitPrice < 20, 'Cheap', 'Expensive' ) AS ExpenseLevel
FROM
Products;
Such that it would print out the product name in one column
and either the word 'Cheap' or 'Expensive' on the other column
depending on the unitprice. This doesn't seem to work in
SQLServer 2000 as it's giving me a syntax error at the IF. Is
this possible using SQLServer? If so, how so?
thnx,
Christoph|||Hi
USE [Northwind];
SELECT
ProductName,
CASE WHEN UnitPrice < 20 THEN 'Cheap'ELSE 'Expensive' END AS ExpenseLevel
FROM
Products;
"Christoph Boget" <jcboget@.yahoo.com> wrote in message
news:uylq2GfhEHA.4064@.TK2MSFTNGP12.phx.gbl...
> I'm not sure if this is the right place to post this message. I
> didn't see a sqlserver.sql newsgroup.
> Anyway, we are porting our site from using mysql to using
> SQLServer 2000 and we've run across a problem. In MySQL,
> you can have a query that looks like this (using Northwind DB
> as an example as that is ubiquitous):
> USE [Northwind];
> SELECT
> ProductName,
> IF( UnitPrice < 20, 'Cheap', 'Expensive' ) AS ExpenseLevel
> FROM
> Products;
> Such that it would print out the product name in one column
> and either the word 'Cheap' or 'Expensive' on the other column
> depending on the unitprice. This doesn't seem to work in
> SQLServer 2000 as it's giving me a syntax error at the IF. Is
> this possible using SQLServer? If so, how so?
> thnx,
> Christoph
>

Help with a complex Join

All
I could use some help with a complex join. I have 3 SIMPLIFIED
FICTIONAL tables (add table scripts and data on bottom of message):
In words I want the following:
a resultset of the avg(x), count(flag), sum(flag) for each and every
(outer join) Station_Number where cast_id<>'full' and flag=0 and z<=3.
I would expect that the count(flag) would return the number of rows in
tblLog but it returns number of rows in tblData (the child of the
1:many). What am I doing wrong? The sql I am using is below (as well
as the result).
daben
--SQL
QUERY---
SELECT TOP 100 PERCENT
tblStation.Station_Number,
AVG(CASE WHEN x <> -999 AND flag=0 and cast_id<>'full' THEN x
END) AS AVG_x,
ISNULL(STDEV(CASE WHEN x <> -999 AND flag=0 and
cast_id<>'full' THEN x END),0)AS STDEV_x,
SUM(flag) AS SUM_flag, COUNT(flag) AS COUNT_flag
FROM
((tblStation LEFT JOIN tblLog
ON (tblStation.Station_Number = tblLog.Station_Number))
LEFT JOIN tblData
ON (tblLog.Log_Index = tblData.Log_Index
AND z<=3))
GROUP BY
tblStation.Station_Number
ORDER BY
tblStation.Station_Number
--QUERY
RESULTSET---
Station_Number AVG_x
STDEV_x SUM_flag
COUNT_flag
-- ----
---- --
--
1 1.4285714285714286
0.53452248382484868 3 13
2 NULL
0.0 NULL 0
3 NULL
0.0 NULL 0
4 NULL
0.0 0 1
5 NULL
0.0 6 7
6 NULL
0.0 NULL 0
7 NULL
0.0 NULL 0
8 2.2000000000000002
1.0954451150103324 3 10
9 NULL
0.0 NULL 0
10 NULL
0.0 NULL 0
(10 row(s) affected)
--SQL ADD
TABLE---
CREATE TABLE [dbo].[tblData] (
[Log_Index] [int] NOT NULL ,
[z] [float] NOT NULL ,
[x] [float] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblLog] (
[Log_Index] [int] NOT NULL ,
[Station_Number] [int] NULL ,
[cast_id] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[flag] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblStation] (
[Station_Number] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblData] WITH NOCHECK ADD
CONSTRAINT [PK_tblData] PRIMARY KEY CLUSTERED
(
[Log_Index],
[z]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblLog] WITH NOCHECK ADD
CONSTRAINT [PK_tblLog] PRIMARY KEY CLUSTERED
(
[Log_Index]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblStation] WITH NOCHECK ADD
CONSTRAINT [PK_tblStation] PRIMARY KEY CLUSTERED
(
[Station_Number]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblLog] ADD
CONSTRAINT [DF_tblLog_cast_id] DEFAULT ('NA') FOR [cast_id],
CONSTRAINT [DF_tblLog_flag] DEFAULT (0) FOR [flag]
GO
ALTER TABLE [dbo].[tblData] ADD
CONSTRAINT [FK_tblData_tblLog] FOREIGN KEY
(
[Log_Index]
) REFERENCES [dbo].[tblLog] (
[Log_Index]
) ON DELETE CASCADE ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[tblLog] ADD
CONSTRAINT [FK_tblLog_tblStation] FOREIGN KEY
(
[Station_Number]
) REFERENCES [dbo].[tblStation] (
[Station_Number]
) ON DELETE CASCADE ON UPDATE CASCADE
GO
--DATA--
tblStation
[Station_Number]
1
2
3
4
5
6
7
8
9
10
tblLog
Log_Index Station_Number cast_id flag
1 1 full 0
2 1 up 0
3 1 down 1
4 1 up 0
5 1 down 0
6 4 full 0
7 5 full 0
8 5 up 1
9 5 down 1
10 8 full 0
11 8 up 0
12 8 up 0
13 8 down 1
tblData
Log_Index z x
2 1 1
2 2 2
2 3 2
2 4 2
2 5 1
2 6 2
2 7 2
2 8 1
3 1 5
3 2 5
3 3 5
3 4 5
3 5 5
3 6 5
3 7 5
3 8 5
4 1 1
4 2 1
4 3 1
4 4 1
4 5 1
4 6 1
4 7 1
4 8 1
5 1 2
5 2 -999
5 3 -999
5 4 -999
5 5 3
5 6 3
5 7 3
5 8 3
8 1 1
8 2 1
8 3 1
8 4 1
9 1 3
9 2 3
9 3 3
9 4 3
11 1 3
11 2 3
11 3 3
11 4 -999
12 1 1
12 2 1
12 3 -999
12 4 1
13 1 -999
13 2 -999
13 3 -999
13 4 -999CREATE TABLE Stations
(station_number INTEGER NOT NULL PRIMARY KEY);
CREATE TABLE Data
(log_index INTEGER NOT NULL PRIMARY KEY,
z FLOAT NOT NULL,
x FLOAT NULL,
PRIMARY KEY (log_index, z));
CREATE TABLE Log
(log_index INTEGER NOT NULL
REFERENCES Log (log_index)
ON DELETE CASCADE
ON UPDATE CASCADE,
station_number INTEGER NULL
REFERENCES Stations (station_number)
ON DELETE CASCADE
ON UPDATE CASCADE,
cast_id VARCHAR(10) DEFAULT 'NA',
flag INTEGER DEFAULT 0,
PRIMARY KEY (log_index, station_number));
I am going to make a gues beore I go to bed.
SELECT S.station_number,
AVG(D.x) AS x_avg,
COALESCE(STDEV(D.x, 0.0) AS x_stdev,
SUM(L.flag) AS flag_tot,
COUNT(L.flag) AS flag_cnt
FROM Station AS S
LEFT OUTER JOIN
Log AS L
ON S.station_number = L.station_number
AND L.flag = 0
AND L.cast_id <> 'full'
LEFT OUTER JOIN
Data AS D
ON L.log_index = D.log_index
AND D.z <= 3
AND D.x <> -999
GROUP BY S.station_number;|||Hi
Thanks for the reply. Your solution is much more elegant than mine, but
it fails on one point. It returns a count of 7 (indicating how many
rows in table D that are found) NOT a count of 4 (which would indicate
how many rows in table Log). I have found that this works (but is
sloppy) using correlated sub queries:
SELECT TOP 100 PERCENT
tblStation.Station_Number,
AVG(CASE WHEN x <> -999 AND flag=0 and cast_id<>'full' THEN x
END) AS AVG_x,
--ISNULL(STDEV(CASE WHEN x <> -999 AND flag=0 and
cast_id<>'full' THEN x END),0)AS STDEV_x,
(SELECT SUM(CASE WHEN cast_id<>'full' THEN flag END) FROM tblLog WHERE
tblStation.Station_Number=tblLog.Station_Number) AS SUM_flag,
(SELECT COUNT(CASE WHEN cast_id<>'full' THEN flag END) FROM tblLog
WHERE tblStation.Station_Number=tblLog.Station_Number ) AS COUNT_flag,
COUNT(flag) as COUNT_flag2
FROM
((tblStation LEFT JOIN tblLog
ON (tblStation.Station_Number = tblLog.Station_Number))
LEFT JOIN tblData
ON (tblLog.Log_Index = tblData.Log_Index
AND z<=3))
GROUP BY
tblStation.Station_Number
ORDER BY
tblStation.Station_Number

Help with "Create View" statement and Eorror Message

Hi all,
I am trying to create a view with approx. 3000 columns... and got the following error message:

"CREATE VIEW failed because column 'HSEPRIN' in view 'MyTestView' exceeds the maximum of 1024 columns.

Is it mean the max number of columns for each table is 1024? I thought in SQL server the table can contain as much information as possible.
Anyone can help to answer my question?

Thank you in advance.As much information vertically, not horizontally.

Frankly, if you are trying to create a view with 3000 columns, the problem is in your design, not SQL Server's limitations!

Why are you doing this? Maybe somebody here can find a better approach for you to take.|||No, a single row in a table can only contain a bit short of 8 kilobytes. A given row in a result set (therefore in a view) can only contain 1024 columns, and there are some limitations on the 1024th column.

That said, how on earth would you make use of a view that wide ?!?! What would you do with it ?

-PatP|||I definitely recommend printing it on legal-size paper set to landscape orientation, using Arial Narrow font.|||You can you go over the row limit in a physical table but you just get some warning about inserts and updates. See it often in poor designs or lack there of.|||Are you by any chance trying to crosstab that 40-year history you were talking about in that other post? That's the only thing I can think of that would give you that many columns. :D|||I do not have to see/create all the columns. However I would like to know the limitation. I just tried running the same query again for 950 columns which was succussful.
Maybe I have to run 3000 columns separately to create 3000/950 views. Can I union them together as a one object/something? In addition,
How am I going to update number of views on daily basis? :confused:|||All kidding aside.

Why don't you provide us with some more information about what exactly you are trying to do? Of course the ddl might be too much 411. But if you give us enough info one of might come up with something or at least some advice.

Some of the folks in this forum are as smart as they think they are. Myself excluded. I am as dumb as I seem. darrrrrrrrrrrrrrrrrrrrrrr!!!!!!!!!!|||This post is related to the one "updating daily information in a history table (was "Help-Brainstorming")"... which provides details.

Sorry about the confusion. And thank you for the help.|||Good Morning All,
Hope you all had a great weekend!
I think I am probably asking a silly question but...
I created a view by using the following statement:

create view TestView1
as
select date as Date,
XXXXX= sum(case when ID='XXXXX' then Field1 else 0 end),
YYYYY= sum(case when ID='YYYYY' then Field1 else 0 end)
from MyTable
Group by Date

The structure of MyTable is:
Date(datetime) ID(char 10) Field1(float)
1/1/65 XXXXX -999.999
1/4/65 XXXXX -999.999
...
2/24/05 XXXXX 500
2/25/05 XXXXX 550
1/1/65 YYYYY -999.999
1/4/65 YYYYY -999.999
...
2/24/05 YYYYY 600
2/25/05 YYYYY 650

when I run "select * from TestView order by date"
The actual results I got:
Date XXXXX YYYYY
1/1/65 0.0 0.0
1/4/65 0.0 0.0
...
2/24/05 500 600
2/25/05 550 650

This is the results I should expect:
Date XXXXX YYYYY
1/1/65 -999.999 -999.999
1/4/65 -999.999 -999.999
...
2/24/05 500 600
2/25/05 550 650

What is wrong with my create view statement? Do I have to specify the datatype?

Thank you for the help in advance.|||What is wrong with it?

The question is, "What good is it?"

What is the practical use of a view with 3000 cross-tabbed columns? You can't print it. You can't display it. You can't use it practically in any other views or procedures.

What are you planning to do with this?|||This is going to be the data source for another application (Matlab).
And this is desired format. It does not matter if I can display them all as long as I can display partially to make sure the information is there and the expected data format.

Should I put information into a table instead of View?

Thank you for the help!|||MatLab can't accept normalized data?

And it can't do its own crosstabs?

That is pretty weak.

I'm sorry, but I just can't suggest any solution along the lines you are thinking, because I think it is going to cause you severe problems in the future.|||Matlab is a statistic package to do math calculation and generate graphs. It may take 5+ hours to run the results therefore I am thinking to use SQL to generate the expected data source format to feed into Matlab.
If you think the only possible solution should be on Matlab side, I guess I have to work on that.
However, do you know why my actual result from my sql statement shows data as "0.0" instead of expected "-999.999" which is stored in the table?

Any suggestion and comments are truely appreciated!
shiparsons|||I don't have that problem..you probably shouldn't be using float though...

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99([ID] varchar(15), [Date] datetime, Field1 float)
GO

INSERT INTO myTable99([Date],[ID],Field1)
SELECT '1/1/65', 'XXXXX', -999.999 UNION ALL
SELECT '1/4/65', 'XXXXX', -999.999 UNION ALL
SELECT '2/24/05', 'XXXXX', 500 UNION ALL
SELECT '2/25/05', 'XXXXX', 550 UNION ALL
SELECT '1/1/65', 'YYYYY', -999.999 UNION ALL
SELECT '1/4/65', 'YYYYY', -999.999 UNION ALL
SELECT '2/24/05', 'YYYYY', 600 UNION ALL
SELECT '2/25/05', 'YYYYY', 650
GO

CREATE VIEW myView99
AS
SELECT [Date]
, SUM(CASE WHEN [ID]='XXXXX' THEN Field1 ELSE 0 END) AS X
, SUM(CASE WHEN [ID]='YYYYY' THEN Field1 ELSE 0 END) AS Y
FROM MyTable99
GROUP BY [Date]
GO

SELECT * FROM myView99
GO

SET NOCOUNT OFF
DROP VIEW myView99
DROP TABLE myTable99
GO|||Brett,
Thank you for the help! You are right. It works fine.
The problem was on my end. In my statement I had a space was quoted in for ID field. (ID=' XXXXX ' instead of ID='XXXXX')

:p

Help when renaming server

This is a multi-part message in MIME format.
--=_NextPart_000_0008_01C5198B.423A4230
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi.
Due to corporate guidelines we are "forced" to change the name of our = Windows 2000 server which runs our precious SQL 7.0 server. I guess = there are some issues I have to deal with afterwards to get the database = engine up and about. Do any of you have good resources (URL's) or other = information which you may share with me, to enable us rename our server = without to much hassle. Please.
Bst Regards
Mr. Smith
--=_NextPart_000_0008_01C5198B.423A4230
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hi.
Due to corporate guidelines we are = "forced" to change the name of our Windows 2000 server which runs our precious SQL = 7.0 server. I guess there are some issues I have to deal with afterwards to = get the database engine up and about. Do any of you have good resources (URL's) = or other information which you may share with me, to enable us rename our server = without to much hassle. Please.

Bst Regards
Mr. Smith
--=_NextPart_000_0008_01C5198B.423A4230--This is a multi-part message in MIME format.
--=_NextPart_000_0178_01C5199A.A55BC500
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi
Look at sp_dropserver and sp_addserver in the BOL.
"Mr. Smith" <nospam@.blindfolded.gone> wrote in message =news:uB9V6KYGFHA.444@.TK2MSFTNGP15.phx.gbl...
Hi.
Due to corporate guidelines we are "forced" to change the name of our =Windows 2000 server which runs our precious SQL 7.0 server. I guess =there are some issues I have to deal with afterwards to get the database =engine up and about. Do any of you have good resources (URL's) or other =information which you may share with me, to enable us rename our server =without to much hassle. Please.
Bst Regards
Mr. Smith
--=_NextPart_000_0178_01C5199A.A55BC500
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML xmlns:o =3D "urn:schemas-microsoft-com:office:office"><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1491" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT size=3D2>Hi</FONT></DIV>
<DIV><FONT size=3D2>Look at sp_dropserver and sp_addserver in the BOL.</FONT></DIV>
<DIV> </DIV>
<BLOCKQUOTE dir=3Dltr style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV>"Mr. Smith" <<A =href=3D"mailto:nospam@.blindfolded.gone">nospam@.blindfolded.gone</A>> =wrote in message <A =href=3D"news:uB9V6KYGFHA.444@.TK2MSFTNGP15.phx.gbl">news:uB9V6KYGFHA.444@.T=K2MSFTNGP15.phx.gbl</A>...</DIV>
<DIV>Hi.</DIV>
<DIV>
<P class=3DMsoNormal style=3D"MARGIN: 0cm 0cm 0pt"><SPAN style=3D"mso-ansi-language: EN-US">Due to corporate guidelines we are ="forced" to change the name of our Windows 2000 server which runs our precious =SQL 7.0 server. I guess there are some issues I have to deal with afterwards =to get the database engine up and about. Do any of you have good resources =(URL's) or other information which you may share with me, to enable us rename our =server without to much hassle. Please.<o:p></o:p></SPAN></P></DIV>
<DIV> </DIV>
<DIV>Bst Regards</DIV>
<DIV>Mr. Smith</DIV></BLOCKQUOTE></BODY></HTML>
--=_NextPart_000_0178_01C5199A.A55BC500--|||This is a multi-part message in MIME format.
--=_NextPart_000_0140_01C51993.260B9F70
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi
After having renamed the server, you just need to run sp_dropserver and =then sp_addserver. You can look up the syntax in BOL.
Regards
Steen "Mr. Smith" <nospam@.blindfolded.gone> skrev i en meddelelse =news:uB9V6KYGFHA.444@.TK2MSFTNGP15.phx.gbl...
Hi.
Due to corporate guidelines we are "forced" to change the name of our =Windows 2000 server which runs our precious SQL 7.0 server. I guess =there are some issues I have to deal with afterwards to get the database =engine up and about. Do any of you have good resources (URL's) or other =information which you may share with me, to enable us rename our server =without to much hassle. Please.
Bst Regards
Mr. Smith
--=_NextPart_000_0140_01C51993.260B9F70
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML xmlns:o =3D "urn:schemas-microsoft-com:office:office"><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1458" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Hi</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>After having renamed the server, you =just need to run sp_dropserver and then sp_addserver. You can look up the syntax in BOL.</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>Regards</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Steen</FONT> </DIV>
<BLOCKQUOTE dir=3Dltr style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV>"Mr. Smith" <<A =href=3D"mailto:nospam@.blindfolded.gone">nospam@.blindfolded.gone</A>> =skrev i en meddelelse <A =href=3D"news:uB9V6KYGFHA.444@.TK2MSFTNGP15.phx.gbl">news:uB9V6KYGFHA.444@.T=K2MSFTNGP15.phx.gbl</A>...</DIV>
<DIV>Hi.</DIV>
<DIV>
<P class=3DMsoNormal style=3D"MARGIN: 0cm 0cm 0pt"><SPAN style=3D"mso-ansi-language: EN-US">Due to corporate guidelines we are ="forced" to change the name of our Windows 2000 server which runs our precious =SQL 7.0 server. I guess there are some issues I have to deal with afterwards =to get the database engine up and about. Do any of you have good resources =(URL's) or other information which you may share with me, to enable us rename our =server without to much hassle. Please.<o:p></o:p></SPAN></P></DIV>
<DIV> </DIV>
<DIV>Bst Regards</DIV>
<DIV>Mr. Smith</DIV></BLOCKQUOTE></BODY></HTML>
--=_NextPart_000_0140_01C51993.260B9F70--|||There's more to it than only sp_dropserver and sp_addserver. Especially for a 7.0 SQL Server. Check
out http://www.karaszi.com/SQLServer/info_change_server_name.asp for more information.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mr. Smith" <nospam@.blindfolded.gone> wrote in message news:uB9V6KYGFHA.444@.TK2MSFTNGP15.phx.gbl...
Hi.
Due to corporate guidelines we are "forced" to change the name of our Windows 2000 server which runs
our precious SQL 7.0 server. I guess there are some issues I have to deal with afterwards to get the
database engine up and about. Do any of you have good resources (URL's) or other information which
you may share with me, to enable us rename our server without to much hassle. Please.
Bst Regards
Mr. Smith|||Thanks Tibor.
The URL whas just what I needed.
Mr. Smith
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eYs%235vYGFHA.3732@.TK2MSFTNGP14.phx.gbl...
> There's more to it than only sp_dropserver and sp_addserver. Especially
for a 7.0 SQL Server. Check
> out http://www.karaszi.com/SQLServer/info_change_server_name.asp for more
information.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Mr. Smith" <nospam@.blindfolded.gone> wrote in message
news:uB9V6KYGFHA.444@.TK2MSFTNGP15.phx.gbl...
> Hi.
> Due to corporate guidelines we are "forced" to change the name of our
Windows 2000 server which runs
> our precious SQL 7.0 server. I guess there are some issues I have to deal
with afterwards to get the
> database engine up and about. Do any of you have good resources (URL's) or
other information which
> you may share with me, to enable us rename our server without to much
hassle. Please.
>
> Bst Regards
> Mr. Smith
>

Help Using Asymetric/Symetric key in a Scalar UDF

The error message I get is as follows:
Invalid use of side-effecting or time-dependent operator in 'OPEN SYMMETRIC KEY' within a function.
&
Invalid use of side-effecting or time-dependent operator in 'CLOSE SYMMETRIC KEY' within a function.

Here is the code I am trying to implement:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER FUNCTION [dbo].[func_GetSIMSPassPhrase]
(
)
RETURNS varchar(30)
AS
BEGIN
OPEN SYMMETRIC KEY sims_sym_Key DECRYPTION BY ASYMMETRIC KEY sims_asym_key
DECLARE @.GUID UNIQUEIDENTIFIER
SET @.GUID = (SELECT key_guid FROM sys.symmetric_keys WHERE name = 'sims_sym_Key')
DECLARE @.passphrase varchar(30)
SELECT @.passphrase = (SELECT CAST(DecryptByKey(EncField) AS VARCHAR(30)) FROM tblEncryptTest)
CLOSE SYMMETRIC KEY sims_sym_Key
RETURN @.passphrase
END

Anyone have any suggestions? TIA

You cannot use OPEN SYMMETRIC KEY in a function. Write a procedure instead - you can use an OUTPUT argument to return the passphrase. For an example, see http://blogs.msdn.com/lcris/archive/2006/01/13/512829.aspx.

Thanks
Laurentiu

|||

Laurentiu,

I found your article after I posted that message. I am still getting this error though:

SELECT permission denied on object 'symmetric_keys', database 'mssqlsystemresource', schema 'sys'.

Here is what I have done so far. I moved OPEN SYMMETRIC KEY to a stored procedure (listed below):

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[uspPassPhraseGet]
@.pss varchar(30) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
OPEN SYMMETRIC KEY sims_sym_Key DECRYPTION BY ASYMMETRIC KEY sims_asym_key
DECLARE @.GUID UNIQUEIDENTIFIER
SET @.GUID = (SELECT key_guid FROM sys.symmetric_keys WHERE name = 'sims_sym_Key')
SET @.pss = (SELECT CAST(DecryptByKey(EncField) AS VARCHAR(30)) FROM tblEncryptTest)
CLOSE SYMMETRIC KEY sims_sym_Key
END

I granted execute permissions to the role that contains the user I am using to access this stored procedure. I am calling this stored procedure from within another stored procedure to access the encrypted passphrase contained in a table encrypted by the Symmetric/Asymetric keys. See example below:

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[uspFreeFormList]
@.userid int
AS

DECLARE @.pss varchar(30)
EXEC [dbo].[uspPassPhraseGet] @.pss OUTPUT

SELECT
...
,CONVERT(varchar(max),DecryptByPassPhrase(@.pss, CONVERT(varchar(max),dbo.tbl_msg_app_freeform.title), 1, CONVERT(varbinary, 23))) as title
,CONVERT(varchar(max),DecryptByPassPhrase(@.pss, CONVERT(varchar(max),dbo.tbl_msg_app_freeform.description), 1, CONVERT(varbinary, 23))) as description
,CONVERT(varchar(max),DecryptByPassPhrase(@.pss, CONVERT(varchar(max),dbo.tbl_msg_app_freeform.shortdesc), 1, CONVERT(varbinary, 23))) as shortdesc,
...
FROM
...
WHERE
...

I know the permissions to the stored procedures are correct because if I set the @.pss output parameter in uspPassPhraseGet to a static string everything works fine. It is when I am accessing the symmetric key that I don't have select granted on sys.symmetric_keys. I have gone in and explicitly granted SELECT for the role I am using to sys.symmetric_keys. However this isn't working. What am I missing? Is there some archane setting I am missing? Also, on a more academic note, is this the right approach to protect a passphrase used in the DecryptByPassPhrase/EncryptByPassPhrase function or is there a better suggestion/scenario to use?

Thanks for your time and attention,

Mike

|||

It looks like you have explicitly denied SELECT permissions on the sys.symmetric_keys catalog to either the use or one of the roles he belongs to.

To see to whom you have denied the permissions, execute the following query in your database:

select user_name(grantee_principal_id) from sys.database_permissions where state = 'D' and major_id = object_id('sys.symmetric_keys')

Then, take the result and execute

revoke select on sys.symmetric_keys to grantee

replacing grantee with the result of the previous query.

Regarding the code, why do you retrieve that GUID in uspPassPhraseGet? And why do you use a passphrase for encryption instead of using the symmetric key itself?

Thanks
Laurentiu

HELP URGENT!

Hi,
I can access my database. I get the message ......cant access...in single
user mode. How can I reset this?
Thanks
The first thing I would try is from EM, right-click on the
database>properties>Options tab, uncheck Restrict Access if it's checked.
"Chris" wrote:

> Hi,
> I can access my database. I get the message ......cant access...in single
> user mode. How can I reset this?
> Thanks
|||Thanks!
"Jack" wrote:
[vbcol=seagreen]
> The first thing I would try is from EM, right-click on the
> database>properties>Options tab, uncheck Restrict Access if it's checked.
> "Chris" wrote:

HELP URGENT!

Hi,
I can access my database. I get the message ......cant access...in single
user mode. How can I reset this?
ThanksThe first thing I would try is from EM, right-click on the
database>properties>Options tab, uncheck Restrict Access if it's checked.
"Chris" wrote:

> Hi,
> I can access my database. I get the message ......cant access...in sing
le
> user mode. How can I reset this?
> Thanks|||Thanks!
"Jack" wrote:
[vbcol=seagreen]
> The first thing I would try is from EM, right-click on the
> database>properties>Options tab, uncheck Restrict Access if it's checked.
> "Chris" wrote:
>

HELP URGENT!

Hi,
I can access my database. I get the message ......cant access...in single
user mode. How can I reset this?
ThanksThe first thing I would try is from EM, right-click on the
database>properties>Options tab, uncheck Restrict Access if it's checked.
"Chris" wrote:
> Hi,
> I can access my database. I get the message ......cant access...in single
> user mode. How can I reset this?
> Thanks|||Thanks!
"Jack" wrote:
> The first thing I would try is from EM, right-click on the
> database>properties>Options tab, uncheck Restrict Access if it's checked.
> "Chris" wrote:
> > Hi,
> > I can access my database. I get the message ......cant access...in single
> > user mode. How can I reset this?
> >
> > Thanks