Best Strategy to backup the system db

Hi,
I am trying to approach method to copy my system database using enterprise
tool. I have researched on Database Mainteance tool and Backup database. I
would like to use one of the method to copy my database without any
transcational log since we don't have any transcational going on and not
growing my database too much.
how would i accomplish this?
any comments would be appreciate
Pooja,
You might want to clarify whether or not this is a system database i.e.,
Master, MSDB, etc... or a user-defined database. When you say "my" database
I'm going to assume it is a user-defined database. If you do not require
the transaction logs to be backed up as part of your database recovery
strategy, you can set the recovery mode for the database to SIMPLE (the
t-log will be automatically truncated). Then schedule your full database
backup as a job or use a Maintenance Plan.
HTH
Jerry
"Pooja" <Pooja@.discussions.microsoft.com> wrote in message
news:8F05EF72-186D-4B93-BA20-E81364824F29@.microsoft.com...
> Hi,
> I am trying to approach method to copy my system database using enterprise
> tool. I have researched on Database Mainteance tool and Backup database. I
> would like to use one of the method to copy my database without any
> transcational log since we don't have any transcational going on and not
> growing my database too much.
> how would i accomplish this?
> any comments would be appreciate

Best Strategy to backup the system db

Hi,
I am trying to approach method to copy my system database using enterprise
tool. I have researched on Database Mainteance tool and Backup database. I
would like to use one of the method to copy my database without any
transcational log since we don't have any transcational going on and not
growing my database too much.
how would i accomplish this?
any comments would be appreciatePooja,
You might want to clarify whether or not this is a system database i.e.,
Master, MSDB, etc... or a user-defined database. When you say "my" database
I'm going to assume it is a user-defined database. If you do not require
the transaction logs to be backed up as part of your database recovery
strategy, you can set the recovery mode for the database to SIMPLE (the
t-log will be automatically truncated). Then schedule your full database
backup as a job or use a Maintenance Plan.
HTH
Jerry
"Pooja" <Pooja@.discussions.microsoft.com> wrote in message
news:8F05EF72-186D-4B93-BA20-E81364824F29@.microsoft.com...
> Hi,
> I am trying to approach method to copy my system database using enterprise
> tool. I have researched on Database Mainteance tool and Backup database. I
> would like to use one of the method to copy my database without any
> transcational log since we don't have any transcational going on and not
> growing my database too much.
> how would i accomplish this?
> any comments would be appreciate

Best Strategy to backup the system db

Hi,
I am trying to approach method to copy my system database using enterprise
tool. I have researched on Database Mainteance tool and Backup database. I
would like to use one of the method to copy my database without any
transcational log since we don't have any transcational going on and not
growing my database too much.
how would i accomplish this?
any comments would be appreciatePooja,
You might want to clarify whether or not this is a system database i.e.,
Master, MSDB, etc... or a user-defined database. When you say "my" database
I'm going to assume it is a user-defined database. If you do not require
the transaction logs to be backed up as part of your database recovery
strategy, you can set the recovery mode for the database to SIMPLE (the
t-log will be automatically truncated). Then schedule your full database
backup as a job or use a Maintenance Plan.
HTH
Jerry
"Pooja" <Pooja@.discussions.microsoft.com> wrote in message
news:8F05EF72-186D-4B93-BA20-E81364824F29@.microsoft.com...
> Hi,
> I am trying to approach method to copy my system database using enterprise
> tool. I have researched on Database Mainteance tool and Backup database. I
> would like to use one of the method to copy my database without any
> transcational log since we don't have any transcational going on and not
> growing my database too much.
> how would i accomplish this?
> any comments would be appreciate

Best practise on Database security

Hi All,
In our development server, everyone (Developers) are member of system
administrator (SA).
So on development server, anyone can do all database access.
In our production server, there are only two type of account which are
SA and Public Account.
SA can do all database access i.e.: creating the database, tables, and
security accounts, performing backups, and tuning the database.
Public account (used by application. passwd is created by SA and
encrypted on app setting), they can not execute query directly to
sqlserver, they can only run stored procedure provided.
Now, i want to develop new procedure to manage account and authority
on database.
Can anyone tell me, a best practise on this? (Database security)
I mean, what account should be provided in development and production
svr,
and what can each type of account do?
Rgds
HFIs it SQL Server 2000/2005?
<harifajri@.gmail.com> wrote in message
news:1176777055.132452.30140@.n59g2000hsh.googlegroups.com...
> Hi All,
> In our development server, everyone (Developers) are member of system
> administrator (SA).
> So on development server, anyone can do all database access.
> In our production server, there are only two type of account which are
> SA and Public Account.
> SA can do all database access i.e.: creating the database, tables, and
> security accounts, performing backups, and tuning the database.
> Public account (used by application. passwd is created by SA and
> encrypted on app setting), they can not execute query directly to
> sqlserver, they can only run stored procedure provided.
> Now, i want to develop new procedure to manage account and authority
> on database.
> Can anyone tell me, a best practise on this? (Database security)
> I mean, what account should be provided in development and production
> svr,
> and what can each type of account do?
> Rgds
> HF
>|||On Apr 17, 12:56 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Is it SQL Server 2000/2005?
> <harifa...@.gmail.com> wrote in message
> news:1176777055.132452.30140@.n59g2000hsh.googlegroups.com...
>
> > Hi All,
> > In our development server, everyone (Developers) are member of system
> > administrator (SA).
> > So on development server, anyone can do all database access.
> > In our production server, there are only two type of account which are
> > SA and Public Account.
> > SA can do all database access i.e.: creating the database, tables, and
> > security accounts, performing backups, and tuning the database.
> > Public account (used by application. passwd is created by SA and
> > encrypted on app setting), they can not execute query directly to
> > sqlserver, they can only run stored procedure provided.
> > Now, i want to develop new procedure to manage account and authority
> > on database.
> > Can anyone tell me, a best practise on this? (Database security)
> > I mean, what account should be provided in development and production
> > svr,
> > and what can each type of account do?
> > Rgds
> > HF- Hide quoted text -
> - Show quoted text -
We are using SQL Server 2000|||Hi
Use ROLEs to secure the data. Make sure that the users have an EXECUTE
permission only to run stored procedure and /or GRANT SELECT on VIEW...
http://vyaskn.tripod.com/sql_server_security_best_practices.htm --security
best practices
<harifajri@.gmail.com> wrote in message
news:1176862709.039446.313880@.n59g2000hsh.googlegroups.com...
> On Apr 17, 12:56 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> Is it SQL Server 2000/2005?
>> <harifa...@.gmail.com> wrote in message
>> news:1176777055.132452.30140@.n59g2000hsh.googlegroups.com...
>>
>> > Hi All,
>> > In our development server, everyone (Developers) are member of system
>> > administrator (SA).
>> > So on development server, anyone can do all database access.
>> > In our production server, there are only two type of account which are
>> > SA and Public Account.
>> > SA can do all database access i.e.: creating the database, tables, and
>> > security accounts, performing backups, and tuning the database.
>> > Public account (used by application. passwd is created by SA and
>> > encrypted on app setting), they can not execute query directly to
>> > sqlserver, they can only run stored procedure provided.
>> > Now, i want to develop new procedure to manage account and authority
>> > on database.
>> > Can anyone tell me, a best practise on this? (Database security)
>> > I mean, what account should be provided in development and production
>> > svr,
>> > and what can each type of account do?
>> > Rgds
>> > HF- Hide quoted text -
>> - Show quoted text -
> We are using SQL Server 2000
>

Best practice for writing own system procedures

Hello,

I'm searching for a best practice or other documentation for writing my own 'system procedure'.
I want to write procs which I can call in the context of every database without using a database name analogous to sp_who for example.
I read about the 'Resource Database'. All system procedures are stored in that readonly database and appear logically in the sys schema of every database.
But I couldn't find documentation about writing my own 'system procedure'.
I discover so far that procs with prefix sp_ stored in the master database do what I want. But is that the only way or is there a better way to do it?
In other threads I read that it is recommended not to use sp_ as prefix for procedures

Wolfgang?

It's not recommended to use the sp_ prefix because there is a slight performance hit if you use it for user stored procedures in a database other than master -- this is because SQL Server will look in master for the stored procedure, if it sees the sp_ prefix. However, if you're creating "system" stored procedures that should be callable from all databases, and which are created in master, then the sp_ prefix might make sense...

There are really no best practices I know of, that apply only to stored procedures in master. They follow the same basic rules as any other stored procedure. Note that you can't create objects in (or even access) the resource database -- it is hidden so that only the query engine can access it.

--
Adam Machanic
Pro SQL Server 2005, available now
http://www..apress.com/book/bookDisplay.html?bID=457
--

|||Just in addition to Adam, the performance hit will be caused from the Cache Miss that is produced if the procedures takes the sp_ prefix, a schema lock on the procedure and a recompilation of the procedure.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||Thank you for your answers.
As only procs with prefix 'sp_' stored in master are callable from all other databases I assume that that is the right way to write my own "system" procedures.
But I'm not very happy with that approach. I don't like to store ny own procs and tables in the master database because master is a central database for the whole server.

Wolfgang|||

You would have to define "system stored procedure" first. Very particularly, a system stored procedure has a prefix of sp_, is written by Microsoft, shipped with the product, and has special rules for name resolution.

I believe you are asking about "administrative stored procedures", these are procedures that you write that perform actions you need against one or more databases within a SQL Server. For these, I use an administrative database on the instance. I usually call it admin. In that database, I put all of my administrative procs along with any supporting tables, views, functions, etc. Any procedure can be called from any database, you just have to fully qualify the procedure.

|||OK, then talk about "administrative stored procedures".
Unfortunatly your approach doesn't fit my requirements. When I execute in a database kunktest1 'admin.dbo.kunkproc' I'm in the context of database admin and not in the kontext of database kunktest1. But I need to be in the kontext of the database from where I call the "administrative stored procedure" to select the objects of that database for example.

Wolfgang|||Normally you will be able to access the tables in the other databases using the three part names of the objects, like DatabaseName.OwnerOrSchema.Objectname. If this is not feasible for you and you really need the context of the database I would suggest generating a procedure in each database customized for each database.

HTH, Jens Suessmeyer:

http://www.sqlserver2005.de

BEST NAS on the MARKET?

Hi all the other GURU out there,
My company is trying to find a good NAS system for storage on our
network. We previously bought a IOMega 640Gbytes NAS, which is a
headless Win 2000 Server, it works but not the greatest.
What NAS out there would you recommand me to buy if I am looking for a
1 to 2 Tbytes NAS?
Thanks in advance.
CompGuru WannabeCompGuRu wrote:
> Hi all the other GURU out there,
> My company is trying to find a good NAS system for storage on our
> network. We previously bought a IOMega 640Gbytes NAS, which is a
> headless Win 2000 Server, it works but not the greatest.
> What NAS out there would you recommand me to buy if I am looking for a
> 1 to 2 Tbytes NAS?
> Thanks in advance.
> CompGuru Wannabe
Is this for use with SQL Server? If so, have a look here for issues and
requirements for using NAS devices with SQL Server.
http://support.microsoft.com/defaul...kb;en-us;304261
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks.. It probably just be a backup server for storing drive images
and source files.. I will give it a try..
COmp GuRU|||Decide on the application before you decide the hardware. NAS is not
for SQL Server.
David Portas
SQL Server MVP
--

BEST NAS on the MARKET?

Hi all the other GURU out there,
My company is trying to find a good NAS system for storage on our
network. We previously bought a IOMega 640Gbytes NAS, which is a
headless Win 2000 Server, it works but not the greatest.
What NAS out there would you recommand me to buy if I am looking for a
1 to 2 Tbytes NAS?
Thanks in advance.
CompGuru WannabeCompGuRu wrote:
> Hi all the other GURU out there,
> My company is trying to find a good NAS system for storage on our
> network. We previously bought a IOMega 640Gbytes NAS, which is a
> headless Win 2000 Server, it works but not the greatest.
> What NAS out there would you recommand me to buy if I am looking for a
> 1 to 2 Tbytes NAS?
> Thanks in advance.
> CompGuru Wannabe
Is this for use with SQL Server? If so, have a look here for issues and
requirements for using NAS devices with SQL Server.
http://support.microsoft.com/default.aspx?scid=kb;en-us;304261
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks.. It probably just be a backup server for storing drive images
and source files.. I will give it a try..
COmp GuRU|||Decide on the application before you decide the hardware. NAS is not
for SQL Server.
--
David Portas
SQL Server MVP
--

BEST NAS on the MARKET?

Hi all the other GURU out there,
My company is trying to find a good NAS system for storage on our
network. We previously bought a IOMega 640Gbytes NAS, which is a
headless Win 2000 Server, it works but not the greatest.
What NAS out there would you recommand me to buy if I am looking for a
1 to 2 Tbytes NAS?
Thanks in advance.
CompGuru Wannabe
CompGuRu wrote:
> Hi all the other GURU out there,
> My company is trying to find a good NAS system for storage on our
> network. We previously bought a IOMega 640Gbytes NAS, which is a
> headless Win 2000 Server, it works but not the greatest.
> What NAS out there would you recommand me to buy if I am looking for a
> 1 to 2 Tbytes NAS?
> Thanks in advance.
> CompGuru Wannabe
Is this for use with SQL Server? If so, have a look here for issues and
requirements for using NAS devices with SQL Server.
http://support.microsoft.com/default...b;en-us;304261
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Thanks.. It probably just be a backup server for storing drive images
and source files.. I will give it a try..
COmp GuRU
|||Decide on the application before you decide the hardware. NAS is not
for SQL Server.
David Portas
SQL Server MVP

Best filtering solution for performance?

Hello,

i have a report with approximately 20 reportparameters from queries. This is really slowing down the system, although the dataload (rows sent back) is not that huge. I read on the internet that there are basicly three approaches.

1, Query parameter with alot of diffrent datasources

2, Table filtering

3, Stored procedures

I wonder which approach is the best and why it takes such a long time to get the report with the report parameters (before generating the report itself).

Thank you for your help!

I would think stored procedures would be the best approach. Have you tried using SQL profile to trace where the slowdown is?

cheers,

Andrew

|||

Hello,

thank you for you fast reponse! I now changed to Stored Procedures, i thin in long term it is better to use SP. But now i Have problems with multivalued parameters, which will be sent as an array to SQL Server. I found some articles about this matter an will try to solve it. The performance problem seems to be solved although I don't know why. Will look into that later. Thanks fpr the profiler tip! Best regards!

Best Development Machine Configuration Recommendations

What is the best / recommended setup for a development system where you want
to develop using .NET 2.0 using VS 2005 against both SSRS 2000 and SSRS
2005?VS 2005 does not create reports that are compatible with SRS 2000. However,
the SRS 2000 report designer works against both SRS 2000 and 2005.
Thanks
Tudor
"Jim" wrote:
> What is the best / recommended setup for a development system where you want
> to develop using .NET 2.0 using VS 2005 against both SSRS 2000 and SSRS
> 2005?
>
>|||But, and this is important, those reports cannot provide for any of the 2005
features like end user sorting, multi-valued parameters etc.
I suggest installing both development environments. To do this you need VS
2003 and VS 2005. They will install side by side and both be usable (that is
what I have done).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Tudor Trufinescu (MSFT)" <TudorTrufinescuMSFT@.discussions.microsoft.com>
wrote in message news:BEF6807F-D848-4C1E-AC6A-32706D9B8F33@.microsoft.com...
> VS 2005 does not create reports that are compatible with SRS 2000.
> However,
> the SRS 2000 report designer works against both SRS 2000 and 2005.
> Thanks
> Tudor
> "Jim" wrote:
>> What is the best / recommended setup for a development system where you
>> want
>> to develop using .NET 2.0 using VS 2005 against both SSRS 2000 and SSRS
>> 2005?
>>

Best design method to allow for "dynamic" records

First, a quick overview of my project. I'm designing a vehicle
tracking system that takes data from multiple types of GPS devices,
stores the data in a common database, and allows the user to view
device locations in real time or create reports on previous activity.
Currently we're only using one type of device, but I'm trying to
futureproof the app so I don't have to redesign it down the road.
Since the devices have different capabilities, I'm trying to come up
with the best method to store their data in a common format. For
example, say I have two devices, DeviceA and DeviceB. Both can report
their latitude, longitude, speed, heading, and a timestamp. DeviceA
can also report an odometer value, whether or not it has a GPS fix,
and telematics data. DeviceB cannot report those values. Along with
this data, every record will be tagged with address information on the
server side. Down the road, we may have DeviceC, DeviceD, etc with
new capabilities.
Now, the question is, how can I store all of this information in a way
that is simple to search/order/etc that is also "dynamic"? I've
thought about using three tables...one would contain "basic" record
info...latitude, longitude, speed, heading, timestamp, and a reason
code for why the record was sent (ignition on/off, start/stop, etc).
A second table would contain address information (street number,
street name, city, state, ZIP) and would be linked back to the basic
table. The third table would contain a single field with an XML
fragment detailing the rest of the information for that record
(odometer value, GPS fix status, telematics, etc).
Problem is, I can't find any method to allow me to search through XML
contained in a column other than a full text search. I also have the
problem of creating a result set containing all of the dynamic columns
(or selected ones only) to be returned to my ASP.NET application for
reporting.
Other methods I have thought about...storing all "extra" info in a
huge table with columns for each value. This would result in a lot of
wasted space (NULL values everywhere for devices that don't support
those features) and new columns would have to be added each time a new
device is supported (if new features are provided). Yet another
method...store values as key/value pairs in a separate table. This
would be rediculously slow though...I would have to generate some
heavy-duty dynamic SQL (crosstab query, basically) to pull all of the
values I need and dump them into a result set.
The data volume will be large (50K-60K records per day) and reporting
needs to be fairly responsive (web based reporting system...generate a
result set from SQL Server, typically using a date/time range, with
the required fields and pass back to data access layer for final
processing). So...out of the three methods I've thought about...any
comments or thoughts about which would be the best way to go? Are
there any other methods I should take into consideration? I know SQL
Server 2005 is supposed to have much improved XML support if I go that
route, but that's not an option at this point...I'm stuck with using
2000 for now.
Thanks for any help you can offer...it will be greatly appreciated!
Why dont you have a table like this for the extra info
(Vehicle ID, Capability ID, Value)
Vehicle ID + Capability ID will be the primary key. You can avoid the NULLs this way as you have rows for only those capabilities the vehicle has. If you think you dont add vehicles often to the system, then you can remove the VehicleID from the table and
create a table for every Vehicle with (CapabilityID and Value) as columns.
Is this a possible option?
Chandra
"Jeff L." wrote:

> First, a quick overview of my project. I'm designing a vehicle
> tracking system that takes data from multiple types of GPS devices,
> stores the data in a common database, and allows the user to view
> device locations in real time or create reports on previous activity.
> Currently we're only using one type of device, but I'm trying to
> futureproof the app so I don't have to redesign it down the road.
> Since the devices have different capabilities, I'm trying to come up
> with the best method to store their data in a common format. For
> example, say I have two devices, DeviceA and DeviceB. Both can report
> their latitude, longitude, speed, heading, and a timestamp. DeviceA
> can also report an odometer value, whether or not it has a GPS fix,
> and telematics data. DeviceB cannot report those values. Along with
> this data, every record will be tagged with address information on the
> server side. Down the road, we may have DeviceC, DeviceD, etc with
> new capabilities.
> Now, the question is, how can I store all of this information in a way
> that is simple to search/order/etc that is also "dynamic"? I've
> thought about using three tables...one would contain "basic" record
> info...latitude, longitude, speed, heading, timestamp, and a reason
> code for why the record was sent (ignition on/off, start/stop, etc).
> A second table would contain address information (street number,
> street name, city, state, ZIP) and would be linked back to the basic
> table. The third table would contain a single field with an XML
> fragment detailing the rest of the information for that record
> (odometer value, GPS fix status, telematics, etc).
> Problem is, I can't find any method to allow me to search through XML
> contained in a column other than a full text search. I also have the
> problem of creating a result set containing all of the dynamic columns
> (or selected ones only) to be returned to my ASP.NET application for
> reporting.
> Other methods I have thought about...storing all "extra" info in a
> huge table with columns for each value. This would result in a lot of
> wasted space (NULL values everywhere for devices that don't support
> those features) and new columns would have to be added each time a new
> device is supported (if new features are provided). Yet another
> method...store values as key/value pairs in a separate table. This
> would be rediculously slow though...I would have to generate some
> heavy-duty dynamic SQL (crosstab query, basically) to pull all of the
> values I need and dump them into a result set.
> The data volume will be large (50K-60K records per day) and reporting
> needs to be fairly responsive (web based reporting system...generate a
> result set from SQL Server, typically using a date/time range, with
> the required fields and pass back to data access layer for final
> processing). So...out of the three methods I've thought about...any
> comments or thoughts about which would be the best way to go? Are
> there any other methods I should take into consideration? I know SQL
> Server 2005 is supposed to have much improved XML support if I go that
> route, but that's not an option at this point...I'm stuck with using
> 2000 for now.
> Thanks for any help you can offer...it will be greatly appreciated!
>
|||Why dont you have a table like this for the extra info
(Vehicle ID, Capability ID, Value)
Vehicle ID + Capability ID will be the primary key. You can avoid the NULLs this way as you have rows for only those capabilities the vehicle has. If you think you dont add vehicles often to the system, then you can remove the VehicleID from the table and
create a table for every Vehicle with (CapabilityID and Value) as columns.
Is this a possible option?
Chandra
"Jeff L." wrote:

> First, a quick overview of my project. I'm designing a vehicle
> tracking system that takes data from multiple types of GPS devices,
> stores the data in a common database, and allows the user to view
> device locations in real time or create reports on previous activity.
> Currently we're only using one type of device, but I'm trying to
> futureproof the app so I don't have to redesign it down the road.
> Since the devices have different capabilities, I'm trying to come up
> with the best method to store their data in a common format. For
> example, say I have two devices, DeviceA and DeviceB. Both can report
> their latitude, longitude, speed, heading, and a timestamp. DeviceA
> can also report an odometer value, whether or not it has a GPS fix,
> and telematics data. DeviceB cannot report those values. Along with
> this data, every record will be tagged with address information on the
> server side. Down the road, we may have DeviceC, DeviceD, etc with
> new capabilities.
> Now, the question is, how can I store all of this information in a way
> that is simple to search/order/etc that is also "dynamic"? I've
> thought about using three tables...one would contain "basic" record
> info...latitude, longitude, speed, heading, timestamp, and a reason
> code for why the record was sent (ignition on/off, start/stop, etc).
> A second table would contain address information (street number,
> street name, city, state, ZIP) and would be linked back to the basic
> table. The third table would contain a single field with an XML
> fragment detailing the rest of the information for that record
> (odometer value, GPS fix status, telematics, etc).
> Problem is, I can't find any method to allow me to search through XML
> contained in a column other than a full text search. I also have the
> problem of creating a result set containing all of the dynamic columns
> (or selected ones only) to be returned to my ASP.NET application for
> reporting.
> Other methods I have thought about...storing all "extra" info in a
> huge table with columns for each value. This would result in a lot of
> wasted space (NULL values everywhere for devices that don't support
> those features) and new columns would have to be added each time a new
> device is supported (if new features are provided). Yet another
> method...store values as key/value pairs in a separate table. This
> would be rediculously slow though...I would have to generate some
> heavy-duty dynamic SQL (crosstab query, basically) to pull all of the
> values I need and dump them into a result set.
> The data volume will be large (50K-60K records per day) and reporting
> needs to be fairly responsive (web based reporting system...generate a
> result set from SQL Server, typically using a date/time range, with
> the required fields and pass back to data access layer for final
> processing). So...out of the three methods I've thought about...any
> comments or thoughts about which would be the best way to go? Are
> there any other methods I should take into consideration? I know SQL
> Server 2005 is supposed to have much improved XML support if I go that
> route, but that's not an option at this point...I'm stuck with using
> 2000 for now.
> Thanks for any help you can offer...it will be greatly appreciated!
>
|||Hi Jeff,
SQL 2005 in effect adds the ability to XQuery the data on a column; there is
the "XML" column type that allows this. Also the performance is quite good
since (from what I understand) data is optimized and indexed based on the
XSD information given when defining this field.
Of course this approach is not an option since Yukon is still many months
away.
See below on the poinst I suggest you to follow... and a mid-way solution
that can help!
Ciao,
Adriano

>...store values as key/value pairs in a separate table. This
> would be rediculously slow though...I would have to generate some
> heavy-duty dynamic SQL (crosstab query, basically) to pull all of the
> values I need and dump them into a result set.
Yes this kind of normalization is very good because you don't rely on actual
fields to store information, thus reducing space wasting.
Performance-speacking: SQL Server 2000 has a great set of features you can
use to improve querying speed.
1) Indexed views: You can perform aggregations on this table using Indexed
Views in order to have real-time view of your data in a "de-normalized" and
summarized way, where necessary.
2) Mantain only last-month data here so you have last-month queries quite
fast; move the oldest ones in a parallel "history "table. Report this table
only when explicitly requested by the user.

> The data volume will be large (50K-60K records per day) and reporting
> needs to be fairly responsive (web based reporting system...generate a
> result set from SQL Server, typically using a date/time range, with
> the required fields and pass back to data access layer for final
> processing). So...out of the three methods I've thought about...any
> comments or thoughts about which would be the best way to go? Are
> there any other methods I should take into consideration? I know SQL
> Server 2005 is supposed to have much improved XML support if I go that
> route, but that's not an option at this point...I'm stuck with using
> 2000 for now.
Another solution?
Build many tables, one for each "device type".. .where you can store "extra"
information without wasting space.

> Thanks for any help you can offer...it will be greatly appreciated!

Best Design for reports over time

Hi everyone,
I am designing a reporting system for the internet using Reporting
Services. Is there a way to access the historical snapshots
programmatically? We are displaying the reports in the browser by
linking to them in the url, but that brings up the current month's
report. We want to be able to run the report monthly(different data)
and generate a snapshot, then link to the snapshots from the custom
ASP.Net application.
Can we do this? Is is there a better way?
Thanks in advance,
ShawnJust render a history snapshot and look at the generated URL. The URL will
look like this:
http://ServerName/Reports/Pages/Report.aspx?ItemPath=%SomeReport&HistoryID=2005-05-11T22:25:40
The HistoryID identifies the history snapshot in UTC time.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"sysdesigner" <sysdesigner@.discussions.microsoft.com> wrote in message
news:7B7A9AA3-2EBB-45D2-85B2-AD58FE3A5A09@.microsoft.com...
> Hi everyone,
> I am designing a reporting system for the internet using Reporting
> Services. Is there a way to access the historical snapshots
> programmatically? We are displaying the reports in the browser by
> linking to them in the url, but that brings up the current month's
> report. We want to be able to run the report monthly(different data)
> and generate a snapshot, then link to the snapshots from the custom
> ASP.Net application.
>
> Can we do this? Is is there a better way?
>
> Thanks in advance,
> Shawn
>
>

Best DataType for content system

Hello,
On our corporate website, we will be using email notifications for
various things. I would like for our marketing guy to be able to edit
the email templates over the web so that I don't have to keep updating
them myself for every little change. I want to store the templates
within the database (since they will be small). The templates will be
HTML based and not more than a few thousand bytes in length. Should I
use a VARCHAR or VARBINARY, or what? I would assume VARCHAR, but I'm
not sure.

Thanks,
WillFoehammer (foehammer@.hotmail.com) writes:
> On our corporate website, we will be using email notifications for
> various things. I would like for our marketing guy to be able to edit
> the email templates over the web so that I don't have to keep updating
> them myself for every little change. I want to store the templates
> within the database (since they will be small). The templates will be
> HTML based and not more than a few thousand bytes in length. Should I
> use a VARCHAR or VARBINARY, or what? I would assume VARCHAR, but I'm
> not sure.

Unless you intend to compress the templates to be able handle case
that the marketing guys enters more than 8000 characters, there is
no reason to use VARBINARY, use VARCHAR.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Best approach with DTS

Let me see if I can explain this.

I have the need to pull data from multiple tables from a DB2 system via ODBC and update or insert as needed into tables in a SQL200 DB.

Step 1.
The data from the initial parent table will need to be limited to being a set number of days old, which I have in place and working.

Step 2
The next tables data needs to be limited from the data retrieved in step 1 (Id like to use the paprent table retrieved in step 1, that is in SQL now, rather than doing it on the DB2 side.

Step 3
The returned rows here, need to be limited to key values returned from step 2

Additional steps apply, but nearly all will be limited to the results of parent tables from the prior step.

What is the best approach to this? I really want to pull table A to SQL, and limit the next child set from Table A, that was pulled to SQL in the prior step.

I also need to do updates rather than dropping and creating the needed tables each time. Insert if no key exists, etc .etc.

What is the best approach?I think DTS is better in this regard.
You need to workout to re-arrange data based upon the requirement.
Once data is imported you can contro updations from SQL side using normal TSQL.|||I think I'm going to continue to limit the selection on the db2 side based on sub queries. Initially set it up to drop and create the tables each time, and after that's all done, modify to import into temp tables from dts and then use sql to update the existing tables from the temp tables, I think this is the approach I'm going to take.

I'm open to ideas for alternatives

Benefits of upgrading SQL 7 to 2K

Could someone please let me know the specific 'benefits' of upgrading a SQL 7 system to a 2K? For instance if any of you have done this, did you notice a significant change within your system(s)?
Thanks!!!Well, as an Enterprise solution, 7.0 cannot be reliably clustered. Performance may improve on certain queries, but don't be surprised to see a degradation, though it's pretty rare. The most benefits are for applications that are either being newly developed or the ones that can be modified without infringing your support agreement with your vendor. The reason is added functionality. But if your system needs only backups and occasional reindexing, - don't fix what ain't broke :)|||There are lots of benefits to upgrading and many articles available on the subject...

Here are a few links that will give you some background:

http://msdn.microsoft.com/library/default.asp?url=/nhp/Default.asp?contentid=28000409

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/whatsnew/wn_whatnew_7im0.asp?frame=true

CPN|||Originally posted by NotAvg1
Could someone please let me know the specific 'benefits' of upgrading a SQL 7 system to a 2K? For instance if any of you have done this, did you notice a significant change within your system(s)?

Thanks!!!

There are a few keywords, functions, etc. that are in 2K that aren't in 7. We have one vendors application that uses some of them, but they are good enough to have an MSDE version that we can use.

I would say that unless you are worried about clustering, or a specific application requires it, I don't think there is enough justification.

Besides I bet a 2K3 or 2K4 version is probably in alpha testing somewhere. That way you could just jump versions.

BEGIN STACK DUMP error in log viewer

Hi

We are having problems with our application that uses SQL Server 2005 in a cluster environment. Sometimes the system stops answering and registers in the log viewer the following error:

=====================================================================

BugCheck Dump

=====================================================================

This file is generated by Microsoft SQL Server

version 9.00.1399.06

upon detection of fatal unexpected error. Please return this file, the query or program that produced the bugcheck, the database and the error log, and any other pertinent information with a Service Request.

Computer type is AT/AT COMPATIBLE.

Bios Version is IBM- 1001

Current time is 16:48:36 12/05/06.

2 Intel x86 level 15, 3600 Mhz processor (s).

Windows NT 5.2 Build 3790 CSD Service Pack 1.

Memory

MemoryLoad = 74%

Total Physical = 3327 MB

Available Physical = 858 MB

Total Page File = 9318 MB

Available Page File = 7058 MB

Total Virtual = 2047 MB

Available Virtual = 274 MB

**Dump thread - spid = 132, PSS = 0x71E09588, EC = 0x71E09590

***Stack Dump being sent to E:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLDump0178.txt

* *******************************************************************************

*

* BEGIN STACK DUMP:

*12/05/06 16:48:36 spid 132

*

* Location:lckmgr.cpp:10820

* Expression:GetLocalLockPartition () == xactLockInfo->GetLocalLockPartition ()

* SPID:132

* Process ID:2436

*

* Input Buffer 255 bytes -

*?16 00 00 00 12 00 00 00 02 00 01 00 00 00 84 00 00 00

*???dD 01 00 00 00 ff ff 0a 00 02 00 00 00 e7 64 09 09 04 d0

*4dS E L E C T00 34 64 09 20 00 53 00 45 00 4c 00 45 00 43 00 54 00

*TAbleA (*) 20 00 54 00 48 00 69 00 73 00 74 00 6f 00 72 00 69 00

(*) There is a query here that I have excluded in the message

*

*MODULEBASEENDSIZE

* sqlservr0100000002BA7FFF01ba8000

* ntdll7C9100007C9D3FFF000c4000

* kernel327C8000007C90BFFF0010c000

* MSVCR8078130000781CAFFF0009b000

* msvcrt77B9000077BE9FFF0005a000

* MSVCP807C4200007C4A6FFF00087000

* ADVAPI3277D9000077E3DFFF000ae000

* RPCRT477C4000077CDEFFF0009f000

* USER3277F4000077FD1FFF00092000

* GDI3277BF000077C37FFF00048000

* CRYPT32760D000076164FFF00095000

* MSASN1760B0000760C1FFF00012000

* Secur3276E7000076E82FFF00013000

* MSWSOCK71970000719B1FFF00042000

* WS2_3271A5000071A66FFF00017000

* WS2HELP71A4000071A47FFF00008000

* USERENV7684000076904FFF000c5000

* opends60333E0000333E6FFF00007000

* NETAPI3271A9000071AE7FFF00058000

* SHELL327C9E00007D1EAFFF0080b000

* SHLWAPI77EE000077F31FFF00052000

* comctl327736000077462FFF00103000

* EntApi3700000037012FFF00013000

* PSAPI76A9000076A9AFFF0000b000

* WININET779D000077A78FFF000a9000

* OLEAUT3277CF000077D7BFFF0008c000

* ole327751000077643FFF00134000

* instapi4806000048069FFF 0000a000

* CLUSAPI74CF000074D01FFF00012000

* RESUTILS74E0000074E12FFF00013000

* sqlevn704F6100004F7A0FFF00191000

* SQLOS344D0000344D4FFF00005000

* rsaenh680000006802EFFF0002f000

* AUTHZ76B6000076B73FFF00014000

* MSCOREE340C000034104FFF00045000

* msv1_076BB000076BD6FFF00027000

* iphlpapi76C1000076C29FFF0001a000

* kerberos3433000034387FFF00058000

* cryptdll766000007660BFFF0000c000

* schannel7667000076696FFF00027000

* COMRES76F30000770BCFFF0018d000

* XOLEHLP343F0000343F5FFF00006000

* MSDTCPRX3440000034477FFF00078000

* msvcp60780C000078120FFF00061000

* MTXCLU74E5000074E68FFF00019000

* VERSION77B8000077B87FFF00008000

* WSOCK3271A0000071A09FFF0000a000

* DNSAPI76DF000076E1EFFF0002f000

* winrnr76E9000076E96FFF00007000

* WLDAP3276E3000076E5EFFF0002f000

* rasadhlp76EA000076EA7FFF00008000

* hnetcfg36190000361E8FFF00059000

* wshtcpip7193000071937FFF00008000

* security3634000036343FFF00004000

* msfte36A6000036CB7FFF00258000

* dbghelp36D0000036E17FFF00118000

* WINTRUST76AD000076AFAFFF 0002b000

* imagehlp76B3000076B58FFF00029000

* dssenh6810000068123FFF00024000

* NTMARTA777B0000777D1FFF00022000

* SAMLIB36FE000036FEEFFF0000f000

* ntdsapi7661000076624FFF00015000

* xpsp2res61BF000061EBFFFF002d0000

* CLBCatQ77650000776D2FFF00083000

* sqlncli61EC0000620E1FFF00222000

* COMCTL3277E4000077ED6FFF00097000

* comdlg32761D000076218FFF00049000

* SQLNCLIR007C0000007F2FFF00033000

* msftepxy621F000062204FFF00015000

* xpsqlbot6286000062865FFF00006000

* xpstar9062880000628C4FFF00045000

* SQLSCM90628E0000628E8FFF00009000

* ODBC32629000006293CFFF0003d000

* BatchParser90629400006295DFFF0001e000

* SQLSVC906297000062989FFF0001a000

* SqlResourceLoader629A0000629A5FFF00006000

* ATL807C6300007C64AFFF0001b000

* odbcint62B0000062B17FFF00018000

* SQLSVC9062B2000062B22FFF00003000

* xpstar9062B3000062B55FFF00026000

* xplog7062B6000062B6BFFF0000c000

* xplog7062B8000062B82FFF00003000

* oledb32631B000063228FFF00079000

* MSDART6323000063249FFF0001a000

* OLEDB32R634D0000634E1FFF00012000

* activeds76D1000076D42FFF 00033000

* adsldpc76CE000076D06FFF00027000

* credui76AA000076ACDFFF0002e000

* ATL769A0000769B7FFF00018000

* adsldp711100007113DFFF0002e000

* SXS75CB000075D6BFFF000bc000

* dbghelp65D4000065E52FFF00113000

*

*Edi: 6610BCB8:636A19003E3A604062F1A0406610D8AD0279E9003E3A63D8

*Esi: 00000000:

*Eax: 6610BB9C:000042AC00000000000000007C815E02000000007C931B34

*Ebx: 0000003F:

*Ecx: 6610C20C:00000000000100070000000000740072636A19046610BBCC

*Edx: 0000003D:

*Eip: 7C815E02:10C2C95E90909000A164909000000018C334408B891C428B

*Ebp: 6610BBEC:6610BC3002172CE4000042AC000000000000000000000000

*SegCs: 0000001B:

*EFlags: 00000246:

*Esp: 6610BB98:71E09588000042AC00000000000000007C815E0200000000

*SegSs: 78130023:000000000000000000000000000000000000000000000000

* *******************************************************************************

* -

* Short Stack Dump

7C815E02 Module(kernel32+00015E02)

02172CE4 Module(sqlservr+01172CE4)

02176BA0 Module(sqlservr+01176BA0)

02019506 Module(sqlservr+01019506)

015738EE Module(sqlservr+005738EE)

021B15B6 Module(sqlservr+011B15B6)

0163DD36 Module(sqlservr+0063DD36)

010E9FA3 Module(sqlservr+000E9FA3)

010B0F5F Module(sqlservr+000B0F5F)

0102C5F8 Module(sqlservr+0002C5F8)

01BEE12B Module(sqlservr+00BEE12B)

01BF2BCB Module(sqlservr+00BF2BCB)

01BF353D Module(sqlservr+00BF353D)

010438E5 Module(sqlservr+000438E5)

01041C35 Module(sqlservr+00041C35)

0100889F Module(sqlservr+0000889F)

010089C5 Module(sqlservr+000089C5)

010086E7 Module(sqlservr+000086E7)

010D764A Module(sqlservr+000D764A)

010D7B71 Module(sqlservr+000D7B71)

010D746E Module(sqlservr+000D746E)

010D83F0 Module(sqlservr+000D83F0)

781329AA Module(MSVCR80+000029AA)

78132A36 Module(MSVCR80+00002A36)

PSS @.0x71E09588

CSession @.0x71E08278

--

m_spid = 132m_cRef = 12m_rgcRefType[0] = 1

m_rgcRefType[1] = 1m_rgcRefType[2] = 9m_rgcRefType[3] = 1

m_rgcRefType[4] = 0m_rgcRefType[5] = 0m_pmo = 0x71E08040

m_pstackBhfPool = 0x00000000m_dwLoginFlags = 0x03e0m_fBackground = 0

m_fClientRequestConnReset = 0m_fUserProc = -1m_fConnReset = 0

m_fIsConnReset = 0m_fInLogin = 0m_fReplRelease = 0

m_fKill = 0m_ulLoginStamp = 3105683m_eclClient = 5

m_protType = 5m_hHttpToken = FFFFFFFF

m_pV7LoginRec

00000000:18010000 02000972 401f0000 00000006 400c0000 ?.......r@........@....

00000014:00000000 e0030000 00000000 00000000 5e000400 ?................^...

00000028:66000200 6a000000 7a001c00 b2000c00 ca000000 ?f...j...z...........

0000003C:ca001c00 02010000 02010b00 60f120db ad481801 ?............`. ..H..

00000050:00001801 00001801 00000000 0000???????????????..............

CPhysicalConnection @.0x71E08188

-

m_pPhyConn->m_pmo = 0x71E08040m_pPhyConn->m_pNetConn = 0x71E08788m_pPhyConn->m_pConnList = 0x71E08260

m_pPhyConn->m_pSess = 0x71E08278m_pPhyConn->m_fTracked = -1m_pPhyConn->m_cbPacketsize = 8000

m_pPhyConn->m_fMars = 0m_pPhyConn->m_fKill = 0

CBatch @.0x71E08A90

m_pSess = 0x71E08278m_pConn = 0x71E089F0m_cRef = 3

m_rgcRefType[0] = 1m_rgcRefType[1] = 1m_rgcRefType[2] = 1

m_rgcRefType[3] = 0m_rgcRefType[4] = 0m_pTask = 0x006F9D38

EXCEPT (null) @.0x6610B4AC

-

exc_number = 0exc_severity = 0exc_func = 0x023D96B0

Task @.0x006F9D38

-

CPU Ticks used (ms) = 1Task State = 2

WAITINFO_INTERNAL: WaitResource = 0x00000000WAITINFO_INTERNAL: WaitType = 0x0

WAITINFO_INTERNAL: WaitSpinlock = 0x00000000SchedulerId = 0x0

ThreadId = 0x444m_state = 0m_eAbortSev = 0

EC @.0x71E09590

--

spid = 132ecid = 0ec_stat = 0x0

ec_stat2 = 0x40ec_atomic = 0x4__fSubProc = 1

ec_dbccContext = 0x00000000__pSETLS = 0x71E08A30__pSEParams = 0x71E08CD0

__pDbLocks = 0x71E09878

SEInternalTLS @.0x71E08A30

-

m_flags = 0m_TLSstatus = 3m_owningTask = 0x006F9D38

m_activeHeapDatasetList = 0x71E08A30m_activeIndexDatasetList = 0x71E08A38

SEParams @.0x71E08CD0

--

m_lockTimeout = -1m_isoLevel = 1048576m_logDontReplicate = 0

m_neverReplicate = 0m_XactWorkspace = 0x03F78940m_pSessionLocks = 0x71E09A88

m_pDbLocks = 0x71E09878m_execStats = 0x3F867018m_pAllocFileLimit = 0x00000000

Does anybody know what’s going on?

I will be very appreciate if someone can help me to solve this problem. Thank you!

Hi,

We would like to investigate this issue. Can you please file a bug via http://connect.microsoft.com/sql. Please upload SqlDump0178.mdmp and SqlDump0178.txt there as well.

Thanks, Ron D.

|||any luck on this one. We are seeing the same error.

Thanks,
Siva|||

Make sure the table being accessed have proper CLUSTERED index.

BEGIN STACK DUMP error in log viewer

Hi

We are having problems with our application that uses SQL Server 2005 in a cluster environment. Sometimes the system stops answering and registers in the log viewer the following error:

=====================================================================

BugCheck Dump

=====================================================================

This file is generated by Microsoft SQL Server

version 9.00.1399.06

upon detection of fatal unexpected error. Please return this file, the query or program that produced the bugcheck, the database and the error log, and any other pertinent information with a Service Request.

Computer type is AT/AT COMPATIBLE.

Bios Version is IBM- 1001

Current time is 16:48:36 12/05/06.

2 Intel x86 level 15, 3600 Mhz processor (s).

Windows NT 5.2 Build 3790 CSD Service Pack 1.

Memory

MemoryLoad = 74%

Total Physical = 3327 MB

Available Physical = 858 MB

Total Page File = 9318 MB

Available Page File = 7058 MB

Total Virtual = 2047 MB

Available Virtual = 274 MB

**Dump thread - spid = 132, PSS = 0x71E09588, EC = 0x71E09590

***Stack Dump being sent to E:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLDump0178.txt

* *******************************************************************************

*

* BEGIN STACK DUMP:

*12/05/06 16:48:36 spid 132

*

* Location:lckmgr.cpp:10820

* Expression:GetLocalLockPartition () == xactLockInfo->GetLocalLockPartition ()

* SPID:132

* Process ID:2436

*

* Input Buffer 255 bytes -

*?16 00 00 00 12 00 00 00 02 00 01 00 00 00 84 00 00 00

*???dD 01 00 00 00 ff ff 0a 00 02 00 00 00 e7 64 09 09 04 d0

*4dS E L E C T00 34 64 09 20 00 53 00 45 00 4c 00 45 00 43 00 54 00

*TAbleA (*) 20 00 54 00 48 00 69 00 73 00 74 00 6f 00 72 00 69 00

(*) There is a query here that I have excluded in the message

*

*MODULEBASEENDSIZE

* sqlservr0100000002BA7FFF01ba8000

* ntdll7C9100007C9D3FFF000c4000

* kernel327C8000007C90BFFF0010c000

* MSVCR8078130000781CAFFF0009b000

* msvcrt77B9000077BE9FFF0005a000

* MSVCP807C4200007C4A6FFF00087000

* ADVAPI3277D9000077E3DFFF000ae000

* RPCRT477C4000077CDEFFF0009f000

* USER3277F4000077FD1FFF00092000

* GDI3277BF000077C37FFF00048000

* CRYPT32760D000076164FFF00095000

* MSASN1760B0000760C1FFF00012000

* Secur3276E7000076E82FFF00013000

* MSWSOCK71970000719B1FFF00042000

* WS2_3271A5000071A66FFF00017000

* WS2HELP71A4000071A47FFF00008000

* USERENV7684000076904FFF000c5000

* opends60333E0000333E6FFF00007000

* NETAPI3271A9000071AE7FFF00058000

* SHELL327C9E00007D1EAFFF0080b000

* SHLWAPI77EE000077F31FFF00052000

* comctl327736000077462FFF00103000

* EntApi3700000037012FFF00013000

* PSAPI76A9000076A9AFFF0000b000

* WININET779D000077A78FFF000a9000

* OLEAUT3277CF000077D7BFFF0008c000

* ole327751000077643FFF00134000

* instapi4806000048069FFF 0000a000

* CLUSAPI74CF000074D01FFF00012000

* RESUTILS74E0000074E12FFF00013000

* sqlevn704F6100004F7A0FFF00191000

* SQLOS344D0000344D4FFF00005000

* rsaenh680000006802EFFF0002f000

* AUTHZ76B6000076B73FFF00014000

* MSCOREE340C000034104FFF00045000

* msv1_076BB000076BD6FFF00027000

* iphlpapi76C1000076C29FFF0001a000

* kerberos3433000034387FFF00058000

* cryptdll766000007660BFFF0000c000

* schannel7667000076696FFF00027000

* COMRES76F30000770BCFFF0018d000

* XOLEHLP343F0000343F5FFF00006000

* MSDTCPRX3440000034477FFF00078000

* msvcp60780C000078120FFF00061000

* MTXCLU74E5000074E68FFF00019000

* VERSION77B8000077B87FFF00008000

* WSOCK3271A0000071A09FFF0000a000

* DNSAPI76DF000076E1EFFF0002f000

* winrnr76E9000076E96FFF00007000

* WLDAP3276E3000076E5EFFF0002f000

* rasadhlp76EA000076EA7FFF00008000

* hnetcfg36190000361E8FFF00059000

* wshtcpip7193000071937FFF00008000

* security3634000036343FFF00004000

* msfte36A6000036CB7FFF00258000

* dbghelp36D0000036E17FFF00118000

* WINTRUST76AD000076AFAFFF 0002b000

* imagehlp76B3000076B58FFF00029000

* dssenh6810000068123FFF00024000

* NTMARTA777B0000777D1FFF00022000

* SAMLIB36FE000036FEEFFF0000f000

* ntdsapi7661000076624FFF00015000

* xpsp2res61BF000061EBFFFF002d0000

* CLBCatQ77650000776D2FFF00083000

* sqlncli61EC0000620E1FFF00222000

* COMCTL3277E4000077ED6FFF00097000

* comdlg32761D000076218FFF00049000

* SQLNCLIR007C0000007F2FFF00033000

* msftepxy621F000062204FFF00015000

* xpsqlbot6286000062865FFF00006000

* xpstar9062880000628C4FFF00045000

* SQLSCM90628E0000628E8FFF00009000

* ODBC32629000006293CFFF0003d000

* BatchParser90629400006295DFFF0001e000

* SQLSVC906297000062989FFF0001a000

* SqlResourceLoader629A0000629A5FFF00006000

* ATL807C6300007C64AFFF0001b000

* odbcint62B0000062B17FFF00018000

* SQLSVC9062B2000062B22FFF00003000

* xpstar9062B3000062B55FFF00026000

* xplog7062B6000062B6BFFF0000c000

* xplog7062B8000062B82FFF00003000

* oledb32631B000063228FFF00079000

* MSDART6323000063249FFF0001a000

* OLEDB32R634D0000634E1FFF00012000

* activeds76D1000076D42FFF 00033000

* adsldpc76CE000076D06FFF00027000

* credui76AA000076ACDFFF0002e000

* ATL769A0000769B7FFF00018000

* adsldp711100007113DFFF0002e000

* SXS75CB000075D6BFFF000bc000

* dbghelp65D4000065E52FFF00113000

*

*Edi: 6610BCB8:636A19003E3A604062F1A0406610D8AD0279E9003E3A63D8

*Esi: 00000000:

*Eax: 6610BB9C:000042AC00000000000000007C815E02000000007C931B34

*Ebx: 0000003F:

*Ecx: 6610C20C:00000000000100070000000000740072636A19046610BBCC

*Edx: 0000003D:

*Eip: 7C815E02:10C2C95E90909000A164909000000018C334408B891C428B

*Ebp: 6610BBEC:6610BC3002172CE4000042AC000000000000000000000000

*SegCs: 0000001B:

*EFlags: 00000246:

*Esp: 6610BB98:71E09588000042AC00000000000000007C815E0200000000

*SegSs: 78130023:000000000000000000000000000000000000000000000000

* *******************************************************************************

* -

* Short Stack Dump

7C815E02 Module(kernel32+00015E02)

02172CE4 Module(sqlservr+01172CE4)

02176BA0 Module(sqlservr+01176BA0)

02019506 Module(sqlservr+01019506)

015738EE Module(sqlservr+005738EE)

021B15B6 Module(sqlservr+011B15B6)

0163DD36 Module(sqlservr+0063DD36)

010E9FA3 Module(sqlservr+000E9FA3)

010B0F5F Module(sqlservr+000B0F5F)

0102C5F8 Module(sqlservr+0002C5F8)

01BEE12B Module(sqlservr+00BEE12B)

01BF2BCB Module(sqlservr+00BF2BCB)

01BF353D Module(sqlservr+00BF353D)

010438E5 Module(sqlservr+000438E5)

01041C35 Module(sqlservr+00041C35)

0100889F Module(sqlservr+0000889F)

010089C5 Module(sqlservr+000089C5)

010086E7 Module(sqlservr+000086E7)

010D764A Module(sqlservr+000D764A)

010D7B71 Module(sqlservr+000D7B71)

010D746E Module(sqlservr+000D746E)

010D83F0 Module(sqlservr+000D83F0)

781329AA Module(MSVCR80+000029AA)

78132A36 Module(MSVCR80+00002A36)

PSS @.0x71E09588

CSession @.0x71E08278

--

m_spid = 132m_cRef = 12m_rgcRefType[0] = 1

m_rgcRefType[1] = 1m_rgcRefType[2] = 9m_rgcRefType[3] = 1

m_rgcRefType[4] = 0m_rgcRefType[5] = 0m_pmo = 0x71E08040

m_pstackBhfPool = 0x00000000m_dwLoginFlags = 0x03e0m_fBackground = 0

m_fClientRequestConnReset = 0m_fUserProc = -1m_fConnReset = 0

m_fIsConnReset = 0m_fInLogin = 0m_fReplRelease = 0

m_fKill = 0m_ulLoginStamp = 3105683m_eclClient = 5

m_protType = 5m_hHttpToken = FFFFFFFF

m_pV7LoginRec

00000000:18010000 02000972 401f0000 00000006 400c0000 ?.......r@........@....

00000014:00000000 e0030000 00000000 00000000 5e000400 ?................^...

00000028:66000200 6a000000 7a001c00 b2000c00 ca000000 ?f...j...z...........

0000003C:ca001c00 02010000 02010b00 60f120db ad481801 ?............`. ..H..

