Sybase OLE DB And Variables Expressions
Feb 17, 2006
I just tried to read data from a sybase db by using a simple "select * from " + @[User::Tablename] defined as expression in a variable, using this variable as command in a Sybase OLE DB source. EvaluateasExpression is set to true, connection usually works fine.
When I try the preview now in the ole db source I receive an error from the
[DataDirect ADO Sybase Provider] "Incorrect syntax near the keyword WHERE"
My Variable does not inlcude any where clause, so does someone know, if the Sybase OLE DB provider add keywords to a statement or where does the WHERE come from??
Thanks in advance
nobsay
View 1 Replies
ADVERTISEMENT
Dec 11, 2007
I want to have this query insert a bunch of XML but i get this error...
Msg 128, Level 15, State 1, Procedure InsertTimeCard, Line 117
The name "ExpenseRptID" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
Msg 128, Level 15, State 1, Procedure InsertTimeCard, Line 151
The name "DateWorked" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
What am i doing wrong...Can anyone help me out!! Thanks!!
p.s I know this query looks crazy...
Code Block
IF EXISTS (SELECT NAME FROM sysobjects WHERE NAME = 'InsertTimeCard' AND type = 'P' AND uid=(Select uid from sysusers where name=current_user))
BEGIN
DROP PROCEDURE InsertTimeCard
END
go
/*********************************************************************************************************
** PROC NAME : InsertTimeCardHoursWorked
**
** AUTHOR : Demetrius Powers
**
** TODO/ISSUES
** ------------------------------------------------------------------------------------
**
**
** MODIFICATIONS
** ------------------------------------------------------------------------------------
** Name Date Comment
** ------------------------------------------------------------------------------------
** Powers 12/11/2007 -Initial Creation
*********************************************************************************************************/
CREATE PROCEDURE InsertTimeCard
@DateCreated DateTime,
@EmployeeID int,
@DateEntered DateTime,
@SerializedXML text,
@Result int output
as
declare @NewTimeCardID int
select @NewTimeCardID = max(TimeCardID) from OPS_TimeCards
-- proc settings
SET NOCOUNT ON
-- local variables
DECLARE @intDoc int
DECLARE @bolOpen bit
SET @bolOpen = 0
--Prepare the XML document to be loaded
EXEC sp_xml_preparedocument @intDoc OUTPUT, @SerializedXML
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler
--The document was prepared so set the boolean indicator so we know to close it if an error occurs.
SET @bolOpen = 1
--Create temp variable to store values inthe XML document
DECLARE @tempXMLTimeCardExpense TABLE
(
TimeCardExpenseID int not null identity(1,1),
TimeCardID int,
ExpenseRptID int,
ExpenseDate datetime,
ProjectID int,
ExpenseDescription nvarchar(510),
ExpenseAmount money,
ExpenseCodeID int,
AttachedRct bit,
SubmittoExpRep bit
)
DECLARE @tempXMLTimeCardWorked TABLE
(
TimeCardDetailID int not null identity(1,1),
TimeCardID int,
DateWorked DateTime,
ProjectID int,
WorkDescription nvarchar(510),
BillableHours float,
BillingRate money,
WorkCodeID int,
Location nvarchar(50)
)
-- begin trans
BEGIN TRANSACTION
insert OPS_TimeCards(NewTimeCardID, DateCreated, EmployeeID, DateEntered, Paid)
values (@NewTimeCardID, @DateCreated, @EmployeeID, @DateEntered, 0)
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler
--Now use @intDoc with XPATH style queries on the XML
INSERT @tempXMLTimeCardExpense (TimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep)
SELECT @NewTimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep
FROM OPENXML(@intDoc, '/ArrayOfTimeCardExpense/TimeCardExpense', 2)
WITH ( ExpenseRptID int 'ExpenseRptID',
ExpenseDate datetime 'ExpenseDate',
ProjectID int 'ProjectID',
ExpenseDescription nvarchar(510) 'ExpenseDescription',
ExpenseAmount money 'ExpenseAmount',
ExpenseCodeID int 'ExpenseCodeID',
AttachedRct bit 'AttachedRct',
SubmittoExpRep bit 'SubmittoExpRep')
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler
-- remove XML doc from memory
EXEC sp_xml_removedocument @intDoc
SET @bolOpen = 0
INSERT OPS_TimeCardExpenses(TimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep)
Values(@NewTimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep)
select @NewTimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep
from @tempXMLTimeCardExpense
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler
-- For time worked...
INSERT @tempXMLTimeCardWorked(TimeCardID, DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location)
SELECT @NewTimeCardID, DateWorked, ProjectID, WorkDescription, BilliableHours, BillingRate, WorkCodeID, Location
FROM OPENXML(@intDoc, '/ArrayOfTimeCardWorked/TimeCardWorked', 2)
WITH ( DateWorked DateTime 'DateWorked',
ProjectID datetime 'ProjectID',
WorkDescription nvarchar(max) 'WorkDescription',
BilliableHours float 'BilliableHours',
BillingRate money 'BillingRate',
WorkCodeID int 'WorkCodeID',
Location nvarchar(50)'Location')
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler
-- remove XML doc from memory
EXEC sp_xml_removedocument @intDoc
SET @bolOpen = 0
INSERT OPS_TimeCardHours(TimeCardID, DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location)
Values(@NewTimeCardID,DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location)
select @NewTimeCardID ,DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location
from @tempXMLTimeCardWorked
-- commit transaction, and exit
COMMIT TRANSACTION
set @Result = @NewTimeCardID
RETURN 0
-- Error Handler
ErrorHandler:
-- see if transaction is open
IF @@TRANCOUNT > 0
BEGIN
-- rollback tran
ROLLBACK TRANSACTION
END
-- set failure values
SET @Result = -1
RETURN -1
go
View 1 Replies
View Related
Jun 24, 2007
Hi,
I have encountered some strange output when testing an SSIS package I've been working on.
Please see this thread for background information: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1590716&SiteID=1. The previous thread was resolved and i have used the tips listed here to create a package with Data Flow Tasks that use expressions to create 'dynamic' queries.
The Data Flow tasks use two package-scope variables to determine the date range that they're operating on - this allows me to script the from / to dates (usually 'last week' range) or, in event of error, 'back date' the job easily without having to update each individual components.
However, something weird is happening - when I have two data flow tasks (DFT) in the same Sequence Container (tasks that are not 'connected' or configured to operate in a flow) only one DFT is picking up the package variable & returning the rows. The other DFT is not returning any rows & seems to be using the default value of the variables.
I have tested this by simply enabling DFT A and disabling DFT B - debugging shows only one DFT executes correctly. The same point is illustrated when I disable DFT A and enable DFT B. However, if I configure DFT A to run after DFT B then both pick up the correct variable values and return the correct row counts!!
I want Data Flow Tasks that pull data from the same remote source to be the same sequence containers so when an error occurs I can get container level transaction support.
Note:
I have thoroughly checked the 'evaluated' expressions in IDE and in Management Studio - this is an SSIS specific quirk (or me!).
Questions:
What's happening here, have I made a conceptual error of how this should work?
On the same variable topic: is there a way to set values for package level variables at execute time .e.g via command line execution / dtsexecui??
Let me know if you need any extra explaination / diagrams etc to understand this, the other (closed) thread I have listed above should provide more context for my problem
Thanks for your support!!
View 2 Replies
View Related
Jun 9, 2004
hi all,
when I try to do the following insert for the table test
create table test (outputs character(10), chk integer)
insert into test values('a',((select count(*) from test where outputs='a')+1))
I am getting the error
The name 'outputs' is illegal in this context. Only constants, constant expressions, or variables allowed here. Column names are illegal.
when i tried the same in DB2 it's working fine. is there anyerror in my syntax or this kind of function not allowed in SQL Server.
regards
Melb
View 1 Replies
View Related
May 18, 2008
hi,
i tried the document below pertaining to linking sybase 12.5 to sqlserver 2000 and sqlserver 2005
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=311875&SiteID=17
http://www.sybase.com/content/1029118/1029118.doc
but when our company upgrade the sybase to 15.x i can't find the oledb configuration stated on the document.
so i can't linked the sybase 15.x now to sqlserver 2005. can somebody help me.!!!please...
where i can find the oledb configuration in sybase 15.xx..
tnx..
View 1 Replies
View Related
Sep 4, 2006
Hi,
I am not comfortable with DTS 2000 but I need to execute a encapsulated DTS 2000 package from a SSIS package. The real problem is when I need to pass SSIS variables to DTS 2000 package. The DTS 2000 package have 3 global variables that I can identify on " Execute DTS 2000 Package Task Editor - Inner Variables ". I believe the SSIS variables must be mapped on " Execute DTS 2000 Package Task Editor - OuterVariables ". How can I associate the SSIS variables(OuterVariables ) to "Inner Variables"? How can I do it? Much Thanks.
João
View 8 Replies
View Related
Jan 24, 2006
Hi,
I would like to design a SSIS package, which have couple of variables. It loads a xls file specified in a variable [varExcelFileFullPath] .
I will run it by commands: exec xp_cmdshell 'dtexec /SQL ....' (pls see an example below).
It seems it does not get the values passed in for those variables. I deployed the package to a sql server.
are there any grammar errors here? I copied it from dtexecui. It worked inside Dtexecui not in dos command.
exec xp_cmdshell 'dtexec /SQL "LoadExcelDB" /SERVER test /USER *** /PASSWORD ****
/MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW
/LOGGER "{6AA833A1-E4B2-4431-831B-DE695049DC61}";"Test.SuperBowl"
/Set Package.Variables[User::varExcelFileName].Properties[Value];"TestAdHocLayer"
/Set Package.Variables[User::varExcelWorkbookName].Value;"Sheet1$"
/Set Package.Variables[User::varExcelFileFullPath].Value;"D: estshareTestAdHocLayer.xls"
/Set Package.Variables[User::varDestinationTableName].Value;"FeaturesTmp"
/Set Package.Variables[User::varPreSQLAction].Value;"delete from FeaturesTmp"
'
thanks,
Guangming
View 2 Replies
View Related
Jun 13, 2007
Hi All.
Can anyone tell me if a Sybase database will run on SQL 2000?
Can this be done?
Cheers
Phil
View 2 Replies
View Related
Apr 20, 2004
Hi Everyone -
I have a master database (sybase) that is in place as a company
legacy server -
I am setting up a web server to act as an intranet and data entry
terminal system.
I want to ultimately remove sybase from the picture, but
for now, i need to grab information from the sybase server
and populate the MSSQL database with the values from the
sybase database.
So my question is this -
Is there a way to setup a "auto polling" or synching between the MSSQL
and the sybase system.
If there isn't then i will need to do a DTS job to grab the values from
the sybase ODBC connection, and populate my database that way -
but there must be another way to accomplish this task.
thanks
take care
tony
View 3 Replies
View Related
Jun 19, 2001
Hi all,
A question that may have been asked on many occasions, but what can be advised as the best way to migrate a Sybase 11.x DB to SQL 7.0, (without using BCP). Does DTS support such migrations and if so, where in DTS can this be done? I'd appreciate any basic help here to get me started on this exercise. MAny thanks
View 2 Replies
View Related
Dec 13, 2000
We plan to convert our current OLTP intensive application from Sybase on NT to SQL 2000.
Which one is the winner in terms of performance. I was told that SQL Server has less tools
to tune the database especially for OLTP intensive database.
Have you done any test comparing the performance between Sybase and SQL server?
What is your opinion on this? Do you know any good third party tools to tune SQL 2000?
Thanks!
View 1 Replies
View Related
Mar 18, 2003
Hello,
We have Trading Applications (Equities & Portfolio written on C++) on MS SQL Server 2000. Now the management is deciding to move it on Sybase 12.5. We currently don't have any issues on any matter, but because everyone is SYbase fan here, thinks applications are critical and we need to move to Sybase. Can anyone who has worked on both knows the major pros & cons of both the system. Actually I wanted these systems to be on MS SQL Server. I need solid reasons except being cheaper than Sybase to show why we should use MS SQL Server
If anyone can share their experiences, it would be really great.
Thanks
Sejal
View 2 Replies
View Related
Aug 11, 2003
I have the following SQL statement from a Sybase 5 database and I don't know how to get it to work in a SQL 7 database. Any help much appreciated.
TIA,
DBA without the PAY
SELECT firms.id,
firms.description,
firm_mailing_addresses.firm,
firm_mailing_addresses.address1,
firm_mailing_addresses.address2,
zip_codes_a.city as mailing_city,
zip_codes_a.state as mailing_state,
firm_mailing_addresses.zip_code as mailing_zip,
firm_mailing_addresses.zip_plus4 as mailing_zip_plus4,
firm_physical_addresses.firm,
firm_physical_addresses.directory_phone,
firm_physical_addresses.address1,
zip_codes_b.city as physical_city,
zip_codes_b.state as physical_state,
firm_physical_addresses.zip_code as physical_zip,
firm_physical_addresses.zip_plus4 as physical_zip_plus4
FROM {oj {oj firms LEFT OUTER JOIN firm_physical_addresses
ON firms.id = firm_physical_addresses.firm}
LEFT OUTER JOIN firm_mailing_addresses
ON firms.id = firm_mailing_addresses.firm},
{oj firm_physical_addresses LEFT OUTER JOIN zip_codes zip_codes_b
ON firm_physical_addresses.zip_code = zip_codes_b.zip_code
AND firm_physical_addresses.zip_plus4 = zip_codes_b.zip_plus4},
{oj firm_mailing_addresses LEFT OUTER JOIN zip_codes zip_codes_a
ON firm_mailing_addresses.zip_code = zip_codes_a.zip_code
AND firm_mailing_addresses.zip_plus4 = zip_codes_a.zip_plus4}
WHERE firms.id = 120
View 4 Replies
View Related
Apr 18, 2001
I will be migrating Sybase Databases (and Centura) to MS SQL Server Databases. While I have administered MS SQL Server for many years, I have never administrated or even saw a Sybase database. My question, can anyone suggest any books to get me up to speed on Sybase, so I can transfer all databases, including tables, procs, etc... to MS Sql Server?
Bill
View 2 Replies
View Related
Jul 22, 2004
has anyone had sucess in troubleshooting conflicts between these two?
I need to shut down the sybase client on a machine that is running MS SQL if thats possible.
View 3 Replies
View Related
Sep 3, 2004
Good day.I have dts running every 10 minutes in a server of slqserver 2000 who is in a country and that it sends data to another country which has a data base sybase12.5.The problem is when by reasons for the WAN the connection fails from a country to another one the dts or job fails executing its processes and I must soon give click to stop and start so that it continues. That I can do so that when the connection is lost and i need to dts reconected automatically and is continued its processing?
thanks and sorry my english :)
View 1 Replies
View Related
Sep 3, 2004
If I need to have SQL Server be able to query a Sybase database on another machine (Adaptive Server anywhere 7.0.2 with an ODBC connection), what is the simplest, least expensive way to do this ?
Thanks
View 1 Replies
View Related
Oct 6, 2004
Coming from Sybase env. I would like to know the equivalent of sp_showplan. What it does is, it shows the showplan for the query being run by spid, which is passed as a parameter to sp_showplan.
Thanks for your help.
View 3 Replies
View Related
Mar 30, 2006
Hello!
Has anyone ever experienced compatibility and/or performance issues when SQL 2K and Sybase are run concurrently on the same server? I believe the server is using Windows 2K3.
Thanks!
View 5 Replies
View Related
Apr 18, 2008
My tribe is creating a central database, running Sybase 5 and Eaglesun. I’m curies if anyone has worked with Eaglesun and if you had any issues with the software or with data conversion??
Thanks for the input
US Navy - We are fueled, armed, and go for launch.
View 2 Replies
View Related
May 19, 2008
Hi Gurus,
I am not sure is this the right forum to get solution to my question.
From where i can get sysbase basics.
I am posting this query in this forum coz this forum and gurus in this forum had helped me in lot critical situtions.
Hope this happens now also..
Thanks,
ServerTeam
View 2 Replies
View Related
Jul 5, 2006
Hi,
I know sql and Dataware housing.I got a job offering for sybase sql but I don't anything about sybase.Will it be in programming in Actually what we will do in sybase? What will be the nature of work? and I just want to know what type of question will be asked ? Please do reply.
Regards,
Raji
View 1 Replies
View Related
Nov 7, 2006
Hi there
I want to pull data out of sysbase database.
Could you tell me which connection should I use to connect to sybase database?
Rahul Kumar
View 4 Replies
View Related
Jan 30, 2006
Hi,
I am assuming someone has had success connecting to Sybase from SSIS. I am having trouble. And I freely admit naivety when it comes to something like this. :-)
I installed dbConnector 6.01 which has a Sybase connector as a part of the package. I then tried to create a connection manager for Sybase using OleDb, but it was not an option in the list of OleDb connections. So, am I correct in assuming that I need to use ODBC? If so, is there an OleDb Sybase driver that can be used with SSIS?
Any help in using SSIS to connect to Sybase would be appreciated.
Thanks,
- Joel
View 6 Replies
View Related
Apr 30, 2006
It's all well and good that Microsoft has finally entered the well-established mobile database market (the market that iAnywhere Solution's SQL Anywhere product current *owns* by a wide margin). However, it should be noted that "SQL Everywhere" is a Sybase trademark. For confirmation of this fact, see http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.mro12.6.mro126ig/html/mro126ig/legal.htm
For confirmation of the fact that SQL Anywhere owns the mobile database market, note that it is the database of choice for the 500,000 mobile devices that will be used in the 2010 US Census; see http://www.internetadsales.com/modules/news/article.php?storyid=7347
Breck
View 2 Replies
View Related
Nov 30, 2005
Is there a way of writting into Sybase ASE/IQ?
View 1 Replies
View Related
Aug 6, 2007
Hi:
How connect Microsoft Visual Studio BI with Sybase ASE 12.0 (not 12.5.x) database via OLE DB?
I try with "Srvr=,[PORT];Catalog=[DBNAME];User Id=[USER];Password=[PASSWORD];" but error
"Error en la conexión de prueba debido a un error al inicializar el proveedor. El proveedor 'Sybase.ASEOLEDBProvider.2' no está registrado en el equipo local."
Thanks
View 1 Replies
View Related
Aug 21, 2007
I found a post from over a year ago saying that Sybase IQ as a DB Destination is not supported by SSIS (there was a hack, but not something for a strategic solution).
Has there been any change to this in the mean time?
Is there anything in the pipeline?
Thanks,
A Sak
View 9 Replies
View Related
Jun 7, 2006
When creating expressions we have access to a list of functions. It is my understanding that while these functions seem to have the same names and parameters as SQL functions, they are not so. They are implemented in the package libraries themselves. It is also my understanding that this function library cannot be extended to add new ones.
Am I correct? If so... why not?
Leaving alone the fact that they follow the same screwy names as SQL (instead of .NET on which SSIS is built on) and what seems to be a limited library (i.e. You have YEAR(), MONTH(), DAY() functions but no HOUR(), MINUTE(), or SEC() functions -- instead you have to use DATEPART())
I mean honestly... a common expression for most people is using date and times for folder and filenames... So instead of a simple .NET type expression of DateTime.ToString("yyyyMMdd") or Format(DateTime.Now, "yyyyMMdd_hhmmss") I end up with the very complex:
(DT_STR, 4, 1252) YEAR( GETDATE() ) + RIGHT("0" + (DT_STR, 2, 1252) MONTH( GETDATE() ), 2) + RIGHT("0" + (DT_STR, 2, 1252) DAY( GETDATE() ), 2) + "_" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("hour", GetDate()), 2) + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("minute", GetDate()), 2) + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("second", GetDate()), 2)
Personally I find myself using Script Tasks and Variables to "build" my expressions and just use Expressions to set the property to the variable. (Which I think may defeat the full purpose of expressions.)
Any thoughts?
View 6 Replies
View Related
May 25, 2007
How do I add comments to expressions I create in Reporting Services?
View 3 Replies
View Related
Jan 5, 2004
Hi,
A partner company unfortunately chose Sybase SQL Anywhere v5 as their database and I have to return data from the tables for a web service that I am writing - YUCK !
The ODBC driver is riddled with errors and has no support. Third party .NET providers do not seem to support v5, so I have resorted to playing with DTS in Enterprise Manager.
After fumbling around for a while, I managed to get an "Execute SQL Task" to return data and saved the package in both SQL and VB.
Now, before I waste any more time heading in this direction, can anybody advise if DTS is supported by MSDE as the full blown SQL Server will not be an option ?
If it is supported - is this a good way to go, or is there a better solution ?
Thanks in advance.
Steve.
View 2 Replies
View Related
Dec 14, 2001
Hi,
I have a situation where in I need to insert a record in sybase database table whenever there is an update on a table in my sql server database.
Can any one suggesst me hoe can I do it?
thanks In advance
Mohan
View 2 Replies
View Related
May 11, 2001
Since I'm not a native English, sorry for my poor English.
I want to migrate the current Sybase database to MSSQL 7.0, can anyone tell me the procedure to accomplish. Thank you.
Regards,
Kung Wu
View 2 Replies
View Related
