Ok gurus here is somethign that I cant explane. I am haveing serious eratic responses from my SQL 7.0 Server. I can take a stored procedure and run it in a query analyzer window and it works fine, I go to rerun the same code in that window a second time and get no return set. The temp table is null. open a new window and it works fine. This code rand like clock work two weeks ago and there have been no updates to the code. I am on an old server wich has given me many hardware head aches in the past....am I having a hardware falure or is this a SQL issue?
JimRE:
I am haveing serious eratic responses from my SQL 7.0 Server. I can take a stored procedure and run it in a query analyzer window and it works fine, I go to rerun the same code in that window a second time and get no return set. The temp table is null. open a new window and it works fine. This code ran like clock work two weeks ago and there have been no updates to the code. I am on an old server wich has given me many hardware head aches in the past....
Q1 Am I having a hardware falure or is this a SQL issue? Jim
A1 From the information / background given, it may be a resource stressed sql server, sick / dying hardware, connectivity issues, possibly workstation issues, or worse, some combination of ills. It may be helpful to characterize the issue further in multiple ways, e.g.(test for the issue from other workstations, monitor with profiler, check server array and other hardware health, etc., etc., etc.).|||Originally posted by DBA
A1 From the information / background given, it may be a resource stressed sql server, sick / dying hardware, connectivity issues, possibly workstation issues, or worse, some combination of ills. It may be helpful to characterize the issue further in multiple ways, e.g.(test for the issue from other workstations, monitor with profiler, check server array and other hardware health, etc., etc., etc.).
the network admin isnt willing to test the box, he says it sql
the problem persists on multiple workstations, this is quad processer with 4 gig of ram and the resorce monitor says that the processors and mem are not near maxed out.........I think I may have some courupt indexes. I had a maintenance plan boom on me in the middle of this db and I think that hurt my indexes. Is that something that can happen?
Jim
PS Fixed the above mentioned stored procedure now just haveing horendious respons time
Jim|||RE:
the network admin isnt willing to test the box, he says it sql
My that's helpful, what if it is actually both?
RE:
Q1 the problem persists on multiple workstations, this is quad processer with 4 gig of ram and the resorce monitor says that the processors and mem are not near maxed out.........I think I may have some courupt indexes. I had a maintenance plan boom on me in the middle of this db and I think that hurt my indexes. Is that something that can happen? Jim PS Fixed the above mentioned stored procedure now just haveing horendious respons time Jim
A1 Consider running dbcc checkdb; them repair (or drop / recreate) any corrupt indicies found. Corrupting indicies would depend on the nature of "boom". Corrupt indexes may account for long response times.|||Originally posted by DBA
My that's helpful, what if it is actually both?
A1 Consider running dbcc checkdb; them repair (or drop / recreate) any corrupt indicies found. Corrupting indicies would depend on the nature of "boom". Corrupt indexes may account for long response times.
Running dbcc checkdb now...can you give me a guess to how long it will run on a 60ish gig db?
Jim|||Originally posted by JDionne
Q1 Running dbcc checkdb now...can you give me a guess to how long it will run on a 60ish gig db?
Jim
A1 Wouldn't want to hazard a guess (depends on to many variables specific to the environment such as disk access rates, how much corruption exists, number of indexes, etc., etc., etc.).
It is possible to get an estimate of how much tempdb space may be required to checkalloc and chekctables via:
dbcc checkdb With EstimateOnly
Hopefully some of this was helpful (any hardware / corruption or other issues identified)?|||Originally posted by DBA
A1 Wouldn't want to hazard a guess (depends on to many variables specific to the environment such as disk access rates, how much corruption exists, number of indexes, etc., etc., etc.).
It is possible to get an estimate of how much tempdb space may be required to checkalloc and chekctables via:
dbcc checkdb With EstimateOnly
Hopefully some of this was helpful (any hardware / corruption or other issues identified)?
as of now the checkdb showed no errors, and there supposidly is no hardware problems...we have rebuilt a few tables and indexes and It seems to be a bit better...rewriten known code a diff way and that has seemed to help a bit to. We also found a corupt data load file and we are hopeing to get that fixed soon. Thanx for your help...It has been invaluable.
Jim
Showing posts with label procedure. Show all posts
Showing posts with label procedure. Show all posts
Monday, March 19, 2012
Sunday, March 11, 2012
Blocking Threshold Exceeded
I have a site that calls a stored procedure to populate data on an ASP page.
50% of the time when I access this page I get a timeout error. I looked in
the Event Viewer and noticed MSSQLSERVER errors with this description:
Error: 50001, Severity: 16, State: 1
Blocking Threshold Exceeded, Threshold at 3. Total Blockers at 44
The total blockers number changes occasionaly but I don't know what this
means and how to fix it. I am not doing anything complex with the stored
procedure. It is a select statement which joins multiple tables. No
inserts, updates, or deletes are being done.
Does anyone have an idea how to correct this?
Thanks.Even though the procedure that performs the query is not
inserting/updating/deleting, is this still being done by any process? When
it actually completes, what is the average and maximum runtime of the query?
Also, the following article describes various methods for identifying what
processes are blocked, what process is doing the blocking, and what specific
T-SQL statement is blocking.
INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
http://support.microsoft.com/defaul...kb;EN-US;224453
If this is a reporting type query against data potentially with uncommitted
transactions, then you may want to consider using "set transaction isolation
level read uncommitted", so long as you understand how this can impact the
results. Read up on this in Books Online.
"Jason" <Jason@.discussions.microsoft.com> wrote in message
news:A1346683-78D7-40CE-9F7F-3AE8521D392F@.microsoft.com...
>I have a site that calls a stored procedure to populate data on an ASP
>page.
> 50% of the time when I access this page I get a timeout error. I looked
> in
> the Event Viewer and noticed MSSQLSERVER errors with this description:
> Error: 50001, Severity: 16, State: 1
> Blocking Threshold Exceeded, Threshold at 3. Total Blockers at 44
> The total blockers number changes occasionaly but I don't know what this
> means and how to fix it. I am not doing anything complex with the stored
> procedure. It is a select statement which joins multiple tables. No
> inserts, updates, or deletes are being done.
> Does anyone have an idea how to correct this?
> Thanks.
>
50% of the time when I access this page I get a timeout error. I looked in
the Event Viewer and noticed MSSQLSERVER errors with this description:
Error: 50001, Severity: 16, State: 1
Blocking Threshold Exceeded, Threshold at 3. Total Blockers at 44
The total blockers number changes occasionaly but I don't know what this
means and how to fix it. I am not doing anything complex with the stored
procedure. It is a select statement which joins multiple tables. No
inserts, updates, or deletes are being done.
Does anyone have an idea how to correct this?
Thanks.Even though the procedure that performs the query is not
inserting/updating/deleting, is this still being done by any process? When
it actually completes, what is the average and maximum runtime of the query?
Also, the following article describes various methods for identifying what
processes are blocked, what process is doing the blocking, and what specific
T-SQL statement is blocking.
INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
http://support.microsoft.com/defaul...kb;EN-US;224453
If this is a reporting type query against data potentially with uncommitted
transactions, then you may want to consider using "set transaction isolation
level read uncommitted", so long as you understand how this can impact the
results. Read up on this in Books Online.
"Jason" <Jason@.discussions.microsoft.com> wrote in message
news:A1346683-78D7-40CE-9F7F-3AE8521D392F@.microsoft.com...
>I have a site that calls a stored procedure to populate data on an ASP
>page.
> 50% of the time when I access this page I get a timeout error. I looked
> in
> the Event Viewer and noticed MSSQLSERVER errors with this description:
> Error: 50001, Severity: 16, State: 1
> Blocking Threshold Exceeded, Threshold at 3. Total Blockers at 44
> The total blockers number changes occasionaly but I don't know what this
> means and how to fix it. I am not doing anything complex with the stored
> procedure. It is a select statement which joins multiple tables. No
> inserts, updates, or deletes are being done.
> Does anyone have an idea how to correct this?
> Thanks.
>
Wednesday, March 7, 2012
Blocked Transactions
I was wanting to write a stored procedure which would identify queries which
are currently being blocked (spids 'blocked by' some other spid) when the
stored procedure is being run.
Is this possible? How do I go about figuring out how to do this?This query will get you the head of the blocking chain...
Declare @.SPID Varchar(500)
Select @.SPID = COALESCE(@.SPID + ',', '') + Cast(a.spid as varchar(20))
From master.dbo.sysprocesses a
where a.blocked = 0 and
a.spid IN (Select b.blocked From master.dbo.sysprocesses b Where b.blocked
!= 0)
Select @.SPID
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Jim Heavey" <JimHeavey@.hotmail.com> wrote in message
news:O$u7BzDmDHA.2080@.TK2MSFTNGP10.phx.gbl...
> I was wanting to write a stored procedure which would identify queries
which
> are currently being blocked (spids 'blocked by' some other spid) when the
> stored procedure is being run.
> Is this possible? How do I go about figuring out how to do this?
>|||Heavey
Just as a starting point, I found that sysprocesses may be a good
place to start. I don't know what this does or anything, but it
looked relevant to the task:
> Select distinct spid, blocked, dbid from master..sysprocesses where blocked
>!= 0 for read only
>open DBA_lockinfo
>Declare @.spid varchar(5)
>Declare @.blocked varchar(5)
>Declare @.dbid varchar(10)
>Declare @.msg varchar (50)
>Declare @.event varchar(500)
>Declare @.event2 varchar(500)
>Create table #aux (EventType varchar(100), Parameters varchar(100),
>EventInfo varchar(500))
>Create table #aux2 (EventType varchar(100), Parameters varchar(100),
>EventInfo varchar(500))
>fetch next from DBA_lockinfo into @.spid, @.blocked, @.dbid
>While @.@.fetch_status = 0
>Begin
>Insert into #aux exec ('dbcc inputbuffer (' + @.spid + ')')
>Insert into #aux2 exec ('dbcc inputbuffer (' + @.blocked + ')')
>Set @.event = (select EventInfo from #aux)
>Set @.event2 = (select EventInfo from #aux2)
Good luck! Shoot me an email if you get a handle on it.
-Toby
On Tue, 21 Oct 2003 20:35:23 -0500, "Jim Heavey"
<JimHeavey@.hotmail.com> wrote:
>I was wanting to write a stored procedure which would identify queries which
>are currently being blocked (spids 'blocked by' some other spid) when the
>stored procedure is being run.
>Is this possible? How do I go about figuring out how to do this?
>|||Here is something I use. Show blocked spid and the path down to the one
causing all the waiting.
ALTER PROCEDURE dbo.ListBlockedTransactions
( @.ShowResults int = 1
)
as
SET NOCOUNT ON
-- created by M. Thomas Groszko
DECLARE BlockedSPIDSCursor CURSOR
FAST_FORWARD
FOR
SELECT BASE.SPID,
BASE.BLOCKED,
BASE.WAITTIME,
BASE.LASTWAITTYPE BLOCKED_LASTWAITTYPE,
CASE WHEN [CORPORATEDIRECTORY].[dbo].[ConcatenateName](SCD.LASTNAME,
SCD.FIRSTNAME, SCD.MIDDLENAME, SCD.PREFERREDNAME) IS NOT NULL
THEN rtrim(BASE.hostname) + '-' +
[CORPORATEDIRECTORY].[dbo].[ConcatenateName](SCD.LASTNAME, SCD.FIRSTNAME,
SCD.MIDDLENAME, SCD.PREFERREDNAME)
ELSE rtrim(BASE.hostname)
END BLOCKED_HOST
FROM SYSPROCESSES BASE
LEFT OUTER JOIN PCDB.dbo.PC PCBASE ON (BASE.hostname = PCBASE.MACHINEID)
LEFT OUTER JOIN PCDB.DBO.PERSON PERSONBASE ON (PCBASE.PERSONID =PERSONBASE.PERSONID)
LEFT JOIN CORPORATEDIRECTORY.dbo.PERSON SCD ON (PERSONBASE.CDID =SCD.PERSONID)
WHERE BASE.BLOCKED <> 0
ORDER BY BASE.last_batch DESC
FOR READ ONLY
DECLARE @.SampleTime DATETIME
SET @.SampleTime = GETDATE()
DECLARE @.BLOCKED_SPID INT
DECLARE @.BLOCKED_BLOCKEDBY INT
DECLARE @.BLOCKED_WAITTIME INT
DECLARE @.BLOCKED_LASTWAITTYPE VARCHAR(32)
DECLARE @.BLOCKED_HOST VARCHAR(255)
OPEN BlockedSPIDSCursor
FETCH BlockedSPIDSCursor INTO
@.BLOCKED_SPID,
@.BLOCKED_BLOCKEDBY,
@.BLOCKED_WAITTIME,
@.BLOCKED_LASTWAITTYPE,
@.BLOCKED_HOST
WHILE @.@.FETCH_STATUS = 0 -- while fetch returns a row
BEGIN
EXEC dbo.ShowBlockingSPID @.SampleTime, @.BLOCKED_BLOCKEDBY, 1
FETCH BlockedSPIDSCursor INTO
@.BLOCKED_SPID,
@.BLOCKED_BLOCKEDBY,
@.BLOCKED_WAITTIME,
@.BLOCKED_LASTWAITTYPE,
@.BLOCKED_HOST
END
CLOSE BlockedSPIDSCursor
DEALLOCATE BlockedSPIDSCursor
RETURN 0
go
ALTER PROCEDURE dbo.ShowBlockingSPID
( @.SampleTime DATETIME,
@.BlockingSPID int,
@.NestLevel int
)
as
SET NOCOUNT ON
-- created by M. Thomas Groszko
DECLARE @.BLOCKING_SPID INT
DECLARE @.BLOCKING_BLOCKEDBY INT
DECLARE @.BLOCKING_WAITTIME INT
DECLARE @.BLOCKING_LASTWAITTYPE VARCHAR(32)
DECLARE @.BLOCKING_HOST VARCHAR(255)
DECLARE @.NESTNEXTLEVEL INT
SELECT @.BLOCKING_SPID = BLOCKING.SPID,
@.BLOCKING_BLOCKEDBY = BLOCKING.BLOCKED,
@.BLOCKING_WAITTIME = BLOCKING.WAITTIME,
@.BLOCKING_LASTWAITTYPE = BLOCKING.LASTWAITTYPE,
@.BLOCKING_HOST =
CASE WHEN [CORPORATEDIRECTORY].[dbo].[ConcatenateName](SCD.LASTNAME,
SCD.FIRSTNAME, SCD.MIDDLENAME, SCD.PREFERREDNAME) IS NOT NULL
THEN rtrim(BLOCKING.hostname) + '-' +
[CORPORATEDIRECTORY].[dbo].[ConcatenateName](SCD.LASTNAME, SCD.FIRSTNAME,
SCD.MIDDLENAME, SCD.PREFERREDNAME)
ELSE rtrim(BLOCKING.hostname)
END
FROM SYSPROCESSES BLOCKING
LEFT OUTER JOIN PCDB.dbo.PC PCBLOCKING ON (BLOCKING.hostname =PCBLOCKING.MACHINEID)
LEFT OUTER JOIN PCDB.DBO.PERSON PERSONBLOCKING ON (PCBLOCKING.PERSONID =PERSONBLOCKING.PERSONID)
LEFT JOIN CORPORATEDIRECTORY.dbo.PERSON SCD ON (PERSONBLOCKING.CDID =SCD.PERSONID)
WHERE BLOCKING.SPID = @.BlockingSPID
IF @.BLOCKING_BLOCKEDBY <> 0
BEGIN
SET @.NESTNEXTLEVEL = @.NestLevel + 1
EXEC dbo.ShowBlockingSPID @.SampleTime, @.BLOCKING_BLOCKEDBY, @.NESTNEXTLEVEL
END
return 0
go
"Jim Heavey" <JimHeavey@.hotmail.com> wrote in message
news:O$u7BzDmDHA.2080@.TK2MSFTNGP10.phx.gbl...
> I was wanting to write a stored procedure which would identify queries
which
> are currently being blocked (spids 'blocked by' some other spid) when the
> stored procedure is being run.
> Is this possible? How do I go about figuring out how to do this?
>
are currently being blocked (spids 'blocked by' some other spid) when the
stored procedure is being run.
Is this possible? How do I go about figuring out how to do this?This query will get you the head of the blocking chain...
Declare @.SPID Varchar(500)
Select @.SPID = COALESCE(@.SPID + ',', '') + Cast(a.spid as varchar(20))
From master.dbo.sysprocesses a
where a.blocked = 0 and
a.spid IN (Select b.blocked From master.dbo.sysprocesses b Where b.blocked
!= 0)
Select @.SPID
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Jim Heavey" <JimHeavey@.hotmail.com> wrote in message
news:O$u7BzDmDHA.2080@.TK2MSFTNGP10.phx.gbl...
> I was wanting to write a stored procedure which would identify queries
which
> are currently being blocked (spids 'blocked by' some other spid) when the
> stored procedure is being run.
> Is this possible? How do I go about figuring out how to do this?
>|||Heavey
Just as a starting point, I found that sysprocesses may be a good
place to start. I don't know what this does or anything, but it
looked relevant to the task:
> Select distinct spid, blocked, dbid from master..sysprocesses where blocked
>!= 0 for read only
>open DBA_lockinfo
>Declare @.spid varchar(5)
>Declare @.blocked varchar(5)
>Declare @.dbid varchar(10)
>Declare @.msg varchar (50)
>Declare @.event varchar(500)
>Declare @.event2 varchar(500)
>Create table #aux (EventType varchar(100), Parameters varchar(100),
>EventInfo varchar(500))
>Create table #aux2 (EventType varchar(100), Parameters varchar(100),
>EventInfo varchar(500))
>fetch next from DBA_lockinfo into @.spid, @.blocked, @.dbid
>While @.@.fetch_status = 0
>Begin
>Insert into #aux exec ('dbcc inputbuffer (' + @.spid + ')')
>Insert into #aux2 exec ('dbcc inputbuffer (' + @.blocked + ')')
>Set @.event = (select EventInfo from #aux)
>Set @.event2 = (select EventInfo from #aux2)
Good luck! Shoot me an email if you get a handle on it.
-Toby
On Tue, 21 Oct 2003 20:35:23 -0500, "Jim Heavey"
<JimHeavey@.hotmail.com> wrote:
>I was wanting to write a stored procedure which would identify queries which
>are currently being blocked (spids 'blocked by' some other spid) when the
>stored procedure is being run.
>Is this possible? How do I go about figuring out how to do this?
>|||Here is something I use. Show blocked spid and the path down to the one
causing all the waiting.
ALTER PROCEDURE dbo.ListBlockedTransactions
( @.ShowResults int = 1
)
as
SET NOCOUNT ON
-- created by M. Thomas Groszko
DECLARE BlockedSPIDSCursor CURSOR
FAST_FORWARD
FOR
SELECT BASE.SPID,
BASE.BLOCKED,
BASE.WAITTIME,
BASE.LASTWAITTYPE BLOCKED_LASTWAITTYPE,
CASE WHEN [CORPORATEDIRECTORY].[dbo].[ConcatenateName](SCD.LASTNAME,
SCD.FIRSTNAME, SCD.MIDDLENAME, SCD.PREFERREDNAME) IS NOT NULL
THEN rtrim(BASE.hostname) + '-' +
[CORPORATEDIRECTORY].[dbo].[ConcatenateName](SCD.LASTNAME, SCD.FIRSTNAME,
SCD.MIDDLENAME, SCD.PREFERREDNAME)
ELSE rtrim(BASE.hostname)
END BLOCKED_HOST
FROM SYSPROCESSES BASE
LEFT OUTER JOIN PCDB.dbo.PC PCBASE ON (BASE.hostname = PCBASE.MACHINEID)
LEFT OUTER JOIN PCDB.DBO.PERSON PERSONBASE ON (PCBASE.PERSONID =PERSONBASE.PERSONID)
LEFT JOIN CORPORATEDIRECTORY.dbo.PERSON SCD ON (PERSONBASE.CDID =SCD.PERSONID)
WHERE BASE.BLOCKED <> 0
ORDER BY BASE.last_batch DESC
FOR READ ONLY
DECLARE @.SampleTime DATETIME
SET @.SampleTime = GETDATE()
DECLARE @.BLOCKED_SPID INT
DECLARE @.BLOCKED_BLOCKEDBY INT
DECLARE @.BLOCKED_WAITTIME INT
DECLARE @.BLOCKED_LASTWAITTYPE VARCHAR(32)
DECLARE @.BLOCKED_HOST VARCHAR(255)
OPEN BlockedSPIDSCursor
FETCH BlockedSPIDSCursor INTO
@.BLOCKED_SPID,
@.BLOCKED_BLOCKEDBY,
@.BLOCKED_WAITTIME,
@.BLOCKED_LASTWAITTYPE,
@.BLOCKED_HOST
WHILE @.@.FETCH_STATUS = 0 -- while fetch returns a row
BEGIN
EXEC dbo.ShowBlockingSPID @.SampleTime, @.BLOCKED_BLOCKEDBY, 1
FETCH BlockedSPIDSCursor INTO
@.BLOCKED_SPID,
@.BLOCKED_BLOCKEDBY,
@.BLOCKED_WAITTIME,
@.BLOCKED_LASTWAITTYPE,
@.BLOCKED_HOST
END
CLOSE BlockedSPIDSCursor
DEALLOCATE BlockedSPIDSCursor
RETURN 0
go
ALTER PROCEDURE dbo.ShowBlockingSPID
( @.SampleTime DATETIME,
@.BlockingSPID int,
@.NestLevel int
)
as
SET NOCOUNT ON
-- created by M. Thomas Groszko
DECLARE @.BLOCKING_SPID INT
DECLARE @.BLOCKING_BLOCKEDBY INT
DECLARE @.BLOCKING_WAITTIME INT
DECLARE @.BLOCKING_LASTWAITTYPE VARCHAR(32)
DECLARE @.BLOCKING_HOST VARCHAR(255)
DECLARE @.NESTNEXTLEVEL INT
SELECT @.BLOCKING_SPID = BLOCKING.SPID,
@.BLOCKING_BLOCKEDBY = BLOCKING.BLOCKED,
@.BLOCKING_WAITTIME = BLOCKING.WAITTIME,
@.BLOCKING_LASTWAITTYPE = BLOCKING.LASTWAITTYPE,
@.BLOCKING_HOST =
CASE WHEN [CORPORATEDIRECTORY].[dbo].[ConcatenateName](SCD.LASTNAME,
SCD.FIRSTNAME, SCD.MIDDLENAME, SCD.PREFERREDNAME) IS NOT NULL
THEN rtrim(BLOCKING.hostname) + '-' +
[CORPORATEDIRECTORY].[dbo].[ConcatenateName](SCD.LASTNAME, SCD.FIRSTNAME,
SCD.MIDDLENAME, SCD.PREFERREDNAME)
ELSE rtrim(BLOCKING.hostname)
END
FROM SYSPROCESSES BLOCKING
LEFT OUTER JOIN PCDB.dbo.PC PCBLOCKING ON (BLOCKING.hostname =PCBLOCKING.MACHINEID)
LEFT OUTER JOIN PCDB.DBO.PERSON PERSONBLOCKING ON (PCBLOCKING.PERSONID =PERSONBLOCKING.PERSONID)
LEFT JOIN CORPORATEDIRECTORY.dbo.PERSON SCD ON (PERSONBLOCKING.CDID =SCD.PERSONID)
WHERE BLOCKING.SPID = @.BlockingSPID
IF @.BLOCKING_BLOCKEDBY <> 0
BEGIN
SET @.NESTNEXTLEVEL = @.NestLevel + 1
EXEC dbo.ShowBlockingSPID @.SampleTime, @.BLOCKING_BLOCKEDBY, @.NESTNEXTLEVEL
END
return 0
go
"Jim Heavey" <JimHeavey@.hotmail.com> wrote in message
news:O$u7BzDmDHA.2080@.TK2MSFTNGP10.phx.gbl...
> I was wanting to write a stored procedure which would identify queries
which
> are currently being blocked (spids 'blocked by' some other spid) when the
> stored procedure is being run.
> Is this possible? How do I go about figuring out how to do this?
>
Friday, February 24, 2012
BLOB
Hi,
I'm new on the group, sorry if that topic is being repeated.
I'm facing the problem of puting a big object into MS SQL.
Does the procedure is being executed on the DB site, or I can
run it outside the DB (separate e.g. C++ programme).
I've read such articles like 'Who's Afraid of the Big, Bad BLOB?'
and I still don't know how to bite it.
Will be gratefull for any help or Inet sources.
LaurusThe Books Online has some examples. Look for "Building SQL Server
Applications" under the Contents tab.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Laurus" <s1336@.poczta.onet.pl> wrote in message
news:dbgu9f$g9m$1@.news.onet.pl...
> Hi,
> I'm new on the group, sorry if that topic is being repeated.
> I'm facing the problem of puting a big object into MS SQL.
> Does the procedure is being executed on the DB site, or I can
> run it outside the DB (separate e.g. C++ programme).
> I've read such articles like 'Who's Afraid of the Big, Bad BLOB?'
> and I still don't know how to bite it.
> Will be gratefull for any help or Inet sources.
> Laurus
>
>|||
> The Books Online has some examples. Look for "Building SQL Server
> Applications" under the Contents tab.
Thanks. I found it.
Laurus
I'm new on the group, sorry if that topic is being repeated.
I'm facing the problem of puting a big object into MS SQL.
Does the procedure is being executed on the DB site, or I can
run it outside the DB (separate e.g. C++ programme).
I've read such articles like 'Who's Afraid of the Big, Bad BLOB?'
and I still don't know how to bite it.
Will be gratefull for any help or Inet sources.
LaurusThe Books Online has some examples. Look for "Building SQL Server
Applications" under the Contents tab.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Laurus" <s1336@.poczta.onet.pl> wrote in message
news:dbgu9f$g9m$1@.news.onet.pl...
> Hi,
> I'm new on the group, sorry if that topic is being repeated.
> I'm facing the problem of puting a big object into MS SQL.
> Does the procedure is being executed on the DB site, or I can
> run it outside the DB (separate e.g. C++ programme).
> I've read such articles like 'Who's Afraid of the Big, Bad BLOB?'
> and I still don't know how to bite it.
> Will be gratefull for any help or Inet sources.
> Laurus
>
>|||
> The Books Online has some examples. Look for "Building SQL Server
> Applications" under the Contents tab.
Thanks. I found it.
Laurus
BLOB
Hi,
I'm new on the group, sorry if that topic is being repeated.
I'm facing the problem of puting a big object into MS SQL.
Does the procedure is being executed on the DB site, or I can
run it outside the DB (separate e.g. C++ programme).
I've read such articles like 'Who's Afraid of the Big, Bad BLOB?'
and I still don't know how to bite it.
Will be gratefull for any help or Inet sources.
Laurus
The Books Online has some examples. Look for "Building SQL Server
Applications" under the Contents tab.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Laurus" <s1336@.poczta.onet.pl> wrote in message
news:dbgu9f$g9m$1@.news.onet.pl...
> Hi,
> I'm new on the group, sorry if that topic is being repeated.
> I'm facing the problem of puting a big object into MS SQL.
> Does the procedure is being executed on the DB site, or I can
> run it outside the DB (separate e.g. C++ programme).
> I've read such articles like 'Who's Afraid of the Big, Bad BLOB?'
> and I still don't know how to bite it.
> Will be gratefull for any help or Inet sources.
> Laurus
>
>
|||
> The Books Online has some examples. Look for "Building SQL Server
> Applications" under the Contents tab.
Thanks. I found it.
Laurus
I'm new on the group, sorry if that topic is being repeated.
I'm facing the problem of puting a big object into MS SQL.
Does the procedure is being executed on the DB site, or I can
run it outside the DB (separate e.g. C++ programme).
I've read such articles like 'Who's Afraid of the Big, Bad BLOB?'
and I still don't know how to bite it.
Will be gratefull for any help or Inet sources.
Laurus
The Books Online has some examples. Look for "Building SQL Server
Applications" under the Contents tab.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Laurus" <s1336@.poczta.onet.pl> wrote in message
news:dbgu9f$g9m$1@.news.onet.pl...
> Hi,
> I'm new on the group, sorry if that topic is being repeated.
> I'm facing the problem of puting a big object into MS SQL.
> Does the procedure is being executed on the DB site, or I can
> run it outside the DB (separate e.g. C++ programme).
> I've read such articles like 'Who's Afraid of the Big, Bad BLOB?'
> and I still don't know how to bite it.
> Will be gratefull for any help or Inet sources.
> Laurus
>
>
|||
> The Books Online has some examples. Look for "Building SQL Server
> Applications" under the Contents tab.
Thanks. I found it.
Laurus
Sunday, February 19, 2012
blank query window- how to get?
In Sql Server Query Analyzer, to get a blank query window, it seems that I
must first edit an existing stored procedure, at which point the "New
window" button is enabled.
Is there a way to get an empty window without first having to edit another
query? If I launch Query Analyzer, I have an empty instance of the app..how
do I get a blank query window?
thx1. You must be connected to an instance of sqlserver.
2. [ctrl]+[N] should open a new window/connection to the connected instance.
-oj
"mrmagoo" <-> wrote in message news:eO$AWA6TGHA.4956@.TK2MSFTNGP09.phx.gbl...
> In Sql Server Query Analyzer, to get a blank query window, it seems that I
> must first edit an existing stored procedure, at which point the "New
> window" button is enabled.
> Is there a way to get an empty window without first having to edit another
> query? If I launch Query Analyzer, I have an empty instance of the
> app..how
> do I get a blank query window?
> thx
>|||that's my point...it doesn't.
I launch query analyzer, and pick the server. It connects.
By default it does open an empty query window. If I close that, there's no
way that I can see to get another one unless I edit another stored
procedure. THEN I can Ctrl + N to get as many new windows as I want.
"oj" <nospam_ojngo@.home.com> wrote in message
news:udqvtV6TGHA.224@.TK2MSFTNGP10.phx.gbl...
> 1. You must be connected to an instance of sqlserver.
> 2. [ctrl]+[N] should open a new window/connection to the connected
instance.
> --
> -oj
>
> "mrmagoo" <-> wrote in message
news:eO$AWA6TGHA.4956@.TK2MSFTNGP09.phx.gbl...
I
another
>|||When you close the last window, there is nothing left in "query window"
cache to use to reinitiate a new connection for another query window. This
is by design and would not be enhanced - QA is now replaced with Management
Studio (sqlwb) in sql2k5.
-oj
"mrmagoo" <-> wrote in message news:eGUfXo6TGHA.4740@.TK2MSFTNGP14.phx.gbl...
> that's my point...it doesn't.
> I launch query analyzer, and pick the server. It connects.
> By default it does open an empty query window. If I close that, there's no
> way that I can see to get another one unless I edit another stored
> procedure. THEN I can Ctrl + N to get as many new windows as I want.
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:udqvtV6TGHA.224@.TK2MSFTNGP10.phx.gbl...
> instance.
> news:eO$AWA6TGHA.4956@.TK2MSFTNGP09.phx.gbl...
> I
> another
>|||ok..thanks...
"oj" <nospam_ojngo@.home.com> wrote in message
news:eGk2Jk8TGHA.4900@.TK2MSFTNGP12.phx.gbl...
> When you close the last window, there is nothing left in "query window"
> cache to use to reinitiate a new connection for another query window. This
> is by design and would not be enhanced - QA is now replaced with
Management
> Studio (sqlwb) in sql2k5.
> --
> -oj
>
> "mrmagoo" <-> wrote in message
news:eGUfXo6TGHA.4740@.TK2MSFTNGP14.phx.gbl...
no
>|||"mrmagoo" <-> wrote in message news:eO$AWA6TGHA.4956@.TK2MSFTNGP09.phx.gbl...
> In Sql Server Query Analyzer, to get a blank query window, it seems that I
> must first edit an existing stored procedure, at which point the "New
> window" button is enabled.
> Is there a way to get an empty window without first having to edit another
> query? If I launch Query Analyzer, I have an empty instance of the
> app..how
> do I get a blank query window?
> thx
>
CTRL+O
or
Select File / Connect...
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||thanks!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:eYLv$gEUGHA.2444@.TK2MSFTNGP14.phx.gbl...
> "mrmagoo" <-> wrote in message
news:eO$AWA6TGHA.4956@.TK2MSFTNGP09.phx.gbl...
I
another
> CTRL+O
> or
> Select File / Connect...
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
must first edit an existing stored procedure, at which point the "New
window" button is enabled.
Is there a way to get an empty window without first having to edit another
query? If I launch Query Analyzer, I have an empty instance of the app..how
do I get a blank query window?
thx1. You must be connected to an instance of sqlserver.
2. [ctrl]+[N] should open a new window/connection to the connected instance.
-oj
"mrmagoo" <-> wrote in message news:eO$AWA6TGHA.4956@.TK2MSFTNGP09.phx.gbl...
> In Sql Server Query Analyzer, to get a blank query window, it seems that I
> must first edit an existing stored procedure, at which point the "New
> window" button is enabled.
> Is there a way to get an empty window without first having to edit another
> query? If I launch Query Analyzer, I have an empty instance of the
> app..how
> do I get a blank query window?
> thx
>|||that's my point...it doesn't.
I launch query analyzer, and pick the server. It connects.
By default it does open an empty query window. If I close that, there's no
way that I can see to get another one unless I edit another stored
procedure. THEN I can Ctrl + N to get as many new windows as I want.
"oj" <nospam_ojngo@.home.com> wrote in message
news:udqvtV6TGHA.224@.TK2MSFTNGP10.phx.gbl...
> 1. You must be connected to an instance of sqlserver.
> 2. [ctrl]+[N] should open a new window/connection to the connected
instance.
> --
> -oj
>
> "mrmagoo" <-> wrote in message
news:eO$AWA6TGHA.4956@.TK2MSFTNGP09.phx.gbl...
I
another
>|||When you close the last window, there is nothing left in "query window"
cache to use to reinitiate a new connection for another query window. This
is by design and would not be enhanced - QA is now replaced with Management
Studio (sqlwb) in sql2k5.
-oj
"mrmagoo" <-> wrote in message news:eGUfXo6TGHA.4740@.TK2MSFTNGP14.phx.gbl...
> that's my point...it doesn't.
> I launch query analyzer, and pick the server. It connects.
> By default it does open an empty query window. If I close that, there's no
> way that I can see to get another one unless I edit another stored
> procedure. THEN I can Ctrl + N to get as many new windows as I want.
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:udqvtV6TGHA.224@.TK2MSFTNGP10.phx.gbl...
> instance.
> news:eO$AWA6TGHA.4956@.TK2MSFTNGP09.phx.gbl...
> I
> another
>|||ok..thanks...
"oj" <nospam_ojngo@.home.com> wrote in message
news:eGk2Jk8TGHA.4900@.TK2MSFTNGP12.phx.gbl...
> When you close the last window, there is nothing left in "query window"
> cache to use to reinitiate a new connection for another query window. This
> is by design and would not be enhanced - QA is now replaced with
Management
> Studio (sqlwb) in sql2k5.
> --
> -oj
>
> "mrmagoo" <-> wrote in message
news:eGUfXo6TGHA.4740@.TK2MSFTNGP14.phx.gbl...
no
>|||"mrmagoo" <-> wrote in message news:eO$AWA6TGHA.4956@.TK2MSFTNGP09.phx.gbl...
> In Sql Server Query Analyzer, to get a blank query window, it seems that I
> must first edit an existing stored procedure, at which point the "New
> window" button is enabled.
> Is there a way to get an empty window without first having to edit another
> query? If I launch Query Analyzer, I have an empty instance of the
> app..how
> do I get a blank query window?
> thx
>
CTRL+O
or
Select File / Connect...
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||thanks!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:eYLv$gEUGHA.2444@.TK2MSFTNGP14.phx.gbl...
> "mrmagoo" <-> wrote in message
news:eO$AWA6TGHA.4956@.TK2MSFTNGP09.phx.gbl...
I
another
> CTRL+O
> or
> Select File / Connect...
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
Blank Parameters do not work when running stored proc in Data view
I have a stored procedure that gives different outputs based on whether an
input parameter is given or not. When I set the parameter to blank, not
NULL, I assume this will give the same result as running it in Query Analyzer
and not inputting anything for that parameter, but instead I get a "input
string was not in the correct format" error. Is this a bug? It is not the
behavior I would expect. I think it is very important that blank parameters
work correctly.I'm talking about the parameters in a SQL Server stored procedure. When I
run the report, it's fine if I allow blank values, and there is no error.
Only in Data View do I get the error when I try to run it or do refresh
fields, and it asks me to input all parameters.
"Aaron Williams" wrote:
> Did you set the parameter to allow blank values?
> "Stefan Wrobel" wrote:
> > I have a stored procedure that gives different outputs based on whether an
> > input parameter is given or not. When I set the parameter to blank, not
> > NULL, I assume this will give the same result as running it in Query Analyzer
> > and not inputting anything for that parameter, but instead I get a "input
> > string was not in the correct format" error. Is this a bug? It is not the
> > behavior I would expect. I think it is very important that blank parameters
> > work correctly.|||That seems about correct. I assumed if you specified a parameter as blank it
was like not sending it to the SP at all. For clarification, I only need to
use 2 of the 4 parameters for the report, so I just deleted the 2 I didn't
need from the parameters list. I assume its as if the Report Server doesn't
even know these parameters exist, and doesn't attempt to set them to
anything. This is how I want it. However, in Data View, if I try to get the
fields in the first place, I have to give some sort of value for all 4
parameters, because if I leave the 2 I don't need set to <Blank>, I get that
error. The only time I ever have run into this problem is while designing
the Report, but not anywhere else. It works fine in Query Analyzer, and also
when I run the report through Report Server.
Actually, now that I think about it, to clarify, here's a simple example
that applies to 1 of the 2 parameters.
@.param1 int,
@.param2 int = @.param1
So basically if I don't specify anything for @.param2, it should just be
equal to what I specified for @.param1. However, there doesn't seem to be any
way in Data View not to specify anything. Leaving it set to <blank> I think
it still tries to set the parameter to something, and obviously to something
that causes an error.
I think that's about the best I can do to explain it. It's not like it
prevents me from using Reporting Services, but it is very annoying and seems
counterintuitive.
Thanks for your help.
"Aaron Williams" wrote:
> Could you provide an example of your stored proc?
> Lets make sure we're on the same page. If I get what you're saying, your
> stored proc allows you to send a parameter value if desired, but if you don't
> send anything, then it will still return a query. So your stored proc might
> look something like this:
> CREATE proc MyStoredProcedure
> @.MyParameter int = null
> AS
> SELECT * FROM MyTable
> WHERE (MyPrimaryKey = @.MyParameter OR @.MyParameter is NULL)
> In this case, you can run it in query analyzer like so:
> exec MyStoredProcedure 1 OR exec MyStoredProcedure ('blank')
> In this case, the first would return one record, and the second would return
> ALL records. So, if this is what you mean, then your problem is that when
> you try to pass a ' ' (blank value) to the report parameter you get an error.
> '
> -Aaron
>
> "Stefan Wrobel" wrote:
> > By the way, I'm speaking about when designing a report in Visual Studio
> >
> > "Stefan Wrobel" wrote:
> >
> > > I'm talking about the parameters in a SQL Server stored procedure. When I
> > > run the report, it's fine if I allow blank values, and there is no error.
> > > Only in Data View do I get the error when I try to run it or do refresh
> > > fields, and it asks me to input all parameters.
> > >
> > > "Aaron Williams" wrote:
> > >
> > > > Did you set the parameter to allow blank values?
> > > >
> > > > "Stefan Wrobel" wrote:
> > > >
> > > > > I have a stored procedure that gives different outputs based on whether an
> > > > > input parameter is given or not. When I set the parameter to blank, not
> > > > > NULL, I assume this will give the same result as running it in Query Analyzer
> > > > > and not inputting anything for that parameter, but instead I get a "input
> > > > > string was not in the correct format" error. Is this a bug? It is not the
> > > > > behavior I would expect. I think it is very important that blank parameters
> > > > > work correctly.|||Could you provide an example of your stored proc?
Lets make sure we're on the same page. If I get what you're saying, your
stored proc allows you to send a parameter value if desired, but if you don't
send anything, then it will still return a query. So your stored proc might
look something like this:
CREATE proc MyStoredProcedure
@.MyParameter int = null
AS
SELECT * FROM MyTable
WHERE (MyPrimaryKey = @.MyParameter OR @.MyParameter is NULL)
In this case, you can run it in query analyzer like so:
exec MyStoredProcedure 1 OR exec MyStoredProcedure ('blank')
In this case, the first would return one record, and the second would return
ALL records. So, if this is what you mean, then your problem is that when
you try to pass a ' ' (blank value) to the report parameter you get an error.
'
-Aaron
"Stefan Wrobel" wrote:
> By the way, I'm speaking about when designing a report in Visual Studio
> "Stefan Wrobel" wrote:
> > I'm talking about the parameters in a SQL Server stored procedure. When I
> > run the report, it's fine if I allow blank values, and there is no error.
> > Only in Data View do I get the error when I try to run it or do refresh
> > fields, and it asks me to input all parameters.
> >
> > "Aaron Williams" wrote:
> >
> > > Did you set the parameter to allow blank values?
> > >
> > > "Stefan Wrobel" wrote:
> > >
> > > > I have a stored procedure that gives different outputs based on whether an
> > > > input parameter is given or not. When I set the parameter to blank, not
> > > > NULL, I assume this will give the same result as running it in Query Analyzer
> > > > and not inputting anything for that parameter, but instead I get a "input
> > > > string was not in the correct format" error. Is this a bug? It is not the
> > > > behavior I would expect. I think it is very important that blank parameters
> > > > work correctly.|||I think the way you want it do it will really only work in Query Analyzer.
Reporting Services gets a list of all the parameters in the stored procedure
when you create the dataset. I assume you could do what you said and remove
the unnessisary parameters from the list, but then you no longer have the
option of using them which is obviously something you want to do.
Another option would be to change your stored procedure so that the optional
parameters can accept NULL's and then change the values later. That way
Reporting Services can send a null value to those parameters and you can
still use them the way you desire. Here's an example of how I would change
your stored proc so you can do what you're looking for.
CREATE proc Tmp_TestParams
@.param1 int,
@.param2 int = null,
@.param3 int = null
AS
-- Here's where you set the value of the blank parameters
IF @.param2 IS null
BEGIN
SET @.param2 = @.param1
END
IF @.param3 is null
BEGIN
SET @.param3 = @.param2
END
SELECT @.param1 as Param1, @.param2 as Param2, @.param3 as Param3
Go
exec Tmp_TestParams 1, null, null
Hope this helps!
-Aaron
"Stefan Wrobel" wrote:
> That seems about correct. I assumed if you specified a parameter as blank it
> was like not sending it to the SP at all. For clarification, I only need to
> use 2 of the 4 parameters for the report, so I just deleted the 2 I didn't
> need from the parameters list. I assume its as if the Report Server doesn't
> even know these parameters exist, and doesn't attempt to set them to
> anything. This is how I want it. However, in Data View, if I try to get the
> fields in the first place, I have to give some sort of value for all 4
> parameters, because if I leave the 2 I don't need set to <Blank>, I get that
> error. The only time I ever have run into this problem is while designing
> the Report, but not anywhere else. It works fine in Query Analyzer, and also
> when I run the report through Report Server.
> Actually, now that I think about it, to clarify, here's a simple example
> that applies to 1 of the 2 parameters.
> @.param1 int,
> @.param2 int = @.param1
> So basically if I don't specify anything for @.param2, it should just be
> equal to what I specified for @.param1. However, there doesn't seem to be any
> way in Data View not to specify anything. Leaving it set to <blank> I think
> it still tries to set the parameter to something, and obviously to something
> that causes an error.
> I think that's about the best I can do to explain it. It's not like it
> prevents me from using Reporting Services, but it is very annoying and seems
> counterintuitive.
> Thanks for your help.
> "Aaron Williams" wrote:
> > Could you provide an example of your stored proc?
> >
> > Lets make sure we're on the same page. If I get what you're saying, your
> > stored proc allows you to send a parameter value if desired, but if you don't
> > send anything, then it will still return a query. So your stored proc might
> > look something like this:
> >
> > CREATE proc MyStoredProcedure
> > @.MyParameter int = null
> >
> > AS
> >
> > SELECT * FROM MyTable
> > WHERE (MyPrimaryKey = @.MyParameter OR @.MyParameter is NULL)
> >
> > In this case, you can run it in query analyzer like so:
> >
> > exec MyStoredProcedure 1 OR exec MyStoredProcedure ('blank')
> >
> > In this case, the first would return one record, and the second would return
> > ALL records. So, if this is what you mean, then your problem is that when
> > you try to pass a ' ' (blank value) to the report parameter you get an error.
> > '
> >
> > -Aaron
> >
> >
> > "Stefan Wrobel" wrote:
> >
> > > By the way, I'm speaking about when designing a report in Visual Studio
> > >
> > > "Stefan Wrobel" wrote:
> > >
> > > > I'm talking about the parameters in a SQL Server stored procedure. When I
> > > > run the report, it's fine if I allow blank values, and there is no error.
> > > > Only in Data View do I get the error when I try to run it or do refresh
> > > > fields, and it asks me to input all parameters.
> > > >
> > > > "Aaron Williams" wrote:
> > > >
> > > > > Did you set the parameter to allow blank values?
> > > > >
> > > > > "Stefan Wrobel" wrote:
> > > > >
> > > > > > I have a stored procedure that gives different outputs based on whether an
> > > > > > input parameter is given or not. When I set the parameter to blank, not
> > > > > > NULL, I assume this will give the same result as running it in Query Analyzer
> > > > > > and not inputting anything for that parameter, but instead I get a "input
> > > > > > string was not in the correct format" error. Is this a bug? It is not the
> > > > > > behavior I would expect. I think it is very important that blank parameters
> > > > > > work correctly.|||Good suggestion, thanks!
"Aaron Williams" wrote:
> I think the way you want it do it will really only work in Query Analyzer.
> Reporting Services gets a list of all the parameters in the stored procedure
> when you create the dataset. I assume you could do what you said and remove
> the unnessisary parameters from the list, but then you no longer have the
> option of using them which is obviously something you want to do.
> Another option would be to change your stored procedure so that the optional
> parameters can accept NULL's and then change the values later. That way
> Reporting Services can send a null value to those parameters and you can
> still use them the way you desire. Here's an example of how I would change
> your stored proc so you can do what you're looking for.
> CREATE proc Tmp_TestParams
> @.param1 int,
> @.param2 int = null,
> @.param3 int = null
> AS
> -- Here's where you set the value of the blank parameters
> IF @.param2 IS null
> BEGIN
> SET @.param2 = @.param1
> END
> IF @.param3 is null
> BEGIN
> SET @.param3 = @.param2
> END
> SELECT @.param1 as Param1, @.param2 as Param2, @.param3 as Param3
> Go
> exec Tmp_TestParams 1, null, null
> Hope this helps!
> -Aaron
> "Stefan Wrobel" wrote:
> > That seems about correct. I assumed if you specified a parameter as blank it
> > was like not sending it to the SP at all. For clarification, I only need to
> > use 2 of the 4 parameters for the report, so I just deleted the 2 I didn't
> > need from the parameters list. I assume its as if the Report Server doesn't
> > even know these parameters exist, and doesn't attempt to set them to
> > anything. This is how I want it. However, in Data View, if I try to get the
> > fields in the first place, I have to give some sort of value for all 4
> > parameters, because if I leave the 2 I don't need set to <Blank>, I get that
> > error. The only time I ever have run into this problem is while designing
> > the Report, but not anywhere else. It works fine in Query Analyzer, and also
> > when I run the report through Report Server.
> >
> > Actually, now that I think about it, to clarify, here's a simple example
> > that applies to 1 of the 2 parameters.
> >
> > @.param1 int,
> > @.param2 int = @.param1
> >
> > So basically if I don't specify anything for @.param2, it should just be
> > equal to what I specified for @.param1. However, there doesn't seem to be any
> > way in Data View not to specify anything. Leaving it set to <blank> I think
> > it still tries to set the parameter to something, and obviously to something
> > that causes an error.
> >
> > I think that's about the best I can do to explain it. It's not like it
> > prevents me from using Reporting Services, but it is very annoying and seems
> > counterintuitive.
> >
> > Thanks for your help.
> >
> > "Aaron Williams" wrote:
> >
> > > Could you provide an example of your stored proc?
> > >
> > > Lets make sure we're on the same page. If I get what you're saying, your
> > > stored proc allows you to send a parameter value if desired, but if you don't
> > > send anything, then it will still return a query. So your stored proc might
> > > look something like this:
> > >
> > > CREATE proc MyStoredProcedure
> > > @.MyParameter int = null
> > >
> > > AS
> > >
> > > SELECT * FROM MyTable
> > > WHERE (MyPrimaryKey = @.MyParameter OR @.MyParameter is NULL)
> > >
> > > In this case, you can run it in query analyzer like so:
> > >
> > > exec MyStoredProcedure 1 OR exec MyStoredProcedure ('blank')
> > >
> > > In this case, the first would return one record, and the second would return
> > > ALL records. So, if this is what you mean, then your problem is that when
> > > you try to pass a ' ' (blank value) to the report parameter you get an error.
> > > '
> > >
> > > -Aaron
> > >
> > >
> > > "Stefan Wrobel" wrote:
> > >
> > > > By the way, I'm speaking about when designing a report in Visual Studio
> > > >
> > > > "Stefan Wrobel" wrote:
> > > >
> > > > > I'm talking about the parameters in a SQL Server stored procedure. When I
> > > > > run the report, it's fine if I allow blank values, and there is no error.
> > > > > Only in Data View do I get the error when I try to run it or do refresh
> > > > > fields, and it asks me to input all parameters.
> > > > >
> > > > > "Aaron Williams" wrote:
> > > > >
> > > > > > Did you set the parameter to allow blank values?
> > > > > >
> > > > > > "Stefan Wrobel" wrote:
> > > > > >
> > > > > > > I have a stored procedure that gives different outputs based on whether an
> > > > > > > input parameter is given or not. When I set the parameter to blank, not
> > > > > > > NULL, I assume this will give the same result as running it in Query Analyzer
> > > > > > > and not inputting anything for that parameter, but instead I get a "input
> > > > > > > string was not in the correct format" error. Is this a bug? It is not the
> > > > > > > behavior I would expect. I think it is very important that blank parameters
> > > > > > > work correctly.|||Did you set the parameter to allow blank values?
"Stefan Wrobel" wrote:
> I have a stored procedure that gives different outputs based on whether an
> input parameter is given or not. When I set the parameter to blank, not
> NULL, I assume this will give the same result as running it in Query Analyzer
> and not inputting anything for that parameter, but instead I get a "input
> string was not in the correct format" error. Is this a bug? It is not the
> behavior I would expect. I think it is very important that blank parameters
> work correctly.|||By the way, I'm speaking about when designing a report in Visual Studio
"Stefan Wrobel" wrote:
> I'm talking about the parameters in a SQL Server stored procedure. When I
> run the report, it's fine if I allow blank values, and there is no error.
> Only in Data View do I get the error when I try to run it or do refresh
> fields, and it asks me to input all parameters.
> "Aaron Williams" wrote:
> > Did you set the parameter to allow blank values?
> >
> > "Stefan Wrobel" wrote:
> >
> > > I have a stored procedure that gives different outputs based on whether an
> > > input parameter is given or not. When I set the parameter to blank, not
> > > NULL, I assume this will give the same result as running it in Query Analyzer
> > > and not inputting anything for that parameter, but instead I get a "input
> > > string was not in the correct format" error. Is this a bug? It is not the
> > > behavior I would expect. I think it is very important that blank parameters
> > > work correctly.
input parameter is given or not. When I set the parameter to blank, not
NULL, I assume this will give the same result as running it in Query Analyzer
and not inputting anything for that parameter, but instead I get a "input
string was not in the correct format" error. Is this a bug? It is not the
behavior I would expect. I think it is very important that blank parameters
work correctly.I'm talking about the parameters in a SQL Server stored procedure. When I
run the report, it's fine if I allow blank values, and there is no error.
Only in Data View do I get the error when I try to run it or do refresh
fields, and it asks me to input all parameters.
"Aaron Williams" wrote:
> Did you set the parameter to allow blank values?
> "Stefan Wrobel" wrote:
> > I have a stored procedure that gives different outputs based on whether an
> > input parameter is given or not. When I set the parameter to blank, not
> > NULL, I assume this will give the same result as running it in Query Analyzer
> > and not inputting anything for that parameter, but instead I get a "input
> > string was not in the correct format" error. Is this a bug? It is not the
> > behavior I would expect. I think it is very important that blank parameters
> > work correctly.|||That seems about correct. I assumed if you specified a parameter as blank it
was like not sending it to the SP at all. For clarification, I only need to
use 2 of the 4 parameters for the report, so I just deleted the 2 I didn't
need from the parameters list. I assume its as if the Report Server doesn't
even know these parameters exist, and doesn't attempt to set them to
anything. This is how I want it. However, in Data View, if I try to get the
fields in the first place, I have to give some sort of value for all 4
parameters, because if I leave the 2 I don't need set to <Blank>, I get that
error. The only time I ever have run into this problem is while designing
the Report, but not anywhere else. It works fine in Query Analyzer, and also
when I run the report through Report Server.
Actually, now that I think about it, to clarify, here's a simple example
that applies to 1 of the 2 parameters.
@.param1 int,
@.param2 int = @.param1
So basically if I don't specify anything for @.param2, it should just be
equal to what I specified for @.param1. However, there doesn't seem to be any
way in Data View not to specify anything. Leaving it set to <blank> I think
it still tries to set the parameter to something, and obviously to something
that causes an error.
I think that's about the best I can do to explain it. It's not like it
prevents me from using Reporting Services, but it is very annoying and seems
counterintuitive.
Thanks for your help.
"Aaron Williams" wrote:
> Could you provide an example of your stored proc?
> Lets make sure we're on the same page. If I get what you're saying, your
> stored proc allows you to send a parameter value if desired, but if you don't
> send anything, then it will still return a query. So your stored proc might
> look something like this:
> CREATE proc MyStoredProcedure
> @.MyParameter int = null
> AS
> SELECT * FROM MyTable
> WHERE (MyPrimaryKey = @.MyParameter OR @.MyParameter is NULL)
> In this case, you can run it in query analyzer like so:
> exec MyStoredProcedure 1 OR exec MyStoredProcedure ('blank')
> In this case, the first would return one record, and the second would return
> ALL records. So, if this is what you mean, then your problem is that when
> you try to pass a ' ' (blank value) to the report parameter you get an error.
> '
> -Aaron
>
> "Stefan Wrobel" wrote:
> > By the way, I'm speaking about when designing a report in Visual Studio
> >
> > "Stefan Wrobel" wrote:
> >
> > > I'm talking about the parameters in a SQL Server stored procedure. When I
> > > run the report, it's fine if I allow blank values, and there is no error.
> > > Only in Data View do I get the error when I try to run it or do refresh
> > > fields, and it asks me to input all parameters.
> > >
> > > "Aaron Williams" wrote:
> > >
> > > > Did you set the parameter to allow blank values?
> > > >
> > > > "Stefan Wrobel" wrote:
> > > >
> > > > > I have a stored procedure that gives different outputs based on whether an
> > > > > input parameter is given or not. When I set the parameter to blank, not
> > > > > NULL, I assume this will give the same result as running it in Query Analyzer
> > > > > and not inputting anything for that parameter, but instead I get a "input
> > > > > string was not in the correct format" error. Is this a bug? It is not the
> > > > > behavior I would expect. I think it is very important that blank parameters
> > > > > work correctly.|||Could you provide an example of your stored proc?
Lets make sure we're on the same page. If I get what you're saying, your
stored proc allows you to send a parameter value if desired, but if you don't
send anything, then it will still return a query. So your stored proc might
look something like this:
CREATE proc MyStoredProcedure
@.MyParameter int = null
AS
SELECT * FROM MyTable
WHERE (MyPrimaryKey = @.MyParameter OR @.MyParameter is NULL)
In this case, you can run it in query analyzer like so:
exec MyStoredProcedure 1 OR exec MyStoredProcedure ('blank')
In this case, the first would return one record, and the second would return
ALL records. So, if this is what you mean, then your problem is that when
you try to pass a ' ' (blank value) to the report parameter you get an error.
'
-Aaron
"Stefan Wrobel" wrote:
> By the way, I'm speaking about when designing a report in Visual Studio
> "Stefan Wrobel" wrote:
> > I'm talking about the parameters in a SQL Server stored procedure. When I
> > run the report, it's fine if I allow blank values, and there is no error.
> > Only in Data View do I get the error when I try to run it or do refresh
> > fields, and it asks me to input all parameters.
> >
> > "Aaron Williams" wrote:
> >
> > > Did you set the parameter to allow blank values?
> > >
> > > "Stefan Wrobel" wrote:
> > >
> > > > I have a stored procedure that gives different outputs based on whether an
> > > > input parameter is given or not. When I set the parameter to blank, not
> > > > NULL, I assume this will give the same result as running it in Query Analyzer
> > > > and not inputting anything for that parameter, but instead I get a "input
> > > > string was not in the correct format" error. Is this a bug? It is not the
> > > > behavior I would expect. I think it is very important that blank parameters
> > > > work correctly.|||I think the way you want it do it will really only work in Query Analyzer.
Reporting Services gets a list of all the parameters in the stored procedure
when you create the dataset. I assume you could do what you said and remove
the unnessisary parameters from the list, but then you no longer have the
option of using them which is obviously something you want to do.
Another option would be to change your stored procedure so that the optional
parameters can accept NULL's and then change the values later. That way
Reporting Services can send a null value to those parameters and you can
still use them the way you desire. Here's an example of how I would change
your stored proc so you can do what you're looking for.
CREATE proc Tmp_TestParams
@.param1 int,
@.param2 int = null,
@.param3 int = null
AS
-- Here's where you set the value of the blank parameters
IF @.param2 IS null
BEGIN
SET @.param2 = @.param1
END
IF @.param3 is null
BEGIN
SET @.param3 = @.param2
END
SELECT @.param1 as Param1, @.param2 as Param2, @.param3 as Param3
Go
exec Tmp_TestParams 1, null, null
Hope this helps!
-Aaron
"Stefan Wrobel" wrote:
> That seems about correct. I assumed if you specified a parameter as blank it
> was like not sending it to the SP at all. For clarification, I only need to
> use 2 of the 4 parameters for the report, so I just deleted the 2 I didn't
> need from the parameters list. I assume its as if the Report Server doesn't
> even know these parameters exist, and doesn't attempt to set them to
> anything. This is how I want it. However, in Data View, if I try to get the
> fields in the first place, I have to give some sort of value for all 4
> parameters, because if I leave the 2 I don't need set to <Blank>, I get that
> error. The only time I ever have run into this problem is while designing
> the Report, but not anywhere else. It works fine in Query Analyzer, and also
> when I run the report through Report Server.
> Actually, now that I think about it, to clarify, here's a simple example
> that applies to 1 of the 2 parameters.
> @.param1 int,
> @.param2 int = @.param1
> So basically if I don't specify anything for @.param2, it should just be
> equal to what I specified for @.param1. However, there doesn't seem to be any
> way in Data View not to specify anything. Leaving it set to <blank> I think
> it still tries to set the parameter to something, and obviously to something
> that causes an error.
> I think that's about the best I can do to explain it. It's not like it
> prevents me from using Reporting Services, but it is very annoying and seems
> counterintuitive.
> Thanks for your help.
> "Aaron Williams" wrote:
> > Could you provide an example of your stored proc?
> >
> > Lets make sure we're on the same page. If I get what you're saying, your
> > stored proc allows you to send a parameter value if desired, but if you don't
> > send anything, then it will still return a query. So your stored proc might
> > look something like this:
> >
> > CREATE proc MyStoredProcedure
> > @.MyParameter int = null
> >
> > AS
> >
> > SELECT * FROM MyTable
> > WHERE (MyPrimaryKey = @.MyParameter OR @.MyParameter is NULL)
> >
> > In this case, you can run it in query analyzer like so:
> >
> > exec MyStoredProcedure 1 OR exec MyStoredProcedure ('blank')
> >
> > In this case, the first would return one record, and the second would return
> > ALL records. So, if this is what you mean, then your problem is that when
> > you try to pass a ' ' (blank value) to the report parameter you get an error.
> > '
> >
> > -Aaron
> >
> >
> > "Stefan Wrobel" wrote:
> >
> > > By the way, I'm speaking about when designing a report in Visual Studio
> > >
> > > "Stefan Wrobel" wrote:
> > >
> > > > I'm talking about the parameters in a SQL Server stored procedure. When I
> > > > run the report, it's fine if I allow blank values, and there is no error.
> > > > Only in Data View do I get the error when I try to run it or do refresh
> > > > fields, and it asks me to input all parameters.
> > > >
> > > > "Aaron Williams" wrote:
> > > >
> > > > > Did you set the parameter to allow blank values?
> > > > >
> > > > > "Stefan Wrobel" wrote:
> > > > >
> > > > > > I have a stored procedure that gives different outputs based on whether an
> > > > > > input parameter is given or not. When I set the parameter to blank, not
> > > > > > NULL, I assume this will give the same result as running it in Query Analyzer
> > > > > > and not inputting anything for that parameter, but instead I get a "input
> > > > > > string was not in the correct format" error. Is this a bug? It is not the
> > > > > > behavior I would expect. I think it is very important that blank parameters
> > > > > > work correctly.|||Good suggestion, thanks!
"Aaron Williams" wrote:
> I think the way you want it do it will really only work in Query Analyzer.
> Reporting Services gets a list of all the parameters in the stored procedure
> when you create the dataset. I assume you could do what you said and remove
> the unnessisary parameters from the list, but then you no longer have the
> option of using them which is obviously something you want to do.
> Another option would be to change your stored procedure so that the optional
> parameters can accept NULL's and then change the values later. That way
> Reporting Services can send a null value to those parameters and you can
> still use them the way you desire. Here's an example of how I would change
> your stored proc so you can do what you're looking for.
> CREATE proc Tmp_TestParams
> @.param1 int,
> @.param2 int = null,
> @.param3 int = null
> AS
> -- Here's where you set the value of the blank parameters
> IF @.param2 IS null
> BEGIN
> SET @.param2 = @.param1
> END
> IF @.param3 is null
> BEGIN
> SET @.param3 = @.param2
> END
> SELECT @.param1 as Param1, @.param2 as Param2, @.param3 as Param3
> Go
> exec Tmp_TestParams 1, null, null
> Hope this helps!
> -Aaron
> "Stefan Wrobel" wrote:
> > That seems about correct. I assumed if you specified a parameter as blank it
> > was like not sending it to the SP at all. For clarification, I only need to
> > use 2 of the 4 parameters for the report, so I just deleted the 2 I didn't
> > need from the parameters list. I assume its as if the Report Server doesn't
> > even know these parameters exist, and doesn't attempt to set them to
> > anything. This is how I want it. However, in Data View, if I try to get the
> > fields in the first place, I have to give some sort of value for all 4
> > parameters, because if I leave the 2 I don't need set to <Blank>, I get that
> > error. The only time I ever have run into this problem is while designing
> > the Report, but not anywhere else. It works fine in Query Analyzer, and also
> > when I run the report through Report Server.
> >
> > Actually, now that I think about it, to clarify, here's a simple example
> > that applies to 1 of the 2 parameters.
> >
> > @.param1 int,
> > @.param2 int = @.param1
> >
> > So basically if I don't specify anything for @.param2, it should just be
> > equal to what I specified for @.param1. However, there doesn't seem to be any
> > way in Data View not to specify anything. Leaving it set to <blank> I think
> > it still tries to set the parameter to something, and obviously to something
> > that causes an error.
> >
> > I think that's about the best I can do to explain it. It's not like it
> > prevents me from using Reporting Services, but it is very annoying and seems
> > counterintuitive.
> >
> > Thanks for your help.
> >
> > "Aaron Williams" wrote:
> >
> > > Could you provide an example of your stored proc?
> > >
> > > Lets make sure we're on the same page. If I get what you're saying, your
> > > stored proc allows you to send a parameter value if desired, but if you don't
> > > send anything, then it will still return a query. So your stored proc might
> > > look something like this:
> > >
> > > CREATE proc MyStoredProcedure
> > > @.MyParameter int = null
> > >
> > > AS
> > >
> > > SELECT * FROM MyTable
> > > WHERE (MyPrimaryKey = @.MyParameter OR @.MyParameter is NULL)
> > >
> > > In this case, you can run it in query analyzer like so:
> > >
> > > exec MyStoredProcedure 1 OR exec MyStoredProcedure ('blank')
> > >
> > > In this case, the first would return one record, and the second would return
> > > ALL records. So, if this is what you mean, then your problem is that when
> > > you try to pass a ' ' (blank value) to the report parameter you get an error.
> > > '
> > >
> > > -Aaron
> > >
> > >
> > > "Stefan Wrobel" wrote:
> > >
> > > > By the way, I'm speaking about when designing a report in Visual Studio
> > > >
> > > > "Stefan Wrobel" wrote:
> > > >
> > > > > I'm talking about the parameters in a SQL Server stored procedure. When I
> > > > > run the report, it's fine if I allow blank values, and there is no error.
> > > > > Only in Data View do I get the error when I try to run it or do refresh
> > > > > fields, and it asks me to input all parameters.
> > > > >
> > > > > "Aaron Williams" wrote:
> > > > >
> > > > > > Did you set the parameter to allow blank values?
> > > > > >
> > > > > > "Stefan Wrobel" wrote:
> > > > > >
> > > > > > > I have a stored procedure that gives different outputs based on whether an
> > > > > > > input parameter is given or not. When I set the parameter to blank, not
> > > > > > > NULL, I assume this will give the same result as running it in Query Analyzer
> > > > > > > and not inputting anything for that parameter, but instead I get a "input
> > > > > > > string was not in the correct format" error. Is this a bug? It is not the
> > > > > > > behavior I would expect. I think it is very important that blank parameters
> > > > > > > work correctly.|||Did you set the parameter to allow blank values?
"Stefan Wrobel" wrote:
> I have a stored procedure that gives different outputs based on whether an
> input parameter is given or not. When I set the parameter to blank, not
> NULL, I assume this will give the same result as running it in Query Analyzer
> and not inputting anything for that parameter, but instead I get a "input
> string was not in the correct format" error. Is this a bug? It is not the
> behavior I would expect. I think it is very important that blank parameters
> work correctly.|||By the way, I'm speaking about when designing a report in Visual Studio
"Stefan Wrobel" wrote:
> I'm talking about the parameters in a SQL Server stored procedure. When I
> run the report, it's fine if I allow blank values, and there is no error.
> Only in Data View do I get the error when I try to run it or do refresh
> fields, and it asks me to input all parameters.
> "Aaron Williams" wrote:
> > Did you set the parameter to allow blank values?
> >
> > "Stefan Wrobel" wrote:
> >
> > > I have a stored procedure that gives different outputs based on whether an
> > > input parameter is given or not. When I set the parameter to blank, not
> > > NULL, I assume this will give the same result as running it in Query Analyzer
> > > and not inputting anything for that parameter, but instead I get a "input
> > > string was not in the correct format" error. Is this a bug? It is not the
> > > behavior I would expect. I think it is very important that blank parameters
> > > work correctly.
Blank Parameters
I am trying to use a stored procedure that can accept blank parameters to
return a complete set of data. when I use the preview screen in reporting
services, the sproc does not return anything, yet when i run the sproc from
anywhere else, it works. Is there a setting I am missing to be able pass
balnk values to a sproc from the report?
Yes, if i pass values to the report, not blanks, the sproc does run and
returns data.
--
John Cleveland
Network Manager
Urban systems LtdTry using SQL Profiler to find out what is actually sent to the stored
procedure. Blank might not be blank. Do you expect a NULL or a '' as a blank
in the sp? Anyway, SQL Profiler shows what is actually sent, so you can
modify either Parameter or Stored Procedure accordingly.
Kaisa M. Lindahl Lervik
"John Cleveland" <JohnCleveland@.discussions.microsoft.com> wrote in message
news:EA34E0D4-7E12-4730-96F5-B783261CADF4@.microsoft.com...
>I am trying to use a stored procedure that can accept blank parameters to
> return a complete set of data. when I use the preview screen in reporting
> services, the sproc does not return anything, yet when i run the sproc
> from
> anywhere else, it works. Is there a setting I am missing to be able pass
> balnk values to a sproc from the report?
> Yes, if i pass values to the report, not blanks, the sproc does run and
> returns data.
> --
> John Cleveland
> Network Manager
> Urban systems Ltd
>|||When you click on preview, is it giving a small entry screen ? if yes dont
enter anything like double quotes etc.. before that is it giving a screen?
Amarnath
"John Cleveland" wrote:
> I am trying to use a stored procedure that can accept blank parameters to
> return a complete set of data. when I use the preview screen in reporting
> services, the sproc does not return anything, yet when i run the sproc from
> anywhere else, it works. Is there a setting I am missing to be able pass
> balnk values to a sproc from the report?
> Yes, if i pass values to the report, not blanks, the sproc does run and
> returns data.
> --
> John Cleveland
> Network Manager
> Urban systems Ltd
>|||I ran a trace in profiler and here is what I got:
exec spCostReport @.Project=N'', @.Phase=N''
These are the 2 params i am trying to send. I have default values set in
the sproc that should kick in if the params are blank. I exported the trace
to make sure there was nothing between the '', and there wasn't. Any thing
else to check for?
When I run this from the dataset screen in reporting services, it runs fine
leaving the params blank. it only seems to be when the report is run.
--
John Cleveland
Network Manager
Urban systems Ltd
"Kaisa M. Lindahl Lervik" wrote:
> Try using SQL Profiler to find out what is actually sent to the stored
> procedure. Blank might not be blank. Do you expect a NULL or a '' as a blank
> in the sp? Anyway, SQL Profiler shows what is actually sent, so you can
> modify either Parameter or Stored Procedure accordingly.
> Kaisa M. Lindahl Lervik
> "John Cleveland" <JohnCleveland@.discussions.microsoft.com> wrote in message
> news:EA34E0D4-7E12-4730-96F5-B783261CADF4@.microsoft.com...
> >I am trying to use a stored procedure that can accept blank parameters to
> > return a complete set of data. when I use the preview screen in reporting
> > services, the sproc does not return anything, yet when i run the sproc
> > from
> > anywhere else, it works. Is there a setting I am missing to be able pass
> > balnk values to a sproc from the report?
> >
> > Yes, if i pass values to the report, not blanks, the sproc does run and
> > returns data.
> >
> > --
> > John Cleveland
> > Network Manager
> > Urban systems Ltd
> >
>
>
return a complete set of data. when I use the preview screen in reporting
services, the sproc does not return anything, yet when i run the sproc from
anywhere else, it works. Is there a setting I am missing to be able pass
balnk values to a sproc from the report?
Yes, if i pass values to the report, not blanks, the sproc does run and
returns data.
--
John Cleveland
Network Manager
Urban systems LtdTry using SQL Profiler to find out what is actually sent to the stored
procedure. Blank might not be blank. Do you expect a NULL or a '' as a blank
in the sp? Anyway, SQL Profiler shows what is actually sent, so you can
modify either Parameter or Stored Procedure accordingly.
Kaisa M. Lindahl Lervik
"John Cleveland" <JohnCleveland@.discussions.microsoft.com> wrote in message
news:EA34E0D4-7E12-4730-96F5-B783261CADF4@.microsoft.com...
>I am trying to use a stored procedure that can accept blank parameters to
> return a complete set of data. when I use the preview screen in reporting
> services, the sproc does not return anything, yet when i run the sproc
> from
> anywhere else, it works. Is there a setting I am missing to be able pass
> balnk values to a sproc from the report?
> Yes, if i pass values to the report, not blanks, the sproc does run and
> returns data.
> --
> John Cleveland
> Network Manager
> Urban systems Ltd
>|||When you click on preview, is it giving a small entry screen ? if yes dont
enter anything like double quotes etc.. before that is it giving a screen?
Amarnath
"John Cleveland" wrote:
> I am trying to use a stored procedure that can accept blank parameters to
> return a complete set of data. when I use the preview screen in reporting
> services, the sproc does not return anything, yet when i run the sproc from
> anywhere else, it works. Is there a setting I am missing to be able pass
> balnk values to a sproc from the report?
> Yes, if i pass values to the report, not blanks, the sproc does run and
> returns data.
> --
> John Cleveland
> Network Manager
> Urban systems Ltd
>|||I ran a trace in profiler and here is what I got:
exec spCostReport @.Project=N'', @.Phase=N''
These are the 2 params i am trying to send. I have default values set in
the sproc that should kick in if the params are blank. I exported the trace
to make sure there was nothing between the '', and there wasn't. Any thing
else to check for?
When I run this from the dataset screen in reporting services, it runs fine
leaving the params blank. it only seems to be when the report is run.
--
John Cleveland
Network Manager
Urban systems Ltd
"Kaisa M. Lindahl Lervik" wrote:
> Try using SQL Profiler to find out what is actually sent to the stored
> procedure. Blank might not be blank. Do you expect a NULL or a '' as a blank
> in the sp? Anyway, SQL Profiler shows what is actually sent, so you can
> modify either Parameter or Stored Procedure accordingly.
> Kaisa M. Lindahl Lervik
> "John Cleveland" <JohnCleveland@.discussions.microsoft.com> wrote in message
> news:EA34E0D4-7E12-4730-96F5-B783261CADF4@.microsoft.com...
> >I am trying to use a stored procedure that can accept blank parameters to
> > return a complete set of data. when I use the preview screen in reporting
> > services, the sproc does not return anything, yet when i run the sproc
> > from
> > anywhere else, it works. Is there a setting I am missing to be able pass
> > balnk values to a sproc from the report?
> >
> > Yes, if i pass values to the report, not blanks, the sproc does run and
> > returns data.
> >
> > --
> > John Cleveland
> > Network Manager
> > Urban systems Ltd
> >
>
>
Thursday, February 16, 2012
BLANK Lines
Hi,
I am running a stored procedure with many select
statements in it. I want the output from the stored
procedure NOT to produce Blank lines between each select
query. How can I do this. I am using Query Analyser to
execute the stored procedure and saving the results to a
text file.
Thanks,
SerghiosHi
It is not clear what you are trying to do here. Output in Query analyser is
not really designed for reports. You may want to look at a different tool.
If you are using something like ADO then it is not the blank lines that are
a problem! You will need to move to the next record set to obtain the second
set of results.
Alternatively... if the outputs are the same then you could use a union.
John
"Serghios" <Serghios.Florides@.elthion.com> wrote in message
news:37aa01c37494$4b8b2d60$a401280a@.phx.gbl...
> Hi,
> I am running a stored procedure with many select
> statements in it. I want the output from the stored
> procedure NOT to produce Blank lines between each select
> query. How can I do this. I am using Query Analyser to
> execute the stored procedure and saving the results to a
> text file.
> Thanks,
> Serghios
I am running a stored procedure with many select
statements in it. I want the output from the stored
procedure NOT to produce Blank lines between each select
query. How can I do this. I am using Query Analyser to
execute the stored procedure and saving the results to a
text file.
Thanks,
SerghiosHi
It is not clear what you are trying to do here. Output in Query analyser is
not really designed for reports. You may want to look at a different tool.
If you are using something like ADO then it is not the blank lines that are
a problem! You will need to move to the next record set to obtain the second
set of results.
Alternatively... if the outputs are the same then you could use a union.
John
"Serghios" <Serghios.Florides@.elthion.com> wrote in message
news:37aa01c37494$4b8b2d60$a401280a@.phx.gbl...
> Hi,
> I am running a stored procedure with many select
> statements in it. I want the output from the stored
> procedure NOT to produce Blank lines between each select
> query. How can I do this. I am using Query Analyser to
> execute the stored procedure and saving the results to a
> text file.
> Thanks,
> Serghios
Blank Field List When Using Stored Procedure
I am trying to use SQL Server 2000 stored procedure that does not take
any parameters in the Report Designer. When I run dataset in Data tab
of report designer and try to REFRESH the fields, this doesn't help
to get a list of fields. I get no error but no data. So the field list
is still empty. I tried
manually entering the fields via the Fields tab on the Data Set view
or by right clicking in the Fields window and this didn't help. When I
try to run the
report I get "An Error has occured during processing. Invalid attempt
to read when no data is present."
This is my stored procedure:
----
CREATE PROC dbo.sp_branch_counts
AS
IF OBJECT_ID('dbo.temp') IS NOT NULL
drop table [temp]
CREATE TABLE [temp] (
[Branch] [nvarchar] (255) NULL,
[InProgress] int NULL,
[Taken] int NULL,
[Dropped] int NULL
)
GO
declare @.loc nvarchar(50)
DECLARE loc_cursor CURSOR LOCAL
FOR
select branch_id from Branch
FOR READ ONLY
OPEN loc_cursor
FETCH NEXT FROM loc_cursor INTO @.loc
WHILE @.@.FETCH_STATUS = 0
BEGIN
insert into [temp] ([Branch],[InProgress],[Taken],[Dropped])
select distinct @.loc, * from
(select count(t.tran_status_id) as InProgress
from MYPELLA_USER_SHORT u INNER JOIN
[TRANSACTION] t ON u.username = t.USER_NAME
INNER JOIN OFFERING o ON t.OFFERING_ID = o.OFFERING_ID
where T.TRAN_CREATE_DATE >='05/01/2004' AND T.TRAN_CREATE_DATE <= dateadd(day,1,'07/01/2004')
and t.tran_status_id=1
and u.branch=@.loc) a,
(select count(t.tran_status_id) as Taken
from MYPELLA_USER_SHORT u INNER JOIN
[TRANSACTION] t ON u.username = t.USER_NAME
INNER JOIN OFFERING o ON t.OFFERING_ID = o.OFFERING_ID
where T.TRAN_CREATE_DATE >='05/01/2004' AND T.TRAN_CREATE_DATE <= dateadd(day,1,'07/01/2004')
and t.tran_status_id=2
and u.branch=@.loc) b,
(select count(t.tran_status_id) as Dropped
from MYPELLA_USER_SHORT u INNER JOIN
[TRANSACTION] t ON u.username = t.USER_NAME
INNER JOIN OFFERING o ON t.OFFERING_ID = o.OFFERING_ID
where T.TRAN_CREATE_DATE >='05/01/2004' AND T.TRAN_CREATE_DATE <= dateadd(day,1,'07/01/2004')
and t.tran_status_id=3
and u.branch=@.loc) c
FETCH NEXT FROM loc_cursor INTO @.loc
END
CLOSE loc_cursor
DEALLOCATE loc_cursor
select * from [temp] order by Branch
GO
Please help!!!Have you tried using a table variable in your stored procedure.
"Natasha" wrote:
> I am trying to use SQL Server 2000 stored procedure that does not take
> any parameters in the Report Designer. When I run dataset in Data tab
> of report designer and try to REFRESH the fields, this doesn't help
> to get a list of fields. I get no error but no data. So the field list
> is still empty. I tried
> manually entering the fields via the Fields tab on the Data Set view
> or by right clicking in the Fields window and this didn't help. When I
> try to run the
> report I get "An Error has occured during processing. Invalid attempt
> to read when no data is present."
> This is my stored procedure:
> ----
> CREATE PROC dbo.sp_branch_counts
> AS
> IF OBJECT_ID('dbo.temp') IS NOT NULL
> drop table [temp]
> CREATE TABLE [temp] (
> [Branch] [nvarchar] (255) NULL,
> [InProgress] int NULL,
> [Taken] int NULL,
> [Dropped] int NULL
> )
> GO
> declare @.loc nvarchar(50)
> DECLARE loc_cursor CURSOR LOCAL
> FOR
> select branch_id from Branch
> FOR READ ONLY
> OPEN loc_cursor
> FETCH NEXT FROM loc_cursor INTO @.loc
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> insert into [temp] ([Branch],[InProgress],[Taken],[Dropped])
> select distinct @.loc, * from
> (select count(t.tran_status_id) as InProgress
> from MYPELLA_USER_SHORT u INNER JOIN
> [TRANSACTION] t ON u.username = t.USER_NAME
> INNER JOIN OFFERING o ON t.OFFERING_ID = o.OFFERING_ID
> where T.TRAN_CREATE_DATE >='05/01/2004' AND T.TRAN_CREATE_DATE <=> dateadd(day,1,'07/01/2004')
> and t.tran_status_id=1
> and u.branch=@.loc) a,
> (select count(t.tran_status_id) as Taken
> from MYPELLA_USER_SHORT u INNER JOIN
> [TRANSACTION] t ON u.username = t.USER_NAME
> INNER JOIN OFFERING o ON t.OFFERING_ID = o.OFFERING_ID
> where T.TRAN_CREATE_DATE >='05/01/2004' AND T.TRAN_CREATE_DATE <=> dateadd(day,1,'07/01/2004')
> and t.tran_status_id=2
> and u.branch=@.loc) b,
> (select count(t.tran_status_id) as Dropped
> from MYPELLA_USER_SHORT u INNER JOIN
> [TRANSACTION] t ON u.username = t.USER_NAME
> INNER JOIN OFFERING o ON t.OFFERING_ID = o.OFFERING_ID
> where T.TRAN_CREATE_DATE >='05/01/2004' AND T.TRAN_CREATE_DATE <=> dateadd(day,1,'07/01/2004')
> and t.tran_status_id=3
> and u.branch=@.loc) c
> FETCH NEXT FROM loc_cursor INTO @.loc
> END
> CLOSE loc_cursor
> DEALLOCATE loc_cursor
> select * from [temp] order by Branch
> GO
> Please help!!!
>|||I tried it and it worked. Thank you so much :)
Natasha
"B. Mark McKinney" <B. Mark McKinney@.discussions.microsoft.com> wrote in message news:<B724506F-89BA-41B9-B786-9E02FFFA17D3@.microsoft.com>...
> Have you tried using a table variable in your stored procedure.
> "Natasha" wrote:
> > I am trying to use SQL Server 2000 stored procedure that does not take
> > any parameters in the Report Designer. When I run dataset in Data tab
> > of report designer and try to REFRESH the fields, this doesn't help
> > to get a list of fields. I get no error but no data. So the field list
> > is still empty. I tried
> > manually entering the fields via the Fields tab on the Data Set view
> > or by right clicking in the Fields window and this didn't help. When I
> > try to run the
> > report I get "An Error has occured during processing. Invalid attempt
> > to read when no data is present."
> >
> > This is my stored procedure:
> > ----
> > CREATE PROC dbo.sp_branch_counts
> > AS
> >
> > IF OBJECT_ID('dbo.temp') IS NOT NULL
> > drop table [temp]
> > CREATE TABLE [temp] (
> > [Branch] [nvarchar] (255) NULL,
> > [InProgress] int NULL,
> > [Taken] int NULL,
> > [Dropped] int NULL
> > )
> > GO
> > declare @.loc nvarchar(50)
> >
> > DECLARE loc_cursor CURSOR LOCAL
> > FOR
> > select branch_id from Branch
> > FOR READ ONLY
> > OPEN loc_cursor
> > FETCH NEXT FROM loc_cursor INTO @.loc
> > WHILE @.@.FETCH_STATUS = 0
> > BEGIN
> > insert into [temp] ([Branch],[InProgress],[Taken],[Dropped])
> >
> > select distinct @.loc, * from
> > (select count(t.tran_status_id) as InProgress
> > from MYPELLA_USER_SHORT u INNER JOIN
> > [TRANSACTION] t ON u.username = t.USER_NAME
> > INNER JOIN OFFERING o ON t.OFFERING_ID = o.OFFERING_ID
> > where T.TRAN_CREATE_DATE >='05/01/2004' AND T.TRAN_CREATE_DATE <=> > dateadd(day,1,'07/01/2004')
> > and t.tran_status_id=1
> > and u.branch=@.loc) a,
> > (select count(t.tran_status_id) as Taken
> > from MYPELLA_USER_SHORT u INNER JOIN
> > [TRANSACTION] t ON u.username = t.USER_NAME
> > INNER JOIN OFFERING o ON t.OFFERING_ID = o.OFFERING_ID
> > where T.TRAN_CREATE_DATE >='05/01/2004' AND T.TRAN_CREATE_DATE <=> > dateadd(day,1,'07/01/2004')
> > and t.tran_status_id=2
> > and u.branch=@.loc) b,
> > (select count(t.tran_status_id) as Dropped
> > from MYPELLA_USER_SHORT u INNER JOIN
> > [TRANSACTION] t ON u.username = t.USER_NAME
> > INNER JOIN OFFERING o ON t.OFFERING_ID = o.OFFERING_ID
> > where T.TRAN_CREATE_DATE >='05/01/2004' AND T.TRAN_CREATE_DATE <=> > dateadd(day,1,'07/01/2004')
> > and t.tran_status_id=3
> > and u.branch=@.loc) c
> > FETCH NEXT FROM loc_cursor INTO @.loc
> > END
> > CLOSE loc_cursor
> > DEALLOCATE loc_cursor
> >
> > select * from [temp] order by Branch
> > GO
> >
> > Please help!!!
> >
any parameters in the Report Designer. When I run dataset in Data tab
of report designer and try to REFRESH the fields, this doesn't help
to get a list of fields. I get no error but no data. So the field list
is still empty. I tried
manually entering the fields via the Fields tab on the Data Set view
or by right clicking in the Fields window and this didn't help. When I
try to run the
report I get "An Error has occured during processing. Invalid attempt
to read when no data is present."
This is my stored procedure:
----
CREATE PROC dbo.sp_branch_counts
AS
IF OBJECT_ID('dbo.temp') IS NOT NULL
drop table [temp]
CREATE TABLE [temp] (
[Branch] [nvarchar] (255) NULL,
[InProgress] int NULL,
[Taken] int NULL,
[Dropped] int NULL
)
GO
declare @.loc nvarchar(50)
DECLARE loc_cursor CURSOR LOCAL
FOR
select branch_id from Branch
FOR READ ONLY
OPEN loc_cursor
FETCH NEXT FROM loc_cursor INTO @.loc
WHILE @.@.FETCH_STATUS = 0
BEGIN
insert into [temp] ([Branch],[InProgress],[Taken],[Dropped])
select distinct @.loc, * from
(select count(t.tran_status_id) as InProgress
from MYPELLA_USER_SHORT u INNER JOIN
[TRANSACTION] t ON u.username = t.USER_NAME
INNER JOIN OFFERING o ON t.OFFERING_ID = o.OFFERING_ID
where T.TRAN_CREATE_DATE >='05/01/2004' AND T.TRAN_CREATE_DATE <= dateadd(day,1,'07/01/2004')
and t.tran_status_id=1
and u.branch=@.loc) a,
(select count(t.tran_status_id) as Taken
from MYPELLA_USER_SHORT u INNER JOIN
[TRANSACTION] t ON u.username = t.USER_NAME
INNER JOIN OFFERING o ON t.OFFERING_ID = o.OFFERING_ID
where T.TRAN_CREATE_DATE >='05/01/2004' AND T.TRAN_CREATE_DATE <= dateadd(day,1,'07/01/2004')
and t.tran_status_id=2
and u.branch=@.loc) b,
(select count(t.tran_status_id) as Dropped
from MYPELLA_USER_SHORT u INNER JOIN
[TRANSACTION] t ON u.username = t.USER_NAME
INNER JOIN OFFERING o ON t.OFFERING_ID = o.OFFERING_ID
where T.TRAN_CREATE_DATE >='05/01/2004' AND T.TRAN_CREATE_DATE <= dateadd(day,1,'07/01/2004')
and t.tran_status_id=3
and u.branch=@.loc) c
FETCH NEXT FROM loc_cursor INTO @.loc
END
CLOSE loc_cursor
DEALLOCATE loc_cursor
select * from [temp] order by Branch
GO
Please help!!!Have you tried using a table variable in your stored procedure.
"Natasha" wrote:
> I am trying to use SQL Server 2000 stored procedure that does not take
> any parameters in the Report Designer. When I run dataset in Data tab
> of report designer and try to REFRESH the fields, this doesn't help
> to get a list of fields. I get no error but no data. So the field list
> is still empty. I tried
> manually entering the fields via the Fields tab on the Data Set view
> or by right clicking in the Fields window and this didn't help. When I
> try to run the
> report I get "An Error has occured during processing. Invalid attempt
> to read when no data is present."
> This is my stored procedure:
> ----
> CREATE PROC dbo.sp_branch_counts
> AS
> IF OBJECT_ID('dbo.temp') IS NOT NULL
> drop table [temp]
> CREATE TABLE [temp] (
> [Branch] [nvarchar] (255) NULL,
> [InProgress] int NULL,
> [Taken] int NULL,
> [Dropped] int NULL
> )
> GO
> declare @.loc nvarchar(50)
> DECLARE loc_cursor CURSOR LOCAL
> FOR
> select branch_id from Branch
> FOR READ ONLY
> OPEN loc_cursor
> FETCH NEXT FROM loc_cursor INTO @.loc
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> insert into [temp] ([Branch],[InProgress],[Taken],[Dropped])
> select distinct @.loc, * from
> (select count(t.tran_status_id) as InProgress
> from MYPELLA_USER_SHORT u INNER JOIN
> [TRANSACTION] t ON u.username = t.USER_NAME
> INNER JOIN OFFERING o ON t.OFFERING_ID = o.OFFERING_ID
> where T.TRAN_CREATE_DATE >='05/01/2004' AND T.TRAN_CREATE_DATE <=> dateadd(day,1,'07/01/2004')
> and t.tran_status_id=1
> and u.branch=@.loc) a,
> (select count(t.tran_status_id) as Taken
> from MYPELLA_USER_SHORT u INNER JOIN
> [TRANSACTION] t ON u.username = t.USER_NAME
> INNER JOIN OFFERING o ON t.OFFERING_ID = o.OFFERING_ID
> where T.TRAN_CREATE_DATE >='05/01/2004' AND T.TRAN_CREATE_DATE <=> dateadd(day,1,'07/01/2004')
> and t.tran_status_id=2
> and u.branch=@.loc) b,
> (select count(t.tran_status_id) as Dropped
> from MYPELLA_USER_SHORT u INNER JOIN
> [TRANSACTION] t ON u.username = t.USER_NAME
> INNER JOIN OFFERING o ON t.OFFERING_ID = o.OFFERING_ID
> where T.TRAN_CREATE_DATE >='05/01/2004' AND T.TRAN_CREATE_DATE <=> dateadd(day,1,'07/01/2004')
> and t.tran_status_id=3
> and u.branch=@.loc) c
> FETCH NEXT FROM loc_cursor INTO @.loc
> END
> CLOSE loc_cursor
> DEALLOCATE loc_cursor
> select * from [temp] order by Branch
> GO
> Please help!!!
>|||I tried it and it worked. Thank you so much :)
Natasha
"B. Mark McKinney" <B. Mark McKinney@.discussions.microsoft.com> wrote in message news:<B724506F-89BA-41B9-B786-9E02FFFA17D3@.microsoft.com>...
> Have you tried using a table variable in your stored procedure.
> "Natasha" wrote:
> > I am trying to use SQL Server 2000 stored procedure that does not take
> > any parameters in the Report Designer. When I run dataset in Data tab
> > of report designer and try to REFRESH the fields, this doesn't help
> > to get a list of fields. I get no error but no data. So the field list
> > is still empty. I tried
> > manually entering the fields via the Fields tab on the Data Set view
> > or by right clicking in the Fields window and this didn't help. When I
> > try to run the
> > report I get "An Error has occured during processing. Invalid attempt
> > to read when no data is present."
> >
> > This is my stored procedure:
> > ----
> > CREATE PROC dbo.sp_branch_counts
> > AS
> >
> > IF OBJECT_ID('dbo.temp') IS NOT NULL
> > drop table [temp]
> > CREATE TABLE [temp] (
> > [Branch] [nvarchar] (255) NULL,
> > [InProgress] int NULL,
> > [Taken] int NULL,
> > [Dropped] int NULL
> > )
> > GO
> > declare @.loc nvarchar(50)
> >
> > DECLARE loc_cursor CURSOR LOCAL
> > FOR
> > select branch_id from Branch
> > FOR READ ONLY
> > OPEN loc_cursor
> > FETCH NEXT FROM loc_cursor INTO @.loc
> > WHILE @.@.FETCH_STATUS = 0
> > BEGIN
> > insert into [temp] ([Branch],[InProgress],[Taken],[Dropped])
> >
> > select distinct @.loc, * from
> > (select count(t.tran_status_id) as InProgress
> > from MYPELLA_USER_SHORT u INNER JOIN
> > [TRANSACTION] t ON u.username = t.USER_NAME
> > INNER JOIN OFFERING o ON t.OFFERING_ID = o.OFFERING_ID
> > where T.TRAN_CREATE_DATE >='05/01/2004' AND T.TRAN_CREATE_DATE <=> > dateadd(day,1,'07/01/2004')
> > and t.tran_status_id=1
> > and u.branch=@.loc) a,
> > (select count(t.tran_status_id) as Taken
> > from MYPELLA_USER_SHORT u INNER JOIN
> > [TRANSACTION] t ON u.username = t.USER_NAME
> > INNER JOIN OFFERING o ON t.OFFERING_ID = o.OFFERING_ID
> > where T.TRAN_CREATE_DATE >='05/01/2004' AND T.TRAN_CREATE_DATE <=> > dateadd(day,1,'07/01/2004')
> > and t.tran_status_id=2
> > and u.branch=@.loc) b,
> > (select count(t.tran_status_id) as Dropped
> > from MYPELLA_USER_SHORT u INNER JOIN
> > [TRANSACTION] t ON u.username = t.USER_NAME
> > INNER JOIN OFFERING o ON t.OFFERING_ID = o.OFFERING_ID
> > where T.TRAN_CREATE_DATE >='05/01/2004' AND T.TRAN_CREATE_DATE <=> > dateadd(day,1,'07/01/2004')
> > and t.tran_status_id=3
> > and u.branch=@.loc) c
> > FETCH NEXT FROM loc_cursor INTO @.loc
> > END
> > CLOSE loc_cursor
> > DEALLOCATE loc_cursor
> >
> > select * from [temp] order by Branch
> > GO
> >
> > Please help!!!
> >
Sunday, February 12, 2012
Bizarre Stored Procedure Behavior
I have dozens of stored procedures, but one of them is suddenly behaving badly.
Basically, if the sp is run as a query (full text), it runs fine and finishes successfully, but if 'executed' (Exec [sp_name]) it fails, saying that one of the referenced tables doesn't exist. The really weird thing is that the table is created earlier in the same procedure and is recognized enough to allow an index to be created on it. But when it comes to an Update statement, forget it. The sp dies saying the object (table) is unrecognized. I have dropped and recreated it, tried everything I can think of.
Has anyone else experienced this? Any ideas what may be causing it?
I'm stumped.
Thanks!you do have the correct names on your command object of the update statement?
Friday, February 10, 2012
BitArray to binary
I want to store a binary string in SqlServer from VB .NET through a
stored procedure. I have been storing the binary data in a BitArray
which I am not set on by any means. When I pass the BitArray into a
stored procedure, which is looking for a variable of type binary, it
throws the following: "Object must implement IConvertible." BitArray
obviously does not implement IConvertable.
stored procedure. I have been storing the binary data in a BitArray
which I am not set on by any means. When I pass the BitArray into a
stored procedure, which is looking for a variable of type binary, it
throws the following: "Object must implement IConvertible." BitArray
obviously does not implement IConvertable.
If anyone has a good way of passing binary data into a stored
procedure through a parameter array I'd appreciate it."Ramzi Abboud" <ramziabb@.gmail.com> wrote in message
news:a5617e65.0410141217.21e48d0@.posting.google.co m...
>I want to store a binary string in SqlServer from VB .NET through a
> stored procedure. I have been storing the binary data in a BitArray
> which I am not set on by any means. When I pass the BitArray into a
> stored procedure, which is looking for a variable of type binary, it
> throws the following: "Object must implement IConvertible." BitArray
> obviously does not implement IConvertable.
> If anyone has a good way of passing binary data into a stored
> procedure through a parameter array I'd appreciate it.
You might get a better answer by posting in a .NET or VB group. I don't know
what a BitArray is, but you can use the ADO Command object to execute stored
procedures with correctly typed parameters, which might be another
possiblity for you.
Simon
bit value as parameter
Hi,
I have a Store Procedure and include a bit parameter need to pass, such as:
exec spGenerateL1 'Bu01', Bit Value,'Administrator'
But I don't know how to pass the Bit Value,
I try to use 0, 1, TRUE, FALSE, the error message always response:
Error converting data type varchar to bit.
Someone can give me a favor, Thanks!
Angi
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Store Procedure as follow:
CREATE PROCEDURE spGenerateL1
@.Category AS NVARCHAR(10), @.KpiUserMode AS BIT, @.Orgn AS NVARCHAR(15),
@.Chart AS NVARCHAR(30)
AS
SET NOCOUNT ON
DECLARE @.tKpiName AS NVARCHAR(40)
DECLARE @.tUnitType AS NVARCHAR( 1)
DECLARE @.tCurrentValue AS DECIMAL(7,2)
DECLARE @.tRange AS BINARY
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE spCursor CURSOR FOR
SELECT a.KpiName, a.UnitType, b.CurrentValue
FROM BisKpi a INNER JOIN BisKpiDetail b
ON a.KpiGUID = b.KpiGUID
AND a.Category = @.Category AND a.KpiUserMode = @.KpiUserMode AND b.Orgn =
@.Orgn
AND b.StartDate = (SELECT FirstDateOfWeek FROM BisCalendar WHERE
DATEDIFF(DAY, FullDate, GETDATE()) = 0)
OPEN spCursor
FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.tRange = (SELECT CASE
WHEN @.tCurrentValue BETWEEN 1 AND 10 THEN
COALESCE(range10,range20,range30,range40
,range50,range60,range70,range80,ran
ge90,range100)
WHEN @.tCurrentValue BETWEEN 10 AND 20 THEN
COALESCE(range20,range30,range40,range50
,range60,range70,range80,range90,ran
ge100)
WHEN @.tCurrentValue BETWEEN 20 AND 30 THEN
COALESCE(range30,range40,range50,range60
,range70,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 30 AND 40 THEN
COALESCE(range40,range50,range60,range70
,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 40 AND 50 THEN
COALESCE(range50,range60,range70,range80
,range90,range100)
WHEN @.tCurrentValue BETWEEN 50 AND 60 THEN
COALESCE(range60,range70,range80,range90
,range100)
WHEN @.tCurrentValue BETWEEN 60 AND 70 THEN
COALESCE(range70,range80,range90,range10
0)
WHEN @.tCurrentValue BETWEEN 70 AND 80 THEN
COALESCE(range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 80 AND 90 THEN COALESCE(range90,range100)
WHEN @.tCurrentValue BETWEEN 90 AND 100 THEN range100
ELSE range0
END AS Indicator
FROM Bis.DBO.ChartConfig WHERE ChartName = '-TpO')
SELECT @.tKpiName, @.tUnitType, @.tCurrentValue, @.tRange
FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
END
CLOSE spCursor
DEALLOCATE spCursorIn your table "BisKpi", what data type is the field "KpuUserMode"
defined as?|||Hi, Tracy,
The field's data type is Bit.
Angi
"Tracy McKibben" <tracy.mckibben@.gmail.com>
'?:1148386860.513912.28450@.y43g2000cwc.googlegroups.com...
> In your table "BisKpi", what data type is the field "KpuUserMode"
> defined as?
>|||Hi Angi
Try this:
declare @.mybit bit
set @.mybit = 1 -- or set @.mybit = 0
exec spGenerateL1 'Bu01', @.mybit ,'Administrator'
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"angi" <angi@.news.microsoft.com> wrote in message
news:%23Hb6UdkfGHA.3916@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have a Store Procedure and include a bit parameter need to pass, such
> as:
> exec spGenerateL1 'Bu01', Bit Value,'Administrator'
> But I don't know how to pass the Bit Value,
> I try to use 0, 1, TRUE, FALSE, the error message always response:
> Error converting data type varchar to bit.
> Someone can give me a favor, Thanks!
> Angi
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Store Procedure as follow:
> CREATE PROCEDURE spGenerateL1
> @.Category AS NVARCHAR(10), @.KpiUserMode AS BIT, @.Orgn AS NVARCHAR(15),
> @.Chart AS NVARCHAR(30)
> AS
> SET NOCOUNT ON
> DECLARE @.tKpiName AS NVARCHAR(40)
> DECLARE @.tUnitType AS NVARCHAR( 1)
> DECLARE @.tCurrentValue AS DECIMAL(7,2)
> DECLARE @.tRange AS BINARY
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> DECLARE spCursor CURSOR FOR
> SELECT a.KpiName, a.UnitType, b.CurrentValue
> FROM BisKpi a INNER JOIN BisKpiDetail b
> ON a.KpiGUID = b.KpiGUID
> AND a.Category = @.Category AND a.KpiUserMode = @.KpiUserMode AND b.Orgn =
> @.Orgn
> AND b.StartDate = (SELECT FirstDateOfWeek FROM BisCalendar WHERE
> DATEDIFF(DAY, FullDate, GETDATE()) = 0)
> OPEN spCursor
> FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.tRange = (SELECT CASE
> WHEN @.tCurrentValue BETWEEN 1 AND 10 THEN
> COALESCE(range10,range20,range30,range40
,range50,range60,range70,range80,r
ange90,range100)
> WHEN @.tCurrentValue BETWEEN 10 AND 20 THEN
> COALESCE(range20,range30,range40,range50
,range60,range70,range80,range90,r
ange100)
> WHEN @.tCurrentValue BETWEEN 20 AND 30 THEN
> COALESCE(range30,range40,range50,range60
,range70,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 30 AND 40 THEN
> COALESCE(range40,range50,range60,range70
,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 40 AND 50 THEN
> COALESCE(range50,range60,range70,range80
,range90,range100)
> WHEN @.tCurrentValue BETWEEN 50 AND 60 THEN
> COALESCE(range60,range70,range80,range90
,range100)
> WHEN @.tCurrentValue BETWEEN 60 AND 70 THEN
> COALESCE(range70,range80,range90,range10
0)
> WHEN @.tCurrentValue BETWEEN 70 AND 80 THEN
> COALESCE(range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 80 AND 90 THEN COALESCE(range90,range100)
> WHEN @.tCurrentValue BETWEEN 90 AND 100 THEN range100
> ELSE range0
> END AS Indicator
> FROM Bis.DBO.ChartConfig WHERE ChartName = '-TpO')
> SELECT @.tKpiName, @.tUnitType, @.tCurrentValue, @.tRange
> FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
> END
> CLOSE spCursor
> DEALLOCATE spCursor
>|||Kalen,
Thank u very much!
Thanks for Tracy, too.
For this point, if Bit field needs to implement such way to pass parameter,
it seems inconvenient and let me want to change it's data type as char or
something else to be a flag.
So, any good idea about use Bit as a flag? or what's kind of data type is
better to be a flag?
Thanks!
Angi
"Kalen Delaney" <replies@.public_newsgroups.com> glsD:OTlfCgtfGHA.4880@.TK2MSFTNGP03
.phx.gbl...
> Hi Angi
> Try this:
> declare @.mybit bit
> set @.mybit = 1 -- or set @.mybit = 0
> exec spGenerateL1 'Bu01', @.mybit ,'Administrator'
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "angi" <angi@.news.microsoft.com> wrote in message
> news:%23Hb6UdkfGHA.3916@.TK2MSFTNGP04.phx.gbl...
>
I have a Store Procedure and include a bit parameter need to pass, such as:
exec spGenerateL1 'Bu01', Bit Value,'Administrator'
But I don't know how to pass the Bit Value,
I try to use 0, 1, TRUE, FALSE, the error message always response:
Error converting data type varchar to bit.
Someone can give me a favor, Thanks!
Angi
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Store Procedure as follow:
CREATE PROCEDURE spGenerateL1
@.Category AS NVARCHAR(10), @.KpiUserMode AS BIT, @.Orgn AS NVARCHAR(15),
@.Chart AS NVARCHAR(30)
AS
SET NOCOUNT ON
DECLARE @.tKpiName AS NVARCHAR(40)
DECLARE @.tUnitType AS NVARCHAR( 1)
DECLARE @.tCurrentValue AS DECIMAL(7,2)
DECLARE @.tRange AS BINARY
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE spCursor CURSOR FOR
SELECT a.KpiName, a.UnitType, b.CurrentValue
FROM BisKpi a INNER JOIN BisKpiDetail b
ON a.KpiGUID = b.KpiGUID
AND a.Category = @.Category AND a.KpiUserMode = @.KpiUserMode AND b.Orgn =
@.Orgn
AND b.StartDate = (SELECT FirstDateOfWeek FROM BisCalendar WHERE
DATEDIFF(DAY, FullDate, GETDATE()) = 0)
OPEN spCursor
FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.tRange = (SELECT CASE
WHEN @.tCurrentValue BETWEEN 1 AND 10 THEN
COALESCE(range10,range20,range30,range40
,range50,range60,range70,range80,ran
ge90,range100)
WHEN @.tCurrentValue BETWEEN 10 AND 20 THEN
COALESCE(range20,range30,range40,range50
,range60,range70,range80,range90,ran
ge100)
WHEN @.tCurrentValue BETWEEN 20 AND 30 THEN
COALESCE(range30,range40,range50,range60
,range70,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 30 AND 40 THEN
COALESCE(range40,range50,range60,range70
,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 40 AND 50 THEN
COALESCE(range50,range60,range70,range80
,range90,range100)
WHEN @.tCurrentValue BETWEEN 50 AND 60 THEN
COALESCE(range60,range70,range80,range90
,range100)
WHEN @.tCurrentValue BETWEEN 60 AND 70 THEN
COALESCE(range70,range80,range90,range10
0)
WHEN @.tCurrentValue BETWEEN 70 AND 80 THEN
COALESCE(range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 80 AND 90 THEN COALESCE(range90,range100)
WHEN @.tCurrentValue BETWEEN 90 AND 100 THEN range100
ELSE range0
END AS Indicator
FROM Bis.DBO.ChartConfig WHERE ChartName = '-TpO')
SELECT @.tKpiName, @.tUnitType, @.tCurrentValue, @.tRange
FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
END
CLOSE spCursor
DEALLOCATE spCursorIn your table "BisKpi", what data type is the field "KpuUserMode"
defined as?|||Hi, Tracy,
The field's data type is Bit.
Angi
"Tracy McKibben" <tracy.mckibben@.gmail.com>
'?:1148386860.513912.28450@.y43g2000cwc.googlegroups.com...
> In your table "BisKpi", what data type is the field "KpuUserMode"
> defined as?
>|||Hi Angi
Try this:
declare @.mybit bit
set @.mybit = 1 -- or set @.mybit = 0
exec spGenerateL1 'Bu01', @.mybit ,'Administrator'
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"angi" <angi@.news.microsoft.com> wrote in message
news:%23Hb6UdkfGHA.3916@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have a Store Procedure and include a bit parameter need to pass, such
> as:
> exec spGenerateL1 'Bu01', Bit Value,'Administrator'
> But I don't know how to pass the Bit Value,
> I try to use 0, 1, TRUE, FALSE, the error message always response:
> Error converting data type varchar to bit.
> Someone can give me a favor, Thanks!
> Angi
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Store Procedure as follow:
> CREATE PROCEDURE spGenerateL1
> @.Category AS NVARCHAR(10), @.KpiUserMode AS BIT, @.Orgn AS NVARCHAR(15),
> @.Chart AS NVARCHAR(30)
> AS
> SET NOCOUNT ON
> DECLARE @.tKpiName AS NVARCHAR(40)
> DECLARE @.tUnitType AS NVARCHAR( 1)
> DECLARE @.tCurrentValue AS DECIMAL(7,2)
> DECLARE @.tRange AS BINARY
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> DECLARE spCursor CURSOR FOR
> SELECT a.KpiName, a.UnitType, b.CurrentValue
> FROM BisKpi a INNER JOIN BisKpiDetail b
> ON a.KpiGUID = b.KpiGUID
> AND a.Category = @.Category AND a.KpiUserMode = @.KpiUserMode AND b.Orgn =
> @.Orgn
> AND b.StartDate = (SELECT FirstDateOfWeek FROM BisCalendar WHERE
> DATEDIFF(DAY, FullDate, GETDATE()) = 0)
> OPEN spCursor
> FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.tRange = (SELECT CASE
> WHEN @.tCurrentValue BETWEEN 1 AND 10 THEN
> COALESCE(range10,range20,range30,range40
,range50,range60,range70,range80,r
ange90,range100)
> WHEN @.tCurrentValue BETWEEN 10 AND 20 THEN
> COALESCE(range20,range30,range40,range50
,range60,range70,range80,range90,r
ange100)
> WHEN @.tCurrentValue BETWEEN 20 AND 30 THEN
> COALESCE(range30,range40,range50,range60
,range70,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 30 AND 40 THEN
> COALESCE(range40,range50,range60,range70
,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 40 AND 50 THEN
> COALESCE(range50,range60,range70,range80
,range90,range100)
> WHEN @.tCurrentValue BETWEEN 50 AND 60 THEN
> COALESCE(range60,range70,range80,range90
,range100)
> WHEN @.tCurrentValue BETWEEN 60 AND 70 THEN
> COALESCE(range70,range80,range90,range10
0)
> WHEN @.tCurrentValue BETWEEN 70 AND 80 THEN
> COALESCE(range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 80 AND 90 THEN COALESCE(range90,range100)
> WHEN @.tCurrentValue BETWEEN 90 AND 100 THEN range100
> ELSE range0
> END AS Indicator
> FROM Bis.DBO.ChartConfig WHERE ChartName = '-TpO')
> SELECT @.tKpiName, @.tUnitType, @.tCurrentValue, @.tRange
> FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
> END
> CLOSE spCursor
> DEALLOCATE spCursor
>|||Kalen,
Thank u very much!
Thanks for Tracy, too.
For this point, if Bit field needs to implement such way to pass parameter,
it seems inconvenient and let me want to change it's data type as char or
something else to be a flag.
So, any good idea about use Bit as a flag? or what's kind of data type is
better to be a flag?
Thanks!
Angi
"Kalen Delaney" <replies@.public_newsgroups.com> glsD:OTlfCgtfGHA.4880@.TK2MSFTNGP03
.phx.gbl...
> Hi Angi
> Try this:
> declare @.mybit bit
> set @.mybit = 1 -- or set @.mybit = 0
> exec spGenerateL1 'Bu01', @.mybit ,'Administrator'
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "angi" <angi@.news.microsoft.com> wrote in message
> news:%23Hb6UdkfGHA.3916@.TK2MSFTNGP04.phx.gbl...
>
bit value as parameter
Hi,
I have a Store Procedure and include a bit parameter need to pass, such as:
exec spGenerateL1 'Bu01', Bit Value,'Administrator'
But I don't know how to pass the Bit Value,
I try to use 0, 1, TRUE, FALSE, the error message always response:
Error converting data type varchar to bit.
Someone can give me a favor, Thanks!
Angi
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Store Procedure as follow:
CREATE PROCEDURE spGenerateL1
@.Category AS NVARCHAR(10), @.KpiUserMode AS BIT, @.Orgn AS NVARCHAR(15),
@.Chart AS NVARCHAR(30)
AS
SET NOCOUNT ON
DECLARE @.tKpiName AS NVARCHAR(40)
DECLARE @.tUnitType AS NVARCHAR( 1)
DECLARE @.tCurrentValue AS DECIMAL(7,2)
DECLARE @.tRange AS BINARY
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE spCursor CURSOR FOR
SELECT a.KpiName, a.UnitType, b.CurrentValue
FROM BisKpi a INNER JOIN BisKpiDetail b
ON a.KpiGUID = b.KpiGUID
AND a.Category = @.Category AND a.KpiUserMode = @.KpiUserMode AND b.Orgn = @.Orgn
AND b.StartDate = (SELECT FirstDateOfWeek FROM BisCalendar WHERE
DATEDIFF(DAY, FullDate, GETDATE()) = 0)
OPEN spCursor
FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.tRange = (SELECT CASE
WHEN @.tCurrentValue BETWEEN 1 AND 10 THEN
COALESCE(range10,range20,range30,range40,range50,range60,range70,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 10 AND 20 THEN
COALESCE(range20,range30,range40,range50,range60,range70,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 20 AND 30 THEN
COALESCE(range30,range40,range50,range60,range70,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 30 AND 40 THEN
COALESCE(range40,range50,range60,range70,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 40 AND 50 THEN
COALESCE(range50,range60,range70,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 50 AND 60 THEN
COALESCE(range60,range70,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 60 AND 70 THEN
COALESCE(range70,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 70 AND 80 THEN
COALESCE(range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 80 AND 90 THEN COALESCE(range90,range100)
WHEN @.tCurrentValue BETWEEN 90 AND 100 THEN range100
ELSE range0
END AS Indicator
FROM Bis.DBO.ChartConfig WHERE ChartName = '¾î-¤T¤p¬õºñ¿O')
SELECT @.tKpiName, @.tUnitType, @.tCurrentValue, @.tRange
FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
END
CLOSE spCursor
DEALLOCATE spCursorIn your table "BisKpi", what data type is the field "KpuUserMode"
defined as?|||Hi, Tracy,
The field's data type is Bit.
Angi
"Tracy McKibben" <tracy.mckibben@.gmail.com>
'?:1148386860.513912.28450@.y43g2000cwc.googlegroups.com...
> In your table "BisKpi", what data type is the field "KpuUserMode"
> defined as?
>|||Hi Angi
Try this:
declare @.mybit bit
set @.mybit = 1 -- or set @.mybit = 0
exec spGenerateL1 'Bu01', @.mybit ,'Administrator'
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"angi" <angi@.news.microsoft.com> wrote in message
news:%23Hb6UdkfGHA.3916@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have a Store Procedure and include a bit parameter need to pass, such
> as:
> exec spGenerateL1 'Bu01', Bit Value,'Administrator'
> But I don't know how to pass the Bit Value,
> I try to use 0, 1, TRUE, FALSE, the error message always response:
> Error converting data type varchar to bit.
> Someone can give me a favor, Thanks!
> Angi
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Store Procedure as follow:
> CREATE PROCEDURE spGenerateL1
> @.Category AS NVARCHAR(10), @.KpiUserMode AS BIT, @.Orgn AS NVARCHAR(15),
> @.Chart AS NVARCHAR(30)
> AS
> SET NOCOUNT ON
> DECLARE @.tKpiName AS NVARCHAR(40)
> DECLARE @.tUnitType AS NVARCHAR( 1)
> DECLARE @.tCurrentValue AS DECIMAL(7,2)
> DECLARE @.tRange AS BINARY
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> DECLARE spCursor CURSOR FOR
> SELECT a.KpiName, a.UnitType, b.CurrentValue
> FROM BisKpi a INNER JOIN BisKpiDetail b
> ON a.KpiGUID = b.KpiGUID
> AND a.Category = @.Category AND a.KpiUserMode = @.KpiUserMode AND b.Orgn => @.Orgn
> AND b.StartDate = (SELECT FirstDateOfWeek FROM BisCalendar WHERE
> DATEDIFF(DAY, FullDate, GETDATE()) = 0)
> OPEN spCursor
> FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.tRange = (SELECT CASE
> WHEN @.tCurrentValue BETWEEN 1 AND 10 THEN
> COALESCE(range10,range20,range30,range40,range50,range60,range70,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 10 AND 20 THEN
> COALESCE(range20,range30,range40,range50,range60,range70,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 20 AND 30 THEN
> COALESCE(range30,range40,range50,range60,range70,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 30 AND 40 THEN
> COALESCE(range40,range50,range60,range70,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 40 AND 50 THEN
> COALESCE(range50,range60,range70,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 50 AND 60 THEN
> COALESCE(range60,range70,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 60 AND 70 THEN
> COALESCE(range70,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 70 AND 80 THEN
> COALESCE(range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 80 AND 90 THEN COALESCE(range90,range100)
> WHEN @.tCurrentValue BETWEEN 90 AND 100 THEN range100
> ELSE range0
> END AS Indicator
> FROM Bis.DBO.ChartConfig WHERE ChartName = '¾î-¤T¤p¬õºñ¿O')
> SELECT @.tKpiName, @.tUnitType, @.tCurrentValue, @.tRange
> FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
> END
> CLOSE spCursor
> DEALLOCATE spCursor
>|||Kalen,
Thank u very much!
Thanks for Tracy, too.
For this point, if Bit field needs to implement such way to pass parameter,
it seems inconvenient and let me want to change it's data type as char or
something else to be a flag.
So, any good idea about use Bit as a flag? or what's kind of data type is
better to be a flag?
Thanks!
Angi
"Kalen Delaney" <replies@.public_newsgroups.com> ¼¶¼g©ó¶l¥ó·s»D:OTlfCgtfGHA.4880@.TK2MSFTNGP03.phx.gbl...
> Hi Angi
> Try this:
> declare @.mybit bit
> set @.mybit = 1 -- or set @.mybit = 0
> exec spGenerateL1 'Bu01', @.mybit ,'Administrator'
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "angi" <angi@.news.microsoft.com> wrote in message
> news:%23Hb6UdkfGHA.3916@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> I have a Store Procedure and include a bit parameter need to pass, such
>> as:
>> exec spGenerateL1 'Bu01', Bit Value,'Administrator'
>> But I don't know how to pass the Bit Value,
>> I try to use 0, 1, TRUE, FALSE, the error message always response:
>> Error converting data type varchar to bit.
>> Someone can give me a favor, Thanks!
>> Angi
>> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>> Store Procedure as follow:
>> CREATE PROCEDURE spGenerateL1
>> @.Category AS NVARCHAR(10), @.KpiUserMode AS BIT, @.Orgn AS NVARCHAR(15),
>> @.Chart AS NVARCHAR(30)
>> AS
>> SET NOCOUNT ON
>> DECLARE @.tKpiName AS NVARCHAR(40)
>> DECLARE @.tUnitType AS NVARCHAR( 1)
>> DECLARE @.tCurrentValue AS DECIMAL(7,2)
>> DECLARE @.tRange AS BINARY
>> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
>> DECLARE spCursor CURSOR FOR
>> SELECT a.KpiName, a.UnitType, b.CurrentValue
>> FROM BisKpi a INNER JOIN BisKpiDetail b
>> ON a.KpiGUID = b.KpiGUID
>> AND a.Category = @.Category AND a.KpiUserMode = @.KpiUserMode AND b.Orgn
>> = @.Orgn
>> AND b.StartDate = (SELECT FirstDateOfWeek FROM BisCalendar WHERE
>> DATEDIFF(DAY, FullDate, GETDATE()) = 0)
>> OPEN spCursor
>> FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
>> WHILE @.@.FETCH_STATUS = 0
>> BEGIN
>> SET @.tRange = (SELECT CASE
>> WHEN @.tCurrentValue BETWEEN 1 AND 10 THEN
>> COALESCE(range10,range20,range30,range40,range50,range60,range70,range80,range90,range100)
>> WHEN @.tCurrentValue BETWEEN 10 AND 20 THEN
>> COALESCE(range20,range30,range40,range50,range60,range70,range80,range90,range100)
>> WHEN @.tCurrentValue BETWEEN 20 AND 30 THEN
>> COALESCE(range30,range40,range50,range60,range70,range80,range90,range100)
>> WHEN @.tCurrentValue BETWEEN 30 AND 40 THEN
>> COALESCE(range40,range50,range60,range70,range80,range90,range100)
>> WHEN @.tCurrentValue BETWEEN 40 AND 50 THEN
>> COALESCE(range50,range60,range70,range80,range90,range100)
>> WHEN @.tCurrentValue BETWEEN 50 AND 60 THEN
>> COALESCE(range60,range70,range80,range90,range100)
>> WHEN @.tCurrentValue BETWEEN 60 AND 70 THEN
>> COALESCE(range70,range80,range90,range100)
>> WHEN @.tCurrentValue BETWEEN 70 AND 80 THEN
>> COALESCE(range80,range90,range100)
>> WHEN @.tCurrentValue BETWEEN 80 AND 90 THEN COALESCE(range90,range100)
>> WHEN @.tCurrentValue BETWEEN 90 AND 100 THEN range100
>> ELSE range0
>> END AS Indicator
>> FROM Bis.DBO.ChartConfig WHERE ChartName = '¾î-¤T¤p¬õºñ¿O')
>> SELECT @.tKpiName, @.tUnitType, @.tCurrentValue, @.tRange
>> FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
>> END
>> CLOSE spCursor
>> DEALLOCATE spCursor
>
I have a Store Procedure and include a bit parameter need to pass, such as:
exec spGenerateL1 'Bu01', Bit Value,'Administrator'
But I don't know how to pass the Bit Value,
I try to use 0, 1, TRUE, FALSE, the error message always response:
Error converting data type varchar to bit.
Someone can give me a favor, Thanks!
Angi
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Store Procedure as follow:
CREATE PROCEDURE spGenerateL1
@.Category AS NVARCHAR(10), @.KpiUserMode AS BIT, @.Orgn AS NVARCHAR(15),
@.Chart AS NVARCHAR(30)
AS
SET NOCOUNT ON
DECLARE @.tKpiName AS NVARCHAR(40)
DECLARE @.tUnitType AS NVARCHAR( 1)
DECLARE @.tCurrentValue AS DECIMAL(7,2)
DECLARE @.tRange AS BINARY
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE spCursor CURSOR FOR
SELECT a.KpiName, a.UnitType, b.CurrentValue
FROM BisKpi a INNER JOIN BisKpiDetail b
ON a.KpiGUID = b.KpiGUID
AND a.Category = @.Category AND a.KpiUserMode = @.KpiUserMode AND b.Orgn = @.Orgn
AND b.StartDate = (SELECT FirstDateOfWeek FROM BisCalendar WHERE
DATEDIFF(DAY, FullDate, GETDATE()) = 0)
OPEN spCursor
FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.tRange = (SELECT CASE
WHEN @.tCurrentValue BETWEEN 1 AND 10 THEN
COALESCE(range10,range20,range30,range40,range50,range60,range70,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 10 AND 20 THEN
COALESCE(range20,range30,range40,range50,range60,range70,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 20 AND 30 THEN
COALESCE(range30,range40,range50,range60,range70,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 30 AND 40 THEN
COALESCE(range40,range50,range60,range70,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 40 AND 50 THEN
COALESCE(range50,range60,range70,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 50 AND 60 THEN
COALESCE(range60,range70,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 60 AND 70 THEN
COALESCE(range70,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 70 AND 80 THEN
COALESCE(range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 80 AND 90 THEN COALESCE(range90,range100)
WHEN @.tCurrentValue BETWEEN 90 AND 100 THEN range100
ELSE range0
END AS Indicator
FROM Bis.DBO.ChartConfig WHERE ChartName = '¾î-¤T¤p¬õºñ¿O')
SELECT @.tKpiName, @.tUnitType, @.tCurrentValue, @.tRange
FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
END
CLOSE spCursor
DEALLOCATE spCursorIn your table "BisKpi", what data type is the field "KpuUserMode"
defined as?|||Hi, Tracy,
The field's data type is Bit.
Angi
"Tracy McKibben" <tracy.mckibben@.gmail.com>
'?:1148386860.513912.28450@.y43g2000cwc.googlegroups.com...
> In your table "BisKpi", what data type is the field "KpuUserMode"
> defined as?
>|||Hi Angi
Try this:
declare @.mybit bit
set @.mybit = 1 -- or set @.mybit = 0
exec spGenerateL1 'Bu01', @.mybit ,'Administrator'
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"angi" <angi@.news.microsoft.com> wrote in message
news:%23Hb6UdkfGHA.3916@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have a Store Procedure and include a bit parameter need to pass, such
> as:
> exec spGenerateL1 'Bu01', Bit Value,'Administrator'
> But I don't know how to pass the Bit Value,
> I try to use 0, 1, TRUE, FALSE, the error message always response:
> Error converting data type varchar to bit.
> Someone can give me a favor, Thanks!
> Angi
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Store Procedure as follow:
> CREATE PROCEDURE spGenerateL1
> @.Category AS NVARCHAR(10), @.KpiUserMode AS BIT, @.Orgn AS NVARCHAR(15),
> @.Chart AS NVARCHAR(30)
> AS
> SET NOCOUNT ON
> DECLARE @.tKpiName AS NVARCHAR(40)
> DECLARE @.tUnitType AS NVARCHAR( 1)
> DECLARE @.tCurrentValue AS DECIMAL(7,2)
> DECLARE @.tRange AS BINARY
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> DECLARE spCursor CURSOR FOR
> SELECT a.KpiName, a.UnitType, b.CurrentValue
> FROM BisKpi a INNER JOIN BisKpiDetail b
> ON a.KpiGUID = b.KpiGUID
> AND a.Category = @.Category AND a.KpiUserMode = @.KpiUserMode AND b.Orgn => @.Orgn
> AND b.StartDate = (SELECT FirstDateOfWeek FROM BisCalendar WHERE
> DATEDIFF(DAY, FullDate, GETDATE()) = 0)
> OPEN spCursor
> FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.tRange = (SELECT CASE
> WHEN @.tCurrentValue BETWEEN 1 AND 10 THEN
> COALESCE(range10,range20,range30,range40,range50,range60,range70,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 10 AND 20 THEN
> COALESCE(range20,range30,range40,range50,range60,range70,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 20 AND 30 THEN
> COALESCE(range30,range40,range50,range60,range70,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 30 AND 40 THEN
> COALESCE(range40,range50,range60,range70,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 40 AND 50 THEN
> COALESCE(range50,range60,range70,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 50 AND 60 THEN
> COALESCE(range60,range70,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 60 AND 70 THEN
> COALESCE(range70,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 70 AND 80 THEN
> COALESCE(range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 80 AND 90 THEN COALESCE(range90,range100)
> WHEN @.tCurrentValue BETWEEN 90 AND 100 THEN range100
> ELSE range0
> END AS Indicator
> FROM Bis.DBO.ChartConfig WHERE ChartName = '¾î-¤T¤p¬õºñ¿O')
> SELECT @.tKpiName, @.tUnitType, @.tCurrentValue, @.tRange
> FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
> END
> CLOSE spCursor
> DEALLOCATE spCursor
>|||Kalen,
Thank u very much!
Thanks for Tracy, too.
For this point, if Bit field needs to implement such way to pass parameter,
it seems inconvenient and let me want to change it's data type as char or
something else to be a flag.
So, any good idea about use Bit as a flag? or what's kind of data type is
better to be a flag?
Thanks!
Angi
"Kalen Delaney" <replies@.public_newsgroups.com> ¼¶¼g©ó¶l¥ó·s»D:OTlfCgtfGHA.4880@.TK2MSFTNGP03.phx.gbl...
> Hi Angi
> Try this:
> declare @.mybit bit
> set @.mybit = 1 -- or set @.mybit = 0
> exec spGenerateL1 'Bu01', @.mybit ,'Administrator'
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "angi" <angi@.news.microsoft.com> wrote in message
> news:%23Hb6UdkfGHA.3916@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> I have a Store Procedure and include a bit parameter need to pass, such
>> as:
>> exec spGenerateL1 'Bu01', Bit Value,'Administrator'
>> But I don't know how to pass the Bit Value,
>> I try to use 0, 1, TRUE, FALSE, the error message always response:
>> Error converting data type varchar to bit.
>> Someone can give me a favor, Thanks!
>> Angi
>> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>> Store Procedure as follow:
>> CREATE PROCEDURE spGenerateL1
>> @.Category AS NVARCHAR(10), @.KpiUserMode AS BIT, @.Orgn AS NVARCHAR(15),
>> @.Chart AS NVARCHAR(30)
>> AS
>> SET NOCOUNT ON
>> DECLARE @.tKpiName AS NVARCHAR(40)
>> DECLARE @.tUnitType AS NVARCHAR( 1)
>> DECLARE @.tCurrentValue AS DECIMAL(7,2)
>> DECLARE @.tRange AS BINARY
>> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
>> DECLARE spCursor CURSOR FOR
>> SELECT a.KpiName, a.UnitType, b.CurrentValue
>> FROM BisKpi a INNER JOIN BisKpiDetail b
>> ON a.KpiGUID = b.KpiGUID
>> AND a.Category = @.Category AND a.KpiUserMode = @.KpiUserMode AND b.Orgn
>> = @.Orgn
>> AND b.StartDate = (SELECT FirstDateOfWeek FROM BisCalendar WHERE
>> DATEDIFF(DAY, FullDate, GETDATE()) = 0)
>> OPEN spCursor
>> FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
>> WHILE @.@.FETCH_STATUS = 0
>> BEGIN
>> SET @.tRange = (SELECT CASE
>> WHEN @.tCurrentValue BETWEEN 1 AND 10 THEN
>> COALESCE(range10,range20,range30,range40,range50,range60,range70,range80,range90,range100)
>> WHEN @.tCurrentValue BETWEEN 10 AND 20 THEN
>> COALESCE(range20,range30,range40,range50,range60,range70,range80,range90,range100)
>> WHEN @.tCurrentValue BETWEEN 20 AND 30 THEN
>> COALESCE(range30,range40,range50,range60,range70,range80,range90,range100)
>> WHEN @.tCurrentValue BETWEEN 30 AND 40 THEN
>> COALESCE(range40,range50,range60,range70,range80,range90,range100)
>> WHEN @.tCurrentValue BETWEEN 40 AND 50 THEN
>> COALESCE(range50,range60,range70,range80,range90,range100)
>> WHEN @.tCurrentValue BETWEEN 50 AND 60 THEN
>> COALESCE(range60,range70,range80,range90,range100)
>> WHEN @.tCurrentValue BETWEEN 60 AND 70 THEN
>> COALESCE(range70,range80,range90,range100)
>> WHEN @.tCurrentValue BETWEEN 70 AND 80 THEN
>> COALESCE(range80,range90,range100)
>> WHEN @.tCurrentValue BETWEEN 80 AND 90 THEN COALESCE(range90,range100)
>> WHEN @.tCurrentValue BETWEEN 90 AND 100 THEN range100
>> ELSE range0
>> END AS Indicator
>> FROM Bis.DBO.ChartConfig WHERE ChartName = '¾î-¤T¤p¬õºñ¿O')
>> SELECT @.tKpiName, @.tUnitType, @.tCurrentValue, @.tRange
>> FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
>> END
>> CLOSE spCursor
>> DEALLOCATE spCursor
>
Bit type parameter for stored procedure
I am trying to supply a bit type parameter to a stored procedure. This is used to update a Bit type field in a table. The field is called PDI
Please please please always post the error encountered, not just the fact that you have an error.
The syntax I am trying to use is:
MyStoredProcedure.Parameters.Add(New SqlParameter("@.Pdi",SqlDbtype.bit))
MyStoredProcedure.Parameters("@.pdi").value = -1
When I do my ExecuteNonQuery I get error 8114
What am I doing wrong?The bit data type can hold one of the following values: 1, 0, or NULL. You cannot store a -1 in a bit column.
Terri|||Ok thanks for that. It will indeed accept 1 or 0
I now need to use the value from a CheckBox type control on my Form. So now
My Code reads:
MyStoredProcedure.Parameters.Add(New SqlParameter("@.Pdi",SqlDbtype.bit))
MyStoredProcedure.Parameters("@.pdi").value = pdi.value
This generates the Error: "String was not recognised as a valid boolean."
What is the syntax for casting to a bit type value?|||You will likely need to convert your string value to a boolean value, using the ToBoolean method, something like this:
MyStoredProcedure.Parameters("@.pdi").value = System.Convert.ToBoolean(pdi.value)
See this article with handy chart for reference:Accessing SQL Server Data in C# with ADO.NET.
Terri|||No that didn't work either.
Just for the hell of it I decided to put the value of the PDI control into a span control like this:
message.innerhtml=pdi.value
I expected to see True or False or 1 or 0 or something. What I got was S00817
What is that!! I'm obviosly going the wrong way about extracting the value from a CheckBox control|||We might need to see more of your code.
Are you using a CheckBox or a CheckBoxList?
If a CheckBox, then it would probably be pdi.Checked, which already returns a boolean value.
I am sorry to be giving you the answer in pieces.
Terri|||Hi,
You need to use:
MyStoredProcedure.Parameters.Add(New SqlParameter("@.Pdi",SqlDbtype.bit))
MyStoredProcedure.Parameters("@.pdi").value = pdi.checked
or unchecked.
HTH
JB|||Yes that fixed it.
MyStoredProcedure.Parameters("@.pdi").value = pdi.checked
Thanks for your help|||
my check box is in the datagrid. I used this line. it have error.
comUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.p_PDA", System.Data.SqlDbType.Bit, "PDA")).Value = CType(e.Item.FindControl("ckPDA"), CheckBox).Checked
Here is my html code. I tried to assign the check box value to byte. It did not work, too. Thanks.
<asp:datagrid id="dtgPhysician" style="Z-INDEX: 102; LEFT: 8px; POSITION: absolute; TOP: 88px" runat="server" Font-Names="Arial" OnCancelCommand="dtgPhysician_Cancel" OnEditCommand="dtgPhysician_Edit" Width="985px" AutoGenerateColumns="False" PagerStyle-Mode="NumericPages" DataKeyField="License" BorderColor="#3366CC" BorderStyle="None" BorderWidth="1px" CellPadding="4" ShowHeader="true" EditItemStyle-BackColor="#eeeeee" BackColor="White" OnUpdateCommand="dtgPhysician_Update">
<SelectedItemStyle Font-Bold="True" ForeColor="#CCFF99" BackColor="#009999"></SelectedItemStyle>
<EditItemStyle BackColor="#EEEEEE"></EditItemStyle>
<AlternatingItemStyle BorderColor="#C0C0FF"></AlternatingItemStyle>
<ItemStyle ForeColor="#003399" BackColor="White"></ItemStyle>
<HeaderStyle Font-Size="Medium" Font-Names="Arial Black" Font-Bold="True" HorizontalAlign="Center" ForeColor="#CCCCFF" VerticalAlign="Middle" BackColor="#003399"></HeaderStyle>
<FooterStyle ForeColor="#003399" BackColor="#99CCCC"></FooterStyle>
<Columns>
<asp:EditCommandColumn ButtonType="PushButton" UpdateText="Update" CancelText="Cancel" EditText="Edit Info"></asp:EditCommandColumn>
<asp:BoundColumn DataField="PName" SortExpression="PNAME asc" ReadOnly="True" HeaderText="Name" HeaderStyle-Font-Name="arial">
<HeaderStyle Font-Names="arial"></HeaderStyle>
</asp:BoundColumn>
<asp:TemplateColumn HeaderText="Status">
<ItemTemplate>
<%# DataBinder.Eval(Container.DataItem, "Status") %>
</ItemTemplate>
<EditItemTemplate>
<asp:DropDownList runat="server" id="lstStatus" DataTextField="Status" SelectedIndex ='<%# GetSelIndex(Container.DataItem("Status")) %>'>
<asp:ListItem Value="0">DOC</asp:ListItem>
<asp:ListItem Value="1">POE</asp:ListItem>
</asp:DropDownList>
</EditItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Group">
<ItemTemplate>
<%# DataBinder.Eval(Container.DataItem, "PGroup") %>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox id="txtGroup" Width="100%" runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "PGroup") %>' />
</EditItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Email">
<ItemTemplate>
<%# DataBinder.Eval(Container.DataItem, "Email") %>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox id="txtEmail" Width="100%" runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "Email") %>' />
</EditItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Logon">
<ItemTemplate>
<%# DataBinder.Eval(Container.DataItem, "LogonName") %>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox id="txtLogon" Width="100%" runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "LogonName") %>' />
</EditItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="PDA">
<ItemTemplate>
<%# DataBinder.Eval(Container.DataItem, "PDA") %>
</ItemTemplate>
<EditItemTemplate>
<asp:CheckBox id="ckPDA" runat="server" Checked ='<%# DataBinder.Eval(Container.DataItem, "PDA") %>'>
</asp:CheckBox>
</EditItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Training">
<EditItemTemplate>
<asp:DropDownList runat="server" id="dpTraining">
<asp:ListItem Value="0">Init Training</asp:ListItem>
<asp:ListItem Value="1">Retraining</asp:ListItem>
</asp:DropDownList>
</EditItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Date">
<EditItemTemplate>
<asp:textbox runat="server" id="txtDate"></asp:textbox>
<asp:CompareValidator id="vrDate" runat="server" ErrorMessage="*date*" ControlToValidate="txtDate" Type="Date" Operator="DataTypeCheck"></asp:CompareValidator>
</EditItemTemplate>
</asp:TemplateColumn>
<asp:BoundColumn DataField="PID" SortExpression="PID" ReadOnly="True" HeaderText="Physician ID">
<HeaderStyle Font-Names="arial"></HeaderStyle>
</asp:BoundColumn>
</Columns>
<PagerStyle HorizontalAlign="Left" ForeColor="#003399" Position="Top" BackColor="#99CCCC" Mode="NumericPages"></PagerStyle>
</asp:datagrid>
yan19454:
I used this line. it have error.
comUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.p_PDA", System.Data.SqlDbType.Bit, "PDA")).Value = CType(e.Item.FindControl("ckPDA"), CheckBox).Checked
Please please please always post the error encountered, not just the fact that you have an error.
Subscribe to:
Posts (Atom)