Hi,
i have a stored procedure as :
----------------------------------------------------------
CREATE PROCEDURE Insert_tblAccount
@Name varchar(50),
@Password varchar(20),
@StatusID int,
@AccountType int,
@DOI datetime,
@ROI varchar(500)
AS
Declare @NewID Numeric(18)
BEGIN
SET NOCOUNT ON
INSERT INTO [tblAccount](Name, [Password], StatusID,AccountType, DOI, ROI )
VALUES(@Name, @Password, @StatusID, @AccountType, @DOI, @ROI)
Set @NewID = SCOPE_IDENTITY()
Select @NewID As NewID
END
----------------------------------------------------------------
i could not understand the line
Select @NewID As NewID
bcoz i have learned that NewId() function returns unique identifier
but above code returns same value as SCOPE_IDENTITY.
So can anybody make me clear what's going on ?????
the aspx file with source code work good. when i try run project which created from VS i take "SQL Server does not exist or access denied." connection strings same:
I am having a problem reinstall SQL Express 2005. I had it installed, but then I unistalled it. I have tried installing it again, but everytime I do it says that the product is already installed. There is no instance of it in my Add/Remove Programs. I have read the threads that relate to this topic, but they haven't helped. I have looked at my logs of the installs, but they don't make sense neither. I just want the program installed again, or a way to get it so that I can use it.
When I try to manually uninstall using msiexec.exe /x <processid> I get an error saying that the package could not be opened. This happens with all the process ids I use that I get from the log files (I have 11 logs).
am using Visual Basic 2005 and trying to create a database, and it will not let me. That is how I know that it is not installed, plus I can't find any instance of it one my computer. Please help. Thanks.
I'm trying to put together a very simple page where you can click the up and down button to move the item up or down in the list and save the new list into the database. I've run the stored procedure... and it works so Its not a procedure problem. Here is the list in my databaseOrder_Id Item Position1 Sebring 12 GrandPrix 23 Accord 44 Miati 3 When I go to click the button I get a object reference not set to an instance of an object... This error haunts me many times. Heres the code to my project:BLL /// <summary> /// Gets Items from the Item table. /// </summary> /// <returns>A list of items.</returns> [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, true)] public OrderItemDataSet.OrderItemDataTable GetItemDataByPosition() { return OrderItemAdapter.GetItemDataByPosition(); }
/// <summary> /// Gets the order id based on the position specified. /// </summary> /// <returns>The order id.</returns> [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, true)] public int SelectOrderIdByPos(int pos) { return SelectOrderIdByPos(pos); }
/// <summary> /// Update Order list. /// </summary> /// <param name="newPos">Position to change to.</param> /// <param name="originalPos">Original position.</param> /// <param name="orderId">Original id.</param> /// <param name="otherOrderId">Position to change id.</param> /// <returns></returns> [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Update, true)] public void ReorderItemByIdAndPosition(int newPos, int originalPos, int orderId, int otherOrderId) { OrderItemAdapter.ReorderItemByIdAndPosition(newPos, originalPos, orderId, otherOrderId); } Codebehind 1 using ReorderListWebApplication.BLL; 2 using System; 3 using System.Collections; 4 using System.Configuration; 5 using System.Data; 6 using System.Linq; 7 using System.Web; 8 using System.Web.Security; 9 using System.Web.UI; 10 using System.Web.UI.HtmlControls; 11 using System.Web.UI.WebControls; 12 using System.Web.UI.WebControls.WebParts; 13 using System.Xml.Linq; 14 15 namespace ReorderListWebApplication 16 { 17 public partial class _Default : System.Web.UI.Page 18 { 19 protected void Page_Load(object sender, EventArgs e) 20 { 21 22 } 23 24 protected void UpButton_OnClick(object sender, EventArgs e) 25 { 26 Label posLabel = (Label)ReorderItemDataList.FindControl("PosLabel"); 27 Label idLabel = (Label)ReorderItemDataList.FindControl("IdLabel"); 28 29 using (ReorderListWebApplication.BLL.OrderItem item = new ReorderListWebApplication.BLL.OrderItem()) 30 { 31 int pos = 0; 32 33 pos = item.SelectOrderIdByPos(Convert.ToInt32(idLabel.Text) - 1); 34 35 // Grab other orderId!! 36 item.ReorderItemByIdAndPosition((Convert.ToInt32(posLabel.Text) - 1), Convert.ToInt32(posLabel.Text), 3, pos); 37 } 38 } 39 40 protected void DownButton_OnClick(object sender, EventArgs e) 41 { 42 using (ReorderListWebApplication.BLL.OrderItem item = new ReorderListWebApplication.BLL.OrderItem()) 43 { 44 45 } 46 } 47 } 48 } 49
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.InvalidOperationException: You have specified that your update command compares all values on SqlDataSource 'SqlDataSource5', but the dictionary passed in for oldValues is empty. Pass in a valid dictionary for update or change your mode to OverwriteChanges. can somebody tell me what this is? Angiemarie
Hello, I could use some help with this error message: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. This if statement is the problem IF (select bill_freq from #header1) = '1' update #header1 Set [Monthend] = aa.pername from sv00564 zz inner join sv00532 aa on zz.wennsoft_period_id = aa.wennsoft_period_id Where zz.rmdnumwk like #header1.Invoice_Number
This is failing:// Trying to update DynamicPageContent.Html where DynamicPageContent.PageID='121'//With// Select Html from DynamicPageHistory where HistoryID='831'//Update DynamicPageContentSET Html=(SELECT Html FROM DynamicPageHistory WHERE HistoryID='831')WHERE PageID='121'With the following error:Server: Msg 279, Level 16, State 3, Line 1The text, ntext, and image data types are invalid in this subquery or aggregate expression.DynamicPageContent.Html is Ntext size 16 and DynamicPageHistory.Html is Ntext size 16. PageID is and int and HistoryID is an int. It fails with single quotes around 831 and 121 and it fails without single quotes. The error message is the same both ways.Ideas?Thanks,Rex
I feel extremly happy today and let me share with this wonderful forum why.
SQL is awesome and I'm really learning that theres alot of resources out there and coding is not that hard once you understand what your doing. Just took awhile for things to sink in. I still have a long ways to go but I'm really enjoying using sql. I practically live in our SQL Server 2000 at work and I'm learning more and more about it everyday.
Thank YOu so much guys I really appreciate all the help you've given me Now I'm gonna study for the sql cert
The error was: System.FormatException: Input string was not in a correct format.
The line this is happening on is: Response.Write("<TD><p>" + rdData("ClientRepID") + "</p></td>")
I don't understand, it is giving that error particularly when I want to get ClientRepID
Some more lines from the code: Response.Write("<TD><p>" + rdData("Username") + "</p></TD>") Response.Write("<TD><p>" + rdData("ClientRepID") + "</p></td>")
The first line the compiler had no problem but when it gets to the second line with ClientRepID it gives me this error. (ClientRepID exists in the database)
Hello, i just bought sql server 2005 with 5 cals (previously i was using a shared sql server with my previous host)... I probably should have asked this question before i bought it... i don't understand how the cal licencing works... the sql server is for running our ecommerce site, i just got a dedicated server to put this on... but from reading the licencing does this mean that only 5 internet users can access my website(or the information in the database) at a time???
OK, so I know that following works if I want update/insert the value "joe's bar" into the table.
set SET QUOTED_IDENTIFIER on update #temp set id=4 where name ='joe''s bar'
The thing is, I do not conceptually understand what is happening and it is driving me nuts. I have read and re-read the description of quoted_identifier in books online and I still don't get it. How does the extra ' after joe make it work? Is there any hope for me? :)
Hi,Newbie here. I have a mailing list program that I really like. I also have anew membership program. The membership program has mailing list signupsbuilt-in, but it isn't nearly as robust as the stand-alone mail program.If someone signs up for a newsletter when they register (membershipprogram), can a "trigger" in SQL Server put the same information into thestand-alone program's tables? And when they remove or update theirlists/info? And vice-versa?Is this what a "trigger" would do? And is it efficient?Thanks,JA
Hi ,I am running SQL server 2000 sp3a and i have set up my database to be in"Full Recovery" mode.I am not able to understand if i have set anything wrong here :1. Full database Backup - every weekend2. Differential Backup - twice a day3. Txn log backup - 4 times a day (4hrs apart)Situation :my Txn Log file is now 18GB ,i can see it's only taking up around 1.5 GB ofactual space usedQues :i thought after every full database backup on each weekend, the txn logbackup will clear up the txn log , if so i see no reason why the txn log cangrow to 18 GB unless between the txn log backup , there's alot of data comingin that caused the txn log to grow but this is not the casekindly advise if my setup is wrong or what could have happened somwhere tocause the log to grow so muchtks & rdgs--Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forum...eneral/200511/1
Hi all,So I've been reading the newsgroups, and reading a few books trying to learnSQL and SQL Server 2000. The books tell me I need a Primary Key, and thatevery table should have one. I know (I think) that a Primary Key is aspecial field that uniquely identifies each record or row within a table.My question is this: If I have a field or column whose values are all (andwill be) different in every row, is that what a Primary Key is, or is it nota primary key until I designate it as such with either a statement or byright-clicking a little key on the table diagram.If so, then what is the reason _behind_ why I have to do that, and have aprimary key?I've been playing with a few tables, and I can select information just fine,relate tables together with joins, and everything works fine (so far) allwithout my designating any Primary Keys--so why, exactly, do I need one, andexactly what is it?I would appreciate any help and clarification.Thanks.
Ok, I have a table with the following fields: ID, Type, Name, Date, Description ID is the PK. Names all have different values, but are predefined... a visual: ID, Type, Name, Date, Description 1, monday, mon1234,3-3-08, some text 2, monday, mon1234,3-4-08, some text 3, monday, mon2345, 3-5-08, some text 4, monday, mon2345, 3-6-08, some text Now What i need to do is get the last report for each unique "Name". How would I do that? When I try to do a Select Distinct, of course, it only gives me those names that only appear once.
Hi,I have a float column in the database. When ever, I enter a value into the table, the values are rounded. Suppose I execute the following script: create table tblamount( flttotal_amount float)insert into tblamount values (0.00)insert into tblamount values (0.00)insert into tblamount values (0.00)insert into tblamount values (0.00)select * from #tblamount returns the following values:0000 but I want the ouput with the values 0.00 that I entered in the DB. Can u pls help me out with this?
can't figure out why the following locking scenario works the way itdoes:spid 1:start transactionselect a row from table T1 with updlockresult: see an Update lock for the row and index keyspid 2:query for the same row as in session 1result: the query succeedsspid 3:do same as spid 1result: blocks trying to get Update lock on the index key for rowNow I do the query again in spid 2 and it blocks trying to get a Sharedlock on the index key, and it's waiting for spid3. why was it able toget a shared lock on a index key that had an Update lock at first butthen can't get the same shared lock when somebody else is also tryingto get an update lock?
I was doing update statements in SQL Server 2000.I have a table with over 16 million rows.It came from several hundred delimited text files,and two of the columns are file ID (int) and Line # (int)Structure is X12 (835). For those unfamiliar with that,each file has one to many BPR lines; each BPR line haszero to many CLP lines, each of those has zero to manySVC lines, each of those has zero to many CAS lines.Working with this through the Enterprise Manager MMC,a lot of things I tried got timeouts.So, I indexed File ID, Line number, and line type, andcreated a new table containing only the columns I knewI would need in the final output--selected fields fromsome of the line types mentioned, plus the line numbersand common file ID for those rows.I indexed every column in that table that I thought I mightsearch on.I loaded it with 31 thousand rows using a select on asubset of the CAS rows. That took far less than a minute.I updated each row with the highest BPR line number not higherthan the CASE line number. About a minute. Not bad, with havingthe worst case number of comparisons being 16 million times 31 thousand.Of course, the indexing should help plus it can be narrowed down bythe "same file" and BPR # < CAS # criteria.But the next update should theoretically be faster: each row now hasa BPR # and a CAS # and I am telling it to find the highest CLP numberBETWEEN those two. So it should have a MUCH smaller set of to searchthrough. Yet it thinks for about five minutes and then announces a timeout.Any suggestions?--Wes GroleauMeasure with a micrometer, mark with chalk, and cut with an axe.
I am trying to understand the currency conversion but facing some problems in the same.The more I am tring to understand that the more confused am i getting.
My requirement is specific. I am trying to write sql queries for the measures which come under the scope of currency conversion.
I will query the Cube to get the measure value :
SELECT [Measures].[Sales Amount] on 0 from [Adventure Works DW]
And then query the SQL database as:
Use AdentureWorksDW SELECT SUM(SalesAmount) FROM FactInternetSales
My understanding is this ( Please correct me if i am wrong) :
If the Currency conversion is:
1. Many to One : Data will be different (in SQL and MDX) as currency conversion is applied which will convert that to a particular (Corporate) currency.
2. One To many : Data will not be different (in SQL and MDX)
2. Many to many : Data will not be different (in SQL and MDX)
I did not get much information on the same from anywhere. I want to undersatnd the script of the Currency conversion. (The script generated by Currency conversion wizard) and then depending on the script (I will extract the Type of conversion) write the SQL for that.
Hi, I hope that someone can help me understand why my query is not returning what I expect. When I run this query:
SELECT DISTINCT(TransactionKey) FROM Transactions_Fact WHERE DateKey = 14550 AND TransactionKey BETWEEN 1 AND 90000000 AND TransactionKey NOT IN (SELECT DISTINCT(TransactionKey) FROM tmpTransactions WHERE TranDate = 14550 AND TransactionKey BETWEEN 1 AND 90000000)
I get 150 rows back, which is what I expect. However, if I leave out the 'AND TransactionKey BETWEEN 1 AND 90000000', then I don't get back anything?!?
SELECT DISTINCT(TransactionKey) FROM Transactions_Fact WHERE DateKey = 14550 AND TransactionKey NOT IN (SELECT DISTINCT(TransactionKey) FROM tmpTransactions WHERE TranDate = 14550)
Any ideas as to what I'm missing here? It seems like it should at least return the same 150 rows. Thanks for any help.
I'm a bear with a very little brain. Please review the following story to see if I understand the concepts.
For the purposes of this exercise let's say that a database is used to control a building's HVAC (Heating, ventilation and cooling) system. It's installed by the HVAC vendor, who installs client software on the PC's in the building. This software allows the occupants of the building to alter the setting of the HVAC system. A young and foolish programmer/DBA - eager to show his mettle - accepts the responsibility of overseeing the system to make sure it works. What could go wrong? Easy money.
Given: A database that contains securable. (Tables, views, schema's etc).
By the second week, there is always someone complaining that it's too hot, too cold, too noisy (fans on too high). Everyone is setting their own settings. No one is happy - the only thing that they agree on is that the system doesn't work - and it's up to the less-young and less-foolish programmer/DBA to fix it.
Option #1: Create an Application Role (AIR_GOD) to control who can really write to the thermostat tables. This effectively blocks anyone who doesn't know the AIR_GOD password from fiddling. This password is only given to a carefully selected few.
Two weeks and fourteen passwords later:
Option #2: Create a fixed database role (AIR_GOD2) and drop only the selected few logins into it. (Our programmer is learning)
It helps. But since the users can access the whole thermostat-table - they end up setting each other's zones settings - sometimes by accident. Sometimes the values entered are insane.
Option #3: Create a Data Access Layer (DAL) Our programmer/DBA learns fast - he removes update rights to the thermostat-table from all users with a login. Now the only way to change a thermostat-table setting is through the stored-procedure(s) with the 'user without a login' impersonation. Values are checked before they're written etc.
Is that about right?
(Our story ends with the programmer/DBA growing older and wiser vowing never - ever - ever - to get involved with HVAC control systems again.)
I tried searching, but it appears the search is still broken.Some of you know my other posts.. pleading for help with a problem (that's really sort of fixed itself/not remanifested itself).Upon this whole ordeal I decided to re-evaluate my nonchelant backup strategy. Which currently is:Backup our DB daily w/ transaction logs. (overwrite the backup daily on the tape).Backup our DB weekly w/ transaction logs.I rotate the tape weekly, on Monday morning. So there is a weekly backup and the last backup from monday morning on each tape.Along with that I have some optimizations and shrinks I run. Reading through the SQL BOL. It seems my strategy is ok, however if we lost the db at the end of the day, we'd lose a full days worth of work.So. Here in lay the questions.The BOL states to possibly back up twice a week, with differential daily backups and 4 hour transaction logs (example).So, I sorta get that..I make a device, "COMPANY.BAK", which writes to my external 'tape' unit.I make a job to back up the complete DB on sunday. to "COMPANY.BAK". (Not sure if I tell it to truncate the transaction log?).I make another job to make nightly differential backups to "COMPANY.BAK" with the 'append to media' switch on.I make yet ANOTHER job to make a transaction log backup. Here's what I don't get. If I set this thing to run every 4 hours, since it's not a differential backup.. rather incremental.. doesn't it just overwrite itself each time? It doesn't append itself to the backup right? So how much data could be missing if the log is overwriting itself rather than appending to itself? Also, if I turn on truncate log on backup, aren't I missing possibly critical transactions from the log for a sucessful restore?I guess I'm a bit befuddled here.
Here is the function I'm trying to write. The purpose is to replace MS Access Val() function. I'm not finished with the logic, I'm just trying to get this much to work now. Here is the function:create function DBO.NumValue -- This function will get the numbers from the front of a field -- and return the value of those numbers in a numeric data type (@mNumInput as charvar(100)) RETURNSnumeric AS BEGIN declare @x as tinyint declare @x1 as tinyint SET @x = 1 WHILE IsNumeric(SubString(@mNumInput, @x, 1)) BEGIN SET @x1 = @x SET @x = @x + 1 CONTINUE END If @x1 > 0 BEGIN RETURN CAST(LEFT(@mNumInput, @x1), Numeric END END Here are the two error messages I'm getting from this function.Server: Msg 156, Level 15, State 1, Procedure NumValue, Line 12 Incorrect syntax near the keyword 'BEGIN'. Server: Msg 156, Level 15, State 1, Procedure NumValue, Line 20 Incorrect syntax near the keyword 'END'. I have no idea what these two error messages mean. TIA,
Hi all, Please, I need some help understanding what I need to do. I'm working with text files and they're too much for Access to handle. The logical conclusion is to use something more robust like SQL. I'm having trouble understanding how it would all fit together, and I'm looking for guidance.
First of all, what do you think is a logical approach to this problem: I get 6 .txt files delivered to our webserver via ftp. Every night there's an update, so they overwrite. I need to be able to display that data on the web page and I thought I could use Access to do it. Well, it won't work, so then there's SQL.
In my reading today, I find out that SQL isn't an environment like Access, it's a language. I'm assuming that means I can't just import all of this data into an SQL db, get it on the webserver, and then start running queries against it like I can in Access, right?
If one of you more experienced users has any ideas, please share. How can I use SQL to search the data in these .txt files? Included with each text file is a .dic file containing field names, data types, and length. What is the best approach. Step by step would be wonderful as I am very new to this. I have only ever worked with SQL queries and Access db.
Hi Folks,SQL Server 2000 SP3 on Windows 2000. I have a database on which I ranthe command :dbcc dbreindex ('tablename')gofor all tables in the database. Then I compared the dbcc showcontigwith all_index output from before and after the reindex and on thelargest table in the database I found this. First output is prior toreindex:Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:7TABLE level scan performed.- Pages Scanned................................: 184867- Extents Scanned..............................: 23203- Extent Switches..............................: 23324- Avg. Pages per Extent........................: 8.0- Scan Density [Best Count:Actual Count].......: 99.07% [23109:23325]- Logical Scan Fragmentation ..................: 11.13%- Extent Scan Fragmentation ...................: 35.46%- Avg. Bytes Free per Page.....................: 60.0- Avg. Page Density (full).....................: 99.26%Second output is from after the reindex:DBCC SHOWCONTIG scanning 'PlannedTransferArchive' table...Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:8TABLE level scan performed.- Pages Scanned................................: 303177- Extents Scanned..............................: 37964- Extent Switches..............................: 42579- Avg. Pages per Extent........................: 8.0- Scan Density [Best Count:Actual Count].......: 89.00% [37898:42580]- Logical Scan Fragmentation ..................: 43.19%- Extent Scan Fragmentation ...................: 24.78%- Avg. Bytes Free per Page.....................: 75.1- Avg. Page Density (full).....................: 99.07%Following are my concerns:The following numbers are all higher after reindex than before reindex:pages scanned, extent switches, logical scan fragmentation, avg bytesfree per page, avg page density.scan density is lower after reindex than before reindexSeems to me that the numbers that are higher after reindex should belower and numbers that are lower after reindex should be higher? Ididn't specify the fill factor in the dbcc reindex command so it shouldhave used the default fill factor. The fill factor has never beenchanged on this machine.Am I missing something?Thanks,Raziq.*** Sent via Developersdex http://www.developersdex.com ***