Change in Job Execution - SQL Server 2005?

I just converted a SQL Server 2000 database to 2005. I have a job whose only
step does the following:
Declare @.Command char(240)
Set @.Command = '\SDBS\Scheduler\Scheduler.Exe ' +
Convert(char(50),Convert(uniqueidentifier,[JOBID])) + ' ' + (Select
Replace([name],' ','~')
From msdb..sysjobs where job_id=[JOBID])
exec master..xp_cmdshell @.Command, NO_OUTPUT
Notice the [JOBID] portion. Under SQL Server 2000, the job id of the job was
passed to the job step. Under 2005, I get an error when I run the job saying
'JOBID' is undefined. Does anyone know what change I need to make?
Amos.Please don't post independently in separate newsgroups. You can add
multiple newsgroups to the header and then all the answers appear as one.
See my reply in the other newsgroup.
Andrew J. Kelly SQL MVP
"Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
news:eIE1j7s5FHA.2576@.TK2MSFTNGP09.phx.gbl...
>I just converted a SQL Server 2000 database to 2005. I have a job whose
>only step does the following:
> Declare @.Command char(240)
> Set @.Command = '\SDBS\Scheduler\Scheduler.Exe ' +
> Convert(char(50),Convert(uniqueidentifier,[JOBID])) + ' ' + (Select
> Replace([name],' ','~')
> From msdb..sysjobs where job_id=[JOBID])
> exec master..xp_cmdshell @.Command, NO_OUTPUT
> Notice the [JOBID] portion. Under SQL Server 2000, the job id of the job
> was passed to the job step. Under 2005, I get an error when I run the job
> saying 'JOBID' is undefined. Does anyone know what change I need to make?
> Amos.
>|||Thank you.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e8AeOpt5FHA.3296@.TK2MSFTNGP09.phx.gbl...
> Please don't post independently in separate newsgroups. You can add
> multiple newsgroups to the header and then all the answers appear as one.
> See my reply in the other newsgroup.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
> news:eIE1j7s5FHA.2576@.TK2MSFTNGP09.phx.gbl...
>>I just converted a SQL Server 2000 database to 2005. I have a job whose
>>only step does the following:
>> Declare @.Command char(240)
>> Set @.Command = '\SDBS\Scheduler\Scheduler.Exe ' +
>> Convert(char(50),Convert(uniqueidentifier,[JOBID])) + ' ' + (Select
>> Replace([name],' ','~')
>> From msdb..sysjobs where job_id=[JOBID])
>> exec master..xp_cmdshell @.Command, NO_OUTPUT
>> Notice the [JOBID] portion. Under SQL Server 2000, the job id of the job
>> was passed to the job step. Under 2005, I get an error when I run the job
>> saying 'JOBID' is undefined. Does anyone know what change I need to make?
>> Amos.
>sql

change distributor ?

Hi There

Ok i have the following setup.

Server 1 - OLTP publisher
Server 2 - Remote Distributor
Server 3 - Warehouse subscriber

Server 4 - New Server (new computer name etc, brand new).

I need Server 4 to be the new remote distributor for Server 1's publication.

I could disable replication and reconfigure from scratch with new server 4 as distributor.
Problem OLTP tons of data , downtime required for initial snapshot to long.

How can i do this without having to completely reconfigure replication , that is minimal downtime.

ThanxUnfortunately, there is not a way to just switch out the Distributor without reconfiguring. The good news, however, is that you can probably do this without too much fuss.

Since the Publisher and Subscriber will remain the same, you should not need to reinitialize the Subscriber; it already has all of the published data. The following should get you where you want to go:

1) Configure Server 4 as a Distributor and enable Server 1 as a Publisher.

2) Generate a script to create your publication(s) on Server 1.

3) Quiesce the old system by making sure that all replicated data has been distributed to the Subscriber.

4) Drop the subscription(s) on Server 3.

5) Disable replication on Server 1.

6) Reconfigure replication on Server 1 with Server 4 as the Distributor.

7) Run the scripts to recreate the publication(s).

8) Add the subscription(s) on Server 3, but use the no_sync option (which means that the Subscriber already has the data).

9) Start allowing changes to the replicated data.

Phillip Garding

|||Hi Phillip

Thank you very much for your input, sorry it has taken so long to reply.

The no_sync option is the trick, i will try test this scenario.

Thanx again

change date format from MMDDYYYY to DDMMYYY ?

Hi,
My report dates are coming out in the following format MM/DD/YYYY. How can I
change this to DD/MM/YYYY.
i.e is this to do with IIS, reporting services or the report itself ?
(machine is DD/MM/YYY)
Thanks
Scottscott wrote:
> Hi,
> My report dates are coming out in the following format MM/DD/YYYY.
How can I
> change this to DD/MM/YYYY.
> i.e is this to do with IIS, reporting services or the report itself ?
> (machine is DD/MM/YYY)
> Thanks
> Scott
In tools/options set International Settings to be same as Microsoft
Office.
Find Report object on the Properties window dropdown and set the
Language property to English (UK)
This should now allow you to set the format of the textbox to
dd/mm/yyyy or similar. (Go to property pages for some default options -
right click text box and go to properties.)|||thanks
scott

Change data structure of Replicated data

