Dowt in create View

I want to create a view from dynamic tables

Query:

CREATE VIEW dbo.TrialView
AS
SELECT *
FROM (select name from dbo.sysobjects where name like 'scTabForm%' and type='u') p

Output:
Name
scTabForm0
scTabForm1

But I want all the fields from table starts with 'scTabForm'

like: select * from scTabForm0,scTabForm1,...

Regards
RaabuI think I know what you are getting at, but could you please clarify for me just a little bit? You have an unknown number of tables, with unknown structure. You want to create a view that will combine these and rename the columns to append the table they came from?

doubt with sql and an store procedure

I'm working with the stored procedure editor of the Visual Web Developer, and I have this:
ALTER PROCEDURE dbo.ClasificacionSelectTodas
(
@.visibilidad bit = 1
)
AS
SET NOCOUNT ON
SELECT Clas_Id, Clas_Madre, Clas_Titulo, Clas_Descripcion, Clas_Prioridad, Clas_Visibilidad
FROM Clasificacion
WHERE (Clas_Visibilidad = @.visibilidad)
As you see, if I don't set any value to "@.visibilidad" it returns me the registries where visibilidad = 1
But what I want is that if I don't set any value to visibilidad, itturns to me all the registries. I was trying giving a default value tovisibilidad like "*" o "?", but it gives me an error.
Any idea?
Thanks
ALTER PROCEDURE dbo.ClasificacionSelectTodas
(
@.visibilidad bit = null
)
AS
SET NOCOUNT ON
SELECT Clas_Id, Clas_Madre, Clas_Titulo, Clas_Descripcion, Clas_Prioridad, Clas_Visibilidad
FROM Clasificacion
WHERE (Clas_Visibilidad = IsNull(@.visibilidad,Clas_Visibilidad))
This will compare Clas_Cisibilidad to the variable passed in (if not null) or with itself (which will always be true).|||Thanks douglas, it works great
Congratulations for your book!
|||Glad it helped.
Also, I am actually working on a new book, tentatively titledStreamlined Web Forms Development. Should be out by ASP.NET 2.0 release, and will make a great stocking suffer<g>.|||I'm sure it will be highly interesting, I'll wait for it ;)

Doubt on unique non clustered Index

Hi All,
I have a table with data:
CREATE TABLE [dbo].[Relation] (
[ID] [uniqueid] IDENTITY (1, 1) NOT NULL ,
[PersonID] [uniqueid] NOT NULL ,
[CurrentID] [uniqueid] NOT NULL ,
[RelativeName] [varchar] (50) NOT NULL ,
)
Here there are duplicate records for a combination of PersonID, CurrentID
and RelativeName.
I need to create a unique non clustered index on PersonID, CurrentID and
RelativeName.
But it throws an error saying duplicate values...
Why I want to create such an index is to stop inserting further duplicates
and the old data is important for me.
Is there any other way i can accomplish this?
Thanks,
PradYou ought to be able to get rid of the duplicates, otherwise your table
lacks integrity because of the redundant data. Why do you say that the old
data is important to you? In what way?
First, you'll have to fix any foreign key references:
UPDATE foo
SET id =
(SELECT MIN(R2.id)
FROM Relation AS R1
, Relation AS R2
WHERE R1.id = foo.id
AND R1.personid = R2.personid
AND R1.currentid = R2.currentid
AND R1.relativename = R2.relativename) ;
Then delete the duplicates:
DELETE FROM Relation
WHERE EXISTS
(SELECT *
FROM Relation AS R
WHERE R.personid = Relation.personid
AND R.currentid = Relation.currentid
AND R.relativename = Relation.relativename
AND R.id < Relation.id) ;
Now you can add the unique constraint.
Hope this helps.
David Portas
SQL Server MVP
--|||Hi
Maybe you should be changing/consolidating the data related that makes this
non-unique. This does not necessarily mean deleting it, for example having a
alternateids table would mean that only one id needs to exist in the "master
"
table.
John
"Pradeep Kutty" wrote:

> Hi All,
> I have a table with data:
> CREATE TABLE [dbo].[Relation] (
> [ID] [uniqueid] IDENTITY (1, 1) NOT NULL ,
> [PersonID] [uniqueid] NOT NULL ,
> [CurrentID] [uniqueid] NOT NULL ,
> [RelativeName] [varchar] (50) NOT NULL ,
> )
> Here there are duplicate records for a combination of PersonID, CurrentID
> and RelativeName.
> I need to create a unique non clustered index on PersonID, CurrentID and
> RelativeName.
> But it throws an error saying duplicate values...
> Why I want to create such an index is to stop inserting further duplicates
> and the old data is important for me.
> Is there any other way i can accomplish this?
> Thanks,
> Prad
>
>|||I prefer this one:
Delete from Relation
Where
[id] not in
(
SELECT MAX([id])
FROM Relation
GROUP BY [personid],[currentid],[relativename])
Regards,
"David Portas" wrote:

> You ought to be able to get rid of the duplicates, otherwise your table
> lacks integrity because of the redundant data. Why do you say that the old
> data is important to you? In what way?
> First, you'll have to fix any foreign key references:
> UPDATE foo
> SET id =
> (SELECT MIN(R2.id)
> FROM Relation AS R1
> , Relation AS R2
> WHERE R1.id = foo.id
> AND R1.personid = R2.personid
> AND R1.currentid = R2.currentid
> AND R1.relativename = R2.relativename) ;
> Then delete the duplicates:
> DELETE FROM Relation
> WHERE EXISTS
> (SELECT *
> FROM Relation AS R
> WHERE R.personid = Relation.personid
> AND R.currentid = Relation.currentid
> AND R.relativename = Relation.relativename
> AND R.id < Relation.id) ;
> Now you can add the unique constraint.
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>
>

dose not SQL-Server has Array data-type?

hi,
in SQL Server Books Online in sp_executesql page thay have this example:
execute sp_executesql
N'select * from pubs.dbo.employee where job_lvl = @.level',
N'@.level tinyint',
@.level = 35
i need to accomplish a similar task, but with un-predictable (may be dozens
of) numbers of parameters.
so, instead of doing something like this (with much more than two
parameters):
execute sp_executesql
N'select * from pubs.dbo.employee where job_lvl = @.level or
job_lvl = @.level2',
N'@.level tinyint , @.level2 tinyint',
@.level = 35
,@.level2 = 32
i want to do something like this:
execute sp_executesql
N'select * from pubs.dbo.employee where job_lvl in ( @.level ) ',
N'@.level ?? ',
@.level = 35,32
which does not work ,of course......
any ideas?
thanks,
edo
See if this helps:
http://vyaskn.tripod.com/passing_arr...procedures.htm
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"edo" <ewilde@.nana.co.il> wrote in message
news:efwHDCdhEHA.556@.tk2msftngp13.phx.gbl...
hi,
in SQL Server Books Online in sp_executesql page thay have this example:
execute sp_executesql
N'select * from pubs.dbo.employee where job_lvl = @.level',
N'@.level tinyint',
@.level = 35
i need to accomplish a similar task, but with un-predictable (may be dozens
of) numbers of parameters.
so, instead of doing something like this (with much more than two
parameters):
execute sp_executesql
N'select * from pubs.dbo.employee where job_lvl = @.level or
job_lvl = @.level2',
N'@.level tinyint , @.level2 tinyint',
@.level = 35
,@.level2 = 32
i want to do something like this:
execute sp_executesql
N'select * from pubs.dbo.employee where job_lvl in ( @.level ) ',
N'@.level ?? ',
@.level = 35,32
which does not work ,of course......
any ideas?
thanks,
edo

dose not SQL-Server has Array data-type?

hi,
in SQL Server Books Online in sp_executesql page thay have this example:
execute sp_executesql
N'select * from pubs.dbo.employee where job_lvl = @.level',
N'@.level tinyint',
@.level = 35
i need to accomplish a similar task, but with un-predictable (may be dozens
of) numbers of parameters.
so, instead of doing something like this (with much more than two
parameters):
execute sp_executesql
N'select * from pubs.dbo.employee where job_lvl = @.level or
job_lvl = @.level2',
N'@.level tinyint , @.level2 tinyint',
@.level = 35
,@.level2 = 32
i want to do something like this:
execute sp_executesql
N'select * from pubs.dbo.employee where job_lvl in ( @.level ) ',
N'@.level '? ',
@.level = 35,32
which does not work ,of course......
any ideas?
thanks,
edoSee if this helps:
http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"edo" <ewilde@.nana.co.il> wrote in message
news:efwHDCdhEHA.556@.tk2msftngp13.phx.gbl...
hi,
in SQL Server Books Online in sp_executesql page thay have this example:
execute sp_executesql
N'select * from pubs.dbo.employee where job_lvl = @.level',
N'@.level tinyint',
@.level = 35
i need to accomplish a similar task, but with un-predictable (may be dozens
of) numbers of parameters.
so, instead of doing something like this (with much more than two
parameters):
execute sp_executesql
N'select * from pubs.dbo.employee where job_lvl = @.level or
job_lvl = @.level2',
N'@.level tinyint , @.level2 tinyint',
@.level = 35
,@.level2 = 32
i want to do something like this:
execute sp_executesql
N'select * from pubs.dbo.employee where job_lvl in ( @.level ) ',
N'@.level '? ',
@.level = 35,32
which does not work ,of course......
any ideas?
thanks,
edo

Don't Know How To Explain It

