creating a table

I need a module to help me create a SQLServer database table. Is there a module that allows users to specify parameters in the creation of a SQLserver table?well, firing a CREATE TABLE sql statement allows a fine level of control over table creation, as does using the SQL Server client tools. what exactly is your requirement here?sql

creating a stored procedure-- help

im novice to sqlserver and stored procedures.
Can the php code below be converted to a stored procedure
$y=1;
$query = "Select * From tblNews Order By aOrder";
$result = mysql_query($query,$db_connection);
$NoRows = mysql_num_rows($result);
if ($NoRows != 0 )
{
while ($row = mysql_fetch_array($result))
{
$UpdateQuery = "Update tblNews
Set aOrder= $y
Where ID=".$row["ID"];
mysql_query($UpdateQuery,$db_connection)
;
$y++;
}
}
basically, i want select all from tblNews, order by aOrder
then update aOrder in each record starting at 1 and incrementing by 1
untill all the records have been processed
Can anyone help me please, is it possible
thanks in advance
SteveIf all values in column [aOrder] are diff, then you can try,
update tblNews
set aOrder = (select count(*) from tblNews as a where a.aOrder <=
tblNews.aOrder)
AMB
"ahoy hoy" wrote:

> im novice to sqlserver and stored procedures.
> Can the php code below be converted to a stored procedure
> $y=1;
> $query = "Select * From tblNews Order By aOrder";
> $result = mysql_query($query,$db_connection);
> $NoRows = mysql_num_rows($result);
> if ($NoRows != 0 )
> {
> while ($row = mysql_fetch_array($result))
> {
> $UpdateQuery = "Update tblNews
> Set aOrder= $y
> Where ID=".$row["ID"];
> mysql_query($UpdateQuery,$db_connectio
n);
> $y++;
> }
> }
>
> basically, i want select all from tblNews, order by aOrder
> then update aOrder in each record starting at 1 and incrementing by 1
> untill all the records have been processed
> Can anyone help me please, is it possible
> thanks in advance
> Steve
>|||
Steve,
You could use something along the lines of this... (Un-Tested)
Create Proc TestProcedure
As Begin
Declare @.ID Integer
Declare @.NewOrder Integer
Set @.NewOrder = 1
Declare OrderCursor Cursor For
Select ID From tblNews
Order By aOrder
Open OrderCursor
Fetch Next From OrderCursor Into @.ID
While @.@.Fetch_Status = 0
Begin
Update tblNews
Set aOrder = @.NewOrder
Where ID = @.ID
Set @.NewOrder = @.NewOrder + 1
Fetch Next From OrderCursor Into @.ID
End
Close OrderCursor
Deallocate OrderCursor
End
Go
Although if it is a huge amount of Data and performance is an issue
then I would probably not use a Cursor.
Hope this helps
Barry|||Barry
thank you so much!
i wouldve been trying to figure that out for days, it is exactly what i
needed.
Just needed to use the correct field names and rename Interger to Int,
proc to procedure!
Now i can finish my job
Its only for a small amount of data, 10-20 records
Awesome
Steve :)
Barry wrote:

> Steve,
>
> You could use something along the lines of this... (Un-Tested)
>
> Create Proc TestProcedure
> As Begin
>
> Declare @.ID Integer
> Declare @.NewOrder Integer
> Set @.NewOrder = 1
>
> Declare OrderCursor Cursor For
> Select ID From tblNews
> Order By aOrder
>
> Open OrderCursor
> Fetch Next From OrderCursor Into @.ID
>
> While @.@.Fetch_Status = 0
> Begin
>
> Update tblNews
> Set aOrder = @.NewOrder
> Where ID = @.ID
> Set @.NewOrder = @.NewOrder + 1
> Fetch Next From OrderCursor Into @.ID
> End
> Close OrderCursor
> Deallocate OrderCursor
>
> End
> Go
>
> Although if it is a huge amount of Data and performance is an issue
> then I would probably not use a Cursor.
> Hope this helps
> Barry
>

Creating a Bottleneck.

I’m using Benchmark factory (TPC-C) on a server that has 3 instances of SQL
Server. Each instance has between 1-3 databases on it. Have set min and max
server memory to 512MB.
What I want:
When the TPC-C transactions are executed I want them to create a memory
and a disk I/O bottleneck.
Apart from the above memory setting what else can I change so that it will
result in a I/O bottleneck?
Cheers!
sqlcatz
If the SQL instance only has 512MB, you almost always get a memory and/or I/O
bottleneck with TPC-C when you configure the number of warehouses to be
larger than 10 and include sufficient number of users. If you want to
increase the I/O load, you can reduce the ratios of the read-only
transactions (OrderStatus and StockLevel) in the transaction mix. If you
don't have a really fast I/O subsystem that has a lot of cache, you should
see I/O bottleneck as long as the ffective size of the test database is
significantly larger than the memory size.
Linchi
"SQLCatz" wrote:

> I’m using Benchmark factory (TPC-C) on a server that has 3 instances of SQL
> Server. Each instance has between 1-3 databases on it. Have set min and max
> server memory to 512MB.
> What I want:
> When the TPC-C transactions are executed I want them to create a memory
> and a disk I/O bottleneck.
> Apart from the above memory setting what else can I change so that it will
> result in a I/O bottleneck?
> Cheers!
> sqlcatz
>
|||Hello Linchi!
Thank you for the suggestion.
I tried it.
Apart from getting memory related errors (expected) - and lots of deadlocks
I dont get anything else. On checking the AvgDiskQueueLength - it's just
2.73. I was expecting a much higher value. What else can I do to get the
bottleneck?
Cheers!
sqlcatz

Creating a Bottleneck.

I’m using Benchmark factory (TPC-C) on a server that has 3 instances of SQ
L
Server. Each instance has between 1-3 databases on it. Have set min and max
server memory to 512MB.
What I want:
When the TPC-C transactions are executed I want them to create a memory
and a disk I/O bottleneck.
Apart from the above memory setting what else can I change so that it will
result in a I/O bottleneck?
Cheers!
sqlcatzIf the SQL instance only has 512MB, you almost always get a memory and/or I/
O
bottleneck with TPC-C when you configure the number of warehouses to be
larger than 10 and include sufficient number of users. If you want to
increase the I/O load, you can reduce the ratios of the read-only
transactions (OrderStatus and StockLevel) in the transaction mix. If you
don't have a really fast I/O subsystem that has a lot of cache, you should
see I/O bottleneck as long as the ffective size of the test database is
significantly larger than the memory size.
Linchi
"SQLCatz" wrote:

> I’m using Benchmark factory (TPC-C) on a server that has 3 instances of
SQL
> Server. Each instance has between 1-3 databases on it. Have set min and m
ax
> server memory to 512MB.
> What I want:
> When the TPC-C transactions are executed I want them to create a memory
> and a disk I/O bottleneck.
> Apart from the above memory setting what else can I change so that it will
> result in a I/O bottleneck?
> Cheers!
> sqlcatz
>|||Hello Linchi!
Thank you for the suggestion.
I tried it.
Apart from getting memory related errors (expected) - and lots of deadlocks
I dont get anything else. On checking the AvgDiskQueueLength - it's just
2.73. I was expecting a much higher value. What else can I do to get the
bottleneck?
Cheers!
sqlcatz

Create virtual directory to remote SQL server on a web server without SQL

Is it possible to create a virtual directory to access a remote SQL
server on a web server that does not have SQL installed on it? I have
previously used the Configure SQL XML Support in IIS tool, and I was
hoping that this could still be installed on a server that has IIS but
not SQL on it. If it can, I would be very grateful to anyone who could
tell me how to do it. If it cannot, I would be equally grateful for
any other solutions to my problem.
Many thanks
You access remote SQL Server, typically, via TCP at certain port (1433 as
default) at certain location (computer name or IP address), plus appropriate
authentication. It has nothing to do with virtual directory on the web
server.
<clockemail@.alltel.net> wrote in message
news:1181750810.617111.24060@.i38g2000prf.googlegro ups.com...
> Is it possible to create a virtual directory to access a remote SQL
> server on a web server that does not have SQL installed on it? I have
> previously used the Configure SQL XML Support in IIS tool, and I was
> hoping that this could still be installed on a server that has IIS but
> not SQL on it. If it can, I would be very grateful to anyone who could
> tell me how to do it. If it cannot, I would be equally grateful for
> any other solutions to my problem.
> Many thanks
>
|||On Jun 13, 12:04 pm, "Norman Yuan" <NotR...@.NotReal.not> wrote:
> You access remote SQL Server, typically, via TCP at certain port (1433 as
> default) at certain location (computer name or IP address), plus appropriate
> authentication. It has nothing to do with virtual directory on the web
> server.
> <clockem...@.alltel.net> wrote in message
> news:1181750810.617111.24060@.i38g2000prf.googlegro ups.com...
>
>
> - Show quoted text -
Sorry, I should have been more specific. I need to make JavaScript
XMLHttpRequests to an SQL Server on a remote server, which I can
currently do by using virtual directories as both the database and
website are on the same server. However, both are moving to different
servers, and the web server does not have SQL installed on it.

Create virtual directory to remote SQL server on a web server without SQL

Is it possible to create a virtual directory to access a remote SQL
server on a web server that does not have SQL installed on it? I have
previously used the Configure SQL XML Support in IIS tool, and I was
hoping that this could still be installed on a server that has IIS but
not SQL on it. If it can, I would be very grateful to anyone who could
tell me how to do it. If it cannot, I would be equally grateful for
any other solutions to my problem.
Many thanksYou access remote SQL Server, typically, via TCP at certain port (1433 as
default) at certain location (computer name or IP address), plus appropriate
authentication. It has nothing to do with virtual directory on the web
server.
<clockemail@.alltel.net> wrote in message
news:1181750810.617111.24060@.i38g2000prf.googlegroups.com...
> Is it possible to create a virtual directory to access a remote SQL
> server on a web server that does not have SQL installed on it? I have
> previously used the Configure SQL XML Support in IIS tool, and I was
> hoping that this could still be installed on a server that has IIS but
> not SQL on it. If it can, I would be very grateful to anyone who could
> tell me how to do it. If it cannot, I would be equally grateful for
> any other solutions to my problem.
> Many thanks
>|||On Jun 13, 12:04 pm, "Norman Yuan" <NotR...@.NotReal.not> wrote:
> You access remote SQL Server, typically, via TCP at certain port (1433 as
> default) at certain location (computer name or IP address), plus appropria
te
> authentication. It has nothing to do with virtual directory on the web
> server.
> <clockem...@.alltel.net> wrote in message
> news:1181750810.617111.24060@.i38g2000prf.googlegroups.com...
>
>
>
> - Show quoted text -
Sorry, I should have been more specific. I need to make JavaScript
XMLHttpRequests to an SQL Server on a remote server, which I can
currently do by using virtual directories as both the database and
website are on the same server. However, both are moving to different
servers, and the web server does not have SQL installed on it.