Hello,
I have a database in SQL Server 2000, on which created publication and it
has one subscriber .
now if I change in data structure , following error occured.
'EMPMAST' table
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot add
columns to table 'EMPMAST' because it is being published for merge
replication.
Harsha........................
Harsha,
please take a look at sp_repladdcolumn.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||and sp_addmergearticle for merge replication
"Harsha Shah" <har_sha_99@.hotmail.com> wrote in message
news:uvdlOqldFHA.688@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I have a database in SQL Server 2000, on which created publication and it
> has one subscriber .
> now if I change in data structure , following error occured.
> 'EMPMAST' table
> - Unable to modify table.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot add
> columns to table 'EMPMAST' because it is being published for merge
> replication.
> Harsha........................
>
>
|||"uykusuz" <yb> wrote in message news:eCssQMmdFHA.720@.TK2MSFTNGP15.phx.gbl...
> and sp_addmergearticle for merge replication
that is for adding articles not columns , sorry
[vbcol=seagreen]
> "Harsha Shah" <har_sha_99@.hotmail.com> wrote in message
> news:uvdlOqldFHA.688@.TK2MSFTNGP14.phx.gbl...
it
>

Challenging sql code

I have two tabels : TABLE1 and TABLE2
TABLE 1 has the following Columns
OID
DATE1
ComonField1
Field1

TABLE 2 has the following Columns
OID
DATE2
ComonField1
Field2

What I am willing to achieve is the following:
Is to find amongst the rows in TABLE2 (they are many having the same comonField value), which one has the closest date (DATE2) to a row in TABLE1 (DATE1) DATE@.2 must be lower than DATE1 but the closest. I am going to be creating 2 columns in TABLE1 where I will put the values of the row that respond to the previous condition (DATE2 and Field2) in TABLE1.

I need to go throug all the rows in TABLE1 and do the same thing i.e. find the appropriate row in TABLE2 and take 2 columns values and put them in front of the row in TABLE1.

Any help please.Originally posted by Nour
I have two tabels : TABLE1 and TABLE2
TABLE 1 has the following Columns
OID
DATE1
ComonField1
Field1

TABLE 2 has the following Columns
OID
DATE2
ComonField1
Field2

What I am willing to achieve is the following:
Is to find amongst the rows in TABLE2 (they are many having the same comonField value), which one has the closest date (DATE2) to a row in TABLE1 (DATE1) DATE@.2 must be lower than DATE1 but the closest. I am going to be creating 2 columns in TABLE1 where I will put the values of the row that respond to the previous condition (DATE2 and Field2) in TABLE1.

I need to go throug all the rows in TABLE1 and do the same thing i.e. find the appropriate row in TABLE2 and take 2 columns values and put them in front of the row in TABLE1.


First of all, there is no way to do that with a single SQL query. And you probably don't want to join them because "there are a lot of field values in common."

What you probably should do is to open a query on each table with the ORDER BY clause so the rows will be sorted the same: by CommonField and by Date. Now you write code that will scan through both tables at the same time... COBOL-style.

The ORDER BY CommonField will cause all of the field values to be together, and since the two queries are sorted the same way you can decide what to do if the fields match, if one's smaller, if the other's smaller, if you're at end-of-table on one or the other or both... about six different cases. For a match, either the dates match or one's smaller or the other's smaller...

This problem can be solved with exactly one sequential pass which goes through both of these two streams at the same time. When all those reels of magnetic-tape were spinning in those science fiction movies, this is exactly what they were doing. In fact, when IBM sold punched card tabulators before computers existed, that's what they were doing, too!

Identically sorted streams of data...|||sundial, what the heck is your SQL Sever background, anyway? Over the last few days you have given some of the worst advice I've seen on this forum.

Nour,

Here is a general solution assuming that the OID fields can't be used to join your two tables. The solution is simpler if it can. This checked out syntactically in Query Analyzer, but without your exact table structure I of course could not test it. Other factors that could come into consideration would be things like duplicate DATE2 values in your second table.

Look this over and let me know if you have any more question.

select DateCompare.*,
table2.*
from
(select table1.OID,
table1.DATE1,
table1.ComonField1,
table1.Field1,
min(datediff(s, table2.DATE2, table1.DATE1)) MinSeconds
from table1
inner join table2 on table1.DATE1 >= table2.DATE2
group by table1.OID,
table1.DATE1,
table1.ComonField1,
table1.Field1) DateCompare
inner join table2 on datediff(s, table2.DATE2, DateCompare.DATE1) = DateCompare.MinSeconds

blindman|||Thanks Guys
I am going to try the SQL script to see if it works with my tables.|||Just a question to blindman
What's the table DateCompare, I am not creating a new table, I willl be updating the TABLE1 by creating two new columns where I will update with the values in the row where the date difference is the minimum.
A clarification:
I am comparing only rows from TABLE1 and TABLE2 which have the same OID et comonField
Thanks|||DateCompare is the name of the subquery, which has to be assigned a name so it can be joined in the outer query. You can give it any name you want as long as you change all the references to it.

blindman

Challenging Insert Trigger

First, please reference the following document which shows the layout of the database and tables:

http://www.eastproject.org/cody/table_layout.pdf

Here's the deal--

I have two databases, SiteData and CommunityServer2. Both run on the same MS SQL 2000 Server. We're concerned about two tables on each database: SiteData.dbo.Users, SiteData.dbo.UM_User_Access_Type, CommunityServer2.dbo.aspnet_Users and CommunityServer2.dbo.aspnet_UsersInRoles.

Whever a user is inserted into the CommunityServer2.dbo.aspnet_Users table, I need to give the user appropriate rules (e.g., moderator, student, etc.) by inserting the correct RoleID in the SiteData.dbo.aspnet_UsersInRoles table.

Have I lost you yet? Good.

Please reference the following trigger:

ALTER TRIGGER trig_UpdateForumRoles
ON CommunityServer2.dbo.aspnet_Users
AFTER INSERT
AS
DECLARE @.Username VARCHAR(100) --variable to hold the username from CommunityServer2.aspnet_Users
DECLARE @.UserType VARCHAR(50) --variable to hold the username from SiteData.dbo.UM_User_Access_Type
DECLARE @.UserID uniqueidentifier --variable to hold the username from CommunityServer2.aspnet_Users
BEGIN

