how to connect with sqlexpress2005 & asp.net for update insert delete codings

I want to know about the how to connect with asp.net 2005 and sqlexpress through codings using parameters

import System.Data.SqlClient;

related update delete insert codings ...

How to know about the make the new connection with different databases sqlserver, oracle mysql...

Hi

Have you had a lookhttp://quickstarts.asp.net/QuickStartv20/default.aspx andhttp://www.asp.net/learn/data-access/.

Work through these samples.. They explain how to access databases using declarative coding (sqldatasource and the likes) and coe behind coding.

Hope that helps.

VJ

How To Connect To Sql Server

Hi

Im trying to develop an information system using CRYSTAL REPORT XI.

Im really having a hard time connecting to a SQLSERVER 2000 database server on my network...

I've tried manipulating the datasource but to no avail... If anyone can help me by showing some example please do...

i would really appreciate your help...

Thanks...!!If you just want to connect to MSSQL from CRXI and add database tables for your report, use OLEDB provider for SQL Server. The connection process is self-explanatory.

How to connect test to production system

I am looking for guidance. I do SQL and other development on 2
computers, and I work remotely from my company's home office. (We use
SQL 2000 and 2005 both.)
My "main" computer has a VPN to the home office. Outside of that, and
before the VPN was set up, I had a TCP connection to ONE of the SQL
server instances at the home office... that specific destination port is
filtered so that only my TCP source IP address can connect to it.
(Now I use the VPN to connect to the home office Exchange server and
shared folders.)
My own "SQL server" computer is connected by Ehternet, ONLY to my
"main" computer. The main computer is obviously multi-homed, connecting
to the SQL computer and also to the outside world through a DSL router
(which is what the VPN connection now uses).
The SQL client on my main computer can see all of the SQL servers at the
home office. When I run the SQL client running on the SQL server
computer, it can only see the server that we had the TCP port set up
for.
The guidance is needed for the following... most of the views and stored
procs that I develop, get the data from the database that I keep a local
copy of, and there is a copy at the home office.
A few of the view and procs need to access data on some of the other SQL
servers at the home office. While my main computer can connect to those
servers, I have trouble setting them up as linked servers from my local
SQL server.
I also would like for the views and procs in my copy of the main
database here to look *exactly* like the views and procs in the database
at the home office... but there are instances of this:
Select * from [server\instance].dbname.dbo.table where...
And server\instance need to be different between the local developement
database and the production database at the home office.
1) Given that I now have a VPN, and the "main" computer is multihomed,
should I be able to link the database on my local SQL server to the
databases at the home office?
2) How do people develop views and stored procs on test systems, when
the call-out to the linked server might need a different server name in
production?
Sorry for such a long question, but I wanted to list all of the info
that might be pertinent.
Thanks.
David Walker
(I asked a specific but related question in the General group, and got
an answer that I couldn't quite do what I thought I could do. This
question is the general case, and I'm loking for advice.)
I would post this to microsoft.public.sqlserver.programming
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"DWalker" <none@.none.com> wrote in message
news:O8XxVY62HHA.1164@.TK2MSFTNGP02.phx.gbl...
>I am looking for guidance. I do SQL and other development on 2
> computers, and I work remotely from my company's home office. (We use
> SQL 2000 and 2005 both.)
> My "main" computer has a VPN to the home office. Outside of that, and
> before the VPN was set up, I had a TCP connection to ONE of the SQL
> server instances at the home office... that specific destination port is
> filtered so that only my TCP source IP address can connect to it.
> (Now I use the VPN to connect to the home office Exchange server and
> shared folders.)
> My own "SQL server" computer is connected by Ehternet, ONLY to my
> "main" computer. The main computer is obviously multi-homed, connecting
> to the SQL computer and also to the outside world through a DSL router
> (which is what the VPN connection now uses).
> The SQL client on my main computer can see all of the SQL servers at the
> home office. When I run the SQL client running on the SQL server
> computer, it can only see the server that we had the TCP port set up
> for.
> The guidance is needed for the following... most of the views and stored
> procs that I develop, get the data from the database that I keep a local
> copy of, and there is a copy at the home office.
> A few of the view and procs need to access data on some of the other SQL
> servers at the home office. While my main computer can connect to those
> servers, I have trouble setting them up as linked servers from my local
> SQL server.
> I also would like for the views and procs in my copy of the main
> database here to look *exactly* like the views and procs in the database
> at the home office... but there are instances of this:
> Select * from [server\instance].dbname.dbo.table where...
> And server\instance need to be different between the local developement
> database and the production database at the home office.
> 1) Given that I now have a VPN, and the "main" computer is multihomed,
> should I be able to link the database on my local SQL server to the
> databases at the home office?
> 2) How do people develop views and stored procs on test systems, when
> the call-out to the linked server might need a different server name in
> production?
> Sorry for such a long question, but I wanted to list all of the info
> that might be pertinent.
> Thanks.
> David Walker
>
> (I asked a specific but related question in the General group, and got
> an answer that I couldn't quite do what I thought I could do. This
> question is the general case, and I'm loking for advice.)
|||"William Vaughn" <billvaNoSPAM@.betav.com> wrote in news:O#W35g72HHA.536
@.TK2MSFTNGP06.phx.gbl:

> I would post this to microsoft.public.sqlserver.programming
>
Thanks. If I can't get things connected the way Charles Wang suggested,
I'll do that.
David

How to connect test to production system

I am looking for guidance. I do SQL and other development on 2
computers, and I work remotely from my company's home office. (We use
SQL 2000 and 2005 both.)
My "main" computer has a VPN to the home office. Outside of that, and
before the VPN was set up, I had a TCP connection to ONE of the SQL
server instances at the home office... that specific destination port is
filtered so that only my TCP source IP address can connect to it.
(Now I use the VPN to connect to the home office Exchange server and
shared folders.)
My own "SQL server" computer is connected by Ehternet, ONLY to my
"main" computer. The main computer is obviously multi-homed, connecting
to the SQL computer and also to the outside world through a DSL router
(which is what the VPN connection now uses).
The SQL client on my main computer can see all of the SQL servers at the
home office. When I run the SQL client running on the SQL server
computer, it can only see the server that we had the TCP port set up
for.
The guidance is needed for the following... most of the views and stored
procs that I develop, get the data from the database that I keep a local
copy of, and there is a copy at the home office.
A few of the view and procs need to access data on some of the other SQL
servers at the home office. While my main computer can connect to those
servers, I have trouble setting them up as linked servers from my local
SQL server.
I also would like for the views and procs in my copy of the main
database here to look *exactly* like the views and procs in the database
at the home office... but there are instances of this:
Select * from [server\instance].dbname.dbo.table where...
And server\instance need to be different between the local developement
database and the production database at the home office.
1) Given that I now have a VPN, and the "main" computer is multihomed,
should I be able to link the database on my local SQL server to the
databases at the home office?
2) How do people develop views and stored procs on test systems, when
the call-out to the linked server might need a different server name in
production?
Sorry for such a long question, but I wanted to list all of the info
that might be pertinent.
Thanks.
David Walker
(I asked a specific but related question in the General group, and got
an answer that I couldn't quite do what I thought I could do. This
question is the general case, and I'm loking for advice.)I would post this to microsoft.public.sqlserver.programming
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"DWalker" <none@.none.com> wrote in message
news:O8XxVY62HHA.1164@.TK2MSFTNGP02.phx.gbl...
>I am looking for guidance. I do SQL and other development on 2
> computers, and I work remotely from my company's home office. (We use
> SQL 2000 and 2005 both.)
> My "main" computer has a VPN to the home office. Outside of that, and
> before the VPN was set up, I had a TCP connection to ONE of the SQL
> server instances at the home office... that specific destination port is
> filtered so that only my TCP source IP address can connect to it.
> (Now I use the VPN to connect to the home office Exchange server and
> shared folders.)
> My own "SQL server" computer is connected by Ehternet, ONLY to my
> "main" computer. The main computer is obviously multi-homed, connecting
> to the SQL computer and also to the outside world through a DSL router
> (which is what the VPN connection now uses).
> The SQL client on my main computer can see all of the SQL servers at the
> home office. When I run the SQL client running on the SQL server
> computer, it can only see the server that we had the TCP port set up
> for.
> The guidance is needed for the following... most of the views and stored
> procs that I develop, get the data from the database that I keep a local
> copy of, and there is a copy at the home office.
> A few of the view and procs need to access data on some of the other SQL
> servers at the home office. While my main computer can connect to those
> servers, I have trouble setting them up as linked servers from my local
> SQL server.
> I also would like for the views and procs in my copy of the main
> database here to look *exactly* like the views and procs in the database
> at the home office... but there are instances of this:
> Select * from [server\instance].dbname.dbo.table where...
> And server\instance need to be different between the local developement
> database and the production database at the home office.
> 1) Given that I now have a VPN, and the "main" computer is multihomed,
> should I be able to link the database on my local SQL server to the
> databases at the home office?
> 2) How do people develop views and stored procs on test systems, when
> the call-out to the linked server might need a different server name in
> production?
> Sorry for such a long question, but I wanted to list all of the info
> that might be pertinent.
> Thanks.
> David Walker
>
> (I asked a specific but related question in the General group, and got
> an answer that I couldn't quite do what I thought I could do. This
> question is the general case, and I'm loking for advice.)|||Hi David,
Since your post has a good many of information, I would like to first
confirm with you if I understand your environment:
1. Your main computer connected to your home office network via VPN;
2. Your local SQL Server computer connected to your main computer via
Ethernet (Local Area Network); It did not directly connect to your home
office network;
3. Your local SQL Server could connect to a remote SQL Server in your home
office network with a specific destination TCP port opened.
Also, I have two questions and appreciate your confirmation since I have
not totally understood your scenario:
1. What is the relationship between your main computer and your local SQL
Server computer?
2. Which computer is your local copy of the main database located on, the
main computer or the local SQL Server computer?
For your two questions,
1) Given that I now have a VPN, and the "main" computer is multihomed,
should I be able to link the database on my local SQL server to the
databases at the home office?
Did you mean databases in different server instances here? If the databases
at the home office were all in the remote SQL Server with specific TCP port
opened, your local SQL Server should be able to connect to the databases in
the remote SQL Server instance.
If you meant that you would like to access the databases in different SQL
Server instances (may be located on different servers) at your home office
via linked server via your local SQL Server, you may try adding your main
server as the gateway of your local SQL Server computer to see if it helps.
Anyway, you need to ensure that the physical connections from your local
SQL Server computer to your remote servers could be established.
If the physical network connections could be established, you can add the
linked servers with SQL authentication (if Windows Authentication could not
be established).
2) How do people develop views and stored procs on test systems, when the
call-out to the linked server might need a different server name in
production?
You may try adding some server aliases on your test systems. These server
aliases have the same names as the server names in production.
For example, your test SQL Server instance name is "Server1\SQL2K", while
your production SQL Server instance name is "ProductServer\SQLServer". You
can first run cliconfg.exe (SQL 2000 client) from command line or SQL
Server Configuration Manager (SQL 2005 client) on your client computer to
configure a server alias "ProductServer\SQLServer" which represents your
test SQL Server instance "Server1\SQL2K" with a specific protocol (TCP/IP
or Named Pipes).
After that, you can run:
Sp_addlinkedserver 'ProductServer\SQLSERVER'
Go
Sp_addlinkedsrvlogin 'ProductServer\SQLSERVER','True'
Go
Then you can run the following statement to query the northwind database in
your local test SQL Server instance "Server1\SQL2K":
SELECT * FROM [ProductServer\SQLServer].northwind.dbo.employees
For configure server alias on a client computer, you may also refer to:
How to connect to a named instance of SQL Server 2005 or SQL Server 2000 by
using the client tools in the earlier version of SQL Server
http://support.microsoft.com/kb/265808/en-us
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Answers inline.
changliw@.online.microsoft.com (Charles Wang[MSFT]) wrote in
news:M55syrX3HHA.4100@.TK2MSFTNGHUB02.phx.gbl:

