Hi all,
is there any tool which is capable to convert query initially written for
msaccess database to query for mssql server.
I have tons of queries which contains iif, trim and similar functions which
should be converted to case, ltrim(rtrim(, etc. etc.
Does anyone know for tool which could do that automatically?
Thanks in advance,
Anabella
Does anyone know of a good XML parser where I can map out tables and fields in an SQL 6.5 database and have that information written out XML format?
View 1 Replies View Related
I can not get this query to work can some on help me?
SELECT count(JOBID) AS 'transcount', count(distinct patientid) AS 'patient count', sum(TRANSPORTATION_TCOST) AS 'tcost,sum(TRANSPORTATION_DISC_COST) as ' dtcost ',avg(TRANSPORTATION_DISC) as "avgTDisc",(sum(transportation_tcost) + sum(TRANSPORTATION_DISC_COST)) as "TGrossAMTBilled', (sum(transportation_tcost) / count(DISTINCT patientid)) AS 'PatAvgT', sum(TRANSPORTATION_DISC) AS 'avgPercentDiscT' job.JURSDICTIONFROM job LEFT JOIN payer ON payer.payerid = job.payerid LEFT JOIN states ON job.jurisdiction = states.initialsWHERE (job.transportation = '1') AND ((job.datedcreated = @startdate) AND (job.datecreated = @enddate)) AND states.region = 'GA'GROUP BY job.JURISDICTIONORDER BY PatAvgT DESC
I would like to know about the website who had convert the code asp to asp.net
please help me
Ashwani Kumar
Hi all!
I have one UDF which take 5 params. When I run this UDF from Query Analyzer (select * from dbo.UDFname (param1, ..., param5)), it take 5 seconds, but when I run from ADP project (doble click), it take > 200 seconds. In both situations I obtain the same result (of course). I look at the connexion, seems ok (File -> Connexion; in .adp).
Can anybody tell me why are this differences? And how to surpass this ? I want to use the access .adp to make one front to run a MSDE db.
Thank's in advance :)
koci.
Hi all!
I have one UDF which take 5 params. When I run this UDF from Query Analyzer, it take 5 seconds, but when I run from ADP project (doble click), it take > 200 seconds. I look at the connexion, but I don't see nothing to change (File -> Connexion; in .adp).
Can anybody tell me why?
Thank's in advance :)
koci.
Can someone help me parsing this ms-access PIVOT sql-statement to ams-sql-server sql-statement?Many thanks in advanceTRANSFORM Count(KlantenStops.id) AS AantalVanidSELECT KlantenStops.Uitvoerder, KlantenStops.KlantFROM KlantenStopsGROUP BY KlantenStops.Uitvoerder, KlantenStops.KlantPIVOT DatePart("m",leverdatum,1,0) In("1","2","3","4","5","6","7","8","9","10","11","12");
View 3 Replies View RelatedI'm converting an ASP system from using msaccess to SQL Server as the db engine, and I'm stumped on the following query
update
timecard
set
TcdDate = #3/18/05#
TcdDate is defined as a date/time type
It will not run with the date bracketed by # signs, and when I take them out, 1/1/1900 is stored in the dbs. Is there a different symbol to bracket the date with or should I be using a function to convert the date?
I'm using MSAccess 2k as a front end to a SQL Server 2k database.
I have a user set up in SQL Server to be database owner for a specific database.
Using NT authentication.
Using ADO.
The MSAccess application executes a store procedure on the SQL Server that (1) deletes a tables if it exists, (2) creates and populates a table (the owner shows up in Enterprise Manager as the user name) and then (3) uses the table (read) in a join to do some other processing.
The (1) delete works fine.
The (2) table is created and populated fine.
On (3) the Select (inner join) to do the aditional processing I get an error message that the table name used is an invalid object.
Workaround.
If I create the table as sa separately and in the store procedure, instead of deleting, creating and populating the table I merely truncate the table and then repopulate it, everything works fine.
I would prefer to not have the table sitting around the database all the time.
Anyway, I'd like to figure out why the table is causing a problem.
Any ideas?
Thanks.
Hi,
I am getting below error when exporting data from SQL Server 2005 to MS Access.
[DTS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLE DB Destination" (1706) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
Its direct mapping from OLE DB Source (SQL Server 2005) to OLE DB Dest (MS-Access Database)
Help from anyone. Plz its urgent.
Regards,
hi,
I have MS Access Database (test.mdb), and i have a table called tblEmployee in that.
How to query the table from SQL Server Query Analyser?
Thanks in advance
qAnand
Migration from MsAccess to Sql-Server.
Problem:
Ing_ID = nz(me.Ing_AutoNr,0)
me.openform "MyForm",,,"ID = " & Ing_ID
This work in MsAccess with new records but this doesn't work in Sql-Server. How can I solve this?
Hi,
I Need to make a script or task that programmatically export data from SQL Server to Access File , and how to call the script from the .NET?
I need to switch from MS Access to SQL Server for my database. To set
up a development environment I downloaded the free Microsoft SQL Server
Express (February CTP version). I installed the required .NET Framework
v2, and then SQLExpress. The install was done using all the defaults,
and was done successfully. I also downloaded and installed the SQL
Express Manager Tool.
The SQL Server was installed on the same machine as my VS.NET
development environment. The SQL Server process is now running, and I
can connect to the server using the SQL Express Manager Tool. This
allows me to view and query the sample databases, but not much else.
To convert my Access .MDB database to SQL Server, I am trying to use
the MS Access Upsizing Wizard. The version of Access I am using is
Access 2002 on a Windows XP-Professional system. The problem is that
Access cannot get a connection to the SQL Server. I tried using the
default server name "(local)" and "Use Trusted Connection", but I
receive the following error:
Connection failed:
SQLState: '01000'
SQL Server Error: 2
[Microsoft][ODBC SQL Server Driver[]Shared Memory]ConnectionOpen (Connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver[]Shared Memory]SQL Server does not exist or access denied.
Curiously, I get this exact same error message even if the SQL service
is stopped. So I'm pretty sure the problem is that it is not finding
the SQL server, and not a security issue.
In order to connect to the SQL server using the SQL Server Manager
Tool, you have to provide the actual instance name for the server
"COMPNAMESQLExpress". So I tried using this server name in the Access
Upsizing Wizard, but this returns the same error message as above
except the first SQL Server Error is 53. I also tried using a Login ID
and password (using the Windows administrator ID and password, and also
the "sa" ID and password) to no avail.
I am at wits end, and can't figure out why Access can't find the SQL Server. Any ideas would be appreciated.
Thanks.
hi,
i export datas from sql server 2000 to Ms Access. but few tables only not expot. and error has come "the microsoft jet data base engine cannot find the input or Query "<t.n>" make sure it exists and that its name is spelled correctly.
but tables are live in sql server 2000.
Please give solution as soon
I need help! When installing SQL Server 2005 Express on one machine, I get an error with the following summary.txt
Machine : xxx
Product: MSXML 6.0 Parser
Product version: 6,10,1129,0
Installed: Failed.
Log file: c:Program Files... (see below)
Error Number: 1625
in log file from above.
MainEngineThread is returning 1625
This installation is forbidden by system policy. Contact your system administrator.
The person trying to install has administration privileges. BTW: The computer shares a wireless internet connection with other computers in the office but the clients or not setup to share data. My user is installing SQL, the data, and app on a single client. The MS Sql Server 2005 express seems to install on all other boxes. Thanks for your time.
Zone: SQL Server 2005
Hi,
I have a msaccess linked server that I use to execute sql 2000 stored procedures from a front end in adp (access data project) format without any problem, if it is used on the same machine where the sql server resides (with any user logged on). In any other machine on the local network where I also need to use it, I get an ole/db error message saying that the microsoft jet database engine can not open the file because it's allready opened exclusivly or because it do not has permissions. I created the linked server with both UNC and normal path with the same result.
Thank's for all the help/clues you can give me.
Hi All,
How can I link a view into MS Access from MS Sql Server, that I can update as a tabble.
Thanks
Laszlo
Environment:Server1 (Local)OS Windows 2000 ServerSQL Server 2000Server2 (Remote)OS Windows 2003 ServerSQL Server 2000(Both with most recent service packs)Using Enterprise Manager, we have set up the Link Server (LINK_A) inthe Local Server 1 to connect to Server 2.The SQL we need to run is the following:INSERT INTO table1(column1,column2)SELECT A.column1, A.column2FROM LINK_A.catalog_name.dbo.table2 AS AWHERE A.column1 xxxx;When we run this from the Query Analyzer, it completes with no problemsin a few seconds.Our problem:When we add the DTS Package as the ActiveX Script (VB Script) to theLocal Package, it times out at "obj_Conn.Execute str_Sql"Dim Sql, obj_ConnSet obj_Conn = CreateObject("ADODB.Connection")obj_Conn.Open XXXXobj_Conn.BeginTransstr_Sql = "INSERT INTO table1("str_Sql = str_Sql & "column1"str_Sql = str_Sql & ", column2"str_Sql = str_Sql & ")"str_Sql = str_Sql & " SELECT Ａ.column1"str_Sql = str_Sql & ", A.column2"str_Sql = str_Sql & " FROM LINK_A.catalog_name.dbo.table2 AS A"str_Sql = str_Sql & " WHERE A.column1 0"str_Sql = str_Sql & ";"obj_Conn.Execute str_Sql----------------------------------------------------------When we make a Stored Procedure and run the following SQL, it freezes.INSERT INTO table1(column1,column2)SELECT A.column1, A.column2FROM LINK_A.catalog_name.dbo.table2 AS AWHERE A.column1 xxxxWe've also tried the following with the same results;INSERT INTO table1(column1,column2)SELECT A.column1, A.column2FROM [LINK_A].[catalog_name].[dbo].[table2] AS AWHERE A.column1 xxxxThe same thing happens when we try to run the "SELECT" by itself.SELECT TOP 1 @test=A.column1FROM LINK_A.catalog_name.dbo.table2 AS AWHERE A.column1 xxxxORDER BY A.column1What is going wrong here, and how do we need to change this so that itruns without timing out or freezing?
View 2 Replies View Related
Hi
I have a linked server to MSAccess DB and, when I try to select record from a table that have a column
with a bad date formatted (with year less than 1753) I receive the message:
.... conversion error from DBTYPE_DBTIMESTAMP to datetime....
My scope is set to NULL this bad values from Sql Server ...
I first try with something like
UPDATE LNK_SVR...TABLE SET FLD=NULL WHERE ISDATE(FLD)=0
But I receive the same error... perheaps the provider generate the error before an Sql Server evaluation ...
So I think to a passthrought query:
exec sp_serveroption @server='LNK_SVR',@optname='rpc out',@optvalue='true'
exec LNK_SVR...sp_executesql 'update table set FLD=NULL WHERE YEAR(FLD)<1753'
But I receive the folowing message ....
Messaggio 7213, livello 16, stato 1, riga 1
Il provider non รจ riuscito a passare parametri di stored procedure remota al server remoto 'LNK_SVR. Verificare che il numero, l'ordine e il valore dei parametri passati siano corretti.
Any suggestion is appreciate.
Thanks in advance
Matteo
Mds
Does enabling/disabling Data Execution Prevention have a performanceimpact on SQL 2000 or SQL 2005?For SQL best performance - how should I configure for:Processor Scheduling:Programs or Background servicesMemory Usage:Programs or System Cache
View 9 Replies View RelatedHello:
I am currently work on mssql 6.5. On my workstation, I have mssql 6.5
cient software.
However, I would like to install mssql 7.0 server on my nt workstation
and work with it to become familiar with 7.0. Can I install mssql 7.0
server on my nt workstation? Can mssql 6.5 client coexist with mssql 7.0
on the same machine if they are in different directories?
Thanks.
David Spaisman
Anybody dealt with EDI parsing/composing?
View 7 Replies View RelatedHi,
Is there any built-in SQL parser available in .NET framework?
I need such a function that when clients send me a SQL statement, I can parse what Tables are within the SQL, parameters, where condition, etc.
Thanks in advance.
Ricky.
I'm getting an error in a Execute SQL command task that returns XML. The error is: An invalid character was found in text content. I traced the error to this article:
http://support.microsoft.com/kb/238833
My question is how would I go about changing the encoding scheme? I've been able to run this package successfully on our dev SSIS box, but it's failing in prod. I suspect because the encoding schemes are different. Where do I go to verify this? How would I change it?
Thanks,
Phil
I get a parser error on a particular page that reads:
Server Error in '/' Application.
Parser Error
Description: An error occurred during the parsing of a resource required to service this request. Please review the following specific parse error details and modify your source file appropriately.
Parser Error Message: The base class includes the field 'LongDescription', but its type (FreeTextBoxControls.FreeTextBox) is not compatible with the type of control (System.Web.UI.HtmlControls.HtmlTextArea).
Source Error:
Line 35: <td class="content" align="right" valign="top">Long
Line 36: Description: </td>
Line 37: <td class="content" align="left" colSpan="3"><textarea id="LongDescription" runat="server" rows="4" class="Content" cols="50"></textarea></td>
Line 38: <TD class="ContentTable" width="1"><IMG src="images/clear.gif" width="1"></TD>
Line 39: </TR>
Source File: *sslmanagementControlsProductDetailsControl.ascx Line: 37
Version Information: Microsoft .NET Framework Version:1.1.4322.2300; ASP.NET Version:1.1.4322.2300
If I comapare the code with the equivalant page in another working instance they are line for line the same.
I need to parse SQL statements directly and extract each segmentindividually. Is there a way reference the Microsoft SQL Parserdirectly from VB.Net?Thanks!*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 6 Replies View Related
Hi every one,
I am having a problem with SSIS. i want to two email addresses to the dts via sql server, ex:
select @strSendTo = 'jsilva@pt.sss.com;vcarreira@pt.sss.com'
select @getdisp = 'dtexec /f "'+ bas.dbo.PathName('SSIS') + 'ProjectsXPNSSISANFSSISANFgetTestes.dtsx" /set Package.Variables[SendTo].Value;"' + @strSendTo + '" /set Package.Variables[vezes].Value;"33"'
exec xp_cmdshell @getdisp
but it returns an error saying:
Argument ""Package.Variables[SendTo].Value;jsilva@pt.imshealth.com;vcarreira@pt.imshealth.com"" for option "set" is not valid.
Can u help me plz.
Thanks.
Looking for TSQL stored procedure code to parse a single column's value into pieces (each piece stored in a seperate variable) for a subsequent update of other columns on this row -- (generically):
1. Declare/Open cursor on TBLx where COOKIES like '%CLIENT1=' or like '%CLIENT2=%' or like '%CLIENT3=%'
2. Fetch row; initiate 3 Loops to locate the trailing values of CLIENT1=nn, CLIENT2=nn,CLIENT3=nn (eg. CLIENT1=3243, CLIENT2=11, CLIENT3=4451)
3. Save the 3 values in variables (eg @VARclient1=3245, @VARclient2=11, @VARclient3=4451)
4. UPDATE where current of cursor.. SET COL1=@VARclient1, COL2=@VARclient2, col3=@VARclient3)
Caveat.. in step 2 above, the various CLIENT strings can be listed in various orders in any various start positions w/in the COOKIEs column)
(eg. CLIENT3=732,CLIENT1=9,CLIENT2=32 ----- starting in a variable position)
I'm familiar w/ the CHARINDEX feature... Any smaple code would be greatly appreciated.. thx in advance!
BT (william.t.turner@ceridian.com)
Back last October Paul DB mentioned the MS Log Parser tool and that he was using it for Exchange Transaction Logs.
I'm not an SQL person myself and would dearly love a little help to get started. Paul, I would be most grateful if you could post/send me even one example of the command line you use to get info out of Exchange T Log files. (I'm using 5.5 at present.)
Much thanks in advance,
Babylon Burns.
When I'm in query analyzer writing a stored procedure, if I make a mistake it will frequently go on and create the procedure without any warnings...I am on 9.00.3042.00, developer edition when this happens.
i.e., i had a stored procedure that had input parameters, and I declared a parameter @xyz nvarchar=NULL. It created the stored procedure anyway, and parsed it correctly as well. In fact, clicking the little 'parse' checkmark doesn't appear to do anything at all...
I didn't find out about my error until I ran the script in a 2000 database.
Is this a known defect? Will there be a patch? Is there a setting I don't yet know about to fix this?
Informatica has an XML parser component that allows me to read an xml file from a data source (Oracle Clob attribute in table in this case), parse it out in our mapping, and then transform the parsed date.
Does anyone know if SSIS has similar functionality?
Flow:
DataSource --> XML Parser --> Expression Component (Transform) --> DataTarget
Thanks
Scott
Can anyone tell me why my syntax is wrong and a fix? Obviously it's the PERIODICITY_HINT, but I'm doing what the BOL says...
ALTER MINING STRUCTURE [zzz]
ADD MINING MODEL [xxx]
(
...
)
Using Microsoft_Time_Series (
PERIODICITY_HINT = {12},
AUTO_DETECT_PERIODICITY=0.1
)
I get this error:
Query (9, 24) Parser: The syntax for '{' is incorrect.