Help I am getting an error when I run my insert statement

Please help I am getting the same error no matter how I change my
quotes.
Microsoft OLE DB Provider for SQL Server error '80040e14'
Incorrect syntax near the keyword 'Plan'.
conn.Execute("INSERT INTO jmiles.Plan([startdate],[finishdate],
[revfinish],[responsible],[action],[title],[dept],[author],[type])
values ('" & request.Form("startdate") &"' ,"&
Request.Form("finishdate") &" ," & Request.Form("revfinish") & ",'" &
Request.Form("responsible") & "','" & Request.Form("action") & "','" &
Request.Form("title") & "','" & Request.Form("dept") & "','" &
session("empid") & "','" & Request.form("type") & "')")
'set Rs =conn.Execute("SELECT * FROM Equipment")
startdate, finshdate,revfinish are all smalldatetime
[Action], responsible, dept, author, type are all text
title is nvarchar
any Ideas?
Try the OLEDB group?
"Tescumeh" <tescumeh@.gmail.com> wrote in message
news:1165868180.115810.136290@.l12g2000cwl.googlegr oups.com...
> Please help I am getting the same error no matter how I change
> my
> quotes.
> Microsoft OLE DB Provider for SQL Server error '80040e14'
> Incorrect syntax near the keyword 'Plan'.
> conn.Execute("INSERT INTO jmiles.Plan([startdate],[finishdate],
> [revfinish],[responsible],[action],[title],[dept],[author],[type])
> values ('" & request.Form("startdate") &"' ,"&
> Request.Form("finishdate") &" ," & Request.Form("revfinish") &
> ",'" &
> Request.Form("responsible") & "','" & Request.Form("action") &
> "','" &
> Request.Form("title") & "','" & Request.Form("dept") & "','" &
> session("empid") & "','" & Request.form("type") & "')")
> 'set Rs =conn.Execute("SELECT * FROM Equipment")
> startdate, finshdate,revfinish are all smalldatetime
> [Action], responsible, dept, author, type are all text
> title is nvarchar
> any Ideas?
>
|||What error message are you getting?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Tescumeh" <tescumeh@.gmail.com> wrote in message
news:1165868180.115810.136290@.l12g2000cwl.googlegr oups.com...
> Please help I am getting the same error no matter how I change my
> quotes.
> Microsoft OLE DB Provider for SQL Server error '80040e14'
> Incorrect syntax near the keyword 'Plan'.
> conn.Execute("INSERT INTO jmiles.Plan([startdate],[finishdate],
> [revfinish],[responsible],[action],[title],[dept],[author],[type])
> values ('" & request.Form("startdate") &"' ,"&
> Request.Form("finishdate") &" ," & Request.Form("revfinish") & ",'" &
> Request.Form("responsible") & "','" & Request.Form("action") & "','" &
> Request.Form("title") & "','" & Request.Form("dept") & "','" &
> session("empid") & "','" & Request.form("type") & "')")
> 'set Rs =conn.Execute("SELECT * FROM Equipment")
> startdate, finshdate,revfinish are all smalldatetime
> [Action], responsible, dept, author, type are all text
> title is nvarchar
> any Ideas?
>
|||Imposter!
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Arnie" <99yoda@.newsgroup.nospam> wrote in message
news:u0e%23N%23XHHHA.3616@.TK2MSFTNGP02.phx.gbl...
> Try the OLEDB group?
>
> "Tescumeh" <tescumeh@.gmail.com> wrote in message
> news:1165868180.115810.136290@.l12g2000cwl.googlegr oups.com...
>
|||Microsoft OLE DB Provider for SQL Server error '80040e14'
Incorrect syntax near the keyword 'Plan'.
imposter?
|||> imposter?
We're both "Arnie".
- Arnie
|||Tescumeh said (on or about) 12/11/2006 15:16:
> Please help I am getting the same error no matter how I change my
> quotes.
> Microsoft OLE DB Provider for SQL Server error '80040e14'
> Incorrect syntax near the keyword 'Plan'.
> conn.Execute("INSERT INTO jmiles.Plan([startdate],[finishdate],
> [revfinish],[responsible],[action],[title],[dept],[author],[type])
> values ('" & request.Form("startdate") &"' ,"&
> Request.Form("finishdate") &" ," & Request.Form("revfinish") & ",'" &
> Request.Form("responsible") & "','" & Request.Form("action") & "','" &
> Request.Form("title") & "','" & Request.Form("dept") & "','" &
> session("empid") & "','" & Request.form("type") & "')")
> 'set Rs =conn.Execute("SELECT * FROM Equipment")
> startdate, finshdate,revfinish are all smalldatetime
> [Action], responsible, dept, author, type are all text
> title is nvarchar
> any Ideas?
>
When I have a problem like that, I find it very helpful to
break the code into parts so I can assign the SQL statement
to a variable, place a breakpoint in the code and inspect
the variable. Then I try copying the SQL and pasting it
into Query Analyzer. It almost always shows me the exact
problem.
|||Thank you I will try that.

Help I am getting an error when I run my insert statement

Please help I am getting the same error no matter how I change my
quotes.
Microsoft OLE DB Provider for SQL Server error '80040e14'
Incorrect syntax near the keyword 'Plan'.
conn.Execute("INSERT INTO jmiles.Plan([startdate],[finishdate],
[revfinish],[responsible],[action],[title],[dept],[a
uthor],[type])
values ('" & request.Form("startdate") &"' ,"&
Request.Form("finishdate") &" ," & Request.Form("revfinish") & ",'" &
Request.Form("responsible") & "','" & Request.Form("action") & "','" &
Request.Form("title") & "','" & Request.Form("dept") & "','" &
session("empid") & "','" & Request.form("type") & "')")
'set Rs =conn.Execute("SELECT * FROM Equipment")
startdate, finshdate,revfinish are all smalldatetime
[Action], responsible, dept, author, type are all text
title is nvarchar
any Ideas?Try the OLEDB group?
"Tescumeh" <tescumeh@.gmail.com> wrote in message
news:1165868180.115810.136290@.l12g2000cwl.googlegroups.com...
> Please help I am getting the same error no matter how I change
> my
> quotes.
> Microsoft OLE DB Provider for SQL Server error '80040e14'
> Incorrect syntax near the keyword 'Plan'.
> conn.Execute("INSERT INTO jmiles.Plan([startdate],[finishdate],
> [revfinish],[responsible],[action],[title],[dept],[
;author],[type])
> values ('" & request.Form("startdate") &"' ,"&
> Request.Form("finishdate") &" ," & Request.Form("revfinish") &
> ",'" &
> Request.Form("responsible") & "','" & Request.Form("action") &
> "','" &
> Request.Form("title") & "','" & Request.Form("dept") & "','" &
> session("empid") & "','" & Request.form("type") & "')")
> 'set Rs =conn.Execute("SELECT * FROM Equipment")
> startdate, finshdate,revfinish are all smalldatetime
> [Action], responsible, dept, author, type are all text
> title is nvarchar
> any Ideas?
>|||What error message are you getting?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Tescumeh" <tescumeh@.gmail.com> wrote in message
news:1165868180.115810.136290@.l12g2000cwl.googlegroups.com...
> Please help I am getting the same error no matter how I change my
> quotes.
> Microsoft OLE DB Provider for SQL Server error '80040e14'
> Incorrect syntax near the keyword 'Plan'.
> conn.Execute("INSERT INTO jmiles.Plan([startdate],[finishdate],
> [revfinish],[responsible],[action],[title],[dept],[
;author],[type])
> values ('" & request.Form("startdate") &"' ,"&
> Request.Form("finishdate") &" ," & Request.Form("revfinish") & ",'" &
> Request.Form("responsible") & "','" & Request.Form("action") & "','" &
> Request.Form("title") & "','" & Request.Form("dept") & "','" &
> session("empid") & "','" & Request.form("type") & "')")
> 'set Rs =conn.Execute("SELECT * FROM Equipment")
> startdate, finshdate,revfinish are all smalldatetime
> [Action], responsible, dept, author, type are all text
> title is nvarchar
> any Ideas?
>|||Imposter!
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Arnie" <99yoda@.newsgroup.nospam> wrote in message
news:u0e%23N%23XHHHA.3616@.TK2MSFTNGP02.phx.gbl...
> Try the OLEDB group?
>
> "Tescumeh" <tescumeh@.gmail.com> wrote in message
> news:1165868180.115810.136290@.l12g2000cwl.googlegroups.com...
>|||Microsoft OLE DB Provider for SQL Server error '80040e14'
Incorrect syntax near the keyword 'Plan'.
imposter?|||> imposter?
We're both "Arnie".
- Arnie|||Tescumeh said (on or about) 12/11/2006 15:16:
> Please help I am getting the same error no matter how I change my
> quotes.
> Microsoft OLE DB Provider for SQL Server error '80040e14'
> Incorrect syntax near the keyword 'Plan'.
> conn.Execute("INSERT INTO jmiles.Plan([startdate],[finishdate],
> [revfinish],[responsible],[action],[title],[dept],[
;author],[type])
> values ('" & request.Form("startdate") &"' ,"&
> Request.Form("finishdate") &" ," & Request.Form("revfinish") & ",'" &
> Request.Form("responsible") & "','" & Request.Form("action") & "','" &
> Request.Form("title") & "','" & Request.Form("dept") & "','" &
> session("empid") & "','" & Request.form("type") & "')")
> 'set Rs =conn.Execute("SELECT * FROM Equipment")
> startdate, finshdate,revfinish are all smalldatetime
> [Action], responsible, dept, author, type are all text
> title is nvarchar
> any Ideas?
>
When I have a problem like that, I find it very helpful to
break the code into parts so I can assign the SQL statement
to a variable, place a breakpoint in the code and inspect
the variable. Then I try copying the SQL and pasting it
into Query Analyzer. It almost always shows me the exact
problem.|||Thank you I will try that.

help for shrinking database

Hi guys.

I have a huge database file. When I run sp_spaceused this are the
results:

db_size 1337,31 Mb
unallocated 14,62 Mb
reserved 1088456 Kb
data 258992 Kb
index_size 6224 Kb
unused 823240 Kb

Now, when I run dbcc shrinkfile(database_name, 50) or whatever value of
percent, it says there is not enough free space in db.

Please advice how to shrink the size of that file and why is it so big?

Thanks in advance

Zvonkowhat about initially shrinking the log , and then the data file?

--
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________

"Zvonko" <zvonko_NOSPAM_@.velkat.net> wrote in message
news:449799a2@.ns1.novi-net.net...
> Hi guys.
> I have a huge database file. When I run sp_spaceused this are the
> results:
> db_size 1337,31 Mb
> unallocated 14,62 Mb
> reserved 1088456 Kb
> data 258992 Kb
> index_size 6224 Kb
> unused 823240 Kb
> Now, when I run dbcc shrinkfile(database_name, 50) or whatever value
of
> percent, it says there is not enough free space in db.
> Please advice how to shrink the size of that file and why is it so
big?
> Thanks in advance
> Zvonko|||Zvonko (zvonko_NOSPAM_@.velkat.net) writes:
> I have a huge database file. When I run sp_spaceused this are the
> results:
> db_size 1337,31 Mb
> unallocated 14,62 Mb
> reserved 1088456 Kb
> data 258992 Kb
> index_size 6224 Kb
> unused 823240 Kb
> Now, when I run dbcc shrinkfile(database_name, 50) or whatever value
> of percent, it says there is not enough free space in db.
> Please advice how to shrink the size of that file and why is it so big?

Big? 1.3 GB is a small database by today's stanadards, at least for a
production system.

The fact that the amount of "unused" is so much larger than data, indicates
that you have one or more tables that suffers from high level of
fragmentation.

This SELECT:

SELECT object_name(id), reserved FROM sysindexes WHERE indid IN (0,1)
ORDER BY reserved DESC

gives you the tables by size. You can then use DBCC SHOWCONTIG to determine
the level of fragmentation, and DBCC DBREINDEX to defragment the tables.
However, you cannot reindex tables that does not have a clustered index
with DBCC DBREINDEX. You can however, create a clustered index on these
tables, and then drop it if you absolutely do not want it.

"reserved" = space actually allocated to tables.
"unused" = space within the reserved space that is not actually used.

DBCC SHRINKFILE operates only with unallocated space. Once you have
defragmented the tables, you may be able to shrink the file.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Do not forget to do some backup B4 the operation...
Jack Vamvas wrote:
> what about initially shrinking the log , and then the data file?
> --
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "Zvonko" <zvonko_NOSPAM_@.velkat.net> wrote in message
> news:449799a2@.ns1.novi-net.net...
> > Hi guys.
> > I have a huge database file. When I run sp_spaceused this are the
> > results:
> > db_size 1337,31 Mb
> > unallocated 14,62 Mb
> > reserved 1088456 Kb
> > data 258992 Kb
> > index_size 6224 Kb
> > unused 823240 Kb
> > Now, when I run dbcc shrinkfile(database_name, 50) or whatever value
> of
> > percent, it says there is not enough free space in db.
> > Please advice how to shrink the size of that file and why is it so
> big?
> > Thanks in advance
> > Zvonko

Help for report(rsInvalidItemPath)

Hi
I have beening trying to build a report with 2 parameters. When I run it
under VS.net, it all went well, but after I deploy it to the server, I got
the message as following: **Reporting Services Error--The path of the item ''
is not valid. The full path must be less than 260 characters long, must start
with slash; other restrictions apply. Check the documentation for complete
set of restrictions. (rsInvalidItemPath) Get Online Help **
what went wrong, please help.
the URL as following:
http://localhost/reportserver?%2fRev_Stats%2fRev_Stats&rs:Command=Render
TIAThe url looks correct. Did you get this url by navigating to the
reportserver virtual directory? Is there anything more in the
reportserver_<timestamp>.log file?
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"wd1153" <wd1153@.discussions.microsoft.com> wrote in message
news:2B5DBCB5-B943-4675-997B-145DF0ECDF17@.microsoft.com...
> Hi
> I have beening trying to build a report with 2 parameters. When I run it
> under VS.net, it all went well, but after I deploy it to the server, I got
> the message as following: **Reporting Services Error--The path of the item
> ''
> is not valid. The full path must be less than 260 characters long, must
> start
> with slash; other restrictions apply. Check the documentation for complete
> set of restrictions. (rsInvalidItemPath) Get Online Help **
> what went wrong, please help.
> the URL as following:
> http://localhost/reportserver?%2fRev_Stats%2fRev_Stats&rs:Command=Render
> TIA
>sql

