I was writing a view that required all of the fields from several large tables. Though I used the * operator in my SELECT statement, SQL Server automatically expanded it to show all of the columns every time I saved the view. Is there a setting somewhere that I can change to make it stop doing that? The query becomes extremely difficult to read because there are so many fields.
View 11 RepliesI want to expand a log device and am having a problem. When in Edit Database Device, the `Change Now` button is disabled and the size of the device is shown as a negative number.
View 1 Replies View RelatedI have a SQL 6.5 database that we parse some data into everyday usingan access program. All this was devises and setup by a programmer thatI can't get in contact with anymore and it has actually run for aboutfive years without a hickup! But just a few days ago our parsingprogram just stops dead before completing and I did get this errormessage."exportaLLdataToSQLifnoerror(): number 3146 Description- odbd- callfailed. [Micorsoft][odbc SQL Server Driver] [sql server] Can'tallocate space for object 'syslogs' in database 'newpdatasql' becausethe 'logsegment' segment is full. If you ran out of space in Syslogs,dump the transaction log. Otherwise, use ALTER DATABASE orsp_extendsegment to increase the size of the segment. (#1105)"I have looked at the database in SQL referred to and I notice that itsays there is 200 MEg allocated for the log size and 250 meg for thedata size and that in both categories there is "0" space available.When I select "EXPAND" on this page it takes me to a screen which hasa graphical presentation with a bar chart showing the available spacein red and the used space in blue on each of what it refers to as'database devices". There are 10 items shown--Temp_DB available space 30 used 30, Newplog 200 available 200 used,NewpDev 250 available 250 used, MSDBlog available 2.00 used 2.00,MSDBData 6 available 6 used, Master 50 available 50 used, Data_log2.00 available 2.00 used, Dale_data 12 available 12 "free space" (this one says free space instead of used?), Contact_log 25 available15 used, Contact_dev 100 available 75 used.So I can see on the first screen that there was 450 meg allocated tothis database called newpdatasql and that all 450 is being used. Andwhen I go to the next screen that shows the devices in this databasethat many of them show that their available space is completely used.The fix is probably to expand the available space for the database. Onthe second graphical screen that shows the bar chart there is anbutton called "expand now" that is greyed out except when you selectthe different devices in a drop-down box at the top of the screen.There are two drop down boxes -- one titled "data devices and onetitled log devices. THe only time the Expand now button is not greyedout is when I select "Dale_Data, "Contact_log", or "Contact_dev". Idid select the "contact_Dev" device clicked on expand and it seemed toI guess expand this device to a fully used level as after I did thisit says that the space available is still 100 meg ( see commentsabove) but now instead of 75 used it says 100 used! Progress or what?I guess I could do this for the other devices as well. One issue forme is whether when the space available for a device is the same as thespace used does this mean that it is at maximum capacity in terms ofdata or only that the space available on the disk for this device iscompletely being made available as potential storage space?Interestingly the original error message mentions "syslogs" and"logsegment" neither are available as devices or are named in thescreens relating to database expansion.Any help would be greatly appreciated.My tollfree number is 866-957 1081.Jeffrey Kilpatrick
View 1 Replies View RelatedIf I have a CustomerDB on ServerA, is it possible to
move/expand the transaction log, .Dat or backup files to
ServerB?
Newbie SQL question - what logic does SQL Server use to place limits on the extendable size of a database device? I have gigs of room on a partition and I would like to extend a 1GB device to accomodate database growth - but for some reason, Enterprise Manager reads that the max size I can extend the device is only 600MB (when I have 4GB free on the partition). Am I just reading the tool incorrectly?
View 2 Replies View RelatedI have a table (we'll call it Ranges) of number ranges:
ID Start End
1 12345 12347
2 12355 12359
3 12380 12389
4...
Note: Start and End are text values..What I need is to output these ranges with their corresponding ID:
ID Number
1 12345
1 12346
1 12347
2 12355
2 12356
2...
Note: Number needs to be text.
SQL 2012 version
Microsoft SQL Server 2012 - 11.0.5532.0 (X64)
Jul 14 2014 15:00:27
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
management->SQL Error Log-> list of error files are not expanding,Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)A severe error occurred on the current command. The results, if any, should be discarded. (Microsoft SQL Server, Error: 0)
Okay, so i have a Dual Xeon SQL2k5 Server set up. it's got 4 10k RPM Raptor Drivers (160gb) on it all stripped together. my question is this, i was just doing all kinds of index tuning, and my server didnt have enough disk space to create 2two of the recommended adjustments. i'm curious, if i should just have those two indexes stored on the 1TB Backup array i have? (two 500gb 7200RPM drives)
or should i backup the database, and expand the Raptor Array and then restore the database and create the indexes?
i'm not really worried about down time as long as it doesnt exceed 2 days.
When running Enterprise Manager from my desktop pc to try and expand a database device I am unable to do so. The size shown is negative, and the max size is the same, with the 'Change Now' box greyed out.
When I log on directly onto the server I can easily expand the db device. There is lots of free space on the server.
Our set up is quite small with four NT workstations and two NT servers with SQL Server 6.5 on them. All these six machines are in the same workgroup. The workstations are on a separate floor to the servers, which are in the computer room. I have a feeling this is a networking issue. Can anyone suggest why this is happening?
Hi, I have created a 30 gb device_db and named it device_A.Then, I am only allocate 15 gb for my database in that device... My question is if I decided to expand the size of my database in device_A, is there any limitation of how much expansion I can use... for instance can I put 15000 in the Data Device to expand the size of the data base in device_A.... I am not sure if I have to use this in one step, as I explained before or I expand by 500 mb( which will take me 30 times)... any bright ideas
regards
Ali
Is there any set option with which, the text output from select statement can be changed. A select of any varchar field defaults to 256 characters. If I define a field of varchar(1024), how can I get an output representing all the 1024 characters. Thanks
View 1 Replies View RelatedI have a user assigned multiple roles and a role can be inherited frommultiple parents (see below). How do I answer such questions as "Howmany roles does the user belongs to?"I answered the above questions by using .NET but I think it can bemore efficient by using just SQL. I would appreciate if you can giveme an answer.Thank you.CREATE TABLE [dbo].[tb_User] ([Id] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,[Name] nvarchar(99) NOT NULL UNIQUE,[Password] nvarchar(99) NOT NULL,) ON [PRIMARY]GOCREATE TABLE [dbo].[tb_Role] ([Id] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,[Name] nvarchar(99) NOT NULL UNIQUE,) ON [PRIMARY]GOCREATE TABLE [dbo].[tb_User_Role] ([UserId] [int] NOT NULL ,[RoleId] [int] NOT NULL,) ON [PRIMARY]GOALTER TABLE [dbo].[tb_User_Role] WITH NOCHECK ADDCONSTRAINT [PK_tb_User_Role] PRIMARY KEY CLUSTERED([UserId],[RoleId]) ON [PRIMARY]GOCREATE TABLE [dbo].[tb_Parent_Role] ([RoleId] [int] NOT NULL ,[ParentRoleId] [int] NOT NULL ,) ON [PRIMARY]GOALTER TABLE [dbo].[tb_Parent_Role] WITH NOCHECK ADDCONSTRAINT [PK_tb_Parent_Role] PRIMARY KEY CLUSTERED([RoleId],[ParentRoleId]) ON [PRIMARY]GO
View 1 Replies View RelatedHi friends,
I am doing a matrix report. in this report the matrix can extend to either 1 day or it can extend to 28 days depending upon the range of parameter we give.
example: if we give range of date 7/16/2007 - 7/16/2007 then only 1 column of data will come but if i give the date range as 7/9/2007 - 8/5/2007 then 28 columns will come as 28 days are selected.
i have given the body of the report such that it accomodates all 28 columns. i have few labels that i have to keep on the page header. the width of these textboxs i have given same as the matrix width when it will have 28 columns. in pdf exports the labels in the export comes aligned with the matrix when 28 columns are selected but in html the matrix expands. so it does not look aligned with the labels in the page header.
I hope the above explanation is clear.
can anyone help me on this.
Thanks.
Microsoft SQL-DMO (ODBC SQLState: 42000) Error 1827: CREATE/ALTER DATABASEfailed.There's more, but it's in italian and I wouldn't know how to translate itproperly.It basically says that the requested size is exceeding some 2048mb limit.I can't see any limitation of this kind in the license. Is there something Iam missing here?Thank you in advance- andrea
View 3 Replies View RelatedWe are currently using an application which uses SQL Server 2005. Both sit on the same box.
This month we're adding another piece of software to our network on a new box which also depends on SQL Server. Since we don't have a directed attached storage device to store (as recommended by Microsoft), we're contemplating installing SQL Server again just for this application. Of course this means that we have to maintain two separate SQL Server databases.
Any thoughts on what we should do?
Thanks
Pleeeease help? I get an error when expanding the Tables object under linked servers in Enterprise Manager on a MSSQL2000 server.
I have a standard install of the Pervasive.SQL V8 data engine and DemoData db on my local PC. The SQL Server is also on my local PC.
I manage to create the Linked Server using the MSSQL Enterprise Manager. I used the following configuration:
Provider Name: Pervasive.SQL V8 OLE DB Provider
Product Name: Pervasive.SQL V8
Data source: DemoData
Provider string: <blank>
Location: workstation03
Catalog: <blank>
However when I try to expand the tables object under Linked Servers in the Enterprise manager I get the following error.
Error 7311: Could not obtain the schema rowset for the OLE DB provider 'UNKNOWN'. The provider supports the interface, but retuns a failure code when it is used. OLE DB error trace [OLE/DB Provider 'UNKNOWN' IDBSchemaRowset::GetRowset returned 0x80040155 ].
How would I fix this?
Thank you.
Hello Everyone,
I am trying to expand the multi-valued parameter menu object so I can display the selection list in a more user-friendly format since the text is fairly long. If left at the default setting then I have to scroll to the right to see the complete string(s). For a single-select parameter I can just CAST the string to CHAR(100) and the menu object will expand. However, it seems that this approach is not working for multi-valued parameter menu objects.
Is this a bug in SSRS, and is there another way to accomplish this. Please let me know.
Thank You!!!
I have a report with a single table, single grouping level, single data set and no sub-reports. It has 3 rows for a grouping header and 3 rows per dataset row of detail. The detail rows are initially hidden and can be expanded by clicking on the header +. Its a fairly standard master-detail report.
Regardless of data size, I get NO page breaks in HTML. I have the Interactive size set to 8.5x11, KeepTogether is set to False, and PageBreakAtEnd is set to False. I would like it to break based on the visible grouping rows.
As it is now, everytime you expand any section, it takes forever to reload for a larger recordset.
I know that "HTML renderer and Preview (which are soft page break renderers) will ignore page breaks of conditionally hidden items and their children.", but how do I get this report to page break?? I've seen a lot of posts on this, but none that seem to have an answer.
I have a reportviewer on a master page in a VS2008 ASP.NET application. When I originally created the page, I did not use a master page. When I moved the code to a master page, the Toolbar is 6 line long instead of 1. Each of the individual components of the ReportViewer toolbar is placed on it's own line. (i.e Navagation, Print, Zoom, Etc.) The same control worked correctly on it's own page.
View 2 Replies View RelatedOn Visual Studio 2013 with SSDT for SQL Server 2014
I noticed something very strange.
I am viewing the package configuration selected option for a package:
Now, i just click on the - sign and then on the plus sign on the "Properties" branch. And the option is cleared:
The ONLY way i found not to mess everything is just to have ALL the options FULL expanded and THEN to select what i want.
Hi,
Is it possible to create Expand/Collapse functionality for the grouped data in Table and Matrix data regions? Essentially, the idea is for the user to be able to see the group/subgroup data if she wishes to by clicking on (+/-) symbols, as is usually the case in Tree View style data grid control in web apps. Any ideas how to accomplish the same in reporting services?
Thanks.
I have a database in development in SQL Server 6.5 that needs to be occasionally deleted and rebuilt from a script when table structures are changed. I found that when very complex queries were performed, the 2 MB default size of tempdb filled up and returned errors, so I went to the Enterprise Manager to expand tempdb, learned that I had to first expand a device to expand tempdb into, and foolishly chose to expand tempdb into the same device space used by my application, instead of into one of the system databases. Now when I try to delete the device in preparation for its rebuild, the Enterprise Manager responds with an error message saying the device can't be deleted because it contains system tables. Is there any way to get the expanded portion of tempdb out of my application device so that the device can be deleted, without reinstalling SQL Server?
View 1 Replies View RelatedI have a subreport on my main report, and the subreport contains a matrix. When I run the report, the subreport seems to expand beyond the width of the main report. The matrix itself does not expand beyond the width of the body of the main report, but for some reason it seems that the subreport does. The subreport does not contain any headers that might be messing up the width. If I cut and paste the matrix from the subreport directly onto the main report, and remove the subreport, it prints fine. But as soon as I include the subreport on the main report it prints with blank pages because the subreport expands beyond the width of the body of the main report. I have checked the width and margins on the subreport and compared to the width of the main report and all looks good. Can anyone help?
Thanks.