Well, i need a piece of code that return me the schema rowset of a desicion tree and let me iterate me on it. I need some columns like node_type, node_unique_name.... etc.. And another question is that the node_distribution has multiple rows of information. How can i access them. I think there must be a adomddatareader object to iterate on them..
Well thanks a lot for your responses...
I see others having this problem when trying to run a query across a link from 2005 to a 2000 server. But we are already running SP4 on our 2000 server. What gives?
OLE DB provider "SQLNCLI" for linked server "s-1" returned message "Unspecified error".
OLE DB provider "SQLNCLI" for linked server "s-1" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Line 5
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "s-1". The provider supports the interface, but returns a failure code when it is used.
Hi, all experts here,
Thank you very much for your kind attention.
I am having a question about the node_distribution.PRABABILITY. Some of the attribute values though have a small number of support for the specific node, but why it has a big node_distribution.probability even greater than 1? How can the node_distribution.PROBABILITY be greater than 1? How dose SQL Server 2005 data mining engine calculate the node_distribution.PRPBABILITY for its Clustering algorithm? Really confused and need guidance for that.
Thank you very much for your help.
With best regards,
Yours sincerely,
Hi, all here,
Thank you very much for your kind attention.
Just noticed that the sum of node_distribution.support value is not equal to the value of node_support for a specific node in my Microsoft Clustering Algorithm Model? If so, how can I correctly count the distribution of each attribute value among all?
Really confused.
And thank you very much in advance for any guidance and advices for that. Looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
Hi, all here,
Thank you very much for your kind attention.
I dont understand another problem within my mining model. When I query the mining model content ,finding that the same attribute_value have different support and probability for the same node within my clustering model. Why is that? Really confused. And really need help for that.
Thank you very much in advance for your help.
With best regards,
Yours sincerely,
hi all,
I'm trying to get some xml data into sql server but i ran into this problem: openxml seems to repeat results ...
I simplified my example to this:
------------------------------------------------------
Declare @rDoc int,
@sDoc varchar(4000)
Set @sDoc = '
<ROOT>
<Rider>aaa</Rider>
<Rider>bbb</Rider>
<Rider>ccc</Rider>
</ROOT>'
EXEC sp_xml_preparedocument @rDoc OUTPUT, @sDoc
SELECT *
FROM OPENXML (@rDoc, 'ROOT/Rider', 1)
WITH (RiderName varchar(50) '../Rider')
----------------------------------------------------------
it returns 3 records for the 'rider' elements, _but_ all the records contain the text 'aaa' :/
does anybody know the solution to this?
cheers,
alex
Hi..I am trying to read text file using OpenRowSet function for this my DBA was created linked Server with Name "DBLServerText", my text file path c:/txtcount.txt. Please guide me syntax any one have idea.
I don't have access on XP_CMDSHELL & BULK Insert commands.
DTS package is also not allowing.
as per my knowledge, I have only one option that is OpenRowset.
Please help me the syntax using with Linked Server. with Linked it is showing the error. " You don't have OLEDB access use with Linked server"
Pls guide me
Thanks in Advance..
Mure
I have sp20, simplified, as:
ALTER PROCEDURE dbo.sp20 (@CustomerID int, @aDate as datetime) AS
SELECT Customers.* INTO #EndResult1 FROM Customers WHERE Customers.CustomerID >= @CustomerID
SELECT Orders.* INTO #EndResult2 FROM Orders Where Orders.[TakenDate] >= @aDate
SELECT #EndResult1.*, #EndResult2.*
FROM #EndResult1 INNER JOIN #EndResult2 ON #EndResult1.CustomerID = #EndResult2.CustomerID
This works fine in EM.
When I try to execute it from MS Access ADP Project I get
'Stored Procedure excuted succesfully, but did not return any records'
Although, in EM it returns the right number of records.
Thank you in advance - Rehman
I am not close to an sql server today and this question was posed to me. can someone hook me up?
" I want to query a column of values and place them into a single string seperated by commas" (as a function)
Table a
123
456
789
321
654
987
output
123,456,789,321,654,987
thanks in advance
Please let me know if I am on the right track here.
I have an Execute SQL Task that selects multiple rows from an OLE DB connection, each row containing 3 columns (data types = string, Int32, Int32). In this task ResultSet = "Full result set" and Result Set > Result Name = 0, Variable Name = [User::viewInfo] which is a user variable with Data Type = Object.
I want to use a Foreach Loop Container to enumerate over the result set rows that are contained in the [User::viewInfo] variable described above. For each resultset row I want to breakout the 3 column values and assign them to 3 corresponding variables that can be referenced in a Data Flow in the Foreach Loop.
Current settings for the Foreach Loop Container: Collection > Enumerator = "Foreach ADO Enumerator", Collection > Enumerator Configuration > ADO object source variable = [User::viewInfo], Enumeration mode = "Rows in the first table". On the Variable Mappings page I select the 3 corresponding user variables I want the rowset column values assigned to, with indexes starting at 1 (not 0).
Thanks - Dana Reed
I'd like the "OUTPUT DELETED.* INTO target table" resulting from a DELETE to occur in the same order as the clustering key of the target table. Is this possible?
View 6 Replies View RelatedI am using a proc that runs fine in SSMS Query editor and also produces rows when I click the Preview button in the OLEDB Source Adapter. It has two input parameters. The proc contains some CTE's and UNION ALL's. It does not contain any dynamic sql. However, when I run the package I get this error:
[OLE DB Source [1]] Error: A rowset based on the SQL command was not returned by the OLE DB provider.
How can that be when the Preview shows rows being returned?
Hi,
I get the following error.
Err.Description - "Rowset does not support fetching backward."
Err. Number - -2147217884
Can somebody tell me the reason behind this error.
My Reasearch: I have got to know that I get this error in the line of code recordset.MoveLast in My code.
My Code environment: I have built an SQL Query string which calls the Strored procedure with one parameter (parameter tyoe is string type). I have attached the SP code below.
and SP contains the call for querying other database in the same server using OPENQUERY command.
and the VB code contains code as Recordset.MoveLast. (Error is generated here and Recordset.RecordCount is also -1)
SP code is:
Code Block
CREATE PROCEDURE [dbo].[sp_RMS]
@sRMS_Status CHAR(12)
AS
BEGIN
DECLARE @sSQL VARCHAR (1000)
SET @sSQL = '''SELECT *
FROM VW_JOKE
WHERE JOKE_ID = ''' + '''' + @sRMS_Status + '''' +
''' ORDER BY JOKE_NO''
EXEC ( 'SELECT * FROM OPENQUERY(lnk_joke__cat,' + @sSQL + ')' )
END
My Questions:
1). Is there any problem in SP as I am using OpenQuery? I queries the SP and concluded that it generates result properly.
2). Is there any problem with the Recordset.MoveLast line? I am using adOpenKeyset, adLockReadOnly as parameters to my Recordset.Open command along with other parameters.
Let me know the corrective steps to be taken to get rid of this error.
Thanks in Advance for your valuable time
Ranjan Jain
Hi.
In OLEDB Destination, AccessMode as OpenRowset Using FastLoad raises the following error:
"Failed to open a fastload rowset db_object. Check that the object exists in the database"
db_object exists in the database. Is there any key point for the table or view used for fastload?
Thanks
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?
I have a strange problem with an OleDB call to a stored procedure thatreturns a rowset.Only the first time I execute the query, after I restart SqlServer, myprogram crashes becausethe rowset is empty. All next calls (after restarting my program, ofcourse) run successfully.The context is:1) if I do not bind the output rowset, my program doesn't crash2) If I run the call without the binding and then I run the call withthe binding, it doesn't crash3) It is not the first query I do in my session4) the call crashes only if in the SP there are some inserts: it is awell known problem with OleDB, but in manyother cases I have fixed it setting NOCOUNT to ON
View 2 Replies View RelatedI am getting the following error when I execute my sql task:
An error occurred while assigning a value to variable "NullVar": " NO result rowset is associated with the execution of this query. "
I am executing a SP that has one input & one output parameter. The output parameter is returning a single row for debugging if the sp failes.
I tried using Jamie's method:(http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx) to get it to work but keep getting the above error. I have the following variables:
sqlSource (string) := Exec RBCprcsInsertWmsInvTransactionRecords '" + (DT_WSTR,10 ) @[User::SnapShotDate] + "', NULL"
NullVar (string)
In the execute sql task, I set the ResultSet to single row. I set SQLSourceType = variable & sourcevariable = user::SqlSource. In the result tab, I added a result set, NewResultName with the variable user::NullVar. I tried different configurations with the parameter mappings but nothing seemed to work. I didn't know if i still had to use this if I am using the sqlSource variable to drive the task.
So I am not sure what I am missing here. Anyone have any suggestions?
Thanks!
John
Locally I develop in SQL server 2005 enterprise. Recently I recreated my db on the server of my hosting company (in sql server 2005 express).I basically recreated the tables and copied the data in it.I now receive the following error when I hit the DB:The 'System.Web.Security.SqlMembershipProvider' requires a
database schema compatible with schema version '1'. However, the
current database schema is not compatible with this version. You may
need to either install a compatible schema with aspnet_regsql.exe
(available in the framework installation directory), or upgrade the
provider to a newer version.I heard something about running aspnet_regsql.exe, but I dont have that access to the DB. Also I dont know if this command does anything more than creating the membership tables and filling it with some default data...Any other solutions/thought on what this can be?Thanks!
I'm trying to store the output of my stored procedure into a temp table, which takes Table Type as Input.
CREATE TYPE [dbo].[Employee] AS TABLE(
[Field] [varchar](50) NULL,
[Criteria] [varchar](50) NULL,
[Value] [varchar](50) NULL
)
declare @Employee dbo.Employee
insert into @Employee values(N'Salary',N'is greater than',N'3520')
DECLARE @sql NVARCHAR(MAX)
SET @sql = 'SELECT * FROM OPENROWSET(''SQLNCLI'',''Server=localhost;Trusted_Connection=yes;'',''EXEC dbo.uspGetEmployee ' + @Employee + ')'
I need to create a stored procedure which uses output of the above stored procedure. Hence, I don't want to put declare and insert into OPENROWSET as I get those values as parameter to the new procedure.
Any other solution instead of using OpenRowSet.
I have a dataflow task. On which I have OLEDB as my source. I connect to my database and execute a stored proc. the stored proc results in a result set with only one row and two columns. First Column is an integer and the second row is a varchar(max) with xml script in it. Not that it should matter because it is in varchar(max).
Anyway, it give me an error
[OLE DB Source [321]] Error: A rowset based on the SQL command was not returned by the OLE DB provider.
What am I doing wrong?
Can I not have a stroed proc that returns a result set as my data source?
I have 35+ tables and 15+ stored procedures with SchemaA, now I want to transfer them to SchemaB.
I know how to do one by one...!
alter schema SchemaB transfer
SchemaA.TableA
but it will take long time...!
Thanks,
i have recently ported an old asp.net web application using crystal reports 9 from windows server 2003 to windows server 2008 . the crystal reports smoothly at first but one of the reports stopped working when the admin changed his password now the report is showing error. Failed to open a rowset
View 3 Replies View Relatedi got a error [OLE DB Destination [16]] Error: Failed to open a fastload rowset for "[dbo].[tempMaster]". Check that the object exists in the database.
i am creating and doping this table in beginning after insert/update i will drop this table but this is error.i am using sql server 2008R2
Hello everybody!I'm using ASP.NET 3.5, MSSQL 2005I bought virtual web hosting .On new user registrations i have an error =(The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'. However, the current database schema is not compatible with this version. You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version. On my virtual machine it work fine but on web hosting i have an error =(What can you propose to me?
View 2 Replies View RelatedHello,
I would like to use SSIS tool to move the data from one database schema to another database schema.
For example:
Source table has
1. UserName (varchar 20) (no null)
2. Email (varchar 50) (can be null)
Destination table has
1. UserID (uniqueidentifier - GUID)
2. UserName (varchar 50) (no null)
3. EmailAddress (nvarchar 50) (can be null)
4. DateTime
Questions:
1. What controls do I use in my Data Flow to make data move between databases with different data types and include new value in UserID as a new GUID and DateTime as a date (GETDATE)?
OLE DB Source, OLE DB Destination, Data Converson and .....
How do I insert Guid and Date at the same time?
2. I have many tables to do data moving. Any sugestions? How do I architect my project? If I create many data flows for each table - it will look complicated.
Please give me some advices here.
Thanks.
I have 2 Excel sheets ( Sheet1 and Summary) in an excel output file.
Sheet1 is created and loaded with data fine.
Summary sheet is getting the following error:
Error: 0xC0202009 at Write Counts and Percentages to Summary Sheet, Excel Destination [337]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37.
Error: 0xC02020E8 at Write Counts and Percentages to Summary Sheet, Excel Destination [337]: Opening a rowset for "Summary" failed. Check that the object exists in the database.
I do have an execute SQL task to create the summary sheet before the data flow task.
The execute SQL task has
CREATE TABLE `Summary` (
`Counts_and_Percentages` LongText
)
Please advise on what I can do to troubleshoot/correct the error. Thanks
More details on the error
DTS.Pipeline] Error: "component "Excel Destination" (337)" failed validation and returned validation status "VS_ISBROKEN".
My Excel file name is an expression
@[User::FullFilePath] + (DT_STR, 4, 1252)DATEPART("yyyy", @[System::ContainerStartTime]) + "-" +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("mm", @[System::ContainerStartTime]), 2) + "-" +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("dd", @[System::ContainerStartTime]), 2) + " " +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("hh", @[System::ContainerStartTime]), 2) +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("mi", @[System::ContainerStartTime]), 2) +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("ss", @[System::ContainerStartTime]), 2) + " CLIENT=" +
@[User::ACCOUNT_NAME] + " output.xls"
I have a SSIS package where im importing data from MS access database file .MDB. I chose Microsoft JET 4.0 OLEDB provider. When i select a table and preview the data, im getting the below error.
Error at Data Flow Task [OLE DB Source [1]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E09.
Error at Data Flow Task [OLE DB Source [1]]: Opening a rowset for "_tblDateDateEarnedMismatch" failed. Check that the object exists in the database.
But it is working for the other tables .
I used SSEUtil to add a schema to my database but I am having problems. Used these steps:SSEUtil -c> USE "c:Rich.mdf"> GO>!RUN Resume.SQL//indicates success>SELECT * FROM SYS.XML_SCHEMA_COLLECTIONS>GO//schema not shown in list> USE master>GO>SELECT * FROM SYS.XML_SCHEMA_COLLECTIONS>GO//schema is shown in the queryIt appears that the schema is not added to the desired database, so when I try to use the schema in Visual Studio, the schema does not appear when I connect to the Rich.mdf database. Any ideas on what I am doing wrong or why this might be happening?ThanksKevin
View 3 Replies View RelatedI am using sql server 2008 R2.I want to copy all the objects of one schema and put it in another schema. I want to do that from command prompt.
In oracle we can export the objects of one user and import to another user using exp and imp. I want similar type.
Hi!
I have a "little" problem with nested case model:
-- "normal" database:
DROP TABLE [unitInfo] ;
GO
CREATE TABLE unitInfo (
unitID INT PRIMARY KEY
, beginDate SMALLDATETIME
, area VARCHAR(10)
, partSize INT
, y2predict MONEY
) ;
go
INSERT INTO unitInfo
VALUES (1, '2007-02-01', 'home', 42, 10.0) ;
INSERT INTO unitInfo
VALUES (2, '2007-03-05', 'home', 43, 11.0) ;
INSERT INTO unitInfo
VALUES (3, '2007-02-02', 'office', 11, 11.4) ;
INSERT INTO unitInfo
VALUES (4, '2007-02-01', 'office', 10, 33.6) ;
INSERT INTO unitInfo
VALUES (5, '2007-02-01', 'office', 42, 44.1) ;
CREATE TABLE unitLog (
id INT IDENTITY(1, 1)
PRIMARY KEY
, logtime SMALLDATETIME
, -- combination of logtime/unitID is unique
unitID INT
, -- "FK" on unitInfo
m1 FLOAT
, m2 FLOAT
)
INSERT INTO [unitLog]
VALUES ('2007-01-01', 1, 43.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-01', 2, 43.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-01', 3, 63.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-02', 4, 432.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-02', 1, 43.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-03', 1, 423.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-04', 1, 432.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-05', 2, 43.0, 441.0)
INSERT INTO [unitLog]
VALUES ('2007-01-06', 2, 43.0, 4.0)
INSERT INTO [unitLog]
VALUES ('2007-01-06', 3, 43.0, 4.0)
INSERT INTO [unitLog]
VALUES ('2007-01-07', 1, 4.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-08', 1, 3.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-08', 1, 43.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-08', 1, 43.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-09', 2, 143.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-10', 3, 143.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-11', 4, 43.0, 144.0)
INSERT INTO [unitLog]
VALUES ('2007-01-11', 5, 43.0, 144.0)
INSERT INTO [unitLog]
VALUES ('2007-01-12', 2, 43.0, 144.0)
INSERT INTO [unitLog]
VALUES ('2007-01-13', 4, 413.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-14', 4, 43.0, 414.0)
INSERT INTO [unitLog]
VALUES ('2007-01-14', 1, 43.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-20', 1, 43.0, 414.0)
INSERT INTO [unitLog]
VALUES ('2007-01-22', 1, 43.0, 414.0)
-- SSAS:
CREATE MINING STRUCTURE NestedStructure
( unitID LONG KEY, beginDate DATE CONTINUOUS, area TEXT DISCRETE
, partSize LONG CONTINUOUS, y2predict DOUBLE CONTINUOUS
, logdata table ( [id] LONG KEY, unitID LONG CONTINUOUS
, m1 DOUBLE CONTINUOUS, m2 DOUBLE CONTINUOUS
)
)
ALTER MINING STRUCTURE NestedStructure
ADD MINING MODEL nestedModel ( unitID , beginDate REGRESSOR, area , partSize REGRESSOR
,y2predict REGRESSOR PREDICT_ONLY
, logdata ([id] , unitID
, m1, m2
)
) USING Microsoft_Decision_Trees
/* version 1*/
insert into NestedStructure ( unitID, beginDate, area, partSize, y2predict
, logdata(skip,unitID, m1, m2))
openrowset('sqloledb', Server=myserver;Trusted_Connection=yes;,
'Shape {select * FROM mydb.dbo.unitInfo }
Append ( { select id, unitID, m1, m2 from mydb.dbo.unitLog }
Relate unitID to unitID ) as logdata ')
Parsing the query ...
OLE DB error: OLE DB or ODBC error: Syntax error or access violation; 42000.
Parsing complete
Where is the error?
/*version 2*/
CREATE MINING STRUCTURE NestedStructure1
( unitID LONG KEY, beginDate DATE CONTINUOUS, area TEXT DISCRETE
, partSize LONG CONTINUOUS, y2predict DOUBLE CONTINUOUS
, logdata table ( [id] LONG KEY, unitID LONG CONTINUOUS
, m1 DOUBLE CONTINUOUS, m2 DOUBLE CONTINUOUS
)
)
ALTER MINING STRUCTURE NestedStructure1
ADD MINING MODEL nestedModel1 ( unitID , beginDate REGRESSOR, area , partSize REGRESSOR
,y2predict REGRESSOR PREDICT_ONLY
, logdata ([id] , unitID
, m1, m2
)
) USING Microsoft_Decision_Trees
insert into mining structure NestedStructure1 ( unitID, beginDate, area, partSize, y2predict
, logdata(skip,unitID, m1, m2))
Shape {openquery(dsnDB,'select * FROM mydb.dbo.unitInfo') }
Append ( { openquery(dsnDB,'select id, unitID, m1, m2 from mydb.dbo.unitLog') }
Relate unitID to unitID ) as logdata
Parsing the query ...
Error (Data mining):
INSERT INTO error: The '[logdata].[id]' nested table key column is not bound to an input rowset column.
Parsing complete
Remark that combination logtime/unitID is the natural key in unitLog.
"ID" is the surrugate key.
What is wrong here...?
SQL 7.0 SP3 replicating a database from a publisher to a SQL 7.0 SP 3 subscriber.
Is there a way to change publisher schema without stopping the replication? Example Add a 'bit' column to a table with default of '0'.
Hi all,
I want to set up a db for an e-commerce site. I need to know how to set up the db correctly with out getting anything mixed up ... 'cos I never done an e-commerce db b4:
I need to know where and how to store the passwords, the product pictures and the customers delivery address which is different from the billing address.
The tables are as follows...
--Customer--
CustID
Email (email & pwd is for login)
password (will this be secure here)
Name
Address (should it all be in one column or firstline,secondline,zipcode columns)
DeliveryAddress
--Product--
ProductID
ItemName
Catogary
Price
SellingPrice
Quantity
ItemsPicture...(not sure where to link the pics to)
DistributorsID (the warehouse who dispatches the item)
--Distributors--
DistributorsID
Name
Company
Address
It would be much appreciated if you could share some info and tips for how to set it up all correctly.
Tools I'll be using are, ASP.NET(C#), ADO.NET, MSSQL (Stored procedures)
Thanks In Advance
HI All,
What Is Schema in sql Server 2005 ?