[SQL] Directe INSERT INTO werkt, maar via Stored Proced niet

[Equator]

Ik ben bezig met een script wat gegevens uit een textfile leest, en deze via een ADO connectie in een MS SQL 2000 database propt.

Wanneer ik via

SQL:

INSERT INTO <tablename> (Kolomnaam1,Kolomnaam2) VALUES ('Waarde1','waarde 2')

deze gegevens invoer gaat dit prima.

Echter wanneer ik deze via een stored procedure wil invoeren (Die in weze het zelfde doet) dan krijg ik dat niet voor elkaar.

De foutmelding waar het systeem mee blijft komen is:

Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type varchar to datetime.

Nu begrijp ik dat als ik in de database heb opgegeven dat kolom1 een integer moet zijn dat je daar dan geen datum in kwijt kan.

Echter:
Wanneer ik kijk naar mijn database design, dan is
kolom1: Naam: Datum Type: datetime Length: 8
kolom2: Naam: Tijd Type: datetime length: 8

Stored Procedure:

SQL:

CREATE PROCEDURE spo_PROCNAAM
@Datum datetime,
@Tijd datetime

AS

INSERT INTO tablename (
        Datum,
        Tijd,
        )
    VALUES (
        @Datum,
        @Tijd,
        )
GO

offtopic:
Ik moet deze waardes invoeren met waardes uit de textfile en niet de actuele tijd en datum. Dit omdat er vertraging zit in het maken van de textfiles en het invoeren in de database.

Doe ik het nu echt verkeerd??
Ik heb op w3schools.com de SQL course nog een keer gevolgd, maar daar werd niet over een datum veld gesproken.

Nogmaals: wanneer ik in deze tabel handmatig een waarde invoer, gaat alles wel goed.(krijg ik geen foutmelding over het converteren van varchar naar datetime)

[P_de_B]

Hij kan de DATUM in @Datum niet converteren naar een datetime formaat. Dit heeft te maken met het datumformaat (dd/mm/jjjj of mm/dd/jjjj)

Je zou even kunnen kijken naar de SET DATEFORMAT optie. Een andere optie is zelf de datum in het formaat JJJJ-MM-DD aanbieden, dit kan altijd goed geconverteerd worden.

[r0bert]

Als ik de foutmelding lees en "Nu begrijp ik dat als ik in de database heb opgegeven dat kolom1 een integer moet zijn dat je daar dan geen datum in kwijt kan." dit, lijkt het me dat je het verkeerd om ziet of typt Je probeert een integer (dus geen vereist datumformaat/notatie) in een datum veld te plaatsen, wat niet wil.

edit: zie bovenbuurman

[ Voor 6% gewijzigd door r0bert op 07-10-2004 14:59 ]

[Equator]

De datum zoals hij in het veld zou worden geplaatst is van het volgende formaat:
07-10-2004
De tijd als: 10:23:12

handmatig lukt dit prima, en via SP niet.

ik zorg in het script dat de textfiles vult al voor de jusite weergave/notatie.

@r0bert
Ik meldde dit alleen om op te merken dat ik weet dat je het juiste type moet aanhouden.
Ik gebruik in kolom1 en in kolom2 GEEN integer

[ Voor 18% gewijzigd door Equator op 07-10-2004 15:08 ]

[Verwijderd]

CyberJ schreef op 07 oktober 2004 @ 15:06:
De datum zoals hij in het veld zou worden geplaatst is van het volgende formaat:
07-10-2004

handmatig lukt dit prima, en via SP niet.

Ik meldde dit alleen om op te merken dat ik weet dat je het juiste type moet aanhouden.
Ik gebruik in kolom1 en in kolom2 GEEN integer

Hoe geef je die data aan de Stored Procedure?

Als je ze zonder ' erom heen geeft, wordt het een int natuurlijk:
7 - 10 - 2004 = -2007

[gorgi_19]

Kijk anders eens in profiler hoe het aangeroepen wordt Waarschijnlijk definieer je het verkeerd in je code

[Equator]

Verwijderd schreef op 07 oktober 2004 @ 15:08:
[...]


Hoe geef je die data aan de Stored Procedure?

Als je ze zonder ' erom heen geeft, wordt het een int natuurlijk:
7 - 10 - 2004 = -2007

Ik declareer in de SP @datum datetime.
Dan zal die SP de waarde op die plek toch niet als integer gaan gebruiken. Dan kan je die SP net zo goed niet gebruiken..

EDIT: @Gordi: goed idee.. Dom dom dat ik daar niet aan denk..

[ Voor 8% gewijzigd door Equator op 07-10-2004 15:10 ]

[Verwijderd]

CyberJ schreef op 07 oktober 2004 @ 15:10:
[...]


Ik declareer in de SP @datum datetime.
Dan zal die SP de waarde op die plek toch niet als integer gaan gebruiken. Dan kan je die SP net zo goed niet gebruiken..

EDIT: @Gordi: goed idee.. Dom dom dat ik daar niet aan denk..

Je roept die SP toch ergens aan. Hoe roep je die aan? Als je het daar verkeerd doet, krijgt je SP een verkeerde waarde.

Edit: Wat gorgi ook zei, was dus wat ik bedoelde.

[ Voor 6% gewijzigd door Verwijderd op 07-10-2004 15:12 ]

[Equator]

Verwijderd schreef op 07 oktober 2004 @ 15:11:
[...]


Je roept die SP toch ergens aan. Hoe roep je die aan? Als je het daar verkeerd doet, krijgt je SP een verkeerde waarde.

Edit: Wat gorgi ook zei, was dus wat ik bedoelde.

Resultaat profiler:

code:

batchCompleted: Execute spo_procnaam '30-09-2004','00:00:01','63','62','65','','44','100','','','','0','1'

VBScript:

strSQL = "EXECUTE spo_procnaam '30-09-2004','00:00:01','63','62','65','','44','100','','','','0','1'"
Cnn.ConnectionString = "driver={SQL Server};server=NEPTUNUS;database=PerfMon;UID=***;PWD=***"
Cnn.Open
Cnn.Execute (strSQL)

strSQL is verder een lineread van een textfile. en kan dus alles bevatten.
vanuit query analyser

code:

EXECUTE spo_procnaam '01-09-2004','00:00:01','63','62','65','','44','100','','','','0','1'

Resultaat: 1 row(s) affected.. (onder zelfde gebruikersnaam)
ZOu het dan toch in het script zitten..

[ Voor 85% gewijzigd door Equator op 07-10-2004 15:24 ]

[Verwijderd]

CyberJ schreef op 07 oktober 2004 @ 15:18:
Resultaat profiler:

code:

batchCompleted: Execute spo_procnaam '30-09-2004','00:00:01','63','62','65','','44','100','','','','0','1'

Wat is die '00:00:01'? Gaat het daarbij niet mis?

Overigens is het vaak aan te raden datums in het 'yyyy-mm-dd' formaat de database in te gooien,

[The Eagle]

Hadden ze daar in SQL niet de functies TO_CHAR en TO_DATE voor uitgevonden

[Equator]

Die 00:00:01 is de tijd van entry in het text bestand: 1 seconde over middernacht.

[P_de_B]

Heb je nu SET DATEFORMAT DMY al geprobeerd?

30-09-2004 kan niet automagisch omgezet worden naar een datatime omdat 30 geen maand is volgens SQL Server.

[Equator]

Yep.. Via Google kwam ik erachter dat deze functie in runtime moest worden gebruikt, dus heb ik hem zowel aan het einde van de stored procedure gezet (vlak voor GO) en een keer voor het inserten in de SP.

Beide hadden geen gevolg.
Ik snap overigens wel wat je bedoeld, maar als ik de stored procedure handmatig aanroep in de query analyser, en ik geef de zelfde data op. Dan werkt hij wel.

Ik ga nog maar eens wat debugcode in het script plaatsen. zodat ik zeker weet dat de waarde's goed zijn.
als iemand nog een idee heeft.

code:

EXEC spo_FUTUREIT_NEPTUNUS '30-09-2004','00:00:01','63','62','65','','44','100','','','','0','1'
EXEC spo_FUTUREIT_NEPTUNUS '30-09-2004','00:10:01','63','62','65','','44','13','','','','0','1'
EXEC spo_FUTUREIT_NEPTUNUS '30-09-2004','00:20:01','63','62','65','','44','2','','','','0','1'

Dit is in weze de string die aan mijn Cnn.Execute() wordt meegegeven.
spo_FUTUREIT_NEPTUNUS is de naam van de klant_server (In dit geval wij zelf)
In weze komt er voor elke klant_server combinatie een aparte spo.

EDIT: Ik heb, om zeker te zijn dat ik een date format wegstuur, een CDate(strDatum) meegegeven. Helaas is dat ook niet de oplossing.

Hmm, net even ingelogd met query analyser, en de sp handmatig afgetrapt.
Krijg ik dezelfde melding.
Dus ik ervoor het commando:
set dateformat DMY gegeven, en verrek.. nu slikt hij het.

Echter de output van een select *:

code:

2004-10-07 00:00:00.000 1899-12-30 14:30:20.000 123 NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
2004-01-09 00:00:00.000 1900-01-01 00:00:01.000 63  62  65  0   44  100 0   0   0   0   1
2004-01-09 00:00:00.000 1900-01-01 00:00:01.000 63  62  65  0   44  100 0   0   0   0   1
2004-08-31 00:00:00.000 1900-01-01 17:20:01.000 65  41  73  0   43  0   0   0   0   0   1

Datetime is echt een datum EN een tijdveld. (Duhh..)

Misschien is het slimmer om deze velden bij elkaar te trekken.
Dus de eerste kolom wordt dan:
datetime met Datum & Tijd..

Is dit slimmer

[ Voor 101% gewijzigd door Equator op 08-10-2004 08:17 ]

[P_de_B]

Ik zie nu pas dat je 2 verschillende velden heb voor datum en tijd? Wat is de reden hiervan? SQL Server zal altijd een datum en een tijd opslaan in een datetime veld. Ik denk dat 00:00:01 niet goed naar een geldige datum tijd gacast kan worden.

probeer eens

code:

EXEC spo_FUTUREIT_NEPTUNUS '2004-09-30','1900-01-01 00:00:01','63','62','65'[..]

Die 1900-01-01 lijkt een beetje raar, maar die zal sowieso aan het veld toegevoegd worden. Kun je niet beter 1 veld maken waarin je de datum en de tijd opslaat?

[Equator]

Zie mijn edit boven je post..

De reden dat ik die apart heb gehouden.. Tja, ergens in de kronkels van mijn hersenen heb ik een keer gedacht dat dat makkelijker zou zijn.. Maar nu begin ik te twijfelelen..

[Equator]

Toch maar de eerste 2 kolommen bijelkaar getrokken.
Naam: Tijd
type: datetime

code die ik ingeef in de queryanalyser:

code:

EXEC spo_FUTUREIT_NEPTUNUS '31-8-2004 23:20:01','65','41','73','','43','0','','','','0','1'
EXEC spo_FUTUREIT_NEPTUNUS '31-8-2004 23:30:01','65','41','73','','43','100','','','','0','1'
EXEC spo_FUTUREIT_NEPTUNUS '31-8-2004 23:40:01','65','41','73','','43','18','','','','0','1'
EXEC spo_FUTUREIT_NEPTUNUS '31-8-2004 23:50:01','65','41','73','','43','5','','','','0','1'

Werkt als een tierelier

select *

code:

2004-08-31 23:20:01.000 65  41  73  0   43  0   0   0   0   0   1
2004-08-31 23:30:01.000 65  41  73  0   43  100 0   0   0   0   1
2004-08-31 23:40:01.000 65  41  73  0   43  18  0   0   0   0   1
2004-08-31 23:50:01.000 65  41  73  0   43  5   0   0   0   0   1

Nu vanuit het script..
En de 100 zeiden.. UhUh Niet dus..

Microsoft OLE DB Provider for ODBC Drivers: [Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type varchar to datetime.

[ Voor 43% gewijzigd door Equator op 08-10-2004 08:32 ]

[P_de_B]

Vanuit het script werk je in een andere connectie, de scope van deze connectie vewacht het datum in het formaat MM-DD-JJJJ waarschijnlijk.

Wat je het beste kunt doen is vanuit het script JJJJ-DD-MM UU:mm:ss doorgeven, dit word altijd goed herkend.

[Equator]

Als ik kijk naar de code die ik hierboven gepost heb:

code:

EXEC spo_FUTUREIT_NEPTUNUS '31-8-2004 23:50:01','65','41','73','','43','5','','','','0','1'

Dit is de string die door mijn script wordt gemaakt.

code:

2004-08-31 23:20:01.000    65    41    73    0    43    0    0    0    0    0    1

Is de output van SQL op een select *
Ik zie verschil. SQL heeft er keurig zijn eigen format op losgelaten.
Waarom werkt dat dan niet als ik dezelfde code met een script probeer te executen.

Ik heb overigens wel geprobeerd wat je zei, maar het gaf dezelfde foutmelding..
Cannot convert type varchar to datetime

[ Voor 25% gewijzigd door Equator op 08-10-2004 08:42 ]

[P_de_B]

CyberJ schreef op 08 oktober 2004 @ 08:42:
Als ik kijk naar de code die ik hierboven gepost heb:

code:

EXEC spo_FUTUREIT_NEPTUNUS '31-8-2004 23:50:01','65','41','73','','43','5','','','','0','1'

Dit is de string die door mijn script wordt gemaakt.

code:

2004-08-31 23:20:01.000    65    41    73    0    43    0    0    0    0    0    1

Is de output van SQL op een select *
Ik zie verschil. SQL heeft er keurig zijn eigen format op losgelaten.
Waarom werkt dat dan niet als ik dezelfde code met een script probeer te executen.

Ik heb overigens wel geprobeerd wat je zei, maar het gaf dezelfde foutmelding..
Cannot convert type varchar to datetime

Als je vanuit je script het volgende doet krijg je een foutmelding??

code:

EXEC spo_FUTUREIT_NEPTUNUS '2004-08-31 23:50:01','65','41','73','','43','5','','','','0','1'

let goed op het formaat!

Wat als je

code:

EXEC spo_FUTUREIT_NEPTUNUS '2004-08-31','65','41','73','','43','5','','','','0','1'

doet? (Zonder de tijd)

[ Voor 22% gewijzigd door P_de_B op 08-10-2004 08:46 ]

[Equator]

Thanks P_de_B
Ik heb het uiteindelijk opgelost met SET DATEFORMAT dmy

heb je dit commando 1 x gegeven in een sessie, dan hoef je dit niet mer te doen.

Wat ik nu heb gedaan is het volgende:
In mijn VB functie die op aanvraag de connectie opend of sluit heb ik bij het openen het commando: Set dateformat dmy opgegeven.
Daarna gaan alle insert's via de SP prima.

Nogmaals: