Problem in inserting value in view

Hi There,

i have created one view on an exsisting table, but when i am inserting value in it, it is giving the following error msg:

Derived table 'View Name' is not updatable because a column of the derived table is derived or constant.

Thanx

Is this related to SSIS? Well the error is coming straight from SQL Server itself, and is fairly clear to me.

You cannot update a column that is not really a column. So what columns are being updated and can you honestly say they are unadulterated columns, and have not been derived or manipulated in any way before being returned from the view.?

If you want a specific answer then post the UPDATE statement and a the DDL (CREATE VIEW...) for the view.

Problem in Crystl Report Viewer.CloseView Method

Hello K.Babu

My Self Sanjeev. i want to ask about crystal report viewer control. i am using crystal report 8.5 version. i using crviewer to view the report. by crviewer.viewreport() i view the reoprt now i want to close the view, so i use crviewer.closeview(index) method but it gives error. index parameter not accepting string data type. and also not 1,-1,0 to close the view. i use activeindex of crviewer but not woking. so please help me to solve this problem. i needed it veru much

SanjeevLooks like something is wrong with dealing with viewObject. Did u use AddView method to add ViewerObject. It's not picking the index properly.

THanks|||hello,

i have not used addview method to add view for the viewobject. how to use it? can u please explain it.

Thankx

Sanjeev|||So far in my application I haven't used view.. it's not clear yet whether I need to use view or not ?

I see this particular function :

closeCurrentView
If the canCloseCurrentView property is True, closes the current view. Equivalent to the Close button in the toolbar. A viewClosed and a viewActivated event are fired. The currentViewName property is changed.

Syntax
void closeCurrentView ()

check whether this server our purpose.

Thanks|||Sorry sir,

this will not solve the problem. i have know that whether the canCloseCurrentView() is method of what? 'coz both the ceviewer and crystalreport is not having property,method,function like this.

and i want to close the view.

sanjeev.|||Do u really need view in your application ? What if you don't close the view ?|||Hello Sir,

I want to create 1 DLL file which includs several reports in it. and i will add reference of that file to my VB Project. So i used Crystal 8.5 and Vb 6.0. i create 1 dll but with ne VB form which have the crystal report viewer control shows the Report which i passed through VB. now when i close the Form of viewer. Dll component does not get closed. i.e. the instance of the object is not closed and project goes into HangUp position. if so i want to close the view first so i can set the object to nothing.

So please help me to di it.

Sanjeev

Problem in connecting to the SQL Server

i created a dataset in asp.net and i am trying to view the page in the internet explorer and i am getting the following error

Login failed for user 'EASTERN-DFDRY3R\ASPNET'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'EASTERN-DFDRY3R\ASPNET'.

Source Error:

Line 29: {
Line 30: // Put user code to initialize the page here
Line 31: sqlDataAdapter1.Fill(dataSet11);
Line 32: DataGrid1.DataBind();
Line 33: }

Source File: c:\inetpub\wwwroot\key banking system\webform2.aspx.cs Line: 31

Stack Trace:

[SqlException: Login failed for user 'EASTERN-DFDRY3R\ASPNET'.]
System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) +484
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) +372
System.Data.SqlClient.SqlConnection.Open() +384
System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState) +44
System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +304
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +77
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +38
Key_Banking_system.WebForm2.Page_Load(Object sender, EventArgs e) in c:\inetpub\wwwroot\key banking system\webform2.aspx.cs:31
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +731

please help me with this....and 'EASTERN-DFDRY3R\ASPNET' is a valid user for the database?|||i dont know understand ur question.|||'EASTERN-DFDRY3R\ASPNET' is the the name of '<MACHINE>\ASPNET' account. Sumas is obviously trying to connect to the DataBase using 'Trusted_Connection=True' in his connection string. To do this, he must add the ASPNET account to the DB user list, and give this account the necessary rights.

Open Enterprise mngr and find your Database, right click and choose 'New Database User'. Locate the 'EASTERN-DFDRY3R\ASPNET' account and add the account. Choose permissions and check 'db_owner'. That should do the trick.|||I'm not sure recommending db_owner is good idea, of course I NEVER do that myself <whistle> ;)

Plus if the server is on a different machine, i.e. not EASTERN_DFDRY3R (natty name by the way), then that local user won't be available. So you'll have to change the IIS/ASPNET user.

Problem during the process database

This is the error when i execute the code for the association algorithem. "Errors in the high-level relational engine. The data source view does not contain a definition for the 'receiptid' column in the 'receiptdesc' table or view.".

Dim ds As New RelationalDataSource("miningas", "miningas")

ds.ConnectionString = "Provider=SQLNCLI;Data Source=localhost;" & _

"Initial Catalog=miningas;Integrated Security=SSPI"

db.DataSources.Add(ds)

' Create connection to datasource to extract schema to dataset

Dim dset As New DataSet()

Dim cn As New SqlConnection("Data Source=localhost;" & _

"Initial Catalog=supermarket;Integrated Security=True")

' Create the receiptdesc data adapter

Dim daProduct As New SqlDataAdapter("SELECT * FROM dbo.receipt", cn)

daProduct.FillSchema(dset, SchemaType.Mapped, "receipt")

' Create the receipt data adapter

Dim dareceipt As New SqlDataAdapter("Select * from dbo.receiptdesc", cn)

dareceipt.FillSchema(dset, SchemaType.Mapped, "receiptdesc")

Dim drreceipt_desc As New DataRelation("Newreceipt_desc", _

dset.Tables("receipt").Columns("receiptid"), _

dset.Tables("receiptdesc").Columns("receiptid"))

dset.Relations.Add(drreceipt_desc)

' Create the dsv, add the dataset, and add to the database

Dim dsv As New DataSourceView("miningas", "miningas")

dsv.DataSourceID = "miningas"

dsv.Schema = dset.Clone()

db.DataSourceViews.Add(dsv)

' Update the database to create the objects on the server.

db.Update(UpdateOptions.ExpandFull)

End Sub

It seems like your column is not there after you create dsv. Can you double check? Otherwise, I have the sample code below to create dsv. Please check the difference.

RelationalDataSourceView dsv = new RelationalDataSourceView("SampleDSV", "SampleDSV");

OleDbConnection connection = new OleDbConnection("Provider=SQLOLEDB.1;Initial Catalog=Northwind;Data Source=localhost;Integrated Security=SSPI;Persist Security Info=False");

try

{

connection.Open();

//********** Fill Customers table in schema ************

OleDbDataAdapter adapter1 = new OleDbDataAdapter ("Select * from [dbo].[Customers] where 1=0", connection);

DataTable[] dataTables = adapter1.FillSchema(dsv.Schema, SchemaType.Mapped, "Customers");

if (dataTables.Length > 0)

{

DataTable dataTable = dataTables[0];

dataTable.ExtendedProperties.Add( “TableType”, “Table”);

dataTable.ExtendedProperties.Add( “DbSchemaName”, "dbo");

dataTable.ExtendedProperties.Add( “DbTableName”, "Customers");

dataTable.ExtendedProperties.Add( “FriendlyName”, "Customers");

}

//********** Fill Orders table in schema ****************

OleDbDataAdapter adapter2 = new OleDbDataAdapter ("Select * from [dbo].[Orders] where 1=0", connection);

dataTables = adapter2.FillSchema(dsv.Schema, SchemaType.Mapped, "Orders");

if (dataTables.Length > 0)

{

DataTable dataTable = dataTables[0];

dataTable.ExtendedProperties.Add( “TableType”, “Table”);

dataTable.ExtendedProperties.Add( “DbSchemaName”, "dbo");

dataTable.ExtendedProperties.Add( “DbTableName”, "Orders");

dataTable.ExtendedProperties.Add( “FriendlyName”, "Orders");

}

//********** Create relationship ****************

DataColumn parentColumn = dsv.Schema.Tables["Customers"].Columns["CustomerID"];

DataColumn childColumn = dsv.Schema.Tables["Orders"].Columns["CustomerID"];

dsv.Schema.Relations.Add("FK_Orders_Customers", parentColumn, childColumn, true);

}

finally

{

connection.Close();

}

|||Sorry, but the code i still cannot solve the problem. Can u able to give more support on this section. I very urgent need it. Thx|||

