Create A View To Get Latest Status For Each Application

Apr 24, 2007

I would like some help creating a view that will display the latest status for each application. The lastest status should be based on CreateDt. 

For example:

Table Structure:

============

Application: ApplicationID, Name, Address, City, State, Zip, etc..

ApplicationAction: ApplicationActionID, ApplicationID, Status (Ex:new, reviewed, approved, closed), CreateDt 

  

View should display:

==============

ApplicantID, ApplicantActionID, Status, CreateDt  

 

Example:

==========

ApplicantID=4, Name=Bob Smith, etc....

ApplicantActionID=1,  ApplicantID=4, Status=New,  CreatDt=1/3/20071:00

ApplicantActionID=2,  ApplicantID=4, Status=Reviewed,  CreatDt=1/3/2007 2:00

ApplicantActionID=3,  ApplicantID=4, Status=Approved,  CreatDt=1/4/2007 1:00

 .... etc....

View should return:

Applicant=4, ApplicantActionID=3, Status=Approved,  CreatDt=1/4/2007 1:00

etc....

 

 

 

 

View 4 Replies


ADVERTISEMENT

Create A View That Will Give Most Current Status (by Statusdatetime) Of Each Order Number

Jan 20, 2015

We are having trouble figuring out how to create a view for this scenario:

We have a status log table that holds an order number, statusdatetime, and statuscode. This table will have multiple status' for the same order number. I want to create a view that will give me the most current status (by statusdatetime) of each order number. This view would show: order number, statusdatetime, and statuscode.

Here is a sample of the data:

Order numberStatusDateTimeStatusCode
1234512/15/2014 15:00CREATE
1234512/15/2014 16:30CONFIRMED
4567812/16/2014 08:00CREATE
9876412/18/2014 12:00CREATE
9876412/19/2014 08:00CONFIRMED
4567812/17/2014 09:30CONFIRMED
4567812/19/2014 15:30IN-TRANSIT

So my view should result in :

Order numberStatusDateTimeStatusCode
1234512/15/2014 16:30CONFIRMED
9876412/19/2014 08:00CONFIRMED
4567812/19/2014 15:30IN-TRANSIT

View 4 Replies View Related

Unable To Synchronize To The Latest Status

Mar 21, 2007

Greeting guys, does anyone here experience the problem that I had currently. I did set up a mirroring for my db for 1 month + and work perfectly. However, due to lack of stability on line connection on the principal database side, so makes mirror side get disconnected frequently. I found that once the principal side get back smooth, the mirroring status shows 'synchronizing' status and I thought it gets fine again.

However, after 2 days (which more than 48 hours) and I check back the status, it still showing 'synchronizing' and synchronize slowly. It's impossible that the mirror side takes more than 2 days for synchonizing. ( Size on principal size is around 1.2GB and mirror side around 800MB) Is there any solution on it ? Is it have to re-set up the mirroring session again? I don't think is line connection issue because I did set up another mirroring session between these two servers and work fine. Hope you guys can help me out asap. Thx.

Best Regards,

Hans

View 3 Replies View Related

Want To Make ''Get Latest Status'' Function More Efficient

May 14, 2007

