Cretae a view in one DB with a stored procedure from another DB

Hi,

Ik created an application with visuals basic.NET. This has a
connection string to one database, let's say 'A'. In this database a
stored procedure is called which should execute a string (which is
passed by the) VB tool. This string is a CREATE VIEW statement en this
should be executed in another database let's say 'B'.

I tried this in Transact - SQL

EXEC('USE B;' + Query)

An error occurs : CREATE VIEW should be the first in a batched
statement.

Could anyone help me with this one?

Greetz,
Hennie"Hennie de Nooijer" <hdenooijer@.hotmail.com> wrote in message
news:191115aa.0405240403.724292f@.posting.google.co m...
> Hi,
> Ik created an application with visuals basic.NET. This has a
> connection string to one database, let's say 'A'. In this database a
> stored procedure is called which should execute a string (which is
> passed by the) VB tool. This string is a CREATE VIEW statement en this
> should be executed in another database let's say 'B'.
> I tried this in Transact - SQL
> EXEC('USE B;' + Query)
> An error occurs : CREATE VIEW should be the first in a batched
> statement.
> Could anyone help me with this one?
> Greetz,
> Hennie

You need a GO right before the CREATE VIEW statement, and this won't work in
dynamic SQL. In any case, this isn't really a good way to create a database
object - it would probably be a lot easier for your client application to
connect directly to database B. Or if you really want to do it from SQL,
then why not create the stored procedure in database B and call it from
database A?

It might help if you can explain what you're trying to achieve, and someone
may be able to suggest a better solution, as creating objects dynamically
from a user application can create significant problems with security and
maintenance. But since you didn't say what your goal is, you may have a good
reason for doing this.

Simon

creating the subscription via RMO

Hi

I'm creating an anonymous merge HTTPS subscription using RMO and vb.net 2005.

I get the following error on the mergePullSubscription.create call

+ InnerException {"'@.job_login' must be a valid Windows Login in the form : 'MACHINE\Login' or 'DOMAIN\Login'. Please see the documentation for 'sp_addmergepullsubscription_agent'.
Changed database context to 'brucesTest'."} System.Exception
What do I need to do to get this parameter set ?

I'm using the code from BOL to create it...

Thanks
BruceHi, Bruce:

You can specify the agent's properties by do the following:

subscription.SynchronizationAgentProcessSecurity.Login = winLogin;
subscription.SynchronizationAgentProcessSecurity.Password = winPassword;

For a completed reference and code snippet, please refer to this TOP in BOL.

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/repref9/html/97a232ce-7171-4124-
8c30-4a4e7f991183.htm

Thanks
Yunwensql

creating the database tables

Hi,

I need to create a table and a few fields in the SQL database programmatically in asp.net.

I know the tabes name, the server which it resides on, username/password. Would anyone have a small sample of this code?

Thanks

radI meant to say, "I know the database name."|||You might check out this article to get you started:Creating a SQL Server Database Programmatically.|||Thanks|||This is the code that I came up with. When I load the page it tells me that my error is here "Line 22: cmdCreate.ExecuteNonQuery()". What am I doing wrong?

<%@. Import Namespace="System.Data" %>
<%@. Import Namespace="System.Data.SqlClient" %
<Script Runat="Server"
Sub Button_Click( s As Object, e As EventArgs )

Dim conUsers As SqlConnection
Dim strCreate As String
Dim cmdCreate As SqlCommand

conUsers = New SqlConnection( "Server=servername;UID=user;PWD=password;database=databasename" )

strCreate = "CREATE TABLE myTable" + "(Application CHAR(100), Project CHAR(100), EnteredDate CHAR(100), EnteredBy CHAR(100), Descriptions CHAR(100), SNPT CHAR(100), Automation CHAR(100), ContactName CHAR(100), ContactNumber CHAR(100), Priority CHAR(100), Status CHAR(100))"

cmdCreate = New SqlCommand( strCreate, conUsers )
conUsers.Open()
cmdCreate.ExecuteNonQuery()
conUsers.Close()

End Sub

</Script>|||Exactly what is the error message? That will help a great deal.|||I could guestimate that it's probably because you aren't setting the cmdCreate.CommandType, your UID for your server isn't a dbo in that database, you should have a space after myTable (just personal thing, shouldn't generate error), table of that name already exists..

yeah, we need what the error is before we can help you.|||Kragie, I have no idea what most of these things you said mean. I am new at asp.net.

The only error it gives me is, "CREATE TABLE permission denied in database 'databasename'.|||OK, the problem is that the SQL Server user does not have permission to add a table in the database. Give the user permission using Enterprise Manager, or use SQL Script in Query Analyzer.

USE databasename
go

GRANT CREATE TABLE TO username
go|||Thanks for the help guys, although I think the error might be incorrect.

Creating Stored Procedures Through VB.NET

Hello,
I've trying to create some stored procedures through VB.Net by adding an
SQLDataAdapter, and following the wizard.
In the wizard I then choose to "Create Stored Procedures Automatically"
However, for some reason I get the following message :
"There was a problem with the INSERT stored procedure. The stored procedure
was not created."
and subsequently my Insert, Update and Delete queries are not created.
Does anyone know why I am getting this message, and how I can fix it ?
The only thing I can think of that may be causing this is the fact that I
have a computed field in my table (forename and surname concatenated).
This is also backed up by the fact that if I do the same task with just a
few fields, then it goes through fine.
I'd appreciate any help you can offer.
Thanks
Post the code and we'll see what's going on.
Cheers,
Jason Lepack
On Mar 9, 3:11 am, Jonny <J...@.discussions.microsoft.com> wrote:
> Hello,
> I've trying to create some stored procedures through VB.Net by adding an
> SQLDataAdapter, and following the wizard.
> In the wizard I then choose to "Create Stored Procedures Automatically"
> However, for some reason I get the following message :
> "There was a problem with the INSERT stored procedure. The stored procedure
> was not created."
> and subsequently my Insert, Update and Delete queries are not created.
> Does anyone know why I am getting this message, and how I can fix it ?
> The only thing I can think of that may be causing this is the fact that I
> have a computed field in my table (forename and surname concatenated).
> This is also backed up by the fact that if I do the same task with just a
> few fields, then it goes through fine.
> I'd appreciate any help you can offer.
> Thanks

Creating Stored Procedures Through VB.NET