SELECT @.Username=Username, @.UserID=UserID FROM Inserted --should only return one record, meaning we'd only insert one record in aspnet_Users at a time.

SELECT @.UserType=UserType
FROM SiteData.dbo.UM_User_Access_Type UM, SiteData.dbo.Users U, aspnet_Users AU, aspnet_Roles AR, aspnet_UsersInRoles AUIR
WHEREU.Username=@.UserName and U.ID = UM.Student_ID and U.Username = AU.UserName and AU.UserID = AUIR.UserID and AUIR.RoleID = AR.RoleID


-- if usertype is a SuperAdmin as determined from SiteData.dbo.UM_User_Access_Type, we insert the appropriate RoleID in aspnet_UsersInRoles
IF (@.UserType = 'SuperAdmin')
BEGIN
UPDATE aspnet_UsersInRole
SET RoleID = 'EDA73944-1B67-4DAA-B6CB-792847B6C694' WHERE UserID = @.UserID
END

IF (@.UserType = 'StaffAdmin' or @.UserType='Partner')
BEGIN
UPDATE aspnet_UsersInRole
SET RoleID = '7B317FF8-7C3C-4D95-AC44-E27E849D4520' WHERE UserID = @.UserID
END

IF (@.UserType = 'Facilitator')
BEGIN
UPDATE aspnet_UsersInRole
SET RoleID = 'D04FEA9F-35E9-41A5-B062-B9C1524D4266' WHERE UserID = @.UserID
END

IF (@.UserType = 'Student')
BEGIN
UPDATE aspnet_UsersInRole
SET RoleID = '4F94ABB9-1371-4834-95D6-E8364824F484' WHERE UserID = @.UserID
END


END
GO

However, it's not yet working. I can create the trigger fine (so no syntax problems); however, it's not updating the aspnet_UsersInRoles table.

Any ideas?

Thank you very much!
-Cody

First, please reference the following document which shows the layout of the database and tables:

http://www.eastproject.org/cody/table_layout.pdf

Here's the deal--

I have two databases, SiteData and CommunityServer2. Both run on the same MS SQL 2000 Server. We're concerned about two tables on each database: SiteData.dbo.Users, SiteData.dbo.UM_User_Access_Type, CommunityServer2.dbo.aspnet_Users and CommunityServer2.dbo.aspnet_UsersInRoles.

Whever a user is inserted into the CommunityServer2.dbo.aspnet_Users table, I need to give the user appropriate rules (e.g., moderator, student, etc.)by inserting the correct RoleID in the SiteData.dbo.aspnet_UsersInRoles table.

Have I lost you yet? Good.

Please reference the following trigger:

ALTER TRIGGER trig_UpdateForumRoles
ON CommunityServer2.dbo.aspnet_Users
AFTER INSERT
AS
DECLARE @.Username VARCHAR(100) --variable to hold the username from CommunityServer2.aspnet_Users
DECLARE @.UserType VARCHAR(50) --variable to hold the username from SiteData.dbo.UM_User_Access_Type
DECLARE @.UserID uniqueidentifier --variable to hold the username from CommunityServer2.aspnet_Users
BEGIN

SELECT @.Username=Username, @.UserID=UserID FROM Inserted --should only return one record, meaning we'd only insert one record in aspnet_Users at a time.

SELECT @.UserType=UserType
FROM SiteData.dbo.UM_User_Access_Type UM, SiteData.dbo.Users U, aspnet_Users AU, aspnet_Roles AR, aspnet_UsersInRoles AUIR
WHEREU.Username=@.UserName and U.ID = UM.Student_ID and U.Username = AU.UserName and AU.UserID = AUIR.UserID and AUIR.RoleID = AR.RoleID


-- if usertype is a SuperAdmin as determined from SiteData.dbo.UM_User_Access_Type, we insert the appropriate RoleID in aspnet_UsersInRoles
IF (@.UserType = 'SuperAdmin')
BEGIN
UPDATE aspnet_UsersInRole
SET RoleID = 'EDA73944-1B67-4DAA-B6CB-792847B6C694' WHERE UserID = @.UserID
END

IF (@.UserType = 'StaffAdmin' or @.UserType='Partner')
BEGIN
UPDATE aspnet_UsersInRole
SET RoleID = '7B317FF8-7C3C-4D95-AC44-E27E849D4520' WHERE UserID = @.UserID
END

IF (@.UserType = 'Facilitator')
BEGIN
UPDATE aspnet_UsersInRole
SET RoleID = 'D04FEA9F-35E9-41A5-B062-B9C1524D4266' WHERE UserID = @.UserID
END

IF (@.UserType = 'Student')
BEGIN
UPDATE aspnet_UsersInRole
SET RoleID = '4F94ABB9-1371-4834-95D6-E8364824F484' WHERE UserID = @.UserID
END


END
GO

However, it's not yet working. I can create the trigger fine (so no syntax problems); however, it's not updating the aspnet_UsersInRoles table.

Any ideas?

Thank you very much!
-Cody

|||

I'm afraid I don't understand your reply.

-Cody

|||You said you have to insert the correct RoleID in the aspnet_UsersInRole table, but all you issue is update statements. Update isn't what you want. Insert is.|||My apologies for the confusion --

The record will already exist in the aspnet_UsersInRoles table; I just need to update the roleID field of the appropriate record.
-Cody|||

Could be anything, try putting print statements in your trigger, then test it using transactions like:

BEGIN TRANSACTION
INSERT ...
ROLLBACK TRANSACTION

then execute that and see what messages you get. You'll need to use sql management studio or query analyzer though to get the print results.

CFE2SQL Server does not exist or access denied.

