| Author |
Topic |
|
tinamiller1
Yak Posting Veteran
78 Posts |
 Posted - 2012-02-07 : 13:44:30
|
| I am trying to create a query to pull up all columns for a certain row number. This row number when I run my SAS SQL Proc SQL; query comes back in the log as there is an invalid varchar row 9054378701.So I need a query that I can pull that row only to see what in the row is causing my errors and fix it so the query in SAS runs.tina m miller |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-07 : 13:46:47
|
| is it SAS sql query you want help with? You may be better off posting it in some SAS forums then. Then is MS SQL Server forum and solutions given here are mostly sql server specific------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
tinamiller1
Yak Posting Veteran
78 Posts |
Posted - 2012-02-07 : 13:56:32
|
| No it is a SQL query I am running in SQL Server. In SAS the code errors because something is wrong with row 9054378701 in my SQL Server. I have an ODBC connecting to the SQL Server that houses this table I am using. So now I have to go into SQL Server and create a query to pull all columns for row 9054378701 and see what is causing my varchar error. Fix whatever column which I believe it is my procedure_code column but will not know until I can pull that row of data only. But this is being done in SQL Servertina m miller |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-07 : 13:57:55
|
| is row a field in sql server table? or is it an identifier SAS uses?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
tinamiller1
Yak Posting Veteran
78 Posts |
Posted - 2012-02-07 : 14:02:01
|
| no. I have 50 fields none of which are named row in the sql table named oxford2009claims. There is an error in row 9054378701 in one of those 50 fields that I need to fix in the oxford table. Once it is fixed then I can go back to my SAS program and rerun. My SAS program is telling me the SQL Server table Oxford2009claims has a varchar error in one of the 50 fields that is on row 9054378701tina m miller |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-07 : 14:06:43
|
quote:
Originally posted by tinamiller1
no. I have 50 fields none of which are named row in the sql table named oxford2009claims. There is an error in row 9054378701 in one of those 50 fields that I need to fix in the oxford table. Once it is fixed then I can go back to my SAS program and rerun. My SAS program is telling me the SQL Server table Oxford2009claims has a varchar error in one of the 50 fields that is on row 9054378701tina m miller
so its an identifier that SAS internally uses. no information reg. which column or even data causing issue?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
tinamiller1
Yak Posting Veteran
78 Posts |
Posted - 2012-02-07 : 14:10:32
|
| My table has almost a billion rows in it. My SAS program coding is set up to tell me if I get an error I want to know what row the error is coming from in my SQL Server table so I can go in and correct it. I just need to know how to do a query to get row 9054378701 in the SQL Server Oxford table. Otherwise since we do not have modify rights where you can click and view the actual table and then filter to that row I have to export the entire billion rows to a .csv if that can even handle it and use ultraedit to get to that row and see what the problem is. I suspect there is a " in the row because I have run into this before with other SQL Server tables we have. But those tables are no where near this size and the error occured in the top 1000 rows. So I could find it easily.tina m miller |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-07 : 14:13:40
|
quote:
Originally posted by tinamiller1
My table has almost a billion rows in it. My SAS program coding is set up to tell me if I get an error I want to know what row the error is coming from in my SQL Server table so I can go in and correct it. I just need to know how to do a query to get row 9054378701 in the SQL Server Oxford table. Otherwise since we do not have modify rights where you can click and view the actual table and then filter to that row I have to export the entire billion rows to a .csv if that can even handle it and use ultraedit to get to that row and see what the problem is. I suspect there is a " in the row because I have run into this before with other SQL Server tables we have. But those tables are no where near this size and the error occured in the top 1000 rows. So I could find it easily.tina m miller
thats why i asked whether row is a field in sql serverif not, there's no way of getting row corresponding to that number as sql server doesnt have concept of numbering rows in table in any order internally. So unless its value in a field of table you cant retrieve it directly using any sql queries.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
tinamiller1
Yak Posting Veteran
78 Posts |
Posted - 2012-02-07 : 14:15:25
|
| So I guess I need to export to UltraEdit and figure out the error. Well that kind of is a bad thing in SQL. No wonder my company is big time using DB2. I have no issues with our DB2 servers ever. SQL Server is a major thorn in our side and everyone complains about its limitations.tina m miller |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-07 : 14:55:57
|
quote:
Originally posted by tinamiller1
So I guess I need to export to UltraEdit and figure out the error. Well that kind of is a bad thing in SQL. No wonder my company is big time using DB2. I have no issues with our DB2 servers ever. SQL Server is a major thorn in our side and everyone complains about its limitations.tina m miller
just out of curiosity how would you've found out row if it was DB2 instead? does it have some kind of internal numbering of rows?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2012-02-07 : 18:02:06
|
quote:
Originally posted by tinamiller1
So I guess I need to export to UltraEdit and figure out the error. Well that kind of is a bad thing in SQL. No wonder my company is big time using DB2. I have no issues with our DB2 servers ever. SQL Server is a major thorn in our side and everyone complains about its limitations.tina m miller
WOW! That's how they all work. Don't blame the product for someone's lack of knowledge about it.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
tinamiller1
Yak Posting Veteran
78 Posts |
Posted - 2012-02-10 : 17:58:06
|
| found the problem. it is a table issue and sql server issue. had to run a ton of queries in sql server to find that integer that was erroring and found the column it was in. had to change it to is column name not null instead of column name >0tina m miller |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-10 : 18:31:49
|
quote:
Originally posted by tinamiller1
found the problem. it is a table issue and sql server issue. had to run a ton of queries in sql server to find that integer that was erroring and found the column it was in. had to change it to is column name not null instead of column name >0tina m miller
tell that its a code issue. what has sql server as a product to do in it if you've not written logic as not null correctly ?  ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
Query to pull certain row