Hey folks, I'm looking at making the following query more efficientpotentially using the ranking functions and I'd like some advice fromthe gurus. The purpose of the following is to have a status for aperson, and also have a historical background as to what they've done,status wise. This was the best way I could come up with to do this afew years back, but I'm wondering if there's a better way with Sql2005.Here's a toned down structure and my query. Any help/critique wouldbe greatly appreciated.CREATE TABLE #Status(StatusID int NOT NULL,StatusName VARCHAR(50) NOT NULL,StatusCategoryID int NOT NULL) ON [PRIMARY]CREATE TABLE #RegStatus([RegistrationID] [uniqueidentifier] NOT NULL,[StatusID] [int] NOT NULL,[StatusTimeStamp] [datetime] NOT NULL,[UniqueRowID] [int] IDENTITY(1,1) NOT NULL) ON [PRIMARY]SET NOCOUNT onINSERT INTO #Status VALUES(200, 'StatusA', 1)INSERT INTO #Status VALUES(210, 'StatusB', 1)INSERT INTO #Status VALUES(115, 'StatusC', 1)INSERT INTO #Status VALUES(112, 'StatusD', 1)INSERT INTO #Status VALUES(314, 'StatusE', 1)INSERT INTO #Status VALUES(15, 'StatusF', 1)INSERT INTO #Status VALUES(22, 'StatusG', 1)INSERT INTO #Status VALUES(300, 'StatusX', 2)INSERT INTO #Status VALUES(310, 'StatusY', 2)INSERT INTO #Status VALUES(320, 'StatusZ', 2)INSERT INTO #RegStatus VALUES('7A6058D0-06CB-4E83-A8C4-B1AFC74B11F0',200, GETDATE())INSERT INTO #RegStatus VALUES('7A6058D0-06CB-4E83-A8C4-B1AFC74B11F0',210, GETDATE())INSERT INTO #RegStatus VALUES('7A6058D0-06CB-4E83-A8C4-B1AFC74B11F0',115, GETDATE())INSERT INTO #RegStatus VALUES('8B94A666-A3DD-4CB1-89A0-9910047AE7A0',112, GETDATE())INSERT INTO #RegStatus VALUES('8B94A666-A3DD-4CB1-89A0-9910047AE7A0',314, GETDATE())INSERT INTO #RegStatus VALUES('8B94A666-A3DD-4CB1-89A0-9910047AE7A0',200, GETDATE())INSERT INTO #RegStatus VALUES('8B94A666-A3DD-4CB1-89A0-9910047AE7A0',22, GETDATE())INSERT INTO #RegStatus VALUES('58AF0FC6-C900-4BD0-B3F7-F9D62701F021',15, GETDATE())INSERT INTO #RegStatus VALUES('58AF0FC6-C900-4BD0-B3F7-F9D62701F021',115, GETDATE())INSERT INTO #RegStatus VALUES('58AF0FC6-C900-4BD0-B3F7-F9D62701F021',200, GETDATE())INSERT INTO #RegStatus VALUES('58AF0FC6-C900-4BD0-B3F7-F9D62701F021',115, GETDATE())SET NOCOUNT Off/*This is a query from within a function that I use to not only get thelateststatus for one registrant, but I can use it to get the latest statusfor everyone as well.*/DECLARE @RegStatusCatID int,@RegID UNIQUEIDENTIFIERSET @RegStatusCatID = 1SET @RegID = nullselect LS.*, S.StatusName, S.StatusCategoryIDfrom #Status Sjoin(select RS.RegistrationID, RS.StatusID, RS.StatusTimeStampfrom #RegStatus RSjoin(SELECT RS.RegistrationID , max(RS.UniqueRowID) UniqueRowIDFROM #RegStatus RSjoin #Status Son RS.StatusID = S.StatusIDand S.StatusCategoryID = @RegStatusCatIDand (@RegID is nullor (@RegID is not nulland RS.RegistrationID = @RegID))group by RS.RegistrationID)LSon RS.UniqueRowID = LS.UniqueRowID) LSon S.StatusID = LS.StatusID--SELECT * FROM #RegStatusDROP TABLE #RegStatusDROP TABLE #Status

View 4 Replies View Related

T-SQL (SS2K8) :: Return Value In A Status Field Which Has Latest Year And Month

May 11, 2015

I have table in which month & year are stored, Like this

Month Year
10 2014
11 2014
12 2014
1 2015
2 2015
3 2015
4 2015

I wanted a query in which it should return the value in a status field which has latest year & month.

View 9 Replies View Related

Passing Status Back From Command Line Application To SQL Job

Jun 4, 2007

Hi,



I have created a job in SQL Server 2005 in which one of the step executes a .NET console application which is created in .NET to update some status to database before the next step. i need some help in sending some status back to sql job when i come accross any problem in the console application for ex when there is a exception i need to send some status to the job, so the job gets failed permanently. i tried few other methods of updating some temporary status database with this error information and have another intermediate step in the job to check for the status... it worked but i dont like doing it. please let me know if there is any other method to do this.



Thanks in advance

Hariharan

View 5 Replies View Related

How To Create Merge Replication With Latest Update Always From Subscribers

May 13, 2006

Hi ,

I am trying to create Replication Topology (Merge Replication) like below.

Subscriber1 --> Publisher <-- Subscriber2.

I have created both subscribers with Subscription Type as Server with Priority as 75. I am updating the Column A of Row_10 in Subscriber1 on time say 11 am. After i am running the Starting synchronizing agent from Subscriber1. The value propagated to Publisher now publisher contains the latest value in Column A. Uptonow the Subscriber2 is not synchronized with Publisher.

Now in Subscriber2 also Column A of Row_10 is updated say at 11.10 am. Actually now Publisher contains the value from Subscriber1 for that Column and in Subscriber2 we have the same column updated.

Now i am running the Synchronization in Subscriber2, i am getting the result which is not expected. Here Publisher's value is propagated to Subscriber2. But as per real scenario Subscriber2 has the Latest value which is updated on 11.10 AM.

I don't know what am i missing here. Actually merge replication should see the time stamp and it has to decide winner. But here it always considers publisher as a winner and puts the data to Subscriber.

Can anyone help ?

Thanks in advance

View 4 Replies View Related

Transact SQL :: Create A Report Which Would Give Latest Transaction On Database

Jul 17, 2015

I'm trying to create a report which would give the latest transaction on a database, which all sit on different servers. I wanted to know if there is an a simple way tracking the latest transactions instead of getting the information from the database tables.

View 3 Replies View Related

Create Output Which Just Reflects Latest Conditions When Joining Back To Contract

Oct 10, 2013

col 1 is the pk for this table called Conditions - this table is related to contract.

Col 1 - PKContract TypeType ID
973300 711917 C30
973301 711917 C32
973302 711917 C31
1152323 711917 C30
1152324 711917 C31
1152325 711917 C32

A contract can many conditions so 1:M...Col 2 is the contract reference and the linking join to contract

Now - a condition related to an contract can have many re-trys and the causes the pk to increment. As you can see there are three conditions related to first attempt and then another three conditions.I want to create an ouput which just reflects the latest conditions when joining back to contract - Is this possible?I have requested to application providers to provide flag, but this will take some time...

View 8 Replies View Related

Write A CREATE VIEW Statement That Defines A View Named Invoice Basic That Returns Three Columns

Jul 24, 2012

Write a CREATE VIEW statement that defines a view named Invoice Basic that returns three columns: VendorName, InvoiceNumber, and InvoiceTotal. Then, write a SELECT statement that returns all of the columns in the view, sorted by VendorName, where the first letter of the vendor name is N, O, or P.

This is what I have so far,

CREATE VIEW InvoiceBasic AS
SELECT VendorName, InvoiceNumber, InvoiceTotal
From Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID

[code]...

View 2 Replies View Related

Create Status Table

May 16, 2006

I have >200 tables and I want to create a table that lists the name ofeach table, the number of records, and the number of locations withinthe table.I've created a cursor to do this but it doesn't like it. I get thefollowing error.Invalid column name '<tablename>'.Here's my scriptDECLARE @tbl varchar(100)DECLARE @sql varchar(1000)-- Insert statements for procedure heredeclare c_table cursor forselect table_name from INFORMATION_SCHEMA.TABLES where table_type ='base table' order by table_nameopen c_tablefetch next from c_table into @tblwhile (@@fetch_status = 0)beginset @SQL = 'INSERT INTO [zzTable_Status]SELECT ('+ @tbl +') as tblname, count(distinct station__no),count(station__no)FROM [bronze_views].'+@tbl+''exec (@SQL)Print @tbl + ' Updated'fetch next from c_table into @tblendclose c_tabledeallocate c_tableAny help is appreciated...

View 13 Replies View Related

Problems On Create Proc Includes Granting Create Table Or View Perissinin SP

Aug 4, 2004

Hi All,

I'm trying to create a proc for granting permission for developer, but I tried many times, still couldn't get successful, someone can help me? The original statement is:

Create PROC dbo.GrantPermission
@user1 varchar(50)

as

Grant create table to @user1
go

Grant create view to @user1
go

Grant create Procedure to @user1
Go



Thanks Guys.

View 14 Replies View Related

Grant CREATE VIEW, CREATE PROCEDURE ...

Apr 12, 2006

Hi,

I have currently a problem with setting up the permissions for some developers. My configuration looks like this.

DB A is the productive database.

DB B is a kind of "development" database.

Now we have a couple of users call them BOB, DAVID, ...

who are members of the db role db_reader and db_writer for the productive db a but they should be allowed to do nearly everything on db b.

Therefor I added them to the db role db_owner for db b.

For testing purposes I tried to "CREATE" a view TEST as BOB in database B but I received the error message

'Msg 262, Level 14, State 1, Procedure Test, Line 3

CREATE VIEW permission denied in database 'b'.'

I cross checked the permissions on db level and I even granted all available permissions on db level but nevertheless I receive this error message.

What's my mistake?

Of course it worked fine when I give them sysadmin rights but then they have far too much permissions.

Regards,

Stefan

View 8 Replies View Related

Alter View / Create View

Aug 14, 2000

I had given one of our developers create view permissions, but he wants to also modify views that are not owned by him, they are owned by dbo.

I ran a profiler trace and determined that when he tries to modify a view using query designer in SQLem or right clicks in SQLem on the view and goes to properties, it is performing a ALTER VIEW. It does the same for dbo in a trace (an ALTER View). He gets a call failed and a permission error that he doesn't have create view permissions, object is owned by dbo, using both methods.

If it is doing an alter view how can I set permissions for that and why does it give a create view error when its really doing an alter view? Very confusing.

View 1 Replies View Related

SQL 2012 :: Create A Basic Report That Displays UserID / Username / Email / Status?

Sep 24, 2014

I need to create a basic report that displays the userid, username, email, status and need to update the status column to enabled when we select any particular username. And the username parameter comes from the available users from the database. How to do this?

Do I need to mention the where clause where username=@username in the query or we don't need any

Also I need to get the available distinct usernames from the database while entering the parameter username

/*DECLARE variables to use IN queries*/
DECLARE @intErrorCode INT
DECLARE @ErrorMessage VARCHAR(MAX)
DECLARE @UserID INT
SET @ErrorMessage = 'Completed.'
SELECT [userid] ,

[code].....

Basically first I need to find the userid, username, accountstatus, email for a given particular user. Then I need to update the accountstatus to enabled for that selected user.

Also I need to create auditing that from before update and after update the accountstatus.

View 1 Replies View Related

Application Role How Query A View Whose Reference Table Is In Another Db?

Aug 23, 2007

Hi everybody.
I created an application role in a database (DB1) and gave it all the rights on a view in DB1 which refers to a table located in another db (DB2). I also gave the rights to the app role on a table of DB1
I tried to use this app. role through the sp_setapprole launched by a user (server principal?) which is SQL Server administrator (and local administrator (Win 2003 Server)).
With the following query
SELECT USER_NAME()

I see that the approle is being used.
Than, if I query the table on DB1 everything works, but if I query the view, referring a table in db2 I get following error:


The server principal "NameOfServerPrincipal" is not able to access the database "DB2" under the current security context.
What should I do to make it work?

The table in DB2 has the same schema of the view in DB1 which refers to it.
I put the DB1 TrustWorthy and both the database have the db_chaining option activated.

Any idea on how to solve the problem would be widely appreciated.
Thank you very much.
Vania

View 5 Replies View Related

See Application Users (View Server State) Within A Stored Procedure

Mar 17, 2008

Hello, i have a problem regarding stored procedures and view server state.

I have an application with a lot of stored procedures, one of them checks data of the connected users.
In SQL 2000 i had no problem getting this information, but in SQL server 2005 i do.

my stored procedure looks like this:


ALTER PROCEDURE [dba].[applsp_GetConnectionInfo]

