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 SQL Data base

Hi,
I want to create SQL Data and default user base using Visual Basic. any body help me to create it
thanks for your advise
byeif you have a script file
you can use shell command or winexec api, e.g.

winexec("osql.exe -S -usa -p -iscriptfile", SW_HIDE)

about osql' parameters, to see sql server online help|||You can execute script string in ADODB.Command object.|||The simplest way is:

Dim cn As ADODB.Connection

Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master;Data Source=myserver"
cn.Execute "create database test"
cn.Close
Set cn = Nothing

You can get more fancy with this. If you will use this more than once I would recommend creating a stored procedure that you would execute. This can also be used if you need to pass parameters like database name, location, size ...

If you are using vb.net take a look at the following ms article:

article (http://support.microsoft.com/default.aspx?scid=kb;en-us;Q305079&ID=kb;en-us;Q305079&SD=MSDN)

Creating SQL Classes ?

I am trying to create a class that will interact with a SQL Server database
I have made up but having a few issues with it.
The basic code is:
Class DatabaseMethods
{
Public:
DatabaseOpen();
DatabaseCreate();
Private:
SqlConnection conn;
};
When compiling (VS C++ 2005) , it won't let me declare 'SqlConnection conn;'
Is there a way around this ?
Cheers
Pete
Posted via NewsDemon.com - Premium Uncensored Newsgroup Service
-->>>>>>http://www.NewsDemon.com<<<<<<--
Unlimited Access, Anonymous Accounts, Uncensored Broadband AccessShould you fully denote the namespace where SQLConnection resides -or add a
'Using System.Data.SQLClient' to the class? How else would this class know
where/how to locate the SQLConnection object?
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"MyMail" <peter.moscatt@.gmail.com> wrote in message
news:44960c8f$0$30230$b9f67a60@.news.newsdemon.com...
>I am trying to create a class that will interact with a SQL Server database
>I have made up but having a few issues with it.
>
> The basic code is:
>
> Class DatabaseMethods
> {
> Public:
> DatabaseOpen();
> DatabaseCreate();
> Private:
> SqlConnection conn;
> };
>
> When compiling (VS C++ 2005) , it won't let me declare 'SqlConnection
> conn;'
>
> Is there a way around this ?
>
> Cheers
> Pete
>
>
> --
> Posted via NewsDemon.com - Premium Uncensored Newsgroup Service
> -->>>>>>http://www.NewsDemon.com<<<<<<--
> Unlimited Access, Anonymous Accounts, Uncensored Broadband Access|||G'Day Arnie,
Thanks for the guide.
I did what you asked (see code below)
// ......... MyHeader.h .........
using namespace System;
using namespace System::Data;
using namespace System::Data::SqlClient;
class DaabaseMethods
{
using System::Data::SqlClient;
public:
void OpenDatabase();
void CreateDatabase();
};
I would have throught because I had already declared
'System::Data::SqlClient' at the top of the header the class should
have seen it.
But anyway, I compiled it and got the following:
error C2886: 'System::Data::SqlClient' : symbol cannot be used in a
member using-declaration
So, what ya reckon '
Pete
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23Vq0ky2kGHA.1324@.TK2MSFTNGP04.phx.gbl...
> Should you fully denote the namespace where SQLConnection
> resides -or add a 'Using System.Data.SQLClient' to the class? How
> else would this class know where/how to locate the SQLConnection
> object?
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "MyMail" <peter.moscatt@.gmail.com> wrote in message
> news:44960c8f$0$30230$b9f67a60@.news.newsdemon.com...
>
Posted via NewsDemon.com - Premium Uncensored Newsgroup Service
-->>>>>>http://www.NewsDemon.com<<<<<<--
Unlimited Access, Anonymous Accounts, Uncensored Broadband Access|||The USING statement has to be outside (and before) the class definition
code -you are correct that the header is where it belongs.
I'm not a C++ person, you need to direct this question to a group that deals
with C++.
Regards,
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"MyMail" <peter.moscatt@.gmail.com> wrote in message
news:44971fa3$0$4190$b9f67a60@.news.newsdemon.com...
> G'Day Arnie,
> Thanks for the guide.
> I did what you asked (see code below)
> // ......... MyHeader.h .........
> using namespace System;
> using namespace System::Data;
> using namespace System::Data::SqlClient;
> class DaabaseMethods
> {
> using System::Data::SqlClient;
> public:
> void OpenDatabase();
> void CreateDatabase();
> };
> I would have throught because I had already declared
> 'System::Data::SqlClient' at the top of the header the class should have
> seen it.
> But anyway, I compiled it and got the following:
> error C2886: 'System::Data::SqlClient' : symbol cannot be used in a member
> using-declaration
>
> So, what ya reckon '
> Pete
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:%23Vq0ky2kGHA.1324@.TK2MSFTNGP04.phx.gbl...
>
> --
> Posted via NewsDemon.com - Premium Uncensored Newsgroup Service
> -->>>>>>http://www.NewsDemon.com<<<<<<--
> Unlimited Access, Anonymous Accounts, Uncensored Broadband Access|||Xref: TK2MSFTNGP01.phx.gbl microsoft.public.sqlserver.programming:610013
No worries Arnie... thanks for the help anyway.
Pete
On Mon, 19 Jun 2006 16:37:01 -0700, "Arnie Rowland" <arnie@.1568.com>
wrote:

>The USING statement has to be outside (and before) the class definition
>code -you are correct that the header is where it belongs.
>I'm not a C++ person, you need to direct this question to a group that deal
s
>with C++.
>Regards,
Posted via NewsDemon.com - Premium Uncensored Newsgroup Service
-->>>>>>http://www.NewsDemon.com<<<<<<--
Unlimited Access, Anonymous Accounts, Uncensored Broadband Access

Creating Reports Using SQL QUERY ANALYZER

I am a student of the University of Phoenix. In My SQL class I was unsuccessful in creating a report using basic SQL commands using the SQL Query Analyzer version 8.00.760.

I am using a version of Microsoft SQL Server 2000 Sold to me by the college. The Disk 1 said SQL Server 2000 Developer Edition. Disk 2 said SQL Server 2000 service pack 3a. The Third Disk Says SQL Server 2000 Reporting Services.

Since I am learning SQL on this platform I wanted to create reports using the SQL Query Analyzer. So how do I create reports using Basic Commands. This is what I have so far.

CREATE TABLE ACCOUNTS
(
Account_Number INT NOT NULL PRIMARY KEY,
Long_Description VarChar(500) NOT NULL,
Short_Description VarChar(500)NOT NULL,
Balance Money NULL,
);

Once I Imported the data from an excel file. I created a view.

CREATE VIEW Account_Report
(Account, Descript, Identifier, Balance) AS
SELECT Account_Number, Long_Description, Short_Description, Balance
FROM Accounts

Then from this view I pulled my report, and the best I could come up with was

SELECT *
FROM Account_Report
ORDER BY Account COMPUTE SUM(Balance)

I want to do more. Such as Create headers, Justify Left right or center, FORMAT Money Column to only have 2 decimal places, Trim the extra space on the right side of the columns, rename the columns, and scroll down 20 lines at a time.

Any help would be appreciated. My class is over so this is realy all just for the furthering of my own knowlegde.

Noctechie

if you are up to making professional reports you have make use of the reporting services

now for your needs, you have to integrate function in your select statement

here some to start with

String Functions

The following table contains samples of string functions. For more information, see String Functions and Using String Functions.

Function

Description

Example

LCASE( )¹,
LOWER( )

Converts strings to lowercase

SELECT UPPER(substring(lname, 1, 1)) +

LOWER(substring (lname, 2, 99))

FROM employee

Displays a last name after the first character is converted to uppercase and the remaining characters to lowercase.

LTRIM( )

Removes leading spaces from a string

SELECT stor_name, LTRIM(stor_address)

FROM stores

Displays an address column after extraneous spaces are removed from the front.

SUBSTRING( )

Extracts one or more characters from a string

SELECT SUBSTRING(phone,1,3)

FROM employee

Displays the first three characters (the area code) of a phone number.

UCASE( )¹,
UPPER( )

Converts strings to uppercase

SELECT * FROM employee

WHERE UPPER(lname) = 'SMITH'

Converts the contents of the lname column to uppercase before comparing them to a specific value (avoids mismatches if the search is case sensitive). For details about case sensitivity in SQL Server, see Query Designer Considerations .

¹ If calling as an ODBC function, use syntax such as: { fn LCASE(text) }.

Date Functions

The following table contains samples of date functions. For more information, see Date and Time Functions.

Function

Description

Example

DATEDIFF( )

Calculates an interval between two dates.

SELECT fname, lname, hire_date

FROM employee

WHERE DATEDIFF(year, hire_date, getdate()) > 5

Locates all employees hired more than five years ago.

DATEPART( )

Returns the specified portion of a date or datetime column, including the day, month, or year.

SELECT DATEPART(year, hire_date)

FROM employee

Displays only the year in which an employee was hired (not the full date).

CURDATE( )¹,
GETDATE( ) or DATE( )

Returns the current date in datetime format. This function is useful as input for many other date functions, such as calculating an interval forward or backward from today.

SELECT order_id

FROM orders

WHERE order_date = GETDATE()

Displays orders placed today.

¹ If calling as an ODBC function, use syntax such as: { fn CURDATE() }.

Mathematical Functions

The following functions are typical of those available in many databases. Refer to Mathematical Functions for more information.

Note You can use the aggregate functions AVG( ), COUNT( ), MAX( ), MIN( ), and SUM( ) to create averages and totals in your report. For details, see Summarizing and Grouping.

Function

Description

Example

ROUND( )

Rounds a number off to the specified number of decimal places

SELECT ROUND(qty * (price * discount), 2)

FROM sales

Displays a total price based on a discount, then rounds the results off to two decimal places.

FLOOR( )

Rounds a number down to the nearest (smallest) whole number

UPDATE titles

SET price = FLOOR(price)

Rounds all prices in the titles table down to the nearest whole number.

CEILING( )

Rounds a number up to the nearest whole number

INSERT INTO archivetitle

SELECT title, CEILING(price)

FROM titles

Copies the title and the price (rounded up to the nearest integer) from the titles table to the archivetitle table.

System Functions

The following functions are typical of those available in many databases. For more information, see System Functions.

Function

Description

Example

DATALENGTH( )

Returns the number of bytes used by the specified expression

SELECT DATALENGTH(au_lname + ', '

+ au_fname)

FROM authors

Lists the number of bytes required for the combination of last and first names.

USER( )¹,
USER_NAME( )

Returns the current user name

SELECT company_name, city, phone

FROM customers

WHERE salesperson = USER_NAME()

Creates a list of customers for the salesperson who runs the query.

¹ If calling as an ODBC function, use syntax such as: { fn USER() }.

Other Functions

The following functions illustrate utility functions available in many databases. For more information, see Functions.

Function

Description

Example

CONVERT( )

Converts data from one data type into another. Useful to format data or to use the contents of a data column as an argument in a function that requires a different data type.

SELECT 'Hired: ' + CONVERT(char (11),

hire_date)

FROM employee

Displays a date with a caption in front of it; the CONVERT( ) function creates a string out of the date so that it can be concatenated with a literal string.

SOUNDEX( )

Returns the Soundex code for the specified expression, which you can use to create "sounds like" searches.

SELECT au_lname, au_fname

FROM authors

WHERE SOUNDEX(au_fname) = 'M240'

Searches for names that sound like "Michael".

STR( )

Converts numeric data into a character string so you can manipulate it with text operators.

SELECT str(job_id) + ' ' +

str(job_lvl)

FROM employee

Displays the job_id and job_lvl columns (both numeric) in a single string.

Creating own Unique Identifier

Hi:
This is pretty basic stuff but I forget how to do this with one update.
I want to create a key for a table. I have two fields:
TableName: MyWorkingTable
PlayerID (int)
Time (DateTime)
My_New_ID (int)
PlayerIDs might be duplicated. But a combination of PlayerID and Time
will always be unique. To begin with, "My_New_ID" values will all be
NULL.
I want to grab an ID value from another table (the max value of all
the IDs from final staging table) and start assigning 'My_New_ID's in
'MyWorkingTable' starting with a number one higher that result. So if
that value is 230, then I start assigning My_New_IDs with 231 and on up
incrementing by one until I run out of records. Then I write all these
records to the final staging table.
How do I do this?
Thanks,
KaydaKayda,
I haven't tested this, it might require adjusting the initial 1 to get
it right.
UPDATE MyWorkingTable
SET My_New_ID = 1 +
(SELECT MAX(OldId) FROM SomeOtherTable) +
(SELECT COUNT(*) FROM MyWorkingTable as T
WHERE T.PlayerID < MyWorkingTable.PlayerID
OR (T.PlayerID = MyWorkingTable.PlayerID
AND T.DateTime < MyWorkingTable.DateTime)
Roy
On 15 Feb 2006 10:40:16 -0800, "Kayda" <blairjee@.gmail.com> wrote:

>Hi:
>This is pretty basic stuff but I forget how to do this with one update.
>I want to create a key for a table. I have two fields:
>TableName: MyWorkingTable
>PlayerID (int)
>Time (DateTime)
>My_New_ID (int)
>PlayerIDs might be duplicated. But a combination of PlayerID and Time
>will always be unique. To begin with, "My_New_ID" values will all be
>NULL.
>I want to grab an ID value from another table (the max value of all
>the IDs from final staging table) and start assigning 'My_New_ID's in
>'MyWorkingTable' starting with a number one higher that result. So if
>that value is 230, then I start assigning My_New_IDs with 231 and on up
>incrementing by one until I run out of records. Then I write all these
>records to the final staging table.
>How do I do this?
>Thanks,
>Kayda

Creating Multiple Charts

Hi,

With Report Builder, I created a basic chart using the chart Wizard..

I am wondering If I can create some 5 charts on a Single Page(Webpage), like a Mini Dashboard.

Can someone please guide me in this regard ?

Thanks in Advance for your help.

Regards,

Sundar

Dear Sundar,

Right Click your chart -> Properties -> Filter tab. You need to explore this tab to achieve what you want.

Here's a step by step article to do so.

HTH,

Suprotim Agarwal

|||

Thanks a lot Suprotim !

It gives exactly the information, I was looking for !!

Cheers,

Sundar

|||

Suprotim.

As per the Article, the author asks us to copy and paste the report , so that we get two Report side-by-side.

He doesnt mention how to copy the report !!!

Any idea how to do this ?

Thanks,

Sundar

|||

Dear Sundar,

Use the standard windows notion

Ctrl+C, Ctrl+V

If you have to display various charts, you will have to shorten the length/width of each and place them side by side. So select one, copy it and paste it besides the other.

HTH,

Suprotim Agarwal

|||

Suprotim,

Thanks again for your Reply.

I tried the windows Ctrl+C and Ctrl+V options.

Seems they are not working :-)

Btw, I am doing this in the 'Design Report' Tab.

Any reason why this is happening ?

Thanks a lot,

Sundar

Creating Multiple Charts

Hi,

With Report Builder, I created a basic chart using the chart Wizard..

I am wondering If I can create some 5 charts on a Single Page(Webpage), like a Mini Dashboard.

Can someone please guide me in this regard ?

Thanks in Advance for your help.

Regards,

Sundar

Dear Sundar,

Right Click your chart -> Properties -> Filter tab. You need to explore this tab to achieve what you want.

Here's a step by step article to do so.

HTH,

Suprotim Agarwal

|||

Thanks a lot Suprotim !

It gives exactly the information, I was looking for !!

Cheers,

Sundar

|||

Suprotim.

As per the Article, the author asks us to copy and paste the report , so that we get two Report side-by-side.

He doesnt mention how to copy the report !!!

Any idea how to do this ?

Thanks,

Sundar

|||

Dear Sundar,

Use the standard windows notion

Ctrl+C, Ctrl+V

If you have to display various charts, you will have to shorten the length/width of each and place them side by side. So select one, copy it and paste it besides the other.

HTH,

Suprotim Agarwal

|||

Suprotim,

Thanks again for your Reply.

I tried the windows Ctrl+C and Ctrl+V options.

Seems they are not working :-)