00000050:00001801 00001801 00000000 0000???????????????..............

CPhysicalConnection @.0x71E08188

-

m_pPhyConn->m_pmo = 0x71E08040m_pPhyConn->m_pNetConn = 0x71E08788m_pPhyConn->m_pConnList = 0x71E08260

m_pPhyConn->m_pSess = 0x71E08278m_pPhyConn->m_fTracked = -1m_pPhyConn->m_cbPacketsize = 8000

m_pPhyConn->m_fMars = 0m_pPhyConn->m_fKill = 0

CBatch @.0x71E08A90

m_pSess = 0x71E08278m_pConn = 0x71E089F0m_cRef = 3

m_rgcRefType[0] = 1m_rgcRefType[1] = 1m_rgcRefType[2] = 1

m_rgcRefType[3] = 0m_rgcRefType[4] = 0m_pTask = 0x006F9D38

EXCEPT (null) @.0x6610B4AC

-

exc_number = 0exc_severity = 0exc_func = 0x023D96B0

Task @.0x006F9D38

-

CPU Ticks used (ms) = 1Task State = 2

WAITINFO_INTERNAL: WaitResource = 0x00000000WAITINFO_INTERNAL: WaitType = 0x0

WAITINFO_INTERNAL: WaitSpinlock = 0x00000000SchedulerId = 0x0

ThreadId = 0x444m_state = 0m_eAbortSev = 0

EC @.0x71E09590

--

spid = 132ecid = 0ec_stat = 0x0

ec_stat2 = 0x40ec_atomic = 0x4__fSubProc = 1

ec_dbccContext = 0x00000000__pSETLS = 0x71E08A30__pSEParams = 0x71E08CD0

__pDbLocks = 0x71E09878

SEInternalTLS @.0x71E08A30

-

m_flags = 0m_TLSstatus = 3m_owningTask = 0x006F9D38

m_activeHeapDatasetList = 0x71E08A30m_activeIndexDatasetList = 0x71E08A38

SEParams @.0x71E08CD0

--

m_lockTimeout = -1m_isoLevel = 1048576m_logDontReplicate = 0

m_neverReplicate = 0m_XactWorkspace = 0x03F78940m_pSessionLocks = 0x71E09A88

m_pDbLocks = 0x71E09878m_execStats = 0x3F867018m_pAllocFileLimit = 0x00000000

Does anybody know what’s going on?

I will be very appreciate if someone can help me to solve this problem. Thank you!

Hi,

We would like to investigate this issue. Can you please file a bug via http://connect.microsoft.com/sql. Please upload SqlDump0178.mdmp and SqlDump0178.txt there as well.

Thanks, Ron D.

|||any luck on this one. We are seeing the same error.

Thanks,
Siva|||

Make sure the table being accessed have proper CLUSTERED index.

BEGIN STACK DUMP error in log viewer

Hi

We are having problems with our application that uses SQL Server 2005 in a cluster environment. Sometimes the system stops answering and registers in the log viewer the following error:

=====================================================================

BugCheck Dump

=====================================================================

This file is generated by Microsoft SQL Server

version 9.00.1399.06

upon detection of fatal unexpected error. Please return this file, the query or program that produced the bugcheck, the database and the error log, and any other pertinent information with a Service Request.

Computer type is AT/AT COMPATIBLE.

Bios Version is IBM- 1001

Current time is 16:48:36 12/05/06.

2 Intel x86 level 15, 3600 Mhz processor (s).

Windows NT 5.2 Build 3790 CSD Service Pack 1.

Memory

MemoryLoad = 74%

Total Physical = 3327 MB

Available Physical = 858 MB

Total Page File = 9318 MB

Available Page File = 7058 MB

Total Virtual = 2047 MB

Available Virtual = 274 MB

**Dump thread - spid = 132, PSS = 0x71E09588, EC = 0x71E09590

***Stack Dump being sent to E:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLDump0178.txt

* *******************************************************************************

*

* BEGIN STACK DUMP:

*12/05/06 16:48:36 spid 132

*

* Location:lckmgr.cpp:10820

* Expression:GetLocalLockPartition () == xactLockInfo->GetLocalLockPartition ()

* SPID:132

* Process ID:2436

*

* Input Buffer 255 bytes -

*?16 00 00 00 12 00 00 00 02 00 01 00 00 00 84 00 00 00

*???dD 01 00 00 00 ff ff 0a 00 02 00 00 00 e7 64 09 09 04 d0

*4dS E L E C T00 34 64 09 20 00 53 00 45 00 4c 00 45 00 43 00 54 00

*TAbleA (*) 20 00 54 00 48 00 69 00 73 00 74 00 6f 00 72 00 69 00

(*) There is a query here that I have excluded in the message

*

*MODULEBASEENDSIZE

* sqlservr0100000002BA7FFF01ba8000

* ntdll7C9100007C9D3FFF000c4000

* kernel327C8000007C90BFFF0010c000

* MSVCR8078130000781CAFFF0009b000

* msvcrt77B9000077BE9FFF0005a000

* MSVCP807C4200007C4A6FFF00087000

* ADVAPI3277D9000077E3DFFF000ae000

* RPCRT477C4000077CDEFFF0009f000

* USER3277F4000077FD1FFF00092000

* GDI3277BF000077C37FFF00048000

* CRYPT32760D000076164FFF00095000

* MSASN1760B0000760C1FFF00012000

* Secur3276E7000076E82FFF00013000

* MSWSOCK71970000719B1FFF00042000

* WS2_3271A5000071A66FFF00017000

* WS2HELP71A4000071A47FFF00008000

* USERENV7684000076904FFF000c5000

* opends60333E0000333E6FFF00007000

* NETAPI3271A9000071AE7FFF00058000

* SHELL327C9E00007D1EAFFF0080b000

* SHLWAPI77EE000077F31FFF00052000

* comctl327736000077462FFF00103000

* EntApi3700000037012FFF00013000

* PSAPI76A9000076A9AFFF0000b000

* WININET779D000077A78FFF000a9000

* OLEAUT3277CF000077D7BFFF0008c000

* ole327751000077643FFF00134000