Hello,
I've trying to create some stored procedures through VB.Net by adding an
SQLDataAdapter, and following the wizard.
In the wizard I then choose to "Create Stored Procedures Automatically"
However, for some reason I get the following message :
"There was a problem with the INSERT stored procedure. The stored procedure
was not created."
and subsequently my Insert, Update and Delete queries are not created.
Does anyone know why I am getting this message, and how I can fix it ?
The only thing I can think of that may be causing this is the fact that I
have a computed field in my table (forename and surname concatenated).
This is also backed up by the fact that if I do the same task with just a
few fields, then it goes through fine.
I'd appreciate any help you can offer.
ThanksPost the code and we'll see what's going on.
Cheers,
Jason Lepack
On Mar 9, 3:11 am, Jonny <J...@.discussions.microsoft.com> wrote:
> Hello,
> I've trying to create some stored procedures through VB.Net by adding an
> SQLDataAdapter, and following the wizard.
> In the wizard I then choose to "Create Stored Procedures Automatically"
> However, for some reason I get the following message :
> "There was a problem with the INSERT stored procedure. The stored procedur
e
> was not created."
> and subsequently my Insert, Update and Delete queries are not created.
> Does anyone know why I am getting this message, and how I can fix it ?
> The only thing I can think of that may be causing this is the fact that I
> have a computed field in my table (forename and surname concatenated).
> This is also backed up by the fact that if I do the same task with just a
> few fields, then it goes through fine.
> I'd appreciate any help you can offer.
> Thanks

Creating Stored Procedures Through VB.NET

Hello,
I've trying to create some stored procedures through VB.Net by adding an
SQLDataAdapter, and following the wizard.
In the wizard I then choose to "Create Stored Procedures Automatically"
However, for some reason I get the following message :
"There was a problem with the INSERT stored procedure. The stored procedure
was not created."
and subsequently my Insert, Update and Delete queries are not created.
Does anyone know why I am getting this message, and how I can fix it ?
The only thing I can think of that may be causing this is the fact that I
have a computed field in my table (forename and surname concatenated).
This is also backed up by the fact that if I do the same task with just a
few fields, then it goes through fine.
I'd appreciate any help you can offer.
ThanksPost the code and we'll see what's going on.
Cheers,
Jason Lepack
On Mar 9, 3:11 am, Jonny <J...@.discussions.microsoft.com> wrote:
> Hello,
> I've trying to create some stored procedures through VB.Net by adding an
> SQLDataAdapter, and following the wizard.
> In the wizard I then choose to "Create Stored Procedures Automatically"
> However, for some reason I get the following message :
> "There was a problem with the INSERT stored procedure. The stored procedure
> was not created."
> and subsequently my Insert, Update and Delete queries are not created.
> Does anyone know why I am getting this message, and how I can fix it ?
> The only thing I can think of that may be causing this is the fact that I
> have a computed field in my table (forename and surname concatenated).
> This is also backed up by the fact that if I do the same task with just a
> few fields, then it goes through fine.
> I'd appreciate any help you can offer.
> Thanks

Creating SQL triggers thru managed code

Hi,
I am using SQL Server 2005 and .NET Framework 2.0.
I need to create a trigger using managed code assembly. I did following
steps:
1. Wrote a .NET class that implements the functionality of the trigger.
2. Compiled the class to produce .NET assembly
3. Registered that assembly using CREATE ASSEMBLY statement
4. Created trigger definition using CREATE TRIGGER statement as
following
<snip>
CREATE TRIGGER Email
ON dbo.Users
FOR INSERT
AS EXTERNAL NAMES UsersTrigger.CLRTriggers.EmailAudit
</snip>
where UsersTrigger, CLRTriggers and EmailAudit are the names of
assembly,
class and method respectively.
But when I execute this statement, I receive error as:- "Could not find
Type
'CLRTriggers' in assembly 'UsersTrigger'".
Anybody knows where I might be going wrong?
Thanks,
ManaGot it. Write Create Trigger statement as follows:
CREATE TRIGGER Email
ON dbo.Users
FOR INSERT
AS EXTERNAL NAMES UsersTrigger.[UsersTrigger.CLRTriggers].EmailAudit
Dont know why u need to mention assembly name again with the class name
!
Mana :)

Creating SQL Schema using objects not script?

Hey new in the .NET SQLDMO thing...

Can you in C# programtically create a SQL DB and then create tables, references etc...programmatically without having to use Sql scripting?

What is the root namespace for this anyone?

Thanks ideas on examples much appreciated!No. SQL is a bridge between programming language and Database, so far.|||Does not matter...

I used a combination of XSD and DataSets to come up with a decent enough solution.

Eric|||Yes, you could use XML/XSL to access SQL Server with appropriate setup of SQL Server, but it seems you still need to use a combined SQL script.

Anyway, do you mind to share your sample with us?|||Yep...or use SQLXML3.0 ...

I think you can do that with and OleDb connection objects..create Schema structures in SQL.

I have it working with and AccessDB right now...using T-SQL script source dynamically.

Thanks,

Eric|||I knew and used SQLXML 3.0 and even previous version well in VS 6.0 age, but now it seems the trick has been replaced by Web Service in most scenarios. I am curious of your using XSD associated with DataSet. If with several line code you could demo the sample, would you please show me the code? Otherwise, never mind.|||yeah it is quite large...really...

I only had to do it because the XSD.exe tool barfed on my XSD it was simply too large...So had to do it manually.

But its' simply creating a defintion file for the data structure in an xsd...define tables, rows, columns, types ect...the use the XSD.exe tool and it will generate a strongly typed dataset deriving from your data structure xsd template.

now you can use that strongly typed class as destination source....iterate over your xsd find what you need to define xsd:complexTypes and xsd:elements etc...then use the strongly typed collection ..

myClassTable.Add("xsdElementToBeNamedATable") etc..for type defs column defs etc...

Then you have a complete composite structure of your db in a DataSet....you can create what ever you want from this...out put the xsd again..if you want etc...

Now you can iterate over the DataSet Tables and create your T-SQL script on that structure. Alternatively you can use a straight XmlSchema iteration over the XSD document and then inline your T-SQL script code during the looping over the various types...xsd:attributes, xsd:sequences, xsd:elements etc...

It's kinda hairy at first.

LOL|||It's kinda hairy at first.

Yes, you are right. I believe so. But, what is your reason to do so? Do you try to reach the database through HTTP protocol?|||Customer provided me a schema and nothing else...

Build me a DB...

No ERD nothing just this monster of an XSD. to the tune of like 30 imports and a file size of like 500k...humongous...

Every tool on the market barfed when trying to parse the XSD.

I have 2 Gig on my machine and serious hardware...the problem is that XMLSpyEnterprise is the only tool that I have seen any bright spots with. I wish you could modify the amount of ram that the tool uses...like the java engines allow you to do...it just runs out of memory...the program stack is simply too small.

Eric|||OK, understandable.

Although the XML stuff (including XSD) seems a new and advanced technology, it has never been a normal release as a tool for Microsoft products until the .NET age when the XML technology is still not explicitly used but with the form of Web Service. In fact, Web Service is called XML Web Service, it could let you use XML/SOAP protocol wihtout expert knowledge on it, actually without XML/SOAP knowledge on it.

