I have a table that stores pass information for coils in a steel mill. For each coil stored, I have a field that has the number of passes made, and then fields for the width and gauge of steel for each pass. (ie, it has pass1gauge, pass1feet, pass2gauge, pass2feet, etc. up to a max of 9 passes ... table was in place and in heavy usage long before I got here, so don't blame me for the problems with the schema ... :)
I need to, in SQL, calculate the weight of the coil (width*last pass gauge*(last pass feet*12)*.283). I know from the pass field how many passes were made, and in VB, I could bring down the recordset, loop through the recordset and calculate this by checking the pass field, but there has to be a way to do this in SQL.
The gist of my problem is I need to, if its a 5 pass coil, do calculations with pass5gauge and pass5feet. Maybe I could do a join back on the same table? But ... well I'm not too experienced with SQL. :confused:
Anyone see how to do this? I can give a crap-ton of more details if needed. I am an admin (the only admin, as a matter of fact) of this db server, so I can do anything needed (ie, stored procedures for testing, views, whatever). I'd like to be able to avoid stored procedures, though, if possible.
I have a select statement running on the client machine linking to different tables in 1 database. All with the same schema. When I ran it, i had this error. I had trial and error, removing 1 table at a time until i hit the one which is causing it. when i removed it, everything's ok. i just wonder if all the tables were using dbo schema what is causing this particular table to throw this error?
Hi All... I'm using a SQL Server 2005 database. I've noticed that columns that are declared as "char" and that have a fixed size tend to put trailing spaces at the end of the data when I pull it out. I guess I can understand why... But it's a pain dealing with it. As I'm bringing my application up, I can see spaces all over the place - I just havent gotten around to doing anything about it yet. What's the easiest/best way to get rid of those spaces. Geez, it'd be real cool if I could put something in the SELECT statement. Any thoughts? Thanks much!! -- Curt
I know this is a known issue since SSRS 2000, but it seems that it hasn't been solved: How can I have column headings on a Matrix Report ? (not in the data region, just in the columns that identifies the rows).
All the workarounds and tricks that I have found are quite nasty and have drawbacks when you export to Excel, for instance:
- Using a table inside upper left blank area in the matrix report: Problem, when you export to excel you loose the Header.
- Using an rectangle and placing textboxes (tweaking with pixels... :-(): When you export to excel you get excel columns merged, quite bad if somebody wants to make some macros or calculations).
- Using a list report... well I need a matrix report :-).
Is there other way to implement this ? This is a known issue since 2000 came up, no really a solution available ? I have seen on SP-2 and there is no solution for this issue... please heeelp.
I have a schema XXX. This schema owns a set of tables say XXX.A, XXX.B and XXX.C.
I have a login XXX mapped to the user XXX.
When I connect to the SQL Server using login XXX and execute the query.
Select * from A, it throws be an error saying that"Invalid Object Name A".
Now when I modify the select statement specifying the schema it works. i.e. Select * from XXX.A
In the former case, is it not that SQL Server tries to search for the table in the current user's account and then if it not available it look's in the dbo's account.
HOw can I make a select without specifying the schema ?
HI, I have set up a database with 5 users, USER1 has default schema USER1, USER2 has default schema USER2 and so on. My problem is that I want to revoke select permission on schema USER1 to user USER2. I issued the following TSQL in SSMS:
REVOKE SELECT ON SCHEMA::USER1 to USER2
Even though I did that, I can still log on as USER2 and be able to issue SELECT statements on USER1 schema tables. The only way I can do to avoid the SELECT on USER1 schema is to DENY select on USER1 schema. Is it normal?
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if
Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' FROM myTables WHERE Conditions are true ORDER BY Field01
The results are just as I need:
Field01 Field02
------------- ----------------------
192473 8461760
192474 22810
Because other reasons. I need to modify that query to:
Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' INTO AuxiliaryTable FROM myTables WHERE Conditions are true ORDER BY Field01 SELECT DISTINCT [Field02] FROM AuxTable The the results are:
Field02
----------------------
22810 8461760
And what I need is (without showing any other field):
Field02
----------------------
8461760 22810
Is there any good suggestion? Thanks in advance for any help, Aldo.
I am running a C# asp.met application which most of the application is running ok but several of my aspx. pages are giving me this error. I am currentyly running MS-SQL 2005 Dev ed. using VS.net 2005. I have turned on access in role to everything and still am getting this error. can someone help me please?
hay there...i'm developing a website using visual studio 2005, when i run it from VS it works fine and it can access the DB.but when i make an alias and run it directly from localhost ..i keep getting this message when i try to fill a DATASET ..Line 198: cmd.CommandText = "Select Line From Buses";Line 199: da.SelectCommand = cmd; Line 200: da.Fill(ds);can anyone help me ?thanx
Hello friends, I want to use select statement in a CASE inside procedure. can I do it? of yes then how can i do it ?
following part of the procedure clears my requirement.
SELECT E.EmployeeID, CASE E.EmployeeType WHEN 1 THEN select * from Tbl1 WHEN 2 THEN select * from Tbl2 WHEN 3 THEN select * from Tbl3 END FROM EMPLOYEE E
can any one help me in this? please give me a sample query.
I'm in need of a few more pointers when it comes to SQL Server 2005 Security setup for accessing a database through IIS web services. There's loads of great advise in these forums which I've followed so pls forgive me if I've missed a post that finally resolves this. I know I'm so close but it feels so very far...
Anyhow, here's the set up: - Server 2003 / SQL Server 2005 / .NET 2.0 / Visual Studio 2005 / Client XP PC
The story so far: - Built an ASPX website in VS2005 on a client PC that connects to a SQL2005 db. No probs during development. - Copied website to WS2005 & configured IIS accordingly. No probs browsing non-db webpages.
The problem: As soon as I browse to a page that extracts data from the db I get the following error message..
[SqlException (0x80131904): The SELECT permission was denied on the object 'tbl_location', database 'SmartDMA', schema 'dbo'.]
...plus a whole load of code that I've seen plastered over lots of forums.
My configurations: - The website is in the default SQL Application Pool that has the identity set to 'Predifed: Network Service' (as I'd previously has a similar error; SQLException (blah blah): Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection)
- IIS Website ASP.NET is v2.0.50727 - Website Authentication Methods / Enable Anonymous Access (ticked) Username: IUSR_SERVERNAME Password: *********. Intergration Windows Authentication (ticked). - SQL Server Mngt, Secuirty / Logins / NT AUTHORITYNETWORK SERVICE lists the db in question with dbo as the Default Schema & public ticket as the db role membership. - connectionString="Data Source=SERVERNAME;Initial Catalog=DBNAME;Integrated Security=true" providerName="System.Data.SqlClient"/>
What I've tried already based on forums advise: - Changing Server Properties to SQL Server & Windows Authentication mode. - Rather than using Integrated Security I've tried the SQL 'sa' account, but not 100% is I did that right (still got the same error anyway).
The rest of the advise I've read appears to confirm the rest of my settings, but I'm still getting the error.
Can anyone please shed any light on what I'm missing here. Feel free to ask questions on any configuration settings I've missed that may help.
I have 3 tables, with this relation: tblChats.WebsiteID = tblWebsite.ID tblWebsite.AccountID = tblAccount.ID
I need to delete rows within tblChats where tblChats.StartTime - GETDATE() < 180 and where they are apart of @AccountID. I have this select statement that works fine, but I am having trouble converting it to a delete statement:
SELECT * FROM tblChats c LEFT JOIN tblWebsites sites ON sites.ID = c.WebsiteID LEFT JOIN tblAccounts accounts on accounts.ID = sites.AccountID WHERE accounts.ID = 16 AND GETDATE() - c.StartTime > 180
Hey guys i have a stock table and a stock type table and what i would like to do is say for every different piece of stock find out how many are available The two tables are like thisstockIDconsumableIDstockAvailableconsumableIDconsumableName So i want to,Select every consumableName in my table and then group all the stock by the consumable ID with some form of total where stockavailable = 1I should then end up with a table like thisEpson T001 - Available 6Epson T002 - Available 0Epson T003 - Available 4If anyone can help me i would be very appreciative. If you want excact table names etc then i can put that here but for now i thought i would ask how you would do it and then give it a go myself.ThanksMatt
SELECT Top 10 Name, Contact AS DCC, DateAdded AS DateTimeFROM NameTaORDER BY DateAdded DESC I'm trying to right a sql statement for a gridview, I want to see the last ten records added to the to the database. As you know each day someone could add one or two records, how can I write it show the last 10 records entered.
Hello How can i say this I would like my if statement to say: if what the client types in Form1.Cust is = to the Select Statement which should be running off form1.Cust then show the Cust otherwise INVALID CUSTOMER NUMBER .here is my if statement. <% If Request.Form("Form1.Cust") = Request.QueryString("RsCustNo") Then%> <%=Request.Params("Cust") %> <% Else %> <p>INVALID CUSTOMER NUMBER</p> <% End If%> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:RsCustNo %>" ProviderName="<%$ ConnectionStrings:RsCustNo.ProviderName %>" SelectCommand="SELECT [CU_CUST_NUM] FROM [CUSTOMER] WHERE ([CU_CUST_NUM] = ?)"> <SelectParameters> <asp:FormParameter FormField="Cust" Name="CU_CUST_NUM" Type="String" /> </SelectParameters> </asp:SqlDataSource>any help would be appreciated
Locally I develop in SQL server 2005 enterprise. Recently I recreated my db on the server of my hosting company (in sql server 2005 express).I basically recreated the tables and copied the data in it.I now receive the following error when I hit the DB:The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'. However, the current database schema is not compatible with this version. You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version.I heard something about running aspnet_regsql.exe, but I dont have that access to the DB. Also I dont know if this command does anything more than creating the membership tables and filling it with some default data...Any other solutions/thought on what this can be?Thanks!
I am using three tables in this query, one is events_detail, one is events_summary, the third if gifts. The original select statement counted the number of ids (event_details.id_number) that appear per event_name (event_summary.event_name).
Now, I would like to add in another column that counts the number of IDs that gave a gift who attended an event that were also listed in the event_ details table. So far I have come up with the following. My main issue is linking the subquery properly back to the main query. how to count in the sub-query and have the result placed within the groups results in the main query.
SELECT es.event_name, es.event_id, COUNT(ed.id_number) Number_Attendees, ( SELECT COUNT(gifts.donor_id) AS Count2 FROM gifts WHERE gifts.donor_id = ed.id_number ) subquery2
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!?!?)
Hello everybody!I'm using ASP.NET 3.5, MSSQL 2005I bought virtual web hosting .On new user registrations i have an error =(The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'. However, the current database schema is not compatible with this version. You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version. On my virtual machine it work fine but on web hosting i have an error =(What can you propose to me?
I would like to use SSIS tool to move the data from one database schema to another database schema.
For example:
Source table has
1. UserName (varchar 20) (no null)
2. Email (varchar 50) (can be null)
Destination table has
1. UserID (uniqueidentifier - GUID)
2. UserName (varchar 50) (no null)
3. EmailAddress (nvarchar 50) (can be null)
4. DateTime
Questions:
1. What controls do I use in my Data Flow to make data move between databases with different data types and include new value in UserID as a new GUID and DateTime as a date (GETDATE)?
OLE DB Source, OLE DB Destination, Data Converson and .....
How do I insert Guid and Date at the same time?
2. I have many tables to do data moving. Any sugestions? How do I architect my project? If I create many data flows for each table - it will look complicated.
Can some one point me in the right direction in how to construct my SQL query within my cursor?
I Have got a cursor which i am using to iterate through a table, What i am trying to do is in my statement(used to open the cursor) is compare 2 tables (the one which my cursor is iterating) to see if there is a matching row in the other table (using both tables ID's Like So:
SELECT column_List FROM Table1 WHERE Table1_id = Table2_id
so for each row my cursor checks if there is a corresponding match in table2... but i would like to write to an error log
and do other statements if there is no match
how do i add this condition to my statement either using an if...else statement proceeding to the next row?
here is the statment i attempted to write:
SELECT column_List FROM table1 WHERE Table1_id = Table2.id
now i want to incoporate the statements below into the statement above as a condition when table1.id <> table2.id
IF table1.id <> table2.id
BEGIN SET @DebugMessage = 'data not live.' RAISERROR (@DebugMessage, 16, 1) WITH LOG END
essentially what i am trying to sayin my statement is:
go to the first row
check if it has a match in table 2,
if there is no match execute a number of statements such as error loging e.t.c
go to the next row
repeat the previous statements
...i also looked through some Case...When statements am just not sure how to put in the condition
I used SSEUtil to add a schema to my database but I am having problems. Used these steps:SSEUtil -c> USE "c:Rich.mdf"> GO>!RUN Resume.SQL//indicates success>SELECT * FROM SYS.XML_SCHEMA_COLLECTIONS>GO//schema not shown in list> USE master>GO>SELECT * FROM SYS.XML_SCHEMA_COLLECTIONS>GO//schema is shown in the queryIt appears that the schema is not added to the desired database, so when I try to use the schema in Visual Studio, the schema does not appear when I connect to the Rich.mdf database. Any ideas on what I am doing wrong or why this might be happening?ThanksKevin
