Best Way To SELECT From Database

Jun 9, 2005

Not totally sure if this is the best place for this question, but regarding maintainability / good practice issues vs speed I have a question regarding what is the best way to SELECT some data from a database.I have the item_id of an item bought.  I want to get a customer's billing information based on the item he bought.  Should I do this with two queries or one using joins?

Here is my dbase schema:

customer_info--------------customer_idnamecc_numexpdate

item_bought------------item_idcustomer_id

And here is a basic pseudocode algorithm:function getCustomerInfo(customerID){    SELECT customer_info.name, customer_info.cc_num, customer_info.expdate FROM customer_info WHERE    customer_id=customerID;   return results;}

function getCustomerInfoWithItemId(itemID){   SELECT item_bought.customer_id FROM item_bought WHERE item_bought.item_id=itemID;      results = getCustomerInfo(customer_Id);

   return results;}

results = getCustomerInfoWithItemId(400);OR

function getCustomerInfoWithItemId(itemID){  SELECT customer_info.name, customer_info.cc_num, customer_info.expdate FROM customer_info, item_bought WHERE item_bought.item_id=itemIDAND customer_info.customer_id = item_bought.customer_id;   return results;}

results = getCustomerInfoWithItemId(400);

View 7 Replies


ADVERTISEMENT

SELECT Permission Denied On Object 'TableID', Database 'Database', Schema 'dbo'

Mar 21, 2007


The error message:

An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for data set 'TestID'. (rsErrorExecutingCommand)
For more information about this error navigate to the report server on the local server machine, or enable remote errors

The log file reads:

---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set 'TestID'. ---> System.Data.SqlClient.SqlException: SELECT permission denied on object 'TableID', database 'Database', schema 'dbo'.

***Background***

General Users got an error message when trying to access any reports we have created.
All admin have no problems with the reports. Users (Domain Users) are given rights (Browser) to the reports and the Data Sources (Browser) and yet cannot view the reports.

An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'DS2'. (rsErrorOpeningConnection)
For more information about this error navigate to the report server on the local server machine, or enable remote errors


I'll add this from the report logs...

w3wp!processing!1!3/20/2007-11:43:25:: e ERROR: Data source €˜DS2€™: An error has occurred. Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source €˜DS2€™. ---> System.Data.SqlClient.SqlException: Cannot open database €œDatabase€? requested by the login. The login failed.
Login failed for user €˜DOMAINUsername€™.

The user has rights via a local group to the report and data source (Browser rights) and the local group has been added as a SQL login.


I gave rights to the databases themselves instead of just to SQL and the error changed (Ah-ha...progress, but why!?!?)

View 3 Replies View Related

SELECT Permission Denied On Object 'database Object', Database 'databasename', Owner 'dbo'.

Mar 27, 2006

I have created a sql login account called "webuser" and has given public role in my database. In my asp.net application i build connection string using above account and its password . We give permission on store procedure for for the above account to execute .We dont give table level permission for the above account . When we run the application with the above settings it runs fine on test server . However Now i have transfered the databse object to live server with its permissions . Now while I executing the aspx page , I am getting above error . I have checked that the store procedure has execute permission for webuser account and dbo(i.e SA) has all the permissions for all database objects . Still why i am getting error ? (Please note , the thing is working fine in test server)



Pl help



Regards

View 4 Replies View Related

Select Data From Table In One Database Server While Connected To Another Database Server.

Dec 18, 2007



Hi,


Is there a way in SQL Server 2005 to use a select statement to fetch data from a table in another Server while running the query in one Server.

Like using a database Server Link in oracle...


Thanks
Pramod

View 8 Replies View Related

Cannot Run SELECT INTO In This Database.

Oct 19, 2001

When I run this query:
select * into database2.UserX.Table1 from database1.dbo.Table1
I get the following error:

Server: Msg 268, Level 16, State 3, Line 1
Cannot run SELECT INTO in this database. The database owner must run sp_dboption to enable this option.

when I change the db option to 'dbo use only' 'false' ,still I get the same error.How to run the above statement?

View 1 Replies View Related

SELECT INTO Different Database

Jul 12, 2007

Hi,

I've been having some trouble finding the correct syntax for the following query, where I am simply trying to move selected records from table A in database A to table A in database B.

Here is what I have:


Code:

SELECT pd.phoneID, phonebookID, timezoneID, phoneNumber, lastName, firstName, address, city, state, country, zip, custom, custom2, custom3, memo, fax, email
INTO history.set_appointment1
FROM pro_PhoneData pd
LEFT JOIN pro_Campaign_1_Results cr ON pd.phoneID = cr.phoneID
WHERE ((phonebookID='3') OR (phonebookID='1'))
AND descriptionID = '203'
AND callTime < GETDATE()-365
ORDER BY callTime DESC



When I execute this query, I get the following message: "The specified schema name "history" either does not exist or you do not have permission to use it."

I've looked up examples for select into queries and have played around with it a bit, but no such luck.

Anyone have a solution?

Thanks!

View 2 Replies View Related

Database Select To Label

Sep 27, 2006

Hello!

I am trying
to select info from a database (MS-SQL) and show that whit a label. And don’t really
get every thing to work. So I am glad for that help I can get.

 

SqlConnection myConnection = new SqlConnection();

       
myConnection.ConnectionString = "data
source='XXX';User ID='XXX';Password=XXX;database='XXX'";

        myConnection.Open();

        SqlCommand dataCommand = new
SqlCommand();

        dataCommand.Connection
= myConnection;

       
dataCommand.CommandText = "SELECT XXX,XXX FROM XXX";

        SqlDataReader dataReader =
dataCommand.ExecuteReader();

        string notis1 = dataReader;

        Label1.Text = notis1;

        myConnection.Close(); Any help whould be helpfoul. 

View 5 Replies View Related

How To Use IF..THEN To Select SQL Server/Database?

May 18, 2007

I have a single form that the users can do lookups for items.  We have two locations each with its own SQL Server and database.  Trying to use an IF...THEN statement as they select the DB they want to query it then creates the connection string for that particular database.  I haven't a clue on how to do this.  If someone could point me to the documentation to do this I would really appreciate it. <%@ Page Language="VB" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDB" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">
Sub get_Dies(ByVal sender As System.Object, ByVal e As System.EventArgs)

Dim dbConnection As New SqlConnection

If ddlDatabase.SelectedValue = "db2" Then
??? dbConnection = "server=server2;Database=db2;UID=user;PWD=pass"
dbConnection.Open()
Else
??? dbConnection = "server=server1;Database=db1;UID=user;PWD=pass"
dbConnection.Open()
End If
Dim sqlString As String = " sql statement"
Dim dbCommand As New SqlCommand(sqlString, dbConnection)

Dim dbDataReader As SqlDataReader
dbDataReader = dbCommand.ExecuteReader(CommandBehavior.CloseConnection)

gvDies.DataSource = dbDataReader
gvDies.DataBind()

dbConnection.Close()

End Sub

</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Die Usage Lookup</title>
<link rel="stylesheet" href="/intranet.css" type="text/css" />
</head>
<body>
<form id="form1" runat="server">
<div>
Find dies matching:
<asp:TextBox ID="tbDies" runat="server" /><br />
Select database:
<asp:DropDownList id="ddlDatabase" runat="server" AutoPostBack="true" OnSelectedIndexChanged="get_Dies">
<asp:ListItem Text="" Selected="true" Value="" />
<asp:ListItem Text="DB1" Value="db1" />
<asp:ListItem Text="DB2" Value="db2" />
</asp:DropDownList>

<asp:gridview ID="gvDies" runat="server" GridLines="none" >
</asp:gridview>


</div>
</form>
</body>
</html>
  

View 2 Replies View Related

How Do Select Data From My Database By IDs

Dec 14, 2007

How do i select data from my Database "HPDB.mdf" where the ID is 13, 14, 15?

I try to do it in the aspx way, but i got a conversion error:
Conversion failed when converting the nvarchar value '13, 14' to data type int.

asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="%$ ConnectionStrings:csHPDB %"
SelectCommand="SELECT * FROM [mgr_table] WHERE ([ID] IN (@ID))"
SelectParameters
asp:Parameter DefaultValue="13, 14, 15" Name="ID" /
/SelectParameters
/asp:SqlDataSource
asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource1"
/asp:GridView

Anybody know how to solve this or do it in Visual Basic way? please help me. Thanks.

View 10 Replies View Related

How Can I See With A Select The Available Database Space

Jan 31, 2005

Is it possible to select the available space of the database in a microsoft sql MSDE edition?

With sysfiles i can see the size of the database but where can i find the available space or the used space of the database.

View 2 Replies View Related

Select From A Database &insert Value Into A New Db

Mar 2, 2006

Hello...

I am using SQL Server Express and ASP.net with C#

I need to grab a value from a table in database1 and insert it into a table in database2... not quite sure how to do this?

Any ideas???

Thanks muchos!

View 11 Replies View Related

******How To Select Logical Database Name*********

Jul 31, 2007

Hi how do I select the current logical database name using a select statement.

View 8 Replies View Related

Select Not Exist From Different Database

Nov 20, 2007



Hi,

I have a problem during my etl process. my customer do not want that everytime the ssis package running, the dw table been cleen up/delete all. so i just have to process only with the new data. For example is like this:

Source Database: db1
Table DataEmp
id name address
----------------------------
1 Mike California
2 David New York
3 Bruce Ohio

Destination Database: db2
Table DimDataEmp
DataEmpKey DataEmpName DataEmpAddress
----------------------------------------------------------------------
1 Mike California
2 David New York

My goal now is, if i run the ssis package the only data transfer is (3, Bruce, Ohio). how am i do it? i already try the merge, merge join, or lookup control, but still don't work. basically i want to "select not exist" the destination table but in different database. please help me, cos i still new in ssis. Thanks

Sincerely Yours,

Yugi

View 3 Replies View Related

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 View Related

SQL Select Instruction On Two Database At The Same Time

Dec 13, 2006

It may sound like a newb question and if thats the case, I'm really sorry, but I would like to know if something like this is possible....
 
Lets say I have a table called TestA on a database DBA on a ServerA
and I have an another table called TestB on a database DBB on a ServerB
Now each of these table has a a field Id, to make an INNER JOIN between these two tables, taht are located on two different server on two differents database...Is it possible?  and if so what would be the SELECT instruction to do it?  Thanks for taking the time to read this :)

View 1 Replies View Related

Need To Select From ASPNETDB And Other Database In One Query

Feb 3, 2007

Hello everyone,
I have a problem with making such a query. I have two databases: DB1 and ASPNETDB - default asp database. I get data from DB1.Books and want to join (INNER JOIN) UserID with UserName from ASPNETDB.Users.
The ponint is that I don't know how to access ASPNETDB in the query. I use SQLDataSource control in which I specified the ConnectionString pointing to DB1 database on my local SQL Server 2005 Express.
On the other hand, if I use a ConnectionString pointing to ASPNETDB as a database dynamically attached from file (~/App_Data/ASPNETDB.MDF), I gain access to the Users table but I get an error message when I try to access DB1.dbo.Books...
I'm getting sick of it.. thanks for any help!
 
kind regards

View 3 Replies View Related

Select Records From Another Database And Table??

Aug 8, 2007

How do I select data from another database name (in the same SQL SERVER LOCATION) and its child table?
 SELECT user_id
FROM [10.10.20.3].CC_host.usr_table
Where(email LIKE N'%@%' And email LIKE N'%usa.com') Order By user_id 
 

View 4 Replies View Related

How Can Select Random Rows From Database?

Jan 23, 2008

Hi
Iam developing online test using ASP.NET,C#.NET.Now my doubt is test takers should get random questions from the database sqlserver2005.Already i ve inserted 10 question into the database.I ve used NEW ID() .But questions are repeating.I am new commer in to IT industry .Provide me with code.Given below is my code.I can retrive questions from the database now but cannot get in random order.protected void Page_Load(object sender, EventArgs e)
{Label10.Visible = false;
Label9.Visible = false;if (!IsPostBack)
{if (Session["id"] == null)
{Session["id"] = 1;
}Session["ans"] = 0;SqlConnection con = new SqlConnection(@"server=123-9181FF31362SQLEXPRESS;user id=sampleu;pwd=Sampleu2;database=n;");
con.Open();
SqlCommand cmd = new SqlCommand("select * from n3 where sno='" + Convert.ToInt16(Session["id"].ToString()) + "' ", con);SqlDataReader re;
re = cmd.ExecuteReader();while (re.Read())
{
 
Label1.Text = re[0].ToString();
Label2.Text = re[1].ToString();
Label3.Text = re[2].ToString();
Label4.Text = re[3].ToString();
Label5.Text = re[4].ToString();
Label6.Text = re[5].ToString();string sss = re[6].ToString();if (sss == "nil")
{
Panel1.Visible = true;Panel2.Visible = false;
}
else
{Panel1.Visible = false;Panel2.Visible = true;
}
}
re.Close();
}
}
 
 protected void Button1_Click(object sender, EventArgs e)
{string Answer = "";if (RadioButton1.Checked == true)
{Answer = "A";
}else if (RadioButton2.Checked == true)
{Answer = "B";
}else if (RadioButton3.Checked == true)
{Answer = "C";
}else if (RadioButton4.Checked == true)
{Answer = "D";
}else if (RadioButton5.Checked == true)
{Answer = "True";
}else if (RadioButton6.Checked == true)
{Answer = "False";
}SqlConnection con1 = new SqlConnection(@"server=123-9181FF31362SQLEXPRESS;user id=sampleu;pwd=Sampleu2;database=n;");
con1.Open();
string ss = Session["id"].ToString();SqlCommand cmd1 = new SqlCommand("select Answer from n3 where sno='" + Convert.ToInt16(Session["id"].ToString()) + "' ", con1);
SqlDataReader re1;string result1 = "";
re1 = cmd1.ExecuteReader();if (re1.Read())
{
result1 = re1[0].ToString();
}
//Session["ans"] = 0;if (Answer == result1)
{Session["ans"] = (Convert.ToInt16(Session["ans"].ToString()) + 1);
 
}
con1.Close();
 Session["id"] = (Convert.ToInt16(Session["id"]) + 1);
RadioButton1.Checked = false;RadioButton2.Checked = false;
RadioButton3.Checked = false;RadioButton4.Checked = false;
RadioButton5.Checked = false;RadioButton6.Checked = false;
 
 SqlConnection con = new SqlConnection(@"server=123-9181FF31362SQLEXPRESS;user id=sampleu;pwd=Sampleu2;database=n;");
con.Open();SqlCommand cmd = new SqlCommand("select * from n3 where sno='" + Convert.ToInt16(Session["id"].ToString()) + "' ", con);SqlDataReader re;
re = cmd.ExecuteReader();
 
 
 while (re.Read())
{
Label1.Text = re[0].ToString();
Label2.Text = re[1].ToString();
Label3.Text = re[2].ToString();
Label4.Text = re[3].ToString();
Label5.Text = re[4].ToString();
Label6.Text = re[5].ToString();string sss = re[6].ToString();if (sss == "nil")
{
Panel1.Visible = true;Panel2.Visible = false;
}
else
{Panel1.Visible = false;Panel2.Visible = true;
}
}
re.Close();int s = Convert.ToInt16(Session["id"].ToString());if (s == 11)
{
Label10.Text = Session["ans"].ToString() + " / " + "10";Label9.Visible = true;
Label10.Visible = true;
//int x = Convert.ToInt16(Session["ans"].ToString());
//int y = (x/10) * 100;//y = Label10.Text;
 
}
////// SqlConnection con6 = new SqlConnection(@"server=123-9181FF31362SQLEXPRESS;user id=sampleu;pwd=Sampleu2;database=n;");
////// con6.Open();
////// SqlCommand cmd6 = new SqlCommand("SELECT CONVERT(int, 10*RAND()) from n3 where sno='" + Convert.ToInt16(Session["id"].ToString()) + "' ", con6);
////// SqlDataReader re6;
////// re6 = cmd6.ExecuteReader();
////// //select top 5 * from Employee order by NEWID()
//////// SELECT FirstName,LastName
////////FROM Person.Contact
////////TABLESAMPLE SYSTEM (10 PERCENT)
 
////// while (re6.Read())
////// {
////// Label1.Text = re6[0].ToString();
////// }
////// con6.Close();
}
}

View 3 Replies View Related

How To Select Only Three Months Recored From Database

Jan 27, 2008

Hi guys,
Could anyone help me to write sql query to select only three months record from database
if the cur month is march then i need jan, feb and mar data , if it is jan, then i need nov , dec and jan data...how can i write sql query for this, i need this for creating reports
and also how cal i write queries based on days...mean to say that i need to select all records of particular month that falls on monday or tuesday...
Help me out.
Thanks in advance

View 1 Replies View Related

Retrieve Select Information From A Database

Mar 31, 2008

If I had a bunch of paragraphs stored in a database field, is there a way, when displaying that data, that I can write instructions to only retrieve the very first paragraph?

View 4 Replies View Related

Select With From Database With A List Of Parameters

May 15, 2008

Hi, i have a big problem. I´m having trouble with the select statement in SQL query language. The problem is that I need to retrieve various data from database and the input object is a list. The simple way of doing this is:SELECT <return values>FROM <datatable name>WHERE (<select data parameter>)My problem is that my where parameter needs to be an array list. The simple way of solving the problem would be using a for statement in my c# code and call my store procedure various times, but my array list can be too long and take a long time to connect and search data for each statement, so I need to access the database once.Can anybody help; I would appreciate it very much thx, Malcolm

 

View 8 Replies View Related

Connecting To A Database And Select Values

Jun 3, 2008

Hello there i try to retrieve some data from my table but i cannot get it to get me the ItemID from the table
here is the connection string it jumps right at the start of the loop strconnection = New SqlConnection("Data source = REPORT-SRVR; Initial Catalog=Cincinnati_Tan_Company;Persist Security Info=True;User ID=basic_user;Password=ctc_user;")
Dim cmdstring As String = "select ItemID, Price FROM Office_Items WHERE Description ='" & item(INTINDEX) & "'" '
strconnection.Open()CMD = New SqlCommand(cmdstring, strconnection)
mYReader = CMD.ExecuteReader()
If mYReader.Read = True ThenWhile mYReader.Read
ItemID = mYReader.Item("ItemID")Session("ItemID") = mYReader.Item("ItemID")
Session.Add("ItemID", ItemID)price = mYReader.Item("price")
Session("price") = mYReader.Item("price")Session.Add("price", price)
End While
End If
mYReader.Close()

View 3 Replies View Related

Select At Random Order From Database

Dec 23, 2005

Hi,
I need to select few items from sql database.I know for ORDER BY, but I need those items to be mixed in random order every time when they are returned from database. Those are the same items every time, just randomly mixed.
Can i do it with SQL, or I have to find another way (e.g. to mix them after sql returns them)?

View 1 Replies View Related

Select Rows From Another Server's Database

Jun 2, 2003

Hi,

I need to import data from another server on recurring basis. Before inserting the data, I need to perform some checking e.g. check the last update date and time.

I am thinking of scheduling SQL job that run SQL Query. The SQL query will make use of cursor to check each row, to decide what to do with the imported data

Is it the right way to import the data? However I did not know how to select data from another server using T-SQL. Can help? I am using SQL 2000

Thanks in advance

Regards,
Christine

View 8 Replies View Related

How To Find Records In Database Using Select

Apr 27, 2004

I need to be able to find certain data as the user has submitted that data twice and delete one record,except that have hundreds of tables and don't know the table where she would have submitted the data, but I have some other key info that I can start with.

My question is,how do i select a record from the database if I don't know the table it comes from?

Could somebody give me an expamle please?

View 2 Replies View Related

Select From Different Database In Stored Procedure

Feb 27, 2007

How do I correctly query from a different database table in a stored procedure?

Here is the procedure:

CREATE PROCEDURE spInsertRegistration

@firstname varchar(25),
@lastname varchar(25),
@job_title varchar(50),
@manager varchar(50),
@cost_center char(3),
@address varchar(50),
@city varchar(50),
@state_id INT,
@zip char(5),
@email varchar(50),
@phone char(10),
@roommate varchar(50),
@arrival datetime,
@departure datetime,
@special_needs varchar(500),
@result INT OUTPUT

AS

DECLARE @stateabr VARCHAR(2)

SET @result=0


SELECT @stateabr=state_abr FROM Public.dbo.state WHERE state_id=@state_id


INSERT INTO registration_form
VALUES(@firstname,@lastname,@job_title,@manager,@cost_center,@address,@city,@stateabr,@zip,@email,@phone,@roommate,@arrival,@departure,@special_needs)

IF @@rowcount>0
SET @result=1
GO

This is the specific line where I'm trying to access a different database:

SELECT @stateabr=state_abr FROM Public.dbo.state WHERE state_id=@state_id

Thank you.

View 3 Replies View Related

Displaying Database Name Within Select Within UNION

Jul 23, 2005

I have the following stored procedure in SQL 2000 and would like todiplay the database name where data is drawn from. I'm using 4databases db1, db2, db3, db4 which all have the same table (Table1)with identical column names (Surname, GivenNames).CREATE PROCEDURE [dbo].[x_searchwildcard] @varSurname VARChar(25)ASSelect a.Surname, a.GivenNamesFrom [db1]..Table1 As aWhere a.Surname LIKE @varSurname + '%'UNIONSelect a.Surname, a.GivenNamesFrom [db2]..Table1 As aWhere a.Surname LIKE @varSurname + '%'UNIONSelect a.Surname, a.GivenNamesFrom [db3]..Table1 As aWhere a.Surname LIKE @varSurname + '%'UNIONSelect a.Surname, a.GivenNamesFrom [db4]..Table1 As aWhere a.Surname LIKE @varSurname + '%'Order By a.Surname,a.GivenNamesGOI tried the followingSelect a.Surname, a.GivenNames, db_name()However it only gave me the name of the database where the storedprocedure is kept (in my case 'Common')I was hoping it would display results something like the followingSurname GivenNames Database------- ---------- --------Fred Smith db1Freddy Smith db2Fred Smith db3Fred Smithe db3Fred Smith db4Fred Smithye db4Instead I receiveSurname GivenNames Database------- ---------- --------Fred Smith commonFreddy Smith commonFred Smith commonFred Smithe commonFred Smith commonFred Smithye commonAny ideas?ThanksRick

View 1 Replies View Related

Select No. Of Rows From The Current Database

Jul 19, 2006

Hello,I want to select the names and number of rows of all tables in thecurrent database, whose name starts with 'sys'my query is:select o.[name], sum(i.[rows]) as numbersfrom sysobjects oinner join sysindexes ion (o.[id] = i.[id])where o.[name] like 'sys%'and i.indid=1group by o.[name]the result of the query is (depending of the database) :name numberssysaltfiles 14syscharsets 114syscolumns 4934syscomments 2035sysconfigures 38sysdatabases 7sysdepends 5524sysdevices 6sysfilegroups 1sysfulltextcatalogs 0sysfulltextnotify 0sysindexes 98syslanguages 33sysmessages 3795sysobjects 1285syspermissions 806sysproperties 0sysreferences 0sysservers 1systypes 26sysusers 14sysxlogins 3But when I count the number of rows of "sysobjects", I getnumbers=1298, which is different from the result displayed above andsame goes for "syscolumns".I also tried "dbcc updateusage [0]" to update the SQL Server but Ididnt help me.Can anyone please advice me on this behaviour?

View 2 Replies View Related

How To Specify Which Database To Use For A Select Statement Within A Cursor?

Nov 9, 2007

Hi everyone,

I have been trying to perform the following task:

Using the sys.databases & sys.sysindexes views to display all the columns with a clustered index for all tables and all databases in a given server. So the end result will have 3 columns:

Database name
Table name
Column name from that table with a clustered index

I have already created the following script which displays all the databases for a given server:

declare @DBname nvarchar(128)
declare testCursorForDB cursor
for
select name from sys.databases with (nolock)
where name not in ('master','tempdb','model','msdb')
order by name

open testCursorForDB
fetch next from testCursorForDB
into @DBname

while @@fetch_status = 0
begin
print @DBname
fetch next from testCursorForDB
into @DBname
end

close testCursorForDB
deallocate testCursorForDB

I also have created the following query which will display all the table and column names which have a clustered index for a given database:

select object_name(i.id) as TableName,

i.name as IndexName
from sys.sysindexes as i with (nolock)
where i.indid = '1'

However, what I need help/advice on is how do I combine these two together into one working script (either using nested cursors or a better way). In other words, how can I specify which database to use (ie. using the "use database_name") so that my query above will be applied to each database found within the cursor.

Any help is greatly appreciated

Thanks!

View 7 Replies View Related

Select Records From Database Which Are 6 Hours Old Or Less

Dec 29, 2007

I have a table which has a datetime field, which is auto populated by MSSQLs GETNOW function.

I would like a query that returns results with dates 6 hours old or less

SELECT *
From Story
WHERE datetime LTE 6 hours ago.

Can anyone offer help?

View 1 Replies View Related

Select Rows From Database Where Fields Are Null

Dec 30, 2006

Hi, how do i do a select statement with asp.net to return a record if a field is null.  I have the following code:
SqlCommand cmd = new SqlCommand("SELECT * FROM Categories WHERE ParentId = @ParentId", cn);.Parameters.Add("@ParentId", SqlDbType.Int).Value = parentId != null ? (object) parentId : DBNull.Value;cn.Open();
The variable parentId is a nullable int.  However when i pass in a null value it doesn't return anything even though there are records in the database that have a null value for the ParentId field.
Appreciate if someone could tell me what i am doing wrong.  Thanks

View 6 Replies View Related

SELECT Permission Was Denied On Database Object

Oct 12, 2007

Hi all,
I have my asp.net application with crystal reports which is using OLE DB connection , when I published the application on my test server every thing was ok and I was able to view,print and exprot my reprot (test server is not a domain controller), BUT when I published the application on the production server which is a domain controller it is giving me this error:
Failed to open a rowset. Details: ADO Error Code: 0x Source: Microsoft OLE DB Provider for SQL Server Description: The SELECT permission was denied on the object 'MyTable', database 'MyDatabase', schema 'dbo'. SQL State: 42000 Native Error: Failed to open a rowset. Error in File C:WINDOWSTEMPMyreport {C4BCF4E0-469D-4425-8556-A3D2A17059B8}.rpt: Failed to open a rowset
 
I tried to give the IIS user all the permisions on the database, no result 
I tried also to make the authentication mode in IIS to Integrated windows authentication (Disable the user IISER_---) but it still give me the same error
 
Please help, any help will be highly appreciated
 

View 7 Replies View Related

Insert,update,select Data From The Database?

Jan 31, 2008

Hi everyone..i m new to this field.. can anyone explain me with simple example onhow to insert,update,select data from the sqldatabase? i m using vwd 2005 express edition along with sql express edition. plz explain the simple example with code (C#) including how to pass connection strings etc.thank you.jack.  

View 6 Replies View Related







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