How can I automate the column titled as ID NUmber in my database, in VS 2005?

I have a table with a primary key titled as 'ID NUmber' which needs to be created automatically, however every time i add a new record the ID is not added and i have to write it manually i.e. 1, 2, 3.., could you please advice me how i can format this; i know you can do this with microsoft Access but with VS 2005 + VB language this option is not available under data type

*i am using VS 2005 and VB language

Go into design view for the table

toward the bottom, you'll see Identity Specification - open that up and set IS IDentity to True

|||Thank you, i have found the 'IS identity' but mine is set to 'No' and is blocked (i can't click on it). should i be using an specific data type; currenly i am using 'Real' is that right? I had to use 'Real' as with other data types the option for 'primary key' was not available.|||

Generally (though it might be possible some other way that I'm not familiar with), the way to do this is with an INT datatype, so it can automatically increment.

Maybe someone else can chime in here, how to do it some other way

|||

To set a column as an Identity column the datatype has to be one of the INT types - tinyint, smallint, int, bigint etc.

|||

i have used the tinyint to set the IS identity as 'yes', however when i run the database and add a new row to the database the row id is for example 9 instead of 2, even though i have deleted all the previous records and doesn't realise that i have now only 1 row and that the next id should be 2. is there any way to correct this?

|||

You need to read up books online about Identity columns. Once a number is assigned to a row, its gone. Even if the row is deleted the number is gone. Any new rows will get the next number. If you were doing this as a test and want to empty the table and reset the identity you can TRUNCATE the table instead of delete. Again, before doing the truncate, read up books online about the command. You can also use DBCC CHEKCIDENT to reset the seed value.

|||Remember, also - tinyint is defined like this:

tinyint

0 to 255

So - I'd recommend at least using INT

One last thing also - you said that your column is titled "ID Number"

My (and others) recommend that you remove the space (IDNumber) and that you never put spaces in your field or table names. Believe me, this will cause you less grief in the future.

how can I add a unique key column to a table and generate its values automatically?

Hi, all,

I have a question about adding a unique key column to an existing table.

what i trying to do is that: I have already created a table, now i wanna add a ID column to this table, and generate the values for ID column from 1 to the existing row number. How can I get this done?

Thanks a lot in advance for any guidance.

Here is an example of how to add an identity column

create table TestID (SomeField varchar(49))
insert into TestID values ('1.1.9')
insert into TestID values ('2.2.2.10')
insert into TestID values ('2.2.2.8')
insert into TestID values ('2.2.2.9')
insert into TestID values ('1.1.7')
insert into TestID values ('1.1.8')
insert into TestID values ('1.2.1')
insert into TestID values ('1.1.1')
insert into TestID values ('1.1.10')
insert into TestID values ('1.1.10')
GO
select * from TestID
GO

alter table TestID add IdField int identity not null
Go
select * from TestID
GO

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Hi, thanks a lot.

Got it done, but the problem is: the new added identity column is not in order? like its values from 1 to 82 is actually not in the order, it is sorted out based on other columns values in the existing table. So how can I modify its values from 1 to 83 in the oder?

Thanks a lot.

|||Order is never guaranteed for a table, use ORDER BY to get the rows back in the order that you want|||Hi, thanks a lot.

How automatically process a Cube periodically

Good morning,

I have create a solution with two cubes, but I want that these cubes process automatically, with a batch or a windows task.

But I don't find how to do this. Do I need to create a script or something like that.

I'm totally lost.

Thanks for your help,

You can use SQL Server Integration Services (search for Processing Objects Using Integration Services in BOL), or you could also Analysis Managment Objects (AMO).
|||

I see this in another article, but I don't found Analysis Services Processing Task.

Is it install with sql server 2005 or not?

Do you have an example that I can see, please or some indications?

Thanks,

|||

Titou wrote:

I see this in another article, but I don't found Analysis Services Processing Task.

Is it install with sql server 2005 or not?

Do you have an example that I can see, please or some indications?

Thanks,

I forgot to note that i work with sql server and analysis services 2005

|||In BI Development Studio, create a new SSIS task. In the Control Flow tab, first add a connection manager for your Analysis Services database (bottom tab, right-click), then drag an Analysis Services Processing Task from the toolbar on the left (the tab may be minimized). In the task, set which objects you want processed.

Save locally, and then go to the file menu and do a Save a copy of... And save it to your SQL Server database that Analysis Services is sitting on.

Go to Management studio and connect to the database (not Analysis Services). Go to the SQL Server Agent and right-click on jobs. Add a new job. In the job type, select SSIS job, select the SSIS package that you just created, and voila! You have a job on the database that you can now set to occur periodically.

It's a little confusing at first, but once it gets going, it's pretty simple. After your first one, you'll probably want to go back and add logging, too...

-Doug
|||

I success in the creation of the SSIS task, but when i go to Sql Server, I can't find my file an I can't had or browse to find it, could you help me?

Thanks

|||

Many options are available. Two immediately come to mind. Both involve scripting. When you have the processing dialog box open (just before you process) you will notice a box at the top that says "Script". It will take whatever you are about to do for processing and create an XMLA script a background window.

Once you have the script then:

1) Create a SQL Agent job and a step within it. There is a new option in the step that allows it to run an Analysis Services Command -- this is the XMLA script that you created above. Simply paste it into the step and then schedule the SQL Agent job as needed.

2) Create an SSIS package and select an Analysis Services Execute DDL task. Insert the XMLA script into it and it will execute it as a step in the SSIS package.

Alternatively, you could create an SSIS package and use the Analsyis Services Processing task and specify what object you wish to process. That works well also.

Finally, coming in WR2 of the Samples MSI in April will be a new utility called ascmd (which is patterened after sqlcmd) that allows you to execute XMLA scripts (and MDX queries) from the command line. If you are interested in getting a pre-release version of it, contact me (dwickert@.microsoft.com) and I will point you to the right folks.

Hope that helps.

_-_-_ Dave

|||

Hi.

Thanks, DWBI_Doug! Worked perfectly!

Regards

Kjetil

|||Thank You DWBI_Doug, You saved my day. i ow you one.

How automatically process a Cube periodically

Good morning,

I have create a solution with two cubes, but I want that these cubes process automatically, with a batch or a windows task.

But I don't find how to do this. Do I need to create a script or something like that.

I'm totally lost.

Thanks for your help,

You can use SQL Server Integration Services (search for Processing

Objects Using Integration Services in BOL), or you could also Analysis

Managment Objects (AMO).|||

I see this in another article, but I don't found Analysis Services Processing Task.

Is it install with sql server 2005 or not?

Do you have an example that I can see, please or some indications?

Thanks,

|||

Titou wrote:

I see this in another article, but I don't found Analysis Services Processing Task.

Is it install with sql server 2005 or not?

Do you have an example that I can see, please or some indications?

Thanks,

I forgot to note that i work with sql server and analysis services 2005

|||In BI Development Studio, create a new SSIS task. In the Control Flow tab, first add a connection manager for your Analysis Services database (bottom tab, right-click), then drag an Analysis Services Processing Task from the toolbar on the left (the tab may be minimized). In the task, set which objects you want processed.

Save locally, and then go to the file menu and do a Save a copy of... And save it to your SQL Server database that Analysis Services is sitting on.

Go to Management studio and connect to the database (not Analysis Services). Go to the SQL Server Agent and right-click on jobs. Add a new job. In the job type, select SSIS job, select the SSIS package that you just created, and voila! You have a job on the database that you can now set to occur periodically.

It's a little confusing at first, but once it gets going, it's pretty simple. After your first one, you'll probably want to go back and add logging, too...

-Doug|||

I success in the creation of the SSIS task, but when i go to Sql Server, I can't find my file an I can't had or browse to find it, could you help me?

Thanks

|||

Many options are available. Two immediately come to mind. Both involve scripting. When you have the processing dialog box open (just before you process) you will notice a box at the top that says "Script". It will take whatever you are about to do for processing and create an XMLA script a background window.

Once you have the script then:

1) Create a SQL Agent job and a step within it. There is a new option in the step that allows it to run an Analysis Services Command -- this is the XMLA script that you created above. Simply paste it into the step and then schedule the SQL Agent job as needed.

2) Create an SSIS package and select an Analysis Services Execute DDL task. Insert the XMLA script into it and it will execute it as a step in the SSIS package.

Alternatively, you could create an SSIS package and use the Analsyis Services Processing task and specify what object you wish to process. That works well also.

Finally, coming in WR2 of the Samples MSI in April will be a new utility called ascmd (which is patterened after sqlcmd) that allows you to execute XMLA scripts (and MDX queries) from the command line. If you are interested in getting a pre-release version of it, contact me (dwickert@.microsoft.com) and I will point you to the right folks.

Hope that helps.

_-_-_ Dave

|||

Hi.

Thanks, DWBI_Doug! Worked perfectly!

Regards

Kjetil

How automatically process a Cube periodically

Good morning,

I have create a solution with two cubes, but I want that these cubes process automatically, with a batch or a windows task.

But I don't find how to do this. Do I need to create a script or something like that.

I'm totally lost.

Thanks for your help,

You can use SQL Server Integration Services (search for Processing Objects Using Integration Services in BOL), or you could also Analysis Managment Objects (AMO).
|||

I see this in another article, but I don't found Analysis Services Processing Task.

Is it install with sql server 2005 or not?

Do you have an example that I can see, please or some indications?

Thanks,

|||

Titou wrote:

I see this in another article, but I don't found Analysis Services Processing Task.

Is it install with sql server 2005 or not?

Do you have an example that I can see, please or some indications?

Thanks,

I forgot to note that i work with sql server and analysis services 2005

|||In BI Development Studio, create a new SSIS task. In the Control Flow tab, first add a connection manager for your Analysis Services database (bottom tab, right-click), then drag an Analysis Services Processing Task from the toolbar on the left (the tab may be minimized). In the task, set which objects you want processed.

Save locally, and then go to the file menu and do a Save a copy of... And save it to your SQL Server database that Analysis Services is sitting on.

Go to Management studio and connect to the database (not Analysis Services). Go to the SQL Server Agent and right-click on jobs. Add a new job. In the job type, select SSIS job, select the SSIS package that you just created, and voila! You have a job on the database that you can now set to occur periodically.

It's a little confusing at first, but once it gets going, it's pretty simple. After your first one, you'll probably want to go back and add logging, too...

-Doug
|||

I success in the creation of the SSIS task, but when i go to Sql Server, I can't find my file an I can't had or browse to find it, could you help me?

Thanks

|||

Many options are available. Two immediately come to mind. Both involve scripting. When you have the processing dialog box open (just before you process) you will notice a box at the top that says "Script". It will take whatever you are about to do for processing and create an XMLA script a background window.

Once you have the script then:

1) Create a SQL Agent job and a step within it. There is a new option in the step that allows it to run an Analysis Services Command -- this is the XMLA script that you created above. Simply paste it into the step and then schedule the SQL Agent job as needed.

2) Create an SSIS package and select an Analysis Services Execute DDL task. Insert the XMLA script into it and it will execute it as a step in the SSIS package.

Alternatively, you could create an SSIS package and use the Analsyis Services Processing task and specify what object you wish to process. That works well also.

Finally, coming in WR2 of the Samples MSI in April will be a new utility called ascmd (which is patterened after sqlcmd) that allows you to execute XMLA scripts (and MDX queries) from the command line. If you are interested in getting a pre-release version of it, contact me (dwickert@.microsoft.com) and I will point you to the right folks.

Hope that helps.

_-_-_ Dave

|||

Hi.

Thanks, DWBI_Doug! Worked perfectly!

Regards

Kjetil

|||Thank You DWBI_Doug, You saved my day. i ow you one.

How are transactions managed for web services

Hi,
How are transactions managed when publishing stored procedures as web
services? Does SQLXML automatically commit the transaction if the stored
procedure succeeded and rollback if it failed?
Thanks.
McGeeky
http://mcgeeky.blogspot.com
Hello McGeeky,

> How are transactions managed when publishing stored procedures as web
> services? Does SQLXML automatically commit the transaction if the
> stored procedure succeeded and rollback if it failed?
For SQL Server 2005 using SOAP endpoints: Nope. Layering a Web Service on
top of a stored proc doesn't change how the stored proc behaves, you still
need to manage the transactions correctly and internally to your own code.
The new TRY-CATCH syntax makes that easier, of course.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||I am using SQL Server 2000 with SQLXML and MS Soap Toolkit. There is not
opportunity to manage the transactions directly so I am presuming that
SQLXML does it automatically.
McGeeky
http://mcgeeky.blogspot.com
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad7411b5a8c7bd5231b417cf@.news.microsoft.co m...
> Hello McGeeky,
>
> For SQL Server 2005 using SOAP endpoints: Nope. Layering a Web Service on
> top of a stored proc doesn't change how the stored proc behaves, you still
> need to manage the transactions correctly and internally to your own code.
> The new TRY-CATCH syntax makes that easier, of course.
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
|||Transaction management is internal to the webservice endpoint. There is no
support for cross-service call transactions. If you want to build such a
system, you will have to build your own multi-level transaction management
scheme.
Best regards
Michael
"McGeeky" <anon@.anon.com> wrote in message
news:edNPGj37FHA.3760@.TK2MSFTNGP14.phx.gbl...
>I am using SQL Server 2000 with SQLXML and MS Soap Toolkit. There is not
>opportunity to manage the transactions directly so I am presuming that
>SQLXML does it automatically.
> --
> McGeeky
> http://mcgeeky.blogspot.com
>
> "Kent Tegels" <ktegels@.develop.com> wrote in message
> news:b87ad7411b5a8c7bd5231b417cf@.news.microsoft.co m...
>
|||Hi Michael. Having the transaction management internal to the webservice is
absolutely ideal and music to my ears. I just wanted to confirm that this
was so before pressing ahead with a large project.
Thanks
McGeeky
http://mcgeeky.blogspot.com
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:OapnwiQAGHA.3864@.TK2MSFTNGP12.phx.gbl...
> Transaction management is internal to the webservice endpoint. There is no
> support for cross-service call transactions. If you want to build such a
> system, you will have to build your own multi-level transaction management
> scheme.
> Best regards
> Michael
> "McGeeky" <anon@.anon.com> wrote in message
> news:edNPGj37FHA.3760@.TK2MSFTNGP14.phx.gbl...
>
|||Hi Michael. Can the transaction isolation level be changed in the stored
procedure? What isolation level does the web service use by default?
Thanks.
McGeeky
http://mcgeeky.blogspot.com
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:OapnwiQAGHA.3864@.TK2MSFTNGP12.phx.gbl...
> Transaction management is internal to the webservice endpoint. There is no
> support for cross-service call transactions. If you want to build such a
> system, you will have to build your own multi-level transaction management
> scheme.
> Best regards
> Michael
> "McGeeky" <anon@.anon.com> wrote in message
> news:edNPGj37FHA.3760@.TK2MSFTNGP14.phx.gbl...
>
|||I am not sure if you can control it via the webservices interface (I am not
the expert here), but I would assume that it uses per default what is set
for the database...
Best regards
Michael
"McGeeky" <anon@.anon.com> wrote in message
news:%23Z%23KzBkAGHA.3456@.TK2MSFTNGP11.phx.gbl...
> Hi Michael. Can the transaction isolation level be changed in the stored
> procedure? What isolation level does the web service use by default?
> Thanks.
> --
> McGeeky
> http://mcgeeky.blogspot.com
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:OapnwiQAGHA.3864@.TK2MSFTNGP12.phx.gbl...
>

How are transactions managed for web services

Hi,
How are transactions managed when publishing stored procedures as web
services? Does SQLXML automatically commit the transaction if the stored
procedure succeeded and rollback if it failed?
Thanks.
McGy
[url]http://mcgy.blogspot.com[/url]Hello McGy,

> How are transactions managed when publishing stored procedures as web
> services? Does SQLXML automatically commit the transaction if the
> stored procedure succeeded and rollback if it failed?
For SQL Server 2005 using SOAP endpoints: Nope. Layering a Web Service on
top of a stored proc doesn't change how the stored proc behaves, you still
need to manage the transactions correctly and internally to your own code.
The new TRY-CATCH syntax makes that easier, of course.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||I am using SQL Server 2000 with SQLXML and MS Soap Toolkit. There is not
opportunity to manage the transactions directly so I am presuming that
SQLXML does it automatically.
McGy
[url]http://mcgy.blogspot.com[/url]
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad7411b5a8c7bd5231b417cf@.news.microsoft.com...
> Hello McGy,
>
> For SQL Server 2005 using SOAP endpoints: Nope. Layering a Web Service on
> top of a stored proc doesn't change how the stored proc behaves, you still
> need to manage the transactions correctly and internally to your own code.
> The new TRY-CATCH syntax makes that easier, of course.
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>|||Transaction management is internal to the webservice endpoint. There is no
support for cross-service call transactions. If you want to build such a
system, you will have to build your own multi-level transaction management
scheme.
Best regards
Michael
"McGy" <anon@.anon.com> wrote in message
news:edNPGj37FHA.3760@.TK2MSFTNGP14.phx.gbl...
>I am using SQL Server 2000 with SQLXML and MS Soap Toolkit. There is not
>opportunity to manage the transactions directly so I am presuming that
>SQLXML does it automatically.
> --
> McGy
> [url]http://mcgy.blogspot.com[/url]
>
> "Kent Tegels" <ktegels@.develop.com> wrote in message
> news:b87ad7411b5a8c7bd5231b417cf@.news.microsoft.com...
>|||Hi Michael. Having the transaction management internal to the webservice is
absolutely ideal and music to my ears. I just wanted to confirm that this
was so before pressing ahead with a large project.
Thanks
McGy
[url]http://mcgy.blogspot.com[/url]
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:OapnwiQAGHA.3864@.TK2MSFTNGP12.phx.gbl...
> Transaction management is internal to the webservice endpoint. There is no
> support for cross-service call transactions. If you want to build such a
> system, you will have to build your own multi-level transaction management
> scheme.
> Best regards
> Michael
> "McGy" <anon@.anon.com> wrote in message
> news:edNPGj37FHA.3760@.TK2MSFTNGP14.phx.gbl...
>|||Hi Michael. Can the transaction isolation level be changed in the stored
procedure? What isolation level does the web service use by default?
Thanks.
McGy
[url]http://mcgy.blogspot.com[/url]
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:OapnwiQAGHA.3864@.TK2MSFTNGP12.phx.gbl...
> Transaction management is internal to the webservice endpoint. There is no
> support for cross-service call transactions. If you want to build such a
> system, you will have to build your own multi-level transaction management
> scheme.
> Best regards
> Michael
> "McGy" <anon@.anon.com> wrote in message
> news:edNPGj37FHA.3760@.TK2MSFTNGP14.phx.gbl...
>|||I am not sure if you can control it via the webservices interface (I am not
the expert here), but I would assume that it uses per default what is set
for the database...
Best regards
Michael
"McGy" <anon@.anon.com> wrote in message
news:%23Z%23KzBkAGHA.3456@.TK2MSFTNGP11.phx.gbl...
> Hi Michael. Can the transaction isolation level be changed in the stored
> procedure? What isolation level does the web service use by default?
> Thanks.
> --
> McGy
> [url]http://mcgy.blogspot.com[/url]
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:OapnwiQAGHA.3864@.TK2MSFTNGP12.phx.gbl...
>