In your case, you somehow has a legacy product from your customer with it quite troublesome. If this is a small piece or you have no way to control it, you might just keep it and use it. Otherwise, you'd better to upgrade it to Web Service to enjoy the advantage of .NET.

This thread,248757, might help you, too.|||Actually,

They want a DB for all kinds of reasons...I am under NDA so I can't say too much other than it involves InfoPath, Office2003, SharePont Server V2.0, SQLServer and a host of XML technologies...very cool stuff..

LOL|||Web services are fairly trivial...I mean I like then and use them for my own corporate stuff. It's a fantastic technology combine it with the WSDL.exe can you got some seriously cool prodcutvitiy tools for creating strongly typed web serviced classes.

This is different...how is using a web service going to solve my DB problem?

Just curious...

Eric|||You didn't state your DB or other problems clearly, how can I know the way to solve them. I even don't know your structure of the problem, and don't know if your problem need to use either XML or Web Service. Basically, the XML/SOAP technology is used for interplatform/interlanguage communications through HTTP protocol. A lot of people use them just in a not necesary scenatio. But, this might not be your case. You think your job is fantastic, that is fantastic.

Creating reports in .NET 2003 for Reporting Services

I'm totally new to the art of creating reports in .NET for RS. Here's what I
need to accomplish. I need to create a report that captures a monthly total
in one column and the Year-To-Date total in another column. How can I use
sequel statements to populate each column with the correct total? The two
queries that I've created are below, but when used in a stored procedure only
the first one is read (both columns have the same totals)
This one used for populating MONTHLY total
SELECT a.LDGACT, a.ACCTNAME, f.BILLAPPREV AS MCurrPeriod
FROM GetFact f INNER JOIN
D_ACCOUNT a ON f.idACCOUNT = a.idACCOUNT INNER JOIN
D_DATE d ON f.PUBDATE = d.DATE
WHERE (a.LDGACT = '0123') AND (d.YEAR = '2005') AND (d.PERIOD = '01') AND
(f.idACCOUNT = a.idACCOUNT)
GROUP BY a.LDGACT, a.ACCTNAME, f.BILLAPPREV
This one used for populating YEAR-TO-DATE total
SELECT a.LDGACT, a.ACCTNAME, SUM(f.BILLAPPREV) AS MYTD
FROM GetFact f INNER JOIN
D_ACCOUNT a ON f.idACCOUNT = a.idACCOUNT INNER JOIN
D_DATE d ON f.PUBDATE = d.DATE
WHERE (a.LDGACT = '0123') AND (d.YEAR = '2005') AND (d.PERIOD BETWEEN '01'
AND '02') AND (f.idACCOUNT = a.idACCOUNT)
GROUP BY a.LDGACT, a.ACCTNAME, f.BILLAPPREV
THANKS
--
DCountI don't know why you would need to do this in a stored procedure. You
are really doing simple selects. As far as stored procedures are
concerened you can only really return one select from a stored
procedure. There are multi resultset stored procedures but these aren't
handled in SQL Reporting Services.
Probably what you should do is just use the data tab in the report
designer. Copy and paste the two selects into two different
datasets...or you could get away with unioning the two together and
putting them in one dataset...like this
SELECT a.LDGACT, a.ACCTNAME, f.BILLAPPREV AS MCurrPeriod , null as
MYTD
FROM GetFact f INNER JOIN
D_ACCOUNT a ON f.idACCOUNT = a.idACCOUNT INNER JOIN
D_DATE d ON f.PUBDATE = d.DATE
WHERE (a.LDGACT = '0123') AND (d.YEAR = '2005') AND (d.PERIOD ='01') AND
(f.idACCOUNT = a.idACCOUNT)
Union all
SELECT a.LDGACT, a.ACCTNAME, null AS MCurrPeriod, SUM(f.BILLAPPREV)
AS MYTD
FROM GetFact f INNER JOIN
D_ACCOUNT a ON f.idACCOUNT = a.idACCOUNT INNER JOIN
D_DATE d ON f.PUBDATE = d.DATE
WHERE (a.LDGACT = '0123') AND (d.YEAR = '2005') AND (d.PERIOD BETWEEN
'01'
AND '02') AND (f.idACCOUNT = a.idACCOUNT)
GROUP BY a.LDGACT, a.ACCTNAME, f.BILLAPPREV|||Another option is to bring back all months data in a singkle query. Summing
all data for an account will give you your YTD figures. To get the MTD
amount, using RunningValues() to sum only those amounts for the current month.
"DCount17" wrote:
> I'm totally new to the art of creating reports in .NET for RS. Here's what I
> need to accomplish. I need to create a report that captures a monthly total
> in one column and the Year-To-Date total in another column. How can I use
> sequel statements to populate each column with the correct total? The two
> queries that I've created are below, but when used in a stored procedure only
> the first one is read (both columns have the same totals)
> This one used for populating MONTHLY total
> SELECT a.LDGACT, a.ACCTNAME, f.BILLAPPREV AS MCurrPeriod
> FROM GetFact f INNER JOIN
> D_ACCOUNT a ON f.idACCOUNT = a.idACCOUNT INNER JOIN
> D_DATE d ON f.PUBDATE = d.DATE
> WHERE (a.LDGACT = '0123') AND (d.YEAR = '2005') AND (d.PERIOD = '01') AND
> (f.idACCOUNT = a.idACCOUNT)
> GROUP BY a.LDGACT, a.ACCTNAME, f.BILLAPPREV
> This one used for populating YEAR-TO-DATE total
> SELECT a.LDGACT, a.ACCTNAME, SUM(f.BILLAPPREV) AS MYTD
> FROM GetFact f INNER JOIN
> D_ACCOUNT a ON f.idACCOUNT = a.idACCOUNT INNER JOIN
> D_DATE d ON f.PUBDATE = d.DATE
> WHERE (a.LDGACT = '0123') AND (d.YEAR = '2005') AND (d.PERIOD BETWEEN '01'
> AND '02') AND (f.idACCOUNT = a.idACCOUNT)
> GROUP BY a.LDGACT, a.ACCTNAME, f.BILLAPPREV
> THANKS
> --
> DCount|||Thanks Luke, this gives me the figures I'm looking for. However, the figures
are displayed in 2 rows instead of one. Instead of this I need the report to
do the following:
Acct# AcctName Monthly Amt YTD Amt
12 CVS 531.90 1223.99
What the reports does now is:
Acct# AcctName Monthly Amt YTD Amt
12 CVS 531.90 1223.99
12 CVS 1223.99
THANKS AGAIN
"Luke" wrote:
> I don't know why you would need to do this in a stored procedure. You
> are really doing simple selects. As far as stored procedures are
> concerened you can only really return one select from a stored
> procedure. There are multi resultset stored procedures but these aren't
> handled in SQL Reporting Services.
> Probably what you should do is just use the data tab in the report
> designer. Copy and paste the two selects into two different
> datasets...or you could get away with unioning the two together and
> putting them in one dataset...like this
> SELECT a.LDGACT, a.ACCTNAME, f.BILLAPPREV AS MCurrPeriod , null as
> MYTD
> FROM GetFact f INNER JOIN
> D_ACCOUNT a ON f.idACCOUNT = a.idACCOUNT INNER JOIN
> D_DATE d ON f.PUBDATE = d.DATE
> WHERE (a.LDGACT = '0123') AND (d.YEAR = '2005') AND (d.PERIOD => '01') AND
> (f.idACCOUNT = a.idACCOUNT)
> Union all
> SELECT a.LDGACT, a.ACCTNAME, null AS MCurrPeriod, SUM(f.BILLAPPREV)
> AS MYTD
> FROM GetFact f INNER JOIN
> D_ACCOUNT a ON f.idACCOUNT = a.idACCOUNT INNER JOIN
> D_DATE d ON f.PUBDATE = d.DATE
> WHERE (a.LDGACT = '0123') AND (d.YEAR = '2005') AND (d.PERIOD BETWEEN
> '01'
> AND '02') AND (f.idACCOUNT = a.idACCOUNT)
> GROUP BY a.LDGACT, a.ACCTNAME, f.BILLAPPREV
>

