Problem scheduling job

I have a script that I wrote (please pardon my less than beautiful code) to
dump some aggregate data into a table for reporting purposes. It populates
a
few temp tables, then inserts into the destination table.
This script works fine in QA, but when I try to run it from a SA Job it
fails with the following error:
Line 12: Incorrect syntax near '20050128'. [SQLSTATE 42000] (Error 170).
NOTE: The step was retried the requested number of times (1) without
succeeding. The step failed.
Here's my script:
--begin script
USE NGEPMProd
GO
SELECT TTM.desc_30 AS TaskType, TU.user_id AS Owner, TU.createdate,T.status
INTO #NotStarted
FROM tasks T
INNER JOIN task_type_mstr TTM ON
TTM.task_type_id = T.task_type_id
INNER JOIN task_users_assigned_to TU ON
TU.task_id = T.task_id
WHERE t.status = 1
ORDER BY TU.user_id ASC
SELECT Owner, COUNT(OWNER) AS NumberNotStarted, CONVERT(char(10), GETDATE(),
101)AS [DATE]
INTO #NSTotals
FROM #NotStarted
GROUP BY owner
ORDER BY OWNER ASC
----
--
USE NGEPMProd
GO
SELECT TTM.desc_30 AS TaskType, TU.user_id AS Owner, TU.createdate,T.status
INTO #InProgress
FROM tasks T
INNER JOIN task_type_mstr TTM ON
TTM.task_type_id = T.task_type_id
INNER JOIN task_users_assigned_to TU ON
TU.task_id = T.task_id
WHERE t.status = 3
ORDER BY TU.user_id ASC
SELECT Owner, COUNT(OWNER) AS NumberInProgress,CONVERT(char(10), GETDATE(),
101)AS [DATE]
INTO #ProgTotals
FROM #InProgress
GROUP BY owner
ORDER BY OWNER ASC
----
-
USE NGEPMProd
GO
SELECT TTM.desc_30 AS TaskType, TU.user_id AS Owner, TU.createdate,T.status,
CONVERT(char(10), T.followup_date, 101)AS FUDate
INTO #Overdue
FROM tasks T
INNER JOIN task_type_mstr TTM ON
TTM.task_type_id = T.task_type_id
INNER JOIN task_users_assigned_to TU ON
TU.task_id = T.task_id
WHERE T.status = '3'
ORDER BY TU.user_id ASC
SELECT Owner, COUNT(OWNER) AS NumberOverdue, CONVERT(char(10), GETDATE(),
101)AS [DATE]
INTO #ODTotals
FROM #Overdue
WHERE FUDate < GETDATE()
GROUP BY owner
ORDER BY OWNER ASC
----
-
USE NGEPMProd
GO
SELECT TTM.desc_30 AS TaskType, TU.user_id AS Owner, TU.createdate,T.status,
CONVERT(char(10), T.completion_date, 101) AS CompletedDate
INTO #Completed
FROM tasks T
INNER JOIN task_type_mstr TTM ON
TTM.task_type_id = T.task_type_id
INNER JOIN task_users_assigned_to TU ON
TU.task_id = T.task_id
WHERE t.status = 2
--att tasks completed in the last 24 hours
AND T.completion_date >= DATEADD(hh, -24,GETDATE())-- or enter a specific
date like this '20050127'
ORDER BY TU.user_id ASC
SELECT Owner, COUNT(OWNER) AS NumberCompleted, CONVERT(char(10), GETDATE(),
101)AS [DATE]
INTO #CompTotals
FROM #Completed
GROUP BY owner
ORDER BY OWNER ASC
----
---
--insert records into permanent table for reports
INSERT INTO task_totals_collector
SELECT UM.user_id AS Employee, NS.NumberNotStarted AS NotStarted,
PT.NumberInProgress AS InProgress,CT.NumberCompleted AS Completed,
CONVERT(char(10), GETDATE(), 101)AS CalcDate, OT.NumberOverdue AS Overdue
FROM user_mstr UM
LEFT JOIN #NSTotals NS
ON UM.user_id=NS.owner
LEFT JOIN #ProgTotals PT ON
UM.user_id=PT.owner
LEFT JOIN #CompTotals CT ON
UM.user_id=CT.owner
LEFT JOIN #ODTotals OT ON
UM.user_id=OT.owner
ORDER BY UM.user_id ASC
--end of script
Patrick Rouse
Microsoft MVP - Terminal Server
http://www.workthin.com"Patrick Rouse" <PatrickRouse@.discussions.microsoft.com> wrote in message
news:F723A708-5FF4-4E8A-9F41-38A49BB08BE4@.microsoft.com...
> --att tasks completed in the last 24 hours
> AND T.completion_date >= DATEADD(hh, -24,GETDATE())-- or enter a specific
> date like this '20050127'
Patrick,
The only thing I can see that might be causing that error is this part; I'm
not sure if this is just wrapped because of the news client, though... You
should double-check that to make sure. I can't find the string '20050128'
in your code anywhere... Do you have some dynamic SQL that's not posted?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||No, I think it's getting this from GETDATE(). The database I'm working with
has a lot of dates stored as CHAR(8), which drives me nuts, as I can ORDER B
Y
on these columns. In my query I CONVERT them to DATETIME, but they're still
formatted as 'YYYYMMDD'.
"Adam Machanic" wrote:

> "Patrick Rouse" <PatrickRouse@.discussions.microsoft.com> wrote in message
> news:F723A708-5FF4-4E8A-9F41-38A49BB08BE4@.microsoft.com...
> Patrick,
> The only thing I can see that might be causing that error is this part; I'
m
> not sure if this is just wrapped because of the news client, though... You
> should double-check that to make sure. I can't find the string '20050128'
> in your code anywhere... Do you have some dynamic SQL that's not posted?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>|||One thing I can suggest is to put brackets ( [ ] ) around all your table and
column names as you have a lot of reserved words as object names.
Andrew J. Kelly SQL MVP
"Patrick Rouse" <PatrickRouse@.discussions.microsoft.com> wrote in message
news:F723A708-5FF4-4E8A-9F41-38A49BB08BE4@.microsoft.com...
>I have a script that I wrote (please pardon my less than beautiful code) to
> dump some aggregate data into a table for reporting purposes. It
> populates a
> few temp tables, then inserts into the destination table.
> This script works fine in QA, but when I try to run it from a SA Job it
> fails with the following error:
> Line 12: Incorrect syntax near '20050128'. [SQLSTATE 42000] (Error 170).
> NOTE: The step was retried the requested number of times (1) without
> succeeding. The step failed.
>
> Here's my script:
> --begin script
> USE NGEPMProd
> GO
> SELECT TTM.desc_30 AS TaskType, TU.user_id AS Owner,
> TU.createdate,T.status
> INTO #NotStarted
> FROM tasks T
> INNER JOIN task_type_mstr TTM ON
> TTM.task_type_id = T.task_type_id
> INNER JOIN task_users_assigned_to TU ON
> TU.task_id = T.task_id
> WHERE t.status = 1
> ORDER BY TU.user_id ASC
> SELECT Owner, COUNT(OWNER) AS NumberNotStarted, CONVERT(char(10),
> GETDATE(),
> 101)AS [DATE]
> INTO #NSTotals
> FROM #NotStarted
> GROUP BY owner
> ORDER BY OWNER ASC
> ----
--
> USE NGEPMProd
> GO
> SELECT TTM.desc_30 AS TaskType, TU.user_id AS Owner,
> TU.createdate,T.status
> INTO #InProgress
> FROM tasks T
> INNER JOIN task_type_mstr TTM ON
> TTM.task_type_id = T.task_type_id
> INNER JOIN task_users_assigned_to TU ON
> TU.task_id = T.task_id
> WHERE t.status = 3
> ORDER BY TU.user_id ASC
> SELECT Owner, COUNT(OWNER) AS NumberInProgress,CONVERT(char(10),
> GETDATE(),
> 101)AS [DATE]
> INTO #ProgTotals
> FROM #InProgress
> GROUP BY owner
> ORDER BY OWNER ASC
> ----
--
> USE NGEPMProd
> GO
> SELECT TTM.desc_30 AS TaskType, TU.user_id AS Owner,
> TU.createdate,T.status,
> CONVERT(char(10), T.followup_date, 101)AS FUDate
> INTO #Overdue
> FROM tasks T
> INNER JOIN task_type_mstr TTM ON
> TTM.task_type_id = T.task_type_id
> INNER JOIN task_users_assigned_to TU ON
> TU.task_id = T.task_id
> WHERE T.status = '3'
> ORDER BY TU.user_id ASC
> SELECT Owner, COUNT(OWNER) AS NumberOverdue, CONVERT(char(10), GETDATE(),
> 101)AS [DATE]
> INTO #ODTotals
> FROM #Overdue
> WHERE FUDate < GETDATE()
> GROUP BY owner
> ORDER BY OWNER ASC
> ----
--
> USE NGEPMProd
> GO
> SELECT TTM.desc_30 AS TaskType, TU.user_id AS Owner,
> TU.createdate,T.status,
> CONVERT(char(10), T.completion_date, 101) AS CompletedDate
> INTO #Completed
> FROM tasks T
> INNER JOIN task_type_mstr TTM ON
> TTM.task_type_id = T.task_type_id
> INNER JOIN task_users_assigned_to TU ON
> TU.task_id = T.task_id
> WHERE t.status = 2
> --att tasks completed in the last 24 hours
> AND T.completion_date >= DATEADD(hh, -24,GETDATE())-- or enter a specific
> date like this '20050127'
> ORDER BY TU.user_id ASC
> SELECT Owner, COUNT(OWNER) AS NumberCompleted, CONVERT(char(10),
> GETDATE(),
> 101)AS [DATE]
> INTO #CompTotals
> FROM #Completed
> GROUP BY owner
> ORDER BY OWNER ASC
> ----
---
> --insert records into permanent table for reports
> INSERT INTO task_totals_collector
> SELECT UM.user_id AS Employee, NS.NumberNotStarted AS NotStarted,
> PT.NumberInProgress AS InProgress,CT.NumberCompleted AS Completed,
> CONVERT(char(10), GETDATE(), 101)AS CalcDate, OT.NumberOverdue AS Overdue
> FROM user_mstr UM
> LEFT JOIN #NSTotals NS
> ON UM.user_id=NS.owner
> LEFT JOIN #ProgTotals PT ON
> UM.user_id=PT.owner
> LEFT JOIN #CompTotals CT ON
> UM.user_id=CT.owner
> LEFT JOIN #ODTotals OT ON
> UM.user_id=OT.owner
> ORDER BY UM.user_id ASC
> --end of script
>
>
> Patrick Rouse
> Microsoft MVP - Terminal Server
> http://www.workthin.com|||I put brackets around the tables named user_id, and then ran just the first
section (up to the first --), which fails at the first GETDATE with
this error:
Incorrect syntax near '20050128'. [SQLSTATE 42000] (Error 170). The step
failed.
Like I said, it works fine in QA, just not in a job. :(
"Andrew J. Kelly" wrote:

> One thing I can suggest is to put brackets ( [ ] ) around all your table a
nd
> column names as you have a lot of reserved words as object names.
> --
> Andrew J. Kelly SQL MVP
>
> "Patrick Rouse" <PatrickRouse@.discussions.microsoft.com> wrote in message
> news:F723A708-5FF4-4E8A-9F41-38A49BB08BE4@.microsoft.com...
>
>|||Patrick,
I haven't followed the whole thread but do you actually have "GO" in your
SQL step. "GO" is a batch terminator used by QA and is not part of T-SQL.
So, you would want to take it out.
-oj
"Patrick Rouse" <PatrickRouse@.discussions.microsoft.com> wrote in message
news:5B11E86A-08E5-4027-926E-84AE2A3AFBEA@.microsoft.com...
>I put brackets around the tables named user_id, and then ran just the first
> section (up to the first --), which fails at the first GETDATE with
> this error:
> Incorrect syntax near '20050128'. [SQLSTATE 42000] (Error 170). The step
> failed.
> Like I said, it works fine in QA, just not in a job. :(
> "Andrew J. Kelly" wrote:
>|||Good to know, but I removed the USE DatabaseName & GO statements and still
get the same error.
"Patrick Rouse" wrote:
> No, I think it's getting this from GETDATE(). The database I'm working wi
th
> has a lot of dates stored as CHAR(8), which drives me nuts, as I can ORDER
BY
> on these columns. In my query I CONVERT them to DATETIME, but they're stil
l
> formatted as 'YYYYMMDD'.
> "Adam Machanic" wrote:
>|||First off as OJ mentions , do you have the GO's in the actual code? How you
know it is the GETDATE() that is failing or are you assuming because it is a
date type value in the error message? Here is the first block from your
original post:
<<<<<
--begin script
USE NGEPMProd
GO
SELECT TTM.desc_30 AS TaskType, TU.user_id AS Owner, TU.createdate,T.status
INTO #NotStarted
FROM tasks T
INNER JOIN task_type_mstr TTM ON
TTM.task_type_id = T.task_type_id
INNER JOIN task_users_assigned_to TU ON
TU.task_id = T.task_id
WHERE t.status = 1
ORDER BY TU.user_id ASC
SELECT Owner, COUNT(OWNER) AS NumberNotStarted, CONVERT(char(10), GETDATE(),
101)AS [DATE]
INTO #NSTotals
FROM #NotStarted
GROUP BY owner
ORDER BY OWNER ASC
I can't tell from this post but it looks like you don't have a space between
the end of the CONVERT and the AS. What happens if you set the date to a
variable and use the variable in the select instead?
Andrew J. Kelly SQL MVP
"Patrick Rouse" <PatrickRouse@.discussions.microsoft.com> wrote in message
news:5B11E86A-08E5-4027-926E-84AE2A3AFBEA@.microsoft.com...
>I put brackets around the tables named user_id, and then ran just the first
> section (up to the first --), which fails at the first GETDATE with
> this error:
> Incorrect syntax near '20050128'. [SQLSTATE 42000] (Error 170). The step
> failed.
> Like I said, it works fine in QA, just not in a job. :(
> "Andrew J. Kelly" wrote:
>|||Patrick,
Make it easier on us if you would script out the job and post it here. ;-)
(you can use EM to do that).
There's probably a silly syntax error.
-oj
"Patrick Rouse" <PatrickRouse@.discussions.microsoft.com> wrote in message
news:58F52F80-E014-4CF7-8868-9A919AA4EBEA@.microsoft.com...
> Good to know, but I removed the USE DatabaseName & GO statements and still
> get the same error.
>|||I assume it's the get date, because that returns the date listed in the erro
r
I posted, and the error states on "line 12" which is where GETDATE is
specified.
"Andrew J. Kelly" wrote:

> First off as OJ mentions , do you have the GO's in the actual code? How y
ou
> know it is the GETDATE() that is failing or are you assuming because it is
a
> date type value in the error message? Here is the first block from your
> original post:
> <<<<<
> --begin script
> USE NGEPMProd
> GO
> SELECT TTM.desc_30 AS TaskType, TU.user_id AS Owner, TU.createdate,T.statu
s
> INTO #NotStarted
> FROM tasks T
> INNER JOIN task_type_mstr TTM ON
> TTM.task_type_id = T.task_type_id
> INNER JOIN task_users_assigned_to TU ON
> TU.task_id = T.task_id
> WHERE t.status = 1
> ORDER BY TU.user_id ASC
> SELECT Owner, COUNT(OWNER) AS NumberNotStarted, CONVERT(char(10), GETDATE(
),
> 101)AS [DATE]
> INTO #NSTotals
> FROM #NotStarted
> GROUP BY owner
> ORDER BY OWNER ASC
> I can't tell from this post but it looks like you don't have a space betwe
en
> the end of the CONVERT and the AS. What happens if you set the date to a
> variable and use the variable in the select instead?
> --
> Andrew J. Kelly SQL MVP
>
> "Patrick Rouse" <PatrickRouse@.discussions.microsoft.com> wrote in message
> news:5B11E86A-08E5-4027-926E-84AE2A3AFBEA@.microsoft.com...
>
>sql

Problem running SSIS from C#

have one script that works fine. I am doing the exact same thging with this new script and it runs fine from C# on my desktop and runs fine from SS on the server but comes back with a failure when trying to run from C# on the server. Is there any additional info I can retrieve about the problem? All I am getting right now is "Failure" from the result field.

if (result == DTSExecResult.Failure)

{

Console.WriteLine("Task failed or abended");

log.Error("Problem with DTS Script");

log.Error(result);

}

else

I would first try looking at the error info in Package.Errors, especially the error description stored in each error object in the collection.|||package.errors did not really give me any useful information. Is there anything else I can look at or canm anybody think of any reason why the SSIS would not work from a C# program but works everyplace else? I have others that work and cannot find any difference between the two.|||If you have other packages that work from your C# program, then it's possible something in that particular package is causing a problem (e.g. missing dependency for a task). I would try to narrow this down by reducing that package is something simpler that works (e.g. by disabling or removing tasks), then start adding back functionality until you get an error.|||

Ted Lee - MSFT wrote:

If you have other packages that work from your C# program, then it's possible something in that particular package is causing a problem (e.g. missing dependency for a task). I would try to narrow this down by reducing that package is something simpler that works (e.g. by disabling or removing tasks), then start adding back functionality until you get an error.

Package runs from the C# on my desktop and runs from SQL Server from the server. It is just after moving the EXE to the server and running it on the server that the SSIS will come back as a "Failure" and bomb.

|||What is the actual error message reported from the package?sql

Problem running SSIS from C#

I am having a problem running a SSIS Script from within a C# program. Script was running fine and then one day it stopped running and is giving me the following errors and nothing has changed.

2007-07-18 14:27:52,098 [1] ERROR reporting.Processor [(null)] - Problem with DTS Script

2007-07-18 14:27:52,895 [1] ERROR reporting.Processor [(null)] - {Microsoft.SqlServer.Dts.Runtime.DtsError, Microsoft.SqlServer.Dts.Runtime.DtsError}

The SSIS runs fine from the process on my machine, from SQL Server on my machine and from SQL Server on the server. But when I run it from the C# executable I get the above errors. This process also runs other SSIS scripts and they all work fine. I am using the following code to execute the script. Can anybody give me some ideas on how to troubleshoot this problem.

Package package = app.LoadFromSqlServer("\\Maintenance Plans\\SCRA2", "ppntt240", "load_abc", "bcp123", null);

DTSExecResult result = package.Execute();

Variables vars = package.Variables;
int rowcount = Convert.ToInt32(vars["count"].Value);
String rowcount2 = "0000000000" + rowcount.ToString();

string fullcnt = rowcount2.ToString().Substring(rowcount2.Length - 10, 10);

if (result == DTSExecResult.Failure)
{
Console.WriteLine("Task failed or abended");
log.Error("Problem with DTS Script");
log.Error(package.Errors);

}
else
{
Console.WriteLine("Task ran successfully");

Can you enable logging in the package so that you can get more detail about the error?|||How do I do this?|||Right-click on the package in the designer, choose Logging, add a log provider (Text is simple to set up, but database is good too), and check the checkbox beside the package name. In the details tab, make sure you check OnError and OnWarning,|||Logging is not an option if I right click. If I select run I see logging and it ask for log provider e.g.(text file, event log or SQL Server) and Configuration String. Is this where I turn it on? What are the best options? Thanks.
|||If you open the package in BIDS, go to the control flow for the package, and right-click in an empty area (one not occupied by a task), you should see the logging option.|||Is there anything unique to the server that would cause the following errors?

PackageStart,PPNTT240,PNCNT\FF22882,SCRA2,{0D01AF9F-5C9C-4B54-942E-2B97FCF12826},{AF032472-7840-4C2B-99D6-692FA7A2AD22},7/19/2007 12:35:21 PM,7/19/2007 12:35:21 PM,0,0x,Beginning of package execution.

OnError,PPNTT240,PNCNT\FF22882,Data Flow Task Direct_Prod,{F36A5050-8FC5-4A2F-A457-42AB134844CF},{AF032472-7840-4C2B-99D6-692FA7A2AD22},7/19/2007 12:35:27 PM,7/19/2007 12:35:27 PM,-1071636471,0x,An OLE DB error has occurred. Error code: 0x80040E07.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E07 Description: "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.".

OnError,PPNTT240,PNCNT\FF22882,SCRA2,{0D01AF9F-5C9C-4B54-942E-2B97FCF12826},{AF032472-7840-4C2B-99D6-692FA7A2AD22},7/19/2007 12:35:27 PM,7/19/2007 12:35:27 PM,-1071636471,0x,An OLE DB error has occurred. Error code: 0x80040E07.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E07 Description: "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.".

OnError,PPNTT240,PNCNT\FF22882,Data Flow Task Direct_Prod,{F36A5050-8FC5-4A2F-A457-42AB134844CF},{AF032472-7840-4C2B-99D6-692FA7A2AD22},7/19/2007 12:35:27 PM,7/19/2007 12:35:27 PM,-1073450982,0x,component "OLE DB Source" (1) failed the pre-execute phase and returned error code 0xC0202009.

OnError,PPNTT240,PNCNT\FF22882,SCRA2,{0D01AF9F-5C9C-4B54-942E-2B97FCF12826},{AF032472-7840-4C2B-99D6-692FA7A2AD22},7/19/2007 12:35:27 PM,7/19/2007 12:35:27 PM,-1073450982,0x,component "OLE DB Source" (1) failed the pre-execute phase and returned error code 0xC0202009.

OnTaskFailed,PPNTT240,PNCNT\FF22882,Data Flow Task Direct_Prod,{F36A5050-8FC5-4A2F-A457-42AB134844CF},{AF032472-7840-4C2B-99D6-692FA7A2AD22},7/19/2007 12:35:27 PM,7/19/2007 12:35:27 PM,0,0x,(null)
OnWarning,PPNTT240,PNCNT\FF22882,SCRA2,{0D01AF9F-5C9C-4B54-942E-2B97FCF12826},{AF032472-7840-4C2B-99D6-692FA7A2AD22},7/19/2007 12:35:27 PM,7/19/2007 12:35:27 PM,-2147381246,0x,The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

PackageEnd,PPNTT240,PNCNT\FF22882,SCRA2,{0D01AF9F-5C9C-4B54-942E-2B97FCF12826},{AF032472-7840-4C2B-99D6-692FA7A2AD22},7/19/2007 12:35:27 PM,7/19/2007 12:35:27 PM,1,0x,End of package execution.

|||

agentf1 wrote:

Is there anything unique to the server that would cause the following errors?

OnError,PPNTT240,PNCNT\FF22882,Data Flow Task Direct_Prod,{F36A5050-8FC5-4A2F-A457-42AB134844CF},{AF032472-7840-4C2B-99D6-692FA7A2AD22},7/19/2007 12:35:27 PM,7/19/2007 12:35:27 PM,-1071636471,0x,An OLE DB error has occurred. Error code: 0x80040E07.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E07 Description: "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.".

OnError,PPNTT240,PNCNT\FF22882,SCRA2,{0D01AF9F-5C9C-4B54-942E-2B97FCF12826},{AF032472-7840-4C2B-99D6-692FA7A2AD22},7/19/2007 12:35:27 PM,7/19/2007 12:35:27 PM,-1071636471,0x,An OLE DB error has occurred. Error code: 0x80040E07.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E07 Description: "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.".

Looks like you have problem with you source data. Since you get out-of-range problem, my bet is that the source date format is messed up. Probably server is expecting mm/dd/yyyy and your source date is dd/mm/yyyy

Thanks.

|||Data is fine. Script runs on server outside of C#.|||Are you absolutely positive that the package is connecting to the same data sources when you run it from C#? I have a hard time believing that an error message on a data conversion error isn't caused by the data |||As John said, Check for the connections in the data flow task (Direct_Prod) when you run from the C# and in Server.

Thanks|||It is the same. It was running fine and then one day just started bombing. It runs from the server ok and runs from the C# ok when it is on my desktop. Strangest thing I have ever seen.

I recently added the logging so I am sure I have the same version.
|||I figured out my problem. The date comes in like this in most cases 07/09/2007 but comes in like this when run via C# on the server 7/9/2007. I am doing this when I build my query in the string + HolidayEndDt.ToString.Substring(0, 10) +

It is also apparently giving me problems in another spot where I compare month to see if it is EOM and it fails because it is comparing 07 to 7/ since I am using substring for that as well.

What do most people do in these instances? How do you build a query in a string that contains a date? Thanks.
|||

agentf1 wrote:

HolidayEndDt.ToString.Substring(0, 10)
What do most people do in these instances? How do you build a query in a string that contains a date? Thanks.

Assuming you HolidayEndDt is datetime type,

HolidayEndDt.ToString("yyyyMMdd") should solve your problem, and as far as i know thats one of the best way to deal with dates.

Thanks

|||

Yes, that is what I did. Thanks.

Actually I did HolidayEndDt.ToString("MM/dd/yyyy") but I guess there is 6 in one and half dozen in the other. More or less the same thing.

problem running sp_addpublication

When I am running sp_addpublication, I am getting the following error. Whats
wrong?
I was able to run the same script before and this stored procedure was
running fine.
Server: Msg 14294, Level 16, State 1, Procedure sp_verify_job_identifiers,
Line 24
Supply either @.job_id or @.job_name to identify the job.
Job 'Server_Name\InstanceNanme-DBName-1' started successfully.
Adam,
I suggest running profiler to see what are the parameters being sent to this
procedure and to debug where the process is going wrong. The most likely
cause is a changed servername, as the error is raised in several system
procedures and the code is usually of the form...
select @.distribution_jobid = job_id from msdb..sysjobs_view where
name = @.name and
UPPER(originating_server) = UPPER(CONVERT(sysname,
SERVERPROPERTY('ServerName')))
if @.distribution_jobid IS NULL
begin
-- Message from msdb.dbo.sp_verify_job_identifiers
RAISERROR(14262, -1, -1, 'Job', @.name)
GOTO UNDO
end
So, if your servername has changed, this could be the cause of the problem.
In this case:
Use Master
go
Select @.@.Servername
This should return your current server name but if it
returns NULL then try:
Use Master
go
Sp_DropServer 'OldName'
GO
Use Master
go
Sp_Addserver 'NewName', 'local'
GO
Stop and Start SQL Services
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thank you Paul for your response. Your solution does make sense, however in
my case the server name has not changed. I had forgotten to run the script to
create jobs, running it seems to have solve the problem. I am pasting a part
of that script to give you the idea.
if (select count(*) from msdb.dbo.syscategories where name =
N'REPL-LogReader') < 1
execute msdb.dbo.sp_add_category N'REPL-LogReader'
Thanks.
-A
"Paul Ibison" wrote:

> Adam,
> I suggest running profiler to see what are the parameters being sent to this
> procedure and to debug where the process is going wrong. The most likely
> cause is a changed servername, as the error is raised in several system
> procedures and the code is usually of the form...
> select @.distribution_jobid = job_id from msdb..sysjobs_view where
> name = @.name and
> UPPER(originating_server) = UPPER(CONVERT(sysname,
> SERVERPROPERTY('ServerName')))
> if @.distribution_jobid IS NULL
> begin
> -- Message from msdb.dbo.sp_verify_job_identifiers
> RAISERROR(14262, -1, -1, 'Job', @.name)
> GOTO UNDO
> end
> So, if your servername has changed, this could be the cause of the problem.
> In this case:
> Use Master
> go
> Select @.@.Servername
> This should return your current server name but if it
> returns NULL then try:
> Use Master
> go
> Sp_DropServer 'OldName'
> GO
> Use Master
> go
> Sp_Addserver 'NewName', 'local'
> GO
> Stop and Start SQL Services
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Hi Adam,
I do have the same issue like while buidling my replication using
scripts it is giving the following error
Server: Msg 14294, Level 16, State 1, Procedure sp_verify_job_identifiers,
Line 24
Supply either @.job_id or @.job_name to identify the job
even if I have created the job for 'REPL-LogReader', before creating
publication it is throwing the same error, is there any way that I can solve
this error.
Please help.
Thanks
Ramesh
"Adam" wrote:
[vbcol=seagreen]
> Thank you Paul for your response. Your solution does make sense, however in
> my case the server name has not changed. I had forgotten to run the script to
> create jobs, running it seems to have solve the problem. I am pasting a part
> of that script to give you the idea.
> if (select count(*) from msdb.dbo.syscategories where name =
> N'REPL-LogReader') < 1
> execute msdb.dbo.sp_add_category N'REPL-LogReader'
> Thanks.
> -A
> "Paul Ibison" wrote:

problem query returning float with comma

I to all

i am bilding a web page, using asp and sql server

I have a few querys in the asp script. my problem is that the values from the query results to tables with float fiels, apear with a comma
and want a dot

like area= 23,5 and I would like to have area= 23.5

in the query analyser there is no problem its all dots
i have my web aplication running in 3 diferent machines and in 2 of them i dont have this problem. the query results to float fiels apear with a dot

in the 3 machines the database is the same , the odbc conection is similar. i have win xp professional in 2 machines and win 2000 server in other. the machine with this problem has xp pro

something i miss in the IIS...

i am lost

some hint would be very nice

thanks for your time and replayThere could be lots of possible ways to get this behavior. Without knowing a lot about your systems I just have to guess.

My first thought would be that two of the clients have installed English-US and the offending client has installed English-UK versions of either MDAC or IIS.

-PatP

Problem Primary Key will not be created?

I use following config in my vb Script to BulkLoad Data and set up tables in
our DB:
objBL.SGDropTables = True
objBL.SchemaGen = True
objBL.SGUseID = True
objBL.BulkLoad = True
The Mapping schema looks like this:
<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"
xmlns:sql="urn:schemas-microsoft-com:xml-sql" >
<ElementType name="IAM_Kostenerfassung.DatensatzNr." dt:type="id"
sql:datatype="nvarchar(15)"/>
<ElementType name="Report" sql:is-constant="1">
<element type="Kosten" />
</ElementType>
<element type="IAM_Kostenerfassung.DatensatzNr." sql:field="DatensatzNr"/>
</Schema>
You will need to use the sql:key-field annotation for this.
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad
|||How does that work in SQLXML Doc i found that:
"sql:key-fields
XML Bulk Load always ignores this annotation."
Can you give me an example for a working XDR-Schema?
""Andrew Conrad"" wrote:

> You will need to use the sql:key-field annotation for this.
> Andrew Conrad
> Microsoft Corp
> http://blogs.msdn.com/aconrad
>
|||I'm sorry - you are correct. SqlXmlBulkload does not any database
constraints.

Problem Primary Key will not be created?

I use following config in my vb Script to BulkLoad Data and set up tables in
our DB:
objBL.SGDropTables = True
objBL.SchemaGen = True
objBL.SGUseID = True
objBL.BulkLoad = True
The Mapping schema looks like this:
<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"
xmlns:sql="urn:schemas-microsoft-com:xml-sql" >
<ElementType name="IAM_Kostenerfassung.DatensatzNr." dt:type="id"
sql:datatype="nvarchar(15)"/>
<ElementType name="Report" sql:is-constant="1">
<element type="Kosten" />
</ElementType>
<element type="IAM_Kostenerfassung.DatensatzNr." sql:field="DatensatzNr"/>
</Schema>You will need to use the sql:key-field annotation for this.
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad|||How does that work in SQLXML Doc i found that:
"sql:key-fields
XML Bulk Load always ignores this annotation."
Can you give me an example for a working XDR-Schema?
""Andrew Conrad"" wrote:

> You will need to use the sql:key-field annotation for this.
> Andrew Conrad
> Microsoft Corp
> http://blogs.msdn.com/aconrad
>|||I'm sorry - you are correct. SqlXmlBulkload does not any database
constraints.

Problem on scripting stored procedures

I have tried to script all of stored procedures in one database on SQL server
2005 then it seems like get stacked. To script one stored procedure has no
problem however when try to script all stored procedures SSMS never respond.
I have never experience this problem on SQL server 2000.
Any help?
M. Matsuda (MMatsuda@.discussions.microsoft.com) writes:
> I have tried to script all of stored procedures in one database on SQL
> server 2005 then it seems like get stacked. To script one stored
> procedure has no problem however when try to script all stored
> procedures SSMS never respond.
> I have never experience this problem on SQL server 2000.
> Any help?
How many procedures are there in the database? There were performance
issues during the beta, but it appears to behave decently now.
One possibility is blocking, if someone has submitted:
BEGIN TRANSACTION
go
CREATE PROCEDURE ...
and never committed the transaction. You can use sp_who to determine if
you have any blocking in the database. If there is a non-zero value in
the Blk column, in means that the spid in Blk blocks the spid on that
line.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||There are 193 stored procedures and same process on SQL 2000 ends less than
30 second. There is no other user using SQL 2005 except myself. (Because this
SQL 2005 is used as application development purpose.)
When script one stored procedure SSMS promptly responded and showed dialog
where to save however when select all stored procedures it did not show the
dialog more than 10 minits.
Performance monitor showed 100% processor time during being stalled.
"Erland Sommarskog" wrote:

> M. Matsuda (MMatsuda@.discussions.microsoft.com) writes:
> How many procedures are there in the database? There were performance
> issues during the beta, but it appears to behave decently now.
> One possibility is blocking, if someone has submitted:
> BEGIN TRANSACTION
> go
> CREATE PROCEDURE ...
> and never committed the transaction. You can use sp_who to determine if
> you have any blocking in the database. If there is a non-zero value in
> the Blk column, in means that the spid in Blk blocks the spid on that
> line.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
|||M. Matsuda (MMatsuda@.discussions.microsoft.com) writes:
> There are 193 stored procedures
That's not an extraordiary lot.

> There is no other user using SQL 2005 except myself.
That does not preclude blocking, if that is what you were thinking.

> When script one stored procedure SSMS promptly responded and showed
> dialog where to save however when select all stored procedures it did
> not show the dialog more than 10 minits.
> Performance monitor showed 100% processor time during being stalled.
Hm, is Mgmt Studio and SQL Server on the same machine? How much memory
is there in the box? How much memory does SQL Server actually have?
What I have noticed with SQL 2005 is that if it falls down 30-35 MB in
memory, the simplest queries can take over 10 seconds.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||SQL 2005 installed in Netfinity 5000 with 896MB RAM (P3 500M HZ) + Windows
2003 Server. SSMS and SQL 2005 reside on same machine.
Before upgrade to SQL 2005, SQL 2000 run on same machine did not have any
problem. Even SQL 2000 + Windows XP installed on notebook (Think pad 512MB
RAM) + Visual Stuido 2005 run on same time does not have any problem to
perform this process.
"Erland Sommarskog" wrote:

> M. Matsuda (MMatsuda@.discussions.microsoft.com) writes:
> That's not an extraordiary lot.
>
> That does not preclude blocking, if that is what you were thinking.
>
> Hm, is Mgmt Studio and SQL Server on the same machine? How much memory
> is there in the box? How much memory does SQL Server actually have?
> What I have noticed with SQL 2005 is that if it falls down 30-35 MB in
> memory, the simplest queries can take over 10 seconds.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
|||I don't know how you can get 896MB of memory, I suspect it is really 512MB.
But in any case you are not meeting the minimum hardware requirements on CPU
for SQL2005 let alone the recommended. As such I would expect it to be
somewhat slow.
http://www.microsoft.com/sql/editions/developer/sysreqs.mspx
Andrew J. Kelly SQL MVP
"M. Matsuda" <MMatsuda@.discussions.microsoft.com> wrote in message
news:A207A819-2286-4552-9EE4-3F4918687303@.microsoft.com...[vbcol=seagreen]
> SQL 2005 installed in Netfinity 5000 with 896MB RAM (P3 500M HZ) +
> Windows
> 2003 Server. SSMS and SQL 2005 reside on same machine.
> Before upgrade to SQL 2005, SQL 2000 run on same machine did not have any
> problem. Even SQL 2000 + Windows XP installed on notebook (Think pad 512MB
> RAM) + Visual Stuido 2005 run on same time does not have any problem to
> perform this process.
> "Erland Sommarskog" wrote:
|||Sounds like you are blaming that hardware has not meet minimum requirement of
SQL 2005. However this system has PIII 600MHZ 896MB RAM (Windows 2003
recognize 896MB). It meets minimum requirement of 32bit SQL 2005.
Would you tell me what a expected time to finish this process on miminum
hardware requirement?
Thanks in advance
"Andrew J. Kelly" wrote:

> I don't know how you can get 896MB of memory, I suspect it is really 512MB.
> But in any case you are not meeting the minimum hardware requirements on CPU
> for SQL2005 let alone the recommended. As such I would expect it to be
> somewhat slow.
> http://www.microsoft.com/sql/editions/developer/sysreqs.mspx
> --
> Andrew J. Kelly SQL MVP
> "M. Matsuda" <MMatsuda@.discussions.microsoft.com> wrote in message
> news:A207A819-2286-4552-9EE4-3F4918687303@.microsoft.com...
>
>
|||M. Matsuda (MMatsuda@.discussions.microsoft.com) writes:
> SQL 2005 installed in Netfinity 5000 with 896MB RAM (P3 500M HZ) +
> Windows 2003 Server. SSMS and SQL 2005 reside on same machine. Before
> upgrade to SQL 2005, SQL 2000 run on same machine did not have any
> problem. Even SQL 2000 + Windows XP installed on notebook (Think pad
> 512MB RAM) + Visual Stuido 2005 run on same time does not have any
> problem to perform this process.
It is not very impressing hardware. And, yes, this problem with SQL Server
being very slow when it's low on memory is much more apparent with SQL 2005
than SQL 2000.
Did you use Task Manager to see how much memory SQL Server has when
performing the scripting operation?
If you have other processes running, for instance a web browser, try closing
these and see if it helps.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Our tight budget won't allow me to upgrade fancy hardware as you may have and
still need to deal with limited resources and so do my small business clients
too.
I had a chance to test same process on IBM XS235 Xeon 3.06GHZ with 1.5GB RAM
relatively enough spec for SQL 2005 however it still took 7-8 minutes to get
response from SSMS. If this is ideal time to finish this process, I may need
to stick SQL 2000 for a while and recommend stay with SQL 2000 to my clients
for time being.
Thank you for your assistance.
"Erland Sommarskog" wrote:

> M. Matsuda (MMatsuda@.discussions.microsoft.com) writes:
> It is not very impressing hardware. And, yes, this problem with SQL Server
> being very slow when it's low on memory is much more apparent with SQL 2005
> than SQL 2000.
> Did you use Task Manager to see how much memory SQL Server has when
> performing the scripting operation?
> If you have other processes running, for instance a web browser, try closing
> these and see if it helps.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
|||M. Matsuda (MMatsuda@.discussions.microsoft.com) writes:
> Our tight budget won't allow me to upgrade fancy hardware as you may
> have and still need to deal with limited resources and so do my small
> business clients too.
Fancy and fancy. A 500 Mhz machine a certainly to reqard as an antiquity
today.

> I had a chance to test same process on IBM XS235 Xeon 3.06GHZ with 1.5GB
> RAM relatively enough spec for SQL 2005 however it still took 7-8
> minutes to get response from SSMS. If this is ideal time to finish this
> process, I may need to stick SQL 2000 for a while and recommend stay
> with SQL 2000 to my clients for time being.
I tried the exercise at home on a Pentium4 2.8 GHz with hyperthreading
and 1.5 GB of memory. I selected 275 procedures, and took about the time you
mentioned to script them. I noticed that SQL Server was eating a lot
of memory, around 440 MB, as well as CPU. For some reason that I don't
understand, Windows Explorer was also consuming CPU.
In this experiment, I selected the procedures from the Summary view.
The next thing I did was to use the scripting wizard. Right-click the
database node in Object Explorer and select Tasks->Generate Scripts.
It took about a minute for me to make the selection. (This database
has over 4000 thousand procedures, so I could not use Select all.)
But once started, the wizard completed within a minute. It may be
because SQL Server now had all it neded in memory. I need to play
with this a little more.
I agree with you that the performance is not satisfactory.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Problem installing MSDE

I'm having problems installing MSDE on a windows XP maching. Running setup with the verbose script gives me this:

Property(C): OriginalDatabase = C:\MSDERelA\Setup\SqlRun01.msi
Property(C): UILevel = 3
Property(C): ACTION = INSTALL
=== Logging stopped: 04/09/2007 14:17:38 ===
MSI (c) (A4:AC) [14:17:38:817]: Note: 1: 1708
MSI (c) (A4:AC) [14:17:38:817]: Product: Microsoft SQL Server Desktop Engine -- Installation operation failed.

MSI (c) (A4:AC) [14:17:38:828]: Grabbed execution mutex.
MSI (c) (A4:AC) [14:17:38:828]: Cleaning up uninstalled install packages, if any exist
MSI (c) (A4:AC) [14:17:38:828]: MainEngineThread is returning 1603

This is just the last little bit I can post the whole thing if necessary. The installation stops and rolls back anyone have any idea what's causing this to fail? The server service is started and running normally.....

Try to reboot the machine and give a try to reinstall it......refer this link might be useful

http://support.microsoft.com/default.aspx?scid=kb;en-us;816499&Product=sql|||I've already tried removing the data files, removing the MSDE reference in the registry and rebooting. All to no avail....

Problem installing MSDE

I'm having problems installing MSDE on a windows XP maching. Running setup with the verbose script gives me this:

Property(C): OriginalDatabase = C:\MSDERelA\Setup\SqlRun01.msi
Property(C): UILevel = 3
Property(C): ACTION = INSTALL
=== Logging stopped: 04/09/2007 14:17:38 ===
MSI (c) (A4:AC) [14:17:38:817]: Note: 1: 1708
MSI (c) (A4:AC) [14:17:38:817]: Product: Microsoft SQL Server Desktop Engine -- Installation operation failed.

MSI (c) (A4:AC) [14:17:38:828]: Grabbed execution mutex.
MSI (c) (A4:AC) [14:17:38:828]: Cleaning up uninstalled install packages, if any exist
MSI (c) (A4:AC) [14:17:38:828]: MainEngineThread is returning 1603

This is just the last little bit I can post the whole thing if necessary. The installation stops and rolls back anyone have any idea what's causing this to fail? The server service is started and running normally.....

Try to reboot the machine and give a try to reinstall it......refer this link might be useful

http://support.microsoft.com/default.aspx?scid=kb;en-us;816499&Product=sql|||I've already tried removing the data files, removing the MSDE reference in the registry and rebooting. All to no avail....

Problem Installing AdventureWorksDB.msi

I'm running on a stand alone machine. No network connections. I get the following error message when running this script:

"Error 1316. A network error occurred while attempting to read from the file C:\Program Files\Microsoft SQL Server\AdventureWorksDB[1].msi"

So how do I resolve this problem, so I can get the Adventure Works DB installed?

This often has to do with a failed installation from an earlier attempt. You may have to clean registry entries or edit the .msi file, but try this tool first:

http://support.microsoft.com/default.aspx?scid=kb;en-us;290301

Buck

|||

Dan,

Did you get this problem solved?

Regards,

Mike Wachal
SQL Express team

-
Check out my tips for getting your answer faster and how to ask a good question: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=307712&SiteID=1

|||The previous response was of minimal value. There were no previous versions that could have failed and fouled up the registry. Any other ideas!|||

The inclusion of the [1] in the file name from the error suggests to me that the file was in a temporary directory, which would be the result of clicking Run directly from the Download page.

If this was the case, then I'd suggest trying to Save the file to your local hard drive and then run the saved file. If that is not the case, it would be worth downloading a fresh copy of the sample database from the download center. It is rare, but occationally files can be damaged during the download process.

If neither of those solve the problem, I can get the folks from the Setup group involved to see if they have better ideas than I do.

Regards,

Mike Wachal
SQL Express team

-
Check out my tips for getting your answer faster and how to ask a good question: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=307712&SiteID=1

|||I think this will work. But unfortunately while uninstalling that install I have corrupted the system. I will be rebuilding the system for a couple of days, and then I will be able to give it a try. Thank You DLM.|||

OK Dan,

Be sure to respond back to the forum with your results as I'm sure others will be interested.

Regards,

Mike Wachal
SQL Express team

-
Check out my tips for getting your answer faster and how to ask a good question: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=307712&SiteID=1

|||

>This often has to do with a failed installation from an earlier attempt. You may have to clean registry entries or edit the .msi file, but try this tool first:

>http://support.microsoft.com/default.aspx?scid=kb;en-us;290301

Thankyou this worked for me winxp sp2 with the error.

Problem Installing AdventureWorksDB.msi

I'm running on a stand alone machine. No network connections. I get the following error message when running this script:

"Error 1316. A network error occurred while attempting to read from the file C:\Program Files\Microsoft SQL Server\AdventureWorksDB[1].msi"

So how do I resolve this problem, so I can get the Adventure Works DB installed?

This often has to do with a failed installation from an earlier attempt. You may have to clean registry entries or edit the .msi file, but try this tool first:

http://support.microsoft.com/default.aspx?scid=kb;en-us;290301

Buck

|||

Dan,

Did you get this problem solved?

Regards,

Mike Wachal
SQL Express team

-
Check out my tips for getting your answer faster and how to ask a good question: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=307712&SiteID=1

|||The previous response was of minimal value. There were no previous versions that could have failed and fouled up the registry. Any other ideas!|||

The inclusion of the [1] in the file name from the error suggests to me that the file was in a temporary directory, which would be the result of clicking Run directly from the Download page.

If this was the case, then I'd suggest trying to Save the file to your local hard drive and then run the saved file. If that is not the case, it would be worth downloading a fresh copy of the sample database from the download center. It is rare, but occationally files can be damaged during the download process.

If neither of those solve the problem, I can get the folks from the Setup group involved to see if they have better ideas than I do.

Regards,

Mike Wachal
SQL Express team

-
Check out my tips for getting your answer faster and how to ask a good question: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=307712&SiteID=1

|||I think this will work. But unfortunately while uninstalling that install I have corrupted the system. I will be rebuilding the system for a couple of days, and then I will be able to give it a try. Thank You DLM.|||

OK Dan,

Be sure to respond back to the forum with your results as I'm sure others will be interested.

Regards,

Mike Wachal
SQL Express team

-
Check out my tips for getting your answer faster and how to ask a good question: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=307712&SiteID=1

|||

>This often has to do with a failed installation from an earlier attempt. You may have to clean registry entries or edit the .msi file, but try this tool first:

>http://support.microsoft.com/default.aspx?scid=kb;en-us;290301

Thankyou this worked for me winxp sp2 with the error.

Problem inserting Rows after table creation

I am using the below SQL to insert a table. The problem is after I run this, I run another script to populate the table (see below). The population script will work if I run it as INSERT INTO ... SELECT TOP 99.9999999 PERCENT ..., but if I put 100 PERCENT, or just use no percent limiter I get the following error: Msg 8624, Internal SQL Server error.

It is weird b/c once something is inserted in the table, i can run the populate script without any problems. Any idea as to why this is happening?

Thanks,

Dave

TABLE GENERATION SCRIPT

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbCelebroAds]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbCelebroAds]
GO

CREATE TABLE [dbo].[tbCelebroAds] (
[AdID] [int] IDENTITY (1, 1) NOT NULL ,
[BranchCode] [int] NOT NULL ,
[PropertyID] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AdScheduleCode] [int] NOT NULL ,
[RecAtCentral] [int] NULL ,
[AdRan] [int] NOT NULL ,
[AdStatusID] [int] NOT NULL ,
[PatID] [int] NULL ,
[RunDate] [datetime] NOT NULL ,
[CreationCost] [float] NOT NULL ,
[BillCost] [float] NOT NULL ,
[AddedDate] [smalldatetime] NOT NULL
) ON [PRIMARY]

TABLE POPULATION SCRIPT

INSERT INTO tbCelebroAds (BranchCode, PropertyID, AdScheduleCode, PatID, AdStatusID, AdRan, RunDate, CreationCost, BillCost, AddedDate)
SELECT [TOP 100 PERCENT or no percent limiter doesn't work, TOP 99.9999999 PERCENT does] BranchCode, PropertyID, AdScheduleCode, cp.PatID, 6, 0, CAST(PubDate AS DATETIME), CAST(ISNULL(pat.Cost,0) AS DECIMAL(10,2)), 0, GetDate()
FROM tbCelebroView cv
LEFT JOIN tbCelebroPubs cp ON cv.PublicationName = cp.PublicationName AND cv.AdSectionName = cp.AdSectionName
LEFT JOIN tbPubToAdType pat ON cp.PatID = pat.PatID
WHERE CAST(BranchCode AS nvarchar(20)) + CAST(PropertyID AS varchar(20)) + CAST(AdScheduleCode AS nvarchar(20)) NOT IN
(SELECT CAST(BranchCode AS nvarchar(20)) + CAST(PropertyID AS varchar(20)) + CAST(AdScheduleCode AS nvarchar(20)) FROM tbCelebroAds)I wonder if it has anything to do with the fact that, in your where clause, you're excluding records that exist in the very table you're inserting into (don't know why you're doing that, but I'm sure you have your reasons). Have you tried removing that last clause to see if it will work?|||The reason I am excluding records that exist in the table is b/c I am trying to perform a sort of "Merge" where only new records get inserted.

Is there a better way of doing this?|||How about inserting existing rows into a temp table first.
Then run you main select, but where records don't exist in your temp table.

Insert into #myTemp (select cast(....) )

insert into tbCelebroAds(....)select ...
where cast(...) not in select * from #myTemp|||Thanks TJ, that is a simple solution that worked perfectly when I tried it.

Thanks again for your help!