* instapi4806000048069FFF 0000a000

* CLUSAPI74CF000074D01FFF00012000

* RESUTILS74E0000074E12FFF00013000

* sqlevn704F6100004F7A0FFF00191000

* SQLOS344D0000344D4FFF00005000

* rsaenh680000006802EFFF0002f000

* AUTHZ76B6000076B73FFF00014000

* MSCOREE340C000034104FFF00045000

* msv1_076BB000076BD6FFF00027000

* iphlpapi76C1000076C29FFF0001a000

* kerberos3433000034387FFF00058000

* cryptdll766000007660BFFF0000c000

* schannel7667000076696FFF00027000

* COMRES76F30000770BCFFF0018d000

* XOLEHLP343F0000343F5FFF00006000

* MSDTCPRX3440000034477FFF00078000

* msvcp60780C000078120FFF00061000

* MTXCLU74E5000074E68FFF00019000

* VERSION77B8000077B87FFF00008000

* WSOCK3271A0000071A09FFF0000a000

* DNSAPI76DF000076E1EFFF0002f000

* winrnr76E9000076E96FFF00007000

* WLDAP3276E3000076E5EFFF0002f000

* rasadhlp76EA000076EA7FFF00008000

* hnetcfg36190000361E8FFF00059000

* wshtcpip7193000071937FFF00008000

* security3634000036343FFF00004000

* msfte36A6000036CB7FFF00258000

* dbghelp36D0000036E17FFF00118000

* WINTRUST76AD000076AFAFFF 0002b000

* imagehlp76B3000076B58FFF00029000

* dssenh6810000068123FFF00024000

* NTMARTA777B0000777D1FFF00022000

* SAMLIB36FE000036FEEFFF0000f000

* ntdsapi7661000076624FFF00015000

* xpsp2res61BF000061EBFFFF002d0000

* CLBCatQ77650000776D2FFF00083000

* sqlncli61EC0000620E1FFF00222000

* COMCTL3277E4000077ED6FFF00097000

* comdlg32761D000076218FFF00049000

* SQLNCLIR007C0000007F2FFF00033000

* msftepxy621F000062204FFF00015000

* xpsqlbot6286000062865FFF00006000

* xpstar9062880000628C4FFF00045000

* SQLSCM90628E0000628E8FFF00009000

* ODBC32629000006293CFFF0003d000

* BatchParser90629400006295DFFF0001e000

* SQLSVC906297000062989FFF0001a000

* SqlResourceLoader629A0000629A5FFF00006000

* ATL807C6300007C64AFFF0001b000

* odbcint62B0000062B17FFF00018000

* SQLSVC9062B2000062B22FFF00003000

* xpstar9062B3000062B55FFF00026000

* xplog7062B6000062B6BFFF0000c000

* xplog7062B8000062B82FFF00003000

* oledb32631B000063228FFF00079000

* MSDART6323000063249FFF0001a000

* OLEDB32R634D0000634E1FFF00012000

* activeds76D1000076D42FFF 00033000

* adsldpc76CE000076D06FFF00027000

* credui76AA000076ACDFFF0002e000

* ATL769A0000769B7FFF00018000

* adsldp711100007113DFFF0002e000

* SXS75CB000075D6BFFF000bc000

* dbghelp65D4000065E52FFF00113000

*

*Edi: 6610BCB8:636A19003E3A604062F1A0406610D8AD0279E9003E3A63D8

*Esi: 00000000:

*Eax: 6610BB9C:000042AC00000000000000007C815E02000000007C931B34

*Ebx: 0000003F:

*Ecx: 6610C20C:00000000000100070000000000740072636A19046610BBCC

*Edx: 0000003D:

*Eip: 7C815E02:10C2C95E90909000A164909000000018C334408B891C428B

*Ebp: 6610BBEC:6610BC3002172CE4000042AC000000000000000000000000

*SegCs: 0000001B:

*EFlags: 00000246:

*Esp: 6610BB98:71E09588000042AC00000000000000007C815E0200000000

*SegSs: 78130023:000000000000000000000000000000000000000000000000

* *******************************************************************************

* -

* Short Stack Dump

7C815E02 Module(kernel32+00015E02)

02172CE4 Module(sqlservr+01172CE4)

02176BA0 Module(sqlservr+01176BA0)

02019506 Module(sqlservr+01019506)

015738EE Module(sqlservr+005738EE)

021B15B6 Module(sqlservr+011B15B6)

0163DD36 Module(sqlservr+0063DD36)

010E9FA3 Module(sqlservr+000E9FA3)

010B0F5F Module(sqlservr+000B0F5F)

0102C5F8 Module(sqlservr+0002C5F8)

01BEE12B Module(sqlservr+00BEE12B)

01BF2BCB Module(sqlservr+00BF2BCB)

01BF353D Module(sqlservr+00BF353D)

010438E5 Module(sqlservr+000438E5)

01041C35 Module(sqlservr+00041C35)

0100889F Module(sqlservr+0000889F)

010089C5 Module(sqlservr+000089C5)

010086E7 Module(sqlservr+000086E7)

010D764A Module(sqlservr+000D764A)

010D7B71 Module(sqlservr+000D7B71)

010D746E Module(sqlservr+000D746E)

010D83F0 Module(sqlservr+000D83F0)

781329AA Module(MSVCR80+000029AA)

78132A36 Module(MSVCR80+00002A36)

PSS @.0x71E09588

CSession @.0x71E08278

--

m_spid = 132m_cRef = 12m_rgcRefType[0] = 1

m_rgcRefType[1] = 1m_rgcRefType[2] = 9m_rgcRefType[3] = 1

m_rgcRefType[4] = 0m_rgcRefType[5] = 0m_pmo = 0x71E08040

m_pstackBhfPool = 0x00000000m_dwLoginFlags = 0x03e0m_fBackground = 0

m_fClientRequestConnReset = 0m_fUserProc = -1m_fConnReset = 0

m_fIsConnReset = 0m_fInLogin = 0m_fReplRelease = 0

m_fKill = 0m_ulLoginStamp = 3105683m_eclClient = 5

m_protType = 5m_hHttpToken = FFFFFFFF

m_pV7LoginRec

00000000:18010000 02000972 401f0000 00000006 400c0000 ?.......r@........@....

00000014:00000000 e0030000 00000000 00000000 5e000400 ?................^...

00000028:66000200 6a000000 7a001c00 b2000c00 ca000000 ?f...j...z...........

0000003C:ca001c00 02010000 02010b00 60f120db ad481801 ?............`. ..H..

00000050:00001801 00001801 00000000 0000???????????????..............

CPhysicalConnection @.0x71E08188

-

m_pPhyConn->m_pmo = 0x71E08040m_pPhyConn->m_pNetConn = 0x71E08788m_pPhyConn->m_pConnList = 0x71E08260

m_pPhyConn->m_pSess = 0x71E08278m_pPhyConn->m_fTracked = -1m_pPhyConn->m_cbPacketsize = 8000

m_pPhyConn->m_fMars = 0m_pPhyConn->m_fKill = 0

CBatch @.0x71E08A90

m_pSess = 0x71E08278m_pConn = 0x71E089F0m_cRef = 3

m_rgcRefType[0] = 1m_rgcRefType[1] = 1m_rgcRefType[2] = 1

m_rgcRefType[3] = 0m_rgcRefType[4] = 0m_pTask = 0x006F9D38

EXCEPT (null) @.0x6610B4AC

-

exc_number = 0exc_severity = 0exc_func = 0x023D96B0

Task @.0x006F9D38

-

CPU Ticks used (ms) = 1Task State = 2

WAITINFO_INTERNAL: WaitResource = 0x00000000WAITINFO_INTERNAL: WaitType = 0x0

WAITINFO_INTERNAL: WaitSpinlock = 0x00000000SchedulerId = 0x0

ThreadId = 0x444m_state = 0m_eAbortSev = 0

EC @.0x71E09590

--

spid = 132ecid = 0ec_stat = 0x0

ec_stat2 = 0x40ec_atomic = 0x4__fSubProc = 1

ec_dbccContext = 0x00000000__pSETLS = 0x71E08A30__pSEParams = 0x71E08CD0

__pDbLocks = 0x71E09878

SEInternalTLS @.0x71E08A30

-

m_flags = 0m_TLSstatus = 3m_owningTask = 0x006F9D38

m_activeHeapDatasetList = 0x71E08A30m_activeIndexDatasetList = 0x71E08A38

SEParams @.0x71E08CD0

--

m_lockTimeout = -1m_isoLevel = 1048576m_logDontReplicate = 0

m_neverReplicate = 0m_XactWorkspace = 0x03F78940m_pSessionLocks = 0x71E09A88

m_pDbLocks = 0x71E09878m_execStats = 0x3F867018m_pAllocFileLimit = 0x00000000

Does anybody know what’s going on?

I will be very appreciate if someone can help me to solve this problem. Thank you!

Hi,

We would like to investigate this issue. Can you please file a bug via http://connect.microsoft.com/sql. Please upload SqlDump0178.mdmp and SqlDump0178.txt there as well.

Thanks, Ron D.

|||any luck on this one. We are seeing the same error.

Thanks,
Siva

|||Make sure the table being accessed have proper CLUSTERED index.

BDNull error...not expected!

I have a connection (SqlConnection1) established through the GUI. Here's the
code:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
'Create and initialise the command object
Dim cmd As New SqlCommand("GetAddress", SqlConnection1)
'State that the type of this command is a stored procedure
cmd.CommandType = CommandType.StoredProcedure
'Create the input paramater
cmd.Parameters.Add("@.BizName", SqlDbType.VarChar, 50)
cmd.Parameters("@.BizName").Direction = ParameterDirection.Input
cmd.Parameters("@.BizName").Value = TextBox1.Text
'Create the output paramater
cmd.Parameters.Add("@.BizAddress", SqlDbType.VarChar, 50)
cmd.Parameters("@.BizAddress").Direction = ParameterDirection.Output
'Enusre the connection is open
If (cmd.Connection.State <> ConnectionState.Open) Then
cmd.Connection.Open()
End If
'Execute the command object
cmd.ExecuteNonQuery()
'Assign the returned value of the output paramater
TextBox2.Text = cmd.Parameters("@.BizAddress").Value
'Close the connection
cmd.Connection.Close()
End Sub
This code allows a textbox (textbox1) to fill an input paramater and
displays the contents of the returned value from the output paramater in a
textbox (textbox2). It works fine with s imple select statement. But with a
stored procedure i get this error:
Cast from type 'DBNull' to type 'String' is not valid.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information abou
t
the error and where it originated in the code.
Exception Details: System.InvalidCastException: Cast from type 'DBNull' to
type 'String' is not valid.
Source Error:
Line 63:
Line 64: 'Assign the returned value of the output paramater
Line 65: TextBox2.Text = cmd.Parameters("@.BizAddress").Value
The code for the stored procedure:
CREATE PROCEDURE GetAddress
@.BizName varchar,
@.BizAddress varchar output
AS
SELECT @.BizAddress = Address FROM nabilTable WHERE Name = @.BizName
GO
Where did i go wrong?
Thanks for any insights.
NabYour Stored Procedure is returning a NULL value in the @.BizAddress output
parameter. You need to assign the value to an Object and check it for
DBNull.value before converting to string:
Dim o As Object
o = cmd.Parameters("@.BizAddress").Value
If (o Is Nothing OrElse o Is DBNull.Value) Then
TextBox2.Text = ""
Else
TextBox2.Text = Convert.ToString(o)
EndIf
"Nab" <Nab@.discussions.microsoft.com> wrote in message
news:96300218-F090-44B3-A5E9-F30FCE80D711@.microsoft.com...
>I have a connection (SqlConnection1) established through the GUI. Here's
>the
> code:
> Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles Button1.Click
> 'Create and initialise the command object
> Dim cmd As New SqlCommand("GetAddress", SqlConnection1)
> 'State that the type of this command is a stored procedure
> cmd.CommandType = CommandType.StoredProcedure
> 'Create the input paramater
> cmd.Parameters.Add("@.BizName", SqlDbType.VarChar, 50)
> cmd.Parameters("@.BizName").Direction = ParameterDirection.Input
> cmd.Parameters("@.BizName").Value = TextBox1.Text
> 'Create the output paramater
> cmd.Parameters.Add("@.BizAddress", SqlDbType.VarChar, 50)
> cmd.Parameters("@.BizAddress").Direction = ParameterDirection.Output
> 'Enusre the connection is open
> If (cmd.Connection.State <> ConnectionState.Open) Then
> cmd.Connection.Open()
> End If
> 'Execute the command object
> cmd.ExecuteNonQuery()
> 'Assign the returned value of the output paramater
> TextBox2.Text = cmd.Parameters("@.BizAddress").Value
> 'Close the connection
> cmd.Connection.Close()
> End Sub
> This code allows a textbox (textbox1) to fill an input paramater and
> displays the contents of the returned value from the output paramater in a
> textbox (textbox2). It works fine with s imple select statement. But with
> a
> stored procedure i get this error:
> Cast from type 'DBNull' to type 'String' is not valid.
> Description: An unhandled exception occurred during the execution of the
> current web request. Please review the stack trace for more information
> about
> the error and where it originated in the code.
> Exception Details: System.InvalidCastException: Cast from type 'DBNull' to
> type 'String' is not valid.
> Source Error:
>
> Line 63:
> Line 64: 'Assign the returned value of the output paramater
> Line 65: TextBox2.Text = cmd.Parameters("@.BizAddress").Value
>
> The code for the stored procedure:
> CREATE PROCEDURE GetAddress
> @.BizName varchar,
> @.BizAddress varchar output
> AS
> SELECT @.BizAddress = Address FROM nabilTable WHERE Name = @.BizName
> GO
> Where did i go wrong?
> Thanks for any insights.
> Nab
>
>|||A NULL @.BizAddress value will be returned when no data is found and this
cannot be converted to a .Net string data type. You can check for NULL
using DbNull.Value:
If cmd.Parameters("@.BizAddress").Value Is DBNull.Value Then
MessageBox.Show("BizName not found")
Else
TextBox2.Text = cmd.Parameters("@.BizAddress").Value
End If
Also, you need specify varchar(50) in your stored procedure parameter
declaration. The default length is 1.
CREATE PROCEDURE GetAddress
@.BizName varchar(50),
@.BizAddress varchar (50) output
AS
SELECT @.BizAddress = Address FROM nabilTable WHERE Name = @.BizName
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Nab" <Nab@.discussions.microsoft.com> wrote in message
news:96300218-F090-44B3-A5E9-F30FCE80D711@.microsoft.com...
>I have a connection (SqlConnection1) established through the GUI. Here's
>the
> code:
> Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles Button1.Click
> 'Create and initialise the command object
> Dim cmd As New SqlCommand("GetAddress", SqlConnection1)
> 'State that the type of this command is a stored procedure
> cmd.CommandType = CommandType.StoredProcedure
> 'Create the input paramater
> cmd.Parameters.Add("@.BizName", SqlDbType.VarChar, 50)
> cmd.Parameters("@.BizName").Direction = ParameterDirection.Input
> cmd.Parameters("@.BizName").Value = TextBox1.Text
> 'Create the output paramater
> cmd.Parameters.Add("@.BizAddress", SqlDbType.VarChar, 50)
> cmd.Parameters("@.BizAddress").Direction = ParameterDirection.Output
> 'Enusre the connection is open
> If (cmd.Connection.State <> ConnectionState.Open) Then
> cmd.Connection.Open()
> End If
> 'Execute the command object
> cmd.ExecuteNonQuery()
> 'Assign the returned value of the output paramater
> TextBox2.Text = cmd.Parameters("@.BizAddress").Value
> 'Close the connection
> cmd.Connection.Close()
> End Sub
> This code allows a textbox (textbox1) to fill an input paramater and
> displays the contents of the returned value from the output paramater in a
> textbox (textbox2). It works fine with s imple select statement. But with
> a
> stored procedure i get this error:
> Cast from type 'DBNull' to type 'String' is not valid.
> Description: An unhandled exception occurred during the execution of the
> current web request. Please review the stack trace for more information
> about
> the error and where it originated in the code.
> Exception Details: System.InvalidCastException: Cast from type 'DBNull' to
> type 'String' is not valid.
> Source Error:
>
> Line 63:
> Line 64: 'Assign the returned value of the output paramater
> Line 65: TextBox2.Text = cmd.Parameters("@.BizAddress").Value
>
> The code for the stored procedure:
> CREATE PROCEDURE GetAddress
> @.BizName varchar,
> @.BizAddress varchar output
> AS
> SELECT @.BizAddress = Address FROM nabilTable WHERE Name = @.BizName
> GO
> Where did i go wrong?
> Thanks for any insights.
> Nab
>
>|||Nab wrote:
> I have a connection (SqlConnection1) established through the GUI.
> Here's the code:
>
You really should post these client-side questions to a more appropriate
newsgroup. Here are some suggestions:
microsoft.public.dotnet.languages.vb.data
microsoft.public.dotnet.framework.adonet
More below:

> Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles Button1.Click
> 'Create and initialise the command object
> Dim cmd As New SqlCommand("GetAddress", SqlConnection1)
> 'State that the type of this command is a stored procedure
> cmd.CommandType = CommandType.StoredProcedure
> 'Create the input paramater
> cmd.Parameters.Add("@.BizName", SqlDbType.VarChar, 50)
> cmd.Parameters("@.BizName").Direction = ParameterDirection.Input
> cmd.Parameters("@.BizName").Value = TextBox1.Text
> 'Create the output paramater
> cmd.Parameters.Add("@.BizAddress", SqlDbType.VarChar, 50)
> cmd.Parameters("@.BizAddress").Direction =
> ParameterDirection.Output
>
<snip>
> 'Execute the command object
> cmd.ExecuteNonQuery()
> 'Assign the returned value of the output paramater
> TextBox2.Text = cmd.Parameters("@.BizAddress").Value
>
<snip>
> Exception Details: System.InvalidCastException: Cast from type
> 'DBNull' to type 'String' is not valid.
>
<snip>
> The code for the stored procedure:
> CREATE PROCEDURE GetAddress
> @.BizName varchar,
> @.BizAddress varchar output
Always, always, ALWAYS set the length of your parameters:
@.BizName varchar(50),
@.BizAddress varchar(50) output
Do not depend on the default values,

> AS
> SELECT @.BizAddress = Address FROM nabilTable WHERE Name = @.BizName
> GO
>
From online help:
If the ParameterDirection is output, and execution of the associated
SqlCommand does not return a value, the SqlParameter contains a null value.
So I would guess that the value of @.BizName is not getting set. Use SQL
Profiler to verify this.
Bob Barrows
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Thanks Dan. Stating the size in the stored procedure did the trick. Cheers.
Nab
"Dan Guzman" wrote:

> A NULL @.BizAddress value will be returned when no data is found and this
> cannot be converted to a .Net string data type. You can check for NULL
> using DbNull.Value:
> If cmd.Parameters("@.BizAddress").Value Is DBNull.Value Then
> MessageBox.Show("BizName not found")
> Else
> TextBox2.Text = cmd.Parameters("@.BizAddress").Value
> End If
> Also, you need specify varchar(50) in your stored procedure parameter
> declaration. The default length is 1.
> CREATE PROCEDURE GetAddress
> @.BizName varchar(50),
> @.BizAddress varchar (50) output
> AS
> SELECT @.BizAddress = Address FROM nabilTable WHERE Name = @.BizName
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Nab" <Nab@.discussions.microsoft.com> wrote in message
> news:96300218-F090-44B3-A5E9-F30FCE80D711@.microsoft.com...
>
>