Select From Database Without Grid Views Etc

Jul 9, 2006

Hi,



I want to be able to select data from a database without using grid
view etc. I can set up a connection and bind it to a grid view but I
would like to do some data processing without showing it. Something like



select id from table where name = "x"

store id in idvar

store the row count to a variable rowcount

if rowcount > 0

    select name from table2 where id = idvar

endif



etc



I can see how to set the sqldatasource select command but not how to run it and get results out of it. Please help

View 1 Replies


ADVERTISEMENT

Using One Database Call To Populate 8 Grid Views

Apr 23, 2008

Hi
I'd like to be a little more efficient in my approach when using databases. Although my site is very simple with a low hit rate expected, I would still like to learn the best methods in designing server load conscious code.
Basically, I have a page where I'd like to show events broken down into days of the week (Monday - Sunday). All the events contain the same data and only difference being the day. Right now, the only way I know how to make this work is to create a unique sqlDataSource (select * where Day = Monday) then assign it to a ‘Monday’ grid view. I then repeat this same process for each day of the week.
As the data is always the same, is there a way to make only call to the database returning all the data, then close the connection and then separate the data there after. To me this seems to be a much more efficient way to manage the data???
Thanks for any help/supportRichard

View 4 Replies View Related

Grant User Group Select Access To All Views In Database

Sep 24, 2013

I'm trying to grant a user group select access to all the views in a database. I already made a query which creates a result set whit the SQL Syntax I (displayed below) need but it seems to be impossible to get this result set executed after creation.

use [AdventureWorksDW2008R2]
SELECT
'GRANT SELECT ON [' + SCHEMA_NAME(Schema_id) + '].[' + name + '] TO [DOMAINGROUP]'
FROM sys.views;

View 5 Replies View Related

Grid And Details View Not Showing Remote Database Data

Oct 27, 2006

hi,i have an sql database on my server on the world wide web.i can make a connection  to the database in visual web developer and all the tables etc are shown in the 'database explorer' of visual web developerwhen i make the query in visual web developer it does retrieve the data froom the remote server database when i 'test query'.... so looking great!the connection string in the webconfig file is left as the default when i run the prgram on my LOCAL HOST with the inbult server that comes with visual web developer it runs fine......so.....running on my local pc it will connect to the database on my www host server and does display the data from that database.i need to change the webconfig files path to the connection  by default it is |DataDirectory|if i change this to the path i suspect is correct as this is the connection i use to the database  C:Inetpubvhostsarcvillage.comhttpdocsApp_DataTestdatabase.mdfwhen i change the path in webconfig then it stops working on my localhost and the error when i ftp it to my host is:Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed. any ideas? 

View 2 Replies View Related

Select * In Views

Apr 15, 2006

People are telling me it is bad to putselect * from <atable>in a view. I better should list all fields of the table inside thedefinition of the view.I dont know exactly why but some say:A select * from makes sql server does a table scan.Is that true, even if i put a where on the select on the view? And what if idont list all fields in the select on the view?Thanks for the answer.Peter

View 33 Replies View Related

Select * In Views

Aug 16, 2007



Hi All,

Iam having 2 tables say Emp table and Department Table. Iam having a view which joins 2 tables.
example
select *,Department.DeptStatus, Department.DepartmentName from Employee INNER JOIN Department ON Department.deptno=Employee.DeptNO.
When I add a new column to the Employee table say EmployeeStatus then my view get corrupted.what i mean is that the last newly added column from emloyee table takes the Name as DeptStatus and shows values of the EmployeeStatus Column. We have been creating new Columns in SQL 2000 we never faced this issue but iam seeing this issue only in SQL Server 2005.

Thanks in Advance.

cheers,
Sada

View 7 Replies View Related

Views Or Select

Aug 2, 2007

Hai, I'm an novice in Sql Server. I wanna know why we are going for VIEWS instead of SELECT statement in Sql Server 2005. However, both are going to perform the same task (i.e, Retreiving data) from the database.

View 5 Replies View Related

Granting Select On Views

Dec 4, 2007

Hello,

How do , I grant select privileges on all the Views of the database to a user in SQl 2005. All the tables and views in the database are under the dbo schema.

Thanks.

View 1 Replies View Related

Views Vs SELECT Statement

Sep 9, 2007

Is it better to use just a SELECT statement or a view when working with a small database? Does anyone know which would perform better?

............................................................thanks

View 12 Replies View Related

SMO Problem / Can't Select Non System Views Without Crash

Mar 5, 2006

I've had a couple problems with SMO and I wonder if anyone else has.  Specifically, when I run through stored procedures (just like your example) it is painfully slow.  Maybe 5 a second.  Another problem is filtering on nonsystem objects with either "SVR.SetDefaultInitFields(typeof(Microsoft.SqlServer.Management.Smo.View), "IsSystemObject"); or View.isSystemObject.
Both cases crash my app.  Any ideas?
Below are two ways that both crash or hang.
SVR.SetDefaultInitFields(typeof(Microsoft.SqlServer.Management.Smo.View), "IsSystemObject");
foreach (Microsoft.SqlServer.Management.Smo.View view in viewCollection)
{}
OR
 
foreach (Microsoft.SqlServer.Management.Smo.View view in viewCollection)
{
if (!view.IsSystemObject)
{}
}
 
 
 

View 1 Replies View Related

VIEWs - Can It Know Wether A SELECT, UPDATE, Etc Is Executing On It

Apr 6, 2000

VIEW behaviour depending on what operation is run on it.

VIEW syntax is CREATE VIEW xx AS SELECT ... It would be very convinient to know wether a select, update, insert or delete is the operation executed on it so the view could take action accordingly, ie
CREATE VIEW xxx AS SELECT ..
CASE UPDATE THEN do_this
CASE SELECT THEN do_that etc.
Does anyone know if this is possible?

View 2 Replies View Related

Views Containing SELECT * Do Not Recompile When Table Is Modified

Mar 17, 2008

If I have a view such as: SELECT T.* FROM T
When I add a column to table T the view is not updated to reflect that change.
Furthermore, if there are other columns after the * in the view (for example SELECT T.*, GETDATE() as "My Date" FROM T) the last columns will contain incorrect data.

Is there a work around for this? An "auto-recompile when tables are modified" kind of option?

Thanks
Nick

PS: This is the script I used for testing:

create table tt (
test1 int primary key,
test2 int)
go
insert into tt (test1, test2) values (1,2)
go
create view vw_tt as select *, getdate() as "My Date" from tt
go
select * from vw_tt
go
create view vw_tt2 as select * from tt
go
alter table tt add test3 int
go
select * from vw_tt
select * from vw_tt2
select * from tt
drop table tt
drop view vw_tt
drop view vw_tt2

View 9 Replies View Related

SQL Security :: How To Grant Select On All Views OR All Columns

Dec 14, 2011

grant SELECT   on all_columns to yogi_bear;
grant SELECT   on all_objects  to yogi_bear;
grant SELECT   on all_views    to yogi_bear;
 
but it doesn't work, why? I know how to do it with a cursor or dynamic sql, but why won't these sql statements work?

View 7 Replies View Related

How Can I Return Data From A Database And Fill A Data Grid View?

May 2, 2007

Hi ! I have a textbox and a Search button. When the user inputs a value and press the button, i  want a datagrid to be filled.
The following code runs:
Dim connect As New Data.SqlClient.SqlConnection( _
"Server=SrvnameSQLEXPRESS;Integrated Security=True; UID= ;password= ; database=dtbsname")
connect.Open()
Dim cmd As New SqlCommand
Dim valor As New SqlParameter("@valor", SqlDbType.VarChar, 50)
cmd.CommandText = "Ver_Contactos_Reducido"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = connect
cmd.Parameters.Add(valor)
cmd.Parameters("@valor").Value = texto
Dim adapter As New SqlDataAdapter(cmd)
Dim ds As New System.Data.DataSet()
adapter.Fill(ds)
GridViewContactos.DataSource = ds
GridViewContactos.DataBind()
connect.Close()
I drag a datagrid on the page and only changed its Id.
Into the SQL database the stored procedure is the following:
ALTER PROCEDURE [dbo].[Ver_Contactos_Reducido]
(@Valor VARCHAR(100))
AS
BEGIN
SET NOCOUNT ON;
SELECT NombreRazonSocial, Nombre, Apellido,TelefonoLaboral,
Interno, TelefonoCelular, Email1, Organizacion
FROM CONTACTOS
WHERE NombreRazonSocial = @Valor OR Nombre = @Valor OR Apellido = @Valor OR  TelefonoLaboral = @Valor OR Interno = @Valor OR
TelefonoCelular =@Valor OR Email1 = @Valor OR Organizacion= @Valor
END
When i run the page i can't see the datagrid, and after i enter a text and press the button, nothing happens. What am i doing wrong??
Thks!!

View 2 Replies View Related

Want A Stored Procedure To Dynamically Select Views Pulled From A Table.

Feb 1, 2007

I am building a dashboard features that allows user to select reports from a dropdownlist. It is pulling from a table called Reports (cols: ReportID, Description, sqlView) In this Report table the report is associated to a view that queries the report.
And the user's selections are stored in table called UserReport (cols: userID, ReportID, createDt) .
I need to get a Dataset to contain datables of all reports selected. (for example a user select 3 reports, the dataset should contain 3 datables that represent the report).
I want to accomplish this by create a store procedure that queries the Reports table and then dynamically executes the views that related to the user selected reports. Can anyone give me an example on how to create the storeprocedure? 
Thanks,
CG 
 
 
 

