Hello everyone,
I am attempting to write a stored procedure to retrieve the record count but
have failed so far. Here's what I have tried...
SET @.value = 'SELECT COUNT(*) FROM ' + @.tablea
EXECUTE sp_executesql @.sql
...but this just ends up displaying the count to the screen, so I did...
SET @.value = 'DECLARE @.tablea_count NVARCHAR(128) SELECT
@.tablea_count=COUNT(*) FROM ' + @.tablea
EXECUTE sp_executesql @.sql, @.tablea_count OUTPUT
but this doesnt seem to work either.
Help?
--
ChrisHave a look here:
http://www.sommarskog.se/dynamic_sql.html
http://www.support.microsoft.com/?id=262499
Andrew J. Kelly SQL MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:292BA190-F8FD-4696-A6EA-8EE930095F5A@.microsoft.com...
> Hello everyone,
> I am attempting to write a stored procedure to retrieve the record count
> but
> have failed so far. Here's what I have tried...
> SET @.value = 'SELECT COUNT(*) FROM ' + @.tablea
> EXECUTE sp_executesql @.sql
> ...but this just ends up displaying the count to the screen, so I did...
> SET @.value = 'DECLARE @.tablea_count NVARCHAR(128) SELECT
> @.tablea_count=COUNT(*) FROM ' + @.tablea
> EXECUTE sp_executesql @.sql, @.tablea_count OUTPUT
> but this doesnt seem to work either.
> Help?
> --
> Chris
Showing posts with label attempting. Show all posts
Showing posts with label attempting. Show all posts
Tuesday, March 27, 2012
Dynamic query to store record count in variable??
Hello everyone,
I am attempting to write a stored procedure to retrieve the record count but
have failed so far. Here's what I have tried...
SET @.value = 'SELECT COUNT(*) FROM ' + @.tablea
EXECUTE sp_executesql @.sql
...but this just ends up displaying the count to the screen, so I did...
SET @.value = 'DECLARE @.tablea_count NVARCHAR(128) SELECT
@.tablea_count=COUNT(*) FROM ' + @.tablea
EXECUTE sp_executesql @.sql, @.tablea_count OUTPUT
but this doesnt seem to work either.
Help?
Chris
Have a look here:
http://www.sommarskog.se/dynamic_sql.html
http://www.support.microsoft.com/?id=262499
Andrew J. Kelly SQL MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:292BA190-F8FD-4696-A6EA-8EE930095F5A@.microsoft.com...
> Hello everyone,
> I am attempting to write a stored procedure to retrieve the record count
> but
> have failed so far. Here's what I have tried...
> SET @.value = 'SELECT COUNT(*) FROM ' + @.tablea
> EXECUTE sp_executesql @.sql
> ...but this just ends up displaying the count to the screen, so I did...
> SET @.value = 'DECLARE @.tablea_count NVARCHAR(128) SELECT
> @.tablea_count=COUNT(*) FROM ' + @.tablea
> EXECUTE sp_executesql @.sql, @.tablea_count OUTPUT
> but this doesnt seem to work either.
> Help?
> --
> Chris
sql
I am attempting to write a stored procedure to retrieve the record count but
have failed so far. Here's what I have tried...
SET @.value = 'SELECT COUNT(*) FROM ' + @.tablea
EXECUTE sp_executesql @.sql
...but this just ends up displaying the count to the screen, so I did...
SET @.value = 'DECLARE @.tablea_count NVARCHAR(128) SELECT
@.tablea_count=COUNT(*) FROM ' + @.tablea
EXECUTE sp_executesql @.sql, @.tablea_count OUTPUT
but this doesnt seem to work either.
Help?
Chris
Have a look here:
http://www.sommarskog.se/dynamic_sql.html
http://www.support.microsoft.com/?id=262499
Andrew J. Kelly SQL MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:292BA190-F8FD-4696-A6EA-8EE930095F5A@.microsoft.com...
> Hello everyone,
> I am attempting to write a stored procedure to retrieve the record count
> but
> have failed so far. Here's what I have tried...
> SET @.value = 'SELECT COUNT(*) FROM ' + @.tablea
> EXECUTE sp_executesql @.sql
> ...but this just ends up displaying the count to the screen, so I did...
> SET @.value = 'DECLARE @.tablea_count NVARCHAR(128) SELECT
> @.tablea_count=COUNT(*) FROM ' + @.tablea
> EXECUTE sp_executesql @.sql, @.tablea_count OUTPUT
> but this doesnt seem to work either.
> Help?
> --
> Chris
sql
Dynamic query to store record count in variable??
Hello everyone,
I am attempting to write a stored procedure to retrieve the record count but
have failed so far. Here's what I have tried...
SET @.value = 'SELECT COUNT(*) FROM ' + @.tablea
EXECUTE sp_executesql @.sql
...but this just ends up displaying the count to the screen, so I did...
SET @.value = 'DECLARE @.tablea_count NVARCHAR(128) SELECT
@.tablea_count=COUNT(*) FROM ' + @.tablea
EXECUTE sp_executesql @.sql, @.tablea_count OUTPUT
but this doesnt seem to work either.
Help?
--
ChrisHave a look here:
http://www.sommarskog.se/dynamic_sql.html
http://www.support.microsoft.com/?id=262499
--
Andrew J. Kelly SQL MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:292BA190-F8FD-4696-A6EA-8EE930095F5A@.microsoft.com...
> Hello everyone,
> I am attempting to write a stored procedure to retrieve the record count
> but
> have failed so far. Here's what I have tried...
> SET @.value = 'SELECT COUNT(*) FROM ' + @.tablea
> EXECUTE sp_executesql @.sql
> ...but this just ends up displaying the count to the screen, so I did...
> SET @.value = 'DECLARE @.tablea_count NVARCHAR(128) SELECT
> @.tablea_count=COUNT(*) FROM ' + @.tablea
> EXECUTE sp_executesql @.sql, @.tablea_count OUTPUT
> but this doesnt seem to work either.
> Help?
> --
> Chris
I am attempting to write a stored procedure to retrieve the record count but
have failed so far. Here's what I have tried...
SET @.value = 'SELECT COUNT(*) FROM ' + @.tablea
EXECUTE sp_executesql @.sql
...but this just ends up displaying the count to the screen, so I did...
SET @.value = 'DECLARE @.tablea_count NVARCHAR(128) SELECT
@.tablea_count=COUNT(*) FROM ' + @.tablea
EXECUTE sp_executesql @.sql, @.tablea_count OUTPUT
but this doesnt seem to work either.
Help?
--
ChrisHave a look here:
http://www.sommarskog.se/dynamic_sql.html
http://www.support.microsoft.com/?id=262499
--
Andrew J. Kelly SQL MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:292BA190-F8FD-4696-A6EA-8EE930095F5A@.microsoft.com...
> Hello everyone,
> I am attempting to write a stored procedure to retrieve the record count
> but
> have failed so far. Here's what I have tried...
> SET @.value = 'SELECT COUNT(*) FROM ' + @.tablea
> EXECUTE sp_executesql @.sql
> ...but this just ends up displaying the count to the screen, so I did...
> SET @.value = 'DECLARE @.tablea_count NVARCHAR(128) SELECT
> @.tablea_count=COUNT(*) FROM ' + @.tablea
> EXECUTE sp_executesql @.sql, @.tablea_count OUTPUT
> but this doesnt seem to work either.
> Help?
> --
> Chris
Friday, March 9, 2012
Dynamic Dynamic Filters
Hello,
I am attempting to use dynamic filters on merge replication to filter rows. In my front end app I am using the ActiveX Merge object to set the Host_Name property. My question is: Is it possible to set the host name to a more complex string?
example:
.HostName = "Col = 'Val1' OR (Col = 'Val2' AND Col = 'Val3')"
The problem I am running into is that the sql wizard for creating the dynamic filters runs a check on the sql statement and won't allow something like:
SELECT <published_columns> FROM [dbo].[Table1] WHERE Host_Name()
Technically if I could somehow bypass the wizard to enter the filter rules this should work. Anyone know if this is possible? Or possibly another way to work around this so that you can pass more than 1 value to the where clause? I suppose you could do so
mething like:
SELECT <published_columns> FROM [dbo].[Table1] WHERE Col = Host_Name()
HostName = "'Val1' OR (Col = 'Val2' AND Col = 'Val3')"
Would this be the only way? Any feedback would be greatly appreciated. Thanks in advance.
I'm unsure on what your filtering condition is. For instance your filter
should evaluate to a boolean true or false. Yours evaluates to hostname.
You can use a UDF to extend the functionalty of your filter or you can do
stuff like this
SELECT <published_columns> FROM [dbo].[authors] WHERE host_name() like 'p%'
or use a subquery
SELECT <published_columns> FROM [dbo].[authors] WHERE host_name() in (select
Servername from ServerList where state='ca')
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:D7626D05-A0A6-4F19-963D-8B588A20AC6E@.microsoft.com...
> Hello,
> I am attempting to use dynamic filters on merge replication to filter
rows. In my front end app I am using the ActiveX Merge object to set the
Host_Name property. My question is: Is it possible to set the host name to a
more complex string?
> example:
> .HostName = "Col = 'Val1' OR (Col = 'Val2' AND Col = 'Val3')"
> The problem I am running into is that the sql wizard for creating the
dynamic filters runs a check on the sql statement and won't allow something
like:
> SELECT <published_columns> FROM [dbo].[Table1] WHERE Host_Name()
> Technically if I could somehow bypass the wizard to enter the filter rules
this should work. Anyone know if this is possible? Or possibly another way
to work around this so that you can pass more than 1 value to the where
clause? I suppose you could do something like:
> SELECT <published_columns> FROM [dbo].[Table1] WHERE Col = Host_Name()
> HostName = "'Val1' OR (Col = 'Val2' AND Col = 'Val3')"
> Would this be the only way? Any feedback would be greatly appreciated.
Thanks in advance.
>
>
|||Yes mine purposely evaluates to hostname. What I am trying to do is set the filter to:
SELECT <published_columns> FROM [dbo].[Table1] WHERE Host_Name()
and then set hostname to something like:
Col = 'Val1' OR Col = 'Val2'
So that the resulting select will look like:
SELECT <published_columns> FROM [dbo].[Table1] WHERE Col = 'Val1' OR Col = 'Val2'
Or whatever other complex WHERE clause that I choose. The problem is that it won't except the above filter. My work around listed above should work (I have yet to test it). Although I am interested in other possibilites to achieve the same results. This s
eems fairly basic unless I am missing something?
"Hilary Cotter" wrote:
> I'm unsure on what your filtering condition is. For instance your filter
> should evaluate to a boolean true or false. Yours evaluates to hostname.
> You can use a UDF to extend the functionalty of your filter or you can do
> stuff like this
> SELECT <published_columns> FROM [dbo].[authors] WHERE host_name() like 'p%'
> or use a subquery
> SELECT <published_columns> FROM [dbo].[authors] WHERE host_name() in (select
> Servername from ServerList where state='ca')
>
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Steve" <Steve@.discussions.microsoft.com> wrote in message
> news:D7626D05-A0A6-4F19-963D-8B588A20AC6E@.microsoft.com...
> rows. In my front end app I am using the ActiveX Merge object to set the
> Host_Name property. My question is: Is it possible to set the host name to a
> more complex string?
> dynamic filters runs a check on the sql statement and won't allow something
> like:
> this should work. Anyone know if this is possible? Or possibly another way
> to work around this so that you can pass more than 1 value to the where
> clause? I suppose you could do something like:
> Thanks in advance.
>
>
|||Steve,
you could have a mapping table which relates values (Val1 and Val2) to
hostnames. In your example there would be 2 rows:
-- MAPPINGTABLE --
hostname1 val1
hostname1 val2
If you include this table in the filter, you can achieve the clause you want
to create.
HTH,
Paul Ibison
|||Paul,
I am not familiar with mapping tables and I have been unable to find any information relating to them. Could you provide an example of what you mean or possibly a link to some information? Thanks.
|||Steve,
it's easiest if I explain how I set up a test: I had 2 tables - region and
HostnameLookup - shown below. The HostnameLookup table defines the multiple
values I'm interested in. I use dynamic filters to filter the HostnameLookup
table using HOST_NAME() and a join filter to join to the region table.
Editing the 2nd step on the merge agent's job to include: -Hostname
PaulsComputer means that only 2 regions get replicated. I've listed the
exact text below in case you want to recreate it to test.
HTH,
Paul Ibison
Region
RegionID RegionDescription rowguid
-- -- --
1 Eastern 9A6377F0-70DF-4A5C-962D-B41A41EFA82B
2 Western C0CAEFAD-7B8C-43D6-87ED-920494A5C60C
3 Northern 6CD9AA33-BE2D-4DCF-B447-745A3B86818E
4 Southern B4826E41-96D6-457C-8645-DD4984AE3BF5
HostnameLookup
RegionDescription Hostname rowguid
-- -- --
Northern PaulsComputer 2367C78A-1001-417B-A3B1-1C74B23F8131
Southern PaulsComputer 4F563C4D-8479-4A7D-A938-490DD514F12A
Filter Clause for HostnameLookup:
SELECT <published_columns> FROM [dbo].[HostnameLookup]
WHERE HostnameLookup.Hostname = HOST_NAME()
Filter Clause for Region:
< All rows published >
Join Filter:
Filtered table is HostnameLookup
Table to FIlter is Region
SELECT <published_columns> FROM [dbo].[HostnameLookup]
INNER JOIN [dbo].[Region] ON Hostnamelookup.regiondescription =
region.regiondescription
Edit the 2nd step on the merge agent's job to include: -Hostname
PaulsComputer
Run the snapshot then merge agents and only 2 regions should be replicated.
|||Hi Paul,
I saw your suggested solution in another web page
http://www.replicationanswers.com/Merge.asp
I was wondering if this Host_Name() is a function that will return the
computer name of the device/desktop that the subscriber database is residing
on?
If I am using mobile devices as subscribers and they have a web service
method called Reader_Id() which returns the unique id of the mobile device,
can I modify your solution accordingly?
Thank you.
"Paul Ibison" wrote:
> Steve,
> it's easiest if I explain how I set up a test: I had 2 tables - region and
> HostnameLookup - shown below. The HostnameLookup table defines the multiple
> values I'm interested in. I use dynamic filters to filter the HostnameLookup
> table using HOST_NAME() and a join filter to join to the region table.
> Editing the 2nd step on the merge agent's job to include: -Hostname
> PaulsComputer means that only 2 regions get replicated. I've listed the
> exact text below in case you want to recreate it to test.
> HTH,
> Paul Ibison
> Region
> --
> RegionID RegionDescription rowguid
> -- -- --
> 1 Eastern 9A6377F0-70DF-4A5C-962D-B41A41EFA82B
> 2 Western C0CAEFAD-7B8C-43D6-87ED-920494A5C60C
> 3 Northern 6CD9AA33-BE2D-4DCF-B447-745A3B86818E
> 4 Southern B4826E41-96D6-457C-8645-DD4984AE3BF5
> HostnameLookup
> --
> RegionDescription Hostname rowguid
> -- -- --
> Northern PaulsComputer 2367C78A-1001-417B-A3B1-1C74B23F8131
> Southern PaulsComputer 4F563C4D-8479-4A7D-A938-490DD514F12A
>
> Filter Clause for HostnameLookup:
> SELECT <published_columns> FROM [dbo].[HostnameLookup]
> WHERE HostnameLookup.Hostname = HOST_NAME()
> Filter Clause for Region:
> < All rows published >
> Join Filter:
> Filtered table is HostnameLookup
> Table to FIlter is Region
> SELECT <published_columns> FROM [dbo].[HostnameLookup]
> INNER JOIN [dbo].[Region] ON Hostnamelookup.regiondescription =
> region.regiondescription
> Edit the 2nd step on the merge agent's job to include: -Hostname
> PaulsComputer
> Run the snapshot then merge agents and only 2 regions should be replicated.
>
>
I am attempting to use dynamic filters on merge replication to filter rows. In my front end app I am using the ActiveX Merge object to set the Host_Name property. My question is: Is it possible to set the host name to a more complex string?
example:
.HostName = "Col = 'Val1' OR (Col = 'Val2' AND Col = 'Val3')"
The problem I am running into is that the sql wizard for creating the dynamic filters runs a check on the sql statement and won't allow something like:
SELECT <published_columns> FROM [dbo].[Table1] WHERE Host_Name()
Technically if I could somehow bypass the wizard to enter the filter rules this should work. Anyone know if this is possible? Or possibly another way to work around this so that you can pass more than 1 value to the where clause? I suppose you could do so
mething like:
SELECT <published_columns> FROM [dbo].[Table1] WHERE Col = Host_Name()
HostName = "'Val1' OR (Col = 'Val2' AND Col = 'Val3')"
Would this be the only way? Any feedback would be greatly appreciated. Thanks in advance.
I'm unsure on what your filtering condition is. For instance your filter
should evaluate to a boolean true or false. Yours evaluates to hostname.
You can use a UDF to extend the functionalty of your filter or you can do
stuff like this
SELECT <published_columns> FROM [dbo].[authors] WHERE host_name() like 'p%'
or use a subquery
SELECT <published_columns> FROM [dbo].[authors] WHERE host_name() in (select
Servername from ServerList where state='ca')
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:D7626D05-A0A6-4F19-963D-8B588A20AC6E@.microsoft.com...
> Hello,
> I am attempting to use dynamic filters on merge replication to filter
rows. In my front end app I am using the ActiveX Merge object to set the
Host_Name property. My question is: Is it possible to set the host name to a
more complex string?
> example:
> .HostName = "Col = 'Val1' OR (Col = 'Val2' AND Col = 'Val3')"
> The problem I am running into is that the sql wizard for creating the
dynamic filters runs a check on the sql statement and won't allow something
like:
> SELECT <published_columns> FROM [dbo].[Table1] WHERE Host_Name()
> Technically if I could somehow bypass the wizard to enter the filter rules
this should work. Anyone know if this is possible? Or possibly another way
to work around this so that you can pass more than 1 value to the where
clause? I suppose you could do something like:
> SELECT <published_columns> FROM [dbo].[Table1] WHERE Col = Host_Name()
> HostName = "'Val1' OR (Col = 'Val2' AND Col = 'Val3')"
> Would this be the only way? Any feedback would be greatly appreciated.
Thanks in advance.
>
>
|||Yes mine purposely evaluates to hostname. What I am trying to do is set the filter to:
SELECT <published_columns> FROM [dbo].[Table1] WHERE Host_Name()
and then set hostname to something like:
Col = 'Val1' OR Col = 'Val2'
So that the resulting select will look like:
SELECT <published_columns> FROM [dbo].[Table1] WHERE Col = 'Val1' OR Col = 'Val2'
Or whatever other complex WHERE clause that I choose. The problem is that it won't except the above filter. My work around listed above should work (I have yet to test it). Although I am interested in other possibilites to achieve the same results. This s
eems fairly basic unless I am missing something?
"Hilary Cotter" wrote:
> I'm unsure on what your filtering condition is. For instance your filter
> should evaluate to a boolean true or false. Yours evaluates to hostname.
> You can use a UDF to extend the functionalty of your filter or you can do
> stuff like this
> SELECT <published_columns> FROM [dbo].[authors] WHERE host_name() like 'p%'
> or use a subquery
> SELECT <published_columns> FROM [dbo].[authors] WHERE host_name() in (select
> Servername from ServerList where state='ca')
>
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Steve" <Steve@.discussions.microsoft.com> wrote in message
> news:D7626D05-A0A6-4F19-963D-8B588A20AC6E@.microsoft.com...
> rows. In my front end app I am using the ActiveX Merge object to set the
> Host_Name property. My question is: Is it possible to set the host name to a
> more complex string?
> dynamic filters runs a check on the sql statement and won't allow something
> like:
> this should work. Anyone know if this is possible? Or possibly another way
> to work around this so that you can pass more than 1 value to the where
> clause? I suppose you could do something like:
> Thanks in advance.
>
>
|||Steve,
you could have a mapping table which relates values (Val1 and Val2) to
hostnames. In your example there would be 2 rows:
-- MAPPINGTABLE --
hostname1 val1
hostname1 val2
If you include this table in the filter, you can achieve the clause you want
to create.
HTH,
Paul Ibison
|||Paul,
I am not familiar with mapping tables and I have been unable to find any information relating to them. Could you provide an example of what you mean or possibly a link to some information? Thanks.
|||Steve,
it's easiest if I explain how I set up a test: I had 2 tables - region and
HostnameLookup - shown below. The HostnameLookup table defines the multiple
values I'm interested in. I use dynamic filters to filter the HostnameLookup
table using HOST_NAME() and a join filter to join to the region table.
Editing the 2nd step on the merge agent's job to include: -Hostname
PaulsComputer means that only 2 regions get replicated. I've listed the
exact text below in case you want to recreate it to test.
HTH,
Paul Ibison
Region
RegionID RegionDescription rowguid
-- -- --
1 Eastern 9A6377F0-70DF-4A5C-962D-B41A41EFA82B
2 Western C0CAEFAD-7B8C-43D6-87ED-920494A5C60C
3 Northern 6CD9AA33-BE2D-4DCF-B447-745A3B86818E
4 Southern B4826E41-96D6-457C-8645-DD4984AE3BF5
HostnameLookup
RegionDescription Hostname rowguid
-- -- --
Northern PaulsComputer 2367C78A-1001-417B-A3B1-1C74B23F8131
Southern PaulsComputer 4F563C4D-8479-4A7D-A938-490DD514F12A
Filter Clause for HostnameLookup:
SELECT <published_columns> FROM [dbo].[HostnameLookup]
WHERE HostnameLookup.Hostname = HOST_NAME()
Filter Clause for Region:
< All rows published >
Join Filter:
Filtered table is HostnameLookup
Table to FIlter is Region
SELECT <published_columns> FROM [dbo].[HostnameLookup]
INNER JOIN [dbo].[Region] ON Hostnamelookup.regiondescription =
region.regiondescription
Edit the 2nd step on the merge agent's job to include: -Hostname
PaulsComputer
Run the snapshot then merge agents and only 2 regions should be replicated.
|||Hi Paul,
I saw your suggested solution in another web page
http://www.replicationanswers.com/Merge.asp
I was wondering if this Host_Name() is a function that will return the
computer name of the device/desktop that the subscriber database is residing
on?
If I am using mobile devices as subscribers and they have a web service
method called Reader_Id() which returns the unique id of the mobile device,
can I modify your solution accordingly?
Thank you.
"Paul Ibison" wrote:
> Steve,
> it's easiest if I explain how I set up a test: I had 2 tables - region and
> HostnameLookup - shown below. The HostnameLookup table defines the multiple
> values I'm interested in. I use dynamic filters to filter the HostnameLookup
> table using HOST_NAME() and a join filter to join to the region table.
> Editing the 2nd step on the merge agent's job to include: -Hostname
> PaulsComputer means that only 2 regions get replicated. I've listed the
> exact text below in case you want to recreate it to test.
> HTH,
> Paul Ibison
> Region
> --
> RegionID RegionDescription rowguid
> -- -- --
> 1 Eastern 9A6377F0-70DF-4A5C-962D-B41A41EFA82B
> 2 Western C0CAEFAD-7B8C-43D6-87ED-920494A5C60C
> 3 Northern 6CD9AA33-BE2D-4DCF-B447-745A3B86818E
> 4 Southern B4826E41-96D6-457C-8645-DD4984AE3BF5
> HostnameLookup
> --
> RegionDescription Hostname rowguid
> -- -- --
> Northern PaulsComputer 2367C78A-1001-417B-A3B1-1C74B23F8131
> Southern PaulsComputer 4F563C4D-8479-4A7D-A938-490DD514F12A
>
> Filter Clause for HostnameLookup:
> SELECT <published_columns> FROM [dbo].[HostnameLookup]
> WHERE HostnameLookup.Hostname = HOST_NAME()
> Filter Clause for Region:
> < All rows published >
> Join Filter:
> Filtered table is HostnameLookup
> Table to FIlter is Region
> SELECT <published_columns> FROM [dbo].[HostnameLookup]
> INNER JOIN [dbo].[Region] ON Hostnamelookup.regiondescription =
> region.regiondescription
> Edit the 2nd step on the merge agent's job to include: -Hostname
> PaulsComputer
> Run the snapshot then merge agents and only 2 regions should be replicated.
>
>
Sunday, February 19, 2012
Dynamic Columns
I am attempting to add columns dynamically and i have that working. The
problem is I cannot collapse the columns if the columns in between are not
selected. For example if they want to see column 1 and column 6 only, there
is blank space between the 2 columns. How do I collapse columns 2-5 and slide
column 6 so it is next to column 1?
Col1 Col6
needs to be:
Col1 Col6Never mind you bunch of losers i got it... (jk about the loser part but i
did get it to work)
"RayMerckel" wrote:
> I am attempting to add columns dynamically and i have that working. The
> problem is I cannot collapse the columns if the columns in between are not
> selected. For example if they want to see column 1 and column 6 only, there
> is blank space between the 2 columns. How do I collapse columns 2-5 and slide
> column 6 so it is next to column 1?
> Col1 Col6
> needs to be:
> Col1 Col6|||How did you do it, Ray?
Ed Allison
"RayMerckel" <RayMerckel@.discussions.microsoft.com> wrote in message
news:19B4F305-2F31-4EA7-AA36-B941D154F485@.microsoft.com...
> Never mind you bunch of losers i got it... (jk about the loser part but i
> did get it to work)
> "RayMerckel" wrote:
>> I am attempting to add columns dynamically and i have that working. The
>> problem is I cannot collapse the columns if the columns in between are
>> not
>> selected. For example if they want to see column 1 and column 6 only,
>> there
>> is blank space between the 2 columns. How do I collapse columns 2-5 and
>> slide
>> column 6 so it is next to column 1?
>> Col1 Col6
>> needs to be:
>> Col1 Col6|||Ed,
well it took me awhile to figure out so some $ for my kids college fund
would be apprec... (JK)...
make every column name a boolean parameter in RS... then in code behind for
the column properties - visibility - [expression]
=IIF((Parameters![Column1])=0,True,False)
=IIF((Parameters![Column2])=0,True,False)... and so on
do that for every column... then the user can pick and choose which column
he/she wants to see... cool...
peace out...
"Ed Allison" wrote:
> How did you do it, Ray?
> Ed Allison
> "RayMerckel" <RayMerckel@.discussions.microsoft.com> wrote in message
> news:19B4F305-2F31-4EA7-AA36-B941D154F485@.microsoft.com...
> > Never mind you bunch of losers i got it... (jk about the loser part but i
> > did get it to work)
> >
> > "RayMerckel" wrote:
> >
> >> I am attempting to add columns dynamically and i have that working. The
> >> problem is I cannot collapse the columns if the columns in between are
> >> not
> >> selected. For example if they want to see column 1 and column 6 only,
> >> there
> >> is blank space between the 2 columns. How do I collapse columns 2-5 and
> >> slide
> >> column 6 so it is next to column 1?
> >> Col1 Col6
> >>
> >> needs to be:
> >> Col1 Col6
>
>|||Thanks for sharing, Ray.
"RayMerckel" <RayMerckel@.discussions.microsoft.com> wrote in message
news:817D0F68-BE49-42C3-AFBE-2FE9D99F3007@.microsoft.com...
> Ed,
> well it took me awhile to figure out so some $ for my kids college fund
> would be apprec... (JK)...
> make every column name a boolean parameter in RS... then in code behind
> for
> the column properties - visibility - [expression]
> =IIF((Parameters![Column1])=0,True,False)
> =IIF((Parameters![Column2])=0,True,False)... and so on
> do that for every column... then the user can pick and choose which column
> he/she wants to see... cool...
> peace out...
> "Ed Allison" wrote:
>> How did you do it, Ray?
>> Ed Allison
>> "RayMerckel" <RayMerckel@.discussions.microsoft.com> wrote in message
>> news:19B4F305-2F31-4EA7-AA36-B941D154F485@.microsoft.com...
>> > Never mind you bunch of losers i got it... (jk about the loser part
>> > but i
>> > did get it to work)
>> >
>> > "RayMerckel" wrote:
>> >
>> >> I am attempting to add columns dynamically and i have that working.
>> >> The
>> >> problem is I cannot collapse the columns if the columns in between are
>> >> not
>> >> selected. For example if they want to see column 1 and column 6 only,
>> >> there
>> >> is blank space between the 2 columns. How do I collapse columns 2-5
>> >> and
>> >> slide
>> >> column 6 so it is next to column 1?
>> >> Col1 Col6
>> >>
>> >> needs to be:
>> >> Col1 Col6
>>
problem is I cannot collapse the columns if the columns in between are not
selected. For example if they want to see column 1 and column 6 only, there
is blank space between the 2 columns. How do I collapse columns 2-5 and slide
column 6 so it is next to column 1?
Col1 Col6
needs to be:
Col1 Col6Never mind you bunch of losers i got it... (jk about the loser part but i
did get it to work)
"RayMerckel" wrote:
> I am attempting to add columns dynamically and i have that working. The
> problem is I cannot collapse the columns if the columns in between are not
> selected. For example if they want to see column 1 and column 6 only, there
> is blank space between the 2 columns. How do I collapse columns 2-5 and slide
> column 6 so it is next to column 1?
> Col1 Col6
> needs to be:
> Col1 Col6|||How did you do it, Ray?
Ed Allison
"RayMerckel" <RayMerckel@.discussions.microsoft.com> wrote in message
news:19B4F305-2F31-4EA7-AA36-B941D154F485@.microsoft.com...
> Never mind you bunch of losers i got it... (jk about the loser part but i
> did get it to work)
> "RayMerckel" wrote:
>> I am attempting to add columns dynamically and i have that working. The
>> problem is I cannot collapse the columns if the columns in between are
>> not
>> selected. For example if they want to see column 1 and column 6 only,
>> there
>> is blank space between the 2 columns. How do I collapse columns 2-5 and
>> slide
>> column 6 so it is next to column 1?
>> Col1 Col6
>> needs to be:
>> Col1 Col6|||Ed,
well it took me awhile to figure out so some $ for my kids college fund
would be apprec... (JK)...
make every column name a boolean parameter in RS... then in code behind for
the column properties - visibility - [expression]
=IIF((Parameters![Column1])=0,True,False)
=IIF((Parameters![Column2])=0,True,False)... and so on
do that for every column... then the user can pick and choose which column
he/she wants to see... cool...
peace out...
"Ed Allison" wrote:
> How did you do it, Ray?
> Ed Allison
> "RayMerckel" <RayMerckel@.discussions.microsoft.com> wrote in message
> news:19B4F305-2F31-4EA7-AA36-B941D154F485@.microsoft.com...
> > Never mind you bunch of losers i got it... (jk about the loser part but i
> > did get it to work)
> >
> > "RayMerckel" wrote:
> >
> >> I am attempting to add columns dynamically and i have that working. The
> >> problem is I cannot collapse the columns if the columns in between are
> >> not
> >> selected. For example if they want to see column 1 and column 6 only,
> >> there
> >> is blank space between the 2 columns. How do I collapse columns 2-5 and
> >> slide
> >> column 6 so it is next to column 1?
> >> Col1 Col6
> >>
> >> needs to be:
> >> Col1 Col6
>
>|||Thanks for sharing, Ray.
"RayMerckel" <RayMerckel@.discussions.microsoft.com> wrote in message
news:817D0F68-BE49-42C3-AFBE-2FE9D99F3007@.microsoft.com...
> Ed,
> well it took me awhile to figure out so some $ for my kids college fund
> would be apprec... (JK)...
> make every column name a boolean parameter in RS... then in code behind
> for
> the column properties - visibility - [expression]
> =IIF((Parameters![Column1])=0,True,False)
> =IIF((Parameters![Column2])=0,True,False)... and so on
> do that for every column... then the user can pick and choose which column
> he/she wants to see... cool...
> peace out...
> "Ed Allison" wrote:
>> How did you do it, Ray?
>> Ed Allison
>> "RayMerckel" <RayMerckel@.discussions.microsoft.com> wrote in message
>> news:19B4F305-2F31-4EA7-AA36-B941D154F485@.microsoft.com...
>> > Never mind you bunch of losers i got it... (jk about the loser part
>> > but i
>> > did get it to work)
>> >
>> > "RayMerckel" wrote:
>> >
>> >> I am attempting to add columns dynamically and i have that working.
>> >> The
>> >> problem is I cannot collapse the columns if the columns in between are
>> >> not
>> >> selected. For example if they want to see column 1 and column 6 only,
>> >> there
>> >> is blank space between the 2 columns. How do I collapse columns 2-5
>> >> and
>> >> slide
>> >> column 6 so it is next to column 1?
>> >> Col1 Col6
>> >>
>> >> needs to be:
>> >> Col1 Col6
>>
Wednesday, February 15, 2012
Dynamic (on the fly) query
Hi all. I'm currently encountering a problem in attempting to find a solution for a dynamic or on the fly query. I understand how you can make a static query and return it as a dataset; for example, say you have a field where a user enters a name on the front end and the db returns the results:
Dim queryString As String = "SELECT [Table].* FROM [Table] WHERE ("& _
"[Table].[Name] = @.Name)"
But what if I have multiple items I would like query based upon what the user picks. For example, say you have five fields: Name, ID Number, Date, Address, and State. Say the user wants to pick all data with a date between Jan. 06 to April 06, with the name of Tom, and in the state of CA. But then next time, the user only wants all data with the name of Susan. So the query is always changing and I am not sure exactly how to go about it. I guess I sorta want similiar functionality as that of the Custom Auto Filter in Excel. I've been reading a couple of the forums and I think people are using a string to pass to query the database. But I am still vague on how to approach this. Any help would be greatly appreciated!
string sql ="select pt.ProjectTaskID, pt.EmployeeID, pt.ProjectID, pt.TaskID, pt.TaskDate, pt.TaskLength " +"from ProjectTask pt, project p where pt.EmployeeID = @.Employee and (pt.TaskDate >= @.startDate and pt.TaskDate <= @.endDate) " +"and pt.projectid = p.projectid"; cmd.Parameters.AddWithValue("@.Employee", nGlobals.TheUser.EmployeeID); cmd.Parameters.AddWithValue("@.startDate", nStartDate); cmd.Parameters.AddWithValue("@.endDate", nEndDate);if (nPrimary > 0) { sql = sql +" and p.biostatPI = @.pi"; cmd.Parameters.AddWithValue("@.pi", nPrimary); }if (nClinicalPrimary > 0) { sql = sql +" and p.clinicalPI = @.clinical"; cmd.Parameters.AddWithValue("@.clinical", nClinicalPrimary); }if (nDept > 0) { sql = sql +" and p.departmentId = @.dept"; cmd.Parameters.AddWithValue("@.dept", nDept); }if ((nGrant !=null) && (nGrant.Length > 0)) { sql = sql +" and p.grantNumber = @.grant"; cmd.Parameters.AddWithValue("@.grant", nGrant); }if ((nProtocol !=null) && (nProtocol.Length > 0)) { sql = sql +" and p.protocolNumber = @.protocol"; cmd.Parameters.AddWithValue("@.protocol", nProtocol); }|||SELECT Name, [ID Number], [Date], Address, State FROM [Table] WHERE Name=ISNULL(@.Name, Name) AND State=ISNULL(@.State, State) AND [Date] BETWEEN @.Date1AND @.Date2
In your parameters, you need handle the default values for these parameters. Hope this gives you one direction to work with. You can also use COALESCE function instead of the ISNULL function in the where clause .
|||
In the above code, there are 5 optional selection parameters. If a value isn't blank, it's added to the where clause, and the appropriate parameter is added as well. There's a bit of overhead, because you're executing different SQL with almost every query.
A more efficient, but more complex method would be to use a stored procedure, with the base select selecting into a temp table, and for each not null criteria, deleting rows from the temp table that don't match the criteria.
|||Ok, try this. Use a stored proc, like this.
-- first, give it every input parameter you will ever want to use to select from this table.
create procedure sometableselect (
@.sometableid int=null,
@.field1 int = null,
@.field2 varchar(50)=null,
@.fromdate datetime=null,
@.todate datetime=null,
@.sort varchar(500)=null
)
as
set nocount on
set ansi_warnings off
declare @.sql varchar(500)
-- This table below should be everything you ever wanted to know about the records
-- in this table. Get all related information and put it in here.
create table #temp1 (
Sometableid int,
field1 int,
field2 varchar(50),
recorddate datetime,
foreignkeyID int,
foreignkeydescription varchar(50),
reccount int
)
-- then insert the records you want to select
insert into #temp1 (
field1,
field2,
recorddate,
foreignkeyid
)
select
field1,
field2,
recorddate,
foreignkeyid)
from sometable
where (@.sometableid is null or @.sometableid=sometableid)
and (@.field1 is null or @.field1=field1)
and (@.field2 is null or patindex('%'+@.field2+'%', field2)>0)
and (@.fromdate is null or @.fromdate <=recorddate)
and (@.todate is null or @.todate >=recorddate)
update #temp1 set foreignkeydescription=fk.description
from foreignkeytable fk where fk.foreignkeyid = #temp1.foreignkeyid
update #temp1 set reccount = (select count(*) from #temp1)
select @.sql ='select * from #temp1 '
if isnull(@.sort,'') <>'' select @.sql = @.sql + ' order by ' + @.sort
exec(@.sql)
drop table #temp1
set nocount off
set ansi_warnings on
You call it from code like this:
query = "TableSelect "
query = query & "@.field1=" & cstr(value1)
query=query & ", @.field2='" & value2 & "'"
There are also other ways to call it, using parameters. I find building a query string to be more convenient and, in case of doubt, I can just copy and paste the query string into Query Analyzer and run it manually to see what it does.
The effect is that it will select by whatever parameters you use -- in combination -- and it will ignore those you don't use. If you don't give it a parameter, it will assume a default value of null, which means the selection criteria will drop out of the selection. If you call it with no parameters, it returns every record in the table, which makes it easy to run simple tests.
The reason you return EVERYTHING related to the record in the stored proc is to make it easier for the front-end programmer. If you write them like this, then one stored proc can be used to select records from the table almost anywhere in a typical application. One stored proc does it all.
The @.sort parameter allows you to return the recordset in whatever order you want by passing it from the front end. The @.sort may be any valid ORDER BY clause for the #temp1 table.
I also return the reccount as the number of records in the recordset. This isn't strictly necessary, but it comes in handy sometimes when you need the number of records on the front end.
If you start out an app by routinely building one of these procs for each table when you develop an app, you will find that it makes building the front end a breeze.
@.
|||Thanks all
Subscribe to:
Posts (Atom)