Do drag and drop controls create DataSets

When I drag a GridView from the toolbox onto a Webform, and then configure its DataSource is a true DataSet created that I can access in the code behind? When my results return I want to be able to access individual rows and cells, taking their values, assiging them to variables and then using the newly equated variable to perform calculations.

Thank you,

Your GridView is bound to dataset It is simple to access values in dataset: your_DataTable.Rows[rowindex][colunmindex]

Or use GridView1.Rows[rowid].Cells[cellindex] to access the control (or findControl(id) if there are many).

Do comments work in EM job?

I've created a job in Enterprise Manager under Management | Jobs. I double
click the particular job, select the Steps tab, then double click the step
name. In the command window are four stored procedures that I execute:
exec sp1
exec sp2
exec sp3
exec sp4
This works fine. If I comment out sp1
-- sp1
does this have any affect? I did comment out all four SPs and they seem to
keep running. I had to disable the job to keep them from running.
Must I disable/enable a job for the comments to take affect?
Thanks,
BrettNot sure about that Brett,
I'd script out the job without/with comments and see what effect that had.
have a look in the scripts
From what you say, it sounds as tho you can't put comments in EM... which
isn't that surprising.
"Brett" <no@.spam.com> wrote in message
news:%23aq%23FmjCFHA.3280@.TK2MSFTNGP14.phx.gbl...
> I've created a job in Enterprise Manager under Management | Jobs. I
> double click the particular job, select the Steps tab, then double click
> the step name. In the command window are four stored procedures that I
> execute:
> exec sp1
> exec sp2
> exec sp3
> exec sp4
> This works fine. If I comment out sp1
> -- sp1
> does this have any affect? I did comment out all four SPs and they seem
> to keep running. I had to disable the job to keep them from running.
> Must I disable/enable a job for the comments to take affect?
> Thanks,
> Brett
>

DMX Shape query error

Hi I created a DMX query to retrieve predictions based on previous customer purchases and wanted to filter out my input data by only purchases made in the current year. I keep receiving this error:

Code Snippet

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

Internal error: An unexpected error occurred (file 'dmxinit.cpp', line 1343, function 'DMXNodeInput::InitFromASTOpenRowset'). (Microsoft SQL Server 2005 Analysis Services)

Program Location:

at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.IExecuteProvider.Execute(ICommandContentProvider contentProvider, AdomdPropertyCollection commandProperties, IDataParameterCollection parameters)
at Microsoft.AnalysisServices.AdomdClient.AdomdCommand.Execute()
at Microsoft.AnalysisServices.Controls.QueryResultGridStorage.ThreadProc()

And, here's my query:

Code Snippet

SELECTFLATTENED

(SELECT *

FROMPredictAssociation([PredictTable],

10,

INCLUDE_NODE_ID,

INCLUDE_STATISTICS

)

WHERE$NODEID <> ''

)

FROM

[Mining Model]

NATURALPREDICTIONJOIN

SHAPE {

OPENQUERY( [datasrc],

'SELECT ''1234'' AS [Customer_D_SID]'

)

} APPEND ({

SHAPE {

OPENQUERY( [datasrc],

'SELECT [Product_D_SID],[Customer_D_SID], [Transaction_Date]

FROM [Base_Sales_F]

WHERE [Customer_D_SID] = ''1234'' '

)

} APPEND ({

OPENQUERY( [datasrc],

'SELECT [Calendar_D_SID],[CALENDAR_YR_NBR]

FROM [dbo].[Calendar_D]

WHERE [CALENDAR_YR_NBR] >= ''2007'' '

)

} RELATE [Calendar_D_SID] TO [Transaction_Date]) AS B

} RELATE B.[Customer_D_SID] TO [Customer_D_SID]) AS [PredictTable]

AS T

I figured the only way to associate the calendar table with the sales table was to use a nested shape statement... is this wrong? Thanks for any help!

The internal error is being raised because your SHAPE statement is generating 2 levels of nesting which doesn't match your model defnition (SQL Server DM only supports single-level nesting i.e. nested tables cannot have table columns).

You need to remove the second nested join and instead, use a view on the transaction table that includes the column (CALENDAR_YR_NBR) you want to filter on.

|||Thank you! Making a view solved my problem.sql

DMX in TSQL?

Hi, I'm new to Transact-SQL and I'm trying to throw a DMX query I have created into a stored procedure because I think it's the only way to pass variables into an openquery statement! It seems that no matter what I try gets me the error 'Incorrect usage of quotes'. I'm trying to use code like this:

Code Snippet

AS

BEGIN

DECLARE @.OPENQUERY nvarchar(4000), @.TSQL nvarchar(4000), @.LinkedServer nvarchar(4000)

SET @.LinkedServer = 'DMSERVER'

SET @.OPENQUERY = 'SELECT * FROM OPENQUERY('+ @.LinkedServer + ','''

SET @.TSQL = 'SELECT FLATTENED * FROM ['+@.miningModel+'].CONTENT'')'

EXEC (@.OPENQUERY+@.TSQL)

END

Which I found from some one else's posting on the data mining forums. I'm completely clueless as to how to make this work - Can I even do what I want to do? It seems like the brackets are causing all the fuss, but they're necessary for DMX queries. Any ideas? Thanks!

Code Snippet

AS

BEGIN

DECLARE @.OPENQUERY nvarchar(4000), @.TSQL nvarchar(4000), @.LinkedServer nvarchar(4000)

SET @.LinkedServer = 'DMSERVER'

SET @.OPENQUERY = 'SELECT * FROM OPENQUERY('+ @.LinkedServer + ','''

SET @.TSQL = 'SELECT * FROM ['+@.miningModel+'].CONTENT'')'

EXEC (@.OPENQUERY+@.TSQL)

END

|||

Still doesn't work...here's the error:

Code Snippet

TITLE: Microsoft Report Designer

An error occurred while retrieving the parameters in the query.
SqlCommand.DeriveParameters failed because the SqlCommand.CommandText property value is an invalid multipart name "AS
BEGIN
DECLARE @.OPENQUERY nvarchar(4000), @.TSQL nvarchar(4000), @.LinkedServer nvarchar(4000)
SET @.LinkedServer = 'abc'
SET @.OPENQUERY = 'SELECT * FROM OPENQUERY('+ @.LinkedServer + ','''
SET @.TSQL = 'SELECT * FROM ['+@.miningModel+'].CONTENT'')'
EXEC (@.OPENQUERY+@.TSQL)

END", incorrect usage of quotes.

ADDITIONAL INFORMATION:

SqlCommand.DeriveParameters failed because the SqlCommand.CommandText property value is an invalid multipart name "AS
BEGIN
DECLARE @.OPENQUERY nvarchar(4000), @.TSQL nvarchar(4000), @.LinkedServer nvarchar(4000)
SET @.LinkedServer = 'abc'
SET @.OPENQUERY = 'SELECT * FROM OPENQUERY('+ @.LinkedServer + ','''
SET @.TSQL = 'SELECT * FROM ['+@.miningModel+'].CONTENT'')'
EXEC (@.OPENQUERY+@.TSQL)

END", incorrect usage of quotes. (System.Data)

BUTTONS:

OK

|||are you initializing the variable @.miningModel?

|||

I have it defined as a report parameter in reporting svcs, with default value of 'PRRelational'... Even if I make it a local variable it still throws the same error...

EDIT: Turns out I was doing this from the wrong location...! Thanks. Will post if I get it to work.

EDIT2: Post shows how to do this: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1676053&SiteID=1&mode=1

DML against remote tables (MSSQL to DB2)

I created a linked server to a DB2 database and I can pull data fine, but when I try to insert/update/delete it tells me "SQL0471N Invocation of routine "SYSIBM.SQLTABLES" failed due to reason "00E7900C"" when trying: DELETE FROM DB2LinkedServer..SPACENAME.TABLENAME

I believe I need to send a clear string to DB2 that doesn't get compiled on the sql server side. Is there something like openquery that I can use for DML statements in SQL Server?Upon further review I found this site: http://support.microsoft.com/kb/270119/EN-US/

It shows how to use openquery to execute DML statements. nifty.

DLL and Xtended Proc...

Hello,
I've created my own sendmail in VB6 SP5 as a DLL and put it on my SQL server
2K SP3. I've a class module with only one public funtion in it.
I want to use the function in this dll so I decalred my DLL as an extended
proc in SQL . apprently SQL doesn't recognize the function in my dll. How
can I use it? What's the best way to do it?
Best regards,I'm not sure if a VB6 DLL can be registered as an extended stored procedure
this way.
If you want to use the functionality of your DLL from SQL Server, you could
use the sp_OA* set of procedures for the same. Refer to
http://www32.brinkster.com/srisamp/sqlarchives.asp for articles on COM
accessibility. You can refer to the articles titled "Using COM Objects in
SQL Server" and "Extending SQL Server with COM Objects".
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Philippe RUELLO" <pruello@.tibco.fr> wrote in message
news:OMjpLlYuDHA.2712@.tk2msftngp13.phx.gbl...
> Hello,
> I've created my own sendmail in VB6 SP5 as a DLL and put it on my SQL
server
> 2K SP3. I've a class module with only one public funtion in it.
> I want to use the function in this dll so I decalred my DLL as an extended
> proc in SQL . apprently SQL doesn't recognize the function in my dll. How
> can I use it? What's the best way to do it?
> Best regards,
>|||Just to confirm - VB only produces COM type .dlls, not call level type which
are required for extended stored procedures. So you cannot register VB .dlls
for use as extended stored procedures in SQL Server.
The other method that SriSamp has suggested (sp_OACreate) is the only way to
access your VB .dll directly from T-SQL.
Regards,
Greg Linwood
SQL Server MVP
"SriSamp" <ssampath@.sct.co.in> wrote in message
news:#j7#xrYuDHA.1576@.TK2MSFTNGP11.phx.gbl...
> I'm not sure if a VB6 DLL can be registered as an extended stored
procedure
> this way.
> If you want to use the functionality of your DLL from SQL Server, you
could
> use the sp_OA* set of procedures for the same. Refer to
> http://www32.brinkster.com/srisamp/sqlarchives.asp for articles on COM
> accessibility. You can refer to the articles titled "Using COM Objects in
> SQL Server" and "Extending SQL Server with COM Objects".
> --
> HTH,
> SriSamp
> Please reply to the whole group only!
> http://www32.brinkster.com/srisamp
> "Philippe RUELLO" <pruello@.tibco.fr> wrote in message
> news:OMjpLlYuDHA.2712@.tk2msftngp13.phx.gbl...
> > Hello,
> > I've created my own sendmail in VB6 SP5 as a DLL and put it on my SQL
> server
> > 2K SP3. I've a class module with only one public funtion in it.
> > I want to use the function in this dll so I decalred my DLL as an
extended
> > proc in SQL . apprently SQL doesn't recognize the function in my dll.
How
> > can I use it? What's the best way to do it?
> > Best regards,
> >
> >
>|||Thanks for the answer.
I tried to use that methos but i've some troubles using the attachements
with CDONT.Newmail
I could use Xp_sendmail, but I want to modify the parameter From.
Have some clues?
"SriSamp" <ssampath@.sct.co.in> a écrit dans le message de
news:%23j7%23xrYuDHA.1576@.TK2MSFTNGP11.phx.gbl...
> I'm not sure if a VB6 DLL can be registered as an extended stored
procedure
> this way.
> If you want to use the functionality of your DLL from SQL Server, you
could
> use the sp_OA* set of procedures for the same. Refer to
> http://www32.brinkster.com/srisamp/sqlarchives.asp for articles on COM
> accessibility. You can refer to the articles titled "Using COM Objects in
> SQL Server" and "Extending SQL Server with COM Objects".
> --
> HTH,
> SriSamp
> Please reply to the whole group only!
> http://www32.brinkster.com/srisamp
> "Philippe RUELLO" <pruello@.tibco.fr> wrote in message
> news:OMjpLlYuDHA.2712@.tk2msftngp13.phx.gbl...
> > Hello,
> > I've created my own sendmail in VB6 SP5 as a DLL and put it on my SQL
> server
> > 2K SP3. I've a class module with only one public funtion in it.
> > I want to use the function in this dll so I decalred my DLL as an
extended
> > proc in SQL . apprently SQL doesn't recognize the function in my dll.
How
> > can I use it? What's the best way to do it?
> > Best regards,
> >
> >
>|||How about xp_smtp_sendmail from www.SQLDev.Net?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Philippe RUELLO" <pruello@.tibco.fr> wrote in message news:uXn1yUZuDHA.2408@.tk2msftngp13.phx.gbl...
> Thanks for the answer.
> I tried to use that methos but i've some troubles using the attachements
> with CDONT.Newmail
> I could use Xp_sendmail, but I want to modify the parameter From.
> Have some clues?
> "SriSamp" <ssampath@.sct.co.in> a écrit dans le message de
> news:%23j7%23xrYuDHA.1576@.TK2MSFTNGP11.phx.gbl...
> > I'm not sure if a VB6 DLL can be registered as an extended stored
> procedure
> > this way.
> > If you want to use the functionality of your DLL from SQL Server, you
> could
> > use the sp_OA* set of procedures for the same. Refer to
> > http://www32.brinkster.com/srisamp/sqlarchives.asp for articles on COM
> > accessibility. You can refer to the articles titled "Using COM Objects in
> > SQL Server" and "Extending SQL Server with COM Objects".
> > --
> > HTH,
> > SriSamp
> > Please reply to the whole group only!
> > http://www32.brinkster.com/srisamp
> >
> > "Philippe RUELLO" <pruello@.tibco.fr> wrote in message
> > news:OMjpLlYuDHA.2712@.tk2msftngp13.phx.gbl...
> > > Hello,
> > > I've created my own sendmail in VB6 SP5 as a DLL and put it on my SQL
> > server
> > > 2K SP3. I've a class module with only one public funtion in it.
> > > I want to use the function in this dll so I decalred my DLL as an
> extended
> > > proc in SQL . apprently SQL doesn't recognize the function in my dll.
> How
> > > can I use it? What's the best way to do it?
> > > Best regards,
> > >
> > >
> >
> >
>