The tables are as follows
CREATE TABLE dbo.Blogs
dId int * PRIMARY
posted datetime(8)
title nvarchar(50)
catgy nvarchar(50)
blogs nvarchar(500)
picId int
UName nvarchar(50)
CREATE TABLE dbo.BlogPix
bgId int * PRIMARY
picId int
Photo nvarchar(50)
Caption(50)
UName nvarchar(50)
I dont know how to explain it but this is the way i have the application
working. The UName on every table in the system is the User name of the User
and it returns rows filtered by UName = @.Uname. However this works fin on my
application but it gets tricky when i try to filter by picId. Ok, the picId
is not a autonumber and any user can get the same number at some time
because i am using the random class to generate a number from the smallest
INT32 to the biggest so i have big scope.
its so hard to explain what i mean so i will give an example,
SELECT TOP (30) *
FROM Blogs INNER JOIN
BlogPix ON Blogs.picId = BlogPix.picId
WHERE UName = @.UName AND PicId = @.PicId
basically i want to retrieve data by using the keyfields dId or picId which
ever way i can so i can return results like the following;
bgId !!!!!!! picId !!!!!!! Photo !!!!!!! Caption !!!!!!! UName
--1--99995688--~/pix/plig.jpg--im a minger--Sean--
--2--99995688--~/pix/pliglet.jpg--im a minger--Sean--
--3--99995688--~/pix/pligkil.jpg--im a minger--Sean--
--4--99995688--~/pix/plipim.jpg--im a minger--Sean--
--5--99995688--~/pix/plim.jpg--im a minger--Sean--
dId !!!!!!! picId !!!!!!! Title !!!!!!!!!!!!!!!!!! Blogs
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!Posted
!!!!!!!!!!! Catgy !!!!!!! UName
--1--99995688--Me On A Gud Day--Im going gud and life is great--21
Aug 2005--Music--Sean--
--2--2568777--Me On A Bad Day--Im going gud and life is great--21
Aug 2005--Music--Sean--
--3--12369875--Me On A Nasty Day--Im going gud and life is
great--21 Aug 2005--Music--Sean--
--4--56888999--Me On A Lovely Day--Im going gud and life is
great--21 Aug 2005--Music--Sean--
--5--5896633--Me On A Dark Day--Im going gud and life is great--21
Aug 2005--Music--Sean--
Hope this helps, i cant really explain what it is but i do knowI think you'll find your answers in the following article:
http://www.sommarskog.se/dyn-search.html
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Eamon Straughn" <blackmanvandam@.aol.com> wrote in message
news:uUu9KJCrFHA.2076@.TK2MSFTNGP14.phx.gbl...
> The tables are as follows
> CREATE TABLE dbo.Blogs
> dId int * PRIMARY
> posted datetime(8)
> title nvarchar(50)
> catgy nvarchar(50)
> blogs nvarchar(500)
> picId int
> UName nvarchar(50)
> CREATE TABLE dbo.BlogPix
> bgId int * PRIMARY
> picId int
> Photo nvarchar(50)
> Caption(50)
> UName nvarchar(50)
> I dont know how to explain it but this is the way i have the application
> working. The UName on every table in the system is the User name of the
User
> and it returns rows filtered by UName = @.Uname. However this works fin on
my
> application but it gets tricky when i try to filter by picId. Ok, the
picId
> is not a autonumber and any user can get the same number at some time
> because i am using the random class to generate a number from the smallest
> INT32 to the biggest so i have big scope.
> its so hard to explain what i mean so i will give an example,
> SELECT TOP (30) *
> FROM Blogs INNER JOIN
> BlogPix ON Blogs.picId = BlogPix.picId
> WHERE UName = @.UName AND PicId = @.PicId
> basically i want to retrieve data by using the keyfields dId or picId
which
> ever way i can so i can return results like the following;
> bgId !!!!!!! picId !!!!!!! Photo !!!!!!! Caption !!!!!!! UName
> --1--99995688--~/pix/plig.jpg--im a minger--Sean--
> --2--99995688--~/pix/pliglet.jpg--im a minger--Sean--
> --3--99995688--~/pix/pligkil.jpg--im a minger--Sean--
> --4--99995688--~/pix/plipim.jpg--im a minger--Sean--
> --5--99995688--~/pix/plim.jpg--im a minger--Sean--
> dId !!!!!!! picId !!!!!!! Title !!!!!!!!!!!!!!!!!! Blogs
> !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!Posted
!!!!!!!!!!! Catgy !!!!!!! UName
> --1--99995688--Me On A Gud Day--Im going gud and life is
great--21
> Aug 2005--Music--Sean--
> --2--2568777--Me On A Bad Day--Im going gud and life is great--21
> Aug 2005--Music--Sean--
> --3--12369875--Me On A Nasty Day--Im going gud and life is
> great--21 Aug 2005--Music--Sean--
> --4--56888999--Me On A Lovely Day--Im going gud and life is
> great--21 Aug 2005--Music--Sean--
> --5--5896633--Me On A Dark Day--Im going gud and life is
great--21
> Aug 2005--Music--Sean--
> Hope this helps, i cant really explain what it is but i do know
>

Dont Display dbo. in Object Explorer

Is there a way to stop SQL Server Management Studio (2005) from showing "dbo." on everything in the object explorer.

I'd love to turn that off on a database I'm working on as, for example, if you have to find tblMember, you'd have to type d-b-o-.-t-b-l-m before you can actually start jumping to the table you're interested in - whereas in Enterprise Manager (2000) you'd only have to type t-b-l-m if you get my meaning.

Hi there,

There no such option as to hide the owner of the datatable. Actually this is good because the owner is a very strong security feature SQL Server provides. You may want to have in mind who is the owner especially if you are going to host your application in a shared environment where all tables will be owned by your Sql user (which will definitely not be sa).
Sorry for bringing the bad news,
Cheers,

Andreas Botsikas