Couple things I notice in your code. the connection string in datasource is different from the connection string you get schema. Also, you don't need to clone the dataset before setting to the dsv.schema. You can just set it directly.

I used your code on new AS database and I have no problem. However, I used same connection string (at least same initial catalog). I am not sure if that makes difference.

BTW, it is better to use my code because you do need to set some extended properties.

Problem during the process database

This is the error when i execute the code for the association algorithem. "Errors in the high-level relational engine. The data source view does not contain a definition for the 'receiptid' column in the 'receiptdesc' table or view.".

Dim ds As New RelationalDataSource("miningas", "miningas")

ds.ConnectionString = "Provider=SQLNCLI;Data Source=localhost;" & _

"Initial Catalog=miningas;Integrated Security=SSPI"

db.DataSources.Add(ds)

' Create connection to datasource to extract schema to dataset

Dim dset As New DataSet()

Dim cn As New SqlConnection("Data Source=localhost;" & _

"Initial Catalog=supermarket;Integrated Security=True")

' Create the receiptdesc data adapter

Dim daProduct As New SqlDataAdapter("SELECT * FROM dbo.receipt", cn)

daProduct.FillSchema(dset, SchemaType.Mapped, "receipt")

' Create the receipt data adapter

Dim dareceipt As New SqlDataAdapter("Select * from dbo.receiptdesc", cn)

dareceipt.FillSchema(dset, SchemaType.Mapped, "receiptdesc")

Dim drreceipt_desc As New DataRelation("Newreceipt_desc", _

dset.Tables("receipt").Columns("receiptid"), _

dset.Tables("receiptdesc").Columns("receiptid"))

dset.Relations.Add(drreceipt_desc)

' Create the dsv, add the dataset, and add to the database

Dim dsv As New DataSourceView("miningas", "miningas")

dsv.DataSourceID = "miningas"

dsv.Schema = dset.Clone()

db.DataSourceViews.Add(dsv)

' Update the database to create the objects on the server.

db.Update(UpdateOptions.ExpandFull)

End Sub

It seems like your column is not there after you create dsv. Can you double check? Otherwise, I have the sample code below to create dsv. Please check the difference.

RelationalDataSourceView dsv = new RelationalDataSourceView("SampleDSV", "SampleDSV");

OleDbConnection connection = new OleDbConnection("Provider=SQLOLEDB.1;Initial Catalog=Northwind;Data Source=localhost;Integrated Security=SSPI;Persist Security Info=False");

try

{

connection.Open();

//********** Fill Customers table in schema ************

OleDbDataAdapter adapter1 = new OleDbDataAdapter ("Select * from [dbo].[Customers] where 1=0", connection);

DataTable[] dataTables = adapter1.FillSchema(dsv.Schema, SchemaType.Mapped, "Customers");

if (dataTables.Length > 0)

{

DataTable dataTable = dataTables[0];

dataTable.ExtendedProperties.Add( “TableType”, “Table”);

dataTable.ExtendedProperties.Add( “DbSchemaName”, "dbo");

dataTable.ExtendedProperties.Add( “DbTableName”, "Customers");

dataTable.ExtendedProperties.Add( “FriendlyName”, "Customers");

}

//********** Fill Orders table in schema ****************

OleDbDataAdapter adapter2 = new OleDbDataAdapter ("Select * from [dbo].[Orders] where 1=0", connection);

dataTables = adapter2.FillSchema(dsv.Schema, SchemaType.Mapped, "Orders");

if (dataTables.Length > 0)

{

DataTable dataTable = dataTables[0];

dataTable.ExtendedProperties.Add( “TableType”, “Table”);

dataTable.ExtendedProperties.Add( “DbSchemaName”, "dbo");

dataTable.ExtendedProperties.Add( “DbTableName”, "Orders");

dataTable.ExtendedProperties.Add( “FriendlyName”, "Orders");

}

//********** Create relationship ****************

DataColumn parentColumn = dsv.Schema.Tables["Customers"].Columns["CustomerID"];

DataColumn childColumn = dsv.Schema.Tables["Orders"].Columns["CustomerID"];

dsv.Schema.Relations.Add("FK_Orders_Customers", parentColumn, childColumn, true);

}

finally

{

connection.Close();

}

|||Sorry, but the code i still cannot solve the problem. Can u able to give more support on this section. I very urgent need it. Thx|||

Couple things I notice in your code. the connection string in datasource is different from the connection string you get schema. Also, you don't need to clone the dataset before setting to the dsv.schema. You can just set it directly.

I used your code on new AS database and I have no problem. However, I used same connection string (at least same initial catalog). I am not sure if that makes difference.

BTW, it is better to use my code because you do need to set some extended properties.

problem creation, script with create view

I created a script like this :
use tk_main
GO
if exists (select table_name from information_schema.views where table_name
= 'V_08701')
drop view V_08701
GO
CREATE VIEW V_08701 (D_ATE,NO_ENVOI,ARRIVEE,TOT_COLIS) AS SELECT
D_ATE,NO_ENVOI,ARRIVEE,SUM(NB_COLIS) FROM POSBAR_L GROUP BY
D_ATE,NO_ENVOI,ARRIVEE
GO
grant all on V_08701 to public
GO
if exists (select table_name from information_schema.views where table_name
= 'V95001') drop view V_95001
GO
CREATE VIEW V_95001 (NO_FAC,NO_ENVOI,ARRIVEE,NOMBRE) AS SELECT
NO_FAC,NO_ENVOI_TK,ARRIVEE,COUNT(NO_ENVO
I_TK) FROM FACTURE_IMP_D GROUP BY
NO_FAC,NO_ENVOI_TK,ARRIVEE
GO
if exists (select table_name from information_schema.views where table_name
= 'V_08601') drop view V_08601
GO
CREATE VIEW V_08601
(DTE,TRANSPORTEUR,ARRIVEE,NO_ENVOI,OPERA
TION_C_D,ETAT_ARRIVEE,ETAT_POSBAR,PO
IDS,TYP_SCANNAGE) AS SELECT ARRIVEE.DTE_ARR_DEP, ARRIVEE.TRANSPORTEUR,
ARRIVEE.ARRIVEE,POSBAR_E.NO_ENVOI,
ARRIVEE.OPERATION_C_D,ARRIVEE.ETAT_ARRIVEE, POSBAR_E.ETAT,
POSBAR_E.POIDS,POSBAR_E.TYP_SCANNAGE FROM ARRIVEE, POSBAR_E WHERE
ARRIVEE.ARRIVEE = POSBAR_E.ARRIVEE
GO
but the analyser doesn't like this script.
Can someboady help me out.
Thanks in advance
RalfWhat error messages do you get?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Ralf Meuser" <rmeuser@.free.fr> wrote in message
news:40618606$0$7376$626a14ce@.news.free.fr...
> I created a script like this :
> use tk_main
> GO
> if exists (select table_name from information_schema.views where
table_name
> = 'V_08701')
> drop view V_08701
> GO
> CREATE VIEW V_08701 (D_ATE,NO_ENVOI,ARRIVEE,TOT_COLIS) AS SELECT
> D_ATE,NO_ENVOI,ARRIVEE,SUM(NB_COLIS) FROM POSBAR_L GROUP BY
> D_ATE,NO_ENVOI,ARRIVEE
> GO
> grant all on V_08701 to public
> GO
> if exists (select table_name from information_schema.views where
table_name
> = 'V95001') drop view V_95001
> GO
> CREATE VIEW V_95001 (NO_FAC,NO_ENVOI,ARRIVEE,NOMBRE) AS SELECT
> NO_FAC,NO_ENVOI_TK,ARRIVEE,COUNT(NO_ENVO
I_TK) FROM FACTURE_IMP_D GROUP BY
> NO_FAC,NO_ENVOI_TK,ARRIVEE
> GO
> if exists (select table_name from information_schema.views where
table_name
> = 'V_08601') drop view V_08601
> GO
> CREATE VIEW V_08601
>
(DTE,TRANSPORTEUR,ARRIVEE,NO_ENVOI,OPERA
TION_C_D,ETAT_ARRIVEE,ETAT_POSBAR,PO[col
or=darkred]
> IDS,TYP_SCANNAGE) AS SELECT ARRIVEE.DTE_ARR_DEP, ARRIVEE.TRANSPORTEUR,
> ARRIVEE.ARRIVEE,POSBAR_E.NO_ENVOI,
> ARRIVEE.OPERATION_C_D,ARRIVEE.ETAT_ARRIVEE, POSBAR_E.ETAT,
> POSBAR_E.POIDS,POSBAR_E.TYP_SCANNAGE FROM ARRIVEE, POSBAR_E WHERE
> ARRIVEE.ARRIVEE = POSBAR_E.ARRIVEE
> GO
> --
> but the analyser doesn't like this script.
> Can someboady help me out.
> Thanks in advance
> Ralf
>
>[/color]|||Sorry I forgot to sedn the error :
Serveur : Msg 170, Niveau 15, tat 1, Procdure V_08701, Ligne 2
Ligne 2 : syntaxe incorrecte vers 'GO'.
Serveur : Msg 170, Niveau 15, tat 1, Ligne 1
Ligne 1 : syntaxe incorrecte vers 'GO'.
Serveur : Msg 111, Niveau 15, tat 1, Ligne 2
'CREATE VIEW' doit tre la premire instruction d'un lot de requtes.
Serveur : Msg 170, Niveau 15, tat 1, Ligne 3
Ligne 3 : syntaxe incorrecte vers 'GO'.
Serveur : Msg 170, Niveau 15, tat 1, Ligne 5
Ligne 5 : syntaxe incorrecte vers 'GO'.
Serveur : Msg 111, Niveau 15, tat 1, Ligne 6
'CREATE VIEW' doit tre la premire instruction d'un lot de requtes.
Serveur : Msg 170, Niveau 15, tat 1, Ligne 7
Ligne 7 : syntaxe incorrecte vers 'GO'.
Serveur : Msg 170, Niveau 15, tat 1, Ligne 9
Ligne 9 : syntaxe incorrecte vers 'GO'.
Serveur : Msg 111, Niveau 15, tat 1, Ligne 10
'CREATE VIEW' doit tre la premire instruction d'un lot de requtes.
Serveur : Msg 170, Niveau 15, tat 1, Ligne 11
Ligne 11 : syntaxe incorrecte vers 'GO'.
Serveur : Msg 170, Niveau 15, tat 1, Ligne 13
Ligne 13 : syntaxe incorrecte vers 'GO'.
Serveur : Msg 111, Niveau 15, tat 1, Ligne 14
'CREATE VIEW' doit tre la premire instruction d'un lot de requtes.
Ralf
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> a crit
dans le message de news:em9NhSaEEHA.3980@.TK2MSFTNGP09.phx.gbl...
> What error messages do you get?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Ralf Meuser" <rmeuser@.free.fr> wrote in message
> news:40618606$0$7376$626a14ce@.news.free.fr...
> table_name
> table_name
BY
> table_name
>
(DTE,TRANSPORTEUR,ARRIVEE,NO_ENVOI,OPERA
TION_C_D,ETAT_ARRIVEE,ETAT_POSBAR,PO[col
or=darkred]
>|||Since this is an English speaking newsgroup, it would be helpful if you woul
d translate the French messages
instead of letting us do that.
I don't see a problem with this, unless you actually have a line-break in th
e middle of a column name in your
code as well (I assume it is inserted by your newsreader).
Perhaps someone has changed the batch separator (from GO to something else)
in Query Analyzer?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Ralf Meuser" <rmeuser@.free.fr> wrote in message news:406197b3$0$309$626a14ce@.news.free.fr.
.
> Sorry I forgot to sedn the error :
> Serveur : Msg 170, Niveau 15, tat 1, Procdure V_08701, Ligne 2
> Ligne 2 : syntaxe incorrecte vers 'GO'.
> Serveur : Msg 170, Niveau 15, tat 1, Ligne 1
> Ligne 1 : syntaxe incorrecte vers 'GO'.
> Serveur : Msg 111, Niveau 15, tat 1, Ligne 2
> 'CREATE VIEW' doit tre la premire instruction d'un lot de requtes.
> Serveur : Msg 170, Niveau 15, tat 1, Ligne 3
> Ligne 3 : syntaxe incorrecte vers 'GO'.
> Serveur : Msg 170, Niveau 15, tat 1, Ligne 5
> Ligne 5 : syntaxe incorrecte vers 'GO'.
> Serveur : Msg 111, Niveau 15, tat 1, Ligne 6
> 'CREATE VIEW' doit tre la premire instruction d'un lot de requtes.
> Serveur : Msg 170, Niveau 15, tat 1, Ligne 7
> Ligne 7 : syntaxe incorrecte vers 'GO'.
> Serveur : Msg 170, Niveau 15, tat 1, Ligne 9
> Ligne 9 : syntaxe incorrecte vers 'GO'.
> Serveur : Msg 111, Niveau 15, tat 1, Ligne 10
> 'CREATE VIEW' doit tre la premire instruction d'un lot de requtes.
> Serveur : Msg 170, Niveau 15, tat 1, Ligne 11
> Ligne 11 : syntaxe incorrecte vers 'GO'.
> Serveur : Msg 170, Niveau 15, tat 1, Ligne 13
> Ligne 13 : syntaxe incorrecte vers 'GO'.
> Serveur : Msg 111, Niveau 15, tat 1, Ligne 14
> 'CREATE VIEW' doit tre la premire instruction d'un lot de requtes.
>
> Ralf
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> a crit
> dans le message de news:em9NhSaEEHA.3980@.TK2MSFTNGP09.phx.gbl...
> BY
> (DTE,TRANSPORTEUR,ARRIVEE,NO_ENVOI,OPERA
TION_C_D,ETAT_ARRIVEE,ETAT_POSBAR,
PO
>

problem creating view on table from linked server DB using IP addr

Hello,
I can create a view on a table from a named linked server database.
select * from server1.RemoteDB.dbo.Table1
But I am having a problem creating a view on a table from a non-named linked
server that is just using the IP address of the server. Example:
Select * from [56.19.175.167].RemoteDB.dbo.Table1
When I run the view (in design mode) the square brackets get moved around
like this:
Select * from [56].[19.175.167.RemoteDB].dbo.Table1
The error message says it cannot find the server [56] and to re-run
sp_addlinkedserver. Could someone share the correct syntax for using the I
P
address as the server name?
Thanks,
Rich> When I run the view (in design mode)
STOP DOING THAT!
Create your view in Query Analyzer, and run the view in Query Analyzer.
Enterprise Mangler's tool for this is quite crippled and this is not the
only problem you'll encounter. Try using a CASE expression in your query,
for one.
A|||Just a few more details:
I am already aliasing the remote table
Select * from [56.19.175.167].RemoteDB.dbo.Table1 tblx
and
for the linked server that I can create a view on - that server resides on
the same server computer as the server I am working from.
The server I am having a problem with is a remote server which resides 3000
miles away from my local server. Does this make a difference?
"Rich" wrote:

> Hello,
> I can create a view on a table from a named linked server database.
> select * from server1.RemoteDB.dbo.Table1
> But I am having a problem creating a view on a table from a non-named link
ed
> server that is just using the IP address of the server. Example:
> Select * from [56.19.175.167].RemoteDB.dbo.Table1
> When I run the view (in design mode) the square brackets get moved around
> like this:
> Select * from [56].[19.175.167.RemoteDB].dbo.Table1
> The error message says it cannot find the server [56] and to re-run
> sp_addlinkedserver. Could someone share the correct syntax for using the
IP
> address as the server name?
> Thanks,
> Rich
>|||> Select * from [56.19.175.167].RemoteDB.dbo.Table1 tblx
Another thing to reduce the complexity here, of having IP addresses
hard-coded into your query, is to create a simply-named alias using Client
Network Utility, and then refer to the alias instead of the IP address. Not
that this makes it okay to use the view designer, but I think it is a better
approach overall. In addition to alleviating problems with 4-dot naming, it
also makes it much easier to update the system should that IP address
change - you just change the alias definition instead of all the places you
manually referred to it in code.

problem creating view on table from linked server DB using IP

>>create a simply-named alias using Client Network Utility
This sounds like a great idea. May I ask where I locate the Client Network
Utility and how to create the simply-named alias?
"Aaron Bertrand [SQL Server MVP]" wrote:

> Another thing to reduce the complexity here, of having IP addresses
> hard-coded into your query, is to create a simply-named alias using Client
> Network Utility, and then refer to the alias instead of the IP address. N
ot
> that this makes it okay to use the view designer, but I think it is a bett
er
> approach overall. In addition to alleviating problems with 4-dot naming,
it
> also makes it much easier to update the system should that IP address
> change - you just change the alias definition instead of all the places yo
u
> manually referred to it in code.
>
>> This sounds like a great idea. May I ask where I locate the Client
> Network
> Utility
Start > Programs > Microsoft SQL Server >|||Thank you. So I went to the utility. I clicked on the Alias tab. Then I
clicked Add. In the Server alias box I entered the alias name which was
simultaneously showing up in the Connection Parameters Server Name. I
changed that to the IP address of the remote server. Then I selected TCP/IP
in the Network Libaries and clicked OK.
In the Linked Server (from Enterprise Manager), I added the Aliased remote
server using the same context as the link using the IP address. I was not
able to link to the Alias server. Do I need to remove the linked server tha
t
is using the IP address first? I am a little afraid of doing that because
I
already have my client app (VB.Net) working and can retrieve data from the
views (which use the IP address of the remote server).
Well, if anything, it is an interesting exercise.
Thanks for the help.
Rich
"Aaron Bertrand [SQL Server MVP]" wrote:

> Start > Programs > Microsoft SQL Server >
>
>|||Server alias: should be "myserver"
Server name: should "a.b.c.d"
When you add the linked server, use the name "myserver"
What does "not able to" mean? Did you get an error message? If so, what
was it?
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:B2E4C610-E3DB-4229-93BD-D0B277868000@.microsoft.com...
> Thank you. So I went to the utility. I clicked on the Alias tab. Then I
> clicked Add. In the Server alias box I entered the alias name which was
> simultaneously showing up in the Connection Parameters Server Name. I
> changed that to the IP address of the remote server. Then I selected
> TCP/IP
> in the Network Libaries and clicked OK.
> In the Linked Server (from Enterprise Manager), I added the Aliased remote
> server using the same context as the link using the IP address. I was not
> able to link to the Alias server. Do I need to remove the linked server
> that
> is using the IP address first? I am a little afraid of doing that
> because I
> already have my client app (VB.Net) working and can retrieve data from the
> views (which use the IP address of the remote server).
> Well, if anything, it is an interesting exercise.
> Thanks for the help.
> Rich
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||When I add the Aliased server I use the Alias name "myServer"
I use the same security context as the original linked server - the UID,
impersonate, and the password, then apply. The new linked server shows up i
n
Enterprise manager, but when I click on tables - about 30 seconds later I ge
t
the error message that either the server "myServer" does not exist or Access
denied. In Query analyzer I try select * from myserver.remotedb.dbo.table1
I get the error message the same error message
"Server does not exist or Access denied"
when I look in sysservers, however
Use Master
select * from sysservers
I can see "myServer" in the list of servers. My question now is if the
Alias is correct - correctly referring to my remote server, and am I using
the correct security context.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Server alias: should be "myserver"
> Server name: should "a.b.c.d"
> When you add the linked server, use the name "myserver"
> What does "not able to" mean? Did you get an error message? If so, what
> was it?
>
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:B2E4C610-E3DB-4229-93BD-D0B277868000@.microsoft.com...
>
>|||Not sure, but you may have to restart the SQL Server service for the alias
to be picked up and routable? Also, have you tried any other authentication
methods (e.g. not defining a local login and checking "be made using this
security context" and putting the remote uid/password in that dialog)?
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:CC0FF896-33EF-4F9E-B881-1867D785255C@.microsoft.com...
> When I add the Aliased server I use the Alias name "myServer"
> I use the same security context as the original linked server - the UID,
> impersonate, and the password, then apply. The new linked server shows up
> in
> Enterprise manager, but when I click on tables - about 30 seconds later I
> get
> the error message that either the server "myServer" does not exist or
> Access
> denied. In Query analyzer I try select * from
> myserver.remotedb.dbo.table1
> I get the error message the same error message
> "Server does not exist or Access denied"
> when I look in sysservers, however
> Use Master
> select * from sysservers
> I can see "myServer" in the list of servers. My question now is if the
> Alias is correct - correctly referring to my remote server, and am I using
> the correct security context.
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>

Problem Creating View from Management Studio

Hi:

I have a parent table and 5 child tables with a PK,FK reference. I designed a view in Management Studio by dropping the master and Child tables, so that I could see the parent data, and all details from child rows.

My view does not contain any data though my tables do. What have I done wrong?

To describe what I want:

I have Parent table with PK, ParentFields.

I have Child1 with FK, Details1, Child2 with FK, Details2 and so on.

I want a view that shows PK, ParentFields, Details1, Details2,..Details5.

Seems simple enough, I dont know what is wrong. I am a beginner and only use the Designer to do this kind of stuff.

Appreciate any help.

TIA

Kar

You can create a simple query such as

CREATE VIEW [dbo].[vVesselsCargo]
AS
SELECT
VC.VesselCargoID,
CMP.CompanyName Shipper
FROM
VesselCargo VC,
Companies CMP
WHERE
CMP.CompanieID = VC.ShipperID

Then you can see how Management Studio was create that View in design mode.

|||

Your problem needs to be described better including (1) the definition of the parent table and the 5 child tables and (2) the definition of the view as it presently exists. My knee-jerk guess would be that you have used an inner join in a situation that merits a left join. Examine this sequence:

Code Snippet

declare @.parent table (pk int, pkInfo varchar(20))
insert into @.parent
select 1, '1st Parent' union all select 2, '2nd Parent'
--select * from @.parent

declare @.child1 table (pk int, detail_1 varchar(30))
insert into @.child1
select 1, 'Pk #1: Detail Rec #1' union all
select 2, 'Pk #2: Detail Rec #2'

declare @.child2 table (pk int, detail_2 varchar(30))
insert into @.child2
select 2, 'Pk #2: Detail Rec #2'

declare @.child3 table (pk int, detail_3 varchar(30))
insert into @.child3
select 1, 'Pk #1: Detail Rec #3'

declare @.child4 table(pk int, detail_4 varchar(30))
insert into @.child4
select 1, 'Pk #1: detail Rec #4'

declare @.child5 table(pk int, detail_5 varchar(30))
insert into @.child5
select 1, 'Pk #1: detail Rec #5'

select p.pk,
p.pkInfo,
detail_1,
detail_2,
detail_3,
detail_4,
detail_5
from @.parent p
join @.child1 c1
on p.pk = c1.pk
join @.child2 c2
on p.pk = c2.pk
join @.child3 c3
on p.pk = c3.pk
join @.child4 c4
on p.pk = c4.pk
join @.child5 c5
on p.pk = c5.pk

/*
pk pkInfo detail_1 detail_2 detail_3 detail_4 detail_5
- -

(0 row(s) affected)

*/

Because there are cases in which one of the detail records is missing, no rows are returned; however, if you change the JOINs into LEFT JOINS, you get results for both PKs:

Code Snippet

select p.pk,
p.pkInfo,
detail_1,
detail_2,
detail_3,
detail_4,
detail_5
from @.parent p
left join @.child1 c1
on p.pk = c1.pk
left join @.child2 c2
on p.pk = c2.pk
left join @.child3 c3
on p.pk = c3.pk
left join @.child4 c4
on p.pk = c4.pk
left join @.child5 c5
on p.pk = c5.pk

/*
pk pkInfo detail_1 detail_2 detail_3 detail_4 detail_5
- - - - - -
1 1st Parent Pk #1: Detail Rec #1 NULL Pk #1: Detail Rec #3 Pk #1: detail Rec #4 Pk #1: detail Rec #5
2 2nd Parent Pk #2: Detail Rec #2 Pk #2: Detail Rec #2 NULL NULL NULL
*/

Perhaps you need to use a left join to return your data; however, you really need to give a better description of your problem.

|||

Hi:

Thanks for the replies. I am a beginner, and I did not use or create any SQL. I just used Management Studio, drag-dropped tables and selected the fields that I want.

The tables already have PK and FK relationships defined, and the Designer automatically created SQL that roughly looks like:

Select Primary.PK, Primary.<Other_Fields>, Child1.<Details>, Child2.<Details> ,Child3.<Details> etc

from primary inner join Child1 on Primary.PK=Secondary.FK etc.

I tried modifying it to Left join as you suggested, but Management Studio automatically changes it to Left Outer Join when I save the view.

My data is as follows:

Primary contains all the Transactions that I need, and the Txn_ID.

Each child could contain details of a particular type. It is not mandatory that a row in Primary will have a row in a child.

So if I have 100 rows in Primary, each Child table could contain 0-100 rows.

I want my output to have 100 rows with the details from the child rows added. So am trying to make my data horizontal.

How do I do that?

Thanks a lot again.

Kar

|||

I would suggest that you learn to create your views and queries from text and not use the GUI tool.

|||

Outer Left Join is actually same as Left join. So either you can use TSQL to create the view or if you want to use the UI then u can right click on the link joining the two tables and chose to select all rows from the parent table - this will automatically modify the join from INNER JOIN to the corresponding OUTER JOIN.

Hope that helps,

Kuntal

|||

Thanks for your replies, I tried left outer joins too, but result isnt what I want.

What I want is like a lookup. Please see example below:

Parent table has 1000 rows.

Child 1 has optional details1 for 50 rows.

Child 2 has optional details2 for 50 rows etc.

My view should contian only 1000 rows, with Details1 lookup from Child 1 and so on.

Left Outer join gives me 1100 rows in this example.

TIA

Kar

|||

Your query should look like -

select p.parentColPrimaryKey, c1.child1optkey1, c2.child2optkey2...

from parentTable as p

left outer join child1 as c1 on c1.primaryKey = p.parentColPrimaryKey

left outer join child2 as c2 on c2.primaryKey = p.parentColPrimaryKey

...

This should return you 1000 rows if your parentTable has 1000 rows and if there is no value in c2 corresponds to some p.parentColPrimaryKey, c2.child2optkey2 will be null for that row.

Hope that helps,

Kuntal

|||

Nope, this is what Management Studio generates, but this query does not give me 1000 rows, it gives me 1000 rows, plus rows in c1, plus rows in c2 etc.

Problem Creating View from Management Studio

Hi:

I have a parent table and 5 child tables with a PK,FK reference. I designed a view in Management Studio by dropping the master and Child tables, so that I could see the parent data, and all details from child rows.

My view does not contain any data though my tables do. What have I done wrong?

To describe what I want:

I have Parent table with PK, ParentFields.

I have Child1 with FK, Details1, Child2 with FK, Details2 and so on.

I want a view that shows PK, ParentFields, Details1, Details2,..Details5.

Seems simple enough, I dont know what is wrong. I am a beginner and only use the Designer to do this kind of stuff.

Appreciate any help.

TIA

Kar

You can create a simple query such as

CREATE VIEW [dbo].[vVesselsCargo]
AS
SELECT
VC.VesselCargoID,
CMP.CompanyName Shipper
FROM
VesselCargo VC,
Companies CMP
WHERE
CMP.CompanieID = VC.ShipperID

Then you can see how Management Studio was create that View in design mode.

|||

Your problem needs to be described better including (1) the definition of the parent table and the 5 child tables and (2) the definition of the view as it presently exists. My knee-jerk guess would be that you have used an inner join in a situation that merits a left join. Examine this sequence:

Code Snippet

declare @.parent table (pk int, pkInfo varchar(20))
insert into @.parent
select 1, '1st Parent' union all select 2, '2nd Parent'
--select * from @.parent

declare @.child1 table (pk int, detail_1 varchar(30))
insert into @.child1
select 1, 'Pk #1: Detail Rec #1' union all
select 2, 'Pk #2: Detail Rec #2'

declare @.child2 table (pk int, detail_2 varchar(30))
insert into @.child2
select 2, 'Pk #2: Detail Rec #2'

declare @.child3 table (pk int, detail_3 varchar(30))
insert into @.child3
select 1, 'Pk #1: Detail Rec #3'

declare @.child4 table(pk int, detail_4 varchar(30))
insert into @.child4
select 1, 'Pk #1: detail Rec #4'

declare @.child5 table(pk int, detail_5 varchar(30))
insert into @.child5
select 1, 'Pk #1: detail Rec #5'

select p.pk,
p.pkInfo,
detail_1,
detail_2,
detail_3,
detail_4,
detail_5
from @.parent p
join @.child1 c1
on p.pk = c1.pk
join @.child2 c2
on p.pk = c2.pk
join @.child3 c3
on p.pk = c3.pk
join @.child4 c4
on p.pk = c4.pk
join @.child5 c5
on p.pk = c5.pk

/*
pk pkInfo detail_1 detail_2 detail_3 detail_4 detail_5
- -

(0 row(s) affected)

*/

Because there are cases in which one of the detail records is missing, no rows are returned; however, if you change the JOINs into LEFT JOINS, you get results for both PKs:

Code Snippet

select p.pk,
p.pkInfo,
detail_1,
detail_2,
detail_3,
detail_4,
detail_5
from @.parent p
left join @.child1 c1
on p.pk = c1.pk
left join @.child2 c2
on p.pk = c2.pk
left join @.child3 c3
on p.pk = c3.pk
left join @.child4 c4
on p.pk = c4.pk
left join @.child5 c5
on p.pk = c5.pk

/*
pk pkInfo detail_1 detail_2 detail_3 detail_4 detail_5
- - - - - -
1 1st Parent Pk #1: Detail Rec #1 NULL Pk #1: Detail Rec #3 Pk #1: detail Rec #4 Pk #1: detail Rec #5
2 2nd Parent Pk #2: Detail Rec #2 Pk #2: Detail Rec #2 NULL NULL NULL
*/

Perhaps you need to use a left join to return your data; however, you really need to give a better description of your problem.

|||

Hi:

Thanks for the replies. I am a beginner, and I did not use or create any SQL. I just used Management Studio, drag-dropped tables and selected the fields that I want.

The tables already have PK and FK relationships defined, and the Designer automatically created SQL that roughly looks like:

Select Primary.PK, Primary.<Other_Fields>, Child1.<Details>, Child2.<Details> ,Child3.<Details> etc

from primary inner join Child1 on Primary.PK=Secondary.FK etc.

I tried modifying it to Left join as you suggested, but Management Studio automatically changes it to Left Outer Join when I save the view.

My data is as follows:

Primary contains all the Transactions that I need, and the Txn_ID.

Each child could contain details of a particular type. It is not mandatory that a row in Primary will have a row in a child.

So if I have 100 rows in Primary, each Child table could contain 0-100 rows.

I want my output to have 100 rows with the details from the child rows added. So am trying to make my data horizontal.

How do I do that?

Thanks a lot again.

Kar

|||

I would suggest that you learn to create your views and queries from text and not use the GUI tool.

|||

Outer Left Join is actually same as Left join. So either you can use TSQL to create the view or if you want to use the UI then u can right click on the link joining the two tables and chose to select all rows from the parent table - this will automatically modify the join from INNER JOIN to the corresponding OUTER JOIN.

Hope that helps,

Kuntal

|||

Thanks for your replies, I tried left outer joins too, but result isnt what I want.

What I want is like a lookup. Please see example below:

Parent table has 1000 rows.

Child 1 has optional details1 for 50 rows.

Child 2 has optional details2 for 50 rows etc.

My view should contian only 1000 rows, with Details1 lookup from Child 1 and so on.

Left Outer join gives me 1100 rows in this example.

TIA

Kar

|||

Your query should look like -

select p.parentColPrimaryKey, c1.child1optkey1, c2.child2optkey2...

from parentTable as p

left outer join child1 as c1 on c1.primaryKey = p.parentColPrimaryKey

left outer join child2 as c2 on c2.primaryKey = p.parentColPrimaryKey

...

This should return you 1000 rows if your parentTable has 1000 rows and if there is no value in c2 corresponds to some p.parentColPrimaryKey, c2.child2optkey2 will be null for that row.

Hope that helps,

Kuntal

|||

Nope, this is what Management Studio generates, but this query does not give me 1000 rows, it gives me 1000 rows, plus rows in c1, plus rows in c2 etc.

Problem Creating View from Management Studio

Hi:

I have a parent table and 5 child tables with a PK,FK reference. I designed a view in Management Studio by dropping the master and Child tables, so that I could see the parent data, and all details from child rows.

My view does not contain any data though my tables do. What have I done wrong?

To describe what I want:

I have Parent table with PK, ParentFields.

I have Child1 with FK, Details1, Child2 with FK, Details2 and so on.

I want a view that shows PK, ParentFields, Details1, Details2,..Details5.

Seems simple enough, I dont know what is wrong. I am a beginner and only use the Designer to do this kind of stuff.

Appreciate any help.

TIA

Kar

You can create a simple query such as

CREATE VIEW [dbo].[vVesselsCargo]
AS
SELECT
VC.VesselCargoID,
CMP.CompanyName Shipper
FROM
VesselCargo VC,
Companies CMP
WHERE
CMP.CompanieID = VC.ShipperID

Then you can see how Management Studio was create that View in design mode.

|||

Your problem needs to be described better including (1) the definition of the parent table and the 5 child tables and (2) the definition of the view as it presently exists. My knee-jerk guess would be that you have used an inner join in a situation that merits a left join. Examine this sequence:

Code Snippet

declare @.parent table (pk int, pkInfo varchar(20))
insert into @.parent
select 1, '1st Parent' union all select 2, '2nd Parent'
--select * from @.parent

declare @.child1 table (pk int, detail_1 varchar(30))
insert into @.child1
select 1, 'Pk #1: Detail Rec #1' union all
select 2, 'Pk #2: Detail Rec #2'

declare @.child2 table (pk int, detail_2 varchar(30))
insert into @.child2
select 2, 'Pk #2: Detail Rec #2'

declare @.child3 table (pk int, detail_3 varchar(30))
insert into @.child3
select 1, 'Pk #1: Detail Rec #3'

declare @.child4 table(pk int, detail_4 varchar(30))
insert into @.child4
select 1, 'Pk #1: detail Rec #4'

declare @.child5 table(pk int, detail_5 varchar(30))
insert into @.child5
select 1, 'Pk #1: detail Rec #5'

select p.pk,
p.pkInfo,
detail_1,
detail_2,
detail_3,
detail_4,
detail_5
from @.parent p
join @.child1 c1
on p.pk = c1.pk
join @.child2 c2
on p.pk = c2.pk
join @.child3 c3
on p.pk = c3.pk
join @.child4 c4
on p.pk = c4.pk
join @.child5 c5
on p.pk = c5.pk

/*
pk pkInfo detail_1 detail_2 detail_3 detail_4 detail_5
- -

(0 row(s) affected)

*/

Because there are cases in which one of the detail records is missing, no rows are returned; however, if you change the JOINs into LEFT JOINS, you get results for both PKs:

Code Snippet

select p.pk,
p.pkInfo,
detail_1,
detail_2,
detail_3,
detail_4,
detail_5
from @.parent p
left join @.child1 c1
on p.pk = c1.pk
left join @.child2 c2
on p.pk = c2.pk
left join @.child3 c3
on p.pk = c3.pk
left join @.child4 c4
on p.pk = c4.pk
left join @.child5 c5
on p.pk = c5.pk

/*
pk pkInfo detail_1 detail_2 detail_3 detail_4 detail_5
- - - - - -
1 1st Parent Pk #1: Detail Rec #1 NULL Pk #1: Detail Rec #3 Pk #1: detail Rec #4 Pk #1: detail Rec #5
2 2nd Parent Pk #2: Detail Rec #2 Pk #2: Detail Rec #2 NULL NULL NULL
*/

Perhaps you need to use a left join to return your data; however, you really need to give a better description of your problem.

|||

Hi:

Thanks for the replies. I am a beginner, and I did not use or create any SQL. I just used Management Studio, drag-dropped tables and selected the fields that I want.

The tables already have PK and FK relationships defined, and the Designer automatically created SQL that roughly looks like:

Select Primary.PK, Primary.<Other_Fields>, Child1.<Details>, Child2.<Details> ,Child3.<Details> etc

from primary inner join Child1 on Primary.PK=Secondary.FK etc.

I tried modifying it to Left join as you suggested, but Management Studio automatically changes it to Left Outer Join when I save the view.

My data is as follows:

Primary contains all the Transactions that I need, and the Txn_ID.

Each child could contain details of a particular type. It is not mandatory that a row in Primary will have a row in a child.

So if I have 100 rows in Primary, each Child table could contain 0-100 rows.

I want my output to have 100 rows with the details from the child rows added. So am trying to make my data horizontal.

How do I do that?

Thanks a lot again.

Kar

|||

I would suggest that you learn to create your views and queries from text and not use the GUI tool.

|||

Outer Left Join is actually same as Left join. So either you can use TSQL to create the view or if you want to use the UI then u can right click on the link joining the two tables and chose to select all rows from the parent table - this will automatically modify the join from INNER JOIN to the corresponding OUTER JOIN.

Hope that helps,

Kuntal

|||

Thanks for your replies, I tried left outer joins too, but result isnt what I want.

What I want is like a lookup. Please see example below:

Parent table has 1000 rows.

Child 1 has optional details1 for 50 rows.

Child 2 has optional details2 for 50 rows etc.

My view should contian only 1000 rows, with Details1 lookup from Child 1 and so on.

Left Outer join gives me 1100 rows in this example.

TIA

Kar

|||

Your query should look like -

select p.parentColPrimaryKey, c1.child1optkey1, c2.child2optkey2...

from parentTable as p

left outer join child1 as c1 on c1.primaryKey = p.parentColPrimaryKey

left outer join child2 as c2 on c2.primaryKey = p.parentColPrimaryKey

...

This should return you 1000 rows if your parentTable has 1000 rows and if there is no value in c2 corresponds to some p.parentColPrimaryKey, c2.child2optkey2 will be null for that row.

Hope that helps,

Kuntal

|||

Nope, this is what Management Studio generates, but this query does not give me 1000 rows, it gives me 1000 rows, plus rows in c1, plus rows in c2 etc.

Problem creating view for cube

Anyone see a problem with creating this view for a cube??

The error I get seems to be in line 8 with the sum function, but I see nothing wrong with it.

CREATE VIEW CarCube AS
SELECT CASE WHEN (GROUPING(CarType) = 1) THEN 'ALL'
ELSE ISNULL(CarType, 'Not Known')
END AS CarType,
CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
ELSE ISNULL(Color, 'Not Known')
END AS Color
SUM(Amount) AS TotalAmt
FROM Inventory
GROUP BY CarType, Color WITH CUBEYou need a comma after Color on the line before|||lol, sometimes the simplest things are the hardest to find.

Thanks|||The people who are really good at finding at simple screw ups are the ones who are really good at making them. ;-)

Problem creating view

Hi
I have the following set up:
CREATE TABLE PURCHASE
(
ITEM_ID int NOT NULL,
CUST_ID int NOT NULL,
)
go
CREATE TABLE ITEM
(
ITEM_ID int NOT NULL,
ITEM_NAME varchar(32) NOT NULL
)
go
CREATE TABLE CUST
(
CUST_ID int NOT NULL,
CUST_NAME varchar(32) NOT NULL
)
go
CREATE VIEW PURCHASE_VIEW
AS
SELECT P.ITEM_ID, I.ITEM_NAME, P.CUST_ID, C.CUST_NAME
FROM PURCHASE P, ITEM I, CUST C
WHERE (P.ITEM_ID = I.ITEM_ID) AND (P.CUST_ID = C.CUST_ID)
go
The problem I have is, the PURCHASE_VIEW only shows rows in PURCHASE
for which a corresponding
ITEM_NAME and CUST_NAME exist in ITEM and CUST. However, I want the
view to return a row for each row of the PURCHASE table, but with the
ITEM_NAME and CUST_NAME set to NULL if no corresponding entry exists
in ITEM or CUST.
Is there some way I can achieve this?
Thanks,
Neil> However, I want the
> view to return a row for each row of the PURCHASE table, but with the
> ITEM_NAME and CUST_NAME set to NULL if no corresponding entry exists
> in ITEM or CUST.
You can use an outer join will return purchases even without corresponding
items and customers. I must ask how how a purchase was possible for an
non-existent item or customer. Perhaps the foreign keys are missing,
leading to data integrity problems.
CREATE VIEW PURCHASE_VIEW
AS
SELECT
P.ITEM_ID,
I.ITEM_NAME,
P.CUST_ID,
C.CUST_NAME
FROM PURCHASE P
LEFT OUTER JOIN ITEM I ON
P.ITEM_ID = I.ITEM_ID
LEFT OUTER JOIN CUST C ON
P.CUST_ID = C.CUST_ID
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1171809173.950612.119490@.l53g2000cwa.googlegroups.com...
> Hi
> I have the following set up:
> CREATE TABLE PURCHASE
> (
> ITEM_ID int NOT NULL,
> CUST_ID int NOT NULL,
> )
> go
> CREATE TABLE ITEM
> (
> ITEM_ID int NOT NULL,
> ITEM_NAME varchar(32) NOT NULL
> )
> go
> CREATE TABLE CUST
> (
> CUST_ID int NOT NULL,
> CUST_NAME varchar(32) NOT NULL
> )
> go
> CREATE VIEW PURCHASE_VIEW
> AS
> SELECT P.ITEM_ID, I.ITEM_NAME, P.CUST_ID, C.CUST_NAME
> FROM PURCHASE P, ITEM I, CUST C
> WHERE (P.ITEM_ID = I.ITEM_ID) AND (P.CUST_ID = C.CUST_ID)
> go
> The problem I have is, the PURCHASE_VIEW only shows rows in PURCHASE
> for which a corresponding
> ITEM_NAME and CUST_NAME exist in ITEM and CUST. However, I want the
> view to return a row for each row of the PURCHASE table, but with the
> ITEM_NAME and CUST_NAME set to NULL if no corresponding entry exists
> in ITEM or CUST.
> Is there some way I can achieve this?
> Thanks,
> Neil
>|||On 18 Feb, 15:02, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:
> You can use an outer join will return purchases even without corresponding
> items and customers. I must ask how how a purchase was possible for an
> non-existent item or customer. Perhaps the foreign keys are missing,
> leading to data integrity problems.
Many thanks, Dan. I will give this a try.
To answer your question, this is a contrived example, which I
simplified for the purpose of this post.

Problem creating view

Hi
I have the following set up:
CREATE TABLE PURCHASE
(
ITEM_IDint NOT NULL,
CUST_IDint NOT NULL,
)
go
CREATE TABLE ITEM
(
ITEM_IDint NOT NULL,
ITEM_NAMEvarchar(32) NOT NULL
)
go
CREATE TABLE CUST
(
CUST_IDint NOT NULL,
CUST_NAMEvarchar(32) NOT NULL
)
go
CREATE VIEW PURCHASE_VIEW
AS
SELECT P.ITEM_ID, I.ITEM_NAME, P.CUST_ID, C.CUST_NAME
FROM PURCHASE P, ITEM I, CUST C
WHERE (P.ITEM_ID = I.ITEM_ID) AND (P.CUST_ID = C.CUST_ID)
go
The problem I have is, the PURCHASE_VIEW only shows rows in PURCHASE
for which a corresponding
ITEM_NAME and CUST_NAME exist in ITEM and CUST. However, I want the
view to return a row for each row of the PURCHASE table, but with the
ITEM_NAME and CUST_NAME set to NULL if no corresponding entry exists
in ITEM or CUST.
Is there some way I can achieve this?
Thanks,
Neil
> However, I want the
> view to return a row for each row of the PURCHASE table, but with the
> ITEM_NAME and CUST_NAME set to NULL if no corresponding entry exists
> in ITEM or CUST.
You can use an outer join will return purchases even without corresponding
items and customers. I must ask how how a purchase was possible for an
non-existent item or customer. Perhaps the foreign keys are missing,
leading to data integrity problems.
CREATE VIEW PURCHASE_VIEW
AS
SELECT
P.ITEM_ID,
I.ITEM_NAME,
P.CUST_ID,
C.CUST_NAME
FROM PURCHASE P
LEFT OUTER JOIN ITEM I ON
P.ITEM_ID = I.ITEM_ID
LEFT OUTER JOIN CUST C ON
P.CUST_ID = C.CUST_ID
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1171809173.950612.119490@.l53g2000cwa.googlegr oups.com...
> Hi
> I have the following set up:
> CREATE TABLE PURCHASE
> (
> ITEM_ID int NOT NULL,
> CUST_ID int NOT NULL,
> )
> go
> CREATE TABLE ITEM
> (
> ITEM_ID int NOT NULL,
> ITEM_NAME varchar(32) NOT NULL
> )
> go
> CREATE TABLE CUST
> (
> CUST_ID int NOT NULL,
> CUST_NAME varchar(32) NOT NULL
> )
> go
> CREATE VIEW PURCHASE_VIEW
> AS
> SELECT P.ITEM_ID, I.ITEM_NAME, P.CUST_ID, C.CUST_NAME
> FROM PURCHASE P, ITEM I, CUST C
> WHERE (P.ITEM_ID = I.ITEM_ID) AND (P.CUST_ID = C.CUST_ID)
> go
> The problem I have is, the PURCHASE_VIEW only shows rows in PURCHASE
> for which a corresponding
> ITEM_NAME and CUST_NAME exist in ITEM and CUST. However, I want the
> view to return a row for each row of the PURCHASE table, but with the
> ITEM_NAME and CUST_NAME set to NULL if no corresponding entry exists
> in ITEM or CUST.
> Is there some way I can achieve this?
> Thanks,
> Neil
>
|||On 18 Feb, 15:02, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:
> You can use an outer join will return purchases even without corresponding
> items and customers. I must ask how how a purchase was possible for an
> non-existent item or customer. Perhaps the foreign keys are missing,
> leading to data integrity problems.
Many thanks, Dan. I will give this a try.
To answer your question, this is a contrived example, which I
simplified for the purpose of this post.

Problem creating view

Hi
I have the following set up:
CREATE TABLE PURCHASE
(
ITEM_ID int NOT NULL,
CUST_ID int NOT NULL,
)
go
CREATE TABLE ITEM
(
ITEM_ID int NOT NULL,
ITEM_NAME varchar(32) NOT NULL
)
go
CREATE TABLE CUST
(
CUST_ID int NOT NULL,
CUST_NAME varchar(32) NOT NULL
)
go
CREATE VIEW PURCHASE_VIEW
AS
SELECT P.ITEM_ID, I.ITEM_NAME, P.CUST_ID, C.CUST_NAME
FROM PURCHASE P, ITEM I, CUST C
WHERE (P.ITEM_ID = I.ITEM_ID) AND (P.CUST_ID = C.CUST_ID)
go
The problem I have is, the PURCHASE_VIEW only shows rows in PURCHASE
for which a corresponding
ITEM_NAME and CUST_NAME exist in ITEM and CUST. However, I want the
view to return a row for each row of the PURCHASE table, but with the
ITEM_NAME and CUST_NAME set to NULL if no corresponding entry exists
in ITEM or CUST.
Is there some way I can achieve this?
Thanks,
Neil> However, I want the
> view to return a row for each row of the PURCHASE table, but with the
> ITEM_NAME and CUST_NAME set to NULL if no corresponding entry exists
> in ITEM or CUST.
You can use an outer join will return purchases even without corresponding
items and customers. I must ask how how a purchase was possible for an
non-existent item or customer. Perhaps the foreign keys are missing,
leading to data integrity problems.
CREATE VIEW PURCHASE_VIEW
AS
SELECT
P.ITEM_ID,
I.ITEM_NAME,
P.CUST_ID,
C.CUST_NAME
FROM PURCHASE P
LEFT OUTER JOIN ITEM I ON
P.ITEM_ID = I.ITEM_ID
LEFT OUTER JOIN CUST C ON
P.CUST_ID = C.CUST_ID
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1171809173.950612.119490@.l53g2000cwa.googlegroups.com...
> Hi
> I have the following set up:
> CREATE TABLE PURCHASE
> (
> ITEM_ID int NOT NULL,
> CUST_ID int NOT NULL,
> )
> go
> CREATE TABLE ITEM
> (
> ITEM_ID int NOT NULL,
> ITEM_NAME varchar(32) NOT NULL
> )
> go
> CREATE TABLE CUST
> (
> CUST_ID int NOT NULL,
> CUST_NAME varchar(32) NOT NULL
> )
> go
> CREATE VIEW PURCHASE_VIEW
> AS
> SELECT P.ITEM_ID, I.ITEM_NAME, P.CUST_ID, C.CUST_NAME
> FROM PURCHASE P, ITEM I, CUST C
> WHERE (P.ITEM_ID = I.ITEM_ID) AND (P.CUST_ID = C.CUST_ID)
> go
> The problem I have is, the PURCHASE_VIEW only shows rows in PURCHASE
> for which a corresponding
> ITEM_NAME and CUST_NAME exist in ITEM and CUST. However, I want the
> view to return a row for each row of the PURCHASE table, but with the
> ITEM_NAME and CUST_NAME set to NULL if no corresponding entry exists
> in ITEM or CUST.
> Is there some way I can achieve this?
> Thanks,
> Neil
>|||On 18 Feb, 15:02, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:
> > However, I want the
> > view to return a row for each row of the PURCHASE table, but with the
> > ITEM_NAME and CUST_NAME set to NULL if no corresponding entry exists
> > in ITEM or CUST.
> You can use an outer join will return purchases even without corresponding
> items and customers. I must ask how how a purchase was possible for an
> non-existent item or customer. Perhaps the foreign keys are missing,
> leading to data integrity problems.
Many thanks, Dan. I will give this a try.
To answer your question, this is a contrived example, which I
simplified for the purpose of this post.

Problem Creating TRIGGER on a VIEW

Hello,

I have a problem that definitely has me stumped.

I have a view that looks at data in a different database. Some of the fields in the view are updateable and some are not. I am trying to create a trigger against the view that will allow me to audit the updates into an audit table. I am having problems when trying to execute the CREATE TRIGGER statement.

I keep getting the message...

Server: Msg 208, Level 16, State 4, Procedure updDocInfo, Line 1
Invalid object name 'vwDC_DocInfo'.

Where vwDC_DocInfo is the name of the view.

Does anyone have any idea why I might be getting this error? The VIEW definitely does exist and I am executing the script in the same database as the view.

The script is included below...

CREATE TRIGGER updDocInfo
ON [vwDC_DocInfo]
FOR UPDATE AS

DECLARE @.ModifiedDate AS DATETIME
SELECT @.ModifiedDate = GETDATE()

-- Audit OLD record.
INSERT tblAudit_DC_DocInfo
SELECT
0 AS AuditType,
ItemID,
Comment,
VersionComment,
CheckedOut,
Title,
BaseParagonDocumentNumber,
Author,
ClientDocumentNumber,
ClientDocumentType,
ClientJobNumber,
[Module],
Unit,
SequenceNumber,
RevisionDate,
ApprovedBy,
CheckedDepartmentManager,
CheckedLeadEngineerDesigner,
IssueType,
RevisedByDesigner,
RevisedByEngineer,
RevisionCode,
HSECheck,
CurrentVersionNumber,
CurrentVersionDate,
USER AS ChangedByUser,
@.ModifiedDate AS DateChanged
FROM DELETED DEL

-- Audit NEW record.
INSERT tblAudit_DC_DocInfo
SELECT
0 AS AuditType,
ItemID,
Comment,
VersionComment,
CheckedOut,
Title,
BaseParagonDocumentNumber,
Author,
ClientDocumentNumber,
ClientDocumentType,
ClientJobNumber,
[Module],
Unit,
SequenceNumber,
RevisionDate,
ApprovedBy,
CheckedDepartmentManager,
CheckedLeadEngineerDesigner,
IssueType,
RevisedByDesigner,
RevisedByEngineer,
RevisionCode,
HSECheck,
CurrentVersionNumber,
CurrentVersionDate,
USER AS ChangedByUser,
@.ModifiedDate AS DateChanged
FROM INSERTED INSI didn't think you could create a trigger on a view, but BOL says otherwise...

Still doesn't keep my example here from exploding though

USE Northwind
GO

CREATE VIEW myView99 AS SELECT * FROM Orders
GO

CREATE TRIGGER myTrigger99 ON myView99 FOR INSERT AS PRINT 'HI'
GO

DROP VIEW myView99
GO|||BOL:

Designing Triggers
Microsoft SQL Server 2000 provides two options when designing triggers:

INSTEAD OF triggers are executed in place of the usual triggering action. INSTEAD OF triggers can also be defined on views with one or more base tables, where they can extend the types of updates a view can support.

AFTER triggers are executed after the action of the INSERT, UPDATE, or DELETE statement is performed. Specifying AFTER is the same as specifying FOR, which is the only option available in earlier versions of SQL Server. AFTER triggers can be specified !!! ONLY !!! on tables.|||Good catch, snail! How about that, Brett, huh? huh? huh?|||Originally posted by snail
BOL:

Designing Triggers
Microsoft SQL Server 2000 provides two options when designing triggers:

INSTEAD OF triggers are executed in place of the usual triggering action. INSTEAD OF triggers can also be defined on views with one or more base tables, where they can extend the types of updates a view can support.

AFTER triggers are executed after the action of the INSERT, UPDATE, or DELETE statement is performed. Specifying AFTER is the same as specifying FOR, which is the only option available in earlier versions of SQL Server. AFTER triggers can be specified !!! ONLY !!! on tables.

Thanks,

I did some experimenting after I created the post. I finally realized that I could only create a INSTEAD OF TRIGGER. It is not obvious in the documentation. The only problem was that I then had to go and recreate the transaction, as I really did want the transaction to go through. Very odd use of triggers...

Problem creating indexed views with linked servers

Hi, I have a problem, maybe someone can help me.

I'm traing to create a view with a Linked Server

This query works great:

select id, descr
from SERVER.DB.dbo.TABLE

When I tray to create the view:

CREATE VIEW dbo.View1 WITH SCHEMABINDING
AS
select id, descr
from SERVER.DB.dbo.TABLE
GO

I have this error:

Server: Msg 4512, Level 16, State 3, Procedure Pais2, Line 3
Cannot schema bind view 'dbo.View1' because name 'SERVER.DB.dbo.TABLE'
is invalid for schema binding. Names must be in two-part format and an
object cannot reference itself.

So I try this:

CREATE VIEW dbo.View1 WITH SCHEMABINDING
AS
select SERVER.DB.dbo.TABLE.id, SERVER.DB.dbo.TABLE.descr
from SERVER.DB.dbo.TABLE
GO

I have this error:

Server: Msg 117, Level 15, State 2, Procedure Pais2, Line 3
The number name 'SERVER.DB.dbo.TABLE' contains more than the maximum
number of prefixes. The maximum is 3.

Then I try this:

CREATE VIEW dbo.View1 WITH SCHEMABINDING
AS
select a.id, a.descr
from SERVER.DB.dbo.TABLE as A
GO

I Have this error

Server: Msg 4512, Level 16, State 3, Procedure View1, Line 3
Cannot schema bind view 'dbo.View1' because name
'iservsql1.osderrhh.dbo.pais' is invalid for schema binding. Names
must be in two-part format and an object cannot reference itself.

This query alone works great:

select a.id, a.descr
from SERVER.DB.dbo.TABLE as A

The names aren't what I describe here (id is not valid without []).

ANY IDEAS?!??!?!

I don't know what else can I do.

I need help!!!

TANKS A LOT!!!!!!!!Ariel (ap@.data54.com) writes:
> Hi, I have a problem, maybe someone can help me.
> I'm traing to create a view with a Linked Server

Stop! Take a step back and consider what you are doing!

An indexed view is a materialized view. This means that when an update
is made to a base table, this update is propagated to the view, not
only logically, but also physically.

Now, if that table on the remote server was updated would should happen?
Particularly, what would happen if our local server was unavailble? Or
the particular database was unavailable?

In practice, you cannot even have cross-database indexed views on the
same server, and you run into a stopblock already WITH SCHEMABINDING.
If you could create a schema-bounbd view that referenced another database,
each attempt to drop or rename a databaes, or set a it offline, would
need to check all other databases on the server for SCHEMABINDING
references.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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