Dividing in a view: 1 or 0

Hello,
I am having, what seems to me as the oddest problem in SQL.
I have created a view based on two tables, the two key columns in these
tables are integers (quantity) what I am trying to do in this table is divide
one by the other to get the weight which I want as a decimal however SQL
Server is giving me a 1 or a 0 which I dont want!
Any ideas?!
Take a look at this
declare @.i1 int, @.i2 int
select @.i1 =10,@.i2 =3
select @.i1/@.i2,@.i1/(@.i2*1.0),1.0*@.i1/@.i2
http://sqlservercode.blogspot.com/
|||Thanks for your response however perhaps I wasnt clear,
The weight value will always be between 1 and 0 in the same way as a
percentage is between 1 and 0!
My problem is that when I have 34 / 823 I get 0 instead of 0.041312272
Thanks in Advance
Chris
"SQL" wrote:

> Take a look at this
> declare @.i1 int, @.i2 int
> select @.i1 =10,@.i2 =3
> select @.i1/@.i2,@.i1/(@.i2*1.0),1.0*@.i1/@.i2
> http://sqlservercode.blogspot.com/
>
|||If you do this
select (1.0*34 / 823 ) you will get .041312
and with this
select round(convert(decimal(9,5),34)/ convert(decimal(9,5),823),9) you
will get .041312272000000
http://sqlservercode.blogspot.com/

Dividing in a view: 1 or 0

Hello,
I am having, what seems to me as the oddest problem in SQL.
I have created a view based on two tables, the two key columns in these
tables are integers (quantity) what I am trying to do in this table is divid
e
one by the other to get the weight which I want as a decimal however SQL
Server is giving me a 1 or a 0 which I dont want!
Any ideas?!Take a look at this
declare @.i1 int, @.i2 int
select @.i1 =10,@.i2 =3
select @.i1/@.i2,@.i1/(@.i2*1.0),1.0*@.i1/@.i2
http://sqlservercode.blogspot.com/|||Thanks for your response however perhaps I wasnt clear,
The weight value will always be between 1 and 0 in the same way as a
percentage is between 1 and 0!
My problem is that when I have 34 / 823 I get 0 instead of 0.041312272
Thanks in Advance
Chris
"SQL" wrote:

> Take a look at this
> declare @.i1 int, @.i2 int
> select @.i1 =10,@.i2 =3
> select @.i1/@.i2,@.i1/(@.i2*1.0),1.0*@.i1/@.i2
> http://sqlservercode.blogspot.com/
>|||If you do this
select (1.0*34 / 823 ) you will get .041312
and with this
select round(convert(decimal(9,5),34)/ convert(decimal(9,5),823),9) you
will get .041312272000000
http://sqlservercode.blogspot.com/

Dividing in a view: 1 or 0