> Hi David,
> Since your post has a good many of information, I would like to first
> confirm with you if I understand your environment:
> 1. Your main computer connected to your home office network via
> VPN; 2. Your local SQL Server computer connected to your main
> computer via Ethernet (Local Area Network); It did not directly
> connect to your home office network;
> 3. Your local SQL Server could connect to a remote SQL Server in
> your home office network with a specific destination TCP port opened.
> Also, I have two questions and appreciate your confirmation since I
> have not totally understood your scenario:
> 1. What is the relationship between your main computer and your
> local SQL Server computer?
They are both XP pro, connected with an Ethernet crossover cable, in the
same workgroup.

> 2. Which computer is your local copy of the main database located
> on, the main computer or the local SQL Server computer?
The local copy of the main database is on the SQL server computer. My
"main" computer is only a client, not running a SQL server.

> For your two questions,
> 1) Given that I now have a VPN, and the "main" computer is
> multihomed, should I be able to link the database on my local SQL
> server to the databases at the home office?
> Did you mean databases in different server instances here? If the
> databases at the home office were all in the remote SQL Server with
> specific TCP port opened, your local SQL Server should be able to
> connect to the databases in the remote SQL Server instance.
> If you meant that you would like to access the databases in different
> SQL Server instances (may be located on different servers) at your
> home office via linked server via your local SQL Server, you may try
> adding your main server as the gateway of your local SQL Server
> computer to see if it helps. Anyway, you need to ensure that the
> physical connections from your local SQL Server computer to your
> remote servers could be established.
> If the physical network connections could be established, you can add
> the linked servers with SQL authentication (if Windows Authentication
> could not be established).
> 2) How do people develop views and stored procs on test systems,
> when the call-out to the linked server might need a different server
> name in production?
> You may try adding some server aliases on your test systems. These
> server aliases have the same names as the server names in production.
> For example, your test SQL Server instance name is "Server1\SQL2K",
> while your production SQL Server instance name is
> "ProductServer\SQLServer". You can first run cliconfg.exe (SQL 2000
> client) from command line or SQL Server Configuration Manager (SQL
> 2005 client) on your client computer to configure a server alias
> "ProductServer\SQLServer" which represents your test SQL Server
> instance "Server1\SQL2K" with a specific protocol (TCP/IP or Named
> Pipes). After that, you can run:
> Sp_addlinkedserver 'ProductServer\SQLSERVER'
> Go
> Sp_addlinkedsrvlogin 'ProductServer\SQLSERVER','True'
> Go
> Then you can run the following statement to query the northwind
> database in your local test SQL Server instance "Server1\SQL2K":
> SELECT * FROM [ProductServer\SQLServer].northwind.dbo.employees
> For configure server alias on a client computer, you may also refer
> to: How to connect to a named instance of SQL Server 2005 or SQL
> Server 2000 by using the client tools in the earlier version of SQL
> Server http://support.microsoft.com/kb/265808/en-us
> Hope this helps. If you have any other questions or concerns, please
> feel free to let me know.
Thanks for the info. I will look into all of it.
I didn't know that I could make an alias which covers both the server
name AND the instance name. In fact, when I tried it, I got "SQL Server
does not exist or access denied." I'll make sure I have the parameters
right, and try again.
David

> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ========================================
=============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no
> rights. ========================================
==============
>
>
>
>|||"William Vaughn" <billvaNoSPAM@.betav.com> wrote in news:O#W35g72HHA.536
@.TK2MSFTNGP06.phx.gbl:

> I would post this to microsoft.public.sqlserver.programming
>
Thanks. If I can't get things connected the way Charles Wang suggested,
I'll do that.
David|||changliw@.online.microsoft.com (Charles Wang[MSFT]) wrote in
news:M55syrX3HHA.4100@.TK2MSFTNGHUB02.phx.gbl:

> Hi David,
[snip]

> You may try adding some server aliases on your test systems. These
> server aliases have the same names as the server names in production.
> For example, your test SQL Server instance name is "Server1\SQL2K",
> while your production SQL Server instance name is
> "ProductServer\SQLServer". You can first run cliconfg.exe (SQL 2000
> client) from command line or SQL Server Configuration Manager (SQL
> 2005 client) on your client computer to configure a server alias
> "ProductServer\SQLServer" which represents your test SQL Server
> instance "Server1\SQL2K" with a specific protocol (TCP/IP or Named
> Pipes). After that, you can run:
> Sp_addlinkedserver 'ProductServer\SQLSERVER'
> Go
> Sp_addlinkedsrvlogin 'ProductServer\SQLSERVER','True'
> Go
> Then you can run the following statement to query the northwind
> database in your local test SQL Server instance "Server1\SQL2K":
> SELECT * FROM [ProductServer\SQLServer].northwind.dbo.employees
>
Charles,
If I use the sp_addlinkedserver and sp_addlinkedsrvlogin, then I *don't*
need to use cliconfig first. Adding the linked server does the whole
thing. I got it to work this way.
Thanks.
David|||Hi David,
Thanks for your updating and response.
I am glad to hear that you used sp_addlinkedserver and sp_addlinkedsrvlogin
to resolve this issue. Yes, sp_addlinkedserver also has the function to
create an alias for a SQL Server instance. You can specify an alias for the
parameter @.server and specify the real server instance name for the
parameter @.datasrc. Personally I would like to use cliconfg since it allows
me to create an alias with a specific communication protocol. Sometimes it
is very helpful for resolving connection issues, especially for TCP/IP
connection when the SQL Server instance is listenning on a specific or
dynamic TCP port not 1433. That is why I recommended that you used it in my
last reply.
Since this issue has been resolved, now I would like check with you to see
whether you have any other questions or concerns. Please feel free to let
us know if you have.
Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============

