I am running some new queries in SQL 2012 (in SSMS) and, while slow, they do run. If I try to use the same query to create a view it persists in timing out in about 30 seconds. I see very little on this subject via google.
View 9 RepliesI have an Access app. that I am migrating the DB portion (queries, tables) to SQL server. I need to create a temp table that lasts as long as the user has the Access FE app. open. Idea is that the temp table stores the user's parameters (used for filtering data entry forms and report). The parameters allow the app. to only show the user his data (cannot view other users data). The SP shown below works OK, it creates a ##Temp table and updates it with the parameters sent by Access FE app. The issue I am having is that as soon as the SP finishes the ##Temp table is removed. I thought of using a regular table, but, I am currently testing this migration in my local SQL server instance, as soon as I move the database to production environment, then users will not be able to create tables as permissions are only read/write.
USE [Work_Allocation]
GO
/****** Object: StoredProcedure [dbo].[spUser_Parameters_update] Script Date: 9/30/2015 12:27:42 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spUser_Parameters_update]
[code]...
What could be the reason for my view to timeout? I thought it was because of the number or records, but i guessed wrong. The view is grabbing data from a UDF i have created.
View 1 Replies View RelatedI want to create a check constraint on a table but the constraint values depend upon another table column as well, now one possible way is to create a function to check the column value. But I don’t want to use the function.
Can I do this in a view if so then how can I achieve this.
I have two table studenTtable and courseTable which is each student take more than one course . 1:M...for example Student1 take 2 courses (C1 , C2). Student2 take 3 courses (C1,C2, C3).I need to create a table/View that contain student information from StudentTable plus all the courses and the score for each course from CoursTable in one row.
for example Row1= Student1_Id ,C1_code ,C1_name ,C1_Score ,C2_code,C2_name ,C2_Score Row2= Student2_Id,C1_code, C1_name,C1_Score,C2_code ,C2_name ,C2_Score , C3_code,C3_name,C3_Score
and since Student one just have two courses , I should enter NULL in 'Course 3 fields'.My Struggle is in the insert statement I tried the following but it show an error
Insert Into Newtable ( St_ID, C1_code,c1_name, C1_Score ,C2_code ,C2_name,C2_score,C3_code ,C3_name,C3_score)
Select (Select St_ID from StudentTable) , (Select C_code,c_name,c_Score from Coursetable,SudentTable where course.Stid =Studet.stid) , (Select C_code,c_name,c_Score from course ,student where course.Stid =Studet.stid ), (Select C_code,c_name,c_Score from course ,student where course.Stid =Studet.stid );
I'm fully aware that the New table/View will break the rules of normalization ,but I need it for specifc purpose.I tried also the PIVOT BY functionality but no luck with it .I also tried writing a code using Matlab (because it is high level sw that it is easy to learn for people not expret in programming as me) but didn't know how to combine the Student and Courses Matrices in my loop.
I have a table like below:
ItemIdAmountTax1Tax2SrvType
111 100 10 20 1
112 200 10 2
113 300 10 30 3
Now I want to create View that will have an exploded resultset based on SrvType.
For SrvType 1 and 2 there will be 2 lines per Itemid - One for 'Amount' anod another for 'Tax1+Tax2'. But for SrvType 3 there will be 3 lines per 'ItemId' - one for 'Amount', one for Tax1 and another for 'Tax2'.
I have a few hundred source records like this. Now sure how to achieve the exploded resultset with a View.
I like to create an SQL view to divide amount 300,000 between 12 month starting from Month July 2014 to June 2015 as shown below
Amount Month Year
25,000 July 2014
25,000 August 2014
25,000 September 2014
25,000 October 2014
25,000 November 2014
25,000 December 2014
25,000 January 2015
25,000 February 2015
.
.
.
.
25,000 June 2015
I have a column that is an int field and it holds the number of seconds that corresponds to a time during the day. Example being that 24331 will be 6:45:31 AM and 67531 will be 6:45:31 PM. I'm looking for code that will show the time in AM/PM format and with the AM and PM at the end of time.
View 5 Replies View RelatedHi all.If I've got a query which has a field with seconds in it... how will I usethe Convert function to get my field converted to the format: HH:MM:SS ?The field with the seconds in is called: "Diff"Thanks alotRudi
View 2 Replies View Related
I have a question about how Query Notification works and the objects and data created when a new subscription is created.
Here is what we are doing:
- When the application starts, we call the SqlDependency start method:
SqlDependency.Start( <connection string> )
- For each query we want to monitor, we run the following:
SqlConnection oConnection = new SqlConnection( <connection string> );
oConnection.Open();
SqlCommand oCommand = new SqlCommand( <sql statement> , oConnection);
SqlDependency oDependency = new SqlDependency(oCommand);
oDependency.OnChange += new OnChangeEventHandler( <handler delegate> );
SqlDataReader objReader = oCommand.ExecuteReader();
objReader.Close();
- When the application shuts down, we call the SqlDependency stop method:
SqlDependency.Stop( <connection string> )
We have observed that when we create a new subscription, the following things happen:
1. A row is added to sys.DM_QN_Subscriptions.
2. query_notification_% internal table is created.
3. SQLQueryNotificationStoredProcedure% stored procedure is created.
4. SQLQueryNotificationService% Service Queue is created.
5. queue_messages% internal table is created.
When the application ends, the stored procedure, service queue and queue messages internal table are dropped. The row in sys.DM_QN_Subscriptions and the query_notification_% persist.
When the application is restarted, a new row is added to sys.DM_QN_Subscriptions, but the existing query_notification_% internal table is used.
Is this normal behavior? Or, is there something we should be doing to clean up?
What I am wondering is if there is a threshold where these persistent items become a problem with locking, performance, other?
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]...
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.
I have a Stored Procedure that has a query in it and it take 0 second and then a stored procedure that takes 16 seconds. From what I can tell they shoul be the same.
It doesn't recompile when i run the stored procedure, I checked that.
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
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.
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
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
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??
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!
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
I know IIf doesn't exist in SQL. However, I am creating a view and want to check the value of a field and if it is 1 the field returns 'This value' if it is 2 then 'That value' , if 3 then 'Another value'.
How can I do this ?
Hi Gurus,I'm a beginner. Would you please tell me if it's possible to create a viewhaving a calcuated column based on the condition of the column on the sqltable.create view vwImaging ASselectEmpID, LastName, FirstName, EmpTag = 'Act' ifFROM tblPerPayI have a table EMP:SSN (char 9)A view giving a formatted SSN (XXX-XX-XXXX,) a--Message posted via http://www.sqlmonster.com
View 3 Replies View RelatedHi.I created a simple view with the following statements:CREATE VIEW dbo.VIEW1ASSELECT dbo.VIEW_ALL.ID,dbo.VIEW_ALL.Code,Another.dbo.OTHER_VIEW.Label as SpecialCodeFROM dbo.VIEW_ALL LEFT OUTER JOINAnother.dbo.OTHER_VIEW ON(dbo.VIEW_ALL.Code + '_0') = Another.dbo.OTHER_VIEW.LabelWhen I run :select * from VIEW1 where code = 'abcde',I would get all 10 rows that are of the same data where the code is'abcde'. But what I thought it should return is really just one rowsince the rest of the 9 rows are exactly the same! I know that I canget my result if I useselect DISTINCT * from VIEW1 where code = 'abcde'But, is there any way I could change my SQL statements(CREATE VIEW)above so that I automatically get 1 row instead of 10 rows of exactlythe same value without using DISTINCT?Thank you for any help.
View 7 Replies View RelatedI have a table in my program which going all the invoices,and I want to create a view where I can see the quantity of the products as many times by 1 as the quantity in the invoice:
Invoice:
Product name quantity
woods 3
at the view
woods 1
woods 1
woods 1
Thank you.
how to create view in sql server 2005 that recognize Identity And Primary key of base table
View 2 Replies View RelatedGood afternoon,
I have a user that needs read only access to all tables in a particular database - Which is working fine.
He also needs to create views, as well as the above, but nothing else.
Is this possible?
Many thanks.
CREATE VIEW getUsedColumns AS SELECT * FROM (SELECT DISTINCT attr FROM iba UNION SELECT DISTINCT attr FROM new_iba) WHERE attr != 0;
its working with oracle
how to chenge in t-sql
thanx
Hey guys, im really new ASP.net. I just upsized my access database to SQL Server Express and I'm trying to mimic some view functionalitty I had in access. I think they call it the expression builder.So bassically Im trying to make a column that does some calculations on other columns. Like:if columnA-columnB > 100 then columnC='YES' else columnC='NO'Does anyone know how to do this? I want to add this column in a view. Can i perhaps just do it in the datagrid? I'm so frusterated at the moment, ive been trying for about 3 hours!Thanks in advance for your help.
View 1 Replies View RelatedI need to create view from the results of executing dynamic SQL.Query like this have incorrect syntax: CREATE VIEW vModuleCatalogObjectValues AS EXECUTE sp_executesql @sql, @paramDefinition, @moduleId; ENDWhat i need to do to get it work?!
View 1 Replies View RelatedHi,
Is there a way you can give a user a permission to "Create View" only but not to create other objects like tables or SP's in a database.
Thanks
Hi,
I tried to create a view on following 4 tables with two different syntaxes. One with ANSI syntax and other with conventional(using WHERE clause). Both SELECT work fine and return same result sets. Views are created in both cases. SELECT from Conventional_View works fine But SELECT from the ANSI_View returns error message without Error Number as :
'DB-Library Process Dead - Connection Broken'
CREATE TABLE dbo.SILT01 (
SILF01A varchar (8) NOT NULL ,
SILF01B varchar (25) NULL ,
SILF01C smallint NULL ,
CONSTRAINT SILT01_PK PRIMARY KEY CLUSTERED
(SILF01A)
)
GO
CREATE TABLE dbo.SILT19 (
SILF23A varchar (8) NOT NULL ,
SILF01A varchar (8) NOT NULL ,
CONSTRAINT aaaaaSILT19_PK PRIMARY KEY CLUSTERED
(
SILF23A,
SILF01A
)
)
GO
CREATE TABLE dbo.SILT20 (
SILF23A varchar (8) NOT NULL ,
SILF15A varchar (8) NOT NULL ,
SILF01A varchar (8) NOT NULL ,
CONSTRAINT aaaaaSILT20_PK PRIMARY KEY CLUSTERED
(
SILF23A,
SILF15A,
SILF01A
)
)
GO
CREATE TABLE dbo.SILT23 (
SILF23A varchar (8) NOT NULL ,
SILF23B varchar (30) NULL ,
SILF23C varchar (40) NULL ,
SILF23D varchar (8) NULL ,
SILF23E varchar (1) NULL ,
SILF23F text NULL ,
SILF23G varchar (25) NULL ,
SILF23H varchar (8) NULL ,
SILF23I varchar (25) NULL ,
SILF23J varchar (25) NULL ,
SILF23K text NULL ,
SILF23L varchar (25) NULL ,
SILF23M varchar (8) NULL ,
SILF23N varchar (25) NULL ,
SILF23O varchar (25) NULL ,
SILF23P text NULL ,
SILF23Q varchar (25) NULL ,
SILF23R varchar (8) NULL ,
SILF23S varchar (25) NULL ,
SILF23T varchar (25) NULL ,
SILF23U varchar (15) NULL ,
SILF23V varchar (15) NULL ,
SILF23W varchar (15) NULL ,
SILF23X varchar (15) NULL ,
SILF23Y varchar (15) NULL ,
SILF23Z varchar (15) NULL ,
SILF23A1 varchar (15) NULL ,
SILF23A2 varchar (15) NULL ,
SILF23A3 varchar (15) NULL ,
SILF23A4 varchar (15) NULL ,
SILF23A5 varchar (15) NULL ,
SILF23A6 varchar (3) NULL ,
CONSTRAINT aaaaaSILT23_PK PRIMARY KEY CLUSTERED
(
SILF23A
)
)
GO
create view ANSI_View as
SELECT DISTINCT SILT01.SILF01A, SILT23.SILF23A,
SILT23.SILF23B, SILT20.SILF15A
FROM ((SILT01 INNER JOIN SILT19 ON SILT01.SILF01A = SILT19.SILF01A)
INNER JOIN SILT23 ON SILT19.SILF23A = SILT23.SILF23A)
LEFT JOIN SILT20 ON (SILT19.SILF01A = SILT20.SILF01A) AND
(SILT19.SILF23A = SILT20.SILF23A)
create view Conventional_View as
SELECT DISTINCT SILT01.SILF01A, SILT23.SILF23A,
SILT23.SILF23B, SILT20.SILF15A
FROM SILT01, SILT19,SILT23, SILT20
WHERE ( SILT01.SILF01A = SILT19.SILF01A AND
SILT19.SILF23A = SILT23.SILF23A ) AND
( ( SILT19.SILF01A *= SILT20.SILF01A) AND
(SILT19.SILF23A *= SILT20.SILF23A) )
SELECT * FROM ANSI_VIEW
'DB-Library Process Dead - Connection Broken'
SELECT * FROM Conventional_View (Works fine)
Expecting Help or Suggestions from experts.
Thanx,
Gunvant Patil( gunvantp@mail.usa.com )
G'day all.
I am trying to create an index on a view that joins two tables.
I get the classic error of course:
'Cannot index the view 'dbname.dbo.HJC_net'. It contains one or more disallowed constructs.'
Thing that gets me is that it all seems pretty normal stuff and I can't see what is stopping it.
Code is below and any help greatly appreciated.
CREATE VIEW dbo.HJC_net WITH SCHEMABINDING AS
SELECTt_number
FROM dbo.ticket_cancellations RIGHT OUTER JOIN
dbo.tickets ON dbo.ticket_cancellations.tc_system_ref = dbo.tickets.t_number
WHERE dbo.tickets.t_cancelled <> - 1 OR
-- Add all cancellation codes that are to be excluded from the NET view below
(dbo.ticket_cancellations.tc_cancellation_code <> 83943
AND dbo.ticket_cancellations.tc_cancellation_code <> 83946)
GO
-- Create a clustered index, it MUST be unique
CREATE UNIQUE CLUSTERED INDEX t_number_unique ON HJC_net(t_number)
I have to try and get my head around this question and ive never seen sql this advanced before.please set me in the right direction.thanks in advance.Using the meeting table schema below provide a SQL statement to create a view in Microsoft SQL Server that will return only 1 record displaying the contents of the MEETING_NUMBER field. The rules for the view are as follows:• The START_DATE field which contains the start date of the meeting record must contain a value before the current system date.• The START_DATE field must be the closest date to the current system date.ID int (4)DOC_CLASS varchar (30)DOC_NUMBER int (4)REVISION int (4)START_DATE datetime (8)FINISH_DATE datetime (8)MEET_TIME varchar (5)VENUE varchar (50)TYPE varchar (50)MEETING_NUMBER varchar (11)CUTOFF_PERIOD int (4)CUTOFF_TIME varchar (5)AGENDA_STATUS varchar (50)SUBJECT varchar (50)SPONSORING_MIN varchar (50)SUPP_AGENDA_STATUS varchar (50)Possible answer:select meeting_numberfrom meetingwhere start_date < getdate()order by start_date desc
View 14 Replies View Related