Hello,
I am having, what seems to me as the oddest problem in SQL.
I have created a view based on two tables, the two key columns in these
tables are integers (quantity) what I am trying to do in this table is divide
one by the other to get the weight which I want as a decimal however SQL
Server is giving me a 1 or a 0 which I dont want!
Any ideas?!Take a look at this
declare @.i1 int, @.i2 int
select @.i1 =10,@.i2 =3
select @.i1/@.i2,@.i1/(@.i2*1.0),1.0*@.i1/@.i2
http://sqlservercode.blogspot.com/|||Thanks for your response however perhaps I wasnt clear,
The weight value will always be between 1 and 0 in the same way as a
percentage is between 1 and 0!
My problem is that when I have 34 / 823 I get 0 instead of 0.041312272
Thanks in Advance
Chris
"SQL" wrote:
> Take a look at this
> declare @.i1 int, @.i2 int
> select @.i1 =10,@.i2 =3
> select @.i1/@.i2,@.i1/(@.i2*1.0),1.0*@.i1/@.i2
> http://sqlservercode.blogspot.com/
>|||If you do this
select (1.0*34 / 823 ) you will get .041312
and with this
select round(convert(decimal(9,5),34)/ convert(decimal(9,5),823),9) you
will get .041312272000000
http://sqlservercode.blogspot.com/

Distrubuted Transactions with AS400

Hi All
I have created stored procs that update SQL and AS400 (Linked server)
tables. I want to wrap both updates in a transaction, however when I do, I
get the error
"The operation could not be performed because OLE DB provider "MSDASQL" for
linked server "LinkedServerName" was unable to begin a distributed
transaction".
We are using Client Access ODBC driver (V5R2, SI06631).
All of the documents that I have read talk about MTS and MS DTC.
MS DTC is running on the SQL Server.
Is there any way of using Distributed Transaction processing without
requiring MTS ?
Thanks in advance.not enough info on the error.
here:
http://support.microsoft.com/kb/306212
you may want to look at the full error when you talk to DB2. like so
DBCC TRACEON (3604, 7300)
then run your sp and seewhat it does.
did you try just issue
BEGIN DISTRIBUTED TRAN
and see if the 2 data sources you are working with can be accessed?
and you the same authentication your application is going to call your sp
with.
Thanks, Liliya
"Jane" wrote:
> Hi All
> I have created stored procs that update SQL and AS400 (Linked server)
> tables. I want to wrap both updates in a transaction, however when I do, I
> get the error
> "The operation could not be performed because OLE DB provider "MSDASQL" for
> linked server "LinkedServerName" was unable to begin a distributed
> transaction".
> We are using Client Access ODBC driver (V5R2, SI06631).
> All of the documents that I have read talk about MTS and MS DTC.
> MS DTC is running on the SQL Server.
> Is there any way of using Distributed Transaction processing without
> requiring MTS ?
> Thanks in advance.|||Thanks for the response.
Yes - used the same auth as the app will use (windows auth)
Yes - just used BEGIN DISTRIBUTED TRAN
Used DBCC TRACEON (3604, 7300) & SET XACT_ABORT ON as per the link in a
Query Window then ran the updates again.
Ran DBCC TRACESTATUS (3604,7300) to check that the flags for the session
were set.
Still failing & still no extra error information.
Under "Configuration Issues" the link states ;
"Start the Distributed Transaction Coordinator (DTC or MSDTC) on all servers
that are involved in the distributed transaction".
I am not sure what needs to running on the AS400 for this to work.
"l" wrote:
> not enough info on the error.
> here:
> http://support.microsoft.com/kb/306212
> you may want to look at the full error when you talk to DB2. like so
> DBCC TRACEON (3604, 7300)
> then run your sp and seewhat it does.
> did you try just issue
> BEGIN DISTRIBUTED TRAN
> and see if the 2 data sources you are working with can be accessed?
> and you the same authentication your application is going to call your sp
> with.
> Thanks, Liliya
>
> "Jane" wrote:
> > Hi All
> >
> > I have created stored procs that update SQL and AS400 (Linked server)
> > tables. I want to wrap both updates in a transaction, however when I do, I
> > get the error
> > "The operation could not be performed because OLE DB provider "MSDASQL" for
> > linked server "LinkedServerName" was unable to begin a distributed
> > transaction".
> > We are using Client Access ODBC driver (V5R2, SI06631).
> > All of the documents that I have read talk about MTS and MS DTC.
> > MS DTC is running on the SQL Server.
> >
> > Is there any way of using Distributed Transaction processing without
> > requiring MTS ?
> >
> > Thanks in advance.|||OLEDB Errors in SQL Profiler shows ;
<hresult>-2147168246</hresult>
<inputs>
<punkTransactionCoord>0x624A0060</punkTransactionCoord>
<isoLevel>4096</isoLevel>
<isoFlags>0</isoFlags>
<pOtherOptions>0x00000000</pOtherOptions>
</inputs>|||> I am not sure what needs to running on the AS400 for this to work.
depend on what are you running on AS400. DB2 version. Here. how to set up
mts (0you have mentioned this one earlier in this thread)
http://www-03.ibm.com/servers/enable/site/db2/mts/mts.pdf
as about ms sql server side, it does not hurt to test if your sql side is
ok. Easy enough as long as you have couple ms sql's on the same domain etc.
if not, can install one on your ws, configure it and see if you can issue
distributed transactions. After all, it will give you a 'lab rat' s well to
experiment if you in need of one.
Thanks, Liliya

Distributor cannot connect to subscriber

I am setting up my 2005 Replication system...

publisher = 2005 sp1

Subscriber = 2005 sp1

I created a publication for a single table. Then I created the subscription to another 2005 server. Had to add it as a subscriber in the wizard. Told it to do the snapshot right away.

Everything seems fine right up to the point where it tries to connect to the subscriber... I get a cannot connect error. I have tried all kinds of security context and accounts for the sql agent to run under but nothing seems to work. I cannot even get a linked server to work. I have the subscriber setup to accept remote connections.

I am not sure where to look at next... I never had this issue in 2000.

