creating a view with columns not in another table

Hello,

I'm somewhat of a newbie and I need to create a view with a column
that is not derived from any other tables. What I want is for this
field to be an auto-increment field or some kind of UID. Can anyone
quickly tell me how to do this.

Here is my dilemma. I need to pull data from a unique data set from a
table which does not have a primary key, and none exists in its data.
Please tell me how to put this data in a view(or another table) so
that I can pull it out with unique records. It doesnt matter what the
unique field consists of.

ThanksAdd newid() to your select statement.

Select newid(),

This will create a UID for each line returned by the select statement.

margraft@.yahoo.com wrote in message news:<1db47099.0411231136.50d722c7@.posting.google.com>...
> Hello,
> I'm somewhat of a newbie and I need to create a view with a column
> that is not derived from any other tables. What I want is for this
> field to be an auto-increment field or some kind of UID. Can anyone
> quickly tell me how to do this.
> Here is my dilemma. I need to pull data from a unique data set from a
> table which does not have a primary key, and none exists in its data.
> Please tell me how to put this data in a view(or another table) so
> that I can pull it out with unique records. It doesnt matter what the
> unique field consists of.
> Thanks|||> Select newid(),
> This will create a UID for each line returned by the select statement.

If you do twice the request, the UID wont be the same... So it removes a lot
of the interest of the UID. The best thing to do would ne to add a primary
key to the table...

--
Vincent|||(margraft@.yahoo.com) writes:
> I'm somewhat of a newbie and I need to create a view with a column
> that is not derived from any other tables. What I want is for this
> field to be an auto-increment field or some kind of UID. Can anyone
> quickly tell me how to do this.
> Here is my dilemma. I need to pull data from a unique data set from a
> table which does not have a primary key, and none exists in its data.
> Please tell me how to put this data in a view(or another table) so
> that I can pull it out with unique records. It doesnt matter what the
> unique field consists of.

Using newid() as Mike suggested will give you a 16-byte binary value,
which may be somewhat difficult to manage. But it's certainly the
easiest method.

If you want an integer number, the best is probably to write a multi-step
table function where you select the data into a table with an IDENTITY
column, and then return that table.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

Creating a SQL statement

Hello all - newbie post forthcoming...

I have a page that is writing to a database (Access) and I am having problems getting the actual SQL statement to execute properly. The code in question is as follows:

sql = "INSERT INTO article (maincat, subcatid, subject, article)"
sql = sql & " Values ('"
sql = sql & quotes(Request.form("maincat")) & "', '"
sql = sql & quotes(Request.form("subcatid")) & "', '"
sql = sql & quotes(Request.form("subject")) & "', '"
sql = sql & quotes(Request.form("article")) & "')"

The response I get is... "Data Type Mismatch in Criteria Expression"

The post data does not have quotes around the data - I am thinking this is the problem.

Any help would be appreciated!Welcome to the ASP.NET Forums, mr_atlanta2kx.

Before you proceed any further, you must read the following:
Please, please, please, learn about injection attacks!|||Thank you for the heads up. This particular function is only available via a password and IP address protected page that is going to be used on an Intranet.|||Assuming subcatid is numeric -> don't use the 'quotes' function on this field.|||Use params, apart from lots of other goodies they take care of irratating things like SQL within a value (including single quotes).

Creating "installation" script

Hi all,

I′m newbie with SQL Server.

I need to create a installation script SQL
(creating tables, indexes, permissions, databases).
This script calls other scripts.
In Oracle I use @.script_name.sql, but in SQL Server
I don′t know to do this.

Other question: Is there a way to call the Query Analyzer
in .BAT file, where the .BAT file calls installation scripts SQL.

Thank U very much,

[]′s
Yes sure you can do this, you have to call the equivalent to sqlplus.exe for SQL Server, its OSQL.EXE. The -o Option takes an input file with the statements that you want to execute. Other options are described in detail -S for the server -U for the User -P for the password and so on.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Thank u very much!!!!

[]′s

Create view of inline function

Hello. I'm a real newbie - using Access 2003 front end and connecting to SQL Server 2005 ODBC.

I'm having trouble accessing functions through access. I've built the following function:

CREATE FUNCTION fnSTR_LEASESTATUS(@.TRS nvarchar(12))

