BAFFLED! Why won't this code work?!?!

Hello,
The below code is intended to create a sp, then loop through a set of
values, each time running the sp with the new value. The CREATE PROC
executes fine, but I get an error when attempting the loop: "Invalid
column name 'tblIR_LB_2'."
Your help appreciated!
CREATE PROC sp_CreateIRTables ( @.tbl smallint)
AS
DECLARE @.SQL varchar(999)
SET @.SQL = 'CREATE TABLE [dbo].[tblIR_LB_' + @.tblname + '] (
[dteDate] [smalldatetime] NULL ,
[sglSTDEV(upper)] [float] NULL ,
[sglSTDEV(lower)] [float] NULL ,
[intMA] [smallint] NULL ,
[dblPL] [float] NULL ,
[dblSTDEV] [float] NULL ,
[dblIR] [float] NULL ,
[intTotalDays] [int] NULL ,
[intExposure] [int] NULL ) ON [PRIMARY]'
EXEC (@.SQL)
DECLARE @.i smallint
SET @.i = 1
WHILE (@.i <= 32)
BEGIN
EXECUTE sp_CreateIRTables @.i
SET @.i = @.i + 1
END> CREATE PROC sp_CreateIRTables ( @.tbl smallint)
DON'T USE sp_ PREFIX!

> SET @.SQL = 'CREATE TABLE [dbo].[tblIR_LB_' + @.tblname + '] (
Is it @.tbl or @.tblname? Pick one.|||> SET @.SQL = 'CREATE TABLE [dbo].[tblIR_LB_' + @.tblname + '] (
@.tblname is, for some reason, a SMALLINT, not any kind of string.
First, I suggest not using this kind of unmanageable scheme.
But to solve your issue in the short term, you can convert to a character
type, e.g.
SET @.SQL = 'CREATE TABLE [dbo].[tblIR_LB_' + CONVERT(VARCHAR(12), @.tblname)
+ '] (|||You can't have a variable in the table name of a CREATE TABLE unless you use
dynamic SQL to create
the table. Why not create a temp table instead?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Butaambala" <brogers75@.yahoo.com> wrote in message
news:1131645127.077192.239150@.f14g2000cwb.googlegroups.com...
> Hello,
> The below code is intended to create a sp, then loop through a set of
> values, each time running the sp with the new value. The CREATE PROC
> executes fine, but I get an error when attempting the loop: "Invalid
> column name 'tblIR_LB_2'."
> Your help appreciated!
>
> CREATE PROC sp_CreateIRTables ( @.tbl smallint)
> AS
> DECLARE @.SQL varchar(999)
> SET @.SQL = 'CREATE TABLE [dbo].[tblIR_LB_' + @.tblname + '] (
> [dteDate] [smalldatetime] NULL ,
> [sglSTDEV(upper)] [float] NULL ,
> [sglSTDEV(lower)] [float] NULL ,
> [intMA] [smallint] NULL ,
> [dblPL] [float] NULL ,
> [dblSTDEV] [float] NULL ,
> [dblIR] [float] NULL ,
> [intTotalDays] [int] NULL ,
> [intExposure] [int] NULL ) ON [PRIMARY]'
> EXEC (@.SQL)
>
> DECLARE @.i smallint
> SET @.i = 1
> WHILE (@.i <= 32)
> BEGIN
> EXECUTE sp_CreateIRTables @.i
> SET @.i = @.i + 1
> END
>

Bad results from my MDX Adventure Works query

I have an Adventure Works MDX query that I want to return all the male employees and their total reseller-sales (including the people below them)

Select [Measures].[Reseller Sales-Sales Amount] on Columns,
non empty
Exists(
[Employee].[Employees].AllMembers
,[Employee].[Gender].&[M]
) on Rows
from [Analysis Services Tutorial]

which, when run, returns

Reseller Sales-Sales Amount
All Employees $80,450,596.98
Ken J. Sánchez $80,450,596.98
Brian S. Welcker $80,450,596.98
Amy E. Alberts $15,535,946.26
Ranjit R. Varkey Chudukatil $4,509,888.93
Stephen Y. Jiang $63,320,315.35
David R. Campbell $3,729,945.35
Garrett R. Vargas $3,609,447.22
Jos Edvaldo. Saraiva $5,926,418.36
Michael G. Blythe $9,293,903.01
Shu K. Ito $6,427,005.56
Stephen Y. Jiang $1,092,123.86
Tete A. Mensa-Annan $2,312,545.69
Tsvi Michael. Reiter $7,171,012.75
Syed E. Abbas $1,594,335.38
Syed E. Abbas $172,524.45

