0

I'm trying to call a 3rd party stored procedure on a MS SQL server. The stored procedure has an ALTER PROCEDURE block at the start containing this line:

     @EffDate   DateType   = NULL,

I'm trying to call it from this script (python2.7 on Win7x64, lightly anonymized):

# coding: utf-8
import pypyodbc, datetime

start_item = 'part'
startdate = datetime.datetime(1950, 1, 1)
query = "{CALL APP_DB.dbo.CostRollBoMStoredProcedure(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}"
values = (start_item,'1','9999','0','0','M,T,F,O','P,M,T','2','C','1950-01-01','1','0','0','0','0','0')

conn  = pypyodbc.connect('DRIVER={SQL Server Native Client 10.0};SERVER=server;UID=user;PWD=pass;APP=Microsoft® Windows® Operating System;WSID=mypc;DATABASE=APP_DB')
cur = conn.cursor() 

cur.execute(query, (values))

This throws the follwing error:

pypyodbc.ProgrammingError: (u'42000', u'[42000] [Microsoft]
[SQL Server Native Client 10.0][SQL Server]Error converting data type varchar to datetime.')

I have tried various formats for the date parameter as a string, or using the startdate variable from line 5.

I've also changed the driver per https://code.google.com/archive/p/pyodbc/issues/204

How do I figure out what format that DateType parameter expects, and send it appropriately in the python script?

eigenmeister
  • 1
  • 1
  • Are you **sure** is says `DateType` and not `Date` or `DateTime`? -- Try it without the `-` dashes. `'19500101'` – SqlZim Feb 16 '17 at 21:49
  • I'm certain - it might be a custom type. Some nearby parameters are NVARCHAR(10), which is probably built in, and ProductCodeType, which is likely not. Where would I find out the definition of a custom type? – eigenmeister Feb 16 '17 at 22:15
  • `select * from sys.types where is_user_defined = 1` – SqlZim Feb 16 '17 at 22:57

0 Answers0