Getting a SQL Express Database to work on a SQL Server

Hi,

I'm getting ready to deploy an ASP.NET application to a server that does not support SQL Express, but does support MS Access and SQL Server. Is there any easy way to convert my SQL Express code to either Access or regular SQL Server code, without having to change very much code in my application. I really like the integration that SQL Express has with Visual Web Developer, and would like to be able to keep that sort of integration if at all possible.

Thanks,
Drew

Assuming by "SQL Server" you mean "SQL Server 2005", the only thing that should need to change is the connection string. The rest of your code should be able to remain the same.|||

Try this blog and if it works let me know

http://weblogs.asp.net/scottgu/archive/2005/08/25/423703.aspx

sql

Getting a Major error.....

When i try to launch SQL 2005, I'm all of a sudden getting the following error:
"SqlWb.exe - Application Error
The application failed to initialize properly (0xc0000034). Click on OK to
terminate the application."
Any suggestions or recommendations? Tried to search the knowledgebase, but,
couldn't find anything.
Thanks.
JJ
Maybe I am wrong but looks like SqlWb is Management Studio so perhaps SQL
Server is really running. Try connecting from another client like Query
Analyzer to see if the instance is running.
With no additional information perhaps what I would try is rebooting the
computer and, if the problem is still there, reinstalling the client tools.
Regards,
Ben Nevarez
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:658463FC-52F7-493E-9FF7-7DA363D12681@.microsoft.com...
> When i try to launch SQL 2005, I'm all of a sudden getting the following
> error:
> "SqlWb.exe - Application Error
> The application failed to initialize properly (0xc0000034). Click on OK to
> terminate the application."
> Any suggestions or recommendations? Tried to search the knowledgebase,
> but,
> couldn't find anything.
> Thanks.
> --
> JJ

Getting a Major error.....

When i try to launch SQL 2005, I'm all of a sudden getting the following error:
"SqlWb.exe - Application Error
The application failed to initialize properly (0xc0000034). Click on OK to
terminate the application."
Any suggestions or recommendations? Tried to search the knowledgebase, but,
couldn't find anything.
Thanks.
--
JJMaybe I am wrong but looks like SqlWb is Management Studio so perhaps SQL
Server is really running. Try connecting from another client like Query
Analyzer to see if the instance is running.
With no additional information perhaps what I would try is rebooting the
computer and, if the problem is still there, reinstalling the client tools.
Regards,
Ben Nevarez
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:658463FC-52F7-493E-9FF7-7DA363D12681@.microsoft.com...
> When i try to launch SQL 2005, I'm all of a sudden getting the following
> error:
> "SqlWb.exe - Application Error
> The application failed to initialize properly (0xc0000034). Click on OK to
> terminate the application."
> Any suggestions or recommendations? Tried to search the knowledgebase,
> but,
> couldn't find anything.
> Thanks.
> --
> JJ

Getting a Major error.....

When i try to launch SQL 2005, I'm all of a sudden getting the following err
or:
"SqlWb.exe - Application Error
The application failed to initialize properly (0xc0000034). Click on OK to
terminate the application."
Any suggestions or recommendations? Tried to search the knowledgebase, but,
couldn't find anything.
Thanks.
JJMaybe I am wrong but looks like SqlWb is Management Studio so perhaps SQL
Server is really running. Try connecting from another client like Query
Analyzer to see if the instance is running.
With no additional information perhaps what I would try is rebooting the
computer and, if the problem is still there, reinstalling the client tools.
Regards,
Ben Nevarez
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:658463FC-52F7-493E-9FF7-7DA363D12681@.microsoft.com...
> When i try to launch SQL 2005, I'm all of a sudden getting the following
> error:
> "SqlWb.exe - Application Error
> The application failed to initialize properly (0xc0000034). Click on OK to
> terminate the application."
> Any suggestions or recommendations? Tried to search the knowledgebase,
> but,
> couldn't find anything.
> Thanks.
> --
> JJsql

Getting a cached report on subsequent previews

I have and application that creates and publishes reports to the Reporting
server. I allow the user to preview the report. If the user creates a
report named 'Test Report', for example, and preview it the first time - all
is fine. If the user makes small changes to the report (say reordering the
ORDER BY in the SQL statement) and keeps the same report name, then
publishes and previews, the report reflecting the latest changes isn't
displayed. The first report is displayed. The user has to hit the refresh
button on the reporting toolbar to get the recently updated report. It's
like I'm getting a cached report because the changes to the report where
minimal. Any way to get around this'
Thx
AdrianAdrian,
I believe there are caching option settings in the System Settings page. You
may want to review this to see if there is an option to fix your issue.
- peteZ
"adrianM" <no_spam@.no_email.org> wrote in message
news:%23Gjq9OVtEHA.904@.TK2MSFTNGP11.phx.gbl...
>I have and application that creates and publishes reports to the Reporting
> server. I allow the user to preview the report. If the user creates a
> report named 'Test Report', for example, and preview it the first time -
> all
> is fine. If the user makes small changes to the report (say reordering
> the
> ORDER BY in the SQL statement) and keeps the same report name, then
> publishes and previews, the report reflecting the latest changes isn't
> displayed. The first report is displayed. The user has to hit the
> refresh
> button on the reporting toolbar to get the recently updated report. It's
> like I'm getting a cached report because the changes to the report where
> minimal. Any way to get around this'
> Thx
> Adrian
>|||For the benefit of the group, in order to avoid the issue mentioned in my
original post you have to add the [rs:ClearSession=true] to the URL.
Adrian

Getting 401 Access denied w/ Endpoint in SQL 2005 from C# client

I have a Windows Forms application in C# (VS.NET 2005) that accesses SQL 2005
web services using integrated authentication.
In development (Windows XP Pro, SQL 2005 Dev) the SQL servers are on the
same workstations as the client and connectivity works fine.
In testing, the windows app is begin deployed to a Windows 2000 Server
workstation and SQL Server 2005 is installed on a Windows 2003 Server. Both
are on the same domain. The user logged into the client station has admin
access on the server and dbo access (and in this case created the endpoints).
However, when the client attempts to access the server, 401 Access Denied is
returned.
The server specifies Integrated Authentication as follows:
CREATE ENDPOINT RMS
STATE = STARTED
AS HTTP (
SITE = 'SERVER',
PATH = '/sql/app1',
AUTHENTICATION = (INTEGRATED),
PORTS=(CLEAR)
)
The client adds the credentials to the cache before the call:
ws.Credentials =
System.Net.CredentialCache.DefaultCredentials;
I think I've covered everything here and from what I have read, the error
indicates that the user is not being authenticated (vs an issue with any
specific SQL object permissions).
Any ideas on what might be happening here and how to fix?
Thanks
-Luther
Hi Luther,
welcome to MSDN newsgroup.
As for the SQL2005's endpoint SOAP webservice, after you created the
endpoint, have you tried using IE browser to view the WSDL document of that
endpoint service? e.g:
http://servername/sql/test_endpoint?WSDL
If this also not work, the problem is likely due to the serverside
setting... If that works, we may need to check the client side code...
Also, you can print out the thread's current secuirty principal to see
whether it is the correct domain account of the server machine ...
In addition , you can also try adding the "Authorizing" setting to grant
certain user/role the permission as below:
AUTHORIZATION sa -- with optional authorization for owner
=======================
CREATE ENDPOINT zipcodes -- create the Http Endpoint
AUTHORIZATION sa -- with optional authorization for owner
STATE = STARTED -- the state of the endpoint
AS HTTP ( -- can be http, tcp, ...
path='/sql/zipcodes', -- the virtual path
AUTHENTICATION=(INTEGRATED), -- type of authentication
PORTS=(CLEAR), -- which ports (clear=all)
SITE ='*' -- site can be '*" or 'myserver'
)
FOR SOAP( -- type of protocol, in this case, SOAP for a webservice
WEBMETHOD 'http://tempuri.org/'.'DistanceBetweenzZipCodes'
-- define the webmethod(s) and fully qualified sproc
(name='zipcodes.dbo.DistanceBetweenZipcodes',
SCHEMA = STANDARD ),
======================
If there're any other findings, please feel free to post here.
Regards,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
| Thread-Topic: Getting 401 Access denied w/ Endpoint in SQL 2005 from C#
client
| thread-index: AcYMnwmiqttQJ9quQhelBHxCetS8Eg==
| X-WBNR-Posting-Host: 63.211.139.67
| From: "=?Utf-8?B?THV0aGVyIE1pbGxlcg==?=" <lex3001@.community.nospam>
| Subject: Getting 401 Access denied w/ Endpoint in SQL 2005 from C# client
| Date: Thu, 29 Dec 2005 09:41:02 -0800
| Lines: 39
| Message-ID: <910F2F43-FF93-4110-A162-2C04A46F4451@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.connect
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGXA03.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.connect:46496
| X-Tomcat-NG: microsoft.public.sqlserver.connect
|
| I have a Windows Forms application in C# (VS.NET 2005) that accesses SQL
2005
| web services using integrated authentication.
|
| In development (Windows XP Pro, SQL 2005 Dev) the SQL servers are on the
| same workstations as the client and connectivity works fine.
|
| In testing, the windows app is begin deployed to a Windows 2000 Server
| workstation and SQL Server 2005 is installed on a Windows 2003 Server.
Both
| are on the same domain. The user logged into the client station has admin
| access on the server and dbo access (and in this case created the
endpoints).
|
| However, when the client attempts to access the server, 401 Access Denied
is
| returned.
|
| The server specifies Integrated Authentication as follows:
|
| CREATE ENDPOINT RMS
| STATE = STARTED
| AS HTTP (
| SITE = 'SERVER',
| PATH = '/sql/app1',
| AUTHENTICATION = (INTEGRATED),
| PORTS=(CLEAR)
| )
|
| The client adds the credentials to the cache before the call:
|
| ws.Credentials =
| System.Net.CredentialCache.DefaultCredentials;
|
| I think I've covered everything here and from what I have read, the error
| indicates that the user is not being authenticated (vs an issue with any
| specific SQL object permissions).
|
| Any ideas on what might be happening here and how to fix?
|
| Thanks
| -Luther
|
|

Getting 401 Access denied w/ Endpoint in SQL 2005 from C# client

I have a Windows Forms application in C# (VS.NET 2005) that accesses SQL 200
5
web services using integrated authentication.
In development (Windows XP Pro, SQL 2005 Dev) the SQL servers are on the
same workstations as the client and connectivity works fine.
In testing, the windows app is begin deployed to a Windows 2000 Server
workstation and SQL Server 2005 is installed on a Windows 2003 Server. Both
are on the same domain. The user logged into the client station has admin
access on the server and dbo access (and in this case created the endpoints)
.
However, when the client attempts to access the server, 401 Access Denied is
returned.
The server specifies Integrated Authentication as follows:
CREATE ENDPOINT RMS
STATE = STARTED
AS HTTP (
SITE = 'SERVER',
PATH = '/sql/app1',
AUTHENTICATION = (INTEGRATED),
PORTS=(CLEAR)
)
The client adds the credentials to the cache before the call:
ws.Credentials =
System.Net.CredentialCache.DefaultCredentials;
I think I've covered everything here and from what I have read, the error
indicates that the user is not being authenticated (vs an issue with any
specific SQL object permissions).
Any ideas on what might be happening here and how to fix?
Thanks
-LutherHi Luther,
welcome to MSDN newsgroup.
As for the SQL2005's endpoint SOAP webservice, after you created the
endpoint, have you tried using IE browser to view the WSDL document of that
endpoint service? e.g:
http://servername/sql/test_endpoint?WSDL
If this also not work, the problem is likely due to the serverside
setting... If that works, we may need to check the client side code...
Also, you can print out the thread's current secuirty principal to see
whether it is the correct domain account of the server machine ...
In addition , you can also try adding the "Authorizing" setting to grant
certain user/role the permission as below:
AUTHORIZATION sa -- with optional authorization for owner
=======================
CREATE ENDPOINT zipcodes -- create the Http Endpoint
AUTHORIZATION sa -- with optional authorization for owner
STATE = STARTED -- the state of the endpoint
AS HTTP ( -- can be http, tcp, ...
path='/sql/zipcodes', -- the virtual path
AUTHENTICATION=(INTEGRATED), -- type of authentication
PORTS=(CLEAR), -- which ports (clear=all)
SITE ='*' -- site can be '*" or 'myserver'
)
FOR SOAP( -- type of protocol, in this case, SOAP for a webservice
WEBMETHOD 'http://tempuri.org/'.'DistanceBetweenzZipCodes'
-- define the webmethod(s) and fully qualified sproc
(name='zipcodes.dbo.DistanceBetweenZipcodes',
SCHEMA = STANDARD ),
======================
If there're any other findings, please feel free to post here.
Regards,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
| Thread-Topic: Getting 401 Access denied w/ Endpoint in SQL 2005 from C#
client
| thread-index: AcYMnwmiqttQJ9quQhelBHxCetS8Eg==
| X-WBNR-Posting-Host: 63.211.139.67
| From: "examnotes" <lex3001@.community.nospam>
| Subject: Getting 401 Access denied w/ Endpoint in SQL 2005 from C# client
| Date: Thu, 29 Dec 2005 09:41:02 -0800
| Lines: 39
| Message-ID: <910F2F43-FF93-4110-A162-2C04A46F4451@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.connect
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.connect:46496
| X-Tomcat-NG: microsoft.public.sqlserver.connect
|
| I have a Windows Forms application in C# (VS.NET 2005) that accesses SQL
2005
| web services using integrated authentication.
|
| In development (Windows XP Pro, SQL 2005 Dev) the SQL servers are on the
| same workstations as the client and connectivity works fine.
|
| In testing, the windows app is begin deployed to a Windows 2000 Server
| workstation and SQL Server 2005 is installed on a Windows 2003 Server.
Both
| are on the same domain. The user logged into the client station has admin
| access on the server and dbo access (and in this case created the
endpoints).
|
| However, when the client attempts to access the server, 401 Access Denied
is
| returned.
|
| The server specifies Integrated Authentication as follows:
|
| CREATE ENDPOINT RMS
| STATE = STARTED
| AS HTTP (
| SITE = 'SERVER',
| PATH = '/sql/app1',
| AUTHENTICATION = (INTEGRATED),
| PORTS=(CLEAR)
| )
|
| The client adds the credentials to the cache before the call:
|
| ws.Credentials =
| System.Net.CredentialCache.DefaultCredentials;
|
| I think I've covered everything here and from what I have read, the error
| indicates that the user is not being authenticated (vs an issue with any
| specific SQL object permissions).
|
| Any ideas on what might be happening here and how to fix?
|
| Thanks
| -Luther
|
|

geting timeout error in application when inserting record

i currently have more tha 3 million of records in my table in sql 7. i am getting timeout error in my web application when i try to insert a record in that table
what could be rhe reason for this? how to avoid this type of problem?
Thanks in advanceis it a single record insert or an insert select? how many indices do you have on the table? have you run a trace and looked at the duration column for the steps in the process? Do you want to post your code?sql

GETDATE() to insert into a datetime field

Hi there, may seem like a really silly question, but i am new to SQL.
I'm building as ASP application in Dreamweaver MX2004 i'm using the insert
record behaviour to upload information to the SQL Server. one of the fields
is DateRegisterred which in Dreamweaver is a hiddenfield with the value set
to GETDATE() called datereg so on the upload behaviour Dreamweaver should
upload GETDATE() from datereg to thedatetimefield DateRegisterred on the SQL
server. However on trying this i get the following error message -
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting
datetime from character string.
/driverjobs/candregistration.asp, line 161
Please any advise as how to fix this would be really really appreciated.
Many thanks
GarethHi
It looks like your field is being taken as a string and you have issues with
the format of the date. Try using using CONVERT(char(19),GETDATE(),120)
instead of getdate() on it's own. If you want to truncate the time use
CONVERT(char(8),GETDATE(),112)
John
"GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
news:8803F2C3-F7BD-4B8F-9A6E-9AD506205EAD@.microsoft.com...
> Hi there, may seem like a really silly question, but i am new to SQL.
> I'm building as ASP application in Dreamweaver MX2004 i'm using the insert
> record behaviour to upload information to the SQL Server. one of the
> fields
> is DateRegisterred which in Dreamweaver is a hiddenfield with the value
> set
> to GETDATE() called datereg so on the upload behaviour Dreamweaver should
> upload GETDATE() from datereg to thedatetimefield DateRegisterred on the
> SQL
> server. However on trying this i get the following error message -
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting
> datetime from character string.
> /driverjobs/candregistration.asp, line 161
> Please any advise as how to fix this would be really really appreciated.
> Many thanks
> Gareth|||Thanks John, should i change it within the hidden field, or somewhere in the
code? i've tried changing it in the hidden field but still get the same erro
r
message?
thanks for your help
Gareth
"John Bell" wrote:

> Hi
> It looks like your field is being taken as a string and you have issues wi
th
> the format of the date. Try using using CONVERT(char(19),GETDATE(),120)
> instead of getdate() on it's own. If you want to truncate the time use
> CONVERT(char(8),GETDATE(),112)
> John
> "GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
> news:8803F2C3-F7BD-4B8F-9A6E-9AD506205EAD@.microsoft.com...
>
>|||Hi again,
I've included the line of code - Thanks again.
<td><input name="DateRegistered" type="hidden" id="DateRegistered"
value="CONVERT(char(8),GETDATE(),112)"></td>
"John Bell" wrote:

> Hi
> It looks like your field is being taken as a string and you have issues wi
th
> the format of the date. Try using using CONVERT(char(19),GETDATE(),120)
> instead of getdate() on it's own. If you want to truncate the time use
> CONVERT(char(8),GETDATE(),112)
> John
> "GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
> news:8803F2C3-F7BD-4B8F-9A6E-9AD506205EAD@.microsoft.com...
>
>|||Hi
You can't put SQL into your code like that, it should be in the query that
you call and then populate the field from this value (in the same way you
populate others!). If you can use the datetime that the insert is made,
then it can be missed out completely and the column defined as not nullable
with GETDATE() as the default.
John
"GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
news:83A6E095-9200-4AA2-93F6-A40C78335320@.microsoft.com...
> Hi again,
> I've included the line of code - Thanks again.
> <td><input name="DateRegistered" type="hidden" id="DateRegistered"
> value="CONVERT(char(8),GETDATE(),112)"></td>
> "John Bell" wrote:
>|||Ahh, I think i understand, the Date function GETDATE() needs to run
automatically on the SQL server not in the ASP code. Thank you for this, it
only leaves me with one problem.
On another page users are allowed to post information for a period of time
which they chose from a drop down list -
where the item label is forinstance 7 days & the value is
DATEADD(d,7,GETDATE())
They can chose from 7,14,21 or 28 days - their choice populates a field
called expirydate.
My theory apparently wont work now, any ideas as to how i can get around
this one?
Onced again thanks for your help
Gareth
"John Bell" wrote:

> Hi
> You can't put SQL into your code like that, it should be in the query that
> you call and then populate the field from this value (in the same way you
> populate others!). If you can use the datetime that the insert is made,
> then it can be missed out completely and the column defined as not nullabl
e
> with GETDATE() as the default.
> John
> "GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
> news:83A6E095-9200-4AA2-93F6-A40C78335320@.microsoft.com...
>
>|||Why not have the default value of the date field equal to getdate() in SQL
Server? If you're doing an INSERT it's a new record, set it then.
Jon
"GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
news:8803F2C3-F7BD-4B8F-9A6E-9AD506205EAD@.microsoft.com...
> Hi there, may seem like a really silly question, but i am new to SQL.
> I'm building as ASP application in Dreamweaver MX2004 i'm using the insert
> record behaviour to upload information to the SQL Server. one of the
> fields
> is DateRegisterred which in Dreamweaver is a hiddenfield with the value
> set
> to GETDATE() called datereg so on the upload behaviour Dreamweaver should
> upload GETDATE() from datereg to thedatetimefield DateRegisterred on the
> SQL
> server. However on trying this i get the following error message -
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting
> datetime from character string.
> /driverjobs/candregistration.asp, line 161
> Please any advise as how to fix this would be really really appreciated.
> Many thanks
> Gareth|||Hi
This may help with the previous issue http://www.aspfaq.com/show.asp?id=2347
Instead of giving a offset, you may want to use a date picker see
http://www.aspfaq.com/show.asp?id=2309, if you do go with the original
option then if you are building up a string for your update statement you
can do something like (untested!):
<%
SQL = "UPDATE table SET NextDate = DATEADD(dd," &
Request.Form("DateOffset") & ",GETDATE())"
response.write sql
%>
If you are calling a stored procedure then you just need to pass the
offset value.
John
"GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
news:EC001645-9C1D-492D-B1A7-83E41668FC8C@.microsoft.com...
> Ahh, I think i understand, the Date function GETDATE() needs to run
> automatically on the SQL server not in the ASP code. Thank you for this,
> it
> only leaves me with one problem.
> On another page users are allowed to post information for a period of time
> which they chose from a drop down list -
> where the item label is forinstance 7 days & the value is
> DATEADD(d,7,GETDATE())
> They can chose from 7,14,21 or 28 days - their choice populates a field
> called expirydate.
> My theory apparently wont work now, any ideas as to how i can get around
> this one?
> Onced again thanks for your help
> Gareth
> "John Bell" wrote:
>|||Thanks John, once again seems very useful, Just a quick question would
something like
CONVERT(char(8), DATEADD(dd," &
Request.Form("expirydate") & ",GETDATE(),112)
work? inserted into the default value?
Thanks
"John Bell" wrote:

> Hi
> This may help with the previous issue http://www.aspfaq.com/show.asp?id=2...ow.asp?id=2309, if you do go with the original
> option then if you are building up a string for your update statement you
> can do something like (untested!):
> <%
> SQL = "UPDATE table SET NextDate = DATEADD(dd," &
> Request.Form("DateOffset") & ",GETDATE())"
> response.write sql
> %>
> If you are calling a stored procedure then you just need to pass the
> offset value.
> John
>
> "GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
> news:EC001645-9C1D-492D-B1A7-83E41668FC8C@.microsoft.com...
>
>|||Hi
If you mean the default for column, then you don't need dateadd e.g
CREATE TABLE MyDates ( id int, dateval datetime not null default
convert(char(8),getdate(),112))
INSERT INTO MyDates (id, dateval ) VALUES ( 1,GETDATE())
INSERT INTO MyDates (id) VALUES ( 2)
INSERT INTO MyDates (id, dateval) VALUES ( 3, '20051225' )
INSERT INTO MyDates (id, dateval) VALUES ( 4, '20051224 23:59:59' )
SELECT * FROM MyDates
John
"GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
news:14E69D08-5318-4B52-9A1E-8DD3A7FB0F5E@.microsoft.com...
> Thanks John, once again seems very useful, Just a quick question would
> something like
> CONVERT(char(8), DATEADD(dd," &
> Request.Form("expirydate") & ",GETDATE(),112)
> work? inserted into the default value?
> Thanks
> "John Bell" wrote:
>sql

GetBytes issue

We recently change our application from Sun JDBC-ODBC
bridge driver to MS SQLServer JDBC driver and one of our
functionality is failing and after debuging dound that
getByte mentod is not retrving the data correctly rather
it change the data. The code is working fine with JdbcOdbc
driver, here is the sample code.
Any help is appreciated.
Thanks
Syed
stmt = objUtil.getConnection().createStatement();
result = stmt.executeQuery(strSQL);
byte[] gbArrKS = null;
while (result.next()){
gbArrKS= result.getBytes("BinrydataCol");
}
Hi. You'll get your best help if you describe the definition of the table
column, and maybe also show what the data really is.
thanks,
Joe Weinstein at BEA
Syed Hussain wrote:

> We recently change our application from Sun JDBC-ODBC
> bridge driver to MS SQLServer JDBC driver and one of our
> functionality is failing and after debuging dound that
> getByte mentod is not retrving the data correctly rather
> it change the data. The code is working fine with JdbcOdbc
> driver, here is the sample code.
> Any help is appreciated.
> Thanks
> --
> Syed
> stmt = objUtil.getConnection().createStatement();
> result = stmt.executeQuery(strSQL);
> byte[] gbArrKS = null;
> while (result.next()){
> gbArrKS= result.getBytes("BinrydataCol");
> }
>
sql

Get value from Report

There is a textbox property where I can set the Jump to URL value equal to
any field from the report. I currently have a windows application with an IE
web control embedded in the windows form. How can I get the value of a
selected field in the report outside of reporting services? Is it possible to
create a web service and pass the value over to it and retrieve it that way
or is there a better way to do this? Thanks for any input.I am not sure what you want to do here. If you want the value of a
selected field from your app to be passed to a report you can pass it
as a parameter to the report.
if you want a value from the report to be passed into your app then you
can pass the value from your report into a custom assembly, which you
can use in your app.|||I would like the value passed from the report to the calling app. Can you
point me to any examples (preferably c#) re. passing values from report into
a custom assembly? Thanks.
"RN" wrote:
> I am not sure what you want to do here. If you want the value of a
> selected field from your app to be passed to a report you can pass it
> as a parameter to the report.
> if you want a value from the report to be passed into your app then you
> can pass the value from your report into a custom assembly, which you
> can use in your app.
>|||The custom assembly would be a C# project with a method in it that
accepts the value you want to pass to your app, which you can save in a
global variable or collection and use in your app. Please refer to
Custom Assemblies in books online for information about how to
reference this assembly in your report and how to call methods in it.
El Presidente wrote:
> I would like the value passed from the report to the calling app.
Can you
> point me to any examples (preferably c#) re. passing values from
report into
> a custom assembly? Thanks.
> "RN" wrote:
> > I am not sure what you want to do here. If you want the value of a
> > selected field from your app to be passed to a report you can pass
it
> > as a parameter to the report.
> > if you want a value from the report to be passed into your app then
you
> > can pass the value from your report into a custom assembly, which
you
> > can use in your app.
> >
> >

get the windows user from SQL server

Hello,
I have an application that use SQL server, (the conexion is not by nt
autentification)
I need to know what is the current windows user.
Any help will be appreciates.
Thanks Diego
Look at BOL, in the Functions:
CURRENT_USER
SYSTEM_USER
USER
USER_NAME
(SUSER_NAME)
HTH, Jens Smeyer
http://www.sqlserver2005.de
"Petxa" <pecharroman2001@.hotmail.com> schrieb im Newsbeitrag
news:eryji3LQFHA.2972@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I have an application that use SQL server, (the conexion is not by nt
> autentification)
> I need to know what is the current windows user.
> Any help will be appreciates.
> Thanks Diego
>
|||these function return the sql users
i am thinking that it isn't possible.

"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:eic3p7LQFHA.3196@.TK2MSFTNGP12.phx.gbl...
> Look at BOL, in the Functions:
> CURRENT_USER
> SYSTEM_USER
> USER
> USER_NAME
> (SUSER_NAME)
> HTH, Jens Smeyer
> --
> http://www.sqlserver2005.de
> --
> "Petxa" <pecharroman2001@.hotmail.com> schrieb im Newsbeitrag
> news:eryji3LQFHA.2972@.TK2MSFTNGP14.phx.gbl...
>
|||Now i understand ;-)
No dont think thats possible
Jens.
"Petxa" <pecharroman2001@.hotmail.com> schrieb im Newsbeitrag
news:uLP7RhMQFHA.3296@.TK2MSFTNGP15.phx.gbl...
> these function return the sql users
> i am thinking that it isn't possible.
>
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in
> message news:eic3p7LQFHA.3196@.TK2MSFTNGP12.phx.gbl...
>
|||Hi
If you are using SQL Authentication, SQL Server does not know the NT user
name as it is not presented by the ODBC/OLE DB stack.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:%23KIdMmMQFHA.3544@.TK2MSFTNGP12.phx.gbl...
> Now i understand ;-)
> No dont think thats possible
> Jens.
>
> "Petxa" <pecharroman2001@.hotmail.com> schrieb im Newsbeitrag
> news:uLP7RhMQFHA.3296@.TK2MSFTNGP15.phx.gbl...
>
|||I have found something related with the envirnment variables, but is needed
have admin rights, and on my application there are any users wihtout this
rights.
(if somebody need this way, please send me a mail ask it
pecharroman2001@.hotmail.com)
so i will search another ways.
Thanks Jens!
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:%23KIdMmMQFHA.3544@.TK2MSFTNGP12.phx.gbl...
> Now i understand ;-)
> No dont think thats possible
> Jens.
>
> "Petxa" <pecharroman2001@.hotmail.com> schrieb im Newsbeitrag
> news:uLP7RhMQFHA.3296@.TK2MSFTNGP15.phx.gbl...
>
|||Short of sticking with Windows Authentication, I don't think this is
possible. You can get the host name from master..sysprocesses or by
using host_name().
David Gugick
Imceda Software
www.imceda.com
|||"Petxa" wrote:

> I have found something related with the envirnment variables, but is
> needed have admin rights, and on my application there are any users
> wihtout this rights.
> (if somebody need this way, please send me a mail ask it
> pecharroman2001@.hotmail.com)
> so i will search another ways.
A terrible, horrible, really bad hack that might work for you if...
- You have control over your app
- You don't care what app_name() returns
You could set the user name in the connection string on the application
side. See the Win32 API function GetUserName and add the name/value combo
"Application Name=what you got from GetUserName" to your connection string.
You might also want to add some kind of identifying prefix and/or postfix to
the name so that you can check to make sure the value you get from app_name
was set with this technique. You can also manually include the application
name so that connections you view with EM or sp_who are easier to identify.
Then in T-SQL, you can get the current connection's user name with something
like
declare @.uid varchar(50)
set @.uid = app_name()
Craig
|||It is realy a good idea, i haven't still tested but it looks great!
Thank Craig! (and all for your help)
"Craig Kelly" <cnkelly.nospam@.nospam.net> wrote in message
news:qCB7e.579079$w62.560396@.bgtnsc05-news.ops.worldnet.att.net...
> "Petxa" wrote:
>
> A terrible, horrible, really bad hack that might work for you if...
> - You have control over your app
> - You don't care what app_name() returns
> You could set the user name in the connection string on the application
> side. See the Win32 API function GetUserName and add the name/value combo
> "Application Name=what you got from GetUserName" to your connection
string.
> You might also want to add some kind of identifying prefix and/or postfix
to
> the name so that you can check to make sure the value you get from
app_name
> was set with this technique. You can also manually include the
application
> name so that connections you view with EM or sp_who are easier to
identify.
> Then in T-SQL, you can get the current connection's user name with
something
> like
> declare @.uid varchar(50)
> set @.uid = app_name()
> Craig
>

Get the windows user from SQL

Hello,
I have an application that use SQL server 2000, (the conexion is not by nt
autentification)
I need to know what is the current windows user.
Any help will be appreciates.
Thanks DiegoAnswered in .sqlserver
Please dont do double posts.
http://livinginternet.com/i/ia_nq_info_news.htm
Jens.
"Petxa" <pecharroman2001@.hotmail.com> schrieb im Newsbeitrag
news:u$%23QN4LQFHA.1396@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I have an application that use SQL server 2000, (the conexion is not by nt
> autentification)
> I need to know what is the current windows user.
> Any help will be appreciates.
> Thanks Diego
>

Get the windows user from SQL

Hello,
I have an application that use SQL server 2000, (the conexion is not by nt
autentification)
I need to know what is the current windows user.
Any help will be appreciates.
Thanks Diego
Answered in .sqlserver
Please dont do double posts.
http://livinginternet.com/i/ia_nq_info_news.htm
Jens.
"Petxa" <pecharroman2001@.hotmail.com> schrieb im Newsbeitrag
news:u$%23QN4LQFHA.1396@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I have an application that use SQL server 2000, (the conexion is not by nt
> autentification)
> I need to know what is the current windows user.
> Any help will be appreciates.
> Thanks Diego
>

get the description of a column

Okay guys heres the senario.

I have written a kick butt asp application that allows me to test sql
statements and manage/display all my databases from the web but I have
a feature I want to include that I can't figure out how. In Enterprise
Manager, one of the column editable properties is the Column
Description. I can't find it in sql server itself. only in the
Enterprise Manager. I need to access it using a sql statement so that
it will display in my table definiation view that I create in the asp
app.These descriptions are kept as extended properties in sysproperties.
Look up sp_addextendedproperty in the help file for more information.
To figure out what Enterprise Manager is doing in situations like
these, you can use Profiler to see what SQL code it is sending to the
server.

-Tom.

get Stored Procedures Parameters, how?

hi
i make smal Application to get information from SQL Server 2000 by using
VB6.
now i can get Databases, Tables and Colomn, and Stored Procedures, but my
problem how i can get SP Parametres?
i am thinking to make small function to get Parametres From SP.Text, but i
think its not good solution.
Tarek M. SialaMaybe you could cross-post to a few more groups, or try this search engine
called google before casting such a wide net. Anyway, here is one page that
might help. Followups set accordingly.
http://www.aspfaq.com/2463
"Tark Siala" <tarksiala@.icc-libya.com> wrote in message
news:edmJJ3scGHA.3908@.TK2MSFTNGP04.phx.gbl...
> hi
> i make smal Application to get information from SQL Server 2000 by using
> VB6.
> now i can get Databases, Tables and Colomn, and Stored Procedures, but my
> problem how i can get SP Parametres?
> i am thinking to make small function to get Parametres From SP.Text, but i
> think its not good solution.
> --
> Tarek M. Siala
>

Get sql statement for parameter through reporting service

hi,

i'm creating a winform application that communicates with a reporting service server through the SOAP interface.

If a user selects a report and presses a button a generic screen is created where I create all winforms controls like datetimepicker(if parameter type is DateTime) or combobox (if parameter type is String). When the parameter value is String and there are DefaultValues I add every value to the combobox. But when it has no DefaultValues and thus is generated through SQL I want the SQL statement but I can't find that property. Her you got the code to make it more comprehensive.

[CODE]
else if (rp.Type.ToString() == "String")
{
ComboBox combobox = new ComboBox();
ValidValue[] values = rp.ValidValues;
if (values == null)
{
String sqlString =
}
else
{
foreach (ValidValue value in values)
{
combobox.Items.Add(value.Label.ToString());
}
}
VerControl += 30;
combobox.Location = new Point(HorControl, VerControl);
combobox.Width = 200;
this.Controls.Add(combobox);
}
[/CODE]
hope you can help

greetings
Never mind. When asking for the report parameters I changed the boolean ForRendering to true so I get the values for SQL based parameters too! I don't have the sql statement but i get the values that I need

Get Sql Err Message 15422 when activating application role.

Running VB 2005 Express Edition and Sql Server 2005 Express Edition (SQLX).

Developing a desktop application which calls a local instance of ".\sqlexpress".

This app needs to set data base options and add/del various table columns.

When activating the application role, I get the following message:

HariCari SQL Error/s 15422 - Application roles can only be activated at the ad hoc level.

Anyone know what this message means?

I have searched SQL Server Books On-Line and been unable to find a list of Sql err numbers. Either I have missed the obvious or Books On-Line has missed the obvious.

Thanks

Gary

You cannot call sp_setapprole or sp_unsetapprole within another stored procedure. These procedures must be called directly (that's what the message means by ad hoc level).

Here's the sp_setapprole topic:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_sa-sz_6tt1.asp

It mentions in the remarks section that: "The sp_setapprole stored procedure can be executed only by direct Transact-SQL statements; it cannot be executed within another stored procedure or from within a user-defined transaction."

Thanks
Laurentiu

|||Many thanks Laurentiu!!!

Obviously, I will not continue going down a path which is not intended to work. You have save me time and agravitation.

Again, Thanks!!!

Gary|||Hi,

I have being trying to setup application roles in my application. I have been reading a couple of threads (the one with Ian), and am still a bit lost.

When you say that application roles must be called directly, how do you get the 3rd party application to to call this SP via ODBC and if you have to manualy type in the SP with the password, doesn't that allow the user (inputer) access to the database.

Please help.|||

You must make the calls directly from your application code, not by embedding them in a stored procedure and calling the stored procedure from the application code. Your application should issue the setapprole request directly, not call a procedure that calls sp_setapprole.

Thanks
Laurentiu

|||This is a snippet of C/C++ ODBC code I'm using. I have a valid statement handle allocated and is freed upon function return.

sqlrc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, parmlen[0], 0, app, parmlen[0], &cb[0]);
sqlrc = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, parmlen[1], 0, pw, parmlen[1], &cb[1]);

strcpy(buff, "{call sp_setapprole (?,?)}");
sqlrc = SQLExecDirect(hstmt, buff, SQL_NTS);

I have examined the stored proc code and have noticed that it signals this error when the nest level is greater than 1. Is there some code in ODBC that creates a SP on the fly and then calls the SQLExecDirect ?

Many thanks.|||

I am not sure what you are asking for. For ODBC questions, you may want to post on the SQL Server Data Access forum.

If you want to find out how to set an application role withing your application, here is a simple C# example that does that. It assumes you have created the application role in a database named test with the following commands:

create database test

use test

create application role approle with password = 'Password)^'

In the following function, you should replace the YOURSERVERNAME string with the name of your SQL Server instance:

static void sql_test()
{
SqlConnection sqlConn = null;
String strConn = "Persist Security Info=false;";

strConn += "Server=\'" + "YOURSERVERNAME"
+ "\';Integrated Security=true;";

try
{
sqlConn = new SqlConnection(strConn);
sqlConn.Open();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return;
}

sqlConn.ChangeDatabase("test");

SqlCommand sqlCmd = new SqlCommand("select user_name()", sqlConn);
try
{
Console.WriteLine(sqlCmd.ExecuteScalar());
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return;
}

sqlCmd = new SqlCommand("sp_setapprole 'approle', 'Password)^'", sqlConn);
try
{
sqlCmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return;
}

sqlCmd = new SqlCommand("select user_name()", sqlConn);
try
{
Console.WriteLine(sqlCmd.ExecuteScalar());
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return;
}
}

This should print your original context and then the context of approle, showing that approle was set.

Thanks
Laurentiu

Get Sql Err Message 15422 when activating application role.

Running VB 2005 Express Edition and Sql Server 2005 Express Edition (SQLX).

Developing a desktop application which calls a local instance of ".\sqlexpress".

This app needs to set data base options and add/del various table columns.

When activating the application role, I get the following message:

HariCari SQL Error/s 15422 - Application roles can only be activated at the ad hoc level.

Anyone know what this message means?

I have searched SQL Server Books On-Line and been unable to find a list of Sql err numbers. Either I have missed the obvious or Books On-Line has missed the obvious.

Thanks

Gary

You cannot call sp_setapprole or sp_unsetapprole within another stored procedure. These procedures must be called directly (that's what the message means by ad hoc level).

Here's the sp_setapprole topic:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_sa-sz_6tt1.asp

It mentions in the remarks section that: "The sp_setapprole stored procedure can be executed only by direct Transact-SQL statements; it cannot be executed within another stored procedure or from within a user-defined transaction."

Thanks
Laurentiu

|||Many thanks Laurentiu!!!

Obviously, I will not continue going down a path which is not intended to work. You have save me time and agravitation.

Again, Thanks!!!

Gary|||Hi,

I have being trying to setup application roles in my application. I have been reading a couple of threads (the one with Ian), and am still a bit lost.

When you say that application roles must be called directly, how do you get the 3rd party application to to call this SP via ODBC and if you have to manualy type in the SP with the password, doesn't that allow the user (inputer) access to the database.

Please help.|||

You must make the calls directly from your application code, not by embedding them in a stored procedure and calling the stored procedure from the application code. Your application should issue the setapprole request directly, not call a procedure that calls sp_setapprole.

Thanks
Laurentiu

|||This is a snippet of C/C++ ODBC code I'm using. I have a valid statement handle allocated and is freed upon function return.

sqlrc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, parmlen[0], 0, app, parmlen[0], &cb[0]);
sqlrc = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, parmlen[1], 0, pw, parmlen[1], &cb[1]);

strcpy(buff, "{call sp_setapprole (?,?)}");
sqlrc = SQLExecDirect(hstmt, buff, SQL_NTS);

I have examined the stored proc code and have noticed that it signals this error when the nest level is greater than 1. Is there some code in ODBC that creates a SP on the fly and then calls the SQLExecDirect ?

Many thanks.|||

I am not sure what you are asking for. For ODBC questions, you may want to post on the SQL Server Data Access forum.

If you want to find out how to set an application role withing your application, here is a simple C# example that does that. It assumes you have created the application role in a database named test with the following commands:

create database test

use test

create application role approle with password = 'Password)^'

In the following function, you should replace the YOURSERVERNAME string with the name of your SQL Server instance:

static void sql_test()
{
SqlConnection sqlConn = null;
String strConn = "Persist Security Info=false;";

strConn += "Server=\'" + "YOURSERVERNAME"
+ "\';Integrated Security=true;";

try
{
sqlConn = new SqlConnection(strConn);
sqlConn.Open();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return;
}

sqlConn.ChangeDatabase("test");

SqlCommand sqlCmd = new SqlCommand("select user_name()", sqlConn);
try
{
Console.WriteLine(sqlCmd.ExecuteScalar());
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return;
}

sqlCmd = new SqlCommand("sp_setapprole 'approle', 'Password)^'", sqlConn);
try
{
sqlCmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return;
}

sqlCmd = new SqlCommand("select user_name()", sqlConn);
try
{
Console.WriteLine(sqlCmd.ExecuteScalar());
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return;
}
}

This should print your original context and then the context of approle, showing that approle was set.

Thanks
Laurentiu

Get report in Excel

Hi,

I've made C# winform application where the user can enter it's parameters (I did this so I can do some inputcontrol on the users parameters which I can't in reportviewer cause it's Closed Source).

Now when the user presses the button Get report in Excel. The application should pass the parameters to the SQL statement and complete this. Then the report should be exported to Excel and Excel should start and show me the report.

All this should work behind the scenes and the user should just get the report in Excel. I've added the Web Reference from the report server to my project but I can't seem to find the correct method.

thanks in advance

Hello,

Have a look at the ReportExecutionService class for RS 2005:

http://msdn2.microsoft.com/en-US/library/microsoft.wssux.reportingserviceswebservice.rsexecutionservice2005.reportexecutionservice(SQL.90).aspx

-Chris