hi,
i am using instead of trigger in sqlserver 2005, my trigger looks like
create trigger [dbo].[docsUpdate]
on [dbo].[Docs]
instead of update
as
IF (Update(MetaInfo))
BEGIN
bla bla
bla bla
[Some operation]
end
my table looks lke this
Dirname LeafName TimeLastModified Extension Metainfo
-- -- -- -- --
if any data in metainfo column updated then my trigger will get fired
and do respective operation, but when dirname , leafname or any if
other column get modified other them metainfo then how these modified
data will get affected in to my docs table.
Since changes done on table will not get affected due to instead of
trigger, how can i update other column values.
please help me.
thanks
sathya narayanan
narayanan@.gsdindia.com
http://www.microsoft.com/communities...7-cdfb17b609f8
AMB
"sathya" wrote:
> hi,
>
> i am using instead of trigger in sqlserver 2005, my trigger looks like
>
> create trigger [dbo].[docsUpdate]
> on [dbo].[Docs]
> instead of update
> as
> IF (Update(MetaInfo))
> BEGIN
> bla bla
> bla bla
> [Some operation]
> end
> my table looks lke this
>
> Dirname LeafName TimeLastModified Extension Metainfo
> -- -- -- -- --
>
> if any data in metainfo column updated then my trigger will get fired
> and do respective operation, but when dirname , leafname or any if
> other column get modified other them metainfo then how these modified
> data will get affected in to my docs table.
> Since changes done on table will not get affected due to instead of
> trigger, how can i update other column values.
> please help me.
>
> thanks
> sathya narayanan
> narayanan@.gsdindia.com
>
Showing posts with label dbo. Show all posts
Showing posts with label dbo. Show all posts
Friday, March 30, 2012
Problem in instead of trigger
hi,
i am using instead of trigger in sqlserver 2005, my trigger looks like
create trigger [dbo].[docsUpdate]
on [dbo].[Docs]
instead of update
as
IF (Update(MetaInfo))
BEGIN
bla bla
bla bla
[Some operation]
end
my table looks lke this
Dirname LeafName TimeLastModified Extension Metainfo
-- -- -- -- --
if any data in metainfo column updated then my trigger will get fired
and do respective operation, but when dirname , leafname or any if
other column get modified other them metainfo then how these modified
data will get affected in to my docs table.
Since changes done on table will not get affected due to instead of
trigger, how can i update other column values.
please help me.
thanks
sathya narayanan
narayanan@.gsdindia.comhttp://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.sqlserver.programming&mid=2144dbff-77b7-4327-b3c7-cdfb17b609f8
AMB
"sathya" wrote:
> hi,
>
> i am using instead of trigger in sqlserver 2005, my trigger looks like
>
> create trigger [dbo].[docsUpdate]
> on [dbo].[Docs]
> instead of update
> as
> IF (Update(MetaInfo))
> BEGIN
> bla bla
> bla bla
> [Some operation]
> end
> my table looks lke this
>
> Dirname LeafName TimeLastModified Extension Metainfo
> -- -- -- -- --
>
> if any data in metainfo column updated then my trigger will get fired
> and do respective operation, but when dirname , leafname or any if
> other column get modified other them metainfo then how these modified
> data will get affected in to my docs table.
> Since changes done on table will not get affected due to instead of
> trigger, how can i update other column values.
> please help me.
>
> thanks
> sathya narayanan
> narayanan@.gsdindia.com
>
i am using instead of trigger in sqlserver 2005, my trigger looks like
create trigger [dbo].[docsUpdate]
on [dbo].[Docs]
instead of update
as
IF (Update(MetaInfo))
BEGIN
bla bla
bla bla
[Some operation]
end
my table looks lke this
Dirname LeafName TimeLastModified Extension Metainfo
-- -- -- -- --
if any data in metainfo column updated then my trigger will get fired
and do respective operation, but when dirname , leafname or any if
other column get modified other them metainfo then how these modified
data will get affected in to my docs table.
Since changes done on table will not get affected due to instead of
trigger, how can i update other column values.
please help me.
thanks
sathya narayanan
narayanan@.gsdindia.comhttp://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.sqlserver.programming&mid=2144dbff-77b7-4327-b3c7-cdfb17b609f8
AMB
"sathya" wrote:
> hi,
>
> i am using instead of trigger in sqlserver 2005, my trigger looks like
>
> create trigger [dbo].[docsUpdate]
> on [dbo].[Docs]
> instead of update
> as
> IF (Update(MetaInfo))
> BEGIN
> bla bla
> bla bla
> [Some operation]
> end
> my table looks lke this
>
> Dirname LeafName TimeLastModified Extension Metainfo
> -- -- -- -- --
>
> if any data in metainfo column updated then my trigger will get fired
> and do respective operation, but when dirname , leafname or any if
> other column get modified other them metainfo then how these modified
> data will get affected in to my docs table.
> Since changes done on table will not get affected due to instead of
> trigger, how can i update other column values.
> please help me.
>
> thanks
> sathya narayanan
> narayanan@.gsdindia.com
>
Monday, March 26, 2012
Problem in creating Asymmetric keys
I am a novice to the SQL server. I am trying to create Asymmetric key using the query
CREATE ASYMMETRIC KEY PacificSales19 AUTHORIZATION dbo
FROM FILE = ' C:\temp\temp1.snk'
ENCRYPTION BY PASSWORD = 'ABC123!@.#$';
GO
But I alwys get the follwing error
The certificate, asymmetric key, or private key file does not exist or has invalid format.
Can anyone please guide me as to how to go ablout creating the ASYMMETRIC KEY FROM FILE.
Thanks and regards
The statement you are trying is correct - this is how you can create an asymmetric key from a file.
You should check that the specified path is correct and that the file is not corrupted. How did you generate that file?
Thanks
Laurentiu
Friday, March 23, 2012
Problem in a QUERY with COUNT
CREATE PROCEDURE [dbo].[GD_SP_HARDWARE_MONITOR_COUNT]
-- Add the parameters for the stored procedure here
@.Direccao nvarchar(10)
AS
DECLARE @.NrMon int
BEGIN
SELECT dbo.Monitor.MON_Monitor AS Item, COUNT(*) AS Unidades, dbo.Monitor.MON_CustoUnitario AS Total
FROM dbo.HARDWARE INNER JOIN
dbo.ADServico_User ON dbo.HARDWARE.UserID = dbo.ADServico_User.UserID INNER JOIN
dbo.SERVICO ON dbo.ADServico_User.GrupoServico = dbo.SERVICO.S_GrupoServico INNER JOIN
dbo.Monitor ON dbo.HARDWARE.MONITOR_ID = dbo.Monitor.MONITOR_ID
WHERE (dbo.HARDWARE.MONITOR_ID <> 5)
GROUP BY dbo.Monitor.MON_Monitor, dbo.Monitor.MON_CustoUnitario
END
DEAR FRIENDS,
HOW CAN I MULTIPLICATE THE VALUE FROM UNIDADES AND TOTAL?
Current output:
GOAL:
THANKS
Wrap your query in an outer query:
SELECT Item, Unidades, Total, Unidades*Total AS SomethingNew
FROM
(
Here comes your existing query
) SubQUery
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||THANKS!!!!!!
FANTASTIC!!!!
|||Another question:
I made a UNION with 2 querys :
ALTER PROCEDURE [dbo].[GD_SP_FACTURA]
-- Add the parameters for the stored procedure here
@.Direccao nvarchar(10)
AS
BEGIN
SELECT Item, Unidades, CustoUnitario, Unidades*CustoUnitario AS Total
FROM
(
SELECT dbo.ModeloPC_Tipo.MOD_Nome AS Item, COUNT(*) AS Unidades, dbo.ModeloPC_Tipo.MOD_CustoUnit AS CustoUnitario
FROM dbo.HARDWARE INNER JOIN
dbo.ADServico_User ON dbo.HARDWARE.UserID = dbo.ADServico_User.UserID INNER JOIN
dbo.SERVICO ON dbo.ADServico_User.GrupoServico = dbo.SERVICO.S_GrupoServico INNER JOIN
dbo.ModeloPC ON dbo.HARDWARE.MODELO_ID = dbo.ModeloPC.MODELO_ID INNER JOIN
dbo.ModeloPC_Tipo ON dbo.ModeloPC.MOD_Tipo = dbo.ModeloPC_Tipo.MOD_ID
WHERE (dbo.HARDWARE.MONITOR_ID <> 5)
GROUP BY dbo.SERVICO.S_NomeDir, dbo.ModeloPC_Tipo.MOD_CustoUnit, dbo.ModeloPC_Tipo.MOD_Nome
HAVING (dbo.SERVICO.S_NomeDir = @.Direccao)
) SubQUery
UNION
SELECT Item, Unidades, CustoUnitario, Unidades*CustoUnitario AS Total
FROM
(
SELECT dbo.Monitor.MON_Monitor AS Item, COUNT(*) AS Unidades, dbo.Monitor.MON_CustoUnitario AS CustoUnitario
FROM dbo.HARDWARE INNER JOIN
dbo.ADServico_User ON dbo.HARDWARE.UserID = dbo.ADServico_User.UserID INNER JOIN
dbo.SERVICO ON dbo.ADServico_User.GrupoServico = dbo.SERVICO.S_GrupoServico INNER JOIN
dbo.Monitor ON dbo.HARDWARE.MONITOR_ID = dbo.Monitor.MONITOR_ID
WHERE (dbo.HARDWARE.MONITOR_ID =1 OR dbo.HARDWARE.MONITOR_ID=2) AND dbo.SERVICO.S_NomeDir=@.Direccao
GROUP BY dbo.Monitor.MON_Monitor, dbo.Monitor.MON_CustoUnitario
) SubQUery
END
OUTPUT:
Desktops 166 433,09 71892,94
Portáteis 3 675,84 2027,52
TFT 15 166 22,08 3665,28
TFT 17 3 24,35 73,05
How can I SUM the last Column of the 2 queries? How can I SUM (Total)?
Thanks!!!
|||
--1.SUM the last two columns
SELECT t.Item, t.Unidades, (t.CustoUnitario+t.Total) AS LAST2Sum FROM (Your UNION result) t
--2.SUM your TOTAL
SELECT SUM(t.Total) AS SumTotal FROM (Your UNION result) t
Tuesday, March 20, 2012
problem exporting views
While exporting a database with "export > objects > selecting the views"
I get the error:
Invalid object name 'dbo.myview'
The weird thing is that this view allready exists in the destination dbase.
Any help appreciated.
THX"nicholas" <murmurait1@.hotmail.com> wrote in
news:Ol2emWkgFHA.1948@.TK2MSFTNGP12.phx.gbl:
> While exporting a database with "export > objects > selecting the
> views" I get the error:
> Invalid object name 'dbo.myview'
> The weird thing is that this view allready exists in the destination
> dbase.
> Any help appreciated.
> THX
>
>
Run the following query in your user database. What are the results?
select t1.name, t2.name
from sysobjects t1, sysusers t2
where t1.name = 'myview' and t1.uid = t2.uid
Regards
JTC ^..^|||Sorry to ask, but how can I do this?
I tried the SQL query analyser and inserted:
select t1.name, t2.name
from sysobjects t1, sysusers t2
where t1.name = [mydatabase].[dbo].[tbl_customers] and t1.uid =
t2.uid
but get this message:
Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'mydatabase.dbo' does not match with a table name or alias
name used in the query
thx a lot
"JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
news:Xns968BC0C2DE4E1daveJTC@.213.123.26.234...
> "nicholas" <murmurait1@.hotmail.com> wrote in
> news:Ol2emWkgFHA.1948@.TK2MSFTNGP12.phx.gbl:
>
> Run the following query in your user database. What are the results?
> select t1.name, t2.name
> from sysobjects t1, sysusers t2
> where t1.name = 'myview' and t1.uid = t2.uid
> --
> Regards
> JTC ^..^|||You are supposed to replace "mydatabase" with the name of your database. And
"myview" with the name
of your view.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"nicholas" <murmurait1@.hotmail.com> wrote in message news:OcWsU$lgFHA.4000@.TK2MSFTNGP12.phx.
gbl...
> Sorry to ask, but how can I do this?
> I tried the SQL query analyser and inserted:
> select t1.name, t2.name
> from sysobjects t1, sysusers t2
> where t1.name = [mydatabase].[dbo].[tbl_customers] and t1.uid
= t2.uid
> but get this message:
> Server: Msg 107, Level 16, State 2, Line 1
> The column prefix 'mydatabase.dbo' does not match with a table name or ali
as
> name used in the query
> thx a lot
> "JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
> news:Xns968BC0C2DE4E1daveJTC@.213.123.26.234...
>|||Yes, ofcourse, I did that.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u%23d$xEmgFHA.2472@.TK2MSFTNGP15.phx.gbl...
> You are supposed to replace "mydatabase" with the name of your database.
And "myview" with the name
> of your view.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "nicholas" <murmurait1@.hotmail.com> wrote in message
news:OcWsU$lgFHA.4000@.TK2MSFTNGP12.phx.gbl...
alias[vbcol=seagreen]
>|||"nicholas" <murmurait1@.hotmail.com> wrote in
news:OQwceImgFHA.3256@.TK2MSFTNGP12.phx.gbl:
> Yes, ofcourse, I did that.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
> wrote in message news:u%23d$xEmgFHA.2472@.TK2MSFTNGP15.phx.gbl...
> And "myview" with the name
> news:OcWsU$lgFHA.4000@.TK2MSFTNGP12.phx.gbl...
> alias
>
Like this...
select t1.name, t2.name
from sysobjects t1, sysusers t2
where t1.name = 'tbl_customers' and t1.uid = t2.uid
The idea is that the query will returns all tables named tbl_customers
along with the table owners. Please Copy and Paste the results.
Regards
JTC ^..^|||Go back to the proposed query, and fix below things:
Do not remove the quotes around the name of the view in the WHERE clause. Do
not fully qualify the
object name in the where clause.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"nicholas" <murmurait1@.hotmail.com> wrote in message news:OQwceImgFHA.3256@.TK2MSFTNGP12.phx.
gbl...
> Yes, ofcourse, I did that.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:u%23d$xEmgFHA.2472@.TK2MSFTNGP15.phx.gbl...
> And "myview" with the name
> news:OcWsU$lgFHA.4000@.TK2MSFTNGP12.phx.gbl...
> alias
>
I get the error:
Invalid object name 'dbo.myview'
The weird thing is that this view allready exists in the destination dbase.
Any help appreciated.
THX"nicholas" <murmurait1@.hotmail.com> wrote in
news:Ol2emWkgFHA.1948@.TK2MSFTNGP12.phx.gbl:
> While exporting a database with "export > objects > selecting the
> views" I get the error:
> Invalid object name 'dbo.myview'
> The weird thing is that this view allready exists in the destination
> dbase.
> Any help appreciated.
> THX
>
>
Run the following query in your user database. What are the results?
select t1.name, t2.name
from sysobjects t1, sysusers t2
where t1.name = 'myview' and t1.uid = t2.uid
Regards
JTC ^..^|||Sorry to ask, but how can I do this?
I tried the SQL query analyser and inserted:
select t1.name, t2.name
from sysobjects t1, sysusers t2
where t1.name = [mydatabase].[dbo].[tbl_customers] and t1.uid =
t2.uid
but get this message:
Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'mydatabase.dbo' does not match with a table name or alias
name used in the query
thx a lot
"JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
news:Xns968BC0C2DE4E1daveJTC@.213.123.26.234...
> "nicholas" <murmurait1@.hotmail.com> wrote in
> news:Ol2emWkgFHA.1948@.TK2MSFTNGP12.phx.gbl:
>
> Run the following query in your user database. What are the results?
> select t1.name, t2.name
> from sysobjects t1, sysusers t2
> where t1.name = 'myview' and t1.uid = t2.uid
> --
> Regards
> JTC ^..^|||You are supposed to replace "mydatabase" with the name of your database. And
"myview" with the name
of your view.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"nicholas" <murmurait1@.hotmail.com> wrote in message news:OcWsU$lgFHA.4000@.TK2MSFTNGP12.phx.
gbl...
> Sorry to ask, but how can I do this?
> I tried the SQL query analyser and inserted:
> select t1.name, t2.name
> from sysobjects t1, sysusers t2
> where t1.name = [mydatabase].[dbo].[tbl_customers] and t1.uid
= t2.uid
> but get this message:
> Server: Msg 107, Level 16, State 2, Line 1
> The column prefix 'mydatabase.dbo' does not match with a table name or ali
as
> name used in the query
> thx a lot
> "JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
> news:Xns968BC0C2DE4E1daveJTC@.213.123.26.234...
>|||Yes, ofcourse, I did that.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u%23d$xEmgFHA.2472@.TK2MSFTNGP15.phx.gbl...
> You are supposed to replace "mydatabase" with the name of your database.
And "myview" with the name
> of your view.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "nicholas" <murmurait1@.hotmail.com> wrote in message
news:OcWsU$lgFHA.4000@.TK2MSFTNGP12.phx.gbl...
alias[vbcol=seagreen]
>|||"nicholas" <murmurait1@.hotmail.com> wrote in
news:OQwceImgFHA.3256@.TK2MSFTNGP12.phx.gbl:
> Yes, ofcourse, I did that.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
> wrote in message news:u%23d$xEmgFHA.2472@.TK2MSFTNGP15.phx.gbl...
> And "myview" with the name
> news:OcWsU$lgFHA.4000@.TK2MSFTNGP12.phx.gbl...
> alias
>
Like this...
select t1.name, t2.name
from sysobjects t1, sysusers t2
where t1.name = 'tbl_customers' and t1.uid = t2.uid
The idea is that the query will returns all tables named tbl_customers
along with the table owners. Please Copy and Paste the results.
Regards
JTC ^..^|||Go back to the proposed query, and fix below things:
Do not remove the quotes around the name of the view in the WHERE clause. Do
not fully qualify the
object name in the where clause.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"nicholas" <murmurait1@.hotmail.com> wrote in message news:OQwceImgFHA.3256@.TK2MSFTNGP12.phx.
gbl...
> Yes, ofcourse, I did that.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:u%23d$xEmgFHA.2472@.TK2MSFTNGP15.phx.gbl...
> And "myview" with the name
> news:OcWsU$lgFHA.4000@.TK2MSFTNGP12.phx.gbl...
> alias
>
problem exporting views
While exporting a database with "export > objects > selecting the views"
I get the error:
Invalid object name 'dbo.myview'
The weird thing is that this view allready exists in the destination dbase.
Any help appreciated.
THX
"nicholas" <murmurait1@.hotmail.com> wrote in
news:Ol2emWkgFHA.1948@.TK2MSFTNGP12.phx.gbl:
> While exporting a database with "export > objects > selecting the
> views" I get the error:
> Invalid object name 'dbo.myview'
> The weird thing is that this view allready exists in the destination
> dbase.
> Any help appreciated.
> THX
>
>
Run the following query in your user database. What are the results?
select t1.name, t2.name
from sysobjects t1, sysusers t2
where t1.name = 'myview' and t1.uid = t2.uid
Regards
JTC ^..^
|||Sorry to ask, but how can I do this?
I tried the SQL query analyser and inserted:
select t1.name, t2.name
from sysobjects t1, sysusers t2
where t1.name = [mydatabase].[dbo].[tbl_customers] and t1.uid = t2.uid
but get this message:
Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'mydatabase.dbo' does not match with a table name or alias
name used in the query
thx a lot
"JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
news:Xns968BC0C2DE4E1daveJTC@.213.123.26.234...
> "nicholas" <murmurait1@.hotmail.com> wrote in
> news:Ol2emWkgFHA.1948@.TK2MSFTNGP12.phx.gbl:
>
> Run the following query in your user database. What are the results?
> select t1.name, t2.name
> from sysobjects t1, sysusers t2
> where t1.name = 'myview' and t1.uid = t2.uid
> --
> Regards
> JTC ^..^
|||You are supposed to replace "mydatabase" with the name of your database. And "myview" with the name
of your view.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"nicholas" <murmurait1@.hotmail.com> wrote in message news:OcWsU$lgFHA.4000@.TK2MSFTNGP12.phx.gbl...
> Sorry to ask, but how can I do this?
> I tried the SQL query analyser and inserted:
> select t1.name, t2.name
> from sysobjects t1, sysusers t2
> where t1.name = [mydatabase].[dbo].[tbl_customers] and t1.uid = t2.uid
> but get this message:
> Server: Msg 107, Level 16, State 2, Line 1
> The column prefix 'mydatabase.dbo' does not match with a table name or alias
> name used in the query
> thx a lot
> "JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
> news:Xns968BC0C2DE4E1daveJTC@.213.123.26.234...
>
|||Yes, ofcourse, I did that.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u%23d$xEmgFHA.2472@.TK2MSFTNGP15.phx.gbl...
> You are supposed to replace "mydatabase" with the name of your database.
And "myview" with the name
> of your view.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "nicholas" <murmurait1@.hotmail.com> wrote in message
news:OcWsU$lgFHA.4000@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
alias
>
|||"nicholas" <murmurait1@.hotmail.com> wrote in
news:OQwceImgFHA.3256@.TK2MSFTNGP12.phx.gbl:
> Yes, ofcourse, I did that.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
> wrote in message news:u%23d$xEmgFHA.2472@.TK2MSFTNGP15.phx.gbl...
> And "myview" with the name
> news:OcWsU$lgFHA.4000@.TK2MSFTNGP12.phx.gbl...
> alias
>
Like this...
select t1.name, t2.name
from sysobjects t1, sysusers t2
where t1.name = 'tbl_customers' and t1.uid = t2.uid
The idea is that the query will returns all tables named tbl_customers
along with the table owners. Please Copy and Paste the results.
Regards
JTC ^..^
|||Go back to the proposed query, and fix below things:
Do not remove the quotes around the name of the view in the WHERE clause. Do not fully qualify the
object name in the where clause.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"nicholas" <murmurait1@.hotmail.com> wrote in message news:OQwceImgFHA.3256@.TK2MSFTNGP12.phx.gbl...
> Yes, ofcourse, I did that.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:u%23d$xEmgFHA.2472@.TK2MSFTNGP15.phx.gbl...
> And "myview" with the name
> news:OcWsU$lgFHA.4000@.TK2MSFTNGP12.phx.gbl...
> alias
>
I get the error:
Invalid object name 'dbo.myview'
The weird thing is that this view allready exists in the destination dbase.
Any help appreciated.
THX
"nicholas" <murmurait1@.hotmail.com> wrote in
news:Ol2emWkgFHA.1948@.TK2MSFTNGP12.phx.gbl:
> While exporting a database with "export > objects > selecting the
> views" I get the error:
> Invalid object name 'dbo.myview'
> The weird thing is that this view allready exists in the destination
> dbase.
> Any help appreciated.
> THX
>
>
Run the following query in your user database. What are the results?
select t1.name, t2.name
from sysobjects t1, sysusers t2
where t1.name = 'myview' and t1.uid = t2.uid
Regards
JTC ^..^
|||Sorry to ask, but how can I do this?
I tried the SQL query analyser and inserted:
select t1.name, t2.name
from sysobjects t1, sysusers t2
where t1.name = [mydatabase].[dbo].[tbl_customers] and t1.uid = t2.uid
but get this message:
Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'mydatabase.dbo' does not match with a table name or alias
name used in the query
thx a lot
"JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
news:Xns968BC0C2DE4E1daveJTC@.213.123.26.234...
> "nicholas" <murmurait1@.hotmail.com> wrote in
> news:Ol2emWkgFHA.1948@.TK2MSFTNGP12.phx.gbl:
>
> Run the following query in your user database. What are the results?
> select t1.name, t2.name
> from sysobjects t1, sysusers t2
> where t1.name = 'myview' and t1.uid = t2.uid
> --
> Regards
> JTC ^..^
|||You are supposed to replace "mydatabase" with the name of your database. And "myview" with the name
of your view.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"nicholas" <murmurait1@.hotmail.com> wrote in message news:OcWsU$lgFHA.4000@.TK2MSFTNGP12.phx.gbl...
> Sorry to ask, but how can I do this?
> I tried the SQL query analyser and inserted:
> select t1.name, t2.name
> from sysobjects t1, sysusers t2
> where t1.name = [mydatabase].[dbo].[tbl_customers] and t1.uid = t2.uid
> but get this message:
> Server: Msg 107, Level 16, State 2, Line 1
> The column prefix 'mydatabase.dbo' does not match with a table name or alias
> name used in the query
> thx a lot
> "JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
> news:Xns968BC0C2DE4E1daveJTC@.213.123.26.234...
>
|||Yes, ofcourse, I did that.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u%23d$xEmgFHA.2472@.TK2MSFTNGP15.phx.gbl...
> You are supposed to replace "mydatabase" with the name of your database.
And "myview" with the name
> of your view.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "nicholas" <murmurait1@.hotmail.com> wrote in message
news:OcWsU$lgFHA.4000@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
alias
>
|||"nicholas" <murmurait1@.hotmail.com> wrote in
news:OQwceImgFHA.3256@.TK2MSFTNGP12.phx.gbl:
> Yes, ofcourse, I did that.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
> wrote in message news:u%23d$xEmgFHA.2472@.TK2MSFTNGP15.phx.gbl...
> And "myview" with the name
> news:OcWsU$lgFHA.4000@.TK2MSFTNGP12.phx.gbl...
> alias
>
Like this...
select t1.name, t2.name
from sysobjects t1, sysusers t2
where t1.name = 'tbl_customers' and t1.uid = t2.uid
The idea is that the query will returns all tables named tbl_customers
along with the table owners. Please Copy and Paste the results.
Regards
JTC ^..^
|||Go back to the proposed query, and fix below things:
Do not remove the quotes around the name of the view in the WHERE clause. Do not fully qualify the
object name in the where clause.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"nicholas" <murmurait1@.hotmail.com> wrote in message news:OQwceImgFHA.3256@.TK2MSFTNGP12.phx.gbl...
> Yes, ofcourse, I did that.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:u%23d$xEmgFHA.2472@.TK2MSFTNGP15.phx.gbl...
> And "myview" with the name
> news:OcWsU$lgFHA.4000@.TK2MSFTNGP12.phx.gbl...
> alias
>
problem exporting views
While exporting a database with "export > objects > selecting the views"
I get the error:
Invalid object name 'dbo.myview'
The weird thing is that this view allready exists in the destination dbase.
Any help appreciated.
THX"nicholas" <murmurait1@.hotmail.com> wrote in
news:Ol2emWkgFHA.1948@.TK2MSFTNGP12.phx.gbl:
> While exporting a database with "export > objects > selecting the
> views" I get the error:
> Invalid object name 'dbo.myview'
> The weird thing is that this view allready exists in the destination
> dbase.
> Any help appreciated.
> THX
>
>
Run the following query in your user database. What are the results?
select t1.name, t2.name
from sysobjects t1, sysusers t2
where t1.name = 'myview' and t1.uid = t2.uid
--
Regards
JTC ^..^|||Sorry to ask, but how can I do this?
I tried the SQL query analyser and inserted:
select t1.name, t2.name
from sysobjects t1, sysusers t2
where t1.name = [mydatabase].[dbo].[tbl_customers] and t1.uid = t2.uid
but get this message:
Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'mydatabase.dbo' does not match with a table name or alias
name used in the query
thx a lot
"JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
news:Xns968BC0C2DE4E1daveJTC@.213.123.26.234...
> "nicholas" <murmurait1@.hotmail.com> wrote in
> news:Ol2emWkgFHA.1948@.TK2MSFTNGP12.phx.gbl:
> > While exporting a database with "export > objects > selecting the
> > views" I get the error:
> > Invalid object name 'dbo.myview'
> >
> > The weird thing is that this view allready exists in the destination
> > dbase.
> >
> > Any help appreciated.
> >
> > THX
> >
> >
> >
> Run the following query in your user database. What are the results?
> select t1.name, t2.name
> from sysobjects t1, sysusers t2
> where t1.name = 'myview' and t1.uid = t2.uid
> --
> Regards
> JTC ^..^|||You are supposed to replace "mydatabase" with the name of your database. And "myview" with the name
of your view.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"nicholas" <murmurait1@.hotmail.com> wrote in message news:OcWsU$lgFHA.4000@.TK2MSFTNGP12.phx.gbl...
> Sorry to ask, but how can I do this?
> I tried the SQL query analyser and inserted:
> select t1.name, t2.name
> from sysobjects t1, sysusers t2
> where t1.name = [mydatabase].[dbo].[tbl_customers] and t1.uid = t2.uid
> but get this message:
> Server: Msg 107, Level 16, State 2, Line 1
> The column prefix 'mydatabase.dbo' does not match with a table name or alias
> name used in the query
> thx a lot
> "JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
> news:Xns968BC0C2DE4E1daveJTC@.213.123.26.234...
>> "nicholas" <murmurait1@.hotmail.com> wrote in
>> news:Ol2emWkgFHA.1948@.TK2MSFTNGP12.phx.gbl:
>> > While exporting a database with "export > objects > selecting the
>> > views" I get the error:
>> > Invalid object name 'dbo.myview'
>> >
>> > The weird thing is that this view allready exists in the destination
>> > dbase.
>> >
>> > Any help appreciated.
>> >
>> > THX
>> >
>> >
>> >
>> Run the following query in your user database. What are the results?
>> select t1.name, t2.name
>> from sysobjects t1, sysusers t2
>> where t1.name = 'myview' and t1.uid = t2.uid
>> --
>> Regards
>> JTC ^..^
>|||Yes, ofcourse, I did that.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u%23d$xEmgFHA.2472@.TK2MSFTNGP15.phx.gbl...
> You are supposed to replace "mydatabase" with the name of your database.
And "myview" with the name
> of your view.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "nicholas" <murmurait1@.hotmail.com> wrote in message
news:OcWsU$lgFHA.4000@.TK2MSFTNGP12.phx.gbl...
> > Sorry to ask, but how can I do this?
> > I tried the SQL query analyser and inserted:
> >
> > select t1.name, t2.name
> > from sysobjects t1, sysusers t2
> > where t1.name = [mydatabase].[dbo].[tbl_customers] and t1.uid = t2.uid
> >
> > but get this message:
> > Server: Msg 107, Level 16, State 2, Line 1
> > The column prefix 'mydatabase.dbo' does not match with a table name or
alias
> > name used in the query
> >
> > thx a lot
> >
> > "JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
> > news:Xns968BC0C2DE4E1daveJTC@.213.123.26.234...
> >> "nicholas" <murmurait1@.hotmail.com> wrote in
> >> news:Ol2emWkgFHA.1948@.TK2MSFTNGP12.phx.gbl:
> >>
> >> > While exporting a database with "export > objects > selecting the
> >> > views" I get the error:
> >> > Invalid object name 'dbo.myview'
> >> >
> >> > The weird thing is that this view allready exists in the destination
> >> > dbase.
> >> >
> >> > Any help appreciated.
> >> >
> >> > THX
> >> >
> >> >
> >> >
> >>
> >> Run the following query in your user database. What are the results?
> >>
> >> select t1.name, t2.name
> >> from sysobjects t1, sysusers t2
> >> where t1.name = 'myview' and t1.uid = t2.uid
> >>
> >> --
> >> Regards
> >> JTC ^..^
> >
> >
>|||"nicholas" <murmurait1@.hotmail.com> wrote in
news:OQwceImgFHA.3256@.TK2MSFTNGP12.phx.gbl:
> Yes, ofcourse, I did that.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
> wrote in message news:u%23d$xEmgFHA.2472@.TK2MSFTNGP15.phx.gbl...
>> You are supposed to replace "mydatabase" with the name of your
>> database.
> And "myview" with the name
>> of your view.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "nicholas" <murmurait1@.hotmail.com> wrote in message
> news:OcWsU$lgFHA.4000@.TK2MSFTNGP12.phx.gbl...
>> > Sorry to ask, but how can I do this?
>> > I tried the SQL query analyser and inserted:
>> >
>> > select t1.name, t2.name
>> > from sysobjects t1, sysusers t2
>> > where t1.name = [mydatabase].[dbo].[tbl_customers] and t1.uid =>> > t2.uid
>> >
>> > but get this message:
>> > Server: Msg 107, Level 16, State 2, Line 1
>> > The column prefix 'mydatabase.dbo' does not match with a table name
>> > or
> alias
>> > name used in the query
>> >
>> > thx a lot
>> >
>> > "JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
>> > news:Xns968BC0C2DE4E1daveJTC@.213.123.26.234...
>> >> "nicholas" <murmurait1@.hotmail.com> wrote in
>> >> news:Ol2emWkgFHA.1948@.TK2MSFTNGP12.phx.gbl:
>> >>
>> >> > While exporting a database with "export > objects > selecting
>> >> > the views" I get the error:
>> >> > Invalid object name 'dbo.myview'
>> >> >
>> >> > The weird thing is that this view allready exists in the
>> >> > destination dbase.
>> >> >
>> >> > Any help appreciated.
>> >> >
>> >> > THX
>> >> >
>> >> >
>> >> >
>> >>
>> >> Run the following query in your user database. What are the
>> >> results?
>> >>
>> >> select t1.name, t2.name
>> >> from sysobjects t1, sysusers t2
>> >> where t1.name = 'myview' and t1.uid = t2.uid
>> >>
>> >> --
>> >> Regards
>> >> JTC ^..^
>> >
>> >
>
Like this...
select t1.name, t2.name
from sysobjects t1, sysusers t2
where t1.name = 'tbl_customers' and t1.uid = t2.uid
The idea is that the query will returns all tables named tbl_customers
along with the table owners. Please Copy and Paste the results.
--
Regards
JTC ^..^|||Go back to the proposed query, and fix below things:
Do not remove the quotes around the name of the view in the WHERE clause. Do not fully qualify the
object name in the where clause.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"nicholas" <murmurait1@.hotmail.com> wrote in message news:OQwceImgFHA.3256@.TK2MSFTNGP12.phx.gbl...
> Yes, ofcourse, I did that.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:u%23d$xEmgFHA.2472@.TK2MSFTNGP15.phx.gbl...
>> You are supposed to replace "mydatabase" with the name of your database.
> And "myview" with the name
>> of your view.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "nicholas" <murmurait1@.hotmail.com> wrote in message
> news:OcWsU$lgFHA.4000@.TK2MSFTNGP12.phx.gbl...
>> > Sorry to ask, but how can I do this?
>> > I tried the SQL query analyser and inserted:
>> >
>> > select t1.name, t2.name
>> > from sysobjects t1, sysusers t2
>> > where t1.name = [mydatabase].[dbo].[tbl_customers] and t1.uid = t2.uid
>> >
>> > but get this message:
>> > Server: Msg 107, Level 16, State 2, Line 1
>> > The column prefix 'mydatabase.dbo' does not match with a table name or
> alias
>> > name used in the query
>> >
>> > thx a lot
>> >
>> > "JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
>> > news:Xns968BC0C2DE4E1daveJTC@.213.123.26.234...
>> >> "nicholas" <murmurait1@.hotmail.com> wrote in
>> >> news:Ol2emWkgFHA.1948@.TK2MSFTNGP12.phx.gbl:
>> >>
>> >> > While exporting a database with "export > objects > selecting the
>> >> > views" I get the error:
>> >> > Invalid object name 'dbo.myview'
>> >> >
>> >> > The weird thing is that this view allready exists in the destination
>> >> > dbase.
>> >> >
>> >> > Any help appreciated.
>> >> >
>> >> > THX
>> >> >
>> >> >
>> >> >
>> >>
>> >> Run the following query in your user database. What are the results?
>> >>
>> >> select t1.name, t2.name
>> >> from sysobjects t1, sysusers t2
>> >> where t1.name = 'myview' and t1.uid = t2.uid
>> >>
>> >> --
>> >> Regards
>> >> JTC ^..^
>> >
>> >
>
I get the error:
Invalid object name 'dbo.myview'
The weird thing is that this view allready exists in the destination dbase.
Any help appreciated.
THX"nicholas" <murmurait1@.hotmail.com> wrote in
news:Ol2emWkgFHA.1948@.TK2MSFTNGP12.phx.gbl:
> While exporting a database with "export > objects > selecting the
> views" I get the error:
> Invalid object name 'dbo.myview'
> The weird thing is that this view allready exists in the destination
> dbase.
> Any help appreciated.
> THX
>
>
Run the following query in your user database. What are the results?
select t1.name, t2.name
from sysobjects t1, sysusers t2
where t1.name = 'myview' and t1.uid = t2.uid
--
Regards
JTC ^..^|||Sorry to ask, but how can I do this?
I tried the SQL query analyser and inserted:
select t1.name, t2.name
from sysobjects t1, sysusers t2
where t1.name = [mydatabase].[dbo].[tbl_customers] and t1.uid = t2.uid
but get this message:
Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'mydatabase.dbo' does not match with a table name or alias
name used in the query
thx a lot
"JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
news:Xns968BC0C2DE4E1daveJTC@.213.123.26.234...
> "nicholas" <murmurait1@.hotmail.com> wrote in
> news:Ol2emWkgFHA.1948@.TK2MSFTNGP12.phx.gbl:
> > While exporting a database with "export > objects > selecting the
> > views" I get the error:
> > Invalid object name 'dbo.myview'
> >
> > The weird thing is that this view allready exists in the destination
> > dbase.
> >
> > Any help appreciated.
> >
> > THX
> >
> >
> >
> Run the following query in your user database. What are the results?
> select t1.name, t2.name
> from sysobjects t1, sysusers t2
> where t1.name = 'myview' and t1.uid = t2.uid
> --
> Regards
> JTC ^..^|||You are supposed to replace "mydatabase" with the name of your database. And "myview" with the name
of your view.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"nicholas" <murmurait1@.hotmail.com> wrote in message news:OcWsU$lgFHA.4000@.TK2MSFTNGP12.phx.gbl...
> Sorry to ask, but how can I do this?
> I tried the SQL query analyser and inserted:
> select t1.name, t2.name
> from sysobjects t1, sysusers t2
> where t1.name = [mydatabase].[dbo].[tbl_customers] and t1.uid = t2.uid
> but get this message:
> Server: Msg 107, Level 16, State 2, Line 1
> The column prefix 'mydatabase.dbo' does not match with a table name or alias
> name used in the query
> thx a lot
> "JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
> news:Xns968BC0C2DE4E1daveJTC@.213.123.26.234...
>> "nicholas" <murmurait1@.hotmail.com> wrote in
>> news:Ol2emWkgFHA.1948@.TK2MSFTNGP12.phx.gbl:
>> > While exporting a database with "export > objects > selecting the
>> > views" I get the error:
>> > Invalid object name 'dbo.myview'
>> >
>> > The weird thing is that this view allready exists in the destination
>> > dbase.
>> >
>> > Any help appreciated.
>> >
>> > THX
>> >
>> >
>> >
>> Run the following query in your user database. What are the results?
>> select t1.name, t2.name
>> from sysobjects t1, sysusers t2
>> where t1.name = 'myview' and t1.uid = t2.uid
>> --
>> Regards
>> JTC ^..^
>|||Yes, ofcourse, I did that.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u%23d$xEmgFHA.2472@.TK2MSFTNGP15.phx.gbl...
> You are supposed to replace "mydatabase" with the name of your database.
And "myview" with the name
> of your view.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "nicholas" <murmurait1@.hotmail.com> wrote in message
news:OcWsU$lgFHA.4000@.TK2MSFTNGP12.phx.gbl...
> > Sorry to ask, but how can I do this?
> > I tried the SQL query analyser and inserted:
> >
> > select t1.name, t2.name
> > from sysobjects t1, sysusers t2
> > where t1.name = [mydatabase].[dbo].[tbl_customers] and t1.uid = t2.uid
> >
> > but get this message:
> > Server: Msg 107, Level 16, State 2, Line 1
> > The column prefix 'mydatabase.dbo' does not match with a table name or
alias
> > name used in the query
> >
> > thx a lot
> >
> > "JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
> > news:Xns968BC0C2DE4E1daveJTC@.213.123.26.234...
> >> "nicholas" <murmurait1@.hotmail.com> wrote in
> >> news:Ol2emWkgFHA.1948@.TK2MSFTNGP12.phx.gbl:
> >>
> >> > While exporting a database with "export > objects > selecting the
> >> > views" I get the error:
> >> > Invalid object name 'dbo.myview'
> >> >
> >> > The weird thing is that this view allready exists in the destination
> >> > dbase.
> >> >
> >> > Any help appreciated.
> >> >
> >> > THX
> >> >
> >> >
> >> >
> >>
> >> Run the following query in your user database. What are the results?
> >>
> >> select t1.name, t2.name
> >> from sysobjects t1, sysusers t2
> >> where t1.name = 'myview' and t1.uid = t2.uid
> >>
> >> --
> >> Regards
> >> JTC ^..^
> >
> >
>|||"nicholas" <murmurait1@.hotmail.com> wrote in
news:OQwceImgFHA.3256@.TK2MSFTNGP12.phx.gbl:
> Yes, ofcourse, I did that.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
> wrote in message news:u%23d$xEmgFHA.2472@.TK2MSFTNGP15.phx.gbl...
>> You are supposed to replace "mydatabase" with the name of your
>> database.
> And "myview" with the name
>> of your view.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "nicholas" <murmurait1@.hotmail.com> wrote in message
> news:OcWsU$lgFHA.4000@.TK2MSFTNGP12.phx.gbl...
>> > Sorry to ask, but how can I do this?
>> > I tried the SQL query analyser and inserted:
>> >
>> > select t1.name, t2.name
>> > from sysobjects t1, sysusers t2
>> > where t1.name = [mydatabase].[dbo].[tbl_customers] and t1.uid =>> > t2.uid
>> >
>> > but get this message:
>> > Server: Msg 107, Level 16, State 2, Line 1
>> > The column prefix 'mydatabase.dbo' does not match with a table name
>> > or
> alias
>> > name used in the query
>> >
>> > thx a lot
>> >
>> > "JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
>> > news:Xns968BC0C2DE4E1daveJTC@.213.123.26.234...
>> >> "nicholas" <murmurait1@.hotmail.com> wrote in
>> >> news:Ol2emWkgFHA.1948@.TK2MSFTNGP12.phx.gbl:
>> >>
>> >> > While exporting a database with "export > objects > selecting
>> >> > the views" I get the error:
>> >> > Invalid object name 'dbo.myview'
>> >> >
>> >> > The weird thing is that this view allready exists in the
>> >> > destination dbase.
>> >> >
>> >> > Any help appreciated.
>> >> >
>> >> > THX
>> >> >
>> >> >
>> >> >
>> >>
>> >> Run the following query in your user database. What are the
>> >> results?
>> >>
>> >> select t1.name, t2.name
>> >> from sysobjects t1, sysusers t2
>> >> where t1.name = 'myview' and t1.uid = t2.uid
>> >>
>> >> --
>> >> Regards
>> >> JTC ^..^
>> >
>> >
>
Like this...
select t1.name, t2.name
from sysobjects t1, sysusers t2
where t1.name = 'tbl_customers' and t1.uid = t2.uid
The idea is that the query will returns all tables named tbl_customers
along with the table owners. Please Copy and Paste the results.
--
Regards
JTC ^..^|||Go back to the proposed query, and fix below things:
Do not remove the quotes around the name of the view in the WHERE clause. Do not fully qualify the
object name in the where clause.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"nicholas" <murmurait1@.hotmail.com> wrote in message news:OQwceImgFHA.3256@.TK2MSFTNGP12.phx.gbl...
> Yes, ofcourse, I did that.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:u%23d$xEmgFHA.2472@.TK2MSFTNGP15.phx.gbl...
>> You are supposed to replace "mydatabase" with the name of your database.
> And "myview" with the name
>> of your view.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "nicholas" <murmurait1@.hotmail.com> wrote in message
> news:OcWsU$lgFHA.4000@.TK2MSFTNGP12.phx.gbl...
>> > Sorry to ask, but how can I do this?
>> > I tried the SQL query analyser and inserted:
>> >
>> > select t1.name, t2.name
>> > from sysobjects t1, sysusers t2
>> > where t1.name = [mydatabase].[dbo].[tbl_customers] and t1.uid = t2.uid
>> >
>> > but get this message:
>> > Server: Msg 107, Level 16, State 2, Line 1
>> > The column prefix 'mydatabase.dbo' does not match with a table name or
> alias
>> > name used in the query
>> >
>> > thx a lot
>> >
>> > "JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
>> > news:Xns968BC0C2DE4E1daveJTC@.213.123.26.234...
>> >> "nicholas" <murmurait1@.hotmail.com> wrote in
>> >> news:Ol2emWkgFHA.1948@.TK2MSFTNGP12.phx.gbl:
>> >>
>> >> > While exporting a database with "export > objects > selecting the
>> >> > views" I get the error:
>> >> > Invalid object name 'dbo.myview'
>> >> >
>> >> > The weird thing is that this view allready exists in the destination
>> >> > dbase.
>> >> >
>> >> > Any help appreciated.
>> >> >
>> >> > THX
>> >> >
>> >> >
>> >> >
>> >>
>> >> Run the following query in your user database. What are the results?
>> >>
>> >> select t1.name, t2.name
>> >> from sysobjects t1, sysusers t2
>> >> where t1.name = 'myview' and t1.uid = t2.uid
>> >>
>> >> --
>> >> Regards
>> >> JTC ^..^
>> >
>> >
>
Friday, March 9, 2012
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.
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.
Saturday, February 25, 2012
Problem creating a Foreign key Constraint
Hello, I'm having some problems trying to create this foreign key constraint:
ALTER TABLE dbo.t2_demaclie
ADD CONSTRAINT FK03_T2_DEMACLIE FOREIGN KEY (dclPerfilCompania, dclOrdenPedido)
REFERENCES DBO.T2_PEDIDOCLIENTE (cdPerfilCompania, nmOrdenPedido)
Server: Msg 547, Level 16, State 1, Line 1
ALTER TABLE statement conflicted with TABLE FOREIGN KEY constraint 'FK03_T2_DEMACLIE'. The conflict occurred in database 'Comfruta_dllo', table 't2_pedidoCliente'.
I'm sure there's no other constraint with the same name, and there's no othe one with the same columns...
Thanks a lot !!
Check this first and see how you go. Something like this should do the trick (should give you zero rows):
select *
from t2_demaclie d
where not exists (select * from t2_pedidocliente p where p.cdPerfilCompania = d.dclPerfilCompania and p.nmOrdenPedido = d.dclOrdenPedido)
Rob|||Very useful ... Thank you so much !|||:) No problem.
Subscribe to:
Posts (Atom)