RETURNS TABLE

AS

RETURN

(

SELECT dbo.tblTRACT.STR, dbo.tblTRACT.[TRACT_#], dbo.tblMIN_OWNERS.Min_Owner_Name AS [OWNER OF RECORD], dbo.tblLEASE_TRACTS.LOC_ID, dbo.tblLOCATION.LPR_No, dbo.tblLOCATION.Lease_ID, dbo.tblLEASE_LOG.Date_Mailed, dbo.tblLEASE_LOG.Scan_Lease_Received, dbo.tblLEASE_LOG.Orig_Lease_Recd, dbo.tblLPR_INVOICES.Invoice_No, dbo.tblLPR_PAY.CHECK_DRAFT_No, dbo.tblLESSORS.Name AS [Lease Name]

FROM dbo.tblTRACT LEFT JOIN ((dbo.tblMIN_OWNERS RIGHT JOIN dbo.tblTRACT_OWNER ON dbo.tblMIN_OWNERS.Min_Owner_ID = dbo.tblTRACT_OWNER.Owner_Lease) LEFT JOIN ((((((dbo.tblLPR RIGHT JOIN dbo.tblLOCATION ON dbo.tblLPR.LPR_No = dbo.tblLOCATION.LPR_No) LEFT JOIN dbo.tblLESSORS ON dbo.tblLPR.Lessor_Number = dbo.tblLESSORS.Lessor_Number) RIGHT JOIN dbo.tblLEASE_TRACTS ON dbo.tblLOCATION.LOC_ID = dbo.tblLEASE_TRACTS.LOC_ID) LEFT JOIN dbo.tblLEASE_LOG ON dbo.tblLPR.LPR_No = dbo.tblLEASE_LOG.LPR_No) LEFT JOIN dbo.tblLPR_INVOICES ON dbo.tblLPR.LPR_No = dbo.tblLPR_INVOICES.LPR_No) LEFT JOIN dbo.tblLPR_PAY ON dbo.tblLPR.LPR_No = dbo.tblLPR_PAY.LPR_No) ON dbo.tblTRACT_OWNER.TRACT__Owner_ID = dbo.tblLEASE_TRACTS.Tract_Owner_Id) ON (dbo.tblTRACT.[TRACT_#] = dbo.tblTRACT_OWNER.[TRACT_#]) AND (dbo.tblTRACT.STR = dbo.tblTRACT_OWNER.STR)

WHERE (((dbo.tblTRACT.STR)=@.TRS))

)

GO

I understand now I can create a view of the function Simply by using the function name in my FROM statement. However I get an error that arguments provided do not match parameters required. However, I'm not getting the prompt to enter my criterion. Is my error in my function statement? I can't save the view. I also understand I could use a pass-through query. Is there some sort of guidance or tutorial on that to which you could point me?

Thanks for your time.

You function has param @.TRS.

You could use it:

Code Snippet

SELECT * FROM fnSTR_LEASESTATUS('Your param value')

|||

Hi,

Let me try to address your concerns

There's no need to create a view of a table function, unless you want the view results somehow filtered or different than the function results.

To use the your table function simply SELECT * FROM fnSTR_LEASESTATUS(<put value here>)

If you want to create a view you need to have rights on the SQL Server 2005 database.

You are probably using an Access data project? Using these projects you are not able to just save queries as in regular Access database. With these projects you are connected directly to the server and therefore anything you save you are saving on the server.

You may want one project to create server objects like functions. Then have another regualr access database to save your queries (pass- through preferred)

|||

That allowed me to run it, but when I try to save it I get the message "Must declare the scalar variable @.TRS".

|||

Ok. I can run it in SQL Server, but how do I get to it in Access? I was hoping I could save it as a view and then link to it the way I have my other views.

Thanks.

|||

Oh, sorry. Forgot to tell you. I decided against ADP after reading on the Access user's group. So I build my views in SQL Server and then link to them.

Thanks.

|||To save your view in SQL Server you need to replace the variable reference, @.TRS, with an actual value. Once you have it saved in SQL Server, you can link like other view links|||And if you want to be prompted from Access, forget saving it as a view in SQL Server and try saving it as an Access query or some other way in Access