Creating Reports

What needs to be installed on a workstation in order to be able to create
reports for reporting services?
I have Visual Studio .NET installed, and having installed the reporting
services client noticed it had appeared as an option in VS.
A colleague installed the reporting services client who didn't have Visual
Studio .NET and doesn't seem to have the ability to create a report.You need VS. I've read here about other software to generate the RDL,
but VS makes it much nicer.

Creating report folder hierarchy in VS.NET

Is it possible to create (or duplicate) the report server Report Manager
folder hierarchy in the VS.NET development IDE? There does not appear to be
an option to add a Folder to the Solution Explorer tree, and if I drag a
folder from the file system into the tree I receive an Access Denied message
when I try to open it.
I would like to be able to create a deployment hierarchy, because now all
reports deploy to the project root on the Report Server, and I have to move
them to the correct folder.
Thanks,
TimFolders are not supported in Report Designer.
It is in our wish list for future version.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tim Dreyling" <TimDreyling@.discussions.microsoft.com> wrote in message
news:3F148B87-78EB-4E61-8D04-BA286EAB13A3@.microsoft.com...
> Is it possible to create (or duplicate) the report server Report Manager
> folder hierarchy in the VS.NET development IDE? There does not appear to
> be
> an option to add a Folder to the Solution Explorer tree, and if I drag a
> folder from the file system into the tree I receive an Access Denied
> message
> when I try to open it.
> I would like to be able to create a deployment hierarchy, because now all
> reports deploy to the project root on the Report Server, and I have to
> move
> them to the correct folder.
> Thanks,
> Tim|||To build a deployment hierarchie you could add several projects to your
solution and enter a different deployment folder for each of the projects.
Hth,
Pieter
"Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
news:%23Yt2JPlrEHA.376@.TK2MSFTNGP14.phx.gbl...
> Folders are not supported in Report Designer.
> It is in our wish list for future version.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Tim Dreyling" <TimDreyling@.discussions.microsoft.com> wrote in message
> news:3F148B87-78EB-4E61-8D04-BA286EAB13A3@.microsoft.com...
>> Is it possible to create (or duplicate) the report server Report Manager
>> folder hierarchy in the VS.NET development IDE? There does not appear to
>> be
>> an option to add a Folder to the Solution Explorer tree, and if I drag a
>> folder from the file system into the tree I receive an Access Denied
>> message
>> when I try to open it.
>> I would like to be able to create a deployment hierarchy, because now all
>> reports deploy to the project root on the Report Server, and I have to
>> move
>> them to the correct folder.
>> Thanks,
>> Tim
>|||Thanks for the answers. I will give it a shot.
"Pieter van Maasdam" wrote:
> To build a deployment hierarchie you could add several projects to your
> solution and enter a different deployment folder for each of the projects.
> Hth,
> Pieter
> "Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
> news:%23Yt2JPlrEHA.376@.TK2MSFTNGP14.phx.gbl...
> > Folders are not supported in Report Designer.
> > It is in our wish list for future version.
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >
> >
> > "Tim Dreyling" <TimDreyling@.discussions.microsoft.com> wrote in message
> > news:3F148B87-78EB-4E61-8D04-BA286EAB13A3@.microsoft.com...
> >> Is it possible to create (or duplicate) the report server Report Manager
> >> folder hierarchy in the VS.NET development IDE? There does not appear to
> >> be
> >> an option to add a Folder to the Solution Explorer tree, and if I drag a
> >> folder from the file system into the tree I receive an Access Denied
> >> message
> >> when I try to open it.
> >>
> >> I would like to be able to create a deployment hierarchy, because now all
> >> reports deploy to the project root on the Report Server, and I have to
> >> move
> >> them to the correct folder.
> >>
> >> Thanks,
> >> Tim
> >
> >
>
>

Creating RDL files at runtime

