Why Does SQL2005 Automatically Insert TOP 100 PERCENT In Views?
Feb 7, 2006
Hi All
I have just upgraded to SQL2005 and found an annoying problem. Whenever
I add a new view or amend an existing one (created in SQL 2000
Enterprise Manager) it inserts TOP 100 Percent into the SQL query. Even
if I delete the clause from the SQL query it puts it straight back in
again. This is a problem as there are some queries where I just don't
want this clause there (ADO.NET cannot update a view with a TOP clause)
Any ideas anyone?
Many thanks
Ian Bell
*** Sent via Developersdex http://www.developersdex.com ***
Hello! I have a SqlDataSource that inserts some data in a database. The field "id" is auto-increment. Is it possible to use the "id", this data-row got from the database automatically, directly after the SqlDataSource.Insert() command in my CodeBehind file? Thank you!
I'm trying to update (increment) Company.SumtotalLogons from CompanyUsers.NumberOfLogons where CompanyUsers.CompanyID = Company.CompanyID
I'd like to either write a formula (if it is even possible to fire a formula from one table update/insert to increment a field in another table), or a stored procedure that triggers an auto update/append into Company.SumTotalLogons
I know this is possible in access, so i'm wondering how to go about it in ms-sql?
Is there a way to read a file name automatically from a network folder? I can successfully bulk insert from this particular folder. The next step is as I add files, I wish to bulk insert the latest file added so the program must make that determination and import that specific file. I can delete the older files if necessary and save them elsewhere but it would still be nice to be able to read the file name. I then wish to store the name of this file, whatever it is, into a field called "SourceFileName" in my table that I am bulk inserting into. Does anyone have an example in dynamic SQL? Thanks.
How to create insert statements of the data from a table for top 'n' rows. I know we can create using generate scripts wizard, but we can't customize the number of rows. In my scenario i got a database with 10 tables where every table got millions of records, but the requirement is to sample out only top 10000 records from each table.
I am planning to generate table CREATE statements from GENERATE Scripts wizard and add this INSERT STATEMENT at the bottom.
EX : INSERT [dbo].[table] ([SERIALID], [BATCHID] VALUES (126751, '9100278GC4PM1', )
I have one stored procedure for insert, update and delete operations, I need automatically logged the insert,update and delete operations. How can I set auto logged mechanism and how can I access the logs in c#? Thanks
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
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.
I just upgraded my SQL 2000 server to SQL2005. I forked out all that money, and now it takes 4~5 seconds for a webpage to load. You can see for yourself. It's pathetic. When I ran SQL2000, i was getting instant results on any webpage. I can't find any tool to optimize the tables or databases. And when I used caused SQL Server to use 100% cpu and 500+MB of ram. I can't have this.Can anyone give me some tips as to why SQL 2005 is so slow?
I have the following query where I am selecting the random row. However I need to select 10% randon rows from the tables. How do I do this? select top 1 * from table ORDER BY NEWID()
I need rate to be a percent, it is returning 1 where XX=YY (e.g 100/100=1), but when the number (rate) is a fraction (90/100=.9)I am getting 0. Don't know what the problem is.
select XX as X, YY as Y, (YY/XX) as rate from blah where blah group by XX, YY order by XX
Hi all whether using TOP clause in SELECT statement or [SET ROWCOUNT n] before SELECT statement, I want to know how SqlServer Behave? whether Fetching data and then choosing n record of them or as soon as fetching n records , Sql Server Stops retrieving the rest of the data? Thanks in advance. Regards.
I have questions table and questions are categorized. I want to query say Top 10 questions but with some percentage based on category. For example, if I have 10 questions in category 1 and 50 questions in category 2 than when I select Top 10 with some percentage I want to come out more questions from category 2 than category 1.
I'd like to understant the Percent Log Used behavior... I monitored the Percent Log Used. For a specific database, it was 50 % used. I backup up the transaction log and the Percent Log Used was still 50 % used. For another database, the log was 60 % used. After the backup, it was 80 % used!!!! Would you help me to understant this situation? Thank you, Fabio
I am trying to take a string code seen below that gives me the income over a 12 month time frame but now I need to take this string and divide it by the acqcost. and it will not let me can you please take a look at this and see whats wrong.
(This string is what gives me the income) =sum(Fields!Total.Value)-Sum(IIF(Fields!eqprecdt.Value< #6/1/2007#,Fields!bookvalue.Value* 0.07,Fields!bookvalue.Value* 0.07*-1 * datediff("d",#5/31/2008#,Fields!eqprecdt.Value)/365))
(This string is the acq cost) SUM(Fields!acqcost.Value)
Hi all As you are aware we have TOP n [Percent] keyword that is used in Select statements ,I have a question?? When this keyword is evaluated by Sql-Server, after retreiving all Query-Result or upon the number of results reach to the specified number,sql-server stops retrieving the rest of record.?
I a bit of newbie making reports and I cant figure out how to calculate the Percent of Total on a report. Assuming I have a field in my datatable called Color, I simply want to create a report that counts the occurences of each color and then perform a calculation as to what percent of the total that each of the colors make up. Basically a report that looks like this....
Color Count Percent Red 50 25% Blue 100 50%
Green 50 25% Total 200
I have a table that has a header, a grouping row based on Color, and a footer. So using the report expressions, my table looks like this
Color Count Percent =Fields!Color.Value =Count(Fields!Color.Value) WHAT EXPRESSION GOES HERE?!?!?!
I have a table of data, shown below that is composed of a scoreid, player id, and score. Using the following SQL Query: "Select Top 50 Percent(score), playerid, scoreid from TScores where playerid = 5 order by score" I can get the lowest 50% of player number 5's scores. How do I rewrite the query to give me each individual player's top 50 percent.
I have a script file which do the replication and in the script there is a lot of table need to be addad as article.
So while use osql execute the script there would be cost a lot of time. What I want to do is show a dialog which could show the progress percent and the file name which is now being addad as article.
I am trying to use this command to change the variable in where clause each time:
psexec servername -E cmd.EXE /c "sqlcmd -S servername /E -d dbname -v entertext="'%anything%'" -i c:myfoldermyscript.sql -o c:mypathoutput.sql" myscript.sql is like: select * from table_name where summary like $(entertext);
This command works just for variables with single and exact word. It does not work for variables which has space between them or when I enter part of summary.In fact, I wanted to write something which the user be able to enter variable each time when he/she run this batch file. because I put this sqlcmd command in batch file( like this in oracle: "select * from table_name where summary = &entertext; " I was looking the same command in sql server , but I did not find it). I do not know .net or any other programing language( I know just csh and command line). I want to do this just with sql command.
I have a question about rounding and converting to percentage and adding in the '%'..This is my original code
SELECT * FROM ( SELECT Baby, CAST( CAST( SUM(TotalBaby) AS DECIMAL )/ CAST( SUM(TotalParent) AS DECIMAL) AS DECIMAL(10,4)) as BabyValue FROM NewBorn WHERE Category = 'Boy'
[code]....
But I also need my data to have the '%' and it should have 2 decimal place which is as below, I have tried to make it this way
CAST( CAST( SUM(TotalBaby) AS DECIMAL )/ CAST( SUM(TotalParent) AS DECIMAL) AS DECIMAL(10,4)) * 100 + '%' as BabyValue
But it prompt me the error 'Error converting data type varchar to numeric.'
I am using SQL Server and have a table that is currently set to decimal(5,2) as the datatype. I am using Tableau as my front-end and if I create a dashboard, I can see my fields like 65.25, 65.72, etc. I want to view them as 65.25% however when I change the data type in Tableau to a %, it shows up as 6525.25%. Is there a way in the SQL Server side to change the column so it shows as a percent? I am sure the answer is no because I have never seen that. Only seen decimal values. Maybe I need to change it to decimal 2.2 if that even exists.
I have a data containing salesman who can belong to groups. The groups have "split" values but the splits are not on a percent basis. Can I create a query to get the percentage in a column? Please look at the table below and my attempted query.
Hi!How can I get percent of executed procedure in MSSQL Server, lik inEnterprise Manager and/or dbMgr2k ?I use Visual C# and MSDE.Thank's for help, gregory
This is my issue. I have values in my table that I need to format as a percent. So when I use the FormatPercent() function, it works but if one of the records has no value for that field. It generates a #Error message in that textbox. I tried using an if statement and that didn't work either. Someone please help me with this. Any information is appreciated.
