hello ... I am trying to copy 1 field from a table in database called DB1 to a same table in DB2 .. I am not sure if this is possible. I found out form www3.schools.com it's doable using Select Into ... but it uses MS Access for example (Northwind.mdb) ... I wonder what's the extension database in MS SQL? Does anyone know where can we check the query syntax on the web? Can anyone tell me it's correct? I dun have MS SQL from home .. so :(
======copy prodID from DB1 to DB2 table called production1 (both DB1 and DB2)=======
// this is done in SQL QUERY ANALYZER under DB1?
SELECT production1.prodID INTO production1 IN DB2 FROM DB1
=====create VIEW in DB2=======
// this has to be done in DB1 ... if I have multiple databases, is there anyway to do it using something like SELECT INTO to create view in differences databases?
CREATE VIEW production_id AS SELECT prodID from DB1
I'm trying to change the file extension of one of the database files of a big database that contains 10 years of EPOS data. When this file was created, at the beginning of 2008, with the following script:
USE [Live]
ALTER DATABASE [Live] ADD FILE (NAME = 'Live_2008',
FILENAME = €˜E:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataLive_2008') TO FILEGROUP [2008] the .ndf file extension was not added to the filename. So the file now has a file type of "File", instead of "SQL Server Secondary Data File" I tried, in a test environment, to bring the database offline, change the filename to "Live_2008.ndf" and bring the database back online, but it gives me an error message saying that "cannot open the Live_2008 file", i'm guessing because it's looking for "Live_2008" and it finds "Live_2008.ndf" instead. This file is quite big (5Gb) and I cannot afford to risk to lose any data. The situation is complicated even further by the fact that it seems that a partition for 2008 does not exist. How can I find out what data this file contains? Is it going to be an issue if I leave the file like that? Thanks
------------------------------------------------------------- Here's what our Immediate window Debugger is tellin' us about our configuration settings:
Now notice the stuff in bold...I have NO IDEA where this gosh-danged thing is reading, but it doesn't seem like it's the app.config file in our class library...
I have made the sql server 2008 database backup through proper procedure.I am doing this backup in remote server. The task is completed successfully. But When I check the folder that I made the backup,is showing extension as file,not .bak. Why the backup extension is not proper? And due to that,when I sent this backupfile through mail to someone,that person is not being able to retrieve or restore it..
I've been using Michaels excellent date function http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519 quite honestly it has saved me loads of time (the e-beers are on me)
For my purposes I need to work sometimes with Financial Years and I also need the Dates available to SQL Server 7. So I used Michaels function to create a table and added two fields (possibly more to follow). Please note that this has only been done on SQL Server 2000
ALTER TABLE DATEINFO ADD -- Needs these to allow NULL so that the Alter Table will work. FINANCIAL_YEAR_START_DATE[datetime]null , -- the start date for the financial year FINANCIAL_YEAR_START_YEAR[smallint]null , -- the year that the financial years starts in depending on the Financial Year Start Date
Given that the table is then populated by Michaels function, to set the FINANCIAL_YEAR_START_DATE I used
/* @ARBITRARY_FINANICAL_YEAR_START_DATE is SET to any date that would be the start date of a financial year only the day and month parts are important, so in this case the start of the financial year is 1 April */ DECLARE @ARBITRARY_FINANCIAL_YEAR_START_DATE DATETIME SET @ARBITRARY_FINANCIAL_YEAR_START_DATE = '20060401' UPDATE DATEINFO SET FINANCIAL_YEAR_START_DATE = CASE WHEN DATEDIFF(dd,DATEADD(yy, year([DATE]) - YEAR(@ARBITRARY_FINANCIAL_YEAR_START_DATE), @ARBITRARY_FINANCIAL_YEAR_START_DATE),[DATE]) < 0 Then DATEADD(YY,YEAR([DATE])- YEAR(@ARBITRARY_FINANCIAL_YEAR_START_DATE) -1, @ARBITRARY_FINANCIAL_YEAR_START_DATE) Else DATEADD(YY,YEAR([DATE])- YEAR(@ARBITRARY_FINANCIAL_YEAR_START_DATE) , @ARBITRARY_FINANCIAL_YEAR_START_DATE) END
With that it is trivial to set FINANCIAL_YEAR_START_YEAR to YEAR(FINANCIAL_YEAR_START_DATE)
steve
-----------
Don't worry head. The computer will do all the thinking from now on.
I was told by my website provider that they do not support the .mdf extension and only support the mdb extension. What am I not getting here? I am using SQL Server Express 2005. Is .mdf a subset of .mdb?
I am running SQL Server 2005 Enterp Trial version, which is about to expire in 14 days.. Is this possible to Extend this? Or Un-installing and re-installing may give me back 180 days again? I haven't tried this, but need to know before I do an attempt?
Don't have other machine, don't want to re-built my system..
I am in need of urgent help. i do believe that when you used the command SPOOL filename without specifying an extension name, it automatically puts an .lst extension name. Can you help me identify if there is any way i can generate a file without an extension name? I am in need of help right now. I hope someone will answer my post as soon as possible. I really really appreciate it!
Hi All,I am receiving the following message when I run Dts package which iscreating a cube.Error Source : Microsoft Data transmission Services (DTS)PackageError Description: File name or extension is too longAny help will be appreciated!Thanks in advance,Mohammed SarwarOcp dba oracle 9i,8i,8.0*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
When rendering extension for RTF or Doc or WordML will be available? Will it be released in service pack for SQL2K5 or it will be released in next version of SQL Server?
In document for encryption in SQLServer with application XP_Crypt, i see file with extension .pem when using private key or public key. How to create file .pem. Please explain me private key or public key.
We just upgraded to 2005 from a previous installation of 2000. Due to performance issues, I stopped the 2000 instance, and now the following message keeps popping up in the event log:
MS SQL SNMP Extension Agent reconnecting.
Following that, there are the following messages:
ODBC error encountered, State = 08001, native error = 17, error message = [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or access denied.. ------------- ODBC error encountered, State = 01000, native error = 2, error message = [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Connect()).. ------------- ODBC error encountered, State = 08001, native error = 17, error message = [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or access denied.. ------------- ODBC error encountered, State = 01000, native error = 2, error message = [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Connect())..
I have actually extended my requestI have a scenario like this.......update esan set tes_address_city = 'TEST1' --at some timeupdate esan set tes_address_city = 'TEST12' --at some timeupdate esan set tes_address_city = 'TEST123' --at some timebackup database TESTWMS to disk = 'D: empRecoveryTestTESTWMS.db'backup log TESTWMS to disk = 'D: empRecoveryTestTESTWMS01.log'I take these backups at the end of day....Now i want to recover till the second update ie 'TEST12'...Assumptions to be made:-I dont know the timing of the updates.I am allowed to take back only once.We can also assume to have a backup of the previous day also.Is this possible and how can i do it.......Please help me ...and urgent also.......Thanks in advanceRVGExtension is hereSuppose that ou database is crashing at 2.30 and i run the restoreRESTORE DATABASE TESTWMS FROM DISK='D: empRecoveryTestTESTWMS.DB'with norecovery --Prev day logRESTORE LOG TESTWMS FROM DISK='D: empRecoveryTestTESTWMS01.log'with norecoveryRESTORE LOG TESTWMS FROM DISK='D: empRecoveryTestTESTWMS02.log'with recovery, STOPAT = '2003-08-26 16:37:45.870'i get message like thisThis log file contains records logged before the designatedpoint-in-time. The database is being left in load state so you canapply another log file.RESTORE LOG successfully processed 0 pages in 0.389 seconds (0.000MB/sec).Now when i try to run aSelect * i get message lkeServer: Msg 927, Level 14, State 2, Line 1Database 'TESTWMS' cannot be opened. It is in the middle of a restore.How do i solve this problem. One way is to use a NO_TRUNCATE with abackup in between (ie incase of database corruption).But i dont want to use a log backup once again. I should be able tosolve it without using a log backup once again. In shot backup onlyonce a day. Aslo i need to refer to a database to that particularpoint of time from my backup.Thanks in advanceRVG
I have installed the SSRS 2005 Forms based login security extension as per the sample and instructions that come with the product install. I have double checked my work and debugged up to the point explained here.
My user is in the database and I can get to the login page. I have traced the code and can see the user being authenticated properly by server.LogonUser, the authentication cookie being created and placed in the response object, and then the redirect url being set to the reports/pages/folder.aspx. I have employed tcptrace and can see the sqlAuthCookie set in subsequent posts.
Unfortunately the redirect to folder.aspx never happens. I keep returning to UILogon.aspx. I think their is something either wrong with the auth cookie or the web.config in the ReportServer IIS virtual directory. The relevant xml from the web.config is attached below.
I would appreciate any thoughts. I am running on W2K3 SP1. Thanks.
Does someone of you know a rendering extension for ppt. Any integrated or third party componens ? The management wants to have the reports as powerpoint presentation
I recently installed the Teradata .NET data provider and I am trying to use named parameters and multi-value parameters in my SQL. I am finding information on this topic hard to come by.
In order to get parameters to work do I need to write my own Teradata data extension from scratch, expand an existing data extension to work with Teradata, or can I simple edit my .config files to point to a generic extension wrapper already existing?
My query should be pretty simple to answer by the gurus. I have SQL Server Express Advance series installed at my system along with VS 2005. I tried the reporting services (remote mode) to display a report in the report viewer on my aspx page. The report simply uses an SQL query to get the result. Everything works fine and dandy!
However if i want to get data from my own data set, i used a webservice and ussed the XML data source option in the report to traverse through the XML. In the preview mode of the RDL everything works fine however when i deploy or rather run it using report viewer on my webpage, it gives an error that 'XML' has not been registered as an extension etc.
I looked in the rsserver config file, it IS configured. The only answer i have is that the EXPRESS version of SQL only supports the regular SQL data sources. I do not understand if that was the case, why does the preview mode work fine??
If i am correct then for the next release the ms developers should disable that or rather give a more meaning full error. I spent an entire day fideling around just to find out that it isnt supported with debates all over the place as to why in the world does it work in the preview mode?
Anyhoo, to get around that issue, i am wondering if the Custom Data Extension would work on the SQL Express or not? I am assuming it wouldnt. Please clearify this. Ideally all i want is to access my own data set in the report, no SQL queries or whatever, CDE seems the way forward but does SQL Express allow it?
Many thanks guyz.
Regards
Zeeshan (btw, its only been two days since i started working with reporting services, so be gentle :-D )
I'm using Windows XP Home, Apache 2.2 as web server, and PHP 5.2.5.
I have php installed in c:php. I have the extensions directory set to c:phpext.
Enabling a different extension, like php_zip works.
If I leave the entry in php.ini for php_sqlsrv.dll, then I get a message that is can't be loaded.
If I try to run php at the command line using php -v, then it pops up a dialg box: Title: php.exe - unable tolocate component Message: The application has failed to start because php5.dll was not found. Then another pop up dialog saying that php_sqlsrv.dll could not be loaded.
If I turn on PHP error messages to be displayed in the browser, and stop and restart Apache then I see: PHP startup: unable to load dynamic link library ./php_sqlsrv.dll - the specified module could not be loaded.
Here's the line in my Apache config file: LoadModule php5_module c:/php/php5apache2_2.dll
There is no plain php5.dll in the c:php directory.
Perhaps this extension ONLY works with IIS as the web server?
I set up a basic ForEach enumerator loop and specified files of type *.sql.
In the directory, I had some files I needed to keep but didn't want the package to touch, so I changed the extension to *.sqlo. Much to my dismay, the ForEach loop picked up those files. (Though it did skip the *.xml and *.bat and a few other types...)
The custom security extension generates the following error, when try to access Report Manager. The specifications are as follows. 1. Its 64-bit SQL Server (Reporting Services) and installed as default instance. 2. The Report Server is accessible programmatically. I can publish, view reports without problems, but Report Manager gives an error. 3. Another instance (named instance) of Reporting Services with the same custom security extension on same server is working fine. Report Manager opens up without problems. 4. Both the instances working fine since 3 installation and configuration (3 months back). Now, only the Report Manager of the default instance is not openeing up.
ReportServerWebApp__08_22_2006_08_52_20.log ------------------------------------------------------------------------- w3wp!ui!1!8/22/2006-08:52:56:: i INFO: Overwriting existing cookie w3wp!ui!1!8/22/2006-08:52:57:: e ERROR: The report server is not responding. Verify that the report server is running and can be accessed from this computer. w3wp!ui!1!8/22/2006-08:52:57:: e ERROR: HTTP status code --> 500 -------Details-------- Microsoft.ReportingServices.UI.Global+RSWebServiceWrapper+CantCommunicateWithReportServerException: The report server is not responding. Verify that the report server is running and can be accessed from this computer.
at Microsoft.ReportingServices.UI.Global.RSWebServiceWrapper.GetSecureMethods()
at Microsoft.SqlServer.ReportingServices2005.RSConnection.IsSecureMethod(String methodname)
at Microsoft.SqlServer.ReportingServices2005.RSConnection.ValidateConnection()
at Microsoft.ReportingServices.UI.Global.SecureAllAPI()
at Microsoft.ReportingServices.UI.ReportingPage.EnsureHttpsLevel(HttpsLevel level)
at Microsoft.ReportingServices.UI.ReportingPage.ReportingPage_Init(Object sender, EventArgs args)
at System.EventHandler.Invoke(Object sender, EventArgs e)
at System.Web.UI.Control.OnInit(EventArgs e)
at System.Web.UI.Page.OnInit(EventArgs e)
at System.Web.UI.Control.InitRecursive(Control namingContainer)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) w3wp!ui!1!8/22/2006-08:52:57:: i INFO: Overwriting existing cookie w3wp!ui!1!8/22/2006-08:52:58:: e ERROR: Exception in ShowErrorPage: System.Threading.ThreadAbortException: Thread was being aborted. at System.Threading.Thread.AbortInternal() at System.Threading.Thread.Abort(Object stateInfo) at System.Web.HttpResponse.End() at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg) at at System.Threading.Thread.AbortInternal() at System.Threading.Thread.Abort(Object stateInfo) at System.Web.HttpResponse.End() at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg)
The service is running fine. Any suggestions/pointers would be greatly appreciated.
I have followed all the steps indicated in the Security Extensions Sample for RS 2005, and I have not changed any webpage code. However, I get the following errors: - when accessing http://<computername>/ReportServer Access to the path 'C:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesReportServerlogon.aspx' is denied - when accessing http://<computername>/Reports Error message 401.3: You do not have permission to view this directory or page using the credentials you supplied
I have written a Data Processing extension for my application and can deploy it on my development machine no problems. My question is: what is the "correct" way of deploying an extension to an end user's machine? Do I have to write a special program to find and modify the Reporting Server config files and copy the extension over. Surely many developers have the same need so there must be a generic solution to this problem, however, I haven't managed to find one.
I first came across this issue in SQL2000 and I thought/hoped it would be rectified with SQL2005 but it appears not to be (unless I'm missing something).
I'm trying to start the generic query designer and i get this error. I have no idea what is wrong, but I know I'm using Visual Studio 2005 V 8.050727.42 .net Framework 2.050727 SSRS Designers v 9:00.3042.00
On my current project we have a requirement to re-authenticate the user when accessing sensitive information.
We want re-authenticate using standard NT logins against Active Directory (not a custom database or SSO.
This is trivial to configure using Basic Authentication, however I would like to use an ASP.NET login form. I would like to avoid writing a security extension as I do not want to perform custom authentication. I would like the web service to use it's built in authentication and authorisation mechanisms.
Is the above possible???
The following gives some more details about wht I've tried.
I have tried configuring the report server/manager with Forms authentication as in the sample but cannot get it to work with out implementing the security extensions.
I changed the web.config files and the policy files for permissioning my dll with FullTrust. I did not configure an extension as I want reporting services to use it's built in windows security mechanisms.
In the login page code behind I call the ReportingService2005.LogonUser() method which always throws the following exception: Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'. The request failed with the error message: --
Reporting Services Error
The report server has encountered a configuration error. See the report server log files for more information. (rsServerConfigurationError) Get Online Help
SQL Server Reporting Services --.
I check the log file and it has the following:
at System.Web.UI.Page.HandleError(Exception e) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) at System.Web.UI.Page.ProcessRequest() at System.Web.UI.Page.ProcessRequestWithNoAssert(HttpContext context) at System.Web.UI.Page.ProcessRequest(HttpContext context) at ASP.logon_aspx.ProcessRequest(HttpContext context) at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) aspnet_wp!library!18!03/13/2007-11:38:23:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details., ; Info: Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details. ---> System.Web.HttpUnhandledException: Exception of type 'System.Web.HttpUnhandledException' was thrown. ---> System.FormatException: Input string was not in a correct format. at System.Text.StringBuilder.FormatError() at System.Text.StringBuilder.AppendFormat(IFormatProvider provider, String format, Object[] args) at System.String.Format(IFormatProvider provider, String format, Object[] args) at Microsoft.Samples.ReportingServices.CustomSecurity.Logon.ServerBtnLogon_Click(Object sender, EventArgs e) in C:Program FilesMicrosoft SQL Server90SamplesReporting ServicesExtension SamplesFormsAuthentication SamplecsFormsAuthenticationLogon.aspx.cs:line 130 at System.Web.UI.WebControls.Button.OnClick(EventArgs e) at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) --- End of inner exception stack trace --- at System.Web.UI.Page.HandleError(Exception e) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) at System.Web.UI.Page.ProcessRequest() at System.Web.UI.Page.ProcessRequestWithNoAssert(HttpContext context) at System.Web.UI.Page.ProcessRequest(HttpContext context) at ASP.logon_aspx.ProcessRequest(HttpContext context) at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) --- End of inner exception stack trace --- aspnet_wp!library!18!03/13/2007-11:38:24:: i INFO: Exception dumped to: c:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesLogFiles flags= ReferencedMemory, AllThreads, SendToWatson aspnet_wp!library!1!03/13/2007-11:39:11:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. See the report server log files for more information., Could not load Authentication extension; Info: Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. See the report server log files for more information.
Am I missing something? Is this even possible? If not, then why isn't it possible?
It feels like I've been going round in circles on something that shouold be pretty trivial to configure.
I'm writing a Data Processing Extension for SSRS 2005 that allows you to use a WSS 3.0 List as a dataset. This works for one list per dataset. Now I am trying to extend the extension to allow multiple lists in a dataset because I want to join lists on a common field (INNER JOIN). The problem is that I can only return one DataTable to the Report Server with the DataReader. Does anyone know if it is possible to return multiple DataTables (without the join)?
I'm a student, working on this project. I know Visual Basic.net but only started last month with Data.
Hi, I'm using WinXP SP 2, IIS 5.1, SQL Server 2005 Standard, and Visual Studio 2005 Professional. When I create a virtual directory with IIS Virtual Directory Management for SQLXML 3.0, I get a Microsoft SQL isapi extension error when going to the default page. If I delete the virtual directory, and then add it directly in the IIS snap-in, with executable permissions, all goes well. My website is from the Wrox book, Beginning ASP.NET 2.0. An appendix in the book says to add the virtual directory with the SQLXML 3.0 tool, and not to use IIS. I'm very new to this, so I'm not sure if I'll lose functionality down the road or not, by using IIS. Has anyone run into this before or have any ideas how to get sqlxml 3.0 virt. dir's to work?
My goal is to have a file (it comes from another system) that I want to import via an OPENROWSET style query.
The query would look like this:
select [NoName] from openrowset('MSDASQL' ,'Driver={Microsoft Access Text Driver (*.txt, *.csv)}; DefaultDir=c:filedir' ,'select * from "file.lst"')
If I make the file a .csv it works fine. However, if it has a not CSV or TXT extension it throws the following error and cannot seem to find a solution to it.
OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Text Driver] Cannot update. Database or object is read-only.". Msg 7350, Level 16, State 2, Line 1 Cannot get the column information from OLE DB provider "MSDASQL" for linked server "(null)".
In addition, (although I can probably find this elsewhere), I need to have the first line 'BLANK' so that it does not miss data (there is no header row). Is there a way to use OPENROWSET without BULK to basically include all rows as data?
In SQL 2k5 every db object say table , table fields etc have extension properties . I plan to populate these extension properties with meta data related information like field description , string length , case conversion , field alignment , enum options etc .
I wish to know if anybody has done, anything like this . Can this properties be easily avaialble in .Net development , reporting services , BIDS etc . Can there be significant advantages wrt this .
I have installed the printer delivery sample and made the changes to the config files as described in the documentation but can only see it available as an option in the data driven subscriptions section. Anyone know how I can get it to show up in the standard subscriptions option?