(

@DBName varchar(100)

)

WITH EXECUTE AS OWNER AS

BEGIN

SET NOCOUNT ON



DECLARE @sCollationMaster VARCHAR(128);

DECLARE @sSqlString VARCHAR(900);



-- Determine collation from master database because collation from master and ultimo database may differ

SELECT @sCollationMaster = CAST(databasepropertyex('master', 'Collation') AS VARCHAR);



SET @sSqlString =

'SELECT max(status) AS Status, max(isnull(SCISUSENAME, ''ULTIMOLOGIN'')) AS Login

, MAX(Rtrim(Rtrim(convert(varchar(255), nt_domain)) + nt_username)) AS NTUser

, max(Rtrim(hostname)) AS Host, MAX(Rtrim(program_name)) AS Program

FROM master.dbo.sysprocesses JOIN dba.SCONNECTIONINFO on SCISPID = CAST(spid AS VARCHAR)

AND ( SCISUSENAME = ISNULL(loginame, '''') COLLATE ' + @sCollationMaster + ' OR ISNULL(loginame, '''') = ''ULTIMOLOGIN'')

WHERE ...... AND DB_NAME(dbid) = ''' + @DBName + '''

GROUP BY hostprocess

ORDER BY Login

';



EXEC(@sSqlString);

END

I've granted view server state permissions to my user 'dba' which is the db_owner.
When i execute the query in the stored procedure seperatly as dba i get all the info i need, but when i execute the stored procedure i don't see anything.

I seem to have the same problem with sp_who2
Executing it gives me information about everyone but when i put in a stored procedure like this:

alter procedure test

with execute as owner as

begin


EXEC sp_who2

end
I just see information about myself


View 5 Replies View Related

Create Table From Web Application

Jan 27, 2006

i am creating a model in which the control panel i am building for a web application, i can write sql statements in a textbox and the query will be done in my application. But i am have a problem creating a table.
this error of "Create table permission not granted in the sql server" pops up whenever i want to execute the create table sql statements from my web page.
How can i permit my sql server to allow creation of tables from my web application.
Thanks

View 3 Replies View Related

Create Audit That Goes To Application Log

Apr 11, 2014

Trying to create an audit that goes to the application log. With a certain criteria, select, update, delete etc.. where like 'example'-- some criteria..is there any reference out there that could give me some examples?

View 3 Replies View Related

Create Groups For An Application

Jun 1, 2006

I have a VB application using an SQL server database. I've created two groups in SQL Server for the database, one called APPS Users and the other called APPS Admin. So far, so good. However, when I place users into the APPS Admin group, it seems that besides getting to my application via a connection string, they also get complete access to my SQL Server. I don't want them to have total access to my SQL Server, only to the application's database.

In short, I want to limit them to nothing more than my application and its related database. In my application, these APPS Admin users will see a different group of menus than the regular users.

Since some of these APPS Admin users are also DBO's, how can I set up my group to limit them?

Hope I made my plight clear.... any help offered is most appreciated! Thank you!

Wherever you go, there you are!

View 18 Replies View Related

When To Consider To Create A Dedicate DB For Log Msg In ONe Application

Dec 17, 2007

I am always not sure whether to consider create another DB for Logging usage.

As my habit, i love to create 2 DBs for one apllication, for example the applaication name is HHH, i would love to create two DBs named HHH and HHHLog.
For HHH, recoording any transaction , for HHHLog, it's only for log alll knids message and archive some transaction tables monthly.

Is this good?
or just one DB is OK.


Give some feedback, thanks.

View 3 Replies View Related

Which Sort Of Data (from Application Architecture) Point Of View It's Worth To Put In XML Datatype (MS SQL 2005)?

Mar 20, 2008

Which sort of data (from application architecture) point of view it's worth to put in XML datatype (MS SQL 2005)?

View 2 Replies View Related

Could Not Create DTS.Application Because Of Error 0x800401F3

Apr 21, 2006

I am trying to run an ssis package from a classic asp web page.

if I run it from the command line it works as expected.

dtexec /f D:publishmypackage.dtsx

however if I try to run it from the web page - I get the following error:

"Could not create DTS.Application because of error 0x800401F3"

I am assuming this is a permissions error - can anyone help?

Can I run a dts package from classic asp?







View 3 Replies View Related

Create Database With Application Role...

Mar 22, 2007

Hi,

I want my application to create database and I do the following things:

1)Create application role
2)Grant create database to application role
3)Activate application role
4)Create database

and I get the answer:

CREATE DATABASE permission denied in database 'master'.

View 1 Replies View Related

Is It Possible To Create Report Subscriptions From A Windows Application

Nov 29, 2007

from with in an application using the report view can you puase a report that is taking a long time to complete and then at a latter date resume where you paused the report.

View 6 Replies View Related

Application Locks Up When Trying To Write To Db After A Replication Where I Create The Database.

Jan 3, 2008

Hello all,

When I create a new database and replicate to it using BeginMonitoredBackgroundSync :







Code Block

public void BeginMonitoredBackgroundSync(string User)
{
CreateReplicationInstance(User);
repl.BeginSynchronize(
OnSimplifiedSynchronizeComplete,
SqlCeReplication_OnStartTableUpload,
SqlCeReplication_OnStartTableDownload,
SqlCeReplication_OnSynchronization,
repl);
}




private void CreateReplicationInstance(string User)
{
repl = new SqlCeReplication();
string host = repl.HostName;
repl.HostName = User;
string dbFilePath = "";


dbFilePath = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase) +
"\" + repl.HostName + ".sdf";
string myConnectionString = string.Format("Data Source = {0};PWD = {1}", dbFilePath, sqlSettings.Items["SqlPassword"]);

repl.InternetUrl = dynamicsReplicationSettings.ReplicationSettingsItems["InternetUrl"];
repl.PublisherSecurityMode = SecurityType.DBAuthentication;
repl.Publisher = dynamicsReplicationSettings.ReplicationSettingsItems["Publisher"];
repl.PublisherDatabase = dynamicsReplicationSettings.ReplicationSettingsItems["PublisherDatabase"];
repl.PublisherLogin = dynamicsReplicationSettings.ReplicationSettingsItems["PublisherLogin"];
repl.PublisherPassword = dynamicsReplicationSettings.ReplicationSettingsItems["PublisherPassword"];
repl.Publication = dynamicsReplicationSettings.ReplicationSettingsItems["Publication"];

repl.Subscriber = "RemoteSubscription" + repl.HostName;
repl.SubscriberConnectionString = myConnectionString;

repl.ConnectionRetryTimeout = 120;
repl.LoginTimeout = 120;
repl.CompressionLevel = 6;

if (File.Exists(dbFilePath))
{
FileInfo info = new FileInfo(dbFilePath);
if (info.Length <= 20480)
{
File.Delete(dbFilePath);
repl.AddSubscription(System.Data.SqlServerCe.AddOption.CreateDatabase);
}
}
else
{
repl.AddSubscription(System.Data.SqlServerCe.AddOption.CreateDatabase);
}

primeConnection();

}
After the replication finishes, I dispose the replication object like so:





Code Block

void OnAsyncSynchronizeComplete(IAsyncResult asyncResult)
{

try
{
repl.EndSynchronize(asyncResult);
if (repl != null)
{
repl.Dispose();
repl = null;

}
if (ReplicationComplete != null) ReplicationComplete(this, true);
}
catch(Exception ex)
{
if (ReplicationComplete != null) ReplicationComplete(ex, false);
}

}

Then later, if I try to update, insert or delete to the database, the application will hang. I can read from it, but I cannot write. If I close the application down and open it back up without replicating, I will not get any lockups. It also will not hang up after any replications prior to the create replication. I think I am doing something wrong in the initial replication that is holding on to some connection to the DB causing it to lock up. Has anyone seen anything like this before?

View 1 Replies View Related

Unable To Create A Local Database To Winform Application

Sep 27, 2007



I saw a video on how its possible to create a local database so that when the application is being deployed, the mdf file will be deployed with it and will contain the enter data. The instructor(Beth Messi) showed that all I need to do is to add the .mdf file using the "Rightclick Project name in solution explorer > select add > add new item > then in the dialog that opens, select SQL database and click ok. I did this but the Visual Studio kept saying: "An error occored while extablishing a connection with the server. When connecting SQL Server 2005, this failure may have been caused by the fact that under the default settings of SQL Server does not allow remote connections. (Provider: Shared memory provider, error: 40 - could not open a connection to SQL Server)"

Honestly, I don't know how to go futher with this. The SQL server am using is the professional edition and I have been able to use it through Visual Studio to create Databases. It connects alright in that senario. Please I really need help here.

View 1 Replies View Related

Can An Authorized User Like SA Create A Table Which The Owner Is Any Users Who Use The Application?

Aug 20, 2007

Hi,

autorized user: sa
any user : nuran
temporary table: birtablo

I need a stored procedure will execute by sa and it will create some required temporary tables for each users. For example table name is birtablo. I mean sa will create table for nuran, and when I checked the owner of the table (birtablo) I want to see nuran not dbo.
sa will execute following command:

create table nuran.birtablo (...........)

Is it possible to cerate a table by sa on behalf of any user? If it is, could you please explain?



Thanks

Nuran

View 5 Replies View Related

Create View

Jan 8, 2001

When I use following command it works fine.

CREATE VIEW dbo.[ikw_custom]
AS
SELECT * FROM rns_keyword.dbo.ikw_custom

but if I use following command with condition it gives me an error.

if not exists(select * from sysobjects where name ='ikw_custom' and type='V')
begin
CREATE VIEW dbo.[ikw_custom]
AS
SELECT * FROM rns_keyword.dbo.ikw_custom
end

help will be appriciated.
Thanks in advance
-Balbir

View 1 Replies View Related

Create View

Apr 15, 2006

I am learning how to work with MSSQL
here is what I like to do
I have a table with URLs like this "www.urls.com" I need to create a view that will insert this "http://" before "www.urls.com" can some please let me know what I need to do
Thank you

View 14 Replies View Related

Create An SQL View From VBA

Sep 25, 2005

Hello All;

I'm new to SQL, and developing my first application using SQL 2000 as the back end for my data.

Can anyone tell me if I can create an SQL View from VBA?

I'm trying to create a data view that access various records from a single table, and it has to distribute that data 2 14 different subforms, representing different days in a 2 week period, which is distingiushed by a field called "Day", that is numbered from 1 to 14.

I also have a summary subform that has the weekly summary (days 1 to 7 and 8 to 14) on seperate subforms. In total I have 16 subforms, which actually source from a single table, just split depending on the day in the period.

As I see it now, creating views iis the best way for me to go, but I also have to be able to change the period id on the fly, so I'm think I have to use VBA to generate these views.

Does any of this make sense, and am I on the right track??

View 2 Replies View Related

Create View

Mar 18, 2006

hi,if the below query
select DISTINCT group_module_menu_info.menulink FROM
user_group_role_info,group_role_module_info,group_ module_menu_info
where user_group_role_info.userid=user_table.userid and
((group_module_menu_info.moduleid = user_group_role_info.moduleid)OR
(user_group_role_info.roleid=group_role_module_inf o.roleid and
group_module_menu_info.moduleid = group_role_module_info.moduleid))


if i want to create a view
how ?
thank you!

View 5 Replies View Related

Create A View NEED HELP!!

Jan 26, 2006

Create a view showing every order that was shipped to Spain. Name the destination column "DestinationSpain". Include code that checks if the view already exists. If it does, it should be dropped and re-created.

Use Northwind
Go
DROP VIEW Spain_Orders
Go
CREATE VIEW Spain_Orders
AS
SELECT *
FROM Orders
WHERE ShipCountry = 'Spain'

this is what I get.

server: Msg 3701, Level 11, State 5, Line 1
Cannot drop the view 'Spain_Orders', because it does not exist in the system catalog.


albanie

View 20 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved