Show SQL search query in pages

Getting started with SQL Server

Index ‹ SQL Server

- Previous
  - 1
    - Calculated Fields in the report Why are you trying to substitute COUNT(*) for 1 The point of SUM(CASE ... 1 ELSE 0 END) is to do a conditional count *for* you. You are summing 1 (not an expression) for each row that fits your condition. See Nothing to substitute and no reason to use COUNT() <s>. >L< Tag: Show SQL search query in pages
  - 2
    - Performance is degraded drastically after migrating to SQL Express 2005 from MSDE 2000 Hi, in sql2005 express you can use the same connectionstrings like in all other sql2005 editions. The following i use in a webproject and it works very fine: <connectionStrings> <clear/> <add name= "Sup" connectionString= "Server=.\SQLEXPRESS;Database=Support;Trusted_Connection=True; Asynchronous Processing=True;MultipleActiveResultsets=True;" providerName="System.Data.SqlClient"/> ... </connectionStrings> The "Server=.\" means the local machine. Here you can say every name where you will connect to. The "SQLEXPRESS" is the instance of the sql Server installed on the machine. Hope this helps Servus Klaus Tag: Show SQL search query in pages
  - 3
    - extract information from tables Hello Guys, my collegue left the job and I have been chosen to work on his assignments. He posted this question before and got the answer but I couldn't find a way to implement it in the program. http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1726638&SiteID=1 I changed the database a bit as I couldn't understand his logic of putting both PayID and TransNo as this would be a linked table and should have many to many relationship Code Snippet TransactionID int Unchecked TransNo varchar(50) Checked RefNo nvarchar(50) Checked FundCodeID smallint Checked TransAmount decimal(18, 2) Checked Description varchar(100) Checked TransPayID (from TransPayLnk Table as foreign key) Tbl_TransPayLnk Code Snippet TransPayID int Unchecked TransNo varchar(50) Checked TransDate datetime Checked Operator char(5) Checked TerminalID tinyint Checked Flagged char(1) Unchecked Remarks text Checked RemarksDate datetime Checked TPayment decimal(18, 2) Checked Tbl_Payment Code Snippet PaymentID int Unchecked PayType varchar(50) Checked Amount decimal(18, 2) Checked TransPayID (Foreign Key from Tbl_TransPayLnk) My requirement would stay the same, I would need to put the data from these 3 tables into a single line without duplicating the records and I would need to display them in a DataGridView. P.S: Would it be possible to have Mixed when a TransPayID is matched in Tbl_payment with more then one record rather then brining cash and cheque. Thank you all for your help. Bye Gemma Tag: Show SQL search query in pages
  - 4
    - AWE working? Hi all, This is a question related to AWE settings. I am on windows 2003 ent version with SQL Server 2005 std version. I have configured SQL Server to use AWE and min memery as 22G and max as 26G (I have 32G in total as physical memory). I do see the message in log as "Windows Address ... Enabled", but when I am using performance monitor to view total memory in kb for SQL Server, it never starts at 22G (usually at 1G). And I doubt why The account I am running SQL Server is an admin account, so it can definitely locked down the pages in memory. Also, I do think AWE is working somehow, after several hours in running, the total memory will up to 8, 9 or 11G. So, apparently, it is over 2G per process limitation. I still want to know. 1. Is my AWE setting working 2. If not, how to fix that 3. How to confirm how many memory is actaully using by SQL Server Thanks, NIng Tag: Show SQL search query in pages
  - 5
    - Problem with ID 17981 Hello, I’m trying to do an update through a system but this message display a problem that I didn’t get to identify exactly the problem to looking for a solution. "Table error: Object ID 17981, index ID 1, page (0:871674153). Test (offset + len < PAGESIZE) failed. Slot 10, offset 0x1a8 is i Error while undoing logged operation in database " Could anyone help me please Thanks, Mauricio Tag: Show SQL search query in pages
  - 6
    - After restoring MASTER database, can't start SQLServer Service: Configuration block version 0 is not a valid version number Thanks. Anyway, I tried rebuilding and that DID work. But when I restored the master database, I was back where I started. I KNOW the database installations are the same version as I did SELECT @@VERSION on both and they match. So I don't understand why restoring a tape from one server to the other causes the other server to throw this error when the versions of the server do appear to be the same. Tag: Show SQL search query in pages
  - 7
    - error: Could not find SP upd.sp_MSrepl_xxx_xxxxxx_1 The plot thickens... If I create a subscriber on the same box as the publisher (i.e. publisher & subscriber are both SQL2000) it all works fine. If the subscriber is SQL2005 it errors looking for SPs owned by 'upd'... Tag: Show SQL search query in pages
  - 8
    - Plz do evaluate this SP Aravind: I think that for step #3 that you might be able to simplify this: Code Snippet Insert into @ResponseList (DynamicQuestionID , Response , SortOrder) select a.DynamicQuestionID , b.Response , a.SortOrder From DynamicRegistration_QuestionOption a left outer join DynamicRegistration_QuestionResponse b on a. DynamicQuestionID = b.DynamicQuestionID And a.QuestionOption = b.Response Where a.DynamicQuestionID = @DynamicQuestionID --'B440AC87-FC53-4735-A6D8-EB4DBFD9D447' And b.UserID = @userID And a.Inactive = 0 order by a.SortOrder Select @count = count(*) From @ResponseList IF (@count > 0 ) Begin While (@loop < 3) Begin if Exists(Select * from @ResponseList Where Pos = @loop) Begin Insert into @ResultSet (ResultValue) values(1) End else Begin Insert into @ResultSet (ResultValue) values(0) End Set @count = @count + 1 Set @loop = @loop + 1 End End Else begin Insert into @ResultSet (ResultValue) values(0) Insert into @ResultSet (ResultValue) values(0) Insert into @ResultSet (ResultValue) values(0) end Select case ResultValue When 0 Then 'False' Else 'True' End as ResultValue from @ResultSet return END to something like this: Code Snippet select case when y.Seq is not null then 'True' else 'False' end as ResultValue from ( select 1 as Seq union all select 2 union all select 3) x left join ( select row_number() over ( order by a.SortOrder, a.DynamicQuestionId ) as Seq, a.DynamicQuestionID, b.Response, a.SortOrder From DynamicRegistration_QuestionOption a left outer join DynamicRegistration_QuestionResponse b on a. DynamicQuestionID = b.DynamicQuestionID And a.QuestionOption = b.Response Where a.DynamicQuestionID = @DynamicQuestionID --'B440AC87-FC53-4735-A6D8-EB4DBFD9D447' And b.UserID = @userID And a.Inactive = 0 order by a.SortOrder ) y on x.Seq = y.Seq and y.Seq <= 3 The final thing that I cannot stress enough is to continue to ask for another set of eyes to look over your work! It is for that reason that I really would like for some of the other more senior members of this forum to also add their comments. I frequently miss something and sometimes just flat out get something wrong. My main motivation to joining this forum was to learn and YOUR question today has helped me to continue to learn. I hope you will continue to contribute. Kent Tag: Show SQL search query in pages
  - 9
    - Hide parameter if other paramter is null What I have is a report using Reporting Services 2005 that will take in a parameter through the URL called RepID, so the parameter is hidden inside the report because it's passed through the URL. This will run the report for the specified Rep. However, I'd like to have another paramater (Or also using RepID) just as Admin which would be a boolean and when Admin='N', the RepID parameter should not be displayed on the screen and just be accepted through the URL paramater RepID. However if Admin='Y' is the URL paramater passed, I want the RepID parameter to be displayed in the report so that the Admin can enter whatever RepID they want to run the report. Is there anyway to do this without creating a separate Admin version of the report Tag: Show SQL search query in pages
  - 10
    - Stored procedure vs. query From a security perspective , I would never allow an application to directly access data tables. From my experience, all data access 'should' be done using Stored Procedures. The code base is centrally managed, and can easily be changed as system and business needs evolve. For example, imagine the impact on the application if data tables have to be re-organized for storage or speed issues. With stored procedures, it is only necessary to change the procedure code so that the same resultset is produced -the application doesn't care where the data comes from as long as it gets what is needed. But when using inline queries, any changes to the data stores can cause catastrophic failure of the application until all queries are located and changed. (From experience, believe me, over the life span of a project, there will be businss needs to change where/how the data is stored .) Also stored procedures can be easily changed to allow auditing. There are a lot of applications that are currently being re-written because of HIPPA and SarBox requirements. In addition to logging changes to data, HIPPA requires logging EVEN a data read operation that touches medical data. You can't do that with an inline query. And in a few occassions, after project roll-out, it was discovered that the application 'should have' done a better job of validating data before sending it to the data server. With a stored procedure, it was quick and easy to add validation code without having to trace out all locations in the application code where a query may have inserted data into a table. (Time from discovery, solution, testing, and 'fix': 45 minutes -no application re-deployement to 500+ desktops.) But from a 'purely performance' perspective, with SQL 2005, there is little difference in most situations. (Though there 'can' be large differences in certain situations.) Tag: Show SQL search query in pages
  - 11
    - data access to large tables in sql 2005 hi all, i have a large table in sql server 2005 (it has about 6 columns and 10 million records). i need to work in a linear way on all the records (i know it sounds dumb but i need to work on all records). now, obviously when trying to work on this table sql server get stuck for timeout or something like that... i've noticed that a simple function like "select top 100 * from ExportTable" still works. is there any way to have sql send me the data when it access it so that i'll still be able to proccess it on the same time, i basically work using dataset so that fixing the timeout wont be helpfull since windows probably wont allow me to load this amount of data into memory. can any1 help Z Tag: Show SQL search query in pages
  - 12
    - SQL Server on Windows CE 5 - I read somwhere that the CAB files needed for installing SQL Server CE on a WinCE OS can be obtained with SQL Server 2005. Does Express edition have those files - I also read that same files are also distributed with VS2005. Since VS2005 is a development product, what are distribution for those CAB files when packaged with VS2005 - Additionally, there is SQL Server CE 1.7MB separate download but I found that the desired CAB files are not distributed with it. Does that mean that that edition is intended to mimic behavior of SQL server running on WinCE Thanks, Jaydutt Tag: Show SQL search query in pages
  - 13
    - Upgrading SQL Server 2000 Enterprise Edition to SQL Server 2005 Standard Edition does this mean I can attach msdb from a SQL2000 (ent) to SQL2005 (std) install No you cannot attach/restore the system dbs as it is different edition and version as well ! your only option is to script the jobs and logins ! you can only attach the user dbs and similarly restore the user dbs ! - Deepak Tag: Show SQL search query in pages
  - 14
    - Should SQL 2005's BPool reservation size vary with "-g" startup parameter? Hello, all We are trying to work out a memory problem associated with a SQLCLR procedure we have developed. This procedure will run properly on a SQLExpress box, but fails with an Insufficient Memory error (701) when run on our 32-bit enterprise edition server on W2K3 Enterprise Server. The server has 3.25 GB of RAM, and does *not* have the /3GB switch enabled; AWE is *not* enabled on SQL Server. I initially suspected the problem was due to the idiosyncracies of how the BPool is allocated on SqlExpress and its 1GB buffer pool limitation, giving a 1GB MemToLeave region for SQLCLR allocations -- substantially larger than the default available on the server with it's default 256MB allocation (+128MB for thread stacks). That seemed to explain why a memory problem might not manifest itself on the client, but would on the server. I then altered the server to include an explicit "-g" parameter at startup, reserving 512MB for MemToLeave, but the query still fails with the same memory errors. I've tried values as high as 1GB (-g1024), but none have worked Our DBA has installed a recent hotfix associated with certain memory errors, to no avail. It appears that the server will max out the CLR allocations at 102MB, regardless of the presence of the -g parameter. Could it be that the MemToLeave region really is that severely fragmented There are still log messages indicating failures to reserve virtual memory... One thing I notice is that the buffer's VM Reserved value never seems to change regardless of the value of the "-g" option. I would expect to see it decrease by the amount specified as reserved, eg -g 768 should leave, for example, something like 1.2GB VM Reserved for buffer pool allocation, but it doesn't. That tells me I'm not really establishing a larger MemToLeave area. Is this expectation incorrect Bottom line, we don't know why this procedure is running on the server, but not in SqlExpress. It is a procedure that connects to a remote Oracle database and collects about 14,000 records, but dies after sending about 7,000 or so records when run on the server. Again, the same query running against the same code on a SqlExpress box works perfectly. Any thoughts or suggestions welcomed and greatly appreciated. David Tag: Show SQL search query in pages
  - 15
    - Trouble with Workflow DTS (SQL 2000) Thank you Phil Brammer Tag: Show SQL search query in pages
  - 16
    - Calculated Measures using AMO Hi Matt, This is exactly what I was looking for, Quote > "......However, if you are creating all your caclulations from code you can choose to put each of your calculations in a seperate Command object within the MdxScript...... " 1. How do we create calculations from code 2. How and where to use the "Calculate" command that you specified Please provide a sample line of code or a link to the same, It would help me to understand better. Thanks a lot for all your help. Regards Vijay R Tag: Show SQL search query in pages
- Next
  - 1
    - Entering data into tables How does one enter data into the tables of a new database in the first place I'm using SQL Server Management Studio Express. I can name the columns, but that seems to be as far as I get. Thanks for any Help. Tag: Show SQL search query in pages
  - 2
    - INDEXING A VIEW in MSSQL2K5x64.de Standard w.SP1 ... ERROR 1939 I'm experiencing a problem while trying to index a view ... I'm using a single field and everything else are default entry's. I'm getting the Error Message 1939 . The code is a follows: USE [CS_WGO] GO SET ARITHABORT ON GO SET CONCAT_NULL_YIELDS_NULL ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO SET ANSI_PADDING ON GO SET ANSI_WARNINGS ON GO SET NUMERIC_ROUNDABORT OFF GO CREATE UNIQUE CLUSTERED INDEX [IDX_ALG_ORG_BEZ_V_1] ON [dbo] . [ALG_ORG_BEZ_V] ( [NDL] ASC ) WITH ( STATISTICS_NORECOMPUTE = OFF , SORT_IN_TEMPDB = OFF , DROP_EXISTING = OFF , IGNORE_DUP_KEY = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = OFF ) ON [PRIMARY] GO And the Error is: Msg 1939, Level 16, State 1, Line 1 Index kann fur die 'ALG_ORG_BEZ_V'-Sicht nicht erstellt werden, da die Sicht nicht schemagebunden ist. For those of you who do not read German ... it means something like ..." The Index kann not be created because the view is not bound to a schema. I don't understand what that means for me ... can someone explain that to me a little bit more in depth ... Thanx ... wvg Tag: Show SQL search query in pages
  - 3
    - SQL Reporting Services performance on navigation pages. kind of what I thought.. thanks for the clarification.. I did find another thread that sort of addresses my issue of slowness on the first load. if you're interested take a look. thank you, Ray http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=477143&SiteID=1 Tag: Show SQL search query in pages
  - 4
    - Select statement to output a date Hi, Try the below scripts 1.) Code Block SELECT CONVERT( varchar(40), GETDATE(), 107) Output for above code : Oct 08, 2007 2.) Use this code to get the output as you said Code Block select datename(mm,getdate()) + ', ' + convert(varchar(2),datepart(dd,getdate())) + ' ' + convert(varchar(4),datepart(yy,getdate())) Output for above code : October, 8 2007 Tag: Show SQL search query in pages
  - 5
    - Dumb question about compatibility level It really sounds like you are running SQL Server 2000. If you did an in place upgrade from 2000 to 2005, the default instance gets upgraded to 2005. If you installed a named instance of 2005 on the same machine where 2000 was running, maybe you are accidently connecting the the default instance that is still running SQL Server 2000. Running @@VERSION will settle the question. Also the version number of the SQL instance you are connected to will show up in SSMS. If you are in a query window, it will say something like 9.0 SP1 down at the bottom, or in Object Explorer it will say SQL Server 9.0.3042 (for 2005 SP2) Tag: Show SQL search query in pages
  - 6
    - Updating the variable's value Thanks for your fast replies.. However, yes the send mail task happens after the data flow. I even didn't create except three user defined variabes of type Int32 and the initial values are set to Zero. Therefore, it can't be a confusion of names by any means, which is driving me crazy! What do you think this might be ! Another interesting peice of information is that the send mail task generates some error and gets red; however, the mail is sent!! Does this affects the values of the variables anyhow Thanks in advance.. Tag: Show SQL search query in pages
  - 7
    - BCP (BULK INSERT) with only <lf> as line delimiter (not <cr><lf>)... Hi, This might be a noob question, but is there any way to specify just <lf> as a line delimiter, and not <cr><lf> ( /n ) as line delimiter in T-SQL call for BULK INSERT (or BCP utility). How please TIA. Tag: Show SQL search query in pages
  - 8
    - Replication using SQL server 2000 (SP4) .. urgent... Hi, We have few branches with individual dbs are sitting in the branches itself. Periodically all the data need to be updated to a single HQ database. At the moment, all the tables's primary key are unique and each branch will have branch ID's in the transaction tables. e.g. Branch1 data need to be updated to HQ db. However, HQ db records which are related to Branch 1 data only need to be updated back to Branch 1. other branch records should not goto Branch1. Similaryly other branch records need to be updated/replicated back to HQ database. And only records related to the particular branch need to be updated from HQ (if any changes made).. At the moment, i can do simple replication from one db to another db only... Can anyone guide/give suggestions on how to go about doing this If possible, please give steps involved... TX Tag: Show SQL search query in pages
  - 9
    - New Database I want to design a new database. When I right-click on 'Data Connections' in the Database Explorer from VB I notice that the 'Create New SQL Server Database...' is greyed out, so I can't create a new database. What is going wrong here, please help. Tag: Show SQL search query in pages
  - 10
    - Analysis Services Lock-ups Thanks for the help. Do you know how licensing would work in this kind of environment with sychronized SSAS databases I'm thinking that the extra SSAS services should be free. Users are telling me that these new SSAS cubes max out at 2 or 3 concurrent users. Depending on the types of data they are pulling, they are constantly locking each other up. (No, don't blame my cube design. I was already following those best practices in Oct '05. ) Tag: Show SQL search query in pages
  - 11
    - Version for procedures Hi Is there any way to check a version of a procedure I mean I need to have version or comment for my procedure to indicate wicth procedure is currently is used in database Tag: Show SQL search query in pages
  - 12
    - table column extended field properties through MS SQL Server Management Studio I have a database defined as: dbname - Tables - dbo.xyz -Columns Field definition_a Field definition_b Field definition_c ... For each "Field definition" I can define "extended properties" So the question is, from a stored procedure in C#, how can I read through the "Field definitions" looking for an extended property of a specific integer value /Boyd Tag: Show SQL search query in pages
  - 13
    - Checking to see if a record exists and if so update else insert Can somebody point me to a simple example of this. I have a flat file and I want to check if the record exist in the file exist in the table, if it does I want to update and if it does not I want to insert. Tag: Show SQL search query in pages
  - 14
    - DISTINCT COUNT WITH NULL VALUES (GRAND TOTAL) Hello again. I was working with KPI's and noticed, that this calculation needs to be improved. When I try to filter by any particular professor, the Grand Total remains the same - for the projects always "59". How could I change the calculateted member Thank You in advance. Tag: Show SQL search query in pages
  - 15
    - Cannot Login VB express and SQL express are on the same desktop. I have an application in VB that works fine.The issue I have is if I go into SQL Management to work on the DBand then close SQL Management Express. When I go back to execute my VB application I get a message like this " Login failed for "user name" file is in use by another application". If I reboot my system and then run the VB application it works OK. I've tried doing a disconnect before I exit SQL Management but this had no affect on the problem. Is there something I need to do when I exit SQL Management Express so I can immediatley run my VB applicaton without having to reboot Tag: Show SQL search query in pages
  - 16
    - SQL Server dropping comma in German language All, I've a VB6 application that writes data to a SQL Server database using ADODB in the VB6 code. My market has been the United States. My application is written for the English language. I've a new user in Germany. Unfortunately, every number that contains a decimal is now stored in the SQL Server database with the decimal dropped. For example, 29.15 gets stored as 2915. I've done some searching and found the culprit is probably the OS Regional Language setting. The Germany user is using German. So it seems ADODB is expressing "29.15" as "29,15", and SQL Server is having a fit when ADODB is trying to write to the database. My client is reluctant to changing all their user profile language settings to German (or the other option, changing their German language setting decimal symbol option to "."). They fear other applications may be affected. Can anyone suggest a solution on my software end Does ADODB have some options of which I'm unaware that will ignore the regional language setting and express decimal-containing numbers simply as "." Or is there a setting in SQL Server to compensate I appreciate all and any help. Thanx! Tag: Show SQL search query in pages

Mr Sam

How to show a SQL search query in pages for example 10 records in each page, like search engines.
I'm using .NET Framework 2, SQL Server 2000 and Adobe Dreamweaver CS3.
Thanx

Top

Re: Show SQL search query in pages

Jens K. Suessmeyer

YOu can have a look a "server side paging" which should return tons of hits in any search engine.

http://weblogs.sqlteam.com/jeffs/archive/2004/03/22/1085.aspx

Jens K. Suessmeyer.

---

http://www.sqlserver2005.de
---

Top