Btw, I am doing this in the 'Design Report' Tab.

Any reason why this is happening ?

Thanks a lot,

Sundar

Creating Multiple Charts

Hi,

With Report Builder, I created a basic chart using the chart Wizard..

I am wondering If I can create some 5 charts on a Single Page(Webpage), like a Mini Dashboard.

Can someone please guide me in this regard ?

Thanks in Advance for your help.

Regards,

Sundar

Dear Sundar,

Right Click your chart -> Properties -> Filter tab. You need to explore this tab to achieve what you want.

Here's a step by step article to do so.

HTH,

Suprotim Agarwal

|||

Thanks a lot Suprotim !

It gives exactly the information, I was looking for !!

Cheers,

Sundar

|||

Suprotim.

As per the Article, the author asks us to copy and paste the report , so that we get two Report side-by-side.

He doesnt mention how to copy the report !!!

Any idea how to do this ?

Thanks,

Sundar

|||

Dear Sundar,

Use the standard windows notion

Ctrl+C, Ctrl+V

If you have to display various charts, you will have to shorten the length/width of each and place them side by side. So select one, copy it and paste it besides the other.

HTH,

Suprotim Agarwal

|||

Suprotim,

Thanks again for your Reply.

I tried the windows Ctrl+C and Ctrl+V options.

Seems they are not working :-)

Btw, I am doing this in the 'Design Report' Tab.

Any reason why this is happening ?

Thanks a lot,

Sundar

Creating Grand Totals

Ive got a basic tabular report using a dataset built from the following
query, Was wondering how do I create Grand Totals for the 'Total Revenue'and
Ã?nits Sold columns on my report, thanks
Charlie
SELECT tblProduct.Name, Count(tblProduct.Name) AS 'Units Sold',
Sum(tblProduct.VOSValue) AS 'Total Revenue',tblProduct.PCMSCode,
tblProductCategory.Name As 'Product Category'
FROM (tblProduct INNER JOIN tblCustomerProduct ON tblProduct.idProduct = tblCustomerProduct.idProduct) LEFT JOIN tblProductCategory ON
tblProduct.idProductCategory = tblProductCategory.idProductCategory
GROUP BY tblProduct.Name, tblProduct.PCMSCode, tblCustomerProduct.idProduct,
tblCustomerProduct.idProductActivity,
tblCustomerProduct.idProductRemovalReason, tblProduct.NonSaleProduct,
tblCustomerProduct.idProductDesignation, tblProductCategory.Name
HAVING (((tblCustomerProduct.idProductActivity)=1) AND
((tblCustomerProduct.idProductRemovalReason) Is Null) AND
((tblProduct.NonSaleProduct)=0) AND
((tblCustomerProduct.idProductDesignation)=1))
ORDER BY tblProduct.NameCharlie:
Just drag and drop the appropriate fields from the Fields (SSRS2000)
or Datasets pane (SSRS2005) into the table footer. SSRS will add the
SUM aggregate for you.
HTH
toolman
Charlie wrote:
> Ive got a basic tabular report using a dataset built from the following
> query, Was wondering how do I create Grand Totals for the 'Total Revenue'=and
> =DAnits Sold columns on my report, thanks
> Charlie
> SELECT tblProduct.Name, Count(tblProduct.Name) AS 'Units Sold',
> Sum(tblProduct.VOSValue) AS 'Total Revenue',tblProduct.PCMSCode,
> tblProductCategory.Name As 'Product Category'
> FROM (tblProduct INNER JOIN tblCustomerProduct ON tblProduct.idProduct ==3D
> tblCustomerProduct.idProduct) LEFT JOIN tblProductCategory ON
> tblProduct.idProductCategory =3D tblProductCategory.idProductCategory
> GROUP BY tblProduct.Name, tblProduct.PCMSCode, tblCustomerProduct.idProdu=ct,
> tblCustomerProduct.idProductActivity,
> tblCustomerProduct.idProductRemovalReason, tblProduct.NonSaleProduct,
> tblCustomerProduct.idProductDesignation, tblProductCategory.Name
> HAVING (((tblCustomerProduct.idProductActivity)=3D1) AND
> ((tblCustomerProduct.idProductRemovalReason) Is Null) AND
> ((tblProduct.NonSaleProduct)=3D0) AND
> ((tblCustomerProduct.idProductDesignation)=3D1))
> ORDER BY tblProduct.Name