Did a little more testing. My distributor/publisher also has SQL 2000 on it. I think this might have something to do with it.

I created a linked server on my subscriber to my publisher/dist and it has no problem connecting what so ever.

Could it be that my pub/dist is using the wrong client files?

|||

Hi William,

Are you using merge or transactional replication?

Is the subscription that you set up a push or pull? This will determine where the distribution or merge agent is running.

In you second note, you indicate that the distributor/publisher has SQL 2000. Is this in addition to SQL 2005, per the first note?

Assuming that you have SQL 2000 and SQL 2005 on the boxes, then you are using named instances for the SQL 2005 installations. If you have named instances, not only do you need to enable remote connections, but you need to insure the SQL Browser service is running for connectivity to work properly.

This link has some more information about SQL Browser -- http://msdn2.microsoft.com/en-us/library/ms165724.aspx

Hope this helps,

Tom

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hey Tom,

I am using transactional replication. The pub/dist has both 2000 (default) and 2005 (named) instances.

The subscription is a push subscription. I will take a look at the link you sent.

=== Edited by William Lowers @. 24 Jan 2007 4:11 PM UTC===
After reading the article I check my pub/dist. It is set to listen on TCP port 1434 which I know works cause I had to have the Firewall openned to get to the server. The SQL Browser server appears to be running.

Is there anything I can do to ensure it is working?

|||

update

Since my subscriber is a dev server and if I mess it up it does not matter I did the following...

I created a publication on this server with itself being the distributer also.. that way it is setup just like the server with 2000 and 2005 on it. I then created a subscription from this server (2005 only) to the 2005 database on the 2000 and 2005 server.

No problem what so ever. So I am leaning towards the fact that it has to do with having both versions on one server...

Any idea? or is this a Bug?

|||

Can you try the following connectivity test from command prompt?

at your dev machine, try to connect to the other box

osql -S<publisher_server> -U<user id> -P<password>

and try the connect from publisher machine to your dev machine as well. If both work, you can rule out the protocal enabling issue. If it works only from your dev box to publisher machine, but not the other way around, it might be as simple as enabling TCP and name/pipe from configuration manager.

Gary

|||

Ok... I did as suggested and the publisher has no problem connecting to the subscriber...

So what does that mean?

I can connect via command line,SMS but not replication.

|||

Ok... So the above statment is only half true. After doing the above I thought about looking at my path cause it executed the osql from the default directory that I was openned to....

So when I execute from 80/tools/binn I can connect without issue.

From 90/tools/binn I get this error

[SQL Native Client]TCP Provider: No connection could be made because the
target machine actively refused it.
[SQL Native Client]Login timeout expired
[SQL Native Client]An error has occurred while establishing a connection to
the server. When connecting to SQL Server 2005, this failure may be caused by
the fact that under the default settings SQL Server does not allow remote
connections.

|||

Can you check the protocal interface are enabled for TCP and name pipe?

You can find the instruction on the following posting from Mahesh,

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1059301&SiteID=1

Gary

|||

I double checked and I already have that set. I cycled the server anyway... Still no luck.

What makes no sense to me is that I can connect with 2000 osql but not 2005 osql...

Is there anyway to check what libraries each client is using?

|||

What was your OS? If windows 2000 server, would install MDAC 2.7/2.8 help?

Thanks.

This posting is provided AS IS with no warranties, and confers no rights.

|||

OS is Windows 2003 SP1...

I am thinking what happened was the re-install of 2000 client tools that was done when we could not work on DTS...

I

|||I will guess that the re-applying of SQL 2000 bits after SQL 2005 was installed is what's causing the problem. Multiple versions of SQL Server installed on the same box is only supported provide you install the earlier version first, then the later version second. Re-installing the earlier version after the later version is already installed can mess things up. I suggest reinstalling/repairing your SQL 2005 installation.|||

That is what I am thinking but since the server is a major player in our production environment, I don't think I will be allowed to...

So I am asking my boss for other options...

Thanks for all the help.

Distribution of SQL Server 2005 DB with Full-Text Search

Using SQL Server 2005 Express (Advanced SP2) I have created a Full-Text Search application in VB for distribution on CD for single PCs. Works fine on my local machine during development.

Although the SQL Server 2005 Express edition can be distributed freely, it does not seem to support Full-Text searches in the distributed version. Is this true? Or am I missing something with my deployment?

If I need another version of Sql Server for distribution of a Full-Text Search app, how do I go about obtaining the proper DB and permission for distribution? The DB size is about 600 MB.

Oh yes, it does support those. YOu will have to install the fulltext search service with the setup and it should work then. If you are installing it using unattended setup file, make sure that you include the feature of fulltext search. There is a sample within the template file which shows how to do it.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hi Jens,

Great to hear that it does support full text search when distributed.

I have installed Sql Server Express 2005 (Adv. SP2) on three machines with full text search enabled during setup. So I think I am getting that part OK.I am not sure I know what you mean by, “There is a sample within the template file which shows how to do it.”

I am able to do full text searches in Management Studio but have never been able to do full-text searches in Visual Basic Express due to the limitation associated with user instance (“Cannot use full-text search in user instance.”)

I have been trying out Visual Studio 2008 Beta 2 and am able to do full-text search in Visual Basic when connecting directly to the DB attached to the server instance.But still get the same error when adding the Pubs DB to the Solution within VB that relies on the User Instance.

Here is a simple app that demonstrates the problem.The full-text search works with the connection to the server instance but not through the User Instance within VB.

Code Snippet

Imports System.Data

Imports System.Data.SqlClient

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim strSearch

strSearch = TextBox1.Text

'Added to Solution as existing item and User Instance – does not work

'Dim conn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\PUBS.MDF;Integrated Security=True;User Instance=True")

'Connected through Sql Server Express and this works!

Dim conn As New SqlConnection("Data Source=OFFICE\SQLEXPRESS;Initial Catalog=Pubs;Integrated Security=True")

Dim ds As New DataSet

conn.Open()

Dim adp As New SqlDataAdapter("Select * From Titles WHERE CONTAINS(Notes, ' """ & strSearch & """ ')", conn)

adp.Fill(ds)

DataGridView1.DataSource = ds.Tables(0)

conn.Close()

End Sub

End Class

I guess if I could distribute the program so that it does not rely on the User Instance during runtime, it would work.Just don’t know how to do that.

So I think I am close to getting the distribution issue resolved, but just don't know what to try next. Thanks for any help you can provide in working this out.

|||

OK, you did not mention that you are using user instances, this feature is NOT supported on a user instance as the error message tells you.

http://msdn2.microsoft.com/en-us/library/ms143684.aspx

|||

Thanks for the info - a bit discouraging but not entirely unexpected. I have been wrestling with this issue for awhile and had basically given up on doing full text search outside Management Studio. Then with the VS 2008 beta I have the option of connecting directly to the server and doing the FTS in Visual Basic.

So I am back to my original question of how to distribute a FTS program using SQL Server. Do I need another version of SQL Server that supports FTS in User Instances? Or some variation on this theme?

Or, is there a way to use SQL Server 2005 Express (Adv SP2) with VB in a way other than with User Instances?

The DB is a static, historical, read only archive so security is not an issue for this application.

I am assuming there must be a way to create a FTS program with SQL Server for distribution on a CD or DVD. If SQL Server Express will not do it, what other options do I have? Or is the problem more a matter of the limitation of User Instances within Visual Basic itself?

Thanks again for the info and any further guidance you can provide.

|||

Hi,

"So I am back to my original question of how to distribute a FTS program using SQL Server. Do I need another version of SQL Server that supports FTS in User Instances? Or some variation on this theme?"

No, the only version which supports user instances is Express. YOu probably will need a variation of the planned envrionment.

"

Or, is there a way to use SQL Server 2005 Express (Adv SP2) with VB in a way other than with User Instances?

The DB is a static, historical, read only archive so security is not an issue for this application.

"

You will need server instances. Install SQL Server Express on the computer and attach the database to the server instance, instead of just attaching it via a user instance. This can be either done using the sp_attachdb command or the Management GUI.|||

I believe that I have attached the DB to the server instance of SQL Server Express using the Management GUI. Otherwise I would not be able to do FTS in Managment Studio. I also think that is why I am able to do FTS on the DB within Visual Studio as attached to the server rather than through a User Instance which does not support FTS.

I am not clear on what happens when I deploy the application including SQL Server 2005 Express. When I have done it before I was able to do regular DB activities, but got he error message about not supporting FTS only when I tried to do the FTS. So I am able to deploy the DB and use it, but it must be in a User Instance which apparently will never support FTS.

Can SQL Server Express be deployed in a way that the distributed version does not have to rely on a User Instance? If not, I don't see how the Express version will ever be usable for FTS in a distributed application.

Thanks for your support in helping me sort this out.

|||

I have been trying to deploy the application with the DB attached to a Server Instance rather than User Instance. No luck yet. I have no problem running the FTS on a Server Instance in VB during development. So it seems to be a matter of getting it deployed properly.

Just so that I am clear as to what I am trying to do, am I correct in assuming that I can deploy Sql Server 2005 Express (Advanced SP2) with my Visual Basic app and it will support Full Text Search so long as the DB is attached to a server instance rather than user instance? Thus I just need to learn how to access the DB with a server instance in the distributed program. Do I understand you correctly on this?

Distribution of SQL Server 2005 DB with Full-Text Search

Using SQL Server 2005 Express (Advanced SP2) I have created a Full-Text Search application in VB for distribution on CD for single PCs. Works fine on my local machine during development.

Although the SQL Server 2005 Express edition can be distributed freely, it does not seem to support Full-Text searches in the distributed version. Is this true? Or am I missing something with my deployment?

If I need another version of Sql Server for distribution of a Full-Text Search app, how do I go about obtaining the proper DB and permission for distribution? The DB size is about 600 MB.

Oh yes, it does support those. YOu will have to install the fulltext search service with the setup and it should work then. If you are installing it using unattended setup file, make sure that you include the feature of fulltext search. There is a sample within the template file which shows how to do it.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hi Jens,

Great to hear that it does support full text search when distributed.

I have installed Sql Server Express 2005 (Adv. SP2) on three machines with full text search enabled during setup. So I think I am getting that part OK.I am not sure I know what you mean by, “There is a sample within the template file which shows how to do it.”

I am able to do full text searches in Management Studio but have never been able to do full-text searches in Visual Basic Express due to the limitation associated with user instance (“Cannot use full-text search in user instance.”)

I have been trying out Visual Studio 2008 Beta 2 and am able to do full-text search in Visual Basic when connecting directly to the DB attached to the server instance.But still get the same error when adding the Pubs DB to the Solution within VB that relies on the User Instance.

Here is a simple app that demonstrates the problem.The full-text search works with the connection to the server instance but not through the User Instance within VB.

Code Snippet

Imports System.Data

Imports System.Data.SqlClient

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim strSearch

strSearch = TextBox1.Text

'Added to Solution as existing item and User Instance – does not work

'Dim conn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\PUBS.MDF;Integrated Security=True;User Instance=True")

'Connected through Sql Server Express and this works!

Dim conn As New SqlConnection("Data Source=OFFICE\SQLEXPRESS;Initial Catalog=Pubs;Integrated Security=True")

Dim ds As New DataSet

conn.Open()

Dim adp As New SqlDataAdapter("Select * From Titles WHERE CONTAINS(Notes, ' """ & strSearch & """ ')", conn)

adp.Fill(ds)

DataGridView1.DataSource = ds.Tables(0)

conn.Close()

End Sub

End Class

I guess if I could distribute the program so that it does not rely on the User Instance during runtime, it would work.Just don’t know how to do that.

So I think I am close to getting the distribution issue resolved, but just don't know what to try next. Thanks for any help you can provide in working this out.

|||

OK, you did not mention that you are using user instances, this feature is NOT supported on a user instance as the error message tells you.

http://msdn2.microsoft.com/en-us/library/ms143684.aspx

|||

Thanks for the info - a bit discouraging but not entirely unexpected. I have been wrestling with this issue for awhile and had basically given up on doing full text search outside Management Studio. Then with the VS 2008 beta I have the option of connecting directly to the server and doing the FTS in Visual Basic.

So I am back to my original question of how to distribute a FTS program using SQL Server. Do I need another version of SQL Server that supports FTS in User Instances? Or some variation on this theme?

Or, is there a way to use SQL Server 2005 Express (Adv SP2) with VB in a way other than with User Instances?

The DB is a static, historical, read only archive so security is not an issue for this application.

I am assuming there must be a way to create a FTS program with SQL Server for distribution on a CD or DVD. If SQL Server Express will not do it, what other options do I have? Or is the problem more a matter of the limitation of User Instances within Visual Basic itself?

Thanks again for the info and any further guidance you can provide.

|||

Hi,

"So I am back to my original question of how to distribute a FTS program using SQL Server. Do I need another version of SQL Server that supports FTS in User Instances? Or some variation on this theme?"

No, the only version which supports user instances is Express. YOu probably will need a variation of the planned envrionment.

"

Or, is there a way to use SQL Server 2005 Express (Adv SP2) with VB in a way other than with User Instances?

The DB is a static, historical, read only archive so security is not an issue for this application.

"

You will need server instances. Install SQL Server Express on the computer and attach the database to the server instance, instead of just attaching it via a user instance. This can be either done using the sp_attachdb command or the Management GUI.|||

I believe that I have attached the DB to the server instance of SQL Server Express using the Management GUI. Otherwise I would not be able to do FTS in Managment Studio. I also think that is why I am able to do FTS on the DB within Visual Studio as attached to the server rather than through a User Instance which does not support FTS.

I am not clear on what happens when I deploy the application including SQL Server 2005 Express. When I have done it before I was able to do regular DB activities, but got he error message about not supporting FTS only when I tried to do the FTS. So I am able to deploy the DB and use it, but it must be in a User Instance which apparently will never support FTS.

Can SQL Server Express be deployed in a way that the distributed version does not have to rely on a User Instance? If not, I don't see how the Express version will ever be usable for FTS in a distributed application.

Thanks for your support in helping me sort this out.

|||

I have been trying to deploy the application with the DB attached to a Server Instance rather than User Instance. No luck yet. I have no problem running the FTS on a Server Instance in VB during development. So it seems to be a matter of getting it deployed properly.

Just so that I am clear as to what I am trying to do, am I correct in assuming that I can deploy Sql Server 2005 Express (Advanced SP2) with my Visual Basic app and it will support Full Text Search so long as the DB is attached to a server instance rather than user instance? Thus I just need to learn how to access the DB with a server instance in the distributed program. Do I understand you correctly on this?

Distribution of Application with SQL Server DB

If I create a Window's application that uses a MS Sql Server DB created with the express edition, do I need to get permission or submit royalties for distribution of my application? If so, where can I find the required info? I have been Googling without success and want to understand what is involved.

My projects are mostly for fun and my own amazement at this point, but I thinking about creating something that may actually become a product and need some guidance before I get much further along with it.

You may freely distribute SQL Server Express Edition.

See this link to register and for more details.

SQL Server 2005 Express Redistribution
http://www.microsoft.com/sql/editions/express/redistregister.mspx

The following may help you customize your own installer for SQL Server.

SQL Server 2005 UnAttended Installations
http://msdn2.microsoft.com/en-us/library/ms144259.aspx
http://msdn2.microsoft.com/en-us/library/bb264562.aspx
http://www.devx.com/dbzone/Article/31648

|||Thanks Arnie. Very encouraging info.

Distribution database of Transaction Replication publication being marked SUSPECT by recov

Hi experts there,
I have a Publication created for Transactional Replication. All the
while working fine.
Now it failed and I am not able to access to the database at all. It
shows the following error message:
Error 926: Database 'distribution' cannot be opened. It has been
marked SUSPECT by recovery. See the SQLServer error log for more
information.
Tried to detach the database but getting the following error: -
"The database cannot be detached while it is being replicated"
Tried running DBCC CheckDB but cannot run with database still in
suspect mode
Basically I cannot perform backup on it, cannot detach it, cannot even
drop the publication.
Tried also the following method:
1) sp_resetstatus DISTRIBUTION
Prior to updating sysdatabases entry for database 'DISTRIBUTION', mode
= 0 and status = 24 (status suspect_bit = 0).
No row in sysdatabases was updated because mode and status are already
correctly reset. No error and no changes made.
2) DBCC CHECKDB ('DISTRIBUTION', REPAIR_REBUILD) WITH ALL_ERRORMSGS
Server: Msg 926, Level 10, State 1, Line 1
Database 'distribution' cannot be opened. It has been marked SUSPECT
by recovery. See the SQL Server errorlog for more information.
Anybody know what cause all this and how to resolve it? Please
help!!!!!
I need to make the Replication running back soonest possible.
Thanks/TewI have seen this behavior once before. The only way we could get it out of
suspect mode was to directly update the sysdatabases table and set the
status to 32768 (emergency bypass). After recycling SQL Server we were then
able to run Checkdb. You might find corruption in the database. If not you
can set the status back to 0 and see if it will recover normally.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Distribution agent cannot find sp_MSupd_users

I created a Publication and Subscription. Once LogReader detected
transactions for replication. Distribution Agent status failed with error
"Cound not find stored procedure 'sp_MSupd_users'". I cannot find any KB on
this error, any suggestion?
Indeed, "users" is my table name
|||It seems it is caused by Command replacement on article defaults, when will
we use command replacement then?
|||Are you doing a nosync initialization? If so, you'll need to use
sp_scriptpublicationcustomprocs (please see
http://www.replicationanswers.com/No...alizations.asp).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

distributing a database and creating a new user

Hi!
I'm new to trans-SQL. I wounder how I can add an user to newly created
database?
I've created a window-application that is using a SQL-database for reading
and storing data.
When I now want to distribute my application I also need to distrubute my
database.
Is the best way of distributing a database through trans-SQL?
Or should I create a new database on my localhost and distribute a backup?
the code I've so far is...
USE master
GO
CREATE DATABASE myTestDB
ON
(NAME = myTestDB_dat,
FILENAME= 'c:\program files\microsoft sql server\mssql\data\myTestDBdat.mdf'
,
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5)
GO
I would like to have an user called "testuser" with password "test"
please help.
Thanks in advanced
Best regards
- Hans -
--
(Have fun programming with ... C#)1. you can add a user using the sp_adduser stored proc
sp_adduser [ @.loginame = ] 'login'
[ , [ @.name_in_db = ] 'user' ]
[ , [ @.grpname = ] 'group' ]
2. or you use an exisiting windows nt account using the so addlogin
sp_adduser [ @.loginame = ] 'login'
[ , [ @.name_in_db = ] 'user' ]
[ , [ @.grpname = ] 'group' ]
3. you can also add a "users" table which contains userinformations such
as userid, name, lastname,etc and the password which you can be hashed using
md5 or sha1 technology of the.net .
In this way you can easily add, remove user or changed their password.
you can make the application or a stored proc to verify if the password is
corerct.
in this approach you will be using a single account to connect to sql server
.
will it will depends on you computing needs
hope it helps
"Hans [DiaGraphIT]" wrote:

> Hi!
> I'm new to trans-SQL. I wounder how I can add an user to newly created
> database?
> I've created a window-application that is using a SQL-database for reading
> and storing data.
> When I now want to distribute my application I also need to distrubute my
> database.
> Is the best way of distributing a database through trans-SQL?
> Or should I create a new database on my localhost and distribute a backup?
> the code I've so far is...
> USE master
> GO
> CREATE DATABASE myTestDB
> ON
> (NAME = myTestDB_dat,
> FILENAME= 'c:\program files\microsoft sql server\mssql\data\myTestDBdat.md
f',
> SIZE = 10,
> MAXSIZE = 50,
> FILEGROWTH = 5)
> GO
> I would like to have an user called "testuser" with password "test"
> please help.
> Thanks in advanced
> --
> Best regards
> - Hans -
> --
> (Have fun programming with ... C#)|||sorry got a little messed up. let me correct my self
for sql user
1. sp_addlogin
Creates a new Microsoft? SQL Server? login that allows a user to connect
to
an instance of SQL Server using SQL Server Authentication.
Syntax
sp_addlogin [ @.loginame = ] 'login'
[ , [ @.passwd = ] 'password' ]
[ , [ @.defdb = ] 'database' ]
[ , [ @.deflanguage = ] 'language' ]
[ , [ @.sid = ] sid ]
[ , [ @.encryptopt = ] 'encryption_option' ]
2. sp_grantlogin for windows account
sp_grantlogin [@.loginame =] 'login'
3. to allow SQL users to use the database you can use
sp_adduser/sp_grantdbaccess
4. to allow Nt users you can use
sp_grantdbaccess
"jose g. de jesus jr mcp, mcdba" wrote:
> 1. you can add a user using the sp_adduser stored proc
> sp_adduser [ @.loginame = ] 'login'
> [ , [ @.name_in_db = ] 'user' ]
> [ , [ @.grpname = ] 'group' ]
> 2. or you use an exisiting windows nt account using the so addlogin
> sp_adduser [ @.loginame = ] 'login'
> [ , [ @.name_in_db = ] 'user' ]
> [ , [ @.grpname = ] 'group' ]
> 3. you can also add a "users" table which contains userinformations such
> as userid, name, lastname,etc and the password which you can be hashed usi
ng
> md5 or sha1 technology of the.net .
> In this way you can easily add, remove user or changed their passwo
rd.
> you can make the application or a stored proc to verify if the password is
> corerct.
> in this approach you will be using a single account to connect to sql serv
er.
> will it will depends on you computing needs
> hope it helps
>
> "Hans [DiaGraphIT]" wrote:
>|||this made sense... Ill try it out first thing tomorrow morning
thank you
Best regards
- Hans -
--
(Have fun programming with ... C#)
"jose g. de jesus jr mcp, mcdba" wrote:
> sorry got a little messed up. let me correct my self
> for sql user
> 1. sp_addlogin
> Creates a new Microsoft? SQL Server? login that allows a user to connec
t to
> an instance of SQL Server using SQL Server Authentication.
> Syntax
> sp_addlogin [ @.loginame = ] 'login'
> [ , [ @.passwd = ] 'password' ]
> [ , [ @.defdb = ] 'database' ]
> [ , [ @.deflanguage = ] 'language' ]
> [ , [ @.sid = ] sid ]
> [ , [ @.encryptopt = ] 'encryption_option' ]
> 2. sp_grantlogin for windows account
> sp_grantlogin [@.loginame =] 'login'
> 3. to allow SQL users to use the database you can use
> sp_adduser/sp_grantdbaccess
> 4. to allow Nt users you can use
> sp_grantdbaccess
>
> "jose g. de jesus jr mcp, mcdba" wrote:
>