Help Fix Slow Query.

I have a query that is taking too long to run. It take 14 seconds to return 6800 rows. However, if I move the query out of a stored proc, it takes 1 second. I want to understand this issue and ideally fix the stored proc case.

I've simplified my actual queries for readability.

-- @.filter is value to filter against or NULL to return all records.
CREATE PROCEDURE queryPlayerStations(@.filter INTEGER)
AS
SELECT * FROM MyTable
-- Other joins and query logic omitted for brevity
WHERE ((@.filter IS NULL) OR (MyTable.Column = @.filter))
GO

DECLARE @.filter INTEGER
SET @.filter = NULL

-- Takes 14 seconds to return 6800 rows. That's unacceptable performance
EXEC dbo.queryPlayerStations @.filter

When I run the query directly in Query Analyzer, it runs very fast.

DECLARE @.filter INTEGER
SET @.filter = NULL

-- Takes ~1 second to return 6800 rows. That's great performance
SELECT * FROM MyTable
-- Other joins and query logic omitted for brevity
WHERE ((@.filter IS NULL) OR (MyTable.Column = @.filter))

When I put the parameters in the stored proc it runs fast.

CREATE PROCEDURE queryPlayerStations
AS
DECLARE @.filter INTEGER
SET @.filter = NULL

SELECT * FROM MyTable
-- Other joins and query logic omitted for brevity
WHERE ((@.filter IS NULL) OR (MyTable.Column = @.filter))
GO

-- Takes ~1 second to return 6800 rows. That's great performance
EXEC dbo.queryPlayerStations

Anyone have any ideas what I can do to improve the stored proc case?Just a quick *guess* before I leave office for tonight...

The optimization in SQL Server works differently depending
on where the parameter is defined (as a procedure call argument or inside using DECLARE). In one of the cases,
it doesn't have enough info to optimize in the best way.|||As Coolberg implied, what happens if you do this:

ALTER PROCEDURE queryPlayerStations(@.filterIN INTEGER)
AS
DECLARE @.filter INTEGER
SET @.filter = @.filterIN

SELECT * FROM MyTable
-- Other joins and query logic omitted for brevity
WHERE ((@.filter IS NULL) OR (MyTable.Column = @.filter))
GO

DECLARE @.filterIN INTEGER
SET @.filterIN = NULL
EXEC dbo.queryPlayerStations @.filterIN

HELP DTS problems !

I had some DTS packages that are scheduled to run as a job every week. They have been running fine for about 4 months.

One of the jobs failed in the weekend. i was normally able to run the job by logging into the server. The job failed instantenously.

I can't even run the DTS package. I am registered on my enterprise manager as an administrator as well. I tried registering as sa but it comes with the same result. i do not have any aliases. and the DTS packages are only working within the indivdual server. The errror messsage is :-

[DBNETLIB][Connection Open (Connect()).]SQL Server does not exist or access is denied

Any help would be greatly appreciated.mmmm...have you checked that all your connection inside the DTS point to a valid server? Can you connect to the server that you use inside the DTS?|||i actually created another new package and it works.
so

1. the old package does not run
2. The new package runs fine.

Do you think that my msdb database is corrupted ? or is it the individual databases ?|||Right-mouse click on the package and select "Versions". See what that shows you (busy fingures, man, busy fingures ;))|||Did that, the packages haven't been modified for the last 6 months.
I think i might restore the msdb database and go from there.|||Check the schedule relationed with your DTS package in SQL Server agent manager.See whether the owner of job had been changed or not.|||The SQL Server agent is being run as a domain user who is an administrator on the SQL Server.

Currently merging our backup tape to get the msdb database.
There was actually some patching of the servers in the weekend and one of the domain controllers was rebooted.

Does anyone think that there is some relation with the domain account, sql server agent and my dts packages are not working|||Things don't happen on their own. Some had to have been changed for the "anomaly" to happen.|||I restored the msdb database still the same error

[DBNETLIB][Connection Open (Connect()).]SQL Server does not exist or access is denied

the UDLs i believe i not working properly. Does anyone know about UDL corruption?

Would restoring the master database help ?|||Is there anything of note in your server logs? What shows up in the job history (especially if you show details)?

Have you tried to execute the package one step at a time, to see what fails? If you have multiple connections, it might not be failing where you'd expect.

Once you know which connection is failing, try to make a connection using the same credentials via Query Analyzer or OSQL.EXE to see if you get a more useful diagnostic message.

If push comes to shove, export your DTS package to a structured storage file and post it... Maybe someone here can see something in there that might help.

I'd exhaust every other avenue before even considering restoring master. That would be an absolute last ditch option for me.

-PatP|||Restoring MASTER would have no affect on success or failure of a DTS package. There NOTHING in MASTER that relates to DTS, NOTHING!..Having said that, - actually there is everything, - databases, logins, linked servers...I'd save the package as a BAS (not structured, Pat) and see what's going on there.|||When you restore the msdb database from a different server, u restore the jobs, dtspackages etc. Does any part of the system tables point to previous server. ? like sysjobs.

Help DTS Job programming

Hello:
I'm scheduling DTS packages to run as a job but when I do that, the job siply does not works, someone tolds me that it's a problem with the SQL and Windows NT authentication and security and he gaves me this URL http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q269074.
but it not so clear to me, i'm working with SQL Server 200 and Windows Server 2000.

What can I do?The job simply does not work ...
This means what are you getting any error?
If so review information from DTS package log and SQL error log or use PROFILER to monitor the activity.

That is the only KBA information available if you want to schedule a dts package.

Come with complete details about issue and setup.|||Always make sure you have the same drivers on the server as you do on your local machine if applicable. Don't know if that helps or not|||If you schedule the DTS package it will run in the context of sqlagent privileges and drivers on that server. And also the same fashion even though if you execute the package manually.|||I've seen this as a common problem when people are connecting to the server but running the package from a machine that does have appropriate drivers.

For poster - this may help, these guys are the bet in the biz with DTS - http://www.sqldts.com/default.aspx?222|||someone tolds me that it's a problem with the SQL and Windows NT authentication and security
BTW what is the current authentication set on SQL Server?

Help Designing an App. to be Run from a Job

Hello,

I'm working on a project that uses SQL Server 2005 and C# 2.0. I need some ideas of the best way to design/implement an application that will be executed from a SQL Server job.

The general idea is: a SQL Server job will call [something] and pass a couple of parameters to [something], then [something] will query a database and gather other information, then [something] will do some processing of data, and finally [something] will output the results for end user consumption.

The problem is that I don't know what [something] should be. Should it be a C# executable (but it can have no UI)? Should it be a web service? Should it be a console application (but, again, it cannot have a UI)? Should the job call a stored procedure, which in turn calls a C# assembly?

Basically, I'm just trying to get some ideas of the best way to design/implement a solution to my situation.

Thanks.

Hi,

without further information, I would suggest to use a c# console app called from the job.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

Thanks for the input.

What other information could I provide to help choose the best solution?

|||

Best thing is to call a Stored Proc from the Sql job