how to connect sql server to oracle

ha
i am facing the problem that
i am unable to connect sql server to oracle
i have sqlserver in my system and oracle server is in remote
i want to access that database with using DTS
so that ,mmy memory will bw save
could any one please help for this
please yaar i am waitinf fro urs reply
Configure it as a linked server. To create linked server , open
EM->Security->Linked Server.
vinu
"dev" <dev@.discussions.microsoft.com> wrote in message
news:50B38365-9030-4810-9753-D58C5FAD591E@.microsoft.com...
> ha
> i am facing the problem that
> i am unable to connect sql server to oracle
> i have sqlserver in my system and oracle server is in remote
> i want to access that database with using DTS
> so that ,mmy memory will bw save
> could any one please help for this
>
> please yaar i am waitinf fro urs reply
>
|||hi
vinu
i did that
EM->Security->Linked Server.
but when ever i am trying to acess the table it gives a message that
ERROR 7399:OLEDB provider 'MSDAORA' reported an error
could u please clarify about this
"vinu" wrote:

> Configure it as a linked server. To create linked server , open
> EM->Security->Linked Server.
> vinu
>
> "dev" <dev@.discussions.microsoft.com> wrote in message
> news:50B38365-9030-4810-9753-D58C5FAD591E@.microsoft.com...
>
>
|||This might be helpful
http://support.microsoft.com/kb/280106
vinu
"dev" <dev@.discussions.microsoft.com> wrote in message
news:4A97A14E-04C8-4460-9688-2A4FAD4697FB@.microsoft.com...[vbcol=seagreen]
> hi
> vinu
> i did that
> EM->Security->Linked Server.
> but when ever i am trying to acess the table it gives a message that
> ERROR 7399:OLEDB provider 'MSDAORA' reported an error
> could u please clarify about this
>
> "vinu" wrote:
|||hi vinu
let me try this it will take some time it has some work with registry
fro that it s late
thanks fro giviing this suggestion
if nothing i will get back to you
thanks yaar
"vt" wrote:

> This might be helpful
> http://support.microsoft.com/kb/280106
>
> vinu
>
> "dev" <dev@.discussions.microsoft.com> wrote in message
> news:4A97A14E-04C8-4460-9688-2A4FAD4697FB@.microsoft.com...
>
>
|||Can you connect using the native Oracle tools? net Configuration Assistant
provides a means of testing the connection, and SQL+ will also do the same.
This is the first step and the most often missed step - to check that you
have a valid TNS entry and the firewall is correctly configured.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
sql

How to connect oracle with SQL 2000

Good evening,

I would like to ask help from professionals, I have oracle database as old system and SQL server 2000 as new system ,these databses for accounting department so we would make oracle as publisher server and SQL as subscriber which we be sure the database in the two system is synochornize. but the main problem I can't make oracle as publisher because when I go to SQL server2000, replication, no option for non sql server.

so I would like to help asap.

thank in advance....have you looked at scheduling a DTS package to import data from Oracle to SQL Server (rather then replication)?|||Making Oracle, or any other non-SQL Server database, publisher is not possible with SQL Server replication. It's only possible to make other platforms subscribers (and even that is a bit tricky).

To achive your goal by replication you will have to use Oracles replication solution or a third party replication tool.

How to configure SQL server to use multiple processor

hi,
We have a system with two processors. What settings should we do in sql
server 2000 standard edition to use both the processors. Can anyone suggest?
Thanks
PremHello,
By itself SQL Server will use both processors. There is no change required.
Thanks
Hari
"Prem" <Prem_a20@.hotmail.com> wrote in message
news:uoDh1q8SHHA.4956@.TK2MSFTNGP04.phx.gbl...
> hi,
> We have a system with two processors. What settings should we do in sql
> server 2000 standard edition to use both the processors. Can anyone
> suggest?
> Thanks
> Prem
>
>|||underprocessable

How to configure SQL server to use multiple processor

hi,
We have a system with two processors. What settings should we do in sql
server 2000 standard edition to use both the processors. Can anyone suggest?
Thanks
PremHello,
By itself SQL Server will use both processors. There is no change required.
Thanks
Hari
"Prem" <Prem_a20@.hotmail.com> wrote in message
news:uoDh1q8SHHA.4956@.TK2MSFTNGP04.phx.gbl...
> hi,
> We have a system with two processors. What settings should we do in sql
> server 2000 standard edition to use both the processors. Can anyone
> suggest?
> Thanks
> Prem
>
>

How to configure SQL SERVER in local on windows xp os

Hi,

I need an suggestion how to run sql server in local. In my system sql server was not running in local, but when I connect to our remote data base with it's IP ,username and password, I can able to connect, but not in the local system sql server.I am using windows XP OS. I tried to register a new connectio to my local but i am getting an error the Acdess denied or connection faild.

Plz any one suggest me how to troubleshoot this problem

Regards
SarathyCould you tell some more details, Version of SQL Server, Firewall configured etc.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

How to Config Virtual Directory for HTTP Access to Achieve XML Template Query in SQL Server2005?

Hi,

I have built a system with xml template query using SQL Server2000.

Now ,I want to update it with SQL Server2005.

The problem is how to config the virtual directory?

In SQL 2005 the SQLXML ISAPI virtual directories are no longer being shipped. Instead we have things like SOAP endpoints and ASP.Net that can provide you HTTP access to your data. If these do not work for you, your SQL 2000 virtual directories should work against SQL 2005, and in addition SQLXML 3 SP3 is available as a Web Release and works against SQL 2005:

http://www.microsoft.com/downloads/details.aspx?FamilyID=51d4a154-8e23-47d2-a033-764259cfb53b&DisplayLang=en

How to Compute Actual Space Used In Datafiles

Has anyone discovered the algorithm and the appropriate system
tables/columns to use to compute the actual amount of space used/remaining
within a database datafile?
I'm trying to hit out to the values displayed in the "Taskpad" view in
Enterprise Manager and want to write a routine around those values.Undocumented DBCC command 'DBCC SHOWFILESTATS' is what drives that display.
You can build some really good stuff around that command.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Jack Snow" <mrbrew5510@.hotmail.com> wrote in message
news:HAemc.146252$Gd3.35432608@.news4.srv.hcvlny.cv.net...
> Has anyone discovered the algorithm and the appropriate system
> tables/columns to use to compute the actual amount of space used/remaining
> within a database datafile?
> I'm trying to hit out to the values displayed in the "Taskpad" view in
> Enterprise Manager and want to write a routine around those values.
>|||Have you looked at sp_spaceused ?
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message news:<u#PQovwMEHA.3944@.tk2msftngp13.phx.gbl>...
> Undocumented DBCC command 'DBCC SHOWFILESTATS' is what drives that display.
> You can build some really good stuff around that command.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
>
> "Jack Snow" <mrbrew5510@.hotmail.com> wrote in message
> news:HAemc.146252$Gd3.35432608@.news4.srv.hcvlny.cv.net...
> > Has anyone discovered the algorithm and the appropriate system
> > tables/columns to use to compute the actual amount of space used/remaining
> > within a database datafile?
> > I'm trying to hit out to the values displayed in the "Taskpad" view in
> > Enterprise Manager and want to write a routine around those values.
> >
> >|||Yes. It has one huge glaring weakness. It does not look at space within a
filegroup. Knowing that a database has 70% free space is useless if I have
a filegroup that is full. With DBCCSHOWFILESTATS, I was able to aggregate
data usage within files and filegroups to get a meaningful space used/free
report.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"WangKhar" <Wangkhar@.yahoo.com> wrote in message
news:bb269444.0405060059.20b88b1e@.posting.google.com...
> Have you looked at sp_spaceused ?
>
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:<u#PQovwMEHA.3944@.tk2msftngp13.phx.gbl>...
> > Undocumented DBCC command 'DBCC SHOWFILESTATS' is what drives that
display.
> > You can build some really good stuff around that command.
> >
> > --
> > Geoff N. Hiten
> > Microsoft SQL Server MVP
> > Senior Database Administrator
> > Careerbuilder.com
> >
> >
> >
> > "Jack Snow" <mrbrew5510@.hotmail.com> wrote in message
> > news:HAemc.146252$Gd3.35432608@.news4.srv.hcvlny.cv.net...
> > > Has anyone discovered the algorithm and the appropriate system
> > > tables/columns to use to compute the actual amount of space
used/remaining
> > > within a database datafile?
> > > I'm trying to hit out to the values displayed in the "Taskpad" view in
> > > Enterprise Manager and want to write a routine around those values.
> > >
> > >

How to Code Conditional "Jump to Report"

I have a system summary report where I want to allow them to drill-thru to
the detail system report only if the system's percent available is less than
100%. I tried to use the Fx on the "jump to report" to enter an IIF
statement something like
=IIF(ReportItems!Percent_Avail.Value < 100, "Detail_Report_Name", "") and
various other things but it didnt work. How can I do this ? I want to
essentially turn OFF the action if the percent is 100 and allow them to
drill-thru to the detail if it is less than 100 percent. The FX is there
next to where you select the report to jump to so I should be able to code an
expression from what I read. Thanks!!I have done something similar with Jump to URL:
= iif( mid(Fields!db_uniqueloadid.Value,1,2)="CP" ,Globals!ReportServerUrl &
"?/Inventory/Composite+Info&CompositeID=" & Fields!db_uniqueloadid.Value
,Globals!ReportServerUrl & "?/Inventory/Load+ID&UniqueLoadID=" &
Fields!db_uniqueloadid.Value)
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"MJ Taft" <MJTaft@.discussions.microsoft.com> wrote in message
news:EC316C43-E68F-4523-BC3B-A131BF4F8AED@.microsoft.com...
>I have a system summary report where I want to allow them to drill-thru to
> the detail system report only if the system's percent available is less
> than
> 100%. I tried to use the Fx on the "jump to report" to enter an IIF
> statement something like
> =IIF(ReportItems!Percent_Avail.Value < 100, "Detail_Report_Name", "") and
> various other things but it didnt work. How can I do this ? I want to
> essentially turn OFF the action if the percent is 100 and allow them to
> drill-thru to the detail if it is less than 100 percent. The FX is there
> next to where you select the report to jump to so I should be able to code
> an
> expression from what I read. Thanks!!|||I cant use "jump to url" ... is there a way to do what I am asking? If so ..
I am not sure how to code the empty string for the report. It gives me an
error about the report cant be loaded or something
"Bruce L-C [MVP]" wrote:
> I have done something similar with Jump to URL:
> = iif( mid(Fields!db_uniqueloadid.Value,1,2)="CP" ,Globals!ReportServerUrl &
> "?/Inventory/Composite+Info&CompositeID=" & Fields!db_uniqueloadid.Value
> ,Globals!ReportServerUrl & "?/Inventory/Load+ID&UniqueLoadID=" &
> Fields!db_uniqueloadid.Value)
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "MJ Taft" <MJTaft@.discussions.microsoft.com> wrote in message
> news:EC316C43-E68F-4523-BC3B-A131BF4F8AED@.microsoft.com...
> >I have a system summary report where I want to allow them to drill-thru to
> > the detail system report only if the system's percent available is less
> > than
> > 100%. I tried to use the Fx on the "jump to report" to enter an IIF
> > statement something like
> > =IIF(ReportItems!Percent_Avail.Value < 100, "Detail_Report_Name", "") and
> > various other things but it didnt work. How can I do this ? I want to
> > essentially turn OFF the action if the percent is 100 and allow them to
> > drill-thru to the detail if it is less than 100 percent. The FX is there
> > next to where you select the report to jump to so I should be able to code
> > an
> > expression from what I read. Thanks!!
>
>|||Why can't you use jump to url? I don't understand, anyplace you can use jump
to report you can use jump to URL. Jump to report is a bit handier but both
will work.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"MJ Taft" <MJTaft@.discussions.microsoft.com> wrote in message
news:BA135695-39B6-48C6-89FF-0F9965E69006@.microsoft.com...
>I cant use "jump to url" ... is there a way to do what I am asking? If so
>..
> I am not sure how to code the empty string for the report. It gives me an
> error about the report cant be loaded or something
> "Bruce L-C [MVP]" wrote:
>> I have done something similar with Jump to URL:
>> = iif( mid(Fields!db_uniqueloadid.Value,1,2)="CP"
>> ,Globals!ReportServerUrl &
>> "?/Inventory/Composite+Info&CompositeID=" & Fields!db_uniqueloadid.Value
>> ,Globals!ReportServerUrl & "?/Inventory/Load+ID&UniqueLoadID=" &
>> Fields!db_uniqueloadid.Value)
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "MJ Taft" <MJTaft@.discussions.microsoft.com> wrote in message
>> news:EC316C43-E68F-4523-BC3B-A131BF4F8AED@.microsoft.com...
>> >I have a system summary report where I want to allow them to drill-thru
>> >to
>> > the detail system report only if the system's percent available is less
>> > than
>> > 100%. I tried to use the Fx on the "jump to report" to enter an IIF
>> > statement something like
>> > =IIF(ReportItems!Percent_Avail.Value < 100, "Detail_Report_Name", "")
>> > and
>> > various other things but it didnt work. How can I do this ? I want
>> > to
>> > essentially turn OFF the action if the percent is 100 and allow them to
>> > drill-thru to the detail if it is less than 100 percent. The FX is
>> > there
>> > next to where you select the report to jump to so I should be able to
>> > code
>> > an
>> > expression from what I read. Thanks!!
>>|||how would I code that passing parameters? not sure of the syntax. In
addition ... where would the server name come from? We are having issues
with exposing the url to the end users since it contains a parameter that
would allow them to get somewhere they shouldnt (if they knew how ). Someone
from MS is working on that for my company right now. I saw on the portal
that "jump to report" exposes the parameters the same as "jump to url"
probably. So .. that brings me back to syntax ... how do I code the
parameter "values" ... I know the parameter you code in the url as
¶meter_name= <= but how to code the field that goes in there from
the report you are drilling from?
"Bruce L-C [MVP]" wrote:
> Why can't you use jump to url? I don't understand, anyplace you can use jump
> to report you can use jump to URL. Jump to report is a bit handier but both
> will work.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "MJ Taft" <MJTaft@.discussions.microsoft.com> wrote in message
> news:BA135695-39B6-48C6-89FF-0F9965E69006@.microsoft.com...
> >I cant use "jump to url" ... is there a way to do what I am asking? If so
> >..
> > I am not sure how to code the empty string for the report. It gives me an
> > error about the report cant be loaded or something
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> I have done something similar with Jump to URL:
> >>
> >> = iif( mid(Fields!db_uniqueloadid.Value,1,2)="CP"
> >> ,Globals!ReportServerUrl &
> >> "?/Inventory/Composite+Info&CompositeID=" & Fields!db_uniqueloadid.Value
> >> ,Globals!ReportServerUrl & "?/Inventory/Load+ID&UniqueLoadID=" &
> >> Fields!db_uniqueloadid.Value)
> >>
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "MJ Taft" <MJTaft@.discussions.microsoft.com> wrote in message
> >> news:EC316C43-E68F-4523-BC3B-A131BF4F8AED@.microsoft.com...
> >> >I have a system summary report where I want to allow them to drill-thru
> >> >to
> >> > the detail system report only if the system's percent available is less
> >> > than
> >> > 100%. I tried to use the Fx on the "jump to report" to enter an IIF
> >> > statement something like
> >> > =IIF(ReportItems!Percent_Avail.Value < 100, "Detail_Report_Name", "")
> >> > and
> >> > various other things but it didnt work. How can I do this ? I want
> >> > to
> >> > essentially turn OFF the action if the percent is 100 and allow them to
> >> > drill-thru to the detail if it is less than 100 percent. The FX is
> >> > there
> >> > next to where you select the report to jump to so I should be able to
> >> > code
> >> > an
> >> > expression from what I read. Thanks!!
> >>
> >>
> >>
>
>|||True, from a security viewpoint there is no difference. You can turn off the
parameter area so they can't change it from Report Manager but if someone
wants to figure out and change the URL that can be done. You have to use web
services if you want to totally hide everything from the user.
As far as how to create this. Look at my example, it covers everything.
Including the use of Globals!ReportServerUrl so no server name is
hardcoded. I suggest trying jump to url without being fancy and then add in
your iif statement.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"MJ Taft" <MJTaft@.discussions.microsoft.com> wrote in message
news:51B133E4-55DA-4F15-9ABF-BF688E5CBA0F@.microsoft.com...
> how would I code that passing parameters? not sure of the syntax. In
> addition ... where would the server name come from? We are having issues
> with exposing the url to the end users since it contains a parameter that
> would allow them to get somewhere they shouldnt (if they knew how ).
> Someone
> from MS is working on that for my company right now. I saw on the portal
> that "jump to report" exposes the parameters the same as "jump to url"
> probably. So .. that brings me back to syntax ... how do I code the
> parameter "values" ... I know the parameter you code in the url as
> ¶meter_name= <= but how to code the field that goes in there
> from
> the report you are drilling from?
> "Bruce L-C [MVP]" wrote:
>> Why can't you use jump to url? I don't understand, anyplace you can use
>> jump
>> to report you can use jump to URL. Jump to report is a bit handier but
>> both
>> will work.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "MJ Taft" <MJTaft@.discussions.microsoft.com> wrote in message
>> news:BA135695-39B6-48C6-89FF-0F9965E69006@.microsoft.com...
>> >I cant use "jump to url" ... is there a way to do what I am asking? If
>> >so
>> >..
>> > I am not sure how to code the empty string for the report. It gives me
>> > an
>> > error about the report cant be loaded or something
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> I have done something similar with Jump to URL:
>> >>
>> >> = iif( mid(Fields!db_uniqueloadid.Value,1,2)="CP"
>> >> ,Globals!ReportServerUrl &
>> >> "?/Inventory/Composite+Info&CompositeID=" &
>> >> Fields!db_uniqueloadid.Value
>> >> ,Globals!ReportServerUrl & "?/Inventory/Load+ID&UniqueLoadID=" &
>> >> Fields!db_uniqueloadid.Value)
>> >>
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "MJ Taft" <MJTaft@.discussions.microsoft.com> wrote in message
>> >> news:EC316C43-E68F-4523-BC3B-A131BF4F8AED@.microsoft.com...
>> >> >I have a system summary report where I want to allow them to
>> >> >drill-thru
>> >> >to
>> >> > the detail system report only if the system's percent available is
>> >> > less
>> >> > than
>> >> > 100%. I tried to use the Fx on the "jump to report" to enter an IIF
>> >> > statement something like
>> >> > =IIF(ReportItems!Percent_Avail.Value < 100, "Detail_Report_Name",
>> >> > "")
>> >> > and
>> >> > various other things but it didnt work. How can I do this ? I
>> >> > want
>> >> > to
>> >> > essentially turn OFF the action if the percent is 100 and allow them
>> >> > to
>> >> > drill-thru to the detail if it is less than 100 percent. The FX is
>> >> > there
>> >> > next to where you select the report to jump to so I should be able
>> >> > to
>> >> > code
>> >> > an
>> >> > expression from what I read. Thanks!!
>> >>
>> >>
>> >>
>>|||I have the same problem, Did you ever figure out how to shut off the action?
thanks.
"MJ Taft" wrote:
> I have a system summary report where I want to allow them to drill-thru to
> the detail system report only if the system's percent available is less than
> 100%. I tried to use the Fx on the "jump to report" to enter an IIF
> statement something like
> =IIF(ReportItems!Percent_Avail.Value < 100, "Detail_Report_Name", "") and
> various other things but it didnt work. How can I do this ? I want to
> essentially turn OFF the action if the percent is 100 and allow them to
> drill-thru to the detail if it is less than 100 percent. The FX is there
> next to where you select the report to jump to so I should be able to code an
> expression from what I read. Thanks!!|||I knew the minute I sent this that I'd figure it out.
I had to use the keywork NOTHING.
= IIF(Fields!Failed_rule_Rule_Cd.Value = "",Nothing,"Page.aspx")
"Paula" wrote:
> I have the same problem, Did you ever figure out how to shut off the action?
> thanks.
> "MJ Taft" wrote:
> > I have a system summary report where I want to allow them to drill-thru to
> > the detail system report only if the system's percent available is less than
> > 100%. I tried to use the Fx on the "jump to report" to enter an IIF
> > statement something like
> > =IIF(ReportItems!Percent_Avail.Value < 100, "Detail_Report_Name", "") and
> > various other things but it didnt work. How can I do this ? I want to
> > essentially turn OFF the action if the percent is 100 and allow them to
> > drill-thru to the detail if it is less than 100 percent. The FX is there
> > next to where you select the report to jump to so I should be able to code an
> > expression from what I read. Thanks!!

how to clear up log file in primary database in log shipping ?

our database system is runninig in log shipping mode.
currently, primary database log file get much more bigger,
but I dare not shrink log file in primary database ,
because do this will affect log shipping.
any way reslove this?
regards!Backup Log has Active and Inactive Portions. To Truncate Inactive
portion user the following command in SQL Query Analyser
USE The following Command
BACKUP LOG { database_name | @.database_name_var }
WITH TRUNCATE_ONLY|||That would break the log shipping!
AFAIK, shrinking of log file should be OK in a log shipping scenario...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rex" <r.ghodasara@.gmail.com> wrote in message
news:1143808657.133001.205870@.u72g2000cwu.googlegroups.com...
> Backup Log has Active and Inactive Portions. To Truncate Inactive
> portion user the following command in SQL Query Analyser
> USE The following Command
> BACKUP LOG { database_name | @.database_name_var }
> WITH TRUNCATE_ONLY
>|||thanks ,
but according to your point
as time goes, the logfile will get bigger and bigger,
shrinking logfile is not the best way?
do you think so ?
I meant how to clear up logfile and don't affect log shipping?|||The log file is emptied each time you do backup of the transaction log. So, it will grow until it
has the size needed to accommodate the modifications you do between two log backups, so just let it
be the size it need to be. If you see it grow beyond that, you can perhaps have had a long running
transaction in the log preventing the file to be emptied to a high degree. See
http://www.karaszi.com/SQLServer/info_dont_shrink.asp for more information.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<lovexueer@.gmail.com> wrote in message news:1143814572.962154.266770@.g10g2000cwb.googlegroups.com...
> thanks ,
> but according to your point
> as time goes, the logfile will get bigger and bigger,
> shrinking logfile is not the best way?
> do you think so ?
> I meant how to clear up logfile and don't affect log shipping?
>

