I'm using VS 2005, and I built a website with a TableAdapter method using the wizard.
I hand wrote a line of code: 1 WITH srtAS
2 (
3 SELECT *,
4 ROW_NUMBER() OVER (ORDER BY date DESC) AS 'RowNumber'
5 FROM table1
6 )
7 SELECT *
8 FROM srt
9 WHERE RowNumber BETWEEN @start AND @end; I didn't find any syntax errors, but the Wizard keeps alerting "the over sql construct or statement is not supported." From my research, I found that it's caused by the Wizard that doesnt support some syntax. Afterall, the problem is:1. I have two parameters @start AND @end. normally if there is no "syntax error", then it will show in the TableAdapter: GetData(@Start,@end). But now since there is this "Syntax error", I won't load these two parameters.2. I saw some solutions to this "syntax error" problem, and they suggested to write query manually, don't use wizard. But I don't know how. thanks in advance-noPC-
does anyone know, when will the functionality of linked reports, my reports, data-driven subscriptions in MOSS integrated mode be enabled? Now it is according to Microsoft not supported. Will this be supported in the future ?Thanks.
I got the following exception from the code listed below:
System.Web.Services.Protocols.SoapException occurred Message="The operation is not supported on a report server that is configured to run in SharePoint integrated mode. ---> The operation is not supported on a report server that is configured to run in SharePoint integrated mode." Source="System.Web.Services" Actor="http://haileizws32b:8080/ReportServer/ReportService2005.asmx" Lang="" Node="http://haileizws32b:8080/ReportServer/ReportService2005.asmx" Role="" StackTrace: at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall) at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters) at Microsoft.PerformancePoint.Planning.Client.Common.ReportServer.ReportingServices2005.ListChildren(String Item, Boolean Recursive) in c:Officedev14ppsplanningclientCommonReportingServices2005.cs:line 1001 at Microsoft.PerformancePoint.Planning.Client.Common.DataAgent.RetrieveDirectoryStructureFromReportingServices(Object caller, Object parameters) in c:Officedev14ppsplanningclientCommonAgentsDataAgent.cs:line 2027 InnerException:
The code is like:
ReportingServices2005 reportingServices = new ReportingServices2005();
I've small issue with SQL 2005 SP2. We have migrated database from another server with backup/restore procedure. Seems that db is properly set with cmp level 7.0. We are using standard maintenance plan for all SQL 2005 servers with option "backup all user databases", to have less work. Now it seems that dbs in SQL COMPATIBILITY LEVEL 7.0 cannot be backuped with Maintenance plan. They are not listed in the list of the databases and Maintenance plan is always reporting failure :
"Database '<DBNAME>' is not valid to be included in the maintenance plan."
Is there any way to fix this? Or can I safely switch db to CMP Level 8.0 at least?
There is a query which when executed in the grid mode(ctrl+d) takes approx 0.02 seconds(about 21,000 rows) But when I execute in the text mode, it takes about 0.40 seconds!! Why is this difference? Also, when the records from this table are read from a VB application, they are equally slow (as in the text mode!) Why is it so slow on the text mode & relatively faster in the grid mode? Has anyone got any idea on ‘Firehose’ style cursor ?(which may speed up access of data in the VB application)
All -- Please help. Why is it that Linq-To-SQL reports that the query operator 'Last' is not supported? The target database is SQL Server 2005. The data provider is SqlClient.
This is the error... System.NotSupportedException: The query operator 'Last' is not supported. at System.Data.Linq.SqlClient.QueryConverter.VisitSequenceOperatorCall(MethodCallExpression mc) at System.Data.Linq.SqlClient.QueryConverter.VisitMethodCall(MethodCallExpression mc) at System.Data.Linq.SqlClient.QueryConverter.VisitInner(Expression node) at System.Data.Linq.SqlClient.QueryConverter.ConvertOuter(Expression node) at System.Data.Linq.SqlClient.SqlProvider.BuildQuery(Expression query, SqlNodeAnnotations annotations) at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query) at System.Data.Linq.DataQuery`1.System.Linq.IQueryProvider.Execute[S](Expression expression) at System.Linq.Queryable.Last[TSource](IQueryable`1 source) This is the code.
public static Guid GetPkIdOfLast() { Guid myPkIdOfLast = Guid.Empty;
DataClassesDataContext myContext = new DataClassesDataContext();
myPkIdOfLast = (from p in myContext.CountryCodes orderby p.Name ascending select p.PkId).Last();
return myPkIdOfLast; }
Do you have any ideas? Please advise. Thank you. -- Mark Kamoski
I have a couple of reports with predefined queries. When I try to edit a data set query it goes directly to query design mode losing the query I already had. As you can see this is a real problem because I can't edit queries. How can I switch off design mode? any ideas are pretty much appreciated.
Durning install I selected Window's Authentication only, but now it seems we may need to use a Mixed Mode with an SA account etc... is there anyway to switch SQL 2005 to use Mixed Mode after the fact?
We have reports in SharePoint integrated mode which are really slow when compared to native mode. I have been asked to research and give info on what exactly causes the delays.
Any articles which give me information as to what happens when a report is run from SharePoint server and where does it log.
We recently upgraded out SQL version from SQL2008R2 to SQL2014. As such, the compatibility mode changed to SQL2104 (120).
We have several queries that used to run fine that now take forever to bring back results. There are no errors (which surprised me). They just take way too long now. PLus they seem to be causing high I/O and CPU.
If I change the compt level back to SQL2008 - these queries run fine.
QUERY with SQL2008 compt level - finished in 2 minutes. QUERY with SQL2014 compt level - finishes in 3 hours 22 minutes.
same exact query - same server - only thing changed was compatibility level.
WHat do I look for in the queries that could be causing this? (they look fine but obviously I'm missing something here)..
I have a stored proc that runs through my databases doing transaction logs. Currently it uses database names but this keeps being changed as a database will be added without me knowing. Next thing the transaction log job is goosed.
I really need something along the lines of select db from sys???? where mode = 'Full'
I receive the following error message when I run a distributed query against a loopback linked server in SQL Server 2005: The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction.
To resolve this problem, I was told that running a distributed query against a loopback linked server is not supported in SQL Server 2005. And I am suggested to use a remote server definition (sp_addserver) instead of a linked server definition to resolve this problem. (Although this is only a temporary resolution, which will deprecate in Katmai)
However, I run into another problem when I use the remote server definition. I receive the following error message: Msg 18483, Level 14, State 1, Line 1 Could not connect to server 'ServerNameSQL2005' because '' is not defined as a remote login at the server. Verify that you have specified the correct login name.
Could anyone please help me out? (I include the reproduce steps for the first error message, followed by my resolution that generates the second error message) ====== Reproduce steps for the first error message ======
On the ComputerAInstanceA instance, run the following statement to create a database and a table: CREATE DATABASE DatabaseA GO USE DatabaseA GO CREATE TABLE TestTable(Col1 int, Col2 varchar(50)) GO INSERT INTO TestTable VALUES (1, 'Hello World') GO
On the ComputerBInstanceB instance, run the following statement to create a database and a table: CREATE DATABASE DatabaseB GO USE DatabaseB GO CREATE TABLE TestTable (Col1 int, Col2 varchar(50)) GO
On the ComputerAInstanceA instance, create a linked server that links to the ComputerBInstanceB instance. Assume the name of the linked server is LNK_ServerB.
On the ComputerBInstanceB instance, create a linked server that links to the ComputerAInstanceA instance. Assume the name of the linked server is LNK_ServerA.
On the ComputerBInstanceB instance, run the following statement: USE DatabaseB GO CREATE PROCEDURE InsertA AS BEGIN SELECT * from LNK_ServerA.DatabaseA.dbo.TestTable END GO
On the ComputerAInstanceA instance, run the following statement: USE DatabaseA GO INSERT INTO TestTable EXEC LNK_ServerB.DatabaseB.dbo.InsertA GO Then I receive the first error message.
======= My resolution that generates the second error message =======
On the ComputerBInstanceB instance, run the following statement: sp_addserver 'ComputerAInstanceA' GO sp_serveroption 'ComputerAInstanceA', 'Data Access', 'TRUE' GO USE DatabaseB GO CREATE PROCEDURE InsertA AS BEGIN SELECT * FROM [ComputerAInstanceA].DatabaseA.dbo.TestTable END GO
On the ComputerAInstanceA instance, run the following statement: USE DatabaseA GO INSERT INTO TestTable EXECUTE [ComputerBInstanceB].[DatabaseB].[dbo].[InsertA] GO Then I receive the second error message.
Recently I read such statments 'When SQL Server is run in "lightweight pooling" mode (fiber mode) and the DTC service is started, unexpected behavior may occur.' Can someone say anything about fibe mode?I am appreciated for it.:)
Currently, our Report Builder is running on Local Intranet mode. I'm investigating what the security implications are in changing it to Internet mode. However, I've not been able to find any documentation on this.
Does anyone know of any documentation that addresses this issue or have experience that they can share with changing Report Builder security zone from Intranet mode to Internet mode?
hi there, I have never use DTS before, now I am reading textbook for some special demand with DTS the textbook not talk very much for the detail of skills. seems the easy way to finish this query is using DTS wizard. but my requirement seems can't be done by DTS wizard. here are my requirement below. [move online Database to offline Database ] 1. the time of data preserve will have to reference separate firm's history data backup time ( for example, A company used to preserve data 6 months, and B company used to preserve data 12 months and so on..) 2. we will have only 2 kind of preserve time one is 6 months another is 12 months 3. The online DB only keeps 6 months data ( for example, when we do the DTS on 11/1 , we will only keep the data which from 5/1~10/31) , all data have to move to off-line DB except the past 6 months data 4. We will have to reference the history data preserve time to delete data after finished data movement those requirement looks very diffcult for me because I have never use DTS before , can you please give me a simple example or maybe some article I can reference?
I have an error message I was wondering if anyone else has seen and resolved. When I open excel and click on the query wizard on the Data Mining Ribbon, I get an error message that says "Input String not in Correct Format"
After I close this dialog box the 'Welcome to Query .... ' wizard will open, but when I select the Advance button my process aborts due to the input string error.
I have only ran association models on the computer.
The Manage Models and Browse buttons work fine.
The error came up on Friday, but it wasn't there on Thursday. There were no changes done to the machine (no installation of new software).
I have tried reinstalling the add-in.
Thank you all for your help,
Davy
I have pasted the SQL syntax below that I get from the error dialog box after the query wizard closes.
See the end of this message for details on invoking just-in-time (JIT) debugging instead of this dialog box.
************** Exception Text ************** System.NullReferenceException: Object reference not set to an instance of an object. at Microsoft.SqlServer.DataMining.Office.Excel.QueryBuilder.QueryBuilderParameters.UpdateNestedColumnName() at Microsoft.SqlServer.DataMining.Office.Excel.XLClientUIManager.DisplayAdvancedQueryBuilder(Object sender, WizardPageEventArgs e) at Microsoft.SqlServer.DataMining.Office.Excel.Wizard.WizardPageBase.OnWizardPageAdvanced(WizardPageEventArgs e) at Microsoft.SqlServer.DataMining.Office.Excel.Wizard.WizardForm.btnAdvanced_Click(Object sender, EventArgs e) at System.Windows.Forms.Control.OnClick(EventArgs e) at System.Windows.Forms.Button.OnClick(EventArgs e) at System.Windows.Forms.Button.WndProc(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m) at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
************** JIT Debugging ************** To enable just-in-time (JIT) debugging, the .config file for this application or computer (machine.config) must have the jitDebugging value set in the system.windows.forms section. The application must also be compiled with debugging enabled.
When JIT debugging is enabled, any unhandled exception will be sent to the JIT debugger registered on the computer rather than be handled by this dialog box.
I have a SQL 2005 OTC. CTP version running on Windows 2003 server.
I would like to find out how the SQL server option changed to Windows Authentication mode from Mixed mode over the weekend. From the SQL log, I don't see when it changed. I would like to see Date/Time and client IP. If I can see User ID (windows) that would be great. Where I can find these info in SQL server?
I am trying to send a variable selected by the user and have all of the results that match that selection show in a datagridview. I tried using this query but it gives me not result. //code SELECT wasteDate, wasteFeed, wasteLbs FROM tbWaste WHERE (wasteDate = 'date1') //code date1 is being set by a datetimepicker I am getting no results when I run the program I have tried out every different combination of ways of coding it i can think of if anyone can help it would be greatly appriciated.
I have a very frustrating problem. But given that this is a development forum, I'll keep that for my 'Envelope Lickers Rehab' and instead talk about an issue I'm having.
I have a very large query (line-wise) that executes in less than a minute when run from the SQL Management Studio as well as via Excel Services.Â
It is a stored procedure with one parameter. When I try to prime the designer with this query it gives me the following message:
"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated."
I've considered cracking open the xml source and manually creating the fields, but I'd rather not go there.
I have two inline selects against a table with a nonclustered columnstore on SQL 2014 (12.0.2000). Both execute in batch mode and when I inner-join the two, they continue to execute in batch mode. When I cross join them, one executes in row mode. Below is some SQL to simulate the issue.
-- The purpose of this script is to demonstrate that -- two queries against a columnstore index that each execute in batch mode -- will continue to execute in batch mode when inner joined. -- However, one of the queries will execute in row mode when cross-joined.
-- Create function to return 0 to n rows IF OBJECT_ID('dbo.IntCount') IS NOT NULL DROP FUNCTION dbo.IntCount;
I have become frustrated and I am not finding the answers I expect.
Here's the gist, we support both Oracle and SQL for our product and we would like to migrate our Clients who are willing/requesting to go from Oracle to SQL. Seems easy enough.
So, I create a Database in SQL 2005, right click and select "Import Data", Source is Microsoft OLE DB Provider for Oracle and I setup my connection. so far so good.
I create my Destination for SQL Native Client to the Database that I plan on importing into. Still good
Next, I select "Copy data from one or more tables or views". I move on to the next screen and select all of the Objects from a Schema. These are Tables that only relate to our application or in other words, nothing Oracle System wise.
When I get to the end it progresses to about 20% and then throws this error about 300 or so times:
Could not connect source component. Warning 0x80202066: Source - AM_ALERTS [1]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.
So, I'm thinking "Alright, we can search on this error and I'm sure there's an easy fix." I do some checking and indeed find out that there is a property setting called "AlwaysUseDefaultCodePage" in the OLEDB Data Source Properties. Great! I go back and look at the connection in the Import and .... there's nothing with that property!
Back to the drawing board. I Create a new SSIS package and figure out quickly that the AlwaysUseDefaultCodePage is in there. I can transfter information from the Oracle Source Table to the SQL Server 2005 Destination Table, but it appears to be a one to one thing. Programming this, if I get it to work at all, will take me about 150 hours or so.
This make perfect sense if all you are doing is copying a few columns or maybe one or two objects, but I am talking about 600 + objects with upwards of 2 million rows of data in each!!
This generates 2 questions: 1. If the Import Data Wizard cannot handle this operation on the fly, then why can't the AlwaysUseDefaultCodePage property be shown as part of the connection 2. How do I create and SSIS Package that will copy all of the data from Oracle to SQL Server? The source tables have been created and have the same Schema and Object Names as the Source. I don't want to create a Data Flow Task 600 times.
Hi I have three questions about several topics. In this code: public string ConnectionString { get { return "Provider=MSOLAP.3;Data Source=localhost;Initial Catalog=Adventure Works DW"; } }
What is data source and initial catalog and what does this code do? And if I want to use other database how can change this code? (This code is for data mining viewer client project) And in this code: SqlConnection cn = new SqlConnection("Data Source=localhost;Initial Catalog=AdventureWorks;Integrated Security=True"); SqlCommand cm = new SqlCommand("Select AddressID,AddressLine1 from Person.Address", cn); SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cm; DataTable dt = new DataTable(); da.Fill(dt); this.comboBox1.DisplayMember = "AddressLine1"; this.comboBox1.ValueMember = "AddressID"; this.comboBox1.DataSource = dt;
what is comboBox1.DisplayMember and comboBox1.ValueMember ,and what is difference between those ? and other question: in adventure works dw project for data mining predicting ,in forecasting model ,if I want to show the result of this query in the combobox in c# how can I show that? SELECT PredictTimeSeries(amount) From [Forecasting] And again in this code ,it has a result which has two culomns ,on of them is for amount and other column is for time ,in sql I can save this result in exsiting table or neew table with wizard,but I want to Do this work in c#,that€™s mean with a adomdconnection I connect to forecasting model and write this query then in a datagridviwe ,Iwant to see the values of prediction in adventure works dw database. Other question: In €œdataminingviwerclient€? project I change this code and you can see it,for this code I have a form that give servername and catlogname in that and then with clcking on a button I want to show the chart of model in a child form ,but I can€™t.
public Form1 form1 = new Form1(); public string m_ServerName; public string m_CatalogName; public Form3() { m_ServerName = ""; m_CatalogName = ""; InitializeComponent(); }
public string ConnectionString { get { return "Provider=MSOLAP.3;Data Source=localhost;Initial Catalog=Adventure Works DW"; } }
private void ShowModel(Panel panel, string modelName) { AdomdConnection conn = new AdomdConnection(); try { MiningModelViewerControl viewer = null; MiningModel model = null; MiningService service = null;
// Clear any existing controls from the panel if (panel.HasChildren) panel.Controls.Clear();
// Connect to server conn.ConnectionString = ConnectionString; conn.Open();
// Determine the viewer type based on the model service and // instantiate the correct viewer model = conn.MiningModels[modelName]; service = conn.MiningServices[model.Algorithm]; if (service.ViewerType == "Microsoft_TimeSeries_Viewer") viewer = new TimeSeriesViewer(); else throw new System.Exception("Custom Viewers not supported");
// Set up and load the viewer viewer.ConnectionString = ConnectionString; viewer.MiningModelName = modelName; viewer.Dock = DockStyle.Fill; panel.Controls.Add(viewer); viewer.LoadViewerData(null); } catch (System.Exception ex) { MessageBox.Show(ex.Message, "Model Load"); } conn.Close(); when I run this code ,I have one error that say: the €œ object not found parametr name:index Please see this code and answer my question. If you just can answer one of my qestions ,please say. Thanks a lot for your answers.With best wishes for you
I am trying to use the Import Wizard to setup a daily job to import new records based on an ID field (PK). The source database is remote and a replica. I am inserting new records to update my table called the same thing. Both are SQL Native Client
Code Snippet select * from [CommWireless].[dbo].[iQclerk_SaleInvoicesAndProducts] as S1 join [IQ_REPLICA].[dbo].[iQclerk_SaleInvoicesAndProducts] as S2 on S1.SaleInvoiceID = S2.SaleInvoiceID where S1.SaleInvoiceID > S2.SaleInvoiceID
When I parse the query, I keep getting an error message.
Deferred prepare could not be completed. Statement(s) could not be prepared. Invalid object name 'IQ_REPLICA.dbo.iQ_SaleInvoicesAndProducts'. (Microsoft SQL Native Client)
Anyone know an easy why to get this to work? Or should I add a create table to verify new records?
Hello, I receive this error "The SQL Server Service Broker for the current database is not enabled, and as a result query notifications are not supported. Please enable the Service Broker for this database if you wish to use notifications." I attach the database in Management Studio to query and enable the broker using the scrip below but to no avail. ALTER DATABASE DataName SET ENABLE_BROKER ‘''<<------successfulandSELECT is_broker_enabled FROM sys.databases WHERE name = 'Database name' ‘'''<<-------value is 1 Global.asax ... Sub Application_Start(ByVal sender As Object, ByVal e As EventArgs) System.Data.SqlClient.SqlDependency.Start(ConfigurationManager.ConnectionStrings("dataConnectionString1").ConnectionString) End Sub...Web.config ... <connectionStrings> <add name="dataConnectionString1" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|jbp_data.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" /> <add name="ASPNETDBConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" /> </connectionStrings>... Hope you could help. cheers,imperialx
Hi, I have created the below procedure in SQL SERVER 2005. But when I copy out the same in SQL SERVER 2000. I get an error at the underlined place. It says Line 32: Incorrect syntax near '('. ALTER procedure [dbo].[VTELcardvalidation1] @CardValue1 int, @CardValue2 int, @CardValue3 int,@Result nvarchar(50)output as begin declare @CrdPinNo varchar(100) declare @CrdNo nvarchar(100)declare @count int declare @inc int declare @concat nvarchar(200)create table #temptable(crno nvarchar(50),pno nvarchar(50))
--First Card --Change here select @count=count(cardno) from vendorcardvalidation where cardvalue=1500 and flag=0if (@count)>@CardValue1 begin declare cur1 cursor forselect top(@CardValue1) pin,cardno from vendorcardvalidation where cardvalue=1500 and flag=0 order by cardno open cur1fetch next from cur1 into @CrdPinNo,@CrdNo while(@@fetch_status=0) beginupdate vendorcardvalidation set flag=1 where cardno=@CrdNo insert into #temptable values(1500,@CrdPinNo)fetch next from cur1 into @CrdPinNo,@CrdNo endclose cur1 deallocate cur1 set @Result='Success'end else begin set @Result='Failure' end select * from #temptable drop table #temptable end
It seems the the word 'top' will not be supported in sqlserver 2000. What should I do? Regards cmrhema
I have the following VB function. The stored proc that is being called ("GetList") inside the function does a simple SQL Select statement against an SQL Server 7 database using OLE DB. I'd like to store the result of the SQL Select statement in the "Rs" parameter to pass back to the calling function. Whenever I call this function, I get an error message stating "No Such Interface Supported". Does anyone know why I cannot do this?
Also, this function is being called from ASP code. That's why the "Rs" is passed as a variant. It's created in the ASP code before this function is called.
------------------------------------------
Function GetAddressDropDownList(Rs As Variant, CustomerId As Long)
Dim ProcComm Dim Parameter
Set ProcComm = CreateObject("ADODB.Command") ProcComm.CommandType = adCmdStoredProc ProcComm.CommandText = "GetList"
Set Parameter = ProcComm.CreateParameter("CustomerId", adInteger, adParamInput) Parameter.Value = CustomerId ProcComm.Parameters.Append Parameter Set Parameter = Nothing
How many "applications" can be loaded onto Microsoft SQL Server 6.5?
Additional Information:
The OS platform is Windows NT Server 4.0 (SP5) on a Compaq ProLiant 1600 (PII 450) with 256Mb RAM Proposed applications, although this probably will mean little, are Dataview (club membership) Peterborough (payroll) and Positive (HR).