In the Stored proc gather all the parameters that you need and use BCP out to keep that data in a flat file.

There is no need to use Webservice or other C# program as this would cause an additional overhead.

|||

There are a number of things your description suggests, and others that it leaves open...

1 - from what you say, it looks like the something does not need access to the resources of the server (as a computer) and only needs access to data inside SQL Server. If this is true, then the use of a SQL Server job to invoke a stored procedure is the way to go (and in SQL Server 2005 you can write your stored procedure in C# if you like).

If, on the other hand, you need access to information outside SQL Server (files, I/O, Active Directory, other PCs, ...) you'll need an app running outside SQL Server (yes - you can invoke an app from inside SQL Server, but why jump through hoops when you don't need to?). If this is the road to take, use Windows' scheduler instead of SQL Server's scheduler.

If you're running inside SQL Server, as one of the other posts suggested, you'll have a Stored Proc to gather parameters, that likely will call another (or more) to do the work. If you're doing stuff outside the database server the app will likely invoke a stored proc to do the 'inside' work - so you're just partitioning where you do what work.

When returning the data to the user - there are a number of options that depend on various factors you've not mentioned as to which is better:

> simply write the data to an output table (with a timestamp on each row if you need to hold multiple day's / run's worth of output) - and the use can have a simple reporting app to read from it.

> If the volume of data is small SQL Server can email the recipient(s) - again, this is probably better if the number of recipients is small. You may also need to consider the security of the data as it travels as email.

> If you need to store the data outside the server (in a dated file, for example) you have to get it out, somehow. If your app is already running outside the server that's a no brainer; if everything is running inside the server I think you'll have to trigger some outside app to pull the data (i.e. I don't think there's a way for a SQL Server job to dump data to the outside world). DO NOT TAKE MY WORD for this - DTS may be invokable as a SQL Server job and may server to store data outside the server, or there may be other capabilities of SQL Server 2005 that I've not encountered yet.

> Worst comes to worst - you have a simple outside app that polls a table in the server for a 'ready' flag and then pulls the data. But that's ugly.

HTH

Griffin

sql

Help creating Embedded code Function

How can I create a VB.NET function to run my query below that my SSRS report can run to retreive TotalPostingDays so I can show that in a textbox in my report? I need help creating the function in the Report properties of my SSRS report and not sure how to call this stored procedure to return TotalPostingDays.

I need to do this calculation by using a UDF, not stored procedure maybe. The problem is, I do still need to do a lookup to my Holiday table as part of my UDF though; the rest can proabably be done in Straight VB for the weekend and other calculations:
LTER PROCEDURE SSRS_Return_TotalPostingDays

AS

DECLARE @.TotalDaysInMonth int,
@.today datetime,
@.TotalWeekendDays int,
@.TotalHolidaysThisMonth int,
@.TotalPostingDays int

SET @.today = GETDATE()

-- TOTAL DAYS THIS MONTH
SET @.TotalDaysInMonth = CASE WHEN DatePart(mm,GetDate()) IN (1,3,5,7,8,10,12) THEN
31
ELSE
DateDiff(day,GetDate(),DateAdd(mm, 1, GetDate()))
END


-- TOTAL HOLIDAYS THIS MONTH
SELECT @.TotalHolidaysThisMonth = (SELECT COUNT(*) FROM ReportingServer.dbo.Holidays
WHERE HolidayDate BETWEEN (DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today))
AND (DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))))

-- TOTAL # WEEKEND DAYS THIS MONTH

DECLARE @.date DATETIME
SET @.date = '20060101'

SELECT @.TotalWeekendDays = 8 +
CASE WHEN ISDATE(CONVERT(CHAR(6), @.date, 112) + '29') = 1 THEN
CASE WHEN DATENAME(WEEKDAY, CONVERT(CHAR(6), @.date, 112) + '01') IN ('Saturday', 'Sunday')
THEN 1 ELSE 0 END ELSE 0 END +
CASE WHEN ISDATE(CONVERT(CHAR(6), @.date, 112) + '30') = 1 THEN
CASE WHEN DATENAME(WEEKDAY, CONVERT(CHAR(6), @.date, 112) + '02') IN ('Saturday', 'Sunday')
THEN 1 ELSE 0 END ELSE 0 END +
CASE WHEN ISDATE(CONVERT(CHAR(6), @.date, 112) + '31') = 1 THEN
CASE WHEN DATENAME(WEEKDAY, CONVERT(CHAR(6), @.date, 112) + '03') IN ('Saturday', 'Sunday')
THEN 1 ELSE 0 END ELSE 0 END

SET @.TotalPostingDays = @.TotalDaysInMonth - (@.TotalHolidaysThisMonth + @.TotalWeekendDays)
RETURN @.TotalPostingDays


for the holiday lookup, I can probably do something like this then use the variable below to proceed or something:
Dim intTotalPostingDays As Integer
Dim objConn As New SqlConnection("Data Source=server;Initial Catalog=database; integrated security=SSPI;persist security info=False; Trusted_Connection=Yes")
Dim objComm As New SqlCommand("SSRS_Return_TotalHolidaysThisMonth", objConn)
objComm.CommandType = CommandType.StoredProcedure
Dim returnValueParam As New SqlClient.SqlParameter("@.RETURN_VALUE", SqlDbType.Int)
objComm.Parameters.Add(returnValueParam)
objComm.Connection.Open()
Dim objReader As SqlClient.SqlDataReader = objComm.ExecuteReader()
intTotalHolidays = returnValueParam.Value()

should I use executescalar instead of datareader? I am not sure where to go here for the entire function that I need so I can get this into my SSRS report.

I would do it a little more simply and call the stored procedure using the RS data source and query functionality. You can call the stored procedure and create a one row data set to use in your report.

Forgot to add you can have multiple datasets in your report, so you are not limited to this query.

|||actually, that's not a bad idea...thanks, will try it.|||

For some reason, completely forgot about datasets in my report! I had initially created one to run a Stored Proc as the DataSet...then I just added another to run this stored proc to return the field then added that field to a textbox and that was it!

thanks for refreshing my memory about datasource, which lead me to create a new dataset instead!

Help creating Embedded code Function

How can I create a VB.NET function to run my query below that my SSRS report can run to retreive TotalPostingDays so I can show that in a textbox in my report? I need help creating the function in the Report properties of my SSRS report and not sure how to call this stored procedure to return TotalPostingDays.

I need to do this calculation by using a UDF, not stored procedure maybe. The problem is, I do still need to do a lookup to my Holiday table as part of my UDF though; the rest can proabably be done in Straight VB for the weekend and other calculations:
LTER PROCEDURE SSRS_Return_TotalPostingDays

AS

DECLARE @.TotalDaysInMonth int,
@.today datetime,
@.TotalWeekendDays int,
@.TotalHolidaysThisMonth int,
@.TotalPostingDays int

SET @.today = GETDATE()

-- TOTAL DAYS THIS MONTH
SET @.TotalDaysInMonth = CASE WHEN DatePart(mm,GetDate()) IN (1,3,5,7,8,10,12) THEN
31
ELSE
DateDiff(day,GetDate(),DateAdd(mm, 1, GetDate()))
END