how to clear up log file in primary database in log shipping ?

our database system is runninig in log shipping mode.
currently, primary database log file get much more bigger,
but I dare not shrink log file in primary database ,
because do this will affect log shipping.
any way reslove this?
regards!
Backup Log has Active and Inactive Portions. To Truncate Inactive
portion user the following command in SQL Query Analyser
USE The following Command
BACKUP LOG { database_name | @.database_name_var }
WITH TRUNCATE_ONLY
|||That would break the log shipping!
AFAIK, shrinking of log file should be OK in a log shipping scenario...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rex" <r.ghodasara@.gmail.com> wrote in message
news:1143808657.133001.205870@.u72g2000cwu.googlegr oups.com...
> Backup Log has Active and Inactive Portions. To Truncate Inactive
> portion user the following command in SQL Query Analyser
> USE The following Command
> BACKUP LOG { database_name | @.database_name_var }
> WITH TRUNCATE_ONLY
>
|||thanks ,
but according to your point
as time goes, the logfile will get bigger and bigger,
shrinking logfile is not the best way?
do you think so ?
I meant how to clear up logfile and don't affect log shipping?
|||The log file is emptied each time you do backup of the transaction log. So, it will grow until it
has the size needed to accommodate the modifications you do between two log backups, so just let it
be the size it need to be. If you see it grow beyond that, you can perhaps have had a long running
transaction in the log preventing the file to be emptied to a high degree. See
http://www.karaszi.com/SQLServer/info_dont_shrink.asp for more information.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<lovexueer@.gmail.com> wrote in message news:1143814572.962154.266770@.g10g2000cwb.googlegr oups.com...
> thanks ,
> but according to your point
> as time goes, the logfile will get bigger and bigger,
> shrinking logfile is not the best way?
> do you think so ?
> I meant how to clear up logfile and don't affect log shipping?
>

how to clear up log file in primary database in log shipping ?

our database system is runninig in log shipping mode.
currently, primary database log file get much more bigger,
but I dare not shrink log file in primary database ,
because do this will affect log shipping.
any way reslove this?
regards!Backup Log has Active and Inactive Portions. To Truncate Inactive
portion user the following command in SQL Query Analyser
USE The following Command
BACKUP LOG { database_name | @.database_name_var }
WITH TRUNCATE_ONLY|||That would break the log shipping!
AFAIK, shrinking of log file should be OK in a log shipping scenario...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rex" <r.ghodasara@.gmail.com> wrote in message
news:1143808657.133001.205870@.u72g2000cwu.googlegroups.com...
> Backup Log has Active and Inactive Portions. To Truncate Inactive
> portion user the following command in SQL Query Analyser
> USE The following Command
> BACKUP LOG { database_name | @.database_name_var }
> WITH TRUNCATE_ONLY
>|||thanks ,
but according to your point
as time goes, the logfile will get bigger and bigger,
shrinking logfile is not the best way?
do you think so ?
I meant how to clear up logfile and don't affect log shipping?|||The log file is emptied each time you do backup of the transaction log. So,
it will grow until it
has the size needed to accommodate the modifications you do between two log
backups, so just let it
be the size it need to be. If you see it grow beyond that, you can perhaps h
ave had a long running
transaction in the log preventing the file to be emptied to a high degree. S
ee
http://www.karaszi.com/SQLServer/info_dont_shrink.asp for more information.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<lovexueer@.gmail.com> wrote in message news:1143814572.962154.266770@.g10g2000cwb.googlegroup
s.com...
> thanks ,
> but according to your point
> as time goes, the logfile will get bigger and bigger,
> shrinking logfile is not the best way?
> do you think so ?
> I meant how to clear up logfile and don't affect log shipping?
>