creating table fields dynamically from another table row

Hello all:

Is it possible to creates fields of the table dynamically?. I have this situation in my project. This is just a small sample. I have row of length 140. I don't wan't to declare all this fields manually using the create table command.

The description of table is as, in this table all the field are of type varchar only, there are like 140 columns.

create dummy emp (
field1 VARCHAR(100), field2 varchar(200), field3 VARCHAR(100)... )

Table: Dummy
================================================== ==
field1 field2 field3........
Empid Empname empaage1 sam 23.........
2 rai 22.........
.
.
.
n raj 45..........
================================================== ==
Now I want to create another table as "EMP" , with proper data type
fields too..

create table emp (
empid int, empname varchar(100), empage int...)

The table should look like as:

Table: EMP
================================================== ==
Empid Empname empaage.........
1 sam 23............
2 rai 22............
.
.
.
n raj 45.............
================================================== ==

I want to do this dynamically....
Some how I need to extract those field from table[dummy]; the first row acts as a column header for the table[Emp] and the subsequent row acts as a record for the table[Emp]

A small rough snippet of the code will be appreciated...

Waiting for replies......
sabyyou can do something like below. but determining the exact filed type in CREATE table is difficult and avoiding that I have taken all fields as varchar(100)

create table #T (SQLTxt varchar(5000))

declare @.SQL varchar(5000)
declare @.FinalSQL varchar(5000)
declare @.ii int

set @.SQL=''
set @.ii=1
while @.ii <= 140
begin
set @.SQL = @.SQL + ' Field' + cast(@.ii as varchar) + ' + '' varchar(100) null,''+'
set @.ii=@.ii+1
end
set @.SQL = left(@.SQL,len(@.SQL)-3)+''''
set @.SQL = 'insert into #T (SQLTxt) select top 1 ' + '''create table Dummy2 (''+' + @.SQL + '+'')''' + ' from Dummy'

exec (@.SQL)
set @.FinalSQL = (select top 1 SQLTxt from #T)
exec (@.FinalSQL)
drop table #T

Creating SQL statements programmatically from listbox (ADVANCED)

I am creating a page that creates a report based on a dynamically created SQL statement which is created by user input.

Everything is good except for the WHERE section, which is created from values in a list box.

For Example:
lstCriteria.items(1).value = "COMPANY = 'foo'"
lstCriteria.items(2).value = "DAY= 2"

I build my SQL statement with these values like so:
SELECT * FROM POO WHERE COMPANY = 'foo' AND DAY = 2

The problem I am having is when there are multiple values of the same type in the list box. Say:
lstCriteria.items(1).value = "COMPANY = 'foo'"
lstCriteria.items(2).value = "DAY= 2"
lstCriteria.items(1).value = "COMPANY = 'moo'"

My employer wants this to be valid, but I am having a tough time coming up with a solution.

I know that my SQL statement needs to now read:
SELECT * FROM POO WHERE COMPANY = 'foo' AND DAY = 2 OR COMPANY = 'poo' AND DAY = 2

I have code set up to read the values of each list box item up to the "=". And I know that I need to compair this value with the others in the list box...but I am not running into any good solutions.

Any HELP?How about OR like values together?

SELECT * FROM POO WHERE (COMPANY = 'foo' OR COMPANY = 'poo') AND DAY = 2

|||Yes, that would work. But I am looking more at how to extract this data from the listbox to a usable format. I am working on setting the first value (the left(N) characters of the listbox item, which is also a column name) in an array, then inserting the secondvalue, checking if it is in the array, if it is, adding it to the array. If it is not, then adding it to a new array. This way I could loop thought the arrays and create my where statement...but It's just a thought on a whiteboard now.

Any Other suggestions?

Creating publication hangs server

What are the actions performed by SQL Server when it creates a new publication or alters a previously created publication?

The reason I ask is because when I perform the above actions on a specific production database the server completely hangs. The tables are not large, maybe 5000 rows. I cannot delete the subscriptions either as this hangs the server too. Using any of the supplied stored procedures also has the same effect.

As this is a production environment I really don't want to do this too often.

Thanks

GavinMy dear friend. I have same problem on a clustered production system. Try this for start. When Creating publication, choose only one or two small tables at a time to create articles. Start snap shot and generate and apply changes to replicated server. Once ok. Start the same thing for other tables. If this works then u are good to go...


Originally posted by gmb72
What are the actions performed by SQL Server when it creates a new publication or alters a previously created publication?

The reason I ask is because when I perform the above actions on a specific production database the server completely hangs. The tables are not large, maybe 5000 rows. I cannot delete the subscriptions either as this hangs the server too. Using any of the supplied stored procedures also has the same effect.

As this is a production environment I really don't want to do this too often.

Thanks

Gavin

Creating offline (local) cubes using XMLA, or any other way..

Hi,

I realise this subject has been discussed a lot but there seems to be no definitive answer.

The company I work for creates many cubes (currently using many SQL statements) for the sales regions at the end of each month and we need to move to a better platform, hence analysis services projects.

Is there any "easy" way to take a cube created by an analysis services project and save it to an offline (local) cube?

Currently I am trying to use VB.NET 2005 to process the XMLA definition from AS2005, which is easy to get at.

The VB code is:

Dim myClient As New Microsoft.AnalysisServices.Xmla.XmlaClient

Dim l_result, l_XmlaCommand As String

l_result = ""

Try

REMDim l_connect As String = "Provider=MSOLAP.3;Data Source=L00086SINEY;Initial Catalog=Beta1;Integrated Security=SSPI "

Dim l_connect As String = "Provider=MSOLAP.3;Data Source=c:\inetpub\wwwroot\xmla\BetaTest.cub;Integrated Security=SSPI;UseExistingFile=False"

myClient.Connect(l_connect)

l_XmlaCommand = "<Statement>begin transaction</Statement>"

myClient.Execute(l_XmlaCommand, "", l_result, False, True)

Dim FS As New IO.StreamReader("C:\XMLA_Command.txt")

l_XmlaCommand = FS.ReadToEnd

FS.Close()

myClient.Execute(l_XmlaCommand, "", l_result, False, True)

l_XmlaCommand = "<Statement>commit transaction</Statement>"

myClient.Execute(l_XmlaCommand, "", l_result, False, True)

Catch ex As Exception

MsgBox("Error in Client connection")

Finally

myClient.Disconnect()

End Try

The XMLA_Command.txt (first few lines) is:

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

<ParentObject>

<DatabaseID>http://localhost/xmla/testcube.cub</DatabaseID>

</ParentObject>

<ObjectDefinition>

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

<ID>Beta1</ID>

<Name>Beta1</Name>

<Annotations>

<Annotation>

and finally the error I get is:

Errors in the metadata manager. Either the database with the ID of 'http://localhost/xmla/testcube.cub' does not exist in the server with the ID of 'L00086SINEY\LocalCube', or the user does not have permissions to access the object.

The cube is created fine when pointed at the Analysis Server but not when a local target is used.

Apologies for the length of post but it explains where I am up to.

Obviously if there is a "create local cube" wizard that I'm missing in AS2005 then I'll be more than happy to abandon the project!

Any help, even if it's book recommendations, would be greatly appreciated.

Thanks,

John.

Follow up:

Sorted the answer myself with a little (OK lot!) of help from a book by Teo Lachev, Applied Microsoft Analysis Services 2005. it uses code so it won't be ideal for everyone but it works and is a lot simpler than trying to work out how XMLA works in AS2005 with local cubes, especially as even xmla.org now seems largely forgotten.

By using the code from the above book you can point the created program to an existing cube created using AS2005 within the BIDS package and output it to a local cube file, very simple.

A big thank you goes out to Teo as days of searching have now finished.

|||John

I'm deeply interested in any solution for creating offline cubes programmatically.

How did you solve your problem?

I managed to create them in general using "CREATE GLOBAL CUBE"-syntax but
I can not ignore processing errors like I can do when using the management console.

Any answer is appreciated

Regards
Klaus Wiesel|||The sample in Teo's book is an application that will build the "CREATE GLOBAL CUBE" command, so it probably will not help you much, there were no options for setting the error configuration. Teo maintains a website at www.prologika.com|||I have put the MDX statment "Create Cube ... " in SQL Agent , and it gives me errors, i want to schedule the creation of the cube , not do it maunally. any help?

Creating offline (local) cubes using XMLA, or any other way..

Hi,

I realise this subject has been discussed a lot but there seems to be no definitive answer.

The company I work for creates many cubes (currently using many SQL statements) for the sales regions at the end of each month and we need to move to a better platform, hence analysis services projects.

Is there any "easy" way to take a cube created by an analysis services project and save it to an offline (local) cube?

Currently I am trying to use VB.NET 2005 to process the XMLA definition from AS2005, which is easy to get at.

The VB code is:

Dim myClient As New Microsoft.AnalysisServices.Xmla.XmlaClient

Dim l_result, l_XmlaCommand As String

l_result = ""

Try

REMDim l_connect As String = "Provider=MSOLAP.3;Data Source=L00086SINEY;Initial Catalog=Beta1;Integrated Security=SSPI "

Dim l_connect As String = "Provider=MSOLAP.3;Data Source=c:\inetpub\wwwroot\xmla\BetaTest.cub;Integrated Security=SSPI;UseExistingFile=False"

myClient.Connect(l_connect)

l_XmlaCommand = "<Statement>begin transaction</Statement>"

myClient.Execute(l_XmlaCommand, "", l_result, False, True)

Dim FS As New IO.StreamReader("C:\XMLA_Command.txt")

l_XmlaCommand = FS.ReadToEnd

FS.Close()

myClient.Execute(l_XmlaCommand, "", l_result, False, True)

l_XmlaCommand = "<Statement>commit transaction</Statement>"

myClient.Execute(l_XmlaCommand, "", l_result, False, True)

Catch ex As Exception

MsgBox("Error in Client connection")

Finally

myClient.Disconnect()

End Try

The XMLA_Command.txt (first few lines) is:

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

<ParentObject>

<DatabaseID>http://localhost/xmla/testcube.cub</DatabaseID>

</ParentObject>

<ObjectDefinition>

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

<ID>Beta1</ID>

<Name>Beta1</Name>

<Annotations>

<Annotation>

and finally the error I get is:

Errors in the metadata manager. Either the database with the ID of 'http://localhost/xmla/testcube.cub' does not exist in the server with the ID of 'L00086SINEY\LocalCube', or the user does not have permissions to access the object.

The cube is created fine when pointed at the Analysis Server but not when a local target is used.

Apologies for the length of post but it explains where I am up to.

Obviously if there is a "create local cube" wizard that I'm missing in AS2005 then I'll be more than happy to abandon the project!

Any help, even if it's book recommendations, would be greatly appreciated.

Thanks,

John.

Follow up:

Sorted the answer myself with a little (OK lot!) of help from a book by Teo Lachev, Applied Microsoft Analysis Services 2005. it uses code so it won't be ideal for everyone but it works and is a lot simpler than trying to work out how XMLA works in AS2005 with local cubes, especially as even xmla.org now seems largely forgotten.

By using the code from the above book you can point the created program to an existing cube created using AS2005 within the BIDS package and output it to a local cube file, very simple.

A big thank you goes out to Teo as days of searching have now finished.

|||John

I'm deeply interested in any solution for creating offline cubes programmatically.

How did you solve your problem?

I managed to create them in general using "CREATE GLOBAL CUBE"-syntax but
I can not ignore processing errors like I can do when using the management console.

Any answer is appreciated

Regards
Klaus Wiesel|||The sample in Teo's book is an application that will build the "CREATE GLOBAL CUBE" command, so it probably will not help you much, there were no options for setting the error configuration. Teo maintains a website at www.prologika.com|||I have put the MDX statment "Create Cube ... " in SQL Agent , and it gives me errors, i want to schedule the creation of the cube , not do it maunally. any help?

Creating offline (local) cubes using XMLA, or any other way..

Hi,

I realise this subject has been discussed a lot but there seems to be no definitive answer.

The company I work for creates many cubes (currently using many SQL statements) for the sales regions at the end of each month and we need to move to a better platform, hence analysis services projects.

Is there any "easy" way to take a cube created by an analysis services project and save it to an offline (local) cube?

Currently I am trying to use VB.NET 2005 to process the XMLA definition from AS2005, which is easy to get at.

The VB code is:

Dim myClientAsNew Microsoft.AnalysisServices.Xmla.XmlaClient

Dim l_result, l_XmlaCommandAsString

l_result =""

Try

REMDim l_connect As String = "Provider=MSOLAP.3;Data Source=L00086SINEY;Initial Catalog=Beta1;Integrated Security=SSPI "

Dim l_connectAsString ="Provider=MSOLAP.3;Data Source=c:\inetpub\wwwroot\xmla\BetaTest.cub;Integrated Security=SSPI;UseExistingFile=False"

myClient.Connect(l_connect)

l_XmlaCommand ="<Statement>begin transaction</Statement>"

myClient.Execute(l_XmlaCommand,"", l_result,False,True)

Dim FSAsNew IO.StreamReader("C:\XMLA_Command.txt")

l_XmlaCommand = FS.ReadToEnd

FS.Close()

myClient.Execute(l_XmlaCommand,"", l_result,False,True)

l_XmlaCommand ="<Statement>commit transaction</Statement>"

myClient.Execute(l_XmlaCommand,"", l_result,False,True)

Catch exAs Exception

MsgBox("Error in Client connection")

Finally

myClient.Disconnect()

EndTry

The XMLA_Command.txt (first few lines) is:

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

<ParentObject>

<DatabaseID>http://localhost/xmla/testcube.cub</DatabaseID>

</ParentObject>

<ObjectDefinition>

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

<ID>Beta1</ID>

<Name>Beta1</Name>

<Annotations>

<Annotation>

and finally the error I get is:

Errors in the metadata manager. Either the database with the ID of 'http://localhost/xmla/testcube.cub' does not exist in the server with the ID of 'L00086SINEY\LocalCube', or the user does not have permissions to access the object.

The cube is created fine when pointed at the Analysis Server but not when a local target is used.

Apologies for the length of post but it explains where I am up to.

Obviously if there is a "create local cube" wizard that I'm missing in AS2005 then I'll be more than happy to abandon the project!

Any help, even if it's book recommendations, would be greatly appreciated.

Thanks,

John.

Follow up:

Sorted the answer myself with a little (OK lot!) of help from a book by Teo Lachev, Applied Microsoft Analysis Services 2005. it uses code so it won't be ideal for everyone but it works and is a lot simpler than trying to work out how XMLA works in AS2005 with local cubes, especially as even xmla.org now seems largely forgotten.

By using the code from the above book you can point the created program to an existing cube created using AS2005 within the BIDS package and output it to a local cube file, very simple.

A big thank you goes out to Teo as days of searching have now finished.

|||John

I'm deeply interested in any solution for creating offline cubes programmatically.

How did you solve your problem?
I managed to create them in general using "CREATE GLOBAL CUBE"-syntax but
I can not ignore processing errors like I can do when using the management console.

Any answer is appreciated

Regards
Klaus Wiesel

|||The sample in Teo's book is an application that will build the "CREATE GLOBAL CUBE" command, so it probably will not help you much, there were no options for setting the error configuration. Teo maintains a website at www.prologika.com|||I have put the MDX statment "Create Cube ... " in SQL Agent , and it gives me errors, i want to schedule the creation of the cube , not do it maunally. any help?

Creating maintenance plans in MSDE

I can create a maintenance plan in Enterprisemanager using the maintenance plan wizard. It automatically creates 4 different jobs. I can generate a script for those jobs and run using a batch file in MSDE. Is there another way to do the same thing. How c
an i create maintenance plans in MSDE
Hi,
You need to just run the same types of scripts against the MSDE. Use OSQL or
a 3rd party tool.
HTH,
Greg Low (MVP)
MSDE Manager SQL Tools
www.whitebearconsulting.com
"hp" <anonymous@.discussions.microsoft.com> wrote in message
news:E4C10AAD-2B43-4BDE-B1B1-C23D326CAE19@.microsoft.com...
> I can create a maintenance plan in Enterprisemanager using the maintenance
plan wizard. It automatically creates 4 different jobs. I can generate a
script for those jobs and run using a batch file in MSDE. Is there another
way to do the same thing. How can i create maintenance plans in MSDE
|||These articles may help you get started:
241397 HOWTO: Back Up a Microsoft Data Engine Database with Transact-SQL
http://support.microsoft.com/?id=241397
328747 INF: Recovery Planning for Microsoft SQL Server 2000 Desktop Engine
http://support.microsoft.com/?id=328747
325003 HOW TO: Manage the SQL Server Desktop Engine (MSDE 2000) by Using the
http://support.microsoft.com/?id=325003
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

Creating indexes (details required)

I need some idea of how SQLServer 2000 creates indexes, particularly
clustered indexes.
I imagine that creating a clustered index is rather similar to defragmenting
a hard disk. Data has to be read from disparate areas, stored "somewhere"
until there is a large enough area and then written to that area in a
contigious manner.
If that premise is correct, where is the temporary storage area? Is it in
the database itself or is it in the TempDB?
The reason I ask is because SQLServer is crashing my machine when it builds
indexes (for details see my posting "SQLServer crashing server!" in
"microsoft.public.sqlserver.server".
Thanks in advance
Griff
When indexes are created the temporary storage for the duplicate ( if
replacing indexes), etc is on the SAME filegroup as the index will be on..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Griff" <Howling@.The.Moon> wrote in message
news:%232qla4KaEHA.2216@.TK2MSFTNGP10.phx.gbl...
> I need some idea of how SQLServer 2000 creates indexes, particularly
> clustered indexes.
> I imagine that creating a clustered index is rather similar to
defragmenting
> a hard disk. Data has to be read from disparate areas, stored "somewhere"
> until there is a large enough area and then written to that area in a
> contigious manner.
> If that premise is correct, where is the temporary storage area? Is it in
> the database itself or is it in the TempDB?
> The reason I ask is because SQLServer is crashing my machine when it
builds
> indexes (for details see my posting "SQLServer crashing server!" in
> "microsoft.public.sqlserver.server".
> Thanks in advance
> Griff
>

Creating indexes (details required)

I need some idea of how SQLServer 2000 creates indexes, particularly
clustered indexes.
I imagine that creating a clustered index is rather similar to defragmenting
a hard disk. Data has to be read from disparate areas, stored "somewhere"
until there is a large enough area and then written to that area in a
contigious manner.
If that premise is correct, where is the temporary storage area? Is it in
the database itself or is it in the TempDB?
The reason I ask is because SQLServer is crashing my machine when it builds
indexes (for details see my posting "SQLServer crashing server!" in
"microsoft.public.sqlserver.server".
Thanks in advance
GriffWhen indexes are created the temporary storage for the duplicate ( if
replacing indexes), etc is on the SAME filegroup as the index will be on..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Griff" <Howling@.The.Moon> wrote in message
news:%232qla4KaEHA.2216@.TK2MSFTNGP10.phx.gbl...
> I need some idea of how SQLServer 2000 creates indexes, particularly
> clustered indexes.
> I imagine that creating a clustered index is rather similar to
defragmenting
> a hard disk. Data has to be read from disparate areas, stored "somewhere"
> until there is a large enough area and then written to that area in a
> contigious manner.
> If that premise is correct, where is the temporary storage area? Is it in
> the database itself or is it in the TempDB?
> The reason I ask is because SQLServer is crashing my machine when it
builds
> indexes (for details see my posting "SQLServer crashing server!" in
> "microsoft.public.sqlserver.server".
> Thanks in advance
> Griff
>

Creating indexes (details required)

I need some idea of how SQLServer 2000 creates indexes, particularly
clustered indexes.
I imagine that creating a clustered index is rather similar to defragmenting
a hard disk. Data has to be read from disparate areas, stored "somewhere"
until there is a large enough area and then written to that area in a
contigious manner.
If that premise is correct, where is the temporary storage area? Is it in
the database itself or is it in the TempDB?
The reason I ask is because SQLServer is crashing my machine when it builds
indexes (for details see my posting "SQLServer crashing server!" in
"microsoft.public.sqlserver.server".
Thanks in advance
GriffWhen indexes are created the temporary storage for the duplicate ( if
replacing indexes), etc is on the SAME filegroup as the index will be on..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Griff" <Howling@.The.Moon> wrote in message
news:%232qla4KaEHA.2216@.TK2MSFTNGP10.phx.gbl...
> I need some idea of how SQLServer 2000 creates indexes, particularly
> clustered indexes.
> I imagine that creating a clustered index is rather similar to
defragmenting
> a hard disk. Data has to be read from disparate areas, stored "somewhere"
> until there is a large enough area and then written to that area in a
> contigious manner.
> If that premise is correct, where is the temporary storage area? Is it in
> the database itself or is it in the TempDB?
> The reason I ask is because SQLServer is crashing my machine when it
builds
> indexes (for details see my posting "SQLServer crashing server!" in
> "microsoft.public.sqlserver.server".
> Thanks in advance
> Griff
>