Hello DBA's
I have installed SQL Server 2005 on a machine with named instance, but later I noticed that I created it with the wrong name than desired. Now I need to change the instace name of the SQL Server that I have installed. How can I do that
Thanks
Satya
There is not a supported way to rename an instance. You need to uninstall and then reinstall.
Thanks,
Peter Saddow
|||Thanks Peter
Satya
Hi,
I created on model in Business Intelligence Development studio.I wants to change the datasourceview(dsv) assigned for that view.for e.g
My mining Model is StudModel.dmm.
The dsv for that model is StudDSV.dsv.
I wants to change the dsv for that model to marksDSV.dsv.
How to do that in Business Intelligence Development studio.
Thanks,
Karthik.
The BI Dev Studio does not provide you with much support for this task.
You can change the DSV manually. It may be very easy or more difficult, depending on how similar the new DSV is compared to the original DSV.
Here is something you can do, manually:
- close the Mining structure designer window
- open the DMM file in BI Dev Studio (right click and select the View Code option)
- look for the <DataSourceViewID> element in the XML. Replace the content of the element with the id of your new data source view
- close and save the file
- double click on the DMM file to load it in the designer and validate the change
Now, if your new data source view contains different tables, or if those tables have differrent columns, more work needs to be done.
Note that the changes will only be visible after you re-deploy and process fully your mining structure
Hope this helps
Hi All,
Suppose I have created one table say "Customer"
Customer (cust_no, cust_name,cust_address)
I want to change the attribute name "cust_no" to "cust_number".
How to do it using ALTER command.
Pls give me the exact syntax for this.
Regards
Abdul
Hi abdul,
The safest way to do so is using the following:
/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
EXECUTE sp_rename N'dbo.Customer.cust_no', N'Tmp_cust_number', 'COLUMN'
GO
EXECUTE sp_rename N'dbo.Customer.Tmp_cust_number', N'cust_number', 'COLUMN'
GO
COMMIT
(An easy way to generate a script like this yourself is changing the layout through SQL Server Management Studio, right-clicking the table, selecting "design", then making the change, and before doing anything else, clicking the left-most button labelled "Generate change script").
HTH.
Peter
Hi,
I've created a SQL Server Express Database that is used by an ASP.NET 2.0 application (developed using VWD Express).
I would like to be able to:
I know that I need to change my connection string - and am able to achieve that with ease - but I'm not sure what the best way of switching between the two ways of attaching on the database side. I keep getting logon type problems etc.
Thanks very much.
Regards
Gary
Hi,that should be normally straight forward using on of the user instances string for "attaching" the database to a instance temporarly (www.connectionstrings.com) Which error do you get while attaching ?
HTH, JEns Suessmeyer.
http://www.sqlserver2005.de
The above command displays user tables. Get each user table name from sysobjects by using cursor or while loop pass the same as input parameter ot the sp_changedbowner
Try ....|||or from enterprise manager,right click on users and from opened users adjust ur adjustments
good luck|||
Quote:
Originally Posted by papayaya
Our programmer has left the company and the tables and views were created with his credential as owner.
How do I change the ownership of all the tables and views using the stored procedure - sp_changedbowner?
Instead of typing the individual table and view name to change the db owner, is there a way to change the owner at once?
This will change the owner of the current database to whatever username you want ( you could put sa in there ) and then remap any alias as well (that's the true).
// John Stone
Quote:
Originally Posted by papayaya
Our programmer has left the company and the tables and views were created with his credential as owner.
How do I change the ownership of all the tables and views using the stored procedure - sp_changedbowner?
Instead of typing the individual table and view name to change the db owner, is there a way to change the owner at once?
Shrimant Patel
&
Harith Patel
Not sure what you mean with different data base. Is the underlying schema always identical and just the data base server / name is different?
If yes, then various approaches for dynamic database connections in RS 2000 are
available:
* Use a custom data processing extension
http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_extend_dataproc_5c2q.asp
* Use the SOAP API by calling SetDataSourceContents:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_ref_soapapi_service_lz_2ojd.asp
* Use the linked server functionality of SQL Server - but read the SQL Server documentation carefully about potential performance impacts.
* If the databases are on the same server, use a dynamic query text (i.e.
="select * from " & Parameters!DatabaseName.Value & "..table")
* If you're just toggling between two or three databases, you can publish the same report 3 times with 3 different names using 3 different data sources and write a main report that shows/hides the correct subreport based on whatever criteria you want.
In addition, native support (expression-based connection strings) is available in RS 2005: Finish the design of the datasets with a constant connection string and make sure everything works. Then, go back to the data tab and open the dataset/data source dialog and change the connection string to be an expression. Use string concatenation to plug in the parameter value. Here is an example of how the RDL would look for a parameter-based connection string:
<DataSources>
<DataSource Name="Northwind">
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>="data source=" & Parameters!ServerName.Value
& ";initial catalog=Northwind;"</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<ReportParameters>
<ReportParameter Name="ServerName">
<DataType>String</DataType>
<Prompt>ServerName</Prompt>
</ReportParameter>
</ReportParameters>
You can also check this blog posting:
http://blogs.msdn.com/bwelcker/archive/2005/04/29/413343.aspx
-- Robert
I am building a report which has 7 columns. Mon thru Sun. The header and data columns are dynamic
For ex the report looks like
Mon Tue Wed Thu Fri Sat Sun
-- - -- - --
2 2 4 2 4 5 6
0 7 6 7 9 4 2
The report header and data are dynamic. The seq of columns,data is not always the same. It could be Tue thru Mon., or Fri thru Thru...etc...
Please advice how to program this dynamic nature of the report
|||Hi KumarYou should change the query so that instead of returning one column per day you "denormalize" it to return one day per row:
Day Count
-- --
Mon 2
Tue 2
Wed 4
...
In the report, you could then just group by the Day field (in a matrix), or use conditional aggregation if you choose a table layout: e.g. for the Monday column in the table you would use =Sum(iif(Fields!Day.Value = "Mon", Fields!Count.Value, 0))
-- Robert
Not sure what you mean with different data base. Is the underlying schema always identical and just the data base server / name is different?
If yes, then various approaches for dynamic database connections in RS 2000 are
available:
* Use a custom data processing extension
http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_extend_dataproc_5c2q.asp
* Use the SOAP API by calling SetDataSourceContents:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_ref_soapapi_service_lz_2ojd.asp
* Use the linked server functionality of SQL Server - but read the SQL Server documentation carefully about potential performance impacts.
* If the databases are on the same server, use a dynamic query text (i.e.
="select * from " & Parameters!DatabaseName.Value & "..table")
* If you're just toggling between two or three databases, you can publish the same report 3 times with 3 different names using 3 different data sources and write a main report that shows/hides the correct subreport based on whatever criteria you want.
In addition, native support (expression-based connection strings) is available in RS 2005: Finish the design of the datasets with a constant connection string and make sure everything works. Then, go back to the data tab and open the dataset/data source dialog and change the connection string to be an expression. Use string concatenation to plug in the parameter value. Here is an example of how the RDL would look for a parameter-based connection string:
<DataSources>
<DataSource Name="Northwind">
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>="data source=" & Parameters!ServerName.Value
& ";initial catalog=Northwind;"</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<ReportParameters>
<ReportParameter Name="ServerName">
<DataType>String</DataType>
<Prompt>ServerName</Prompt>
</ReportParameter>
</ReportParameters>
You can also check this blog posting:
http://blogs.msdn.com/bwelcker/archive/2005/04/29/413343.aspx
-- Robert
I am building a report which has 7 columns. Mon thru Sun. The header and data columns are dynamic
For ex the report looks like
Mon Tue Wed Thu Fri Sat Sun
-- - -- - --
2 2 4 2 4 5 6
0 7 6 7 9 4 2
The report header and data are dynamic. The seq of columns,data is not always the same. It could be Tue thru Mon., or Fri thru Thru...etc...
Please advice how to program this dynamic nature of the report
|||Hi KumarYou should change the query so that instead of returning one column per day you "denormalize" it to return one day per row:
Day Count
-- --
Mon 2
Tue 2
Wed 4
...
In the report, you could then just group by the Day field (in a matrix), or use conditional aggregation if you choose a table layout: e.g. for the Monday column in the table you would use =Sum(iif(Fields!Day.Value = "Mon", Fields!Count.Value, 0))
-- Robert
Hi,
I have created a SQLCLR (database) using C#.. named SQLCLRtest
The Connection string is stored in SQLCLRtest.csproj file
here is code of csproj file
<Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
<PropertyGroup>
<AssemblyOwner>
</AssemblyOwner>
</PropertyGroup>
<ProjectExtensions>
<VisualStudio>
<FlavorProperties GUID="{c252feb5-a946-4202-b1d4-9916a0590387}">
<DatabaseConnection Name="Data Source=DBserver;Initial Catalog=TestDB;Integrated Security=True" Provider="{91510608-8809-4020-8897-FBA057E22D54}" ConnectionString="01000000D08C9DDF0115D1118C7A00C04FC297EB01000000A2744997FFD51E459D0421E51E830EF30000000002000000000003660000A8000000100000005B59ACF96DA2A587CBFA27595B0F245E0000000004800000A000000010000000BB1D5F562AC3FE7F56F8E57C36E0E7A4B0000000CE828F399233A389D95E2D99B2CAA64DE5F5A19EF0CBB716D195DF60EE38B58B0C07674E2F80538C02ED27200C79A71B0F6F9177E598089CDA95B8DDEEF966A958C6EDE4E72CABBC39941FEED534E3384EF3A4B4A51704726BF5D43F2C3C9BD674885B9675FECD86E54498ED9E1957FCD7DCF0CE8ED99C8529FD9234EB4E760FDD6819E3E42A7771E0A5B18452C01C13976C0DDDF1B5B87D75F0490762C6A004AD093A3DF9210F7D03371D67E4901EB51400000005557E36590040C06F796463ABFEC165D2E60750" />
</FlavorProperties>
</VisualStudio>
</ProjectExtensions>
</Project>
Problem:
I have an sp which start an external process, It is working at my local machine but does not work at remote server.
FACTS:
Local machine has SQLExpress (SQL version) while remote machine has SQL Enterprise ..
SQL service is running under the System Account at both machines..
I have enabled the Sql server Service to interact with desptop, so that it can start a process in GUI mode.
I thin there is something in connectionstring which does not allow the application to connect to server with appropriate rights.
How I can chage this setting .. is it possible to write this info manually..
Please, comments
Thanks
You should be able to change the connection string from the database tab in project properties. When you click on browse, It will allow you to choose from different existing connections or add a new reference.
Thanks,
-Vineet
|||Muna,
Did Vineet's post solve your problem? The reply is correct to my knowledge.
Derek
Any suggestions?
Best regards.
One way I could change it was to open the report in Designer. On the data tab you can change the report model here. If your report was built in designer then you are all set. The problem is when your report was done in builder. Then there is no going back.
If any suggestion on a better way to do while still keeping the report as a report builder version I would love to hear it.
Thanks,
Rahul
|||You can download the RDL file for a report builder report by looking at the properties for that report in Report Manager and clicking the Edit link. Then you can manually edit the RDL and change out the GUID for the old report model to be the GUID for the new report model. (Create a report with the new report model and then look at the RDL to see the GUID of the new report model.) Then you can click the Update link on the report properties and upload the modified RDL.
The only thing to be aware of is that if you're using a relational report model, the IDs for entities and fields are GUIDs. So if you built the new report model from scratch, the GUIDs for common entities and fields may be different.
|||That worked great. I guess we still need to keep with our Visual Notepad stills.
Thanks,
Rahul