I created a CustomerReport.rdl from VS.NET. I previewed the report in
the SQLServer Report Manager and it shows the report. that is perfect.
Now what i did is, I opened the CustomerReport.rdl in Notedpad.
Obviously it shows me the XML format of the report design. I copied
all XML content of the report (Ctrl+A/paste) and copied into a new
notepad window and saved the new file as test.rdl.
Now when I open the test.rdl report from Report Manager, I see that
Iam asked to enter the SQLServer authentication (sa/password). Only
after I do the SQLServer authentication, Iam able to view the report
in the Report Manager. Why does it ask me so.
The thing is Iam playing with the rdl because I need to create reports
on the fly (at runtime) in my project requirement. Please share your
experiences if you've done something simialar.
thanks,
Anand SagarHello,
What kind of datasource are you using ? shared or private to the report ?
In Report Designer or Report Manager, connections to data sources are
managed by ReportDesigner or ReportingServices and credentials are stored
securily in Repository for ReportingServices. Credentials are not part of
RDL and you should not find then in Notepad. If you have a shared Datasource
you can configure it with ReportManager and reference it in your RDL, so you
will not have to authenticate again with Test.rdl
HTH
Jerome Berthaud (MCSD)
http://www.winsight.fr
"Anand Sagar" <anandsagar@.gmail.com> wrote in message
news:3f76a771.0408070018.29eb85ae@.posting.google.com...
> I created a CustomerReport.rdl from VS.NET. I previewed the report in
> the SQLServer Report Manager and it shows the report. that is perfect.
> Now what i did is, I opened the CustomerReport.rdl in Notedpad.
> Obviously it shows me the XML format of the report design. I copied
> all XML content of the report (Ctrl+A/paste) and copied into a new
> notepad window and saved the new file as test.rdl.
>
> Now when I open the test.rdl report from Report Manager, I see that
> Iam asked to enter the SQLServer authentication (sa/password). Only
> after I do the SQLServer authentication, Iam able to view the report
> in the Report Manager. Why does it ask me so.
>
> The thing is Iam playing with the rdl because I need to create reports
> on the fly (at runtime) in my project requirement. Please share your
> experiences if you've done something simialar.
> thanks,
> Anand Sagar|||Hi,
You can test it live on internet and change the parameter
ProductFamily=Food in ProductFamily=Food. Here you will find 4 ways to
start a MS Reporting Services Report as enduser:
http://128.171.208.112/ReportServer?%2fFoodmart+Sales&ProductFamily=Food
http://128.171.208.112/ReportServer?%2fFoodmart+Sales&rs%3aFormat=HTML4.0&ProductFamily=Food&rc%3aToolbar=True&rc%3aJavaScript=True&rc%3aLinkTarget=_top&rc%3aArea=Toolbar
http://128.171.208.112/ReportServer?%2fFoodmart+Sales&ProductFamily=Food&rs%3aFormat=HTML4.0&rc%3aToolbar=True&rc%3aJavaScript=True&rc%3aLinkTarget=_top&rc%3aArea=Report
Any other parameter can be used in an action from MS As 2000. For more
info: www.reportportal.com / www.gmsbv.nl
Regards,
Marco
www.gmsbv.nl - your partner for MS Analyses & Reporting Services in
Europe !
"Jerome BERTHAUD" <jerome.berthaud@.winsight.fr> wrote in message news:<uoKmx5IfEHA.3428@.TK2MSFTNGP11.phx.gbl>...
> Hello,
> What kind of datasource are you using ? shared or private to the report ?
> In Report Designer or Report Manager, connections to data sources are
> managed by ReportDesigner or ReportingServices and credentials are stored
> securily in Repository for ReportingServices. Credentials are not part of
> RDL and you should not find then in Notepad. If you have a shared Datasource
> you can configure it with ReportManager and reference it in your RDL, so you
> will not have to authenticate again with Test.rdl
> HTH
> Jerome Berthaud (MCSD)
> http://www.winsight.fr
>
> "Anand Sagar" <anandsagar@.gmail.com> wrote in message
> news:3f76a771.0408070018.29eb85ae@.posting.google.com...
> > I created a CustomerReport.rdl from VS.NET. I previewed the report in
> > the SQLServer Report Manager and it shows the report. that is perfect.
> >
> > Now what i did is, I opened the CustomerReport.rdl in Notedpad.
> > Obviously it shows me the XML format of the report design. I copied
> > all XML content of the report (Ctrl+A/paste) and copied into a new
> > notepad window and saved the new file as test.rdl.
> >
> >
> > Now when I open the test.rdl report from Report Manager, I see that
> > Iam asked to enter the SQLServer authentication (sa/password). Only
> > after I do the SQLServer authentication, Iam able to view the report
> > in the Report Manager. Why does it ask me so.
> >
> >
> > The thing is Iam playing with the rdl because I need to create reports
> > on the fly (at runtime) in my project requirement. Please share your
> > experiences if you've done something simialar.
> >
> > thanks,
> > Anand Sagar|||One additional comment to Jerome's posting: If you use the managed SQL
provider ("SQL") in the data source, you can use IntegratedSecurity for
testing purposes (and therefore no need for credentials):
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<IntegratedSecurity>true</IntegratedSecurity>
<ConnectString>data source=(local); ... </ConnectString>
</ConnectionProperties>
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jerome BERTHAUD" <jerome.berthaud@.winsight.fr> wrote in message
news:uoKmx5IfEHA.3428@.TK2MSFTNGP11.phx.gbl...
> Hello,
> What kind of datasource are you using ? shared or private to the report ?
> In Report Designer or Report Manager, connections to data sources are
> managed by ReportDesigner or ReportingServices and credentials are stored
> securily in Repository for ReportingServices. Credentials are not part of
> RDL and you should not find then in Notepad. If you have a shared
Datasource
> you can configure it with ReportManager and reference it in your RDL, so
you
> will not have to authenticate again with Test.rdl
> HTH
> Jerome Berthaud (MCSD)
> http://www.winsight.fr
>
> "Anand Sagar" <anandsagar@.gmail.com> wrote in message
> news:3f76a771.0408070018.29eb85ae@.posting.google.com...
> > I created a CustomerReport.rdl from VS.NET. I previewed the report in
> > the SQLServer Report Manager and it shows the report. that is perfect.
> >
> > Now what i did is, I opened the CustomerReport.rdl in Notedpad.
> > Obviously it shows me the XML format of the report design. I copied
> > all XML content of the report (Ctrl+A/paste) and copied into a new
> > notepad window and saved the new file as test.rdl.
> >
> >
> > Now when I open the test.rdl report from Report Manager, I see that
> > Iam asked to enter the SQLServer authentication (sa/password). Only
> > after I do the SQLServer authentication, Iam able to view the report
> > in the Report Manager. Why does it ask me so.
> >
> >
> > The thing is Iam playing with the rdl because I need to create reports
> > on the fly (at runtime) in my project requirement. Please share your
> > experiences if you've done something simialar.
> >
> > thanks,
> > Anand Sagar
>

Creating Procedures from file and .net

I am trying to read a SP.sql file where i want to write all the procedures to create them in the database

CREATE PROCEDURE SP_1
AS
BEGIN
DELETE FROM tb1
END

CREATE PROCEDURE SP_2
AS
BEGIN
DELETE FROM tb2
END

if there is only one procedure in the file it works, with > 1 procedure it fails
inserting GO between doesnt work

how is it possible to run a sql file with many stored procedures in it from a server langages ?

thank youI tried with OSQL with GO between the two proc creation scripts , it works fine.|||no it doesnt work for me, from SQL SERVER Management studio of course it works
but reading/executing a file from a server langage doesnt work in that way

OSQL is not a server langage|||I tried with OSQL with GO between the two proc creation scripts , it works fine.

Hariarul1 is right,you should look into BOL first to use OSQL utility.
Check this..

EXEC master..xp_cmdshell 'osql -S Servername -U sa -P -ic:\SQLScript.sql'|||i just want to run from asp.net application an .SQL file to create stored procedure and views
is there any way to have in the same sql file many procedures (as my exemple shows) ?

i am not going to use another tool, if noy possible then i can run them one by one in the code , but will be fine to get all at the same place

thank you|||i just want to run from asp.net application an .SQL file to create stored procedure and views
is there any way to have in the same sql file many procedures (as my exemple shows) ?

i am not going to use another tool, if noy possible then i can run them one by one in the code , but will be fine to get all at the same place

thank you

OSql utility is given along with sql server so you need not have to install it seperately,just execute that above sql statement in your ASP.net application,thats all..you will get what you want...|||ok then i must look how it works

i am creating all the tables from aspnet aplication and i cannot creature procedures , one yes, 2 not !!!

that sound crazy

thanks a lot|||Your query is failing because CREATE PROC must be the first statement in a batch. in osql, the GOs separate batches.

GO is not a keyword in sql however. it's understood only by osql.exe and query analyzer to separate batches (sqlcmd.exe and ssms in 2005 recognize GO as well). The sql server engine itself does not recognize this keyword though, so you can't have GOs in the sql that you send to the server using SqlClient, for example (I assume you are using SqlClient since you are using .net).

If you have SMO installed however, you can also use the ServerConnection class to execute scripts from code that are separated by GOs. Use the ExecuteNonQuery() method and friends. If you use this class, you don't have to launch osql.exe as a separate process, and you don't have to use xp_cmdshell (which you should never use if you can get away with it. using it opens up some pretty big security holes).

See this page for details: http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.management.common.serverconnec tion.executenonquery.aspx

Note that SMO is installed by default if you have the 2005 client tools installed (SSMS). also you can download SMO and install it separately here:

http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en (search for SMO on that page.)

SMO will work against any 2000 or 2005 server.|||thank you jezemine
i run of course all my create procedure one by one from a class and execute command
but i find very practicle to have them outside, not hard coded
i am using a xml file

thank you to every body

creating new users with dbo permissions

I am trying to create a new user for a SQL Server database and use the credentials in an
ASP.NET app.

Problem is dbo permissions are not being applied to the database objects when I set up a
new user (Logins -> New Logins) with SQL Server Authentication, set the default database to
the database I want.

I then get an error message saying that the user has not been granted access to their default
database : DBNAME and therefore will not be able to gain access to their default database.

I'm not sure why this is as I am logged in as sa.

Anyway, when I ignore the error and set-up this new user through the Users section of the
database none of the dbo permissions carry over.

Can anyone help?
Cheers.SQL Server's default authentication is via Windows only and you have to invoke
authentication via SQL Server usernames/passwords by right clicking on the server name, selecting the Security tab and making the change.

Creating new field while app running

Hi
Is it possible to add a field to an sql server 20005 table programmatically
by a vb.net app while the vb.net app has the table being viewed on a vb.net
form?
Thanks
Regards
you can, if you use dataset for example, then your program retrieve the data
then close the connection. so there is no lock when you'll add a field.
also, adding a field is a transaction like any other transaction in the
database, so when the table become available, the field will be added
"John" <John@.nospam.infovis.co.uk> wrote in message
news:ulQbPTHoFHA.1088@.TK2MSFTNGP14.phx.gbl...
> Hi
> Is it possible to add a field to an sql server 20005 table
> programmatically by a vb.net app while the vb.net app has the table being
> viewed on a vb.net form?
> Thanks
> Regards
>
|||John,
You can, however it is probably not a good idea.
You can do that with SQL "Alter" commands and use the Adonet command
(SQLClient.SQLCommand) ExecuteNonQuery
You have crossposted to more newsgroups, did you know that the real
newsgroup for your question is
microsoft.public.dotnet.framework.adonet
I hope this helps,
Cor
|||or maybe
microsoft.public.sqlserver.programming ? ( as there are more ways to
perform this as with ADO.Net )
ofcourse it is always the est in a managed situation to let the DBA perform
these tasks , as changing columns might also add the need of performance
analyzing of the database and take apropriate actions that might affect the
database in a possitive way ( adding deleting indexes , constrains etc etc )
if your program is also responsible for the managing task ( like a user
level deployed MSDE database ) it is a good idea in my opinion to do the
following
perform the wanted tasks on a sql server ,with enterprise manager , optimize
the database, now export all your changes as SQL files
you can now import these files in your deployed sql instances either with a
shell call to the command line tools or just read them in and execute the
statements on the connection object ( ofcourse you need to log in with SA ,
administrator rights to do that , or with a pre defined user that was
granted the apropriate rights on the database to perform these actions )
regards
Michel Posseth
"Cor Ligthert [MVP]" <notmyfirstname@.planet.nl> wrote in message
news:enuiN7JoFHA.2904@.TK2MSFTNGP14.phx.gbl...
> John,
> You can, however it is probably not a good idea.
> You can do that with SQL "Alter" commands and use the Adonet command
> (SQLClient.SQLCommand) ExecuteNonQuery
> You have crossposted to more newsgroups, did you know that the real
> newsgroup for your question is
> microsoft.public.dotnet.framework.adonet
> I hope this helps,
> Cor
>
|||John (John@.nospam.infovis.co.uk) writes:
> Is it possible to add a field to an sql server 20005 table
> programmatically by a vb.net app while the vb.net app has the table
> being viewed on a vb.net form?
Well, depends on what you mean with possible. But since ADO .Net is
disconnected, there are no locks on the table preventing columns to
be added by you or anyone else.
What you will happen to your VB app next time it access the table
I don't know. Since, a table is intended to be a static entity which
changes only on application upgrades, it's not that your dataset is
going to change automatically. There may be methods to add columns,
though.
Anyway, unless this is a temp table you are working with, you are
probably off-track here. Describe you real business problem, and you
may get better approaches to solve that problem.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Creating new field while app running

Hi
Is it possible to add a field to an sql server 20005 table programmatically
by a vb.net app while the vb.net app has the table being viewed on a vb.net
form?
Thanks
Regardsyou can, if you use dataset for example, then your program retrieve the data
then close the connection. so there is no lock when you'll add a field.
also, adding a field is a transaction like any other transaction in the
database, so when the table become available, the field will be added
"John" <John@.nospam.infovis.co.uk> wrote in message
news:ulQbPTHoFHA.1088@.TK2MSFTNGP14.phx.gbl...
> Hi
> Is it possible to add a field to an sql server 20005 table
> programmatically by a vb.net app while the vb.net app has the table being
> viewed on a vb.net form?
> Thanks
> Regards
>|||John,
You can, however it is probably not a good idea.
You can do that with SQL "Alter" commands and use the Adonet command
(SQLClient.SQLCommand) ExecuteNonQuery
You have crossposted to more newsgroups, did you know that the real
newsgroup for your question is
microsoft.public.dotnet.framework.adonet
I hope this helps,
Cor|||or maybe
microsoft.public.sqlserver.programming ' ( as there are more ways to
perform this as with ADO.Net )
ofcourse it is always the est in a managed situation to let the DBA perform
these tasks , as changing columns might also add the need of performance
analyzing of the database and take apropriate actions that might affect the
database in a possitive way ( adding deleting indexes , constrains etc etc )
if your program is also responsible for the managing task ( like a user
level deployed MSDE database ) it is a good idea in my opinion to do the
following
perform the wanted tasks on a sql server ,with enterprise manager , optimize
the database, now export all your changes as SQL files
you can now import these files in your deployed sql instances either with a
shell call to the command line tools or just read them in and execute the
statements on the connection object ( ofcourse you need to log in with SA ,
administrator rights to do that , or with a pre defined user that was
granted the apropriate rights on the database to perform these actions )
regards
Michel Posseth
"Cor Ligthert [MVP]" <notmyfirstname@.planet.nl> wrote in message
news:enuiN7JoFHA.2904@.TK2MSFTNGP14.phx.gbl...
> John,
> You can, however it is probably not a good idea.
> You can do that with SQL "Alter" commands and use the Adonet command
> (SQLClient.SQLCommand) ExecuteNonQuery
> You have crossposted to more newsgroups, did you know that the real
> newsgroup for your question is
> microsoft.public.dotnet.framework.adonet
> I hope this helps,
> Cor
>|||John (John@.nospam.infovis.co.uk) writes:
> Is it possible to add a field to an sql server 20005 table
> programmatically by a vb.net app while the vb.net app has the table
> being viewed on a vb.net form?
Well, depends on what you mean with possible. But since ADO .Net is
disconnected, there are no locks on the table preventing columns to
be added by you or anyone else.
What you will happen to your VB app next time it access the table
I don't know. Since, a table is intended to be a static entity which
changes only on application upgrades, it's not that your dataset is
going to change automatically. There may be methods to add columns,
though.
Anyway, unless this is a temp table you are working with, you are
probably off-track here. Describe you real business problem, and you
may get better approaches to solve that problem.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Creating new field while app running

Hi
Is it possible to add a field to an sql server 20005 table programmatically
by a vb.net app while the vb.net app has the table being viewed on a vb.net
form?
Thanks
Regardsyou can, if you use dataset for example, then your program retrieve the data
then close the connection. so there is no lock when you'll add a field.
also, adding a field is a transaction like any other transaction in the
database, so when the table become available, the field will be added
"John" <John@.nospam.infovis.co.uk> wrote in message
news:ulQbPTHoFHA.1088@.TK2MSFTNGP14.phx.gbl...
> Hi
> Is it possible to add a field to an sql server 20005 table
> programmatically by a vb.net app while the vb.net app has the table being
> viewed on a vb.net form?
> Thanks
> Regards
>|||John,
You can, however it is probably not a good idea.
You can do that with SQL "Alter" commands and use the Adonet command
(SQLClient.SQLCommand) ExecuteNonQuery
You have crossposted to more newsgroups, did you know that the real
newsgroup for your question is
microsoft.public.dotnet.framework.adonet
I hope this helps,
Cor|||or maybe
microsoft.public.sqlserver.programming ' ( as there are more ways to
perform this as with ADO.Net )
ofcourse it is always the est in a managed situation to let the DBA perform
these tasks , as changing columns might also add the need of performance
analyzing of the database and take apropriate actions that might affect the
database in a possitive way ( adding deleting indexes , constrains etc etc )
if your program is also responsible for the managing task ( like a user
level deployed MSDE database ) it is a good idea in my opinion to do the
following
perform the wanted tasks on a sql server ,with enterprise manager , optimize
the database, now export all your changes as SQL files
you can now import these files in your deployed sql instances either with a
shell call to the command line tools or just read them in and execute the
statements on the connection object ( ofcourse you need to log in with SA ,
administrator rights to do that , or with a pre defined user that was
granted the apropriate rights on the database to perform these actions )
regards
Michel Posseth
"Cor Ligthert [MVP]" <notmyfirstname@.planet.nl> wrote in message
news:enuiN7JoFHA.2904@.TK2MSFTNGP14.phx.gbl...
> John,
> You can, however it is probably not a good idea.
> You can do that with SQL "Alter" commands and use the Adonet command
> (SQLClient.SQLCommand) ExecuteNonQuery
> You have crossposted to more newsgroups, did you know that the real
> newsgroup for your question is
> microsoft.public.dotnet.framework.adonet
> I hope this helps,
> Cor
>|||John (John@.nospam.infovis.co.uk) writes:
> Is it possible to add a field to an sql server 20005 table
> programmatically by a vb.net app while the vb.net app has the table
> being viewed on a vb.net form?
Well, depends on what you mean with possible. But since ADO .Net is
disconnected, there are no locks on the table preventing columns to
be added by you or anyone else.
What you will happen to your VB app next time it access the table
I don't know. Since, a table is intended to be a static entity which
changes only on application upgrades, it's not that your dataset is
going to change automatically. There may be methods to add columns,
though.
Anyway, unless this is a temp table you are working with, you are
probably off-track here. Describe you real business problem, and you
may get better approaches to solve that problem.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Creating messages from SQL to the Windows Event Viewer app log

(For MS SQL 2000... Please don't suggest anything using VB or .NET. I need
something either somehow built-in with SQL 2000 or using VBscript.)
Anyone know of a way to create a test message somehow through MS SQL so that
it shows in the Windows application event log?
For example, would creating a job to run a dummy stored procedure create an
entry in the app event log?
(Problem is that when SQL is clustered, it uses a different 'source' name in
the event viewer. The 'source' name can't be different when writing a
simple script from what I can tell. I need to be able to easily create test
messages using the SQL cluster name.)
MarcoMarco Shaw wrote:
> (For MS SQL 2000... Please don't suggest anything using VB or .NET. I ne
ed
> something either somehow built-in with SQL 2000 or using VBscript.)
> Anyone know of a way to create a test message somehow through MS SQL so th
at
> it shows in the Windows application event log?
> For example, would creating a job to run a dummy stored procedure create a
n
> entry in the app event log?
> (Problem is that when SQL is clustered, it uses a different 'source' name
in
> the event viewer. The 'source' name can't be different when writing a
> simple script from what I can tell. I need to be able to easily create te
st
> messages using the SQL cluster name.)
> Marco
>
>
Have you looked at RAISERROR?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||RAISERROR( 'This is a test event message', 10, 1 ) WITH LOG
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Marco Shaw" <marco@.Znbnet.nb.ca> wrote in message news:OAXoOx2pGHA.1600@.TK2MSFTNGP04.phx.gb
l...
> (For MS SQL 2000... Please don't suggest anything using VB or .NET. I ne
ed
> something either somehow built-in with SQL 2000 or using VBscript.)
>
> Anyone know of a way to create a test message somehow through MS SQL so th
at
> it shows in the Windows application event log?
>
> For example, would creating a job to run a dummy stored procedure create a
n
> entry in the app event log?
>
> (Problem is that when SQL is clustered, it uses a different 'source' name
in
> the event viewer. The 'source' name can't be different when writing a
> simple script from what I can tell. I need to be able to easily create te
st
> messages using the SQL cluster name.)
>
> Marco
>
>
>

Creating messages from SQL to the Windows Event Viewer app log

(For MS SQL 2000... Please don't suggest anything using VB or .NET. I need
something either somehow built-in with SQL 2000 or using VBscript.)
Anyone know of a way to create a test message somehow through MS SQL so that
it shows in the Windows application event log?
For example, would creating a job to run a dummy stored procedure create an
entry in the app event log?
(Problem is that when SQL is clustered, it uses a different 'source' name in
the event viewer. The 'source' name can't be different when writing a
simple script from what I can tell. I need to be able to easily create test
messages using the SQL cluster name.)
MarcoMarco Shaw wrote:
> (For MS SQL 2000... Please don't suggest anything using VB or .NET. I need
> something either somehow built-in with SQL 2000 or using VBscript.)
> Anyone know of a way to create a test message somehow through MS SQL so that
> it shows in the Windows application event log?
> For example, would creating a job to run a dummy stored procedure create an
> entry in the app event log?
> (Problem is that when SQL is clustered, it uses a different 'source' name in
> the event viewer. The 'source' name can't be different when writing a
> simple script from what I can tell. I need to be able to easily create test
> messages using the SQL cluster name.)
> Marco
>
>
Have you looked at RAISERROR?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||This is a multi-part message in MIME format.
--=_NextPart_000_06FD_01C6A732.93A36440
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
RAISERROR( 'This is a test event message', 10, 1 ) WITH LOG
-- Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Marco Shaw" <marco@.Znbnet.nb.ca> wrote in message =news:OAXoOx2pGHA.1600@.TK2MSFTNGP04.phx.gbl...
> (For MS SQL 2000... Please don't suggest anything using VB or .NET. =I need
> something either somehow built-in with SQL 2000 or using VBscript.)
> > Anyone know of a way to create a test message somehow through MS SQL =so that
> it shows in the Windows application event log?
> > For example, would creating a job to run a dummy stored procedure =create an
> entry in the app event log?
> > (Problem is that when SQL is clustered, it uses a different 'source' =name in
> the event viewer. The 'source' name can't be different when writing a
> simple script from what I can tell. I need to be able to easily =create test
> messages using the SQL cluster name.)
> > Marco
> > >
--=_NextPart_000_06FD_01C6A732.93A36440
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

RAISERROR( 'This is a test =event message', 10, 1 ) WITH LOG
-- Arnie Rowland"To be =successful, your heart must accompany your knowledge."
"Marco Shaw" =wrote in message news:OAXoOx2pGHA.1600@.TK2MSFTNGP04.phx.gbl...> =(For MS SQL 2000... Please don't suggest anything using VB or .NET. I need> something either somehow built-in with SQL 2000 or using VBscript.)> > Anyone know of a way to create a test =message somehow through MS SQL so that> it shows in the Windows =application event log?> > For example, would creating a job to run a dummy =stored procedure create an> entry in the app event log?> > =(Problem is that when SQL is clustered, it uses a different 'source' =name in> the event viewer. The 'source' name can't be different =when writing a> simple script from what I can tell. I need to be =able to easily create test> messages using the SQL cluster name.)> => Marco> > >

---

--=_NextPart_000_06FD_01C6A732.93A36440--

creating database

I am working on site in asp.net with c# and using database sql server 2000 and managing all the data on server i have created one database on server and in that i have created 15 table. should i continue with the same or create another database for table.

tell me how many tables should i maintain in one database. and what precausion should i take while mainting database to increse the speed of site.and what precausion should i take to avoid serverload

please guide me i dont have an idea.

thanks for spending ur valuable time for me.

I suppose that would depend on the database structure of your application. If your application required more tables i guess you create one in the existing database it self.

I dont think you need to worry much with only 15 tables in the database. Frankly your db can handle many tables still.... :) so chill

I would suggest create a unique db for each application. Again if there are very few tables in each application u can use the same database as well. There are many tweaks in the database than just the no. of tables to make it faster... so i will not consider more tables as a major problem for bad database perfomance.

|||

Sharvari:

I am working on site in asp.net with c# and using database sql server 2000 and managing all the data on server i have created one database on server and in that i have created 15 table. should i continue with the same or create another database for table.

You would generally create a new database for each individual application unless several applications may share the data.

Sharvari:

tell me how many tables should i maintain in one database. and what precausion should i take while mainting database to increse the speed of site.and what precausion should i take to avoid serverload

There is no set limit that defines how many tables you should have. The important thing when it comes to database efficiency is the table design, adequate levels of normalisation, proper indexing, and write good optimised SQL for data retrieval.

|||

hi,

read this link this may be helpful to you.

http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0001029.htm

|||

Sharvari:

i have created one database on server and in that i have created 15 table. should i continue with the same or create another database for table.

You can go on creatng as many tables/stored procedure/functions/views as you want in a database. There is indeed alimit of maximum number of objects that can be created in a database but don't worry you are far...far away from that.

Sharvari:

what precausion should i take while mainting database to increse the speed of site.and what precausion should i take to avoid serverload

There are many techniques for better administration of SQL Server. Just try a google and you'll find so many informative and useful articles.

|||whats the effect on server by using table and whats the effect on server by using store procedure which is more convinient. thanks for spending ur valuable time for me.|||

Do you mean that you will use stored procedure to get the data or use a table to get the data?

|||

Well, I can't exactly understand what you are trying to ask. Anyways, I think you are trying to compare tables and stored procedures. But, tables and stored procedures have different objectives.

Tables are used to store the data in a database.

Stored Procedures are used to modify the data stored in the tables. By modify I mean Insert/Update/Delete the data.

So, basically both of these 2 have different purposes and there should be no comparison between these 2.

|||

Sharvari:

whats the effect on server by using table and whats the effect on server by using store procedure which is more convinient. thanks for spending ur valuable time for me.

As mentioned, tables are where you store the data. Stored procedures provide a mechanism which allows you to encapsulate logic into a unit which can be re-used. Therefore a stored procedure can contain logic on how to select/update/delete data and various other things.