Does anyone have any idea what is causing the following error.
"CFE2SQL Server does not exist or access denied."
--
Thanks
JDSJDS wrote:
> Does anyone have any idea what is causing the following error.
> "CFE2SQL Server does not exist or access denied."
>
I'd say the server does not exist, can't be accessed, or you don't have
permission to use it. Without further detail, that's about all I can offer.|||The Server exist and this problem is happen sporadically. I can't find any
information on what is CFE2SQL.
--
Thanks
JDS
"Tracy McKibben" wrote:
> JDS wrote:
> > Does anyone have any idea what is causing the following error.
> >
> > "CFE2SQL Server does not exist or access denied."
> >
> >
> I'd say the server does not exist, can't be accessed, or you don't have
> permission to use it. Without further detail, that's about all I can offer.
>|||JDS wrote:
> The Server exist and this problem is happen sporadically. I can't find any
> information on what is CFE2SQL.
Where are you seeing this message displayed? The actual error message
is "SQL Server does not exist or access denied", the "CFE2" part is
coming from elsewhere.

CFE2SQL Server does not exist or access denied.

JDS wrote:
> Does anyone have any idea what is causing the following error.
> "CFE2SQL Server does not exist or access denied."
>
I'd say the server does not exist, can't be accessed, or you don't have
permission to use it. Without further detail, that's about all I can offer.The Server exist and this problem is happen sporadically. I can't find any
information on what is CFE2SQL.
--
Thanks
JDS
"Tracy McKibben" wrote:

> JDS wrote:
> I'd say the server does not exist, can't be accessed, or you don't have
> permission to use it. Without further detail, that's about all I can offe
r.
>|||Does anyone have any idea what is causing the following error.
"CFE2SQL Server does not exist or access denied."
Thanks
JDS|||JDS wrote:
> Does anyone have any idea what is causing the following error.
> "CFE2SQL Server does not exist or access denied."
>
I'd say the server does not exist, can't be accessed, or you don't have
permission to use it. Without further detail, that's about all I can offer.|||The Server exist and this problem is happen sporadically. I can't find any
information on what is CFE2SQL.
--
Thanks
JDS
"Tracy McKibben" wrote:

> JDS wrote:
> I'd say the server does not exist, can't be accessed, or you don't have
> permission to use it. Without further detail, that's about all I can offe
r.
>|||JDS wrote:
> The Server exist and this problem is happen sporadically. I can't find any
> information on what is CFE2SQL.
Where are you seeing this message displayed? The actual error message
is "SQL Server does not exist or access denied", the "CFE2" part is
coming from elsewhere.|||JDS wrote:
> The Server exist and this problem is happen sporadically. I can't find any
> information on what is CFE2SQL.
Where are you seeing this message displayed? The actual error message
is "SQL Server does not exist or access denied", the "CFE2" part is
coming from elsewhere.

central place for stored procedure variables