so there are three problems with this result, and I think they all have 1 solution. First, I don't want the 'All Employees' row. Second, there is a female in my results, seemingly because this female is the supervisor of some of the males. I asked for no females in my query. Third, Syed shows up twice, because he is a supervisor and a salesman himself. What I want are these results..

Ken J. Sánchez $80,450,596.98
Brian S. Welcker $80,450,596.98
Ranjit R. Varkey Chudukatil $4,509,888.93
Stephen Y. Jiang $63,320,315.35
David R. Campbell $3,729,945.35
Garrett R. Vargas $3,609,447.22
Jos Edvaldo. Saraiva $5,926,418.36
Michael G. Blythe $9,293,903.01
Shu K. Ito $6,427,005.56
Stephen Y. Jiang $1,092,123.86
Tete A. Mensa-Annan $2,312,545.69
Tsvi Michael. Reiter $7,171,012.75
Syed E. Abbas $1,594,335.38

Notice that there are no "All Employees", the woman is gone, and Syed is only a supervisor, and not an underling also. What MDX query would give me these results?

Thanks,

Todd Wilder

Referring to my response to your earlier post, this query seems to return the results you want - except for the order:

Select [Measures].[Reseller Sales Amount] on Columns,
non empty Generate(exists([Employee].[Employee].[Employee],
[Employee].[Gender].&[M]),
{LinkMember([Employee].[Employee].CurrentMember,
[Employee].[Employees])}) on Rows
from [Adventure Works]

|||Your queries dont seem to return anything on my cubes - your measure is named slightly different then mine and I don't have any tuples like [Employee].[Employee].[Employee]. Where did your cube come from?|||

Are you aware of the Adventure Works standard sample cube?

http://msdn2.microsoft.com/en-us/library/ms143804.aspx

>>

SQL Server 2005 Books Online

Running Setup to Install AdventureWorks Sample Databases and Samples

Updated: 17 July 2006

The AdventureWorks (OLTP), AdventureWorksDW (data warehouse), and Adventure Works DW (analysis services) sample databases, as well as the companion samples, are not installed by default in SQL Server 2005. You can download these from SQL Server 2005 Samples and Sample Databases at the Microsoft Download Center, or you can use the following procedures to install the sample databases and samples during or after setup. Additional instructions for deploying the Adventure Works DW analysis services project are also provided.

...

>>

bad page ID in MSDB DB

Hi All,
Greeting,

Sql Server 7
OS: Win NT

In the sql server logs i see the below error alerts

I/O error (bad page ID) detected during read of BUF pointer = 0x11e09e80, page ptr = 0x446b4000, pageid = (0x1:0x2c78), dbid = 4, status = 0x801, file = F:\MSSQL7\DATA\msdbdata.mdf..

Error: 823, Severity: 24, State: 1

Please help me in this.

Thanks in Advance
AdilHere's one place to look: http://support.microsoft.com/kb/q281809/

Bad day with .net

Hi

I am new to ASP.net and trying to connecting with SQL Server in Visual Studio by following instruction are given in below url page.

http://www.codeproject.com/aspnet/SQLConnect.asp?

I have done all steps upto where below code is shown in page

*********************************************************

Private Sub Page_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
SqlConnection1.Open()
If SqlConnection1.State = ConnectionState.Open Then
Label4.Text = "SQL Connection is open"
Else
Label4.Text = "SQL Connection is closed"
End If
End Sub

******************************************************

But I am getting error !

Server Error in '/WebService1' Application.

---

Login failed for user 'COMP3\ASPNET'.

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: Login failed for user 'COMP3\ASPNET'.

Source Error:

Line 126: 'Put user code to initialize the page hereLine 127:Line 128:SqlConnection1.Open()Line 129:If SqlConnection1.State = ConnectionState.Open ThenLine 130: Label1.Text = "SQL Connection is open"

Source File:c:\inetpub\wwwroot\WebService1\SqlConnect.aspx.vb Line:128

Stack Trace:

[SqlException: Login failed for user 'COMP3\ASPNET'.] System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) +474 System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) +372 System.Data.SqlClient.SqlConnection.Open() +384 WebService1.SqlConnect.Page_Load(Object sender, EventArgs e) in c:\inetpub\wwwroot\WebService1\SqlConnect.aspx.vb:128 System.Web.UI.Control.OnLoad(EventArgs e) +67 System.Web.UI.Control.LoadRecursive() +35 System.Web.UI.Page.ProcessRequestMain() +731

---

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

Note : All steps are finely executed by me !

One thing more As one instruction in Help Page that "right click theSQLConnection1 object to study its properties."

But i am not able to see the properties of it becoz properties tag is disable in right click and i am strange with this experience in Visual Studio that i am not able to edit the code in Editor area ( Where Below code in seen)

Private Sub Page_Load(ByVal sender As System.Object, _ByVal e As .EventArgs) Handles MyBase.Load
'Put user code to initialize the page here

End Sub

So I am totally confused or unable to find out the problem that in my Visual Studio Or Connectivity?

PLEASE HELP ME TO RESOLVE this issue!!

Check your connection string and see if you are providing the correct credentials to access the database.|||

Connection String is automatically generated by Visual Studio.Net aftter dragging table 'employee' from server explorer

So I think it not changeable.

Suggest me other way to connecting with database

|||

See "SQL Server" in this document:How To: Use the Network Service Account to Access Resources in ASP.NET.

In your case, though, you are using the ASPNET account, not NETWORK SERVICE, but all the same steps still apply.

The connection string is still configurable, though.

backup, restore commands for db with multiple data files and fileg

I use the below code to backup the database with multiple datafiles, and the
n
try to restore with the command below, but it throws error.
How do I perform backup/restore of database with multiple data files and log
files?
BACKUP DATABASE abc
FILE = 'abc_Data', FILEGROUP = 'PRIMARY',
FILE = 'abc_DataLOB', FILEGROUP = 'FG_abc_DataLOB',
FILE = 'abc_dat', FILEGROUP = 'FG_abc_dat',
FILE = 'abc_dat2', FILEGROUP = 'FG_abcdat2',
FILE = 'abc_dat3', FILEGROUP = 'FG_abcdat3',
TO DISK = 'd:\SQLBackups\abc_200507121535.BAK'
WITH INIT, STATS = 10
go
RESTORE DATABASE abc
FROM DISK ='d:\SQLBackups\abc_200507121535.BAK'
WITH
MOVE 'abc_Data' TO 'd:\MSSQL\MSSQL\data\abc_Data.MDF',
MOVE 'abc_DataLOB' TO
'd:\MSSQL\MSSQL\data\abc_DataLOB.NDF',
MOVE 'abc_dat' TO 'd:\MSSQL\MSSQL\data\abc_dat.NDF' ,
MOVE 'abc_dat2' TO
'd:\MSSQL\MSSQL\data\abc_dat2.NDF' ,
MOVE 'abc_dat3' TO 'd:\MSSQL\MSSQL\data\abc_dat3.NDF',
MOVE 'abc_Log1' TO
'D:\MSSQL\MSSQL\Data\abc_Log1.LDF',
MOVE 'abc_Log2' TO 'D:\MSSQL\MSSQL\Data\abc_Log2.LDF',
RECOVERY, STATS = 10
GOBackup the entire db instead of backing up specific files or filegroups.
BACKUP DATABASE abc
TO DISK = 'd:\SQLBackups\abc_200507121535.BAK'
WITH INIT, STATS = 10
go
AMB
"Pari" wrote:

> I use the below code to backup the database with multiple datafiles, and t
hen
> try to restore with the command below, but it throws error.
> How do I perform backup/restore of database with multiple data files and l
og
> files?
> BACKUP DATABASE abc
> FILE = 'abc_Data', FILEGROUP = 'PRIMARY',
> FILE = 'abc_DataLOB', FILEGROUP = 'FG_abc_DataLOB',
> FILE = 'abc_dat', FILEGROUP = 'FG_abc_dat',
> FILE = 'abc_dat2', FILEGROUP = 'FG_abcdat2',
> FILE = 'abc_dat3', FILEGROUP = 'FG_abcdat3',
> TO DISK = 'd:\SQLBackups\abc_200507121535.BAK'
> WITH INIT, STATS = 10
> go
> RESTORE DATABASE abc
> FROM DISK ='d:\SQLBackups\abc_200507121535.BAK'
> WITH
> MOVE 'abc_Data' TO 'd:\MSSQL\MSSQL\data\abc_Data.MDF',
> MOVE 'abc_DataLOB' TO
> 'd:\MSSQL\MSSQL\data\abc_DataLOB.NDF',
> MOVE 'abc_dat' TO 'd:\MSSQL\MSSQL\data\abc_dat.NDF' ,
> MOVE 'abc_dat2' TO
> 'd:\MSSQL\MSSQL\data\abc_dat2.NDF' ,
> MOVE 'abc_dat3' TO 'd:\MSSQL\MSSQL\data\abc_dat3.NDF',
> MOVE 'abc_Log1' TO
> 'D:\MSSQL\MSSQL\Data\abc_Log1.LDF',
> MOVE 'abc_Log2' TO 'D:\MSSQL\MSSQL\Data\abc_Log2.LDF',
> RECOVERY, STATS = 10
> GO
>

backup, restore commands for db with multiple data files and fileg

I use the below code to backup the database with multiple datafiles, and then
try to restore with the command below, but it throws error.
How do I perform backup/restore of database with multiple data files and log
files?
BACKUP DATABASE abc
FILE = 'abc_Data', FILEGROUP = 'PRIMARY',
FILE = 'abc_DataLOB', FILEGROUP = 'FG_abc_DataLOB',
FILE = 'abc_dat', FILEGROUP = 'FG_abc_dat',
FILE = 'abc_dat2', FILEGROUP = 'FG_abcdat2',
FILE = 'abc_dat3', FILEGROUP = 'FG_abcdat3',
TO DISK = 'd:\SQLBackups\abc_200507121535.BAK'
WITH INIT, STATS = 10
go
RESTORE DATABASE abc
FROM DISK ='d:\SQLBackups\abc_200507121535.BAK'
WITH
MOVE 'abc_Data' TO 'd:\MSSQL\MSSQL\data\abc_Data.MDF',
MOVE 'abc_DataLOB' TO
'd:\MSSQL\MSSQL\data\abc_DataLOB.NDF',
MOVE 'abc_dat' TO 'd:\MSSQL\MSSQL\data\abc_dat.NDF' ,
MOVE 'abc_dat2' TO
'd:\MSSQL\MSSQL\data\abc_dat2.NDF' ,
MOVE 'abc_dat3' TO 'd:\MSSQL\MSSQL\data\abc_dat3.NDF',
MOVE 'abc_Log1' TO
'D:\MSSQL\MSSQL\Data\abc_Log1.LDF',
MOVE 'abc_Log2' TO 'D:\MSSQL\MSSQL\Data\abc_Log2.LDF',
RECOVERY, STATS = 10
GOBackup the entire db instead of backing up specific files or filegroups.
BACKUP DATABASE abc
TO DISK = 'd:\SQLBackups\abc_200507121535.BAK'
WITH INIT, STATS = 10
go
AMB
"Pari" wrote:
> I use the below code to backup the database with multiple datafiles, and then
> try to restore with the command below, but it throws error.
> How do I perform backup/restore of database with multiple data files and log
> files?
> BACKUP DATABASE abc
> FILE = 'abc_Data', FILEGROUP = 'PRIMARY',
> FILE = 'abc_DataLOB', FILEGROUP = 'FG_abc_DataLOB',
> FILE = 'abc_dat', FILEGROUP = 'FG_abc_dat',
> FILE = 'abc_dat2', FILEGROUP = 'FG_abcdat2',
> FILE = 'abc_dat3', FILEGROUP = 'FG_abcdat3',
> TO DISK = 'd:\SQLBackups\abc_200507121535.BAK'
> WITH INIT, STATS = 10
> go
> RESTORE DATABASE abc
> FROM DISK ='d:\SQLBackups\abc_200507121535.BAK'
> WITH
> MOVE 'abc_Data' TO 'd:\MSSQL\MSSQL\data\abc_Data.MDF',
> MOVE 'abc_DataLOB' TO
> 'd:\MSSQL\MSSQL\data\abc_DataLOB.NDF',
> MOVE 'abc_dat' TO 'd:\MSSQL\MSSQL\data\abc_dat.NDF' ,
> MOVE 'abc_dat2' TO
> 'd:\MSSQL\MSSQL\data\abc_dat2.NDF' ,
> MOVE 'abc_dat3' TO 'd:\MSSQL\MSSQL\data\abc_dat3.NDF',
> MOVE 'abc_Log1' TO
> 'D:\MSSQL\MSSQL\Data\abc_Log1.LDF',
> MOVE 'abc_Log2' TO 'D:\MSSQL\MSSQL\Data\abc_Log2.LDF',
> RECOVERY, STATS = 10
> GO
>

backup, restore commands for db with multiple data files and fileg

I use the below code to backup the database with multiple datafiles, and then
try to restore with the command below, but it throws error.
How do I perform backup/restore of database with multiple data files and log
files?
BACKUP DATABASE abc
FILE = 'abc_Data', FILEGROUP = 'PRIMARY',
FILE = 'abc_DataLOB', FILEGROUP = 'FG_abc_DataLOB',
FILE = 'abc_dat', FILEGROUP = 'FG_abc_dat',
FILE = 'abc_dat2', FILEGROUP = 'FG_abcdat2',
FILE = 'abc_dat3', FILEGROUP = 'FG_abcdat3',
TO DISK = 'd:\SQLBackups\abc_200507121535.BAK'
WITH INIT, STATS = 10
go
RESTORE DATABASE abc
FROM DISK ='d:\SQLBackups\abc_200507121535.BAK'
WITH
MOVE 'abc_Data' TO 'd:\MSSQL\MSSQL\data\abc_Data.MDF',
MOVE 'abc_DataLOB' TO
'd:\MSSQL\MSSQL\data\abc_DataLOB.NDF',
MOVE 'abc_dat' TO 'd:\MSSQL\MSSQL\data\abc_dat.NDF' ,
MOVE 'abc_dat2' TO
'd:\MSSQL\MSSQL\data\abc_dat2.NDF' ,
MOVE 'abc_dat3' TO 'd:\MSSQL\MSSQL\data\abc_dat3.NDF',
MOVE 'abc_Log1' TO
'D:\MSSQL\MSSQL\Data\abc_Log1.LDF',
MOVE 'abc_Log2' TO 'D:\MSSQL\MSSQL\Data\abc_Log2.LDF',
RECOVERY, STATS = 10
GO
Backup the entire db instead of backing up specific files or filegroups.
BACKUP DATABASE abc
TO DISK = 'd:\SQLBackups\abc_200507121535.BAK'
WITH INIT, STATS = 10
go
AMB
"Pari" wrote:

> I use the below code to backup the database with multiple datafiles, and then
> try to restore with the command below, but it throws error.
> How do I perform backup/restore of database with multiple data files and log
> files?
> BACKUP DATABASE abc
> FILE = 'abc_Data', FILEGROUP = 'PRIMARY',
> FILE = 'abc_DataLOB', FILEGROUP = 'FG_abc_DataLOB',
> FILE = 'abc_dat', FILEGROUP = 'FG_abc_dat',
> FILE = 'abc_dat2', FILEGROUP = 'FG_abcdat2',
> FILE = 'abc_dat3', FILEGROUP = 'FG_abcdat3',
> TO DISK = 'd:\SQLBackups\abc_200507121535.BAK'
> WITH INIT, STATS = 10
> go
> RESTORE DATABASE abc
> FROM DISK ='d:\SQLBackups\abc_200507121535.BAK'
> WITH
> MOVE 'abc_Data' TO 'd:\MSSQL\MSSQL\data\abc_Data.MDF',
> MOVE 'abc_DataLOB' TO
> 'd:\MSSQL\MSSQL\data\abc_DataLOB.NDF',
> MOVE 'abc_dat' TO 'd:\MSSQL\MSSQL\data\abc_dat.NDF' ,
> MOVE 'abc_dat2' TO
> 'd:\MSSQL\MSSQL\data\abc_dat2.NDF' ,
> MOVE 'abc_dat3' TO 'd:\MSSQL\MSSQL\data\abc_dat3.NDF',
> MOVE 'abc_Log1' TO
> 'D:\MSSQL\MSSQL\Data\abc_Log1.LDF',
> MOVE 'abc_Log2' TO 'D:\MSSQL\MSSQL\Data\abc_Log2.LDF',
> RECOVERY, STATS = 10
> GO
>