View 3 Replies View Related

Bulk Insert Into Views That Select From Table On Remote Server

Jul 20, 2005

Hi all,We have an application through which we are bulk inserting rows into aview. The definition of the view is such that it selects columns froma table on a remote server. I have added the servers usingsp_addlinkedserver on both database servers.When I call the Commit API of oledb I get the following error:Error state: 1, Severity: 19, Server: TST-PROC22, Line#: 1, msg:SqlDumpExceptionHandler: Process 66 generated fatal exception c0000005EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.I would like to know if we can bulk insert rows into a view thataccesses a table on the remote server using the "bulk insert" or bcpcommand. I tried a small test through SQL Query Analyser to use "bulkinsert" on a such a view.The test that I performed was the following:On database server 1 :create table iqbal (var1 int, var2 int)On database server 2 (remote server):create view iqbal as select var1,var2 from[DBServer1].[SomeDB].[dbo].[iqbal]set xact_abort onbulk insert iqbal from '\MachineIqbaliqbaldata.txt'The bulk insert operation failed with the following error message:[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData(CheckforData()).Server: Msg 11, Level 16, State 1, Line 0General network error. Check your network documentation.Connection BrokenThe file iqbaldata.txt contents were :112233If the table that the view references is on the same server then weare able to bulk insert successfully.Is there a way by which I should be able to bulk insert rows into aview that selects from a table on a remote server. If not then couldanyone suggest a workaround. I would actually like to know someworkaround to get the code working using OLEDB. Due to unavoidablereasons I cannot output the records to the file and then use bcp tobulk insert the records in the remote table. I need to have some wayof doing it using OLEDB.Thanks in advanceIqbal

View 7 Replies View Related

Are Embedded Views (Views Within Views...) Evil And If So Why?

Apr 3, 2006

Fellow database developers,I would like to draw on your experience with views. I have a databasethat includes many views. Sometimes, views contains other views, andthose views in turn may contain views. In fact, I have some views inmy database that are a product of nested views of up to 6 levels deep!The reason we did this was.1. Object-oriented in nature. Makes it easy to work with them.2. Changing an underlying view (adding new fields, removing etc),automatically the higher up views inherit this new information. Thismake maintenance very easy.3. These nested views are only ever used for the reporting side of ourapplication, not for the day-to-day database use by the application.We use Crystal Reports and Crystal is smart enough (can't believe Ijust said that about Crystal) to only pull back the fields that arebeing accessed by the report. In other words, Crystal will issue aSelect field1, field2, field3 from ReportingView Where .... eventhough "ReportingView" contains a long list of fields.Problems I can see.1. Parent views generally use "Select * From childview". This meansthat we have to execute a "sp_refreshview" command against all viewswhenever child views are altered.2. Parent views return a lot of information that isn't necessarilyused.3. Makes it harder to track down exactly where the information iscoming from. You have to drill right through to the child view to seethe raw table joins etc.Does anyone have any comments on this database design? I would love tohear your opinions and tales from the trenches.Best regards,Rod.

View 15 Replies View Related

Hiding Database Views

Oct 17, 2001

Help Please!
How can I hide databases from users that do not have permission to log onto them in the SQL Enterprise Manager.
Thanks in advance for the help.
-Rich

View 3 Replies View Related

Run 1 Query Against All Views In Database

Jul 16, 2014

In SQL server 2008 how can I run the same query against each view in that database?

One database about 75 views.

Or even something like
Select 'yes' from view
Where shipping_status = 'delayed'

And return a list of view names so I know which views to query against.

I ran

Code:
select name from sysobjects where type = v

And got a list of my views. How can I now run my SQL statement against the list to see which views contain the info?

View 1 Replies View Related

Database Views Hierarchy

Feb 2, 2006

Hello:In my SQL Server databases I have complex related tables and views.There is any tool which allow you to see (print) this complex hierarchy ofviews and tables as hierarchical tree?Thanks,GB

View 3 Replies View Related

Alter All Objects In A Database / Views / Triggers

Apr 20, 2015

I am trying to replace object name in views , triggers, stored procs, UDF,TVF etc.I have created a automated script to replace 'dbo.Cust' with 'dbo.Customer' in all objects and generate script as ALTER Statements. some objects are still scripted out as Create. Reason is it has some extra space in between

CREATE TABLE #test1(
[NAME] [nvarchar](128) NOT NULL,
[DEFINITION] [nvarchar](max) NULL,
[DEFINITION_bk] [nvarchar](max) NULL,
[type] [char](2) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

[code]....

View 5 Replies View Related

No Views After Creating A Database With Restore From Backup

Nov 13, 2007

Forgive me if I'm asking a simple question, but I'm new to database administration
I'm trying to migrate a database from a server running Windows 2000 Server and SQL Server 2000 to a new machine running Windows Server 2003 and SQL Server 2005. I moved a copy of a recent database backup file to the new server, and created a new database with Restore From Backup. The tables seem to have restored fine, but my views are non-existent. Has anyone seen this problem before, or does anyone know of something I may have skipped?

View 3 Replies View Related

Is There A Way To Give Someone The Right To Create Only Views And No Other Objects In A Database

Feb 25, 2008

Is there a way to give someone the right to create only views and no other objects in a database? Currently I have given the individual ddladmin database rose, but would rather be more restrictive?

View 1 Replies View Related

How To Display List Of Tables &&amp; SP &&amp; Views In A Database?

Aug 16, 2007



Hi
In SQL Server 2000 & 2005

How to Display list of Tables & S.Ps & Views in a Database?

Please advise

Thanks

View 4 Replies View Related

Searching The Keywords In A Collection Of Views Present In A Database

Jun 17, 2008

Hi All,
i have some views in my database, and these views are having some columns,i want to know particular column name to be there in particular view.
For example ,just like functionality of sp_search_code 'Keyword'.
like this i want to search in views .
or else please let me know  sp_search_code 'Keyword'. was used for views also.
Thanks and  Regards,
G.JaganMohanrao.
 
 
 

View 2 Replies View Related

How Can I Get The List Of Tables And Views Together In The Database By The Dbconnection.getSchema()

May 3, 2006

I have tried SqlConnection.getSchema("Tables"). it returns me a list of all tables while getSchema("Views") returns me the views in the form of DataTable. But can we have a way to get them together in the same DataTable as a return result? because I want to read the rows by  DataTable.CreateDataReader() later.

View 2 Replies View Related

Avoid Cross Database Views In Data Warehouse

May 4, 2007

We have a relational database (rd) and a data warehouse (dw). This dw has a table (tw) with all key fields (dimension keys) and metric related (measures) fields. This table is populated with monthly data each month. The tw is joined to various look up views present in the dw to obtain name fields from rd. The DBA wants me to remove the look up views. I now have following 2 options that I can think of –

1)Further de-normalize the tw and store the name fields as well. However, there are two issues with this option –

a.The size of tw will grow tremendously.
b.We are storing monthly data and the values in the name fields may change after some time. Then we will have to put in additional views/objects to obtain the latest name.

2)Using ETL, obtain the copy of rd tables overnight in dw. We will then join tw with these tables and there will no longer be cross database joins. However, this will be a burden on maintenance and support.

As of now these are the possible options I can come up with. Which one would you suggest and why? If you have another option, please let me know.

Thank you all in advance,

sajmera

View 1 Replies View Related

Script All Database Tables,views And Stored Proced

Jun 24, 2008

what's the easiest way to script all table,views and stored procedures?

I would like to script them like from ssms i right click on each one and choose script table as --- but how can I do it for all of them at once?

View 1 Replies View Related

Large Views Vs Multiple Small Views

Sep 6, 2007

Which is more efficient? One large view that joins >=10 tables, or a few smaller views that join only the tables needed for individual pages?

View 1 Replies View Related

How To Find Out The List Of All Tables And Views Owned By A Particular User In Any Database?

Oct 15, 2001

How to find out the list of all tables and views owned by a particular user in any database by querying the system tables?Thanks.

View 2 Replies View Related

SQL Server Admin 2014 :: Script To Find Nested Views In A Database

Oct 7, 2015

Any easy way to find if there are any nested views existing in the database?...using SQL server 2014...

View 1 Replies View Related

SQL Server 2014 :: Query For Listing Views In A Database Involving Tables From Other Databases?

Oct 19, 2015

script to get the list of views in a database, involving tables from other databases?I AM using SQL server 2014

View 2 Replies View Related

Recompiling Views That Reference Other Views

Jun 28, 2007

Hello.

Newbie here. I've only been using SQL for about a year now and have some minor questions about sql objects that reference other objects.



We have some views which reference other views in the joins. I will call one the primary view and the one being referenced in the joins as the secondary view.

Recently we made changes to the secondary view.

After which the primary views which referenced it would not work because of this change and had to be 'refreshed' by using drop/create scripts which essentially just dropped it and recreated the exact same view. I do not recall the exact error message that was returned other than it seemed to suggest that it could no longer see the secondary view since it had been changed. Nothing in the primary view was changed in any way, just the secondary.



Some here where I work have suggested off hand that this was a recompile of the primary view because the contents of the secondary changed.

My questions are:

1. Exactly why did this happen and is there a proper name for it when it does?

2. The same problem does not seem to occur when we have stored procedures referencing views in the joins which had just been changed. Why is that?



Thanks for any help on the matter. I greatly appreciate it.

View 3 Replies View Related







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