Search results for query: *

select cast(substring(PID,10,4)-right(PID,4) as int) returns an invalid operator for data type error. I'm going to try SQLDenis suggestion shortly. Thanks for the help.

Thanks ca8msm, I'm trying to construct the select statement first. Here is what I have so far: select CODE = case when len(PID) = 17 then left(PID,9) substring(PID,10,4) else PID end from mySourceTable My question is how do I proceed further with the code to be able to...

My table has the field (PID varchar 17) either 13 or 17 character records. I would like to use the logic below to construct a new table with two fields (CODE varchar 13) and (CODEKEY varchar 17) IF len(PID) = 13 Then insert into iTable ESLE IF len(PID) = 17 Then 1. insert the first 9 characters...

Question for James, t1 and t2 are those sql tables? are they temp tales then or what? Thanks

...the AS400 file. Here is some code UPDATE sqlEmp SET EmpID = SEEN, Fname = SEFNM, Lname = SELNM, Add = SESTR1, Status = SESTAT FROM (SELECT * FROM OPENQUERY(AS400Svr, 'select SEEN, SEFNM, SELNM, SESTR1, SESTAT from my400Lib/my400File')) Is there a better way than this? thanks

I'm currently using OPENQUERY to move data form AS400 to SQL. INSERT is what I use and it is working just fine. However I'm just curious on how UPDATE query syntax is written for similar puropse. Any headsup is appreciated. Thanks

Thanks to all for their response. I end up with the following: IF (select * from openquery(MYLINKEDSERVER, 'select count(*) from myFile/myLib')) > 0 BEGIN DELETE mySQLtable INSERT INTO mySQLtable(R1, R2, R3) SELECT * from openquery(MYLINKEDSERVER, 'select W1, W2, W3 from...

...Server I linked to an iSeries AS400 and everything works just fine. I can query the AS400 file/table with the following count statement: select * from openquery(MYLINKEDSERVER, 'select count(*) from myFile/myLib') I would like to use an IF statement first to check whether the AS400...

I suggest you write some code and let us know what you'r thinking and I'm sure someone will help you.

You right nigelrivett, Query Analyzer does the trick. What I found out was that when you run Enterprise Manager Wizards against objects owned by anyone other than yourself or dbo, you will receive such an error. The solution for me was to user either Query Analyzer or Edit server registration...

I'm geting the error message "Error 21776 [SQL-DMO]the name 'mytable' was not found in the Tables collection". I was trying to create a non clustered index on 'mytable' using the Wizard from Enterprise Manager. This is happening to all the talbes in that database. How can I correct...

Hello all, I have a decimal data type in my table that holds financial data. I setup the scale to 2, so it holds the decimal places. SQL for some reason kept rounding up. How do I display the data as is. Example, 125.89 display as 126.00 Should I use 'money' data type rather than the decimal...

Hi everyone, I'm trying to setup transactional replication. During the process of setting up the Distributor I'm getting the following message: "The snapshot folder path, "\\\MyServer\D$\MSSQL\...", uses a special share name which can usually be accessed only by a login with...

Hey AngelWPB could you please point me to a document or something about how to set up such a process. That would be great. I'm still open for more ideas. Thanks

Hell everyone, I currently have our AS400 linked to SQL Server and we are doing a nightly upload from AS40 to SQL. This process is working great and our intranet site is flying. We need to upload some what same data into our internet hosting company's SQL server. I'm just looking for ideas...

I solved my problem as well. Group by did for me. insert into t1(f1) select * from openquery(myAS400, 'select f400 from Lib1/File1') group by f400

...records and keep the first one of each group and delete the rest. My field f1 is varchar type. If I write my query this way - see below - I get everything including the duplicates: insert into t1(f1) select * from openquery(myAS400, 'select f400 from Lib1/File1') Any help is greatly...

I would like to execute an UPDATE statement rather than the INSERT statement. That way I can update my SQL table quickly as changes occur to the AS400 file. Thanks in advance. I tried the DTS method, but I got to the point where I still have to build a query to transfer data. I rather just do...

...Can someone give me a hand on where to start? I'm thinking to create a stored procedure that query the AS400 and update my sql table nightly. My current query is a simple one: SELECT * FROM OpenQuery(MyServer, 'SELECT field1, field2 FROM LIBRARY1/FILE1'). Thanks for all the help I can get.

Thanks to both Jay and lienyi. lienyi you are exactly right when I replaced the period with / it worked. You guys are good. Can you guys point me to a book or manual on how those queries work on the AS400. I appreciate that. Els2