exec(select...), how supress the output?

Hi
I have a dynamically constructed sql query that I want to execute, e.g.
exec('select * from ' + @.tablename)

(1) Can I suppress the output somehow if this returns no values?
(2) Can I use the result of this query in another query somehow? e.g.
select
(3) Can I control the size of the columns in the output somehow

Thanks
F(foldface@.yahoo.co.uk) writes:
> I have a dynamically constructed sql query that I want to execute, e.g.
> exec('select * from ' + @.tablename)
> (1) Can I suppress the output somehow if this returns no values?

EXEC ('IF EXISTS (SELECT * FROM ' + @.tablename ' + ') SELECT * FROM ' +
@.tablename)

> (2) Can I use the result of this query in another query somehow? e.g.
> select

INSERT #tmp (...)
EXEC('...')

> (3) Can I control the size of the columns in the output somehow

This question is unclear. Output columns from SQL Server does not
really have any size, but that is up to the client tool you use.

But if you are using Query Analyzer, and want some nice output there,
you can use convert(varchar(n), ...) where n is the size of your choice.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> > (1) Can I suppress the output somehow if this returns no values?
> EXEC ('IF EXISTS (SELECT * FROM ' + @.tablename ' + ') SELECT * FROM ' +
> @.tablename)

worked this out in the end but as I'm using google for news access from
work I couldn't reply to my own mail. Thanks anyway

> > (2) Can I use the result of this query in another query somehow? e.g.
> > select
> INSERT #tmp (...)
> EXEC('...')

sorry, don't understand this? Can you elaborate?

> > (3) Can I control the size of the columns in the output somehow
> But if you are using Query Analyzer, and want some nice output there,
> you can use convert(varchar(n), ...) where n is the size of your choice.

create table #TempTable (id int)
insert #TempTable Values (convert(varchar(50), 2))
select * from #TempTable

I know this is meant to convert values but what exactly should I be seeing
here? I am talking about Query Analyser|||(foldface@.yahoo.co.uk) writes:
>> > (2) Can I use the result of this query in another query somehow? e.g.
>> > select
>>
>> INSERT #tmp (...)
>> EXEC('...')
> sorry, don't understand this? Can you elaborate?

You can save the output from an EXEC() statement in a temp table, and
then use the temp table in the next query.

>> > (3) Can I control the size of the columns in the output somehow
>>
>> But if you are using Query Analyzer, and want some nice output there,
>> you can use convert(varchar(n), ...) where n is the size of your choice.
> create table #TempTable (id int)
> insert #TempTable Values (convert(varchar(50), 2))
> select * from #TempTable
> I know this is meant to convert values but what exactly should I be seeing
> here? I am talking about Query Analyser

I don't know exactly what you are trying to achieve, and you might be
better off if you explained more about your business requirements.

But the column width in QA depends on two things: the column name and the
data type. If you want control over the column width, all output columns
must be varchar. You cannot control the width of a float or an int column.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

exec proc premission

Hi
I created a user called development in sql server 2005 and make it a member
of datareader,datewriter and ddladmin role. now I created a proc under that
development account. however when I try to exec that proc, its give a
permission denied error. How come? it was able to create proc, but not
exec, its there something I have to do?
thanksPermission denied on what object? Does the proc manipulate some table,
view or proc that the development user does not have permissions to
manipulate? Are there explicit deny permissions for the development
user on some objects in the DB? Can you post actual code and actual
error messages?
*mike hodgson*
http://sqlnerd.blogspot.com
paulixml wrote:

>Hi
>I created a user called development in sql server 2005 and make it a member
>of datareader,datewriter and ddladmin role. now I created a proc under tha
t
>development account. however when I try to exec that proc, its give a
>permission denied error. How come? it was able to create proc, but not
>exec, its there something I have to do?
>thanks
>
>|||hi
here what I did, I create the prc connected as development,
create proc [dbo].[getpivot1_prc]as
select * from pivot_tmp
then I try to exec it after I create, I get this
Msg 229, Level 14, State 5, Procedure getpivot1_prc, Line 1
EXECUTE permission denied on object 'getpivot1_prc', database 'cap2005',
schema 'dbo'.
thanks
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message
news:uRc3wnoaGHA.1200@.TK2MSFTNGP03.phx.gbl...
> Permission denied on what object? Does the proc manipulate some table,
> view or proc that the development user does not have permissions to
> manipulate? Are there explicit deny permissions for the development
> user on some objects in the DB? Can you post actual code and actual
> error messages?
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> paulixml wrote:
>
>|||The db_ddladmin role allowed you to create the proc as a different user
(dbo) but since you explicitly stated the owner of that proc when you
created it, the development user is not the owner and therefore doesn't
have permissions to execute it. And being a memory of db_datareader,
db_datawriter & db_ddladmin does not imply execute permissions on that
new proc.
Either create the proc as development.getpivot1_prc (CREATE PROC
development.getpivot1_prc AS ...), which would make the development user
the owner of the proc and therefore imply execute permissions for the
development user, or explicitly grant execute permissions on that proc
to the development user or a role that that user is a member of (but you
can't change the permissions with the development user because
db_ddladmin does not allow you to run GRANT statements).
Why are you using the development user to create a proc owned by the dbo
user?
*mike hodgson*
http://sqlnerd.blogspot.com
paulixml wrote:

>hi
>here what I did, I create the prc connected as development,
>create proc [dbo].[getpivot1_prc]as
>select * from pivot_tmp
>then I try to exec it after I create, I get this
>Msg 229, Level 14, State 5, Procedure getpivot1_prc, Line 1
>EXECUTE permission denied on object 'getpivot1_prc', database 'cap2005',
>schema 'dbo'.
>thanks
>"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message
>news:uRc3wnoaGHA.1200@.TK2MSFTNGP03.phx.gbl...
>
>
>

Exception repeately occurring

Hi

I have a strange problem with my SQL server 2k instllation - every 10
mintutes when I have the Profiler trace with the entire "Errors" Event
category selected, the following 5 exceptions show up in the trace and
that too for the same SPID.

Error: 16955, Severity: 16, State: 2
Error: 16945, Severity: 16, State: 1
Error: 16955, Severity: 16, State: 2
Error: 16945, Severity: 16, State: 1
Error: 16955, Severity: 16, State: 2
Error: 16945, Severity: 16, State: 1

I have no clue why this is occurring - I tried running a trace with the
SP:StmtCompleted event on, but no other stored procedures show up with
the same spid close to the time where this exception is logged.

Does anyone have a clue as to why this error is occurring ?

RahulPondy (fd96121@.yahoo.com) writes:
> I have a strange problem with my SQL server 2k instllation - every 10
> mintutes when I have the Profiler trace with the entire "Errors" Event
> category selected, the following 5 exceptions show up in the trace and
> that too for the same SPID.
> Error: 16955, Severity: 16, State: 2
> Error: 16945, Severity: 16, State: 1
> Error: 16955, Severity: 16, State: 2
> Error: 16945, Severity: 16, State: 1
> Error: 16955, Severity: 16, State: 2
> Error: 16945, Severity: 16, State: 1
> I have no clue why this is occurring - I tried running a trace with the
> SP:StmtCompleted event on, but no other stored procedures show up with
> the same spid close to the time where this exception is logged.

So what you in a such situation like this is this:

select * from master..sysmessages where error in (16955, 16945)

You could also have looked up the errors in Books Online, by simply
searching for them. This could give you the bonus that there might be
entire topic to troubleshoot the problem. I would not expect that in
this case, though.

These are the messages:

16945 The cursor was not declared.
16955 Could not create an acceptable cursor.

I would guess that 16945 is a consequence of 16955.

Apparently there is some code out there where the cursor declaration
fails, and where there is no error handling, so that execution continues.
Note that this may not have to be a stored procedure. Hypothetically
it could be a server-side cursor initiated by some client API as well.

In any case, it's a problem specific to that process, and it is not that
your server is about to go belly-up.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Exception in xa_open with SqlServer 2000 SP3a on Win 2003

Hi!
I have an application server and try to access my SqlServer 2000
database running on Win 2003 using a two-phase commit connection
(datasource property enable2Phase=true). The JDBC driver is SP3.
I receive the following exception:
Caused by: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
JDBC][SQLServer]xa_open (0) returns -3
at com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown
Source)
at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown Source)
at
com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sErrorToken(Unknown
Source)
at
com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReplyToken(Unknown
Source)
at
com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.pro cessReplyToken(Unknown
Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReply(Unknown
Source)
at
com.microsoft.jdbc.sqlserver.SQLServerImplStatemen t.getNextResultType(Unknown
Source)
at
com.microsoft.jdbc.base.BaseStatement.commonTransi tionToState(Unknown
Source)
at com.microsoft.jdbc.base.BaseStatement.postImplExec ute(Unknown
Source)
at
com.microsoft.jdbc.base.BasePreparedStatement.post ImplExecute(Unknown
Source)
at com.microsoft.jdbc.base.BaseStatement.commonExecut e(Unknown Source)
at
com.microsoft.jdbc.base.BaseStatement.executeUpdat eInternal(Unknown Source)
at
com.microsoft.jdbc.base.BasePreparedStatement.exec uteUpdate(Unknown Source)
at
com.microsoft.jdbcx.sqlserver.SQLServerImplXAResou rce.executeXaRpc(Unknown
Source)
at
com.microsoft.jdbcx.sqlserver.SQLServerImplXAResou rce.executeXaRpc(Unknown
Source)
at
com.microsoft.jdbcx.sqlserver.SQLServerImplXAResou rce.open(Unknown Source)
at com.microsoft.jdbcx.base.BaseXAConnection.<init>(U nknown Source)
at
com.microsoft.jdbcx.base.BaseXADataSource.getXACon nection(Unknown Source)
at
com.microsoft.jdbcx.sqlserver.SQLServerDataSource. getXAConnection(Unknown
Source)
If I disable either the enable2Phase or use a SqlServer database on
Win2000 everything works fine.
Any ideas?
My JDBC driver reports the following:
Database product name : Microsoft SQL Server
Database product version : Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
JDBC driver name : SQLServer
JDBC driver version : 2.2.0040
David S. Faller wrote:

> Hi!
> I have an application server and try to access my SqlServer 2000
> database running on Win 2003 using a two-phase commit connection
> (datasource property enable2Phase=true). The JDBC driver is SP3.
> I receive the following exception:
> Caused by: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
> JDBC][SQLServer]xa_open (0) returns -3
Hi. That's not a driver issue, it is a failure of an ancillary ddl that
implements the extended stored procedures thet SQLServer uses for XA.
There have been recent fixes to those DLLs, so the first thing to do is to
upgrade the DBMS to the latest fixpack.
Joe Weinstein at BEA

> at com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown
> Source)
> at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown Source)
> at
> com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sErrorToken(Unknown
> Source)
> at
> com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReplyToken(Unknown
> Source)
> at
> com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.pro cessReplyToken(Unknown
> Source)
> at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReply(Unknown
> Source)
> at
> com.microsoft.jdbc.sqlserver.SQLServerImplStatemen t.getNextResultType(Unknown
> Source)
> at
> com.microsoft.jdbc.base.BaseStatement.commonTransi tionToState(Unknown
> Source)
> at com.microsoft.jdbc.base.BaseStatement.postImplExec ute(Unknown
> Source)
> at
> com.microsoft.jdbc.base.BasePreparedStatement.post ImplExecute(Unknown
> Source)
> at com.microsoft.jdbc.base.BaseStatement.commonExecut e(Unknown Source)
> at
> com.microsoft.jdbc.base.BaseStatement.executeUpdat eInternal(Unknown Source)
> at
> com.microsoft.jdbc.base.BasePreparedStatement.exec uteUpdate(Unknown Source)
> at
> com.microsoft.jdbcx.sqlserver.SQLServerImplXAResou rce.executeXaRpc(Unknown
> Source)
> at
> com.microsoft.jdbcx.sqlserver.SQLServerImplXAResou rce.executeXaRpc(Unknown
> Source)
> at
> com.microsoft.jdbcx.sqlserver.SQLServerImplXAResou rce.open(Unknown Source)
> at com.microsoft.jdbcx.base.BaseXAConnection.<init>(U nknown Source)
> at com.microsoft.jdbcx.base.BaseXADataSource.getXACon nection(Unknown
> Source)
> at
> com.microsoft.jdbcx.sqlserver.SQLServerDataSource. getXAConnection(Unknown
> Source)
> If I disable either the enable2Phase or use a SqlServer database on
> Win2000 everything works fine.
> Any ideas?
> My JDBC driver reports the following:
> Database product name : Microsoft SQL Server
> Database product version : Microsoft SQL Server 2000 - 8.00.760 (Intel
> X86)
> JDBC driver name : SQLServer
> JDBC driver version : 2.2.0040
|||Hi Joe,
Thanks for this information. I verified that the SQL Server has been
updated to SP3a and that the sqljdbc.dll in my SQL Server's binn
directory is the one shipped with the latest JDBC driver (SP3).
Still no luck...
Can you give me more details on which dll has to be updated and where to
get the update? The list of post-SP3 updates of SQL Server does not list
any XA related issues
(http://support.microsoft.com/common/...v2000presp4fix).
Thanks,
David Faller
Joe Weinstein wrote:
>
> David S. Faller wrote:
>
> Hi. That's not a driver issue, it is a failure of an ancillary ddl that
> implements the extended stored procedures thet SQLServer uses for XA.
> There have been recent fixes to those DLLs, so the first thing to do is to
> upgrade the DBMS to the latest fixpack.
> Joe Weinstein at BEA
>
>
|||Hi Joe,
Thanks for this information. I verified that the SQL Server has been
updated to SP3a and that the sqljdbc.dll in my SQL Server's binn
directory is the one shipped with the latest JDBC driver (SP3).
Still no luck...
Can you give me more details on which dll has to be updated and where to
get the update? The list of post-SP3 updates of SQL Server does not list
any XA related issues
(http://support.microsoft.com/common/...v2000presp4fix).
Thanks,
David Faller
Joe Weinstein wrote:
>
> David S. Faller wrote:
>
> Hi. That's not a driver issue, it is a failure of an ancillary ddl that
> implements the extended stored procedures thet SQLServer uses for XA.
> There have been recent fixes to those DLLs, so the first thing to do is to
> upgrade the DBMS to the latest fixpack.
> Joe Weinstein at BEA
>
>
|||David S. Faller wrote:

> Hi Joe,
> Thanks for this information. I verified that the SQL Server has been
> updated to SP3a and that the sqljdbc.dll in my SQL Server's binn
> directory is the one shipped with the latest JDBC driver (SP3).
> Still no luck...
> Can you give me more details on which dll has to be updated and where to
> get the update? The list of post-SP3 updates of SQL Server does not list
> any XA related issues
> (http://support.microsoft.com/common/...v2000presp4fix).
Hi. I am surprised that sp4 isn't out yet. I had been told it would have been.
I also have heqrd that MS will make no mention of the XA problem(s) until
the sp4 comes out, at which time it will be in the release notes.
Joe
[vbcol=seagreen]
>
> Thanks,
> David Faller
> Joe Weinstein wrote:
|||--
| Message-ID: <4151A8CE.20005@.bea.com>
| Date: Wed, 22 Sep 2004 09:31:10 -0700
| From: Joe Weinstein <joeNOSPAM@.bea.com>
| User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.4)
Gecko/20030624 Netscape/7.1 (ax)
| X-Accept-Language: en-us, en
| MIME-Version: 1.0
| To: "David S. Faller" <davidfaller@.de.ibm.com>
| Subject: Re: Exception in xa_open with SqlServer 2000 SP3a on Win 2003
| References: <#W9Gg$#nEHA.2096@.TK2MSFTNGP15.phx.gbl>
<415062D3.6080401@.bea.com> <OSVCflIoEHA.2340@.TK2MSFTNGP10.phx.gbl>
| In-Reply-To: <OSVCflIoEHA.2340@.TK2MSFTNGP10.phx.gbl>
| Content-Type: text/plain; charset=us-ascii; format=flowed
| Content-Transfer-Encoding: 7bit
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| NNTP-Posting-Host: sj-ez-63-96-169-77.bea.com 63.96.169.77
| Lines: 1
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTN GP09.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.jdbcdriver:6296
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
|
|
| David S. Faller wrote:
|
| > Hi Joe,
| >
| > Thanks for this information. I verified that the SQL Server has been
| > updated to SP3a and that the sqljdbc.dll in my SQL Server's binn
| > directory is the one shipped with the latest JDBC driver (SP3).
| > Still no luck...
| >
| > Can you give me more details on which dll has to be updated and where
to
| > get the update? The list of post-SP3 updates of SQL Server does not
list
| > any XA related issues
| >
(http://support.microsoft.com/common/...v2000presp4fix).
|
| Hi. I am surprised that sp4 isn't out yet. I had been told it would have
been.
| I also have heqrd that MS will make no mention of the XA problem(s) until
| the sp4 comes out, at which time it will be in the release notes.
| Joe
|
| >
| >
| > Thanks,
| > David Faller
| >
| > Joe Weinstein wrote:
| >
| >>
| >>
| >> David S. Faller wrote:
| >>
| >>> Hi!
| >>>
| >>> I have an application server and try to access my SqlServer 2000
| >>> database running on Win 2003 using a two-phase commit connection
| >>> (datasource property enable2Phase=true). The JDBC driver is SP3.
| >>>
| >>> I receive the following exception:
| >>>
| >>> Caused by: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver
| >>> for JDBC][SQLServer]xa_open (0) returns -3
| >>
| >>
| >>
| >> Hi. That's not a driver issue, it is a failure of an ancillary ddl that
| >> implements the extended stored procedures thet SQLServer uses for XA.
| >> There have been recent fixes to those DLLs, so the first thing to do
| >> is to
| >> upgrade the DBMS to the latest fixpack.
| >> Joe Weinstein at BEA
| >>
| >>> at com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown
| >>> Source)
| >>> at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown
| >>> Source)
| >>> at
| >>> com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sErrorToken(Unknown
| >>> Source)
| >>> at
| >>> com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReplyToken(Unknown
| >>> Source)
| >>> at
| >>>
com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.pro cessReplyToken(Unknown
| >>> Source)
| >>> at
| >>> com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReply(Unknown
Source)
| >>> at
| >>>
com.microsoft.jdbc.sqlserver.SQLServerImplStatemen t.getNextResultType(Unknow
n
| >>> Source)
| >>> at
| >>> com.microsoft.jdbc.base.BaseStatement.commonTransi tionToState(Unknown
| >>> Source)
| >>> at com.microsoft.jdbc.base.BaseStatement.postImplExec ute(Unknown
| >>> Source)
| >>> at
| >>> com.microsoft.jdbc.base.BasePreparedStatement.post ImplExecute(Unknown
| >>> Source)
| >>> at com.microsoft.jdbc.base.BaseStatement.commonExecut e(Unknown
| >>> Source)
| >>> at
| >>> com.microsoft.jdbc.base.BaseStatement.executeUpdat eInternal(Unknown
| >>> Source)
| >>> at
| >>> com.microsoft.jdbc.base.BasePreparedStatement.exec uteUpdate(Unknown
| >>> Source)
| >>> at
| >>>
com.microsoft.jdbcx.sqlserver.SQLServerImplXAResou rce.executeXaRpc(Unknown
| >>> Source)
| >>> at
| >>>
com.microsoft.jdbcx.sqlserver.SQLServerImplXAResou rce.executeXaRpc(Unknown
| >>> Source)
| >>> at
| >>> com.microsoft.jdbcx.sqlserver.SQLServerImplXAResou rce.open(Unknown
| >>> Source)
| >>> at com.microsoft.jdbcx.base.BaseXAConnection.<init>(U nknown
Source)
| >>> at
| >>> com.microsoft.jdbcx.base.BaseXADataSource.getXACon nection(Unknown
| >>> Source)
| >>> at
| >>>
com.microsoft.jdbcx.sqlserver.SQLServerDataSource. getXAConnection(Unknown
| >>> Source)
| >>>
| >>> If I disable either the enable2Phase or use a SqlServer database on
| >>> Win2000 everything works fine.
| >>>
| >>> Any ideas?
| >>>
| >>> My JDBC driver reports the following:
| >>> Database product name : Microsoft SQL Server
| >>> Database product version : Microsoft SQL Server 2000 - 8.00.760
| >>> (Intel X86)
| >>> JDBC driver name : SQLServer
| >>> JDBC driver version : 2.2.0040
| >>
| >>
| >>
|
|
We have a Knowledge Base article that describes some additional changes
that are required for XA transaction support on Windows Server 2003:
817066 INFO: Registry Entries Are Required for XA Transaction Support
http://support.microsoft.com/?id=817066
Please review this document and see if this resolves the issue.
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
|||Carb Simien [MSFT] wrote:
> We have a Knowledge Base article that describes some additional
> changes
> that are required for XA transaction support on Windows Server 2003:
> 817066 INFO: Registry Entries Are Required for XA Transaction
> Support
> http://support.microsoft.com/?id=817066
> Please review this document and see if this resolves the issue.
> Carb Simien, MCSE MCDBA MCAD
> Microsoft Developer Support - Web Data
>
I tried adding a registry entry for sqlsrv32.dll but I still get
"[SQLServer]xa_open (0) returns -3" in Java and "xa_open_entry failed"
in sqljdbc.log. Are there other DLLs I'm supposed to add? How do we
find out which DLLs need registry entries?
memebag
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message400191.html

Exception : Create Database failed 'tmpDb'

Hi

I'm getting Create Database failed 'tmpdb'

when I'm executing fallowing code

ServerConnection scon = new ServerConnection("local", "sa", "");

Server GSer=new Server(scon);

Database db2 = new Database(GSer, "xyz");

GSer.ConnectionContext.SqlExecutionModes = SqlExecutionModes.ExecuteSql;

db2.Create();

Can Anybody help me in this?

Thanks in advance

hi,

there should be an inner exception stating what the actual problem is. if you can′t find any more information, try to use the capture mode instead of the execution mode and execute the script in the SSMS Query Window. You will get a mote detailed explanation in here.

HTH, Jens K. Suessmeyer.

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

Thanks jens

can I create database object in C# code.

|||

Its not comming now with

GSer.ConnectionContext.SqlExecutionModes = SqlExecutionModes.ExecuteSql;

getting "ExecuteNonQuery failed for Database 'xyz'.

error when I'm trying to execute scripts on db like

StreamReader sw=new StreamReader("C:\\script.sql");

string script = sw.ReadToEnd();

db2.ExecuteNonQuery(script);

What the error in this

|||

I am using SMO to create a database with this code

StreamReader sr = new StreamReader(@."C:\GestMed\Bases\Create_GestMed_Prod.sql");

string script = sr.ReadToEnd();

script = script.Replace("GestMed_Prod",nombase);

ComCr?erSite.Serveur.ConnectionContext.Connect();

try

{

ComCr?erSite.Serveur.Databases["master"].ExecuteNonQuery(script);

str = " Base " + nombase + " : cr?ation OK";

Ecrire_Journal(sw,str);

}

catch (Exception e)

{

str = " Erreur Cr?ate Base " + nombase;

Ecrire_Journal(sw,str);

str = " Message : " + e.Message;

sw.WriteLine(sw);

SuiviTxt.Text += str + nl;

if ( e.InnerException != null )

{

str = " InnerException : " + e.InnerException.Message;

sw.WriteLine(str);

sw.Flush();

SuiviTxt.Text += str + nl;

}

sw.Flush();

ComCr?erSite.Cr?? = false;

this.Refresh();

return false;;

}

ComCr?erSite.Serveur.Refresh();

That's working

Maybe you have forgotten to connect to the server

I am using a generic script where i'm able to choose the name of the database

GestMed_Prod ==> GestMedSite0Prod for example

I had some problems with SMO it's why i decided to build an installation textfile where i keep all messages Exception.Message and InnerException.Message

I hopthat it will be useful for you and excuse me for my poor english

Have a good day

|||Hi,

sorry for not keeping up quicker. You should use the capture mode to see the commands which are generated by the SMO framework:

SqlExecutionModes.CaptureSql

After catching the string[] of command you can see which commands are causing the errors.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

I guess you are trying to create a database on local server.

If that is the case please use (local) or . instead of local (notice paranthesis).

Ex:Microsoft.SqlServer.Management.Common.ServerConnection scon = new Microsoft.SqlServer.Management.Common.ServerConnection("(local)", "sa", "");

I executed your code with just this change and it worked fine for me. Hope this helps.

Thanks,

Sravanthi.

|||

using the above code

i write dingdong.sql which is following

create database dingdong
go

use [dingdong]
go

create table test(name varchar(20));
go

create table test1(father varchar(21));
go

database created but table did not created, what is the reason

Thanks

|||

Sorry my problem solved,by using following code

StreamReader sr = new StreamReader(@."C:\jehan.sql");

string script = sr.ReadToEnd();

textBox1.Text = script;

Microsoft.SqlServer.Management.Smo.Server srvSql;

Microsoft.SqlServer.Management.Common.ServerConnection srvConn = new Microsoft.SqlServer.Management.Common.ServerConnection

(Global.ConnectionToDatabase1());

srvSql = new Server(srvConn);

srvSql.Databases["master"].ExecuteNonQuery(script);

Global.con1.Close();

Excel to Sql Server

Hi

I am trying to upload a excel spreadsheet using a web application application into a sql server database.

Basically I'm tryign to code a a upload button, that takes the excel spreadsheet and inserts it into a table in the database.

I have been lookign for code examples but cannot find out, and I am really struggling...any help would be greatly appreciated. I am trying to do this using asp.net (vb).

ThanksI'm not an app developer but this may help (or might not :))

http://www.sqldev.net/dts/DotNETCookBook.htm

excel source with optional columns

Hi:

I use a SSIS package to loop thro a folder and load data from multiple excel files to a SQL2005 table. Works fine except when an excel has a missing col.

Col names in xls are always a subset of col names in the table. The missing cols are random, else I would just have made another package:-)

Once a missing column is found, I get runtime and design time errors, and metadata problems. How can a get SSIS to ignore missing columns?

TIA

I recently solved this problem using a dynamically built select statement. Is it always just 1 column that's missing or do you need to load a dynamic number of columns? If it's a truly dynamic then the algorithm is a little more complex...|||

Thanks for your response. Request you tell me more aboout it.

I did the whole thing in BIDS in a SSIS project, using a ForEach container, a Excel Source and an OleDB destination. I was hoping to achieve my objectives with these objects and their settings :-).

|||I used a For Each Loop and then a For Loop to solve this problem.

The first For Each Loop iterates threw the columns names in the spreadsheet. It contains a script component that counts the columns storing the result in a variable. There might be a more efficient way to count columns but I couldn't figure out how.

The second For Loop container uses this counter variable to select and load each column one at a time. It contains 2 components; a script component that builds a select statement and a data flow task that actually moves the data using the select statement.

Here is the script code that dynamically builds each select statement:

Public Sub Main()
Dim SelectCommand As String
Dim WorksheetName As String
Dim ColumnLoopIndex As Integer
WorksheetName = Dts.Variables("WorksheetName").Value.ToString
ColumnLoopIndex = CInt(Dts.Variables("ColumnLoopIndex").Value)
SelectCommand = "Select F" & ColumnLoopIndex.ToString & " AS CurrentColumn from [" & WorksheetName & "]"
Dts.Variables("SelectCommand").Value = SelectCommand
Dts.TaskResult = Dts.Results.Success
End Sub

Please note: Depending on your data and how dynamic you want the

package to be you could skip the second For Loop and build a single select

statement that loads all of the columns. In this case your dynamically built select

statement would contain return fields like "SELECT F1, F2, NULL AS F3, NULL AS F4

FROM [myworksheetname]" to account for missing F3 and F4 columns.

Excel Source dt_ntext problems

Hi:

I import data from multiple excel files into SQL DB. I have trouble with fields that could contain >255 chars.

If I have the col type = DT_Ntext in my Data Flow, the package fails for files that do not have any values >255 chars.

If I have the external coltype=dt_wstr and the output coltype=dt_wstr(4000) the package fails if the file contains any value >255 chars.(Implicit conversion does not occur, as expected).

I worked around by adding a dummy first row with >255 chars.

Is there a way to use a cast function to solve this prob? I tried using Select dt_ntext(fieldname) from Sheet1$, but that does not work.

Is there some clean way to get around this problem?

TIA
Kar

Hi Karfast

For now this have to be done manually. I was able successfully execute getting advise from Bob Bojanic

Follw these instructions carefully:

1) Go to Mapping option at Destination, Point your mouse for each of row having your above problem and its corresponding destination (note the Datatypes types and Lengths)

2) Open Advanced Editor for the Excel Source, Under Amend the Types and Lengths accordingly for all the columns in destination to match your source Columns' type and length

Thanks

Subhash Subramanyam

|||

Metadata of Excel sheets is often hard to deal with. The dummy first row seems as a clever solution to me. It is not elegant but the entire JET provider is far from that.

Select dt_ntext(fieldname) is not going to work as dt_ntext can be recognized only by SSIS and not by the JET provider. The more adequate query would be select LongText(fieldname), but I have no idea if JET can deal with it (don't have a way to try it at the moment).

HTH.

|||

Thanks, Bob.

I tried:

Select LongText(Fieldname) , and also tried out Memo etc. All these give a Undefined Function error.

I cant even find any help on this. There is apparently something called a Jet SQL Reference, but I couldnt find it. Not even in Access 2000 Local Help.

So I guess the dirty workaround is the only way :-(

Kar

Excel MODE function - value that occurs the most

Hi

I am looking for an implementation of the EXCEL MODE function for Analysis Services calculation. MEDIAN is available as integrated function, but MODE is missing.

Has someone of you an MDX implementation for this ? (for an Sum Measure) (- I am concerned about the performance if I do this over >50.000 Member Sets manually.)

Best Regards

HANNES

I have programmed my one procedure the do the job.

If you are interested the code is available at http://www.hmayer.net/tiki-list_file_gallery.php?galleryId=24

HANNES

|||

Interesting problem, Hannes. It is possible in MDX - here's a solution:

Code Snippet

with

member measures.countsame as

count(

filter(union([Date].[Calendar].currentmember.level.members, {[Date].[Calendar].currentmember} as currentmonth)

, ([Date].[Calendar].currentmember, [Measures].[Customer Count])

= (currentmonth.item(0).item(0), [Measures].[Customer Count])

)

)

member measures.mode as

(topcount([Date].[Calendar].[Month].members,1, measures.countsame).item(0).item(0), [Measures].[Customer Count])

select {[Measures].[Customer Count], measures.countsame, measures.mode} on 0,

[Date].[Calendar].[Month].members on 1

from [Adventure Works]

Although I suspect there might be a more a efficient way of doing it in MDX (I need to think a bit), custom code may well give you the best performance. If you could test this against your procedure I'd be interested to hear the result!

Regards,

Chris

Excel MODE function - value that occurs the most

Hi

I am looking for an implementation of the EXCEL MODE function for Analysis Services calculation. MEDIAN is available as integrated function, but MODE is missing.

Has someone of you an MDX implementation for this ? (for an Sum Measure) (- I am concerned about the performance if I do this over >50.000 Member Sets manually.)

Best Regards

HANNES

I have programmed my one procedure the do the job.

If you are interested the code is available at http://www.hmayer.net/tiki-list_file_gallery.php?galleryId=24

HANNES

|||

Interesting problem, Hannes. It is possible in MDX - here's a solution:

Code Snippet

with

member measures.countsame as

count(

filter(union([Date].[Calendar].currentmember.level.members, {[Date].[Calendar].currentmember} as currentmonth)

, ([Date].[Calendar].currentmember, [Measures].[Customer Count])

= (currentmonth.item(0).item(0), [Measures].[Customer Count])

)

)

member measures.mode as

(topcount([Date].[Calendar].[Month].members,1, measures.countsame).item(0).item(0), [Measures].[Customer Count])

select {[Measures].[Customer Count], measures.countsame, measures.mode} on 0,

[Date].[Calendar].[Month].members on 1

from [Adventure Works]

Although I suspect there might be a more a efficient way of doing it in MDX (I need to think a bit), custom code may well give you the best performance. If you could test this against your procedure I'd be interested to hear the result!

Regards,

Chris