Hello,
I have numerous stored procedures with the following. Every time I make a
change I have to update all of my stored procedures. Is there a more
efficient way of doing this?
--Set Web Page Path
DECLARE
@.strWebDocPath_home varchar(300), @.strWebDocPath_aspx varchar(300),
@.strPhoto_home varchar(300), @.strPhoto_aspx varchar(300),
@.strRightArrow_home varchar(300), @.strRightArrow_aspx varchar(300),
--Set path for web pages
SET @.strWebDocPath_home = '<a href="http://links.10026.com/?link=web_doc/'
SET @.strWebDocPath_aspx = '<a href="http://links.10026.com/?link=../../web_doc/'
SET @.strPhoto_home = '<img src="http://pics.10026.com/?src=/library/images/photo/'
SET @.strPhoto_aspx = '<img src="http://pics.10026.com/?src=../../images/photo/'
SET @.strRightArrow_home = '<img src="http://pics.10026.com/?src=/library/images/icons/rightarrow.gif"
width="8" height="5">'
SET @.strRightArrow_aspx = '<img src="http://pics.10026.com/?src=../../images/icons/rightarrow.gif"
width="../../8" height="../../5">'
Thanks in advance,
sck10Why don't you put this data in a table?
David Portas
SQL Server MVP
--|||Depending on your overall system architecture, security requirements, system
complexity and maintenance provisions, you can either have the paths
represented as values in a table within the database, or an external config
file, or in rare instances in a registry or in .ini files etc.
Anith|||sck10 wrote:
> Hello,
> I have numerous stored procedures with the following. Every time I
> make a change I have to update all of my stored procedures. Is there
> a more efficient way of doing this?
>
> --Set Web Page Path
> DECLARE
> @.strWebDocPath_home varchar(300), @.strWebDocPath_aspx
> varchar(300), @.strPhoto_home varchar(300),
> @.strPhoto_aspx varchar(300), @.strRightArrow_home varchar(300),
> @.strRightArrow_aspx varchar(300),
> --Set path for web pages
> SET @.strWebDocPath_home = '<a href="http://links.10026.com/?link=web_doc/'
> SET @.strWebDocPath_aspx = '<a href="http://links.10026.com/?link=../../web_doc/'
> SET @.strPhoto_home = '<img src="http://pics.10026.com/?src=/library/images/photo/'
> SET @.strPhoto_aspx = '<img src="http://pics.10026.com/?src=../../images/photo/'
> SET @.strRightArrow_home = '<img src="http://pics.10026.com/?src=/library/images/icons/rightarrow.gif"
> width="8" height="5">'
> SET @.strRightArrow_aspx = '<img
> src="http://pics.10026.com/?src=../../images/icons/rightarrow.gif" width="../../8"
> height="../../5">'
Put the HTML snippets in a table and write a function to access the
table by string ID. For example:
Create Table HTMLSnippet (
HTMLID CHAR(15) NOT NULL PRIMARY KEY,
HTMLSnip VARCHAR(100),
HTMLDesc VARCHAR(255))
-- insert values into table
Insert Into HTMLSnippet Values ('DOCPATHHOME, '<a href="http://links.10026.com/?link=web_doc/', 'The
Doc path home')
-- etc
Create Function dbo.GetHTMLSnippet (
@.HTMLID CHAR(15) )
RETURNS VARCHAR(100)
AS
DECLARE @.HTMLCode VARCHAR(100)
SELECT @.HTMLCode = HTMLCode From HTMLSnippet Where @.HTMLID = @.HTMLID
RETURN @.HTMLCode
-- to use function
Declare @.test varchar(1000)
Select @.test = dbo.GetHTMLSnippet('DOCPATHHOME') + ...
Not tested, but should give you a good start.
David Gugick
Imceda Software
www.imceda.com|||I agree with David, put it into a table and then SELECT the values in your
SP's. Then if they change you only have to change them in the table on
time, all your SP's will be able to use the updated info appropriately...
"sck10" <sck10@.online.nospam> wrote in message
news:Oa2Oy0oUFHA.3140@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I have numerous stored procedures with the following. Every time I make a
> change I have to update all of my stored procedures. Is there a more
> efficient way of doing this?
>
> --Set Web Page Path
> DECLARE
> @.strWebDocPath_home varchar(300), @.strWebDocPath_aspx varchar(300),
> @.strPhoto_home varchar(300), @.strPhoto_aspx varchar(300),
> @.strRightArrow_home varchar(300), @.strRightArrow_aspx
> varchar(300),
> --Set path for web pages
> SET @.strWebDocPath_home = '<a href="http://links.10026.com/?link=web_doc/'
> SET @.strWebDocPath_aspx = '<a href="http://links.10026.com/?link=../../web_doc/'
> SET @.strPhoto_home = '<img src="http://pics.10026.com/?src=/library/images/photo/'
> SET @.strPhoto_aspx = '<img src="http://pics.10026.com/?src=../../images/photo/'
> SET @.strRightArrow_home = '<img src="http://pics.10026.com/?src=/library/images/icons/rightarrow.gif"
> width="8" height="5">'
> SET @.strRightArrow_aspx = '<img src="http://pics.10026.com/?src=../../images/icons/rightarrow.gif"
> width="../../8" height="../../5">'
> --
> Thanks in advance,
> sck10
>|||One more vote for using SQL Server to store data! Alternatively you might
consider building something (or finding something) to do macro's for SQL
code to have this automatically done for you (it might be slightly faster to
do what you have done rather than a table, if you have ultra-high
performance needs:
For example in your code put:
--<start macro replace-name_of_macro>
--Set Web Page Path
DECLARE
@.strWebDocPath_home varchar(300), @.strWebDocPath_aspx varchar(300),
@.strPhoto_home varchar(300), @.strPhoto_aspx varchar(300),
@.strRightArrow_home varchar(300), @.strRightArrow_aspx varchar(300),
--Set path for web pages
SET @.strWebDocPath_home = '<a href="http://links.10026.com/?link=web_doc/'
SET @.strWebDocPath_aspx = '<a href="http://links.10026.com/?link=../../web_doc/'
SET @.strPhoto_home = '<img src="http://pics.10026.com/?src=/library/images/photo/'
SET @.strPhoto_aspx = '<img src="http://pics.10026.com/?src=../../images/photo/'
SET @.strRightArrow_home = '<img src="http://pics.10026.com/?src=/library/images/icons/rightarrow.gif"
width="8" height="5">'
SET @.strRightArrow_aspx = '<img src="http://pics.10026.com/?src=../../images/icons/rightarrow.gif"
width="../../8" height="../../5">'
--<end macro replace-name_of_macro>
Now it would be easy to go through all of the files with stored procedures
in them and do a find and replace for this stuff. I would probably
suggest just using a table and cross joining to it whenever you need these
values in a few cases to try first. In the end it will be a far better
solution than putting data into variables. SQL Server does not now, and
forseeable future will not have constants like this.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"sck10" <sck10@.online.nospam> wrote in message
news:Oa2Oy0oUFHA.3140@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I have numerous stored procedures with the following. Every time I make a
> change I have to update all of my stored procedures. Is there a more
> efficient way of doing this?
>
> --Set Web Page Path
> DECLARE
> @.strWebDocPath_home varchar(300), @.strWebDocPath_aspx varchar(300),
> @.strPhoto_home varchar(300), @.strPhoto_aspx varchar(300),
> @.strRightArrow_home varchar(300), @.strRightArrow_aspx
> varchar(300),
> --Set path for web pages
> SET @.strWebDocPath_home = '<a href="http://links.10026.com/?link=web_doc/'
> SET @.strWebDocPath_aspx = '<a href="http://links.10026.com/?link=../../web_doc/'
> SET @.strPhoto_home = '<img src="http://pics.10026.com/?src=/library/images/photo/'
> SET @.strPhoto_aspx = '<img src="http://pics.10026.com/?src=../../images/photo/'
> SET @.strRightArrow_home = '<img src="http://pics.10026.com/?src=/library/images/icons/rightarrow.gif"
> width="8" height="5">'
> SET @.strRightArrow_aspx = '<img src="http://pics.10026.com/?src=../../images/icons/rightarrow.gif"
> width="../../8" height="../../5">'
> --
> Thanks in advance,
> sck10
>

Cell Data Security question

I have a cube that needs to filter out data for a group of users based on the company they work for.

I thought about using the following MDX expression in the Cell Data section in the Edit Roles window for this particular cube:

Ancestor([Dim OPCO].CurrentMember,[OPCO]) IS [COMPANY NAME]

The issue is that the users have to select there particular OPCO COMPANY Name in which ever tool they are using otherwise they will only see NA as the value for all measures.

I would like to filter the data somehow so they do not need to select the particular company they are working for. I would then setup different role groups based on the users company.

Is there some other way of accomplishing this type of task using a filter or scope command?

Thanks ahead of time

Steve Fibich

Hi Steve,

Once you have your roles in place, how about using member security to only permit the company users to see the member for their company? (And make this the default member for that particular role - I assume this is possible, but have yet to do it myself!).

An alternative is to have separate cubes for each company, which I can appreciate is a considerable amount of overhead.

Regards,

Will.

|||

Will,

This sounds like a good solution, how do you set the default propertie in the role. I tried the the following MDX but I get an error when I try to open the Cube.

Under default Member for that particular dimension

[Dim OPCO].[Opco Desc].MemberValue =[Company Name]

I am pretty new to MDX so if this statement is incorrect please let me know. Also is there a good place to get actual examples of MDX vs. the description provided through most of the MS MSDN help sites.

Thanks,

Steve

|||

Never mind I just found the MDX GUI Tool ....I am a little slow today.

Thanks for your help.

CD -- RePost

I posted the following back in May and received the reply below.

I asked again in July, but got no response so I assume that wasn't the way to handle it ...

So -- here is the RePost & Reply.

Question is still open: Is the CD ready yet ?/

Roger

===========================================

Can I get: SQL Express with Advanced Services on a CD (or DVD)

I'm on DialUp so a download would take 24hr+.

I was able to get the MS .NET Framework 2.0 on DVD because an MVP took pity on me and was kind enough to give me the "Secret" URL to the correct Order Desk.

I'm hoping one of you knows the URL for this CD/DVD

Roger

=========================================

Hi Roger,

We're working on CD images for SQL Server Express with Advanced Services in all the languages that SQL Server usually ships in. The English version is in final testing and should be available in a couple of weeks.

Thanks

---

Lead Program Manager, Microsoft SQL Server Storage Engine

Hi AdeptBlue,

We're working on posting a CD image file similar to what VS Express has done. I'm not aware of specific plans to make a physical CD available for people to order. It's not a matter of "secret order desks" we just haven't produced the physical media.

I'll pass the request onto our marketing devision, but I don't know what their plans are with regards to physical CD media.

Regards,

Mike

catching of output for RESTORE FILELISTONLY.

Hi Folks,
How can I catch the output(columns) of the following SQL
command in a table or each column in variables?
RESTORE FILELISTONLY
FROM DISK='c:\temp\FlexKIDS.bak'
I have tried the following construction, which did NOT
work:
CREATE TABLE #LIST_FILE
(LogicalName varchar(120),
PhysicalName varchar(500),
L_Type char(1),
L_FileGROUP varchar(60),
L_size int,
Max_size int)
INSERT #LIST_FILE EXEC RESTORE FILELISTONLY FROM
DISK='c:\temp\FlexKIDS.bak'INSERT #LIST_FILE EXEC ('RESTORE FILELISTONLY FROM
DISK=''c:\temp\FlexKIDS.bak''')
--
Jacco Schalkwijk
SQL Server MVP
"jack" <jbonapart@.dicon.nl> wrote in message
news:090801c3a834$312770e0$a401280a@.phx.gbl...
> Hi Folks,
> How can I catch the output(columns) of the following SQL
> command in a table or each column in variables?
> RESTORE FILELISTONLY
> FROM DISK='c:\temp\FlexKIDS.bak'
> I have tried the following construction, which did NOT
> work:
> CREATE TABLE #LIST_FILE
> (LogicalName varchar(120),
> PhysicalName varchar(500),
> L_Type char(1),
> L_FileGROUP varchar(60),
> L_size int,
> Max_size int)
>
> INSERT #LIST_FILE EXEC RESTORE FILELISTONLY FROM
> DISK='c:\temp\FlexKIDS.bak'|||Insert into #LIST_FILE
exec('RESTORE FILELISTONLY
FROM DISK = ''c:\temp\FlexKIDS.bak''')
This will help to capture the result set into a temp. table
Try this out .!
Regards,
Raghu
>--Original Message--
>Hi Folks,
>How can I catch the output(columns) of the following SQL
>command in a table or each column in variables?
>RESTORE FILELISTONLY
>FROM DISK='c:\temp\FlexKIDS.bak'
>I have tried the following construction, which did NOT
>work:
>CREATE TABLE #LIST_FILE
>(LogicalName varchar(120),
> PhysicalName varchar(500),
> L_Type char(1),
> L_FileGROUP varchar(60),
> L_size int,
> Max_size int)
>
>INSERT #LIST_FILE EXEC RESTORE FILELISTONLY FROM
>DISK='c:\temp\FlexKIDS.bak'
>.
>

Catastrophic Failure, MSDASQL, Linked Servers and MySQL

When executing a query which joins against a large resultset from a linked server, SQL Server reports the following error:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "foo" reported an error. The provider reported an unexpected catastrophic failure.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "foo".

This is using SQL Server 2005 with SP2, and MyODBC version 3.51.16 (although previous versions also seem to have the same behavior).

Once the error occurs any subsequent query against the linked server (even something as simple as select top 3 * From foo...users) fails with the same error message. Queries using the OpenQuery syntax also fail with this error.

I've tried to determine if there are some error logs that might give me more insight into what's going on, but the above events don't seem to log to the ODBC trace logs. And I can't seem to figure out a way to trace the MSDASQL (i.e., oledb) events.

I initially thought there might be a problem with the MySQL myODBC driver, but the ODBC layer itself does not seem to have a problem. But the following steps indicate otherwise:
1. Create a new Linked Server to MySQL. Same error as above
2. Execute a query against the ODBC DSN directly, using a shell program. This works fine

I'm trying to clearly identify if the problem is with the MySQL driver or with the MSDASQL provider. It would appear that the latter is at fault, given the error message, and the lack of any ODBC logging.

Would anyone have any insights about what might be going on here, and if there is any possible resolution?
Here is a link to the tracing article : http://msdn2.microsoft.com/en-us/library/aa964124.aspx
MSDASQL does not produce sufficient trace information, since it was not extensively instrumented, but you might get some traces from other layers and modules - ODBC driver manager, MSDART, MSDATL3.
The ODBC driver manager contains much more ETW-enabled tracing in Vista than in the previous versions.|||

Hello Cybertoast:

Can you resolve this problem ?

I have the same error.

Thanks a lot

Daniel Hefez

Catastrophic Failure, MSDASQL, Linked Servers and MySQL

When executing a query which joins against a large resultset from a linked server, SQL Server reports the following error:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "foo" reported an error. The provider reported an unexpected catastrophic failure.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "foo".

This is using SQL Server 2005 with SP2, and MyODBC version 3.51.16 (although previous versions also seem to have the same behavior).

Once the error occurs any subsequent query against the linked server (even something as simple as select top 3 * From foo...users) fails with the same error message. Queries using the OpenQuery syntax also fail with this error.

I've tried to determine if there are some error logs that might give me more insight into what's going on, but the above events don't seem to log to the ODBC trace logs. And I can't seem to figure out a way to trace the MSDASQL (i.e., oledb) events.

I initially thought there might be a problem with the MySQL myODBC driver, but the ODBC layer itself does not seem to have a problem. But the following steps indicate otherwise:
1. Create a new Linked Server to MySQL. Same error as above
2. Execute a query against the ODBC DSN directly, using a shell program. This works fine

I'm trying to clearly identify if the problem is with the MySQL driver or with the MSDASQL provider. It would appear that the latter is at fault, given the error message, and the lack of any ODBC logging.

Would anyone have any insights about what might be going on here, and if there is any possible resolution?
Here is a link to the tracing article : http://msdn2.microsoft.com/en-us/library/aa964124.aspx
MSDASQL does not produce sufficient trace information, since it was not extensively instrumented, but you might get some traces from other layers and modules - ODBC driver manager, MSDART, MSDATL3.
The ODBC driver manager contains much more ETW-enabled tracing in Vista than in the previous versions.|||

Hello Cybertoast:

Can you resolve this problem ?

I have the same error.

Thanks a lot

Daniel Hefez

catalog information cannot be retrieved

What is the solution to the following udl test
connection error message.
First you receive a 'Test Completed Successfully'
Then you receive 'Login failed. Catalog information
cannot be retrieved.'
Hi,
Could u try installing the latest MDAC and verify.
http://www.microsoft.com/downloads/d...fe3-c795-4b7d-
b037-185d0506396c&DisplayLang=en
Thanks
Hari
MCDBA
"illworth@.hotmail.com" <anonymous@.discussions.microsoft.com> wrote in
message news:338701c47ee2$d8342060$a301280a@.phx.gbl...
> What is the solution to the following udl test
> connection error message.
> First you receive a 'Test Completed Successfully'
> Then you receive 'Login failed. Catalog information
> cannot be retrieved.'
>
|||We have 2.71 installed. Same udl copied to another
workstation works.

>--Original Message--
>Hi,
>Could u try installing the latest MDAC and verify.
>http://www.microsoft.com/downloads/details.aspx?
FamilyID=6c050fe3-c795-4b7d-
>b037-185d0506396c&DisplayLang=en
>Thanks
>Hari
>MCDBA
>"illworth@.hotmail.com"
<anonymous@.discussions.microsoft.com> wrote in
>message news:338701c47ee2$d8342060$a301280a@.phx.gbl...
>
>.
>

catalog information cannot be retrieved

What is the solution to the following udl test
connection error message.
First you receive a 'Test Completed Successfully'
Then you receive 'Login failed. Catalog information
cannot be retrieved.'Hi,
Could u try installing the latest MDAC and verify.
http://www.microsoft.com/downloads/...0fe3-c795-4b7d-
b037-185d0506396c&DisplayLang=en
Thanks
Hari
MCDBA
"illworth@.hotmail.com" <anonymous@.discussions.microsoft.com> wrote in
message news:338701c47ee2$d8342060$a301280a@.phx.gbl...
> What is the solution to the following udl test
> connection error message.
> First you receive a 'Test Completed Successfully'
> Then you receive 'Login failed. Catalog information
> cannot be retrieved.'
>|||We have 2.71 installed. Same udl copied to another
workstation works.

>--Original Message--
>Hi,
>Could u try installing the latest MDAC and verify.
>http://www.microsoft.com/downloads/details.aspx?
FamilyID=6c050fe3-c795-4b7d-
>b037-185d0506396c&DisplayLang=en
>Thanks
>Hari
>MCDBA
>"illworth@.hotmail.com"
<anonymous@.discussions.microsoft.com> wrote in
>message news:338701c47ee2$d8342060$a301280a@.phx.gbl...
>
>.
>

Cast Problem

If I run the following query in SQL Server Management Studio it returns the correct results: (Searching the table for the field "SpecimenID (an INT)" against the data entered (a Text Field - "7575-01") from the submitted form.

SELECT ClinicalID, SpecimenID, PatientID, LabID, Accession, Bacillus, Francisella, Yersinia, Brucella, Burkholderia, Coxiella, Staphylococcus, Other,
OtherExplanation, CollectionDate, strddlTransportMedium, strddlSpecimenSource, UserName, Test, SpecimenCount, DateAndTime
FROM ClinicalSpecimen
WHERE (SpecimenID = CAST('7575-01' AS VARCHAR(50)))
ORDER BY SpecimenID DESC

However, when I try to use the same logic in the ASPX.VB code behind page, as follows below, I either get an error message (Syntax error converting the varchar value '' to a column of data type int.) or record not found... Can someone please explain what I am missing here...

MySQL ="SELECT * FROM ClinicalSpecimen WHERE SpecimenID = CAST(('" & AccessionPresent &"') AS VARCHAR(50))"

*"AccessionPresent" is the value of the text field retrieved from the form.

I guess what I am really asking is how can I search for an INT value in a table using a VARCHAR Field.

Thank you for any or all assistance !!!

Looks like you have a blank value that you are trying to convert to int. Check for NULL/Blanks in your application before you CAST as varchar.

|||

Instead of:
MySQL ="SELECT * FROM ClinicalSpecimen WHERE SpecimenID = CAST(('" & AccessionPresent &"') AS VARCHAR(50))"

Try:
MySQL ="SELECT * FROM ClinicalSpecimen WHERE SpecimenID = CAST('" & AccessionPresent &"' AS VARCHAR(50))"

Remove the Paren's, also as shark said, do something like if string.isnullorempty(AccessionPresent) then put a dummy value their or whatever

|||

Try this (use this query in your MySQL):

1SELECT *2FROM ClinicalSpecimen3WHERE SpecimenID =CAST(4ISNULL(5 ('" & AccessionPresent & "')6 , -1-- you will get -1 for Null cases7 )8AS VARCHAR(50)9 )1011-- I splited the query to make it more readable (you can put it in one line)

Hope this will help.

Good luck.

|||

You are casting the wrong side to a varchar. The right side of your comparision is always a varchar already. It's the int side that isn't.

Try:

Dim conn as new SqlConnection(...)

Dim cmd as new SqlCommand("SELECT * FROM ClinicalSpecimen WHERE CAST(SpecimenID AS varchar(50))=@.SpecimenID",conn)

cmd.Parameters.Add("@.SpecimenID",SqlDbType.varchar).Value=AccessionPresent

Note, this also removes the SQL Injection problem you had.

|||

Motley:

You are casting the wrong side to a varchar. The right side of your comparision is always a varchar already. It's the int side that isn't......

Good catch Motley.

|||

ndinakar:

Motley:

You are casting the wrong side to a varchar. The right side of your comparision is always a varchar already. It's the int side that isn't......

Good catch Motley.

Yes, good catch Motley

How we did not realise that!!

CAST Money ?

I have tried to use the following statement (SQL Server 2005) where XXX is a
numeric field but there is no $ sign appears.
CAST (XXX,Money)
In this way, I try to use '$' + CAST (XXX, varchar) and it is OK. Is there
any better way to handle it AND why the first statement doesn't work ?
Thanks
PeterPeter
No, but do you think there is only '$' on the earth?
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:O3Sw0%23YsHHA.1060@.TK2MSFTNGP06.phx.gbl...
>I have tried to use the following statement (SQL Server 2005) where XXX is
>a numeric field but there is no $ sign appears.
> CAST (XXX,Money)
> In this way, I try to use '$' + CAST (XXX, varchar) and it is OK. Is
> there any better way to handle it AND why the first statement doesn't work
> ?
> Thanks
> Peter
>|||Peter,
Hi
the answer is NO. The best thing is to format it at the client side
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:O3Sw0%23YsHHA.1060@.TK2MSFTNGP06.phx.gbl...
>I have tried to use the following statement (SQL Server 2005) where XXX is
>a numeric field but there is no $ sign appears.
> CAST (XXX,Money)
> In this way, I try to use '$' + CAST (XXX, varchar) and it is OK. Is
> there any better way to handle it AND why the first statement doesn't work
> ?
> Thanks
> Peter
>|||>I have tried to use the following statement (SQL Server 2005) where XXX is
>a numeric field but there is no $ sign appears.
And why should it? Wouldn't the English be a little angry if they had to
then run REPLACE to put a pound symbol instead of a dollar sign?
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006

CAST Money ?

I have tried to use the following statement (SQL Server 2005) where XXX is a
numeric field but there is no $ sign appears.
CAST (XXX,Money)
In this way, I try to use '$' + CAST (XXX, varchar) and it is OK. Is there
any better way to handle it AND why the first statement doesn't work ?
Thanks
PeterPeter
No, but do you think there is only '$' on the earth?
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:O3Sw0%23YsHHA.1060@.TK2MSFTNGP06.phx.gbl...
>I have tried to use the following statement (SQL Server 2005) where XXX is
>a numeric field but there is no $ sign appears.
> CAST (XXX,Money)
> In this way, I try to use '$' + CAST (XXX, varchar) and it is OK. Is
> there any better way to handle it AND why the first statement doesn't work
> ?
> Thanks
> Peter
>|||Peter,
Hi
the answer is NO. The best thing is to format it at the client side
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:O3Sw0%23YsHHA.1060@.TK2MSFTNGP06.phx.gbl...
>I have tried to use the following statement (SQL Server 2005) where XXX is
>a numeric field but there is no $ sign appears.
> CAST (XXX,Money)
> In this way, I try to use '$' + CAST (XXX, varchar) and it is OK. Is
> there any better way to handle it AND why the first statement doesn't work
> ?
> Thanks
> Peter
>|||>I have tried to use the following statement (SQL Server 2005) where XXX is
>a numeric field but there is no $ sign appears.
And why should it? Wouldn't the English be a little angry if they had to
then run REPLACE to put a pound symbol instead of a dollar sign?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006

CAST error

I am keep getting error when I use CAST function in Expression.

If I run (DT_I4)("1") I got following error.

TITLE: Expression Builder

Cannot convert expression value to property type.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.DataTransformationServices.Controls.TaskUIFramework.TaskUIFrameworkSR&EvtID=CannotAssignExpressionToProperty&LinkId=20476

ADDITIONAL INFORMATION:

Cannot convert 'System.Int32' to 'System.Int64'.

BUTTONS:

OK

This says to me that the target of the expression, such as a property, is of type Int64, or DT_I8, but your value is only Int32 Try the following instead-

(DT_I8)("1")