Creating DB schema from a script or stored procedure?

Hi there,

I am a fairly experienced programmer, but new to SQL Server - I understand basic DB theory well enough, but don't have much practical experience with using SQL Server.

I'm working on a project at the moment, where, as part of the spec, users can create 'systems' in the database. For example, in a parts database for a pumping station, there may be 10,000 parts. Rather than have one huge database for, say, 10 pumping stations, we would prefer to have 10 smaller databases, each dedicated to its own system. The schemas would be identical.

I think one approach to this would be have an empty database in SQL server (with the correct tables/schemas/relationships etc) and then copy that within SQL server, with a new name (the system name), probably using a stored procedure.

My question: Is this possible, is there already a stored procedure in SQL Server (2000) to do this, or do I have to write one? Writing a SP to physically create the database from scratch would be a nightmare, I'm hoping there is a simple 'copy_db to new_db' type stored procedure. Maybe there is a program can read a DB and create a script to re-create the DB under a new name?

Any information greatly appreciated.

Mark Wills.In my experience the multi-database approach is never worth the considerable administrative requirements to support it. Unless you anticipate the database size to increase way beyond the ability of a single database server to support it, you should serious rethink your plan and consider adding an additional field or table to your schema to make it scalable.|||I am in agreement with Blindman but if you persist with this scheme you can create your initial database by scripting it out or using the Enterprise Mangeler (whoops) Manager (EM).

Once you have your template defined, in the EM you can right click on your database and go to ALL TASKS and choose Generate SQL Scripts. You can make the EM script out all or some of your objects. then I guess you can wrap all of that code in a SP.

There is another way. If all of your DBs are going to have the same structure and objects on this instance of SQL server I believe you can alter your model db and everytime you issue CREATE DATABASE, you should get all of the objects you created in model. I have never used this second method so I would read up on it first before trying.|||Unless you would like to be able to backup and recover per station separately, otherwise single database is ok. I have database with 1.5 tera bytes, it works ok.|||Unless you would like to be able to backup and recover per station separately, otherwise single database is ok. I have database with 1.5 tera bytes, it works ok.

1.5 TERA BYTES? :eek: Holy cow, thats one hell of an MP3 collection :)

Ok guys, thanks for the information. I'll do it like you say. As I say, whilst I'm vaguely familar with DB theory, SQL Server is a whole new ballgame for me - it's a bit daunting, so I'm sure I'll be back with many questions! Thanks for taking the time to reply.

Mark.