-- TOTAL HOLIDAYS THIS MONTH
SELECT @.TotalHolidaysThisMonth = (SELECT COUNT(*) FROM ReportingServer.dbo.Holidays
WHERE HolidayDate BETWEEN (DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today))
AND (DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))))

-- TOTAL # WEEKEND DAYS THIS MONTH

DECLARE @.date DATETIME
SET @.date = '20060101'

SELECT @.TotalWeekendDays = 8 +
CASE WHEN ISDATE(CONVERT(CHAR(6), @.date, 112) + '29') = 1 THEN
CASE WHEN DATENAME(WEEKDAY, CONVERT(CHAR(6), @.date, 112) + '01') IN ('Saturday', 'Sunday')
THEN 1 ELSE 0 END ELSE 0 END +
CASE WHEN ISDATE(CONVERT(CHAR(6), @.date, 112) + '30') = 1 THEN
CASE WHEN DATENAME(WEEKDAY, CONVERT(CHAR(6), @.date, 112) + '02') IN ('Saturday', 'Sunday')
THEN 1 ELSE 0 END ELSE 0 END +
CASE WHEN ISDATE(CONVERT(CHAR(6), @.date, 112) + '31') = 1 THEN
CASE WHEN DATENAME(WEEKDAY, CONVERT(CHAR(6), @.date, 112) + '03') IN ('Saturday', 'Sunday')
THEN 1 ELSE 0 END ELSE 0 END

SET @.TotalPostingDays = @.TotalDaysInMonth - (@.TotalHolidaysThisMonth + @.TotalWeekendDays)
RETURN @.TotalPostingDays


for the holiday lookup, I can probably do something like this then use the variable below to proceed or something:
Dim intTotalPostingDays As Integer
Dim objConn As New SqlConnection("Data Source=server;Initial Catalog=database; integrated security=SSPI;persist security info=False; Trusted_Connection=Yes")
Dim objComm As New SqlCommand("SSRS_Return_TotalHolidaysThisMonth", objConn)
objComm.CommandType = CommandType.StoredProcedure
Dim returnValueParam As New SqlClient.SqlParameter("@.RETURN_VALUE", SqlDbType.Int)
objComm.Parameters.Add(returnValueParam)
objComm.Connection.Open()
Dim objReader As SqlClient.SqlDataReader = objComm.ExecuteReader()
intTotalHolidays = returnValueParam.Value()

should I use executescalar instead of datareader? I am not sure where to go here for the entire function that I need so I can get this into my SSRS report.

I would do it a little more simply and call the stored procedure using the RS data source and query functionality. You can call the stored procedure and create a one row data set to use in your report.

Forgot to add you can have multiple datasets in your report, so you are not limited to this query.

|||actually, that's not a bad idea...thanks, will try it.|||

For some reason, completely forgot about datasets in my report! I had initially created one to run a Stored Proc as the DataSet...then I just added another to run this stored proc to return the field then added that field to a textbox and that was it!

thanks for refreshing my memory about datasource, which lead me to create a new dataset instead!

sql

Help creating a DELETE trigger.

I need to create a trigger that will run whenever a record is deleted. I
want the trigger to delete records from table2 where the value in the ID
field matches the value in the ID field of the record being deleted from
table1.
I'm new to triggers and am not sure how to set this up.
Thanks,
JohnTry,
create trigger tr_table1_del on table1
for delete
as
set nocount on
if exists(select * from deleted as d inner join table2 as t on d.[id] = t[id])
begin
delete table2
where exists(select * from deleted ad d where d.[id] = table2.[id])
if @.@.error != 0
begin
rollback transaction
raiserror('Error deleting rows in table2.', 16, 1)
return
end
end
go
AMB
"John Piotrowski" wrote:

> I need to create a trigger that will run whenever a record is deleted. I
> want the trigger to delete records from table2 where the value in the ID
> field matches the value in the ID field of the record being deleted from
> table1.
> I'm new to triggers and am not sure how to set this up.
> Thanks,
>
> John

Help batch script task stuck in a loop

I have a .bat file that calls a script file to log in to a server and upload a file to my pub account. When I run the .bat file it just executes the command over and over and never executes the script file. Does anyone know why? Am I missing something in my script or .bat file commands below?

my .bat file contains this>ftp -s:script.txt

my script file contains this:

open myserver.com
username
password
put W:\UPLOAD\doc.txt
bye
exit

Thanks,

Mike

Looping doesn't seem reminiscent of the batch file or the script you have listed here. I sounds like it may be due to some logic in your package. Any chance this is happening in your package?|||

It works now.

|||

Mike,

I have the same problem, can you post the solution?

Thanks.

Sebastian.

|||

Instead of using the batch file, I used a ftp script and called it using the Execute Process Task Editor:

Executable: C:\WINDOWS\system32\ftp.exe

Arguments: -s:"C:\Documents and Settings\me\Desktop\SQL 2005 DMS project-Prod\Download E-Apps\ftpscript.txt"

the ftpscript.txt file contains the following:

open hostname
me
password
Ascii
put filepath\name
bye
exit

sql

Help batch script task stuck in a loop

I have a .bat file that calls a script file to log in to a server and upload a file to my pub account. When I run the .bat file it just executes the command over and over and never executes the script file. Does anyone know why? Am I missing something in my script or .bat file commands below?

my .bat file contains this>ftp -s:script.txt

my script file contains this:

open myserver.com
username
password
put W:\UPLOAD\doc.txt
bye
exit

Thanks,

Mike

Looping doesn't seem reminiscent of the batch file or the script you have listed here. I sounds like it may be due to some logic in your package. Any chance this is happening in your package?|||

It works now.

|||

Mike,

I have the same problem, can you post the solution?

Thanks.

Sebastian.

|||

Instead of using the batch file, I used a ftp script and called it using the Execute Process Task Editor:

Executable: C:\WINDOWS\system32\ftp.exe

Arguments: -s:"C:\Documents and Settings\me\Desktop\SQL 2005 DMS project-Prod\Download E-Apps\ftpscript.txt"

the ftpscript.txt file contains the following:

open hostname
me
password
Ascii
put filepath\name
bye
exit

HELP ! - How can a run a sql script to install a DB in a MS SQL Server?

Hello everyone,
I am new in the area of developing and I want to run a couple of sql scripts, what is the procedure to run them if I am using a MS SQL Server?
Thanks,
ArmandoUse Query Analyzer.

Check out the Books On Line.

Cheers,

hmscott

Help - the replication Failed to Run

error i get from replication when i force them to run the process.
mircosoft sql - DMO(ODBC sql state:42000)
Error 14258: Cannot perform this operation while sqlserveragent is
starting.try again.
But i not restart any sqlserveragent And the sql server servies manager show
that all running.
hope can get asnwer as soon as posible .
thanks.
does this help?
http://support.microsoft.com/default.aspx/kb/303287
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"sam" <sam@.discussions.microsoft.com> wrote in message
news:04A2D6ED-C57E-49EF-8952-50E082F5109F@.microsoft.com...
> error i get from replication when i force them to run the process.
> mircosoft sql - DMO(ODBC sql state:42000)
> Error 14258: Cannot perform this operation while sqlserveragent is
> starting.try again.
> But i not restart any sqlserveragent And the sql server servies manager
> show
> that all running.
> hope can get asnwer as soon as posible .
> thanks.

Help - Simple Question

I am running a T-SQL query and if there are one or more records returned by
it I need to run another query. How does a newbie like me determine this or
how do I get the value of the COUNT function into a variable I can use
elsewhere in my sproc?
After I see that the first query has records I am going to use values from
it to run the second query and I assume I am going to use a cursor to
accomplish this. Thank you.>I am running a T-SQL query and if there are one or more records returned by
>it I need to run another query. How does a newbie like me determine this
>or how do I get the value of the COUNT function into a variable I can use
>elsewhere in my sproc?
DECLARE @.rc INT;
SELECT ... FROM table1 WHERE ... ;
SET @.rc = @.@.ROWCOUNT;
IF @.rc > 0
SELECT ... FROM table2 WHERE ... ;

> After I see that the first query has records I am going to use values from
> it to run the second query and I assume I am going to use a cursor to
> accomplish this. Thank you.
Ugh. I am sure what you are doing could be done with a simple join, rather
than a nested cursor of some kind, which should be avoided at all costs (in
most situations). If you can do a better job describing exactly what you
want to do (see http://www.aspfaq.com/5006 )... I am sure someone can help
you with a much more efficient, set-based approach.
A|||Without seeing your DDL - and the query - it's hard to say. Perhaps what
you want is a derived table or Common Table Expression.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Code Boy" <CodeBoy@.microsoft.com> wrote in message
news:%23HoXwxyVGHA.2208@.TK2MSFTNGP15.phx.gbl...
I am running a T-SQL query and if there are one or more records returned by
it I need to run another query. How does a newbie like me determine this or
how do I get the value of the COUNT function into a variable I can use
elsewhere in my sproc?
After I see that the first query has records I am going to use values from
it to run the second query and I assume I am going to use a cursor to
accomplish this. Thank you.|||Code
BOL's example
USE pubs
DECLARE @.RowCount int
EXEC sp_executesql
N'SELECT @.RowCount = COUNT(*) FROM authors',
N'@.RowCount int OUTPUT',
@.RowCount OUTPUT
RAISERROR ('Authors rowcount is %d', 0, 1, @.RowCount)
"Code Boy" <CodeBoy@.microsoft.com> wrote in message
news:%23HoXwxyVGHA.2208@.TK2MSFTNGP15.phx.gbl...
>I am running a T-SQL query and if there are one or more records returned by
>it I need to run another query. How does a newbie like me determine this
>or how do I get the value of the COUNT function into a variable I can use
>elsewhere in my sproc?
>
> After I see that the first query has records I am going to use values from
> it to run the second query and I assume I am going to use a cursor to
> accomplish this. Thank you.
>|||Thank, you this is enough to get me going again!
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23d26T0yVGHA.5580@.TK2MSFTNGP11.phx.gbl...
> DECLARE @.rc INT;
> SELECT ... FROM table1 WHERE ... ;
> SET @.rc = @.@.ROWCOUNT;
> IF @.rc > 0
> SELECT ... FROM table2 WHERE ... ;
>
> Ugh. I am sure what you are doing could be done with a simple join,
> rather than a nested cursor of some kind, which should be avoided at all
> costs (in most situations). If you can do a better job describing exactly
> what you want to do (see http://www.aspfaq.com/5006 )... I am sure someone
> can help you with a much more efficient, set-based approach.
> A
>|||Uri, wy do you resort to dynamic SQL here?

> Code
> BOL's example
> USE pubs
> DECLARE @.RowCount int
> EXEC sp_executesql
> N'SELECT @.RowCount = COUNT(*) FROM authors',
> N'@.RowCount int OUTPUT',
> @.RowCount OUTPUT
> RAISERROR ('Authors rowcount is %d', 0, 1, @.RowCount)|||u need to give more information on what is it that you want to select.
for a newbie try to unlearn whatever you have learnt about cursors :)
you need to look at the result set as a whole when you are processing and
not a single record ar a time.|||No attack :-)))) Yep , I was reading some article in the BOL and just put
this examle out .
It is probably time to go home after very long work day
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OsSRF3yVGHA.4340@.TK2MSFTNGP10.phx.gbl...
> Uri, wy do you resort to dynamic SQL here?
>
>|||I juz think Uri is trying to make a point.. And what would that be'|||Hello,
When you state:
> you need to look at the result set as a whole when you are processing and
> not a single record ar a time.
are you referring specifically to cursors or do you extend this thought to
everything
encompassed in a SELECT statement?I think your making a point but I want to
know
exactly what it is:)
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:4873AB17-3EF3-42B0-A389-C9927E899EC0@.microsoft.com...
> u need to give more information on what is it that you want to select.
> for a newbie try to unlearn whatever you have learnt about cursors :)
> you need to look at the result set as a whole when you are processing and
> not a single record ar a time.

Help - Simple Question

I am running a T-SQL query and if there are one or more records returned by
it I need to run another query. How does a newbie like me determine this or
how do I get the value of the COUNT function into a variable I can use
elsewhere in my sproc?
After I see that the first query has records I am going to use values from
it to run the second query and I assume I am going to use a cursor to
accomplish this. Thank you.>I am running a T-SQL query and if there are one or more records returned by
>it I need to run another query. How does a newbie like me determine this
>or how do I get the value of the COUNT function into a variable I can use
>elsewhere in my sproc?
DECLARE @.rc INT;
SELECT ... FROM table1 WHERE ... ;
SET @.rc = @.@.ROWCOUNT;
IF @.rc > 0
SELECT ... FROM table2 WHERE ... ;

> After I see that the first query has records I am going to use values from
> it to run the second query and I assume I am going to use a cursor to
> accomplish this. Thank you.
Ugh. I am sure what you are doing could be done with a simple join, rather
than a nested cursor of some kind, which should be avoided at all costs (in
most situations). If you can do a better job describing exactly what you
want to do (see http://www.aspfaq.com/5006 )... I am sure someone can help
you with a much more efficient, set-based approach.
A|||Without seeing your DDL - and the query - it's hard to say. Perhaps what
you want is a derived table or Common Table Expression.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Code Boy" <CodeBoy@.microsoft.com> wrote in message
news:%23HoXwxyVGHA.2208@.TK2MSFTNGP15.phx.gbl...
I am running a T-SQL query and if there are one or more records returned by
it I need to run another query. How does a newbie like me determine this or
how do I get the value of the COUNT function into a variable I can use
elsewhere in my sproc?
After I see that the first query has records I am going to use values from
it to run the second query and I assume I am going to use a cursor to
accomplish this. Thank you.|||Code
BOL's example
USE pubs
DECLARE @.RowCount int
EXEC sp_executesql
N'SELECT @.RowCount = COUNT(*) FROM authors',
N'@.RowCount int OUTPUT',
@.RowCount OUTPUT
RAISERROR ('Authors rowcount is %d', 0, 1, @.RowCount)
"Code Boy" <CodeBoy@.microsoft.com> wrote in message
news:%23HoXwxyVGHA.2208@.TK2MSFTNGP15.phx.gbl...
>I am running a T-SQL query and if there are one or more records returned by
>it I need to run another query. How does a newbie like me determine this
>or how do I get the value of the COUNT function into a variable I can use
>elsewhere in my sproc?
>
> After I see that the first query has records I am going to use values from
> it to run the second query and I assume I am going to use a cursor to
> accomplish this. Thank you.
>|||Thank, you this is enough to get me going again!
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:%23d26T0yVGHA.5580@.TK2MSFTNGP11.phx.gbl...
> DECLARE @.rc INT;
> SELECT ... FROM table1 WHERE ... ;
> SET @.rc = @.@.ROWCOUNT;
> IF @.rc > 0
> SELECT ... FROM table2 WHERE ... ;
>
> Ugh. I am sure what you are doing could be done with a simple join,
> rather than a nested cursor of some kind, which should be avoided at all
> costs (in most situations). If you can do a better job describing exactly
> what you want to do (see http://www.aspfaq.com/5006 )... I am sure someone
> can help you with a much more efficient, set-based approach.
> A
>|||Uri, wy do you resort to dynamic SQL here?

> Code
> BOL's example
> USE pubs
> DECLARE @.RowCount int
> EXEC sp_executesql
> N'SELECT @.RowCount = COUNT(*) FROM authors',
> N'@.RowCount int OUTPUT',
> @.RowCount OUTPUT
> RAISERROR ('Authors rowcount is %d', 0, 1, @.RowCount)|||u need to give more information on what is it that you want to select.
for a newbie try to unlearn whatever you have learnt about cursors
you need to look at the result set as a whole when you are processing and
not a single record ar a time.|||No attack :-)))) Yep , I was reading some article in the BOL and just put
this examle out .
It is probably time to go home after very long work day
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:OsSRF3yVGHA.4340@.TK2MSFTNGP10.phx.gbl...
> Uri, wy do you resort to dynamic SQL here?
>
>|||I juz think Uri is trying to make a point.. And what would that be'|||Hello,
When you state:
> you need to look at the result set as a whole when you are processing and
> not a single record ar a time.
are you referring specifically to cursors or do you extend this thought to
everything
encompassed in a SELECT statement?I think your making a point but I want to
know
exactly what it is
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:4873AB17-3EF3-42B0-A389-C9927E899EC0@.microsoft.com...
> u need to give more information on what is it that you want to select.
> for a newbie try to unlearn whatever you have learnt about cursors
> you need to look at the result set as a whole when you are processing and
> not a single record ar a time.

Help - Simple Question

I am running a T-SQL query and if there are one or more records returned by
it I need to run another query. How does a newbie like me determine this or
how do I get the value of the COUNT function into a variable I can use
elsewhere in my sproc?
After I see that the first query has records I am going to use values from
it to run the second query and I assume I am going to use a cursor to
accomplish this. Thank you.
>I am running a T-SQL query and if there are one or more records returned by
>it I need to run another query. How does a newbie like me determine this
>or how do I get the value of the COUNT function into a variable I can use
>elsewhere in my sproc?
DECLARE @.rc INT;
SELECT ... FROM table1 WHERE ... ;
SET @.rc = @.@.ROWCOUNT;
IF @.rc > 0
SELECT ... FROM table2 WHERE ... ;

> After I see that the first query has records I am going to use values from
> it to run the second query and I assume I am going to use a cursor to
> accomplish this. Thank you.
Ugh. I am sure what you are doing could be done with a simple join, rather
than a nested cursor of some kind, which should be avoided at all costs (in
most situations). If you can do a better job describing exactly what you
want to do (see http://www.aspfaq.com/5006 )... I am sure someone can help
you with a much more efficient, set-based approach.
A
|||Without seeing your DDL - and the query - it's hard to say. Perhaps what
you want is a derived table or Common Table Expression.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
"Code Boy" <CodeBoy@.microsoft.com> wrote in message
news:%23HoXwxyVGHA.2208@.TK2MSFTNGP15.phx.gbl...
I am running a T-SQL query and if there are one or more records returned by
it I need to run another query. How does a newbie like me determine this or
how do I get the value of the COUNT function into a variable I can use
elsewhere in my sproc?
After I see that the first query has records I am going to use values from
it to run the second query and I assume I am going to use a cursor to
accomplish this. Thank you.
|||Code
BOL's example
USE pubs
DECLARE @.RowCount int
EXEC sp_executesql
N'SELECT @.RowCount = COUNT(*) FROM authors',
N'@.RowCount int OUTPUT',
@.RowCount OUTPUT
RAISERROR ('Authors rowcount is %d', 0, 1, @.RowCount)
"Code Boy" <CodeBoy@.microsoft.com> wrote in message
news:%23HoXwxyVGHA.2208@.TK2MSFTNGP15.phx.gbl...
>I am running a T-SQL query and if there are one or more records returned by
>it I need to run another query. How does a newbie like me determine this
>or how do I get the value of the COUNT function into a variable I can use
>elsewhere in my sproc?
>
> After I see that the first query has records I am going to use values from
> it to run the second query and I assume I am going to use a cursor to
> accomplish this. Thank you.
>
|||Thank, you this is enough to get me going again!
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23d26T0yVGHA.5580@.TK2MSFTNGP11.phx.gbl...
> DECLARE @.rc INT;
> SELECT ... FROM table1 WHERE ... ;
> SET @.rc = @.@.ROWCOUNT;
> IF @.rc > 0
> SELECT ... FROM table2 WHERE ... ;
>
> Ugh. I am sure what you are doing could be done with a simple join,
> rather than a nested cursor of some kind, which should be avoided at all
> costs (in most situations). If you can do a better job describing exactly
> what you want to do (see http://www.aspfaq.com/5006 )... I am sure someone
> can help you with a much more efficient, set-based approach.
> A
>
|||Uri, wy do you resort to dynamic SQL here?

> Code
> BOL's example
> USE pubs
> DECLARE @.RowCount int
> EXEC sp_executesql
> N'SELECT @.RowCount = COUNT(*) FROM authors',
> N'@.RowCount int OUTPUT',
> @.RowCount OUTPUT
> RAISERROR ('Authors rowcount is %d', 0, 1, @.RowCount)
|||u need to give more information on what is it that you want to select.
for a newbie try to unlearn whatever you have learnt about cursors
you need to look at the result set as a whole when you are processing and
not a single record ar a time.
|||No attack :-)))) Yep , I was reading some article in the BOL and just put
this examle out .
It is probably time to go home after very long work day
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OsSRF3yVGHA.4340@.TK2MSFTNGP10.phx.gbl...
> Uri, wy do you resort to dynamic SQL here?
>
>
|||I juz think Uri is trying to make a point.. And what would that be?
|||Hello,
When you state:
> you need to look at the result set as a whole when you are processing and
> not a single record ar a time.
are you referring specifically to cursors or do you extend this thought to
everything
encompassed in a SELECT statement?I think your making a point but I want to
know
exactly what it is
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:4873AB17-3EF3-42B0-A389-C9927E899EC0@.microsoft.com...
> u need to give more information on what is it that you want to select.
> for a newbie try to unlearn whatever you have learnt about cursors
> you need to look at the result set as a whole when you are processing and
> not a single record ar a time.

Help - Simple Question

I am running a T-SQL query and if there are one or more records returned by
it I need to run another query. How does a newbie like me determine this or
how do I get the value of the COUNT function into a variable I can use
elsewhere in my sproc?
After I see that the first query has records I am going to use values from
it to run the second query and I assume I am going to use a cursor to
accomplish this. Thank you.>I am running a T-SQL query and if there are one or more records returned by
>it I need to run another query. How does a newbie like me determine this
>or how do I get the value of the COUNT function into a variable I can use
>elsewhere in my sproc?
DECLARE @.rc INT;
SELECT ... FROM table1 WHERE ... ;
SET @.rc = @.@.ROWCOUNT;
IF @.rc > 0
SELECT ... FROM table2 WHERE ... ;
> After I see that the first query has records I am going to use values from
> it to run the second query and I assume I am going to use a cursor to
> accomplish this. Thank you.
Ugh. I am sure what you are doing could be done with a simple join, rather
than a nested cursor of some kind, which should be avoided at all costs (in
most situations). If you can do a better job describing exactly what you
want to do (see http://www.aspfaq.com/5006 )... I am sure someone can help
you with a much more efficient, set-based approach.
A|||Without seeing your DDL - and the query - it's hard to say. Perhaps what
you want is a derived table or Common Table Expression.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Code Boy" <CodeBoy@.microsoft.com> wrote in message
news:%23HoXwxyVGHA.2208@.TK2MSFTNGP15.phx.gbl...
I am running a T-SQL query and if there are one or more records returned by
it I need to run another query. How does a newbie like me determine this or
how do I get the value of the COUNT function into a variable I can use
elsewhere in my sproc?
After I see that the first query has records I am going to use values from
it to run the second query and I assume I am going to use a cursor to
accomplish this. Thank you.|||Thank, you this is enough to get me going again!
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23d26T0yVGHA.5580@.TK2MSFTNGP11.phx.gbl...
> >I am running a T-SQL query and if there are one or more records returned
> >by it I need to run another query. How does a newbie like me determine
> >this or how do I get the value of the COUNT function into a variable I
> >can use elsewhere in my sproc?
> DECLARE @.rc INT;
> SELECT ... FROM table1 WHERE ... ;
> SET @.rc = @.@.ROWCOUNT;
> IF @.rc > 0
> SELECT ... FROM table2 WHERE ... ;
>> After I see that the first query has records I am going to use values
>> from it to run the second query and I assume I am going to use a cursor
>> to accomplish this. Thank you.
> Ugh. I am sure what you are doing could be done with a simple join,
> rather than a nested cursor of some kind, which should be avoided at all
> costs (in most situations). If you can do a better job describing exactly
> what you want to do (see http://www.aspfaq.com/5006 )... I am sure someone
> can help you with a much more efficient, set-based approach.
> A
>|||Uri, wy do you resort to dynamic SQL here?
> Code
> BOL's example
> USE pubs
> DECLARE @.RowCount int
> EXEC sp_executesql
> N'SELECT @.RowCount = COUNT(*) FROM authors',
> N'@.RowCount int OUTPUT',
> @.RowCount OUTPUT
> RAISERROR ('Authors rowcount is %d', 0, 1, @.RowCount)|||u need to give more information on what is it that you want to select.
for a newbie try to unlearn whatever you have learnt about cursors :)
you need to look at the result set as a whole when you are processing and
not a single record ar a time.|||No attack :-)))) Yep , I was reading some article in the BOL and just put
this examle out .
It is probably time to go home after very long work day
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OsSRF3yVGHA.4340@.TK2MSFTNGP10.phx.gbl...
> Uri, wy do you resort to dynamic SQL here?
>
>> Code
>> BOL's example
>> USE pubs
>> DECLARE @.RowCount int
>> EXEC sp_executesql
>> N'SELECT @.RowCount = COUNT(*) FROM authors',
>> N'@.RowCount int OUTPUT',
>> @.RowCount OUTPUT
>> RAISERROR ('Authors rowcount is %d', 0, 1, @.RowCount)
>|||I juz think Uri is trying to make a point.. And what would that be'|||Code
BOL's example
USE pubs
DECLARE @.RowCount int
EXEC sp_executesql
N'SELECT @.RowCount = COUNT(*) FROM authors',
N'@.RowCount int OUTPUT',
@.RowCount OUTPUT
RAISERROR ('Authors rowcount is %d', 0, 1, @.RowCount)
"Code Boy" <CodeBoy@.microsoft.com> wrote in message
news:%23HoXwxyVGHA.2208@.TK2MSFTNGP15.phx.gbl...
>I am running a T-SQL query and if there are one or more records returned by
>it I need to run another query. How does a newbie like me determine this
>or how do I get the value of the COUNT function into a variable I can use
>elsewhere in my sproc?
>
> After I see that the first query has records I am going to use values from
> it to run the second query and I assume I am going to use a cursor to
> accomplish this. Thank you.
>|||Hello,
When you state:
> you need to look at the result set as a whole when you are processing and
> not a single record ar a time.
are you referring specifically to cursors or do you extend this thought to
everything
encompassed in a SELECT statement?I think your making a point but I want to
know
exactly what it is:)
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:4873AB17-3EF3-42B0-A389-C9927E899EC0@.microsoft.com...
> u need to give more information on what is it that you want to select.
> for a newbie try to unlearn whatever you have learnt about cursors :)
> you need to look at the result set as a whole when you are processing and
> not a single record ar a time.

Help - Rebuild System Databases on a clustered instance

I am in a DR senario and I am having trouble rebuilding the system databases on my SQL 2005 cluster.

I run the following string:

start /wait setup.exe /qn VS=Vname INSTANCENAME=instname REINSTALL=SQL_Engine REBUILDDATABASE=1 ADMINPASSWORD=password SQLACCOUNT=domain\acct SQLPASSWORD=password AGTACCOUNT=domain\acct AGTPASSWORD=password REINSTALLMODE=vomus

The setup fails with the following error:

Microsoft SQL Server 2005 9.00.2047.00
==============================
OS Version : Microsoft Windows Server 2003 family, Service Pack 1 (Build 3790)
Time : Wed Dec 13 15:41:44 2006
CLUSTERSVR1 : To install Microsoft SQL Server 2005, COM+ should work.
CLUSTERSVR1 : To change an existing instance of Microsoft SQL Server 2005 to a different edition of SQL Server 2005, you must run SQL Server 2005 Setup from the command prompt and include the SKUUPGRADE=1 parameter.
CLUSTERSVR1 : To install Microsoft SQL Server 2005, COM+ should work.
CLUSTERSVR1 : To change an existing instance of Microsoft SQL Server 2005 to a different edition of SQL Server 2005, you must run SQL Server 2005 Setup from the command prompt and include the SKUUPGRADE=1 parameter.
Machine : CLUSTERSVR1
Product : Microsoft SQL Server 2005
Product Version : 9.00.1399.06
Install : Failed
Error Number : 5897

SQL 2005 was originally installed in May '06 as RTM. SP1 applied in September '06.

I have the original RTM install disk but obviously SP1 is causing rebuild to fail.

Can anyone advise on this please.

D.

Can you apply SP1 and then retry the procedure?

I mean doing the setup of 2005, applying SP1 and then retrying?

|||

JohDas,

Unfortunately I can't.

Don't forget I do not have any databases - system or user. Therefore SP1 cannot authenticate to SQL Server to process the upgrade.

I have a bad feeling that my only option is to uninstall/reinstall SQL 2005.

But this is a last resort and I want to avaoid it if I can.

D.