CRecordset consumes SQL Server Memory

I have an MFC app with a thread that uses a ODBC consumer class to access a
SQL server. When I write the code different ways I get different results an
d
don't know why.
Example 1:
threadFunc()
{
CtblMyTable myRecordset(NULL);
while( !kill )
{
myRecordset.Open();
myRecordset.Close();
}
}
Example 2:
threadFunc()
{
while( !kill )
{
CtblMyTable myRecordset(NULL);
myRecordset.Open();
myRecordset.Close();
}
}
Example 3:
threadFunc()
{
while( !kill )
{
CDatabase db;
CtblMyTable myRecordset(&db);
myRecordset.Open();
myRecordset.Close();
db.Close();
}
}
Example 1 uses up no memory in the SQL server. Example 2 consumes memory in
the SQL server at a fast rate and eventually my "stolen pages" start to
increment. Example 3 consumes memory at a much slower rate, like less than
a
tenth of the rate.
Can anyone explain why the location of the declaration or the declaration of
a CDatabase object affects memory usage in my SQL Server? Is there a way
that I can get the memory to clean up in SQL Server? I eventually crash
every few ws because I run out of memory in SQL Server and I think it has
something to do with the fact that most of my code is not like example 1.
ThanksHi
Passing NULL to your recordset constructor to have a CDatabase object
constructed and connected for you automatically each time it is called. This
is probably not getting cleaned up until your procedure exits and the
Recordset is distroyed. Having a single CDatabase object for your
application will be more efficient.
John
"Jason Wood" <jason.wood@.woodtc.com.nospam> wrote in message
news:7918D6CB-4E57-4722-BB9A-64BBD9F6C6DE@.microsoft.com...
>I have an MFC app with a thread that uses a ODBC consumer class to access a
> SQL server. When I write the code different ways I get different results
> and
> don't know why.
> Example 1:
> threadFunc()
> {
> CtblMyTable myRecordset(NULL);
> while( !kill )
> {
> myRecordset.Open();
> myRecordset.Close();
> }
> }
>
> Example 2:
> threadFunc()
> {
> while( !kill )
> {
> CtblMyTable myRecordset(NULL);
> myRecordset.Open();
> myRecordset.Close();
> }
> }
>
> Example 3:
> threadFunc()
> {
> while( !kill )
> {
> CDatabase db;
> CtblMyTable myRecordset(&db);
> myRecordset.Open();
> myRecordset.Close();
> db.Close();
> }
> }
>
> Example 1 uses up no memory in the SQL server. Example 2 consumes memory
> in
> the SQL server at a fast rate and eventually my "stolen pages" start to
> increment. Example 3 consumes memory at a much slower rate, like less
> than a
> tenth of the rate.
> Can anyone explain why the location of the declaration or the declaration
> of
> a CDatabase object affects memory usage in my SQL Server? Is there a way
> that I can get the memory to clean up in SQL Server? I eventually crash
> every few ws because I run out of memory in SQL Server and I think it
> has
> something to do with the fact that most of my code is not like example 1.
> Thanks

creating temporary table with SELECT INTO

Hi

Dose any body know why a temporary table gets deleted after querying it the
first time (using SELECT INTO)?
When I run the code bellow I'm getting an error message when open the temp
table for the second time.
Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E37)
Invalid object name '#testtable'.

-----------------------
-----
cnn.Execute("SELECT category, product INTO #testtable FROM properties")
'--creating temporary TestTable and populate it with values from another
table

SET rst_testt = cnn.Execute("SELECT * from #testtable") '-- opening
the temporary TestTable
SET rst_testt2 = cnn.Execute("SELECT * from #testtable") '-- ERROR
opening the temporary TestTable for the second time (that where the error
occurred)

rst_testt2.Close '-- closing table connection
SET rst_testt2 = nothing

rst_testt.Close '-- closing table connection
SET rst_testt = nothing

cnn.Execute("DROP TABLE #testtable") '-- dropping the temporary
TestTable
'-----------------------
-----

But when I create the temp table first and then INSERT INTO that table some
values then it is working fine.
'-----------------------
-----
cnn.Execute("CREATE TABLE #testtable (category VARCHAR(3), product
VARCHAR(3))")
cnn.Execute("INSERT INTO #testtable VALUES('5','4')")

SET rst_testt = cnn.Execute("SELECT * from #testtable") '-- opening
the temporary TestTable
SET rst_testt2 = cnn.Execute("SELECT * from #testtable") '-- opening
the temporary TestTable for the second time

rst_testt2.Close '-- closing table connection
SET rst_testt2 = nothing

rst_testt.Close '-- closing table connection
SET rst_testt = nothing

cnn.Execute("DROP TABLE #testtable") '-- dropping the temporary
TestTable
'-----------------------
-----

Does any body know why the first code (SELECT INTO) is not working where the
second code it working?

regards,
goznal[posted and mailed, please reply in news]

gonzal (k2net@.dodo.com.au) writes:
> Dose any body know why a temporary table gets deleted after querying it
> the first time (using SELECT INTO)?
> When I run the code bellow I'm getting an error message when open the temp
> table for the second time.
> Error Type:
> Microsoft OLE DB Provider for SQL Server (0x80040E37)
> Invalid object name '#testtable'.
> -----------------------
> cnn.Execute("SELECT category, product INTO #testtable FROM properties")
> '--creating temporary TestTable and populate it with values from another
> table
> SET rst_testt = cnn.Execute("SELECT * from #testtable")
> '-- opening the temporary TestTable
> SET rst_testt2 = cnn.Execute("SELECT * from #testtable")
> '-- ERROR opening the temporary TestTable for the second time (that
> where the error occurred)

What's happening is that ADO is opening a second connection behind your
back. This has really not anything to do with how you created the table.

The reason that ADO opens an extra connection, is because there are
rows waiting to be fetched on the first connection, so ADO cannot
submit a query on that connection. I cannot really say why it only happened
in one place, but it may be related to the type of cursor you picked,
and the fact that in the second example, there is only one row in
the table.

Use a static client-side cursor. I would expect this to resovle the
problem.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland
You were right.
ADO is opening an extra connection thats why it is not working and the
second example is working because only one record was inserted. If I
change to second code to:

cnn.Execute("INSERT INTO #testtable VALUES('5','4')")
cnn.Execute("INSERT INTO #testtable VALUES('2','3')") -- inserting
second row

Then it is not working, same as the first example.

As you have suggested in your previous email to use a static client-side
cursor, I have change to code to:

set rst_tre = Server.CreateObject("ADODB.RecordSet"
rst_tre.CursorLocation = 1 '-- adUseClient
rst_tre.CursorType = 3 '-- adOpenStatic
rst_tre.LockType = 2 -- adLockOptimistic
sql_tre = "SELECT category, product INTO #testtable FROM products"
rst_tre.Open sql_tre,cnn
SET rst_tre = nothing

But it is still not working.
Any suggestion why?

I head a look at some logs from SQL:

SQL:BatchCompleted; SELECT category, product INTO #testtable FROM
products
SQL:BatchCompleted; SELECT * from #testtable
Audit Login -- network protocol:
SQL:BatchCompleted SELECT * from #testtable
Audit Logout

Any suggestions why ADO opens another connection after opening the
#testtable for the first time?

Another think I have noticed is that when I open the #testtable only
once it is working fine.

Thanks,
Gonzal

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||gonzal (nospam@.abc.abc) writes:
>
> set rst_tre = Server.CreateObject("ADODB.RecordSet"
> rst_tre.CursorLocation = 1 '-- adUseClient
> rst_tre.CursorType = 3 '-- adOpenStatic
> rst_tre.LockType = 2 -- adLockOptimistic
> sql_tre = "SELECT category, product INTO #testtable FROM products"
> rst_tre.Open sql_tre,cnn
> SET rst_tre = nothing
>
> But it is still not working.
> Any suggestion why?

I have to admit that I'm no ADO guru, so I cannot really say what is
happening. However I did a quick test, and this did not give me a
second connection:

Dim cnn As ADODB.Connection
Dim cnnErr As ADODB.Error
Set cnn = New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim rs2 As New ADODB.Recordset

On Error GoTo errHandle

Dim oCommand As ADODB.Command

cnn.ConnectionString = "Provider=" & Trim$(Provider.Text) & ";" & _
"Data Source=" & Trim$(DataSource.Text) & ";" & _
"User Id=" & Trim$(User.Text) & ";" & _
"Password=" & Trim$(Password.Text) & ";" & _
"Initial Catalog=" & Trim$(Database.Text) & ";" & _
"Use Procedure for Prepare=0"
cnn.ConnectionTimeout = 5
cnn.CursorLocation = adUseClient
cnn.Open

Set oCommand = CreateObject("ADODB.Command")
Set oCommand.ActiveConnection = cnn

oCommand.CommandType = adCmdText
oCommand.CommandText = "select name into #temp from sysobjects"
oCommand.Execute

rs.Open "SELECT * FROM #temp", cnn
rs2.Open "SELECT * FROM #temp", cnn

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Try to make the temporary table a global variable by using two #
characters like: ##testtable. This probably will solve your problem.

Greetings Sjaak

creating temp table inside SP

I am doing a code review of a stored procedure.
What the procedure does is to create a temp table and then based on parameters sent to
the procedure, call different stored procedures which inserts into the temp table just
created.
Are their any issues with procedures creating temp table on the fly. Any scalability
issues?
There is a good explanation in the BOL. See "DBCC DBREINDEX".
AMB
"rkusenet" wrote:

> I am doing a code review of a stored procedure.
> What the procedure does is to create a temp table and then based on parameters sent to
> the procedure, call different stored procedures which inserts into the temp table just
> created.
> Are their any issues with procedures creating temp table on the fly. Any scalability
> issues?
>
|||Sorry, wrong place.
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> There is a good explanation in the BOL. See "DBCC DBREINDEX".
>
> AMB
> "rkusenet" wrote:
|||Here's a short article - posted previously by Aaron - on the subject.
http://www.aspfaq.com/show.asp?id=2475
Enjoy.
"rkusenet" <usenet.rk@.gmail.com> wrote in message
news:3cstc2F6q880iU1@.individual.net...
>I am doing a code review of a stored procedure. What the procedure does is
>to create a temp table and then based on parameters sent to
> the procedure, call different stored procedures which inserts into the
> temp table just
> created.
> Are their any issues with procedures creating temp table on the fly. Any
> scalability
> issues?
>
|||"Michael C#" <howsa@.boutdat.com> wrote in message
news:ewjaY42RFHA.1348@.TK2MSFTNGP15.phx.gbl...
> Here's a short article - posted previously by Aaron - on the subject.
> http://www.aspfaq.com/show.asp?id=2475
>
excellent information there.
thanks a lot.

creating temp table inside SP

I am doing a code review of a stored procedure.
What the procedure does is to create a temp table and then based on parameters sent to
the procedure, call different stored procedures which inserts into the temp table just
created.
Are their any issues with procedures creating temp table on the fly. Any scalability
issues?There is a good explanation in the BOL. See "DBCC DBREINDEX".
AMB
"rkusenet" wrote:
> I am doing a code review of a stored procedure.
> What the procedure does is to create a temp table and then based on parameters sent to
> the procedure, call different stored procedures which inserts into the temp table just
> created.
> Are their any issues with procedures creating temp table on the fly. Any scalability
> issues?
>|||Sorry, wrong place.
AMB
"Alejandro Mesa" wrote:
> There is a good explanation in the BOL. See "DBCC DBREINDEX".
>
> AMB
> "rkusenet" wrote:
> > I am doing a code review of a stored procedure.
> > What the procedure does is to create a temp table and then based on parameters sent to
> > the procedure, call different stored procedures which inserts into the temp table just
> > created.
> > Are their any issues with procedures creating temp table on the fly. Any scalability
> > issues?
> >
> >|||Here's a short article - posted previously by Aaron - on the subject.
http://www.aspfaq.com/show.asp?id=2475
Enjoy.
"rkusenet" <usenet.rk@.gmail.com> wrote in message
news:3cstc2F6q880iU1@.individual.net...
>I am doing a code review of a stored procedure. What the procedure does is
>to create a temp table and then based on parameters sent to
> the procedure, call different stored procedures which inserts into the
> temp table just
> created.
> Are their any issues with procedures creating temp table on the fly. Any
> scalability
> issues?
>|||"Michael C#" <howsa@.boutdat.com> wrote in message
news:ewjaY42RFHA.1348@.TK2MSFTNGP15.phx.gbl...
> Here's a short article - posted previously by Aaron - on the subject.
> http://www.aspfaq.com/show.asp?id=2475
>
excellent information there.
thanks a lot.sql

creating temp table inside SP

I am doing a code review of a stored procedure.
What the procedure does is to create a temp table and then based on paramete
rs sent to
the procedure, call different stored procedures which inserts into the temp
table just
created.
Are their any issues with procedures creating temp table on the fly. Any sca
lability
issues?There is a good explanation in the BOL. See "DBCC DBREINDEX".
AMB
"rkusenet" wrote:

> I am doing a code review of a stored procedure.
> What the procedure does is to create a temp table and then based on parame
ters sent to
> the procedure, call different stored procedures which inserts into the tem
p table just
> created.
> Are their any issues with procedures creating temp table on the fly. Any s
calability
> issues?
>|||Sorry, wrong place.
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> There is a good explanation in the BOL. See "DBCC DBREINDEX".
>
> AMB
> "rkusenet" wrote:
>|||Here's a short article - posted previously by Aaron - on the subject.
http://www.aspfaq.com/show.asp?id=2475
Enjoy.
"rkusenet" <usenet.rk@.gmail.com> wrote in message
news:3cstc2F6q880iU1@.individual.net...
>I am doing a code review of a stored procedure. What the procedure does is
>to create a temp table and then based on parameters sent to
> the procedure, call different stored procedures which inserts into the
> temp table just
> created.
> Are their any issues with procedures creating temp table on the fly. Any
> scalability
> issues?
>|||"Michael C#" <howsa@.boutdat.com> wrote in message
news:ewjaY42RFHA.1348@.TK2MSFTNGP15.phx.gbl...
> Here's a short article - posted previously by Aaron - on the subject.
> http://www.aspfaq.com/show.asp?id=2475
>
excellent information there.
thanks a lot.

Creating tables with variables value?

Hi I would like to know how can I create a table with a veriable from my code.

This is my code:

DECLARE @.MTH INT
DECLARE @.MTH2 INT
DECLARE @.DAY INT
DECLARE @.BGN DATETIME
DECLARE @.END DATETIME
DECLARE @.ENDI INT
DECLARE @.DTCTRL DATETIME
DECLARE @.TBLNAME TABLE (NOME VARCHAR(30))
--||_@.MTH_ ===>_RANGE_MONTH/DAYS||
SET @.MTH = 11 + 1
_RANGE_MONTH">--||_@.MTH2_===>_RANGE_MONTH__||
SET @.MTH2 = 5
--||_||

SET @.BGN = CONVERT(VARCHAR(8),DATEADD(MM,-@.MTH,GETDATE()),112)
SET @.END = CONVERT(VARCHAR(8),GETDATE(),112)
SET @.DAY = 0

WHILE @.BGN <> @.END
BEGIN
WHILE CONVERT(VARCHAR(8),DATEADD(DD,-@.DAY,GETDATE()),112) = @.END
BEGIN
INSERT INTO @.TBLNAME
SELECT 'TB_CHEQUE_' + CONVERT(VARCHAR,DATEADD(DD,-@.DAY,GETDATE()),112)
SET @.DAY = @.DAY + 1
END
SET @.DTCTRL = CONVERT(VARCHAR,DATEADD(DD,-@.DAY,GETDATE()),112)
SET @.END = DATEADD(DD,-1,@.END)
END

SET @.ENDI = 0
WHILE @.MTH2 <> @.ENDI
BEGIN
INSERT INTO @.TBLNAME
SELECT 'TB_CHEQUE_' + LEFT(CONVERT(VARCHAR,DATEADD(MM,-@.ENDI,@.DTCTRL),112),6)
SET @.ENDI = @.ENDI + 1
END
SET @.DTCTRL = DATEADD(MM,-@.ENDI,@.DTCTRL)
SET @.ENDI = 0

WHILE @.ENDI <> 6
BEGIN
INSERT INTO @.TBLNAME
SELECT 'TB_CHEQUE_' + CAST(DATEPART(YY,DATEADD(YY,-@.ENDI, GETDATE())) AS VARCHAR)
SET @.ENDI = @.ENDI + 1
END

SELECT * FROM @.TBLNAME

The name of the table is in each line of the table @.TBLNAME !

Any help ?

Can you please rephrase your question and perhaps give a repro? I'm not sure what it is that you are asking.

Thanks|||For each row of the table @.TBLNAME I need to create a table with that Name and i don't know how to do that|||

Like this -
usage:
exec InsertIntoTableName 'temptablename'

create proc InsertIntoTableName(@.TBLNAME sysname)
as
DECLARE @.MTH INT
DECLARE @.MTH2 INT
DECLARE @.DAY INT
DECLARE @.BGN DATETIME
DECLARE @.END DATETIME
DECLARE @.ENDI INT
DECLARE @.DTCTRL DATETIME
DECLARE @.sql VARCHAR(8000)
SET @.SQL = 'create table ##'+@.TBLNAME +'(NOME VARCHAR(112))'
print @.SQL
exec (@.SQL)
--||_@.MTH_ ===>_RANGE_MONTH/DAYS||
SET @.MTH = 11 + 1
_RANGE_MONTH">--||_@.MTH2_===>_RANGE_MONTH__||
SET @.MTH2 = 5
--||_||

SET @.BGN = CONVERT(VARCHAR(8),DATEADD(MM,-@.MTH,GETDATE()),112)
SET @.END = CONVERT(VARCHAR(8),GETDATE(),112)
SET @.DAY = 0

WHILE @.BGN <> @.END
BEGIN
WHILE CONVERT(VARCHAR(8),DATEADD(DD,-@.DAY,GETDATE()),112) = @.END
BEGIN
SET @.SQL = 'INSERT INTO ##'+ @.TBLNAME + ' SELECT ''TB_CHEQUE_' + CONVERT(VARCHAR,DATEADD(DD,-@.DAY,GETDATE()),112) +''''
EXEC (@.SQL)
SET @.DAY = @.DAY + 1
END
SET @.DTCTRL = CONVERT(VARCHAR,DATEADD(DD,-@.DAY,GETDATE()),112)
SET @.END = DATEADD(DD,-1,@.END)
END

SET @.ENDI = 0
WHILE @.MTH2 <> @.ENDI
BEGIN
SET @.SQL = ' INSERT INTO ##'+ @.TBLNAME + ' SELECT ''TB_CHEQUE_' + LEFT(CONVERT(VARCHAR,DATEADD(MM,-@.ENDI,@.DTCTRL),112),6) +''''
EXEC (@.sql)
SET @.ENDI = @.ENDI + 1
END
SET @.DTCTRL = DATEADD(MM,-@.ENDI,@.DTCTRL)
SET @.ENDI = 0

WHILE @.ENDI <> 6
BEGIN
SET @.SQL='INSERT INTO ##'+@.TBLNAME + ' SELECT ''TB_CHEQUE_' + CAST(DATEPART(YY,DATEADD(YY,-@.ENDI, GETDATE())) AS VARCHAR)+''''
EXEC (@.sql)
SET @.ENDI = @.ENDI + 1
END

exec ('SELECT * FROM ##'+@.TBLNAME)

sql

Creating tables from ASP.

Hi list, i wanto to use an ASP page to create tables from the devolopers
intranet.
Inside Code, i have the following conn string:
set objRS = CreateObject("ADODB.Recordset")
objcon.connectionstring = "Provider=SQLOLEDB.1;User
ID=sa;Password=XXXXXX;Persist Security Info=True;Initial Catalog=master;Data
Source=" & server
I have one string called sqltxt that uses the Create table statement. this
is the execution code: "3704,Operation is not allowed when the object is
closed"
Whats wrong'
Thanks.
objRS.open sqltxt, objcon
response.write err.number & "," & err.description & "<p>"
response.write sqltxt
ObjRS.CLOSE
When i try to execute this, i recieve the message :As a general suggestion, consider using a ADO command object instead of a
recordset object.
Anith|||Can u tell me how?
"Anith Sen" wrote:

> As a general suggestion, consider using a ADO command object instead of a
> recordset object.
> --
> Anith
>
>|||Assuming you really wanted to do this, you should use the Execute method off
the
Connection object like so:
(ah VBScript...Forgot how joyous it was <insert sarcasm> ;-> )
Dim oConn
Dim sSQL
Set oConn = CreateObject("ADODB.Connection")
oConn.ConnectionString = ...
oConn.Open
sSQL = "Create Table...
oConn.Execute sSQL
Set oConn = Nothing
HTH
Thomas
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:1CBE6886-30B6-4FD2-995E-DFFEC08D7123@.microsoft.com...
> Hi list, i wanto to use an ASP page to create tables from the devolopers
> intranet.
> Inside Code, i have the following conn string:
> set objRS = CreateObject("ADODB.Recordset")
> objcon.connectionstring = "Provider=SQLOLEDB.1;User
> ID=sa;Password=XXXXXX;Persist Security Info=True;Initial Catalog=master;Da
ta
> Source=" & server
> I have one string called sqltxt that uses the Create table statement. this
> is the execution code: "3704,Operation is not allowed when the object is
> closed"
> Whats wrong'
> Thanks.
> objRS.open sqltxt, objcon
> response.write err.number & "," & err.description & "<p>"
> response.write sqltxt
> ObjRS.CLOSE
> When i try to execute this, i recieve the message :|||Thanks for help Thomas, it works fine!!!
Now, i have a doubt.
When the transaction occurs, i use the err.number and err.description to see
any errors, but i coul not view anything. what do i need to see the "The
command(s) completed successfully."
Thanks again
"Thomas Coleman" wrote:

> Assuming you really wanted to do this, you should use the Execute method o
ff the
> Connection object like so:
> (ah VBScript...Forgot how joyous it was <insert sarcasm> ;-> )
> Dim oConn
> Dim sSQL
> Set oConn = CreateObject("ADODB.Connection")
> oConn.ConnectionString = ...
> oConn.Open
> sSQL = "Create Table...
> oConn.Execute sSQL
> Set oConn = Nothing
>
> HTH
>
> Thomas
>
>
> "Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
> news:1CBE6886-30B6-4FD2-995E-DFFEC08D7123@.microsoft.com...
>
>|||> When the transaction occurs, i use the err.number and err.description to seed">
> any errors, but i coul not view anything. what do i need to see the "The
> command(s) completed successfully."
From ASP? Of course not. From ADO code, you know that a command executed
successfully if an error was not thrown. If we are talking about VBScript,
you'll have to check for Err.Number right after you execute the statement li
ke
so:
sSQL = "Create Table...
oConn.Execute sSQL
If Err.Number <> 0 Then
..call error handler or do whatever in response to an error...
End If
Set oConn = Nothing
Since we're talking about the creation of the table, if the create table cal
l to
ADO fired successfully, then you should be ok to query against it or put dat
a in
it.
Thomas
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:A2338E8C-02A9-48DB-936C-325B78F9E811@.microsoft.com...
> Thanks for help Thomas, it works fine!!!
> Now, i have a doubt.
> When the transaction occurs, i use the err.number and err.description to s
ee
> any errors, but i coul not view anything. what do i need to see the "The
> command(s) completed successfully."
> Thanks again
> "Thomas Coleman" wrote:
>
<snip>|||Tinchos,

> Hi list, i wanto to use an ASP page to create tables from the devolopers
> intranet.
> ...
> Whats wrong'
Although Thomas already answered your question, you should really rethink
your approach. Very little good can come from allowing tables to be created
by the application ... why are you doing this in the first place?
-- Alex Papadimoulis|||I want to offer a service to developers (only datareader and datawriters) th
e
possibility to create custom tables or execute some scripts. I think its the
best solution to have less workload for me.
"Alex Papadimoulis" wrote:

> Tinchos,
>
> Although Thomas already answered your question, you should really rethink
> your approach. Very little good can come from allowing tables to be create
d
> by the application ... why are you doing this in the first place?
> -- Alex Papadimoulis|||So, how can i parse errors like "Server: Msg 2714, Level 16, State 6, Line 1
There is already an object named 'table' in the database" '
Objcon.execute sqltxt
if err.number = 0 then
Response.write "Ok...
else
response.write err.number & "," & err.description & "<p>"
end if
if i try to create the table twice i dont recieve any error messages...
Thanks again!!!!|||Firstly, the system will throw an error if you execute a Create Table statem
ent
and the table exists. You need to be mindful of the *owner* of the table.
Specifically, make sure you prefix the table name with the owner like so:
Create Table dbo.TableName
(
..
)
Otherwise, the table will be created under the user's database name. So you
might get Joe.TableName, Fred.TableName and even dbo.TableName.
Secondly, you need to look at the Errors collection on the Connection object
and
trap the specific error number associated with the a duplicate table name.
Thus..
oConn.Execute sSQL
Select Case Err.Number
Case 0
'do nothing
Case 12344 <-- place the actual error number here
..tell user there was a duplicate
Case Else
..display general error
End Select
Thomas
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:AB4A1EB6-1374-43DD-9722-48BEA85AF2D5@.microsoft.com...
> So, how can i parse errors like "Server: Msg 2714, Level 16, State 6, Line
1
> There is already an object named 'table' in the database" '
> Objcon.execute sqltxt
> if err.number = 0 then
> Response.write "Ok...
> else
> response.write err.number & "," & err.description & "<p>"
> end if
> if i try to create the table twice i dont recieve any error messages...
> Thanks again!!!!

creating SSIS packages in c#

How can I create a SSIS package using C#? Can anybody have source code for the same.

thanx in advance,

Vinod

You can use the object model of SSIS to do the same. Please refer to the books online for the SSIS Object Model.

Thanks,

S Suresh

Creating SQL triggers thru managed code

Hi,
I am using SQL Server 2005 and .NET Framework 2.0.
I need to create a trigger using managed code assembly. I did following
steps:
1. Wrote a .NET class that implements the functionality of the trigger.
2. Compiled the class to produce .NET assembly
3. Registered that assembly using CREATE ASSEMBLY statement
4. Created trigger definition using CREATE TRIGGER statement as
following
<snip>
CREATE TRIGGER Email
ON dbo.Users
FOR INSERT
AS EXTERNAL NAMES UsersTrigger.CLRTriggers.EmailAudit
</snip>
where UsersTrigger, CLRTriggers and EmailAudit are the names of
assembly,
class and method respectively.
But when I execute this statement, I receive error as:- "Could not find
Type
'CLRTriggers' in assembly 'UsersTrigger'".
Anybody knows where I might be going wrong?
Thanks,
ManaGot it. Write Create Trigger statement as follows:
CREATE TRIGGER Email
ON dbo.Users
FOR INSERT
AS EXTERNAL NAMES UsersTrigger.[UsersTrigger.CLRTriggers].EmailAudit
Dont know why u need to mention assembly name again with the class name
!
Mana :)

Creating SQL Script with code

Hello there
I have huge databse that i need to generate it to script sometimes
The Sql Generate SQL Script tool is not smart: it build the tables, views,
store procedures and function on the end. But if the view or store procedure
is based on one of the functions the script won't work
Therefore i need to develop my own tool for scripting databse.
Is the a way to get script that create each object in the system, including
all its items?
..
roy@.atidsm.co.il
: 03-5611606
' 050-7709399> The Sql Generate SQL Script tool is not smart: it build the tables, views,
> store procedures and function on the end. But if the view or store
procedure
> is based on one of the functions the script won't work
If you are using EM, you can find on the Formatting tab an otion to include
all dependet objects.

> Therefore i need to develop my own tool for scripting databse.
> Is the a way to get script that create each object in the system,
including
> all its items?
As EM and QA, you can use SQL DMO to script objects. heck
http://www.karaszi.com/sqlserver/in...ate_script.asp.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Hi,
I would suggest you to use sp_helptext or retrieve info from syscomments
table.
Ed
"Roy Goldhammer" wrote:

> Hello there
> I have huge databse that i need to generate it to script sometimes
> The Sql Generate SQL Script tool is not smart: it build the tables, views,
> store procedures and function on the end. But if the view or store procedu
re
> is based on one of the functions the script won't work
> Therefore i need to develop my own tool for scripting databse.
> Is the a way to get script that create each object in the system, includin
g
> all its items?
> --
> ??òé a?ì????
> ù.?. òúé? ?e??ú ú??e?
> roy@.atidsm.co.il
> èì: 03-5611606
> ?ì_' 050-7709399
>
>|||Perhaps you messe dsomething up, there is a way to script all object
depending the one you want to have via UI. SO the follwing code, will
procuding the script for creating the Function and the view if you only
select the view to create and check the checkbox saying to script also
depending objects.
Try it out,
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
CREATE FUNCTION [dbo].FCT_YadaYada()
RETURNS INT AS
BEGIN
return 1
END
Create View VI_YadaYada
AS
Select dbo.FCT_YadaYada() as YadaYada
"Roy Goldhammer" <roygoldh@.hotmail.com> schrieb im Newsbeitrag
news:%23%23pvEFYQFHA.3076@.tk2msftngp13.phx.gbl...
> Hello there
> I have huge databse that i need to generate it to script sometimes
> The Sql Generate SQL Script tool is not smart: it build the tables, views,
> store procedures and function on the end. But if the view or store
> procedure
> is based on one of the functions the script won't work
> Therefore i need to develop my own tool for scripting databse.
> Is the a way to get script that create each object in the system,
> including
> all its items?
> --
>
> ..
> roy@.atidsm.co.il
> : 03-5611606
> ' 050-7709399
>

Creating SQL Classes ?

I am trying to create a class that will interact with a SQL Server database
I have made up but having a few issues with it.
The basic code is:
Class DatabaseMethods
{
Public:
DatabaseOpen();
DatabaseCreate();
Private:
SqlConnection conn;
};
When compiling (VS C++ 2005) , it won't let me declare 'SqlConnection conn;'
Is there a way around this ?
Cheers
Pete
Posted via NewsDemon.com - Premium Uncensored Newsgroup Service
-->>>>>>http://www.NewsDemon.com<<<<<<--
Unlimited Access, Anonymous Accounts, Uncensored Broadband AccessShould you fully denote the namespace where SQLConnection resides -or add a
'Using System.Data.SQLClient' to the class? How else would this class know
where/how to locate the SQLConnection object?
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"MyMail" <peter.moscatt@.gmail.com> wrote in message
news:44960c8f$0$30230$b9f67a60@.news.newsdemon.com...
>I am trying to create a class that will interact with a SQL Server database
>I have made up but having a few issues with it.
>
> The basic code is:
>
> Class DatabaseMethods
> {
> Public:
> DatabaseOpen();
> DatabaseCreate();
> Private:
> SqlConnection conn;
> };
>
> When compiling (VS C++ 2005) , it won't let me declare 'SqlConnection
> conn;'
>
> Is there a way around this ?
>
> Cheers
> Pete
>
>
> --
> Posted via NewsDemon.com - Premium Uncensored Newsgroup Service
> -->>>>>>http://www.NewsDemon.com<<<<<<--
> Unlimited Access, Anonymous Accounts, Uncensored Broadband Access|||G'Day Arnie,
Thanks for the guide.
I did what you asked (see code below)
// ......... MyHeader.h .........
using namespace System;
using namespace System::Data;
using namespace System::Data::SqlClient;
class DaabaseMethods
{
using System::Data::SqlClient;
public:
void OpenDatabase();
void CreateDatabase();
};
I would have throught because I had already declared
'System::Data::SqlClient' at the top of the header the class should
have seen it.
But anyway, I compiled it and got the following:
error C2886: 'System::Data::SqlClient' : symbol cannot be used in a
member using-declaration
So, what ya reckon '
Pete
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23Vq0ky2kGHA.1324@.TK2MSFTNGP04.phx.gbl...
> Should you fully denote the namespace where SQLConnection
> resides -or add a 'Using System.Data.SQLClient' to the class? How
> else would this class know where/how to locate the SQLConnection
> object?
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "MyMail" <peter.moscatt@.gmail.com> wrote in message
> news:44960c8f$0$30230$b9f67a60@.news.newsdemon.com...
>
Posted via NewsDemon.com - Premium Uncensored Newsgroup Service
-->>>>>>http://www.NewsDemon.com<<<<<<--
Unlimited Access, Anonymous Accounts, Uncensored Broadband Access|||The USING statement has to be outside (and before) the class definition
code -you are correct that the header is where it belongs.
I'm not a C++ person, you need to direct this question to a group that deals
with C++.
Regards,
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"MyMail" <peter.moscatt@.gmail.com> wrote in message
news:44971fa3$0$4190$b9f67a60@.news.newsdemon.com...
> G'Day Arnie,
> Thanks for the guide.
> I did what you asked (see code below)
> // ......... MyHeader.h .........
> using namespace System;
> using namespace System::Data;
> using namespace System::Data::SqlClient;
> class DaabaseMethods
> {
> using System::Data::SqlClient;
> public:
> void OpenDatabase();
> void CreateDatabase();
> };
> I would have throught because I had already declared
> 'System::Data::SqlClient' at the top of the header the class should have
> seen it.
> But anyway, I compiled it and got the following:
> error C2886: 'System::Data::SqlClient' : symbol cannot be used in a member
> using-declaration
>
> So, what ya reckon '
> Pete
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:%23Vq0ky2kGHA.1324@.TK2MSFTNGP04.phx.gbl...
>
> --
> Posted via NewsDemon.com - Premium Uncensored Newsgroup Service
> -->>>>>>http://www.NewsDemon.com<<<<<<--
> Unlimited Access, Anonymous Accounts, Uncensored Broadband Access|||Xref: TK2MSFTNGP01.phx.gbl microsoft.public.sqlserver.programming:610013
No worries Arnie... thanks for the help anyway.
Pete
On Mon, 19 Jun 2006 16:37:01 -0700, "Arnie Rowland" <arnie@.1568.com>
wrote:

>The USING statement has to be outside (and before) the class definition
>code -you are correct that the header is where it belongs.
>I'm not a C++ person, you need to direct this question to a group that deal
s
>with C++.
>Regards,
Posted via NewsDemon.com - Premium Uncensored Newsgroup Service
-->>>>>>http://www.NewsDemon.com<<<<<<--
Unlimited Access, Anonymous Accounts, Uncensored Broadband Access

Creating reports in MS SQL Server Report Server

Hi...
Need some help with creating reports in MS SQL Server Report Server and column name "Profit / Loss (%)".
Got an error when I try to run the code below.
The purpose is to avoid a division by zero... or do anyone know a better way solve this?

=IIF(SUM(Fields!RECOGNITION_REVENUE_RECOGNIZED.Value)=0 AND SUM(Fields!PROFIT_AND_LOSS_AMOUNT.Value)=0, 0, IIF(SUM(Fields!RECOGNITION_REVENUE_RECOGNIZED.Value)=0 AND SUM(Fields!PROFIT_AND_LOSS_AMOUNT.Value)<>0, -1, SUM(Fields!PROFIT_AND_LOSS_AMOUNT.Value)))
/
IIF(SUM(Fields!RECOGNITION_REVENUE_RECOGNIZED.Value)=0 AND SUM(Fields!PROFIT_AND_LOSS_AMOUNT.Value)=0, 1, IIF(SUM(Fields!RECOGNITION_REVENUE_RECOGNIZED.Value)=0 AND SUM(Fields!PROFIT_AND_LOSS_AMOUNT.Value)<>0, 1, SUM(Fields!RECOGNITION_REVENUE_RECOGNIZED.Value)))
Error message:
The value expression for the textbox ‘textbox86’ refers to the field ‘PROFIT_AND_LOSS_AMOUNT’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

//chwa

This error message is referring to the scope of the field ‘PROFIT_AND_LOSS_AMOUNT’. It cannot find it in the dataset. Try doing

SUM(Fields!PROFIT_AND_LOSS_AMOUNT.Value, "dataset name here")

for all those things above.

creating replication with extra column

Hi ,
I have a few tables that i wanted to replicate from ServerA to ServerB ,
however, these tables in ServerB needs a country code column as it's sort of
a Data Center.
How i can add the column during replication setup ?
kindly advise
tks & rdgs
Use the ALTER TABLE statement to add the column to the subscription table.
Note the column must allow NULLs or have a DEFAULT constraint.
HTH
Jerry
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:3329F606-26D6-4D88-8E45-D623C8787E49@.microsoft.com...
> Hi ,
> I have a few tables that i wanted to replicate from ServerA to ServerB ,
> however, these tables in ServerB needs a country code column as it's sort
> of
> a Data Center.
> How i can add the column during replication setup ?
> kindly advise
> tks & rdgs

creating replication with extra column

Hi ,
I have a few tables that i wanted to replicate from ServerA to ServerB ,
however, these tables in ServerB needs a country code column as it's sort of
a Data Center.
How i can add the column during replication setup ?
kindly advise
tks & rdgsUse the ALTER TABLE statement to add the column to the subscription table.
Note the column must allow NULLs or have a DEFAULT constraint.
HTH
Jerry
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:3329F606-26D6-4D88-8E45-D623C8787E49@.microsoft.com...
> Hi ,
> I have a few tables that i wanted to replicate from ServerA to ServerB ,
> however, these tables in ServerB needs a country code column as it's sort
> of
> a Data Center.
> How i can add the column during replication setup ?
> kindly advise
> tks & rdgs

Creating New DB using SMO

Hi,
I am trying to create a new database using SMO, I am getting Set_Parent
Failure Error when try to create a new database.
This is the code I am using to create the database.

Database database= new Database(DbServer, "MyNewDB");
database.Create();
I know that if you just use the first line it will try to attach the database, and u will get set_parent failure error, but I am using .Create() also.... still I am getting the error.... can anyone help me in this ?
Thanks
Rujith

If above is the code you are using, I would think you would get a compile error. The Database Constructor overload that takes 2 parameters is the following:

Database(Server server, string name);

where you must give it a server object where the new database will reside.

Here is some sample code that works:

Server server = new Server(".");
Database db = new Database(server,"MyDatabaseName");
db.Create();

I have a few SMO tutorials that I have been slowly writing that may be of help:

SQL Server Management Objects (SMO) Tutorials

Regards,

Dave

|||

Dave,

Sorry for the wrong code, I removed the first line(creating server), while putting in the forum. Thats the reason for the syntax error. In my actual code, I am creating server first, then calling the database constructor.

Did you ever get the Set_parent failure error while working with SMO?

-Rujith

|||

You can create that error a couple of ways:

Database db = new Database(null,"MyDatabaseName");
db.Create();

or

Database db = new Database();
db.Parent = null;

Either way, if you pass in null for the server (parent) you will get the error:

SetParent failed for Database 'MyDatabaseName'.

Change your code to see if server is null before passing it into the constructor.

Regards,

Dave

Creating new connection manager types

Hi,
Wanted to know how one can write new connection manager types, and, additionally, if there exist any code samples that show how this can be done. I searched through BOL but could not locate any.
I basically work with C++ and was curiuos if code samples exist in it? I have worked with a couple of ETL tools (or 'platforms' should I say) before, but never with SSIS.
Would really appreciate it if someone could help me out here.We're working on providing some samples for custom connection managers. Writing them in native code isn't supported this release, but in managed it is. The pattern is the same as for custom tasks. Derive from the base connection manager type in the Microsoft.SQLServer.ManagedRuntime assembly and override AcquireConnection.
This will get you the super simple connection manager.|||is there any update on this one? a sample would be rather nice?

Creating new connection manager types

Hi,
Wanted to know how one can write new connection manager types, and, additionally, if there exist any code samples that show how this can be done. I searched through BOL but could not locate any.
I basically work with C++ and was curiuos if code samples exist in it? I have worked with a couple of ETL tools (or 'platforms' should I say) before, but never with SSIS.
Would really appreciate it if someone could help me out here.We're working on providing some samples for custom connection managers. Writing them in native code isn't supported this release, but in managed it is. The pattern is the same as for custom tasks. Derive from the base connection manager type in the Microsoft.SQLServer.ManagedRuntime assembly and override AcquireConnection.
This will get you the super simple connection manager.|||is there any update on this one? a sample would be rather nice?

Creating local cubes from relational sources using XMLA

Hi,

I have looked at Chris Webb′s example in creating local cubes. I′m trying to follow his instructions but I think that the syntax or the code is incorrect somehow.

Code Snippet

CREATE GLOBAL CUBE statement. FILENAME|C:\myCube.cub|DDL|

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<Parallel>
<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
</Parallel>

Hello Inmon,

pleae visit the blog entry again and read again:

http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!883.entry

It is not possible to create a local cube from relational data with CREATE GLOBAL CUBE Syntax.

I got the sample running.

HTH

J?rg

|||

Hi J?rg,

Then I would be happy if you told me how you solved it.

This is the example I followed by Chris:

· Open MDX Sample app - yes, this is another one of those areas where SQLMS won't do the job. Connect to the Adventure Works database

· Paste a CREATE GLOBAL CUBE statement into a query pane. Don't run it yet though...

· Start a new Profiler trace on your target database

· Run the CREATE GLOBAL CUBE statement, then stop the trace

· Inside your trace, you'll see the CREATE GLOBAL CUBE statement appear twice followed by a statement which begins with something like this: "CREATE GLOBAL CUBE statement. FILENAME|c:\MyfileName.cub|DDL|" and is followed by an XMLA batch statement

· Copy the XMLA batch statement out to MDX Sample App (if you find you can't do this, you might need to save the trace as an XML file and open it in Notepad or something) and

· Close the connection to your server cube in MDX Sample app, then reconnect to the path you want to create your new cub file at, eg "c:\cubtest.cub" (instead of supplying the name of your server in the connection dialog box).

· Run the Batch statement, and your local cube is created!

|||

Hi Inmon,

this is the sample for relational datasources

In SSMS script the database you've just created to a new query editor window. Still in SSMS, right-click on the database again and select Process, select 'Process Full' then instead of clicking OK click the Script button and choose to script to a new query editor window. You now have the two XMLA commands you need to create and process your local cube, you just need to combine them into one. Select all of the Create statement generated when you scripted the database and paste it into the Batch statement generated from the Process dialog, immediately after the first line (containing the 'Batch' command) and before the XMLA that actually does the processing. Copy the new statement, open up MDX Sample app, connect to the path of the new local cube file, paste the statement into MDX Sample app and then run it. And voila, your local cube will be built direct from the relational data source.|||

Hi Joschko,

I have read that example too. Combine the two scripts into one is no problem but I can′t connect to the path of my local cube. When I connect to my local cube I connect to Analysis Services and writing c:\myCube.cub in the server name field. The .cub file creates but following message appear:

Code Snippet

TITLE: Microsoft SQL Server Management Studio

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

ADDITIONAL INFORMATION:

A connection cannot be made. Ensure that the server is running. (Microsoft.AnalysisServices.Xmla)

The 'c:\myCube.cub' local cube file cannot be opened. (Microsoft.AnalysisServices.Xmla)

The operation completed successfully (Microsoft.AnalysisServices.Xmla)

When I then run the script I recieve the following message:

Code Snippet

TITLE: Microsoft SQL Server Management Studio

Either the user, DOMAIN\userid, does not have access to the c:\myCube.cub database, or the database does not exist. (Microsoft SQL Server 2005 Analysis Services)

|||

Hi,

I have now succeeded with connect the script into one. Wpuld you like to confirm this?

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Parallel>
<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
</Parallel>
</Batch>

When I connect to my local cube through new query and select AS XMLA query and the run it I receive the following message:

Code Snippet

Executing the query ...

A connection cannot be made. Ensure that the server is running.

Execution complete

Regards,

Inmon

|||

Inmon,

I've just got the message you sent me via my blog, but I can't reply because you're communication settings won't allow me. Can you contact me directly please? You can find my contact details at www.crossjoin.co.uk

Chris

|||

I Chris,

You ought to have the email right now. But you can write down the detail instructions about local cubes here at msdn if you want to. Thanks again for you help.

Creating local cubes from relational sources using XMLA

Hi,

I have looked at Chris Webb′s example in creating local cubes. I′m trying to follow his instructions but I think that the syntax or the code is incorrect somehow.

Code Snippet

CREATE GLOBAL CUBE statement. FILENAME|C:\myCube.cub|DDL|

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<Parallel>
<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
</Parallel>

Hello Inmon,

pleae visit the blog entry again and read again:

http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!883.entry

It is not possible to create a local cube from relational data with CREATE GLOBAL CUBE Syntax.

I got the sample running.

HTH

J?rg

|||

Hi J?rg,

Then I would be happy if you told me how you solved it.

This is the example I followed by Chris:

· Open MDX Sample app - yes, this is another one of those areas where SQLMS won't do the job. Connect to the Adventure Works database

· Paste a CREATE GLOBAL CUBE statement into a query pane. Don't run it yet though...

· Start a new Profiler trace on your target database

· Run the CREATE GLOBAL CUBE statement, then stop the trace

· Inside your trace, you'll see the CREATE GLOBAL CUBE statement appear twice followed by a statement which begins with something like this: "CREATE GLOBAL CUBE statement. FILENAME|c:\MyfileName.cub|DDL|" and is followed by an XMLA batch statement

· Copy the XMLA batch statement out to MDX Sample App (if you find you can't do this, you might need to save the trace as an XML file and open it in Notepad or something) and

· Close the connection to your server cube in MDX Sample app, then reconnect to the path you want to create your new cub file at, eg "c:\cubtest.cub" (instead of supplying the name of your server in the connection dialog box).

· Run the Batch statement, and your local cube is created!

|||

Hi Inmon,

this is the sample for relational datasources

In SSMS script the database you've just created to a new query editor window. Still in SSMS, right-click on the database again and select Process, select 'Process Full' then instead of clicking OK click the Script button and choose to script to a new query editor window. You now have the two XMLA commands you need to create and process your local cube, you just need to combine them into one. Select all of the Create statement generated when you scripted the database and paste it into the Batch statement generated from the Process dialog, immediately after the first line (containing the 'Batch' command) and before the XMLA that actually does the processing. Copy the new statement, open up MDX Sample app, connect to the path of the new local cube file, paste the statement into MDX Sample app and then run it. And voila, your local cube will be built direct from the relational data source.|||

Hi Joschko,

I have read that example too. Combine the two scripts into one is no problem but I can′t connect to the path of my local cube. When I connect to my local cube I connect to Analysis Services and writing c:\myCube.cub in the server name field. The .cub file creates but following message appear:

Code Snippet

TITLE: Microsoft SQL Server Management Studio

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

ADDITIONAL INFORMATION:

A connection cannot be made. Ensure that the server is running. (Microsoft.AnalysisServices.Xmla)

The 'c:\myCube.cub' local cube file cannot be opened. (Microsoft.AnalysisServices.Xmla)

The operation completed successfully (Microsoft.AnalysisServices.Xmla)

When I then run the script I recieve the following message:

Code Snippet

TITLE: Microsoft SQL Server Management Studio

Either the user, DOMAIN\userid, does not have access to the c:\myCube.cub database, or the database does not exist. (Microsoft SQL Server 2005 Analysis Services)

|||

Hi,

I have now succeeded with connect the script into one. Wpuld you like to confirm this?

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Parallel>
<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
</Parallel>
</Batch>

When I connect to my local cube through new query and select AS XMLA query and the run it I receive the following message:

Code Snippet

Executing the query ...

A connection cannot be made. Ensure that the server is running.

Execution complete

Regards,

Inmon

|||

Inmon,

I've just got the message you sent me via my blog, but I can't reply because you're communication settings won't allow me. Can you contact me directly please? You can find my contact details at www.crossjoin.co.uk

Chris

|||

I Chris,

You ought to have the email right now. But you can write down the detail instructions about local cubes here at msdn if you want to. Thanks again for you help.

Creating local cubes from relational sources using XMLA

Hi,

I have looked at Chris Webb′s example in creating local cubes. I′m trying to follow his instructions but I think that the syntax or the code is incorrect somehow.

Code Snippet

CREATE GLOBAL CUBE statement. FILENAME|C:\myCube.cub|DDL|

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<Parallel>
<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
</Parallel>

Hello Inmon,

pleae visit the blog entry again and read again:

http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!883.entry

It is not possible to create a local cube from relational data with CREATE GLOBAL CUBE Syntax.

I got the sample running.

HTH

J?rg

|||

Hi J?rg,

Then I would be happy if you told me how you solved it.

This is the example I followed by Chris:

· Open MDX Sample app - yes, this is another one of those areas where SQLMS won't do the job. Connect to the Adventure Works database

· Paste a CREATE GLOBAL CUBE statement into a query pane. Don't run it yet though...

· Start a new Profiler trace on your target database

· Run the CREATE GLOBAL CUBE statement, then stop the trace

· Inside your trace, you'll see the CREATE GLOBAL CUBE statement appear twice followed by a statement which begins with something like this: "CREATE GLOBAL CUBE statement. FILENAME|c:\MyfileName.cub|DDL|" and is followed by an XMLA batch statement

· Copy the XMLA batch statement out to MDX Sample App (if you find you can't do this, you might need to save the trace as an XML file and open it in Notepad or something) and

· Close the connection to your server cube in MDX Sample app, then reconnect to the path you want to create your new cub file at, eg "c:\cubtest.cub" (instead of supplying the name of your server in the connection dialog box).

· Run the Batch statement, and your local cube is created!

|||

Hi Inmon,

this is the sample for relational datasources

In SSMS script the database you've just created to a new query editor window. Still in SSMS, right-click on the database again and select Process, select 'Process Full' then instead of clicking OK click the Script button and choose to script to a new query editor window. You now have the two XMLA commands you need to create and process your local cube, you just need to combine them into one. Select all of the Create statement generated when you scripted the database and paste it into the Batch statement generated from the Process dialog, immediately after the first line (containing the 'Batch' command) and before the XMLA that actually does the processing. Copy the new statement, open up MDX Sample app, connect to the path of the new local cube file, paste the statement into MDX Sample app and then run it. And voila, your local cube will be built direct from the relational data source.|||

Hi Joschko,

I have read that example too. Combine the two scripts into one is no problem but I can′t connect to the path of my local cube. When I connect to my local cube I connect to Analysis Services and writing c:\myCube.cub in the server name field. The .cub file creates but following message appear:

Code Snippet

TITLE: Microsoft SQL Server Management Studio

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

ADDITIONAL INFORMATION:

A connection cannot be made. Ensure that the server is running. (Microsoft.AnalysisServices.Xmla)

The 'c:\myCube.cub' local cube file cannot be opened. (Microsoft.AnalysisServices.Xmla)

The operation completed successfully (Microsoft.AnalysisServices.Xmla)

When I then run the script I recieve the following message:

Code Snippet

TITLE: Microsoft SQL Server Management Studio

Either the user, DOMAIN\userid, does not have access to the c:\myCube.cub database, or the database does not exist. (Microsoft SQL Server 2005 Analysis Services)

|||

Hi,

I have now succeeded with connect the script into one. Wpuld you like to confirm this?

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Parallel>
<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
</Parallel>
</Batch>

When I connect to my local cube through new query and select AS XMLA query and the run it I receive the following message:

Code Snippet

Executing the query ...

A connection cannot be made. Ensure that the server is running.

Execution complete

Regards,

Inmon

|||

Inmon,

I've just got the message you sent me via my blog, but I can't reply because you're communication settings won't allow me. Can you contact me directly please? You can find my contact details at www.crossjoin.co.uk

Chris

|||

I Chris,

You ought to have the email right now. But you can write down the detail instructions about local cubes here at msdn if you want to. Thanks again for you help.