SqlDbx
You are not logged in.
Sometimes, when scripting a create or alter view, procedure, or function, the script is not identical to original stored in sql server.
This results in lost indentation or syntax errors when executing the create or alter.
This only seems to happen with longer scripts where consecutive spaces exist on a 4K or 8K boundary.
The cause is that sqldbx is using sp_helptext to retrieve the source text.
sp_helptext should not be used for scripting (moreover, sp_helptext fails miserably in tempdb), and neither should INFORMATION_SCHEMA (these views return only 4000 characters).
Instead, object_definition(), sys.sql_modules, or syscomments should be used.
A script to reproduce the problem can be found here:
https://www.sugarsync.com/pf/D008008_74_9513353723
Offline
I am not able to reproduce this issue. What version of SQL Server you use?
I tried from 2000 to 2008 and results are consistent.
Offline
Perhaps you changed the testproc name? Every byte counts and the problem "moves around"...
Reproduced w/ 2005 sp2 (x86), 2008 sp1 (x86), 2008 R2 (x86), 2012 (x64)
Screencap (200K) here: https://www.sugarsync.com/pf/D008008_79591396_04584
Offline
Ok. I see the issue now. Thank you for pointing it out.
Offline
BIG BUG Lines disappear
Version 3.63
@str_rcodiversamo nvarchar(30), -- Regroupement comptable divers pour les organismes AMO
@str_comptedebitamc nvarchar(1), -- Type de compte AMC débiteur
-- D : Compte pahar(30) = NULL,
-- Journal
@dec_id_cjrn BIGINT = NULL,
@bln_MajFacture BIT = 0
Version 3.61
@str_rcodiversamo nvarchar(30), -- Regroupement comptable divers pour les organismes AMO
@str_comptedebitamc nvarchar(1), -- Type de compte AMC débiteur
-- D : Compte par dossier
-- P : Compte par patient
-- M : Compte par mois
-- O : Compte par organisme
-- R : Compte par regroupement d'organisme
-- U : Compte unique
@str_compteamc nvarchar(30), -- Compte à utiliser pour le débit AMC
-- peut-être à null si @str_comptedebitamc = D ou P ou O ou R
@str_compteracineamc nvarchar(30), -- Compte racine en cas d'organisme AMC
-- sert si @str_comptedebitamc = O
@str_rcodiversamc nvarchar(30), -- Regroupement comptable divers pour les organismes AMC
@str_produitdivers nvarchar(30), -- Compte de produit divers
@str_debiteurdivers nvarchar(30), -- Compte débiteur divers
@str_tvadivers nvarchar(30), -- Compte tva divers
@str_idtva_cgre bigint, -- Grille de TVA
@dec_idrco_cgre bigint, -- Grille des comptes associés aux codes de regroupement des organismes
@str_debitnegatif nvarchar(1), -- Sens de l'écriture si débit négatif
@str_creditnegatif nvarchar(1), -- Sens de l'écriture si credit négatif
@str_axeanalytique1 nvarchar(10), -- axe analytique 1
-- E : Etablissement ; C : CMD ; G : GHS ; U : Unité fonctionnelle
@str_axeanalytique2 nvarchar(10), -- axe analytique 2
-- E : Etablissement ; C : CMD ; G : GHS ; U : Unité fonctionnelle
@dec_id_user bigint, -- Uilisateur
@bln_encours bit, -- Ecriture des en cours
@dte_encours datetime, -- Date d'arrêté des en cours
-- Comptes collectifs
@str_collectifpatient nvarchar(30),
@str_collectifamo nvarchar(30),
@str_collectifamc nvarchar(30),
@bln_precloture bit, --Fonction est appelée lors de la phase de pré clôture les comptes de tiers inexistants sont alors créés
-- Ventes honoraires
@RecueilTypeLigne nvarchar(1) = '3', -- Par défaut Vente Prestation (3) sinon Honoraire (4)
@str_comptedebitmedecin nvarchar(1) = Null,
@str_comptemedecin nvarchar(30) = Null,
@str_compteracinemedecin nvarchar(30) = Null,
@str_collectifmedecin nvarchar(30) = NULL,
-- Journal
@dec_id_cjrn BIGINT = NULL,
@bln_MajFacture BIT = 0
Offline
What version of SQL Server?
Offline
for SQL Server 2000
in SQL Server 2008 R2
i have this :
@str_comptedebitamc nvarchar(1), -- Type de compte AMC débiteur
-- D : Compte pa
GO
Offline
I am not able to reproduce this issue in 2000 or 2008 R2.
Can you provide more information.
Offline
I think it's related to long function or procedure
in sql server 2008 R2
procedure in version 3.61
IF OBJECT_ID ('dbo.fil_fbo_age') IS NOT NULL
DROP FUNCTION dbo.fil_fbo_age
GO
CREATE FUNCTION fil_fbo_age
(
@Dte_DateBorn AS DATETIME,
@Dte_DateOrigin AS DATETIME,
@Str_Template AS NVARCHAR(30),
@Bit_Signifiant AS BIT
)
RETURNS NVARCHAR(60) AS
BEGIN
DECLARE @Int_ResYear AS INT
DECLARE @Int_ResMonth AS INT
DECLARE @Int_ResDay AS INT
DECLARE @Int_NbDayPreviousMonth AS INT
DECLARE @Str_FormatedReturn AS NVARCHAR(60)
DECLARE @Str_Year AS NVARCHAR(10)
DECLARE @Str_Month AS NVARCHAR(10)
DECLARE @Str_Day AS NVARCHAR(10)
DECLARE @Str_Baby AS NVARCHAR(10)
DECLARE @Int_NbDaysFebruary AS INT
DECLARE @Int_BornMonth AS INT
SET @Int_ResYear = DATEDIFF(YEAR, @Dte_DateBorn, @Dte_DateOrigin)
SET @Int_ResMonth = DATEDIFF(MONTH, @Dte_DateBorn, @Dte_DateOrigin)
SET @Int_ResDay = DATEDIFF(DAY, @Dte_DateBorn, @Dte_DateOrigin)
SET @Int_BornMonth = DATEPART(MONTH, @Dte_DateBorn)
IF
(
(
(@Int_BornMonth % 4 = 1) AND (@Int_BornMonth % 100 = 0)
) OR
(
(@Int_BornMonth % 400 = 1)
)
)
SET @Int_NbDaysFebruary = 29
ELSE
SET @Int_NbDaysFebruary = 28
SET @Int_NbDayPreviousMonth =
CASE
WHEN @Int_BornMonth = 1 THEN 31
WHEN @Int_BornMonth = 2 THEN @Int_NbDaysFebruary
WHEN @Int_BornMonth = 3 THEN 31
WHEN @Int_BornMonth = 4 THEN 30
WHEN @Int_BornMonth = 5 THEN 31
WHEN @Int_BornMonth = 6 THEN 30
WHEN @Int_BornMonth = 7 THEN 31
WHEN @Int_BornMonth = 8 THEN 31
WHEN @Int_BornMonth = 9 THEN 30
WHEN @Int_BornMonth = 10 THEN 31
WHEN @Int_BornMonth = 11 THEN 30
WHEN @Int_BornMonth = 12 THEN 31
END
IF
(
DATEPART(MONTH, @Dte_DateBorn) > DATEPART(MONTH, @Dte_DateOrigin) OR
(
DATEPART(MONTH, @Dte_DateBorn) = DATEPART(MONTH, @Dte_DateOrigin) AND
DATEPART(DAY, @Dte_DateBorn) > DATEPART(DAY, @Dte_DateOrigin)
)
)
SET @Int_ResYear = @Int_ResYear - 1
IF DATEPART(MONTH, @Dte_DateBorn) <= DATEPART(MONTH, @Dte_DateOrigin)
SET @Int_ResMonth = DATEPART(MONTH, @Dte_DateOrigin) - DATEPART(MONTH, @Dte_DateBorn)
ELSE
BEGIN
SET @Int_ResMonth = (DATEPART(MONTH, @Dte_DateOrigin) +12) - DATEPART(MONTH, @Dte_DateBorn)
IF DATEPART(DAY, @Dte_DateBorn) > DATEPART(DAY, @Dte_DateOrigin)
SET @Int_ResMonth = @Int_ResMonth - 1
END
IF DATEPART(DAY, @Dte_DateBorn) <= DATEPART(DAY, @Dte_DateOrigin)
SET @Int_ResDay = DATEPART(DAY, @Dte_DateOrigin) - DATEPART(DAY, @Dte_DateBorn)
ELSE
SET @Int_ResDay = ( @Int_NbDayPreviousMonth - DATEPART(DAY, @Dte_DateBorn)) + DATEPART(DAY, @Dte_DateOrigin)
SET @Str_Year =
CASE
WHEN @Int_ResYear <= 1 THEN 'an'
ELSE 'ans'
END
SET @Str_Month = 'mois'
SET @Str_Day =
CASE
WHEN @Int_ResDay <= 1 THEN 'jour'
ELSE 'jours'
END
IF @Bit_Signifiant = 1
BEGIN
SET @Str_Year = CONVERT(NVARCHAR, @Int_ResYear) + ' ' + @Str_Year + ' '
SET @Str_Month = CONVERT(NVARCHAR, @Int_ResMonth) + ' ' + @Str_Month + ' '
SET @Str_Day = CONVERT(NVARCHAR, @Int_ResDay) + ' ' + @Str_Day
SET @Str_Baby = ''
END
ELSE
BEGIN
IF @Int_ResYear = 0 SET @Str_Year = '' ELSE SET @Str_Year = CONVERT(NVARCHAR, @Int_ResYear) + ' ' + @Str_Year + ' '
IF @Int_ResMonth = 0 SET @Str_Month = '' ELSE SET @Str_Month = CONVERT(NVARCHAR, @Int_ResMonth) + ' ' + @Str_Month + ' '
IF @Int_ResDay = 0 SET @Str_Day = '' ELSE SET @Str_Day = CONVERT(NVARCHAR, @Int_ResYear) + ' ' + @Str_Day
IF (@Int_ResYear = 0 AND @Int_ResMonth = 0 AND @Int_ResDay = 0) SET @Str_Baby = 'Nouveau né' ELSE SET @Str_Baby = ''
END
SET @Str_FormatedReturn =
CASE
WHEN @Str_Template = 'AAAA ans MM mois' THEN CONVERT(NVARCHAR, @Str_Year + @Str_Month + @Str_Baby)
WHEN @Str_Template = 'AAAA ans MM mois JJ jours' THEN CONVERT(NVARCHAR, @Str_Year + @Str_Month + @Str_Day + @str_Baby)
WHEN @Str_Template = 'AAAA' THEN CONVERT(NVARCHAR,@Int_ResYear)
ELSE 'FORMAT NON FOURNI'
END
RETURN @Str_FormatedReturn
END
GO
in version 3.63
IF OBJECT_ID ('dbo.fil_fbo_age') IS NOT NULL
DROP FUNCTION dbo.fil_fbo_age
GO
CREATE FUNCTION fil_fbo_age
(
@Dte_DateBorn AS DATETIME,
@Dte_DateOrigin AS DATETIME,
@Str_Template AS NVARCHAR(30),
@Bit_Signifiant AS BIT
)
RETURNS NVARCHAR(60) AS
BEGIN
DECLARE @Int_ResYear AS INT
DECLARE @Int_ResMonth AS INT
DECLARE @Int_ResDay AS INT
DECLARE @Int_NbDayPreviousMonth AS INT
DECLARE @Str_FormatedReturn AS NVARCHAR(60)
DECLARE @Str_Year AS NVARCHAR(10)
DECLARE @Str_Month AS NVARCHAR(10)
DECLARE @Str_Day AS NVARCHAR(10)
DECLARE @Str_Baby AS NVARCHAR(10)
DECLARE @Int_NbDaysFebruary AS INT
DECLARE @Int_BornMonth AS INT
SET @Int_ResYear = DATEDIFF(YEAR, @Dte_DateBorn, @Dte_DateOrigin)
SET @Int_ResMonth = DATEDIFF(MONTH, @Dte_DateBorn, @Dte_DateOrigin)
SET @Int_ResDay = DATEDIFF(DAY, @Dte_DateBorn, @Dte_DateOrigin)
SET @Int_BornMonth = DATEPART(MONTH, @Dte_DateBorn)
IF
(
(
(@Int_BornMonth % 4 = 1) AND (@Int_BornMonth % 100 = 0)
) OR
(
(@Int_BornMonth % 400 = 1)
)
)
SET @Int_NbDaysFebruary = 29
ELSE
SET @Int_NbDaysFebruary = 28
SET @Int_NbDayPreviousMonth =
CASE
WHEN @Int_BornMonth = 1 THEN 31
WHEN @Int_BornMonth = 2 THEN @Int_NbDaysFebruary
WHEN @Int_BornMonth = 3 THEN 31
WHEN @Int_BornMonth = 4 THEN 30
WHEN @Int_BornMonth = 5 THEN 31
WHEN @Int_BornMonth = 6 THEN 30
WHEN @Int_BornMonth = 7 THEN 31
WHEN @Int_BornMonth = 8 THEN 31
WHEN @Int_BornMonth = 9 THEN 30
WHEN @Int_BornMonth = 10 THEN 31
WHEN @Int_BornMonth = 11 THEN 30
WHEN @Int_BornMonth = 12 THEN 31
END
IF
(
DATEPART(MONTH, @Dte_DateBorn) > DATEPART(MONTH, @Dte_DateOrigin) OR
(
DATEPART(MONTH, @Dte_DateBorn) = DATEPART(MONTH, @Dte_DateOrigin) AND
DATEPART(DAY, @Dte_DateBorn) > DATEPART(DAY, @Dte_DateOrigin)
)
)
SET @Int_ResYear = @Int_ResYear - 1
IF DATEPART(MONTH, @Dte_DateBorn) <= DATEPART(MONTH, @Dte_DateOrigin)
SET @Int_ResMonth = DATEPART(MONTH, @Dte_DateOrigin) - DATEPART(MONTH, @Dte_DateBorn)
ELSE
BEGIN
SET @Int_ResMonth = (DATEPART(MO
GO
Offline
This is the query SqlDbx uses to retrieve source:
SELECT definition
FROM sys.all_sql_modules WHERE object_id = object_id ('fil_fbo_age')
Does it returns everything?
Are you using Personal or Professional edition?
If Professional x86 or x64?
Offline
sqlDbx personal
in sqlDbx :
SELECT definition
FROM sys.all_sql_modules WHERE object_id = object_id ('fil_fbo_age')
return a NTEXT value and truncate the result
SELECT CAST(definition AS NVARCHAR(MAX))
FROM sys.all_sql_modules WHERE object_id = object_id ('fil_fbo_age')
return a NTEXT value and truncate the result
SELECT CAST(definition AS TEXT)
FROM sys.all_sql_modules WHERE object_id = object_id ('fil_fbo_age')
return a TEXT value with no truncate
Offline
OK. I think I know what is going on.
Looks like you have old SQL Server driver installed on your machine.
I was able to reproduce your issue using SQL Server 2000 driver to connect to SQL Server 2008.
Offline
In option : SQL Server Native Client 10.0, SQL Native Client, SQL Server
I think SqlDbx should prohibit loading the database if the driver is SQL Server because it is no longer compatible.
Offline
and also SQL Native Client
Offline