if elseif .. in Store procedure:

Could anyone tell me the sytnax of IF command in store procedure ?
if ..
elseif ...
Thanks a lo.t
Does any tutorial web site teach about SP sytnax ?
There is no ElseIF but you can nest multiple Else's:
IF X = 1
...
ELSE
IF X = 2
...
ELSE
IF X = 3
...
Andrew J. Kelly SQL MVP
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:u9QY2UrDFHA.2632@.TK2MSFTNGP12.phx.gbl...
> Could anyone tell me the sytnax of IF command in store procedure ?
> if ..
> elseif ...
> Thanks a lo.t
> Does any tutorial web site teach about SP sytnax ?
>
|||You can make it a bit more like an elseif, if you put the ELSE and IF on the
same lines:
IF 1 = 0
PRINT 1
ELSE IF 1 = 2
PRINT 2
ELSE
PRINT 3
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:es4sKgrDFHA.2756@.TK2MSFTNGP15.phx.gbl...
> There is no ElseIF but you can nest multiple Else's:
>
> IF X = 1
> ...
> ELSE
> IF X = 2
> ...
> ELSE
> IF X = 3
> ...
> --
> Andrew J. Kelly SQL MVP
>
> "Agnes" <agnes@.dynamictech.com.hk> wrote in message
> news:u9QY2UrDFHA.2632@.TK2MSFTNGP12.phx.gbl...
>

IE hangs after running Report viewer

Hi all,

I run a web application with form authentication. In my web application, I have some links to call some reports from a reporting server. When user clicks the link of web report, a login dialog box asks user to enter the id and password. It works fine. However some web pages sometime can not run as usual after calling the report server. Can anyone give me some advisement to fix the problem?

Thanks in advance.

Best regards,

Tom

Hi,

From your description, it seems that your IE Browser hangs after you run your report viewer, right?

In most cases, the cause of this kind of issue is that you have not installed the service pack for your IE or your SQLServer Reporting Service.

Here are some links which should be installed:

If you are using SQLServer2000.

SQLServer2000 Reporting Service SP2
http://www.microsoft.com/downloads/details.aspx?FamilyId=502C0D89-1308-4662-8F58-CEC55EF1235B&displaylang=en

If you are using SQLServer2005

SQLServer2005 SP2

http://www.microsoft.com/downloads/details.aspx?FamilyId=d07219b2-1e23-49c8-8f0c-63fa18f26d3a&DisplayLang=en

If you are using IE6:

IE6 SP1
http://www.microsoft.com/downloads/details.aspx?FamilyID=1e1550cb-5e5d-48f5-b02b-20b602228de6&DisplayLang=en

Thanks.

|||

I tried to update the SP2 for the SQL server 2005, but the said error does not be fixed. I am using IE 7 for testing. Can anyone help me and give me some advisement?

Thanks a lot.

Tom

Idle Timeout, Orphan Timeout help

Here's the DB Connection code for a site (Web.config file)
I was wondering how I could specify an idle timeout or orphan timeout.
I tried idle timeout=x and kept getting errors

<add key="DBConn"
value="server=localhost;Trusted_Connection=true;database=SaltwaterFishing;Min Pool
Size=5;Max Pool Size=150;"></add
Thank youHere is the documentation describing allowed options for a SQL Connection.|||Thank you

Identity sql servers on a web application - how

Hi,

What I want to do is simple to explain, hard to obtain (i guess):

I want to fill a dropdownlist with the names of the sqlservers detected on the system. I′ve installed a sw recently that prompts for that, and when you select that server, then it prompts you to insert username and password, and then appears a dropdownlist again with the names of the databases of that sqlserver.

Is this possible to do, at least fill dropdownlists with sqlservers and database names?!?I don't see how, thanks!

I don't know about getting a list of servers, but to get a list of the databases you can just use the sql server profiler to "spy" on that application to get the query it used to find all the databases in that instance...|||

Hi dvd,

try this

DataTable servers = ((DbDataSourceEnumerator)SqlClientFactory.Instance.CreateDataSourceEnumerator()).GetDataSources();
servers.Columns.Add(newDataColumn("FullServerName",typeof(String),"ServerName + ISNULL(IIF(InstanceName <> \'\', (\'\\\' + InstanceName), InstanceName), \'\') "));
this.ddlServer.ValueMember ="FullServerName";
this.ddlServer.DisplayMember ="FullServerName";
 
the server table will contains the server and instance installed on each server, after you can bind to whatever you need.
 
 hope this help

|||

kpeguero@.hotmail.com:

Hi dvd,

try this

DataTable servers = ((DbDataSourceEnumerator)SqlClientFactory.Instance.CreateDataSourceEnumerator()).GetDataSources();
servers.Columns.Add(newDataColumn("FullServerName",typeof(String),"ServerName + ISNULL(IIF(InstanceName <> \'\', (\'\\\' + InstanceName), InstanceName), \'\') "));
this.ddlServer.ValueMember ="FullServerName";
this.ddlServer.DisplayMember ="FullServerName";
 
the server table will contains the server and instance installed on each server, after you can bind to whatever you need.
 
 hope this help

Ok so I've done this:

When you click a button, it fills a dropdownlist with the instances of the sqlservers available:

protected void Button1_Click(object sender, EventArgs e)
{

DbProviderFactory dbpf = SqlClientFactory.Instance;

DbDataSourceEnumerator instancia = dbpf.CreateDataSourceEnumerator();
DataTable dt = instancia.GetDataSources();

if (dt.Rows.Count < 1)
ddlServer.Items.Add("No items");
else{
foreach (DataRow dr in dt.Rows)
{
ddlServer.Items.Add(dr["ServerName"].ToString());
}
}

}

However it gives always the "No items" issue, which means no sql server found...am I missing something?!?Thanks!

|||

I dont know how you can get the server name but if you have the server name you can get the rest of the info.

SELECT * from <servername>.master.dbo.sysdatabases.

|||

I've been trying hard on this, however, no success. Using the last code I've posted, the only thing I get is "No items". Am I missing something? Cde on my web.config, saying which is my provider, factory or something? Appreciate a little help, thanks a lot!

|||

No one?Really stuck on this!

Identity range management with web synchronization

I am using SQL Server 2005 replication with anonymous subscriptions and web
synchronization. The server is SQL 2005 Standard and the subscribers are SQL
2005 Express. Every subscriber syncs with the publisher once an hour. There
are no re-publishers.
The subscription databases each run a client program that works on the local
subset of data (partitioning is done by SUSER_SNAME).
My problem is that periodically the client program will fail with an
Identity Range Check Constraint. The error can be fixed by replicating with
the server.
The way I understand SQL2005 replication with SQL2005 subscribers, each
subscriber should be getting a primary and a secondary range of identity
values. When the primary range runs out they should switch to the secondary
range and refresh the primary range (and vice-versa when the secondary range
runs out).
Alternatively, non-SQL2005 subscribers should request a new identity range
when their identity range threshold is exceeded. I have a subscriber identity
range of 10000 and a threshold of 80%. None of the subscribers have inserted
2000 rows between replications (they typically insert no more than 100 rows).
Please help.
<bump>
Let me try again...
I am using web synchronization for merge replication between SQL 2005 Std
and SQL 2005 Express. Occasionally the subscriber databases use their entire
identity range without requesting a new identity range from the publisher.
They request a new range only after the old range has been completely used.
Does anyone have any ideas how to fix this?
Thanks.
"John Van Vliet" wrote:

> I am using SQL Server 2005 replication with anonymous subscriptions and web
> synchronization. The server is SQL 2005 Standard and the subscribers are SQL
> 2005 Express. Every subscriber syncs with the publisher once an hour. There
> are no re-publishers.
> The subscription databases each run a client program that works on the local
> subset of data (partitioning is done by SUSER_SNAME).
> My problem is that periodically the client program will fail with an
> Identity Range Check Constraint. The error can be fixed by replicating with
> the server.
> The way I understand SQL2005 replication with SQL2005 subscribers, each
> subscriber should be getting a primary and a secondary range of identity
> values. When the primary range runs out they should switch to the secondary
> range and refresh the primary range (and vice-versa when the secondary range
> runs out).
> Alternatively, non-SQL2005 subscribers should request a new identity range
> when their identity range threshold is exceeded. I have a subscriber identity
> range of 10000 and a threshold of 80%. None of the subscribers have inserted
> 2000 rows between replications (they typically insert no more than 100 rows).
> Please help.

IDENTITY COLUMN QUESTION??

Hi
I am using VB.net/ASP.NET and SQL Server 2000 for a web application.

I have AccountNo as identity column. I use some dummy records for testing. But, every time I do a Delete from table and Insert Dummy rows, the identity Number of AccountNo starts for (Max(AccountNo)+1) of previously inserted rows. This throws of the AccountNo associated with my Dummy rows and actual table table rows.

How can I reset the IDENTITY to start from 1 without re-creating the table?

Please advice. Thanks in advance.

PankajTo my knowledge, you need to use TRUNCATE TABLE tablename to reset the identity value|||You can also use:

DBCC CHECKIDENT (authors, RESEED, 30)

where authors is the tablename and 30 is where you want to restart the IDENTITY. keep in mind that you will need to be aware of any potential conflicts in your data, such as if you are using the IDENTITY column as a primary key.

cs