cant show data from my database

Hi

I have this code, but i can't get it to work, and if i delete <% %> then the if statsment is not working, how do i get this code to work.

1<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionStringMain%>"2 SelectCommand="SELECT [SiteMainID], [SiteMainText] FROM [MainSiteText] WHERE ([SiteMainID] = @.SiteMainID)">3<% Dim pageString As String = "Def"4 If Not (Request.QueryString("Page") Is Nothing) Then5 pageString = Request.QueryString("Page").ToString6 End If7 If pageString = "Def" Then8%>9 <SelectParameters>10 <asp:Parameter DefaultValue="1" Name="SiteMainID" Type="Int32" />11 </SelectParameters>12<%13 Else14 If pageString = "Page1" Then15%>16 <SelectParameters>17 <asp:Parameter DefaultValue="2" Name="SiteMainID" Type="Int32" />18 </SelectParameters>19<%20 Else21 If pageString = "Page2" Then22%>23 <SelectParameters>24 <asp:Parameter DefaultValue="3" Name="SiteMainID" Type="Int32" />25 </SelectParameters>26<%2728 End If29 End If30%>31</asp:SqlDataSource>

you can do it your sql if you want

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionStringMain%>"SelectCommand="SELECT [SiteMainID], [SiteMainText] FROM [MainSiteText] WHERE [SiteMainID] = (

SelectCase @.SiteMainIDWhen'Page1'Then 2When'Page2'Then 3Else 1End)">
<SelectParameters>
<asp:QueryStringParameterQueryStringField="Page"DefaultValue="Def"Name="SiteMainID"Type="String"/>
</SelectParameters>
|||

this might work too...

First remove all the code below the initial sqldatasource control code. in other words all the parameter code stuff.

Protected Sub SqlDataSource1_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles SqlDataSource1.Init
Dim pageString As String = "Def"
If Not (Request.QueryString("Page") Is Nothing) Then
pageString = Request.QueryString("Page").ToString
End If
SqlDataSource1.SelectParameters.Clear()
If pageString = "Def" Then
SqlDataSource1.SelectParameters.Add(New WebControls.Parameter("SiteMainID", TypeCode.Int32, "1"))
Else
If pageString = "Page1" Then
SqlDataSource1.SelectParameters.Add(New WebControls.Parameter("SiteMainID", TypeCode.Int32, "2"))
ElseIf pageString = "Page2" Then
SqlDataSource1.SelectParameters.Add(New WebControls.Parameter("SiteMainID", TypeCode.Int32, "3"))
End If
End If
End Sub

|||

if u mean like this, then i get a codeblock error for the <% thats its not supported in this context:

1<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionStringMain%>"2 SelectCommand="SELECT [SiteMainID], [SiteMainText] FROM [MainSiteText] WHERE ([SiteMainID] = @.SiteMainID)">3<%4 Protected Sub SqlDataSource1_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles SqlDataSource1.Init5 Dim pageString As String = "Def"6 If Not (Request.QueryString("Page") Is Nothing) Then7 pageString = Request.QueryString("Page").ToString8 End If9 SqlDataSource1.SelectParameters.Clear()10 If pageString = "Def" Then11 SqlDataSource1.SelectParameters.Add(New WebControls.Parameter("SiteMainID", TypeCode.Int32, "1"))12 Else13 If pageString = "Page1" Then14 SqlDataSource1.SelectParameters.Add(New WebControls.Parameter("SiteMainID", TypeCode.Int32, "2"))15 ElseIf pageString = "Page2" Then16 SqlDataSource1.SelectParameters.Add(New WebControls.Parameter("SiteMainID", TypeCode.Int32, "3"))17 End If18 End If19 End Sub20%>21</asp:SqlDataSource>

|||

Cant i use session !?

Like this:

<asp:Content ID="Main" ContentPlaceHolderID="ContentPlaceHolderMain" Runat="Server"><% Dim pageString As String = "Def" Dim PageID = CType(Session("pageid"), Integer) If Not (Request.QueryString("Page") Is Nothing) Then pageString = Request.QueryString("Page").ToString End If If pageString = "Def" Then PageID = "1" Else If pageString = "Page2" Then PageID = "2" Else If pageString = "Page3" Then PageID = "3" End If End If End If%><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionStringMain%>" SelectCommand="SELECT [SiteMainID], [SiteMainText] FROM [MainSiteText] WHERE ([SiteMainID] = '" & Session("pageid") & "')"></asp:SqlDataSource>

my only problem with this is that i get a "Attribute 'session' is not a valid attribute of the element 'SqlDateSource'"|||

Yes

You could bind SelectCommand to Session.

But I would do it in SqlDataSource1_DataBinding Event:

protected void SqlDataSource1_DataBinding(object sender, EventArgs e) { String sql ="SELECT column1 FROM test WHERE column1 ='" + Session["pageid"] +"'"; SqlDataSource1.SelectCommand = sql; }

You need to change it to VB.

Hope this helps.

Can't see a New Data Driven Subscription link

Hi:

I have SSRS 2005 standard but can't see the New Data Driven Subscription link so I am not sure how to create it! Please help..

Thanks.

As far as I am concerned the data driven subscription is an enterprise-only feature.

cheers,
Markus

Can't restore master db!

Hi:
I've got a problem that's causing me headaches! I'm using Veritas Backup Exe
c to perform my db backups. I've just
recently taken over the DBA responsibilities and one of the first things I'v
e wanted to do is test those backups since
we've never had occassion to use the backups. I have a test system that I'm
using and have it configured exactly as the
production system.
After installing MS SQL Server 7 putting on SP4 and all the security patches
I try to restore all the databases. I can
get all my databases restored except the master db that contains all my secu
rity info. I keep getting the message
"must be in single user mode". I've tried to set the master db into single
user mode and there's just no way to do it!!
I've tried using the EM and the sp_dboption. Without that master db restore
d I'm in deep doo-doo!
Any ideas how I can handle this' Any help/suggestions is greatly appreciat
ed.
TIA
Cathy
SQL Server 7( SP4), NT4.0Hi,
To restore the master database you have start the SQL server itself in
Single user mode.
1. Stop the MSSQL server and sql agent service
2. Go to command prompt and execute below command,.
sqlservr.exe -c -m
3. Login to sql server using query analyzer and retore the master database.
4. After the restore .. go to old comamnd prompt and press CONTRL and C to
close the old session
5. Go to control panel and start the MSSQL server and Sql agent service.
Note:
I recommend you to restore the master database first followed with other
databases.
Thanks
Hari
MCDBA
"Cathy Finnegan" <eliminates_spam_caeriel1@.cablespeed.com> wrote in message
news:jamsg0tqoig5fqs15kdonltvlitoiktf3u@.
4ax.com...
> Hi:
> I've got a problem that's causing me headaches! I'm using Veritas Backup
Exec to perform my db backups. I've just
> recently taken over the DBA responsibilities and one of the first things
I've wanted to do is test those backups since
> we've never had occassion to use the backups. I have a test system that
I'm using and have it configured exactly as the
> production system.
> After installing MS SQL Server 7 putting on SP4 and all the security
patches I try to restore all the databases. I can
> get all my databases restored except the master db that contains all my
security info. I keep getting the message
> "must be in single user mode". I've tried to set the master db into
single user mode and there's just no way to do it!!
> I've tried using the EM and the sp_dboption. Without that master db
restored I'm in deep doo-doo!
> Any ideas how I can handle this' Any help/suggestions is greatly
appreciated.
> TIA
> Cathy
> SQL Server 7( SP4), NT4.0|||This method will work. I think Hari copied and pasted the answer directly
from Books Online! I would have provided exactly the same answer. I
probably would have also pointed you at Books Online (within your SQL Server
program group). BACKUP and RESTORE information and examples can be found
there. Knowing how to restore the data that you are backing up is
important. Cathy, you are smart to be doing your research now (instead of
when the entire company is waiting for you to restore the data).
Keith
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:u%23SoMWKeEHA.1764@.TK2MSFTNGP10.phx.gbl...
> Hi,
> To restore the master database you have start the SQL server itself in
> Single user mode.
> 1. Stop the MSSQL server and sql agent service
> 2. Go to command prompt and execute below command,.
> sqlservr.exe -c -m
> 3. Login to sql server using query analyzer and retore the master
database.
> 4. After the restore .. go to old comamnd prompt and press CONTRL and C to
> close the old session
> 5. Go to control panel and start the MSSQL server and Sql agent service.
> Note:
> I recommend you to restore the master database first followed with other
> databases.
> Thanks
> Hari
> MCDBA
>
> "Cathy Finnegan" <eliminates_spam_caeriel1@.cablespeed.com> wrote in
message
> news:jamsg0tqoig5fqs15kdonltvlitoiktf3u@.
4ax.com...
> Exec to perform my db backups. I've just
> I've wanted to do is test those backups since
> I'm using and have it configured exactly as the
> patches I try to restore all the databases. I can
> security info. I keep getting the message
> single user mode and there's just no way to do it!!
> restored I'm in deep doo-doo!
> appreciated.
>|||Thanks guys for your prompt reply.
I thought I was starting the SQL Server in single user mode: I'm using the
command "net start mssqlserver -m"... Is this not correct'
"Keith Kratochvil" wrote:

> This method will work. I think Hari copied and pasted the answer directly
> from Books Online! I would have provided exactly the same answer. I
> probably would have also pointed you at Books Online (within your SQL Serv
er
> program group). BACKUP and RESTORE information and examples can be found
> there. Knowing how to restore the data that you are backing up is
> important. Cathy, you are smart to be doing your research now (instead of
> when the entire company is waiting for you to restore the data).
> --
> Keith
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:u%23SoMWKeEHA.1764@.TK2MSFTNGP10.phx.gbl...
> database.
> message
>|||No, NET START starts the MSSQLSERVER service, and the -m is a parameter to
NET START, which doesn't take any parameters so it wouldn't know what to do
with it.
You need to run the sqlservr executable, and the pass the -m flag to it as
Hari suggested.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"caeriel" <caeriel@.discussions.microsoft.com> wrote in message
news:9542FB0A-84B5-4DDD-8498-87C5F4A91D4A@.microsoft.com...
> Thanks guys for your prompt reply.
> I thought I was starting the SQL Server in single user mode: I'm using
the command "net start mssqlserver -m"... Is this not correct'[vbcol=seagreen]
> "Keith Kratochvil" wrote:
>
directly[vbcol=seagreen]
Server[vbcol=seagreen]
found[vbcol=seagreen]
of[vbcol=seagreen]
C to[vbcol=seagreen]
service.[vbcol=seagreen]
other[vbcol=seagreen]
Backup[vbcol=seagreen]
things[vbcol=seagreen]
that[vbcol=seagreen]
my[vbcol=seagreen]|||Keith: I'm still unable to restore the master database...
I tried starting the server as you & Hari instructed; the server started in
single user mode. However, the restore has to be issued from the Veritas so
ftware interface. Here's the SQL Server log.
*************************************
========================================
==============================
Job server: Test
Job name: MasterDB NoRecov 8-03
Job started: Tuesday, August 03, 2004 at 10:08:07 AM
Job type: Restore
Job Log: BEX41.txt
========================================
==============================
Drive and media information from media mount:
Robotic Library Name:
Drive Name: Quantum 1
Slot: 0
Media GUID: {2E9659D2-C5B5-43BF-BD2A-502B2E1054E1}
Media Label: Test Wk1 Monday
Overwrite Protection Time Left: None
Append Time Left: Infinite
========================================
==============================
Job Operation - Restore
========================================
==============================
Restoring data to Test .
Storage Media #1: " Media created 8/2/04 08:24:56 PM "
Backup performed on 8/3/04 at 8:12 AM
Backup set #6: " Full Backup "
Restore started on 8/3/04 at 10:10:35 AM.
An error occurred on a query to database master.
^ ^ ^ ^ ^
SQL Server returned the following error message:
RESTORE DATABASE must be used in single user mode when trying to restore the
master database.
^ ^ ^ ^ ^
Restore completed on 8/3/04 at 10:13:42 AM.
1 item was skipped.
Processed 0 bytes in 3 minutes and 7 seconds.
Throughput rate: 0.0 MB/min
---
========================================
==============================
Job ended: Tuesday, August 03, 2004 at 10:14:31 AM
Job completion status: Failed
========================================
==============================
****************************************
************
2004-08-03 09:55:16.51 kernel Microsoft SQL Server 7.00 - 7.00.623 (Intel
X86)
Nov 27 1998 22:20:07
Copyright (c) 1988-1998 Microsoft Corporation
Standard Edition on Windows NT 4.0 (Build 1381: Service Pack 6)
2004-08-03 09:55:16.51 kernel Copyright (C) 1988-1997 Microsoft Corporatio
n.
2004-08-03 09:55:16.52 kernel All rights reserved.
2004-08-03 09:55:16.52 kernel Logging SQL Server messages in file 'D:\MSSQ
L7\log\ERRORLOG'.
2004-08-03 09:55:16.58 kernel initconfig: Number of user connections limit
ed to 32767.
2004-08-03 09:55:16.58 kernel SQL Server is starting at priority class 'no
rmal'(1 CPU detected).
2004-08-03 09:55:16.70 kernel User Mode Scheduler configured for thread pr
ocessing
2004-08-03 09:55:17.96 server Directory Size: 10801
2004-08-03 09:55:18.05 spid1 Using dynamic lock allocation. [2500] Lo
ck Blocks, [5000] Lock Owner Blocks
2004-08-03 09:55:18.05 kernel Attempting to initialize Distributed Transac
tion Coordinator.
2004-08-03 09:55:18.82 spid1 Failed to obtain TransactionDispenserInterfa
ce: XACT_E_TMNOTAVAILABLE
2004-08-03 09:55:18.83 spid1 Warning ******************
2004-08-03 09:55:18.83 spid1 SQL Server started in single user mode. Upda
tes allowed to system catalogs.
2004-08-03 09:55:18.84 spid1 Warning ******************
2004-08-03 09:55:18.85 spid1 Starting up database 'master'.
2004-08-03 09:55:18.85 spid1 Opening file D:\MSSQL7\data\master.mdf.
2004-08-03 09:55:18.89 spid1 Opening file D:\MSSQL7\data\mastlog.ldf.
2004-08-03 09:55:18.99 spid1 Loading SQL Server's Unicode collation.
2004-08-03 09:55:19.01 spid1 Loading SQL Server's non-Unicode sort order
and character set.
2004-08-03 09:55:19.24 spid1 4 transactions rolled forward in database 'm
aster' (1).
2004-08-03 09:55:19.25 spid1 0 transactions rolled back in database 'mast
er' (1).
2004-08-03 09:55:19.35 spid1 Starting up database 'model'.
2004-08-03 09:55:19.36 spid1 Opening file D:\MSSQL7\DATA\model.mdf.
2004-08-03 09:55:19.43 spid1 Opening file D:\MSSQL7\DATA\modellog.ldf.
2004-08-03 09:55:19.63 spid1 Clearing tempdb database.
2004-08-03 09:55:19.69 spid1 Creating file D:\MSSQL7\DATA\TEMPDB.MDF.
2004-08-03 09:55:19.87 spid1 Closing file D:\MSSQL7\DATA\TEMPDB.MDF.
2004-08-03 09:55:19.97 spid1 Creating file D:\MSSQL7\DATA\TEMPLOG.LDF.
2004-08-03 09:55:19.99 spid1 Closing file D:\MSSQL7\DATA\TEMPLOG.LDF.
2004-08-03 09:55:20.12 spid1 Opening file D:\MSSQL7\DATA\TEMPDB.MDF.
2004-08-03 09:55:20.14 spid1 Opening file D:\MSSQL7\DATA\TEMPLOG.LDF.
2004-08-03 09:55:21.15 spid1 Closing file D:\MSSQL7\DATA\TEMPDB.MDF.
2004-08-03 09:55:21.25 spid1 Closing file D:\MSSQL7\DATA\TEMPLOG.LDF.
2004-08-03 09:55:21.34 spid1 Starting up database 'tempdb'.
2004-08-03 09:55:21.38 spid1 Opening file D:\MSSQL7\DATA\TEMPDB.MDF.
2004-08-03 09:55:21.41 spid1 Opening file D:\MSSQL7\DATA\TEMPLOG.LDF.
2004-08-03 09:55:21.69 spid1 Server name is 'AOATESTBOX'.
2004-08-03 09:55:21.70 kernel Using 'SQLEVN70.DLL' version '7.00.623'.
2004-08-03 09:55:21.71 kernel Using 'OPENDS60.DLL' version '7.00.00.0623'.
2004-08-03 09:55:21.71 ods Using 'SSNMPN70.DLL' version '7.0.623' to li
sten on '\\.\pipe\sql\query'.
2004-08-03 09:55:21.72 ods Using 'SSMSSO70.DLL' version '7.0.623' to li
sten on '3180'.
2004-08-03 09:55:21.82 ods Using 'SSMSRP70.DLL' version '7.0.623' to li
sten on 'AOATESTBOX'.
2004-08-03 09:55:22.68 spid5 Starting up database 'msdb'.
2004-08-03 09:55:22.68 spid5 Opening file D:\MSSQL7\DATA\msdbdata.mdf.
2004-08-03 09:55:22.70 spid6 Starting up database 'pubs'.
2004-08-03 09:55:22.70 spid6 Opening file D:\MSSQL7\DATA\pubs.mdf.
2004-08-03 09:55:22.72 spid7 Starting up database 'Northwind'.
2004-08-03 09:55:22.72 spid7 Opening file D:\MSSQL7\DATA\northwnd.mdf.
2004-08-03 09:55:22.82 spid5 Opening file D:\MSSQL7\DATA\msdblog.ldf.
2004-08-03 09:55:22.85 spid7 Opening file D:\MSSQL7\DATA\northwnd.ldf.
2004-08-03 09:55:22.87 spid6 Opening file D:\MSSQL7\DATA\pubs_log.ldf.
2004-08-03 09:55:23.55 spid1 Recovery complete.
2004-08-03 09:55:23.56 spid1 SQL Server's Unicode collation is:
2004-08-03 09:55:23.57 spid1 'English' (ID = 1033).
2004-08-03 09:55:23.57 spid1 comparison style = 196609.
2004-08-03 09:55:23.58 spid1 SQL Server's non-Unicode sort order is:
2004-08-03 09:55:23.58 spid1 'nocase_850' (ID = 42).
2004-08-03 09:55:23.58 spid1 SQL Server's non-Unicode character set is:
2004-08-03 09:55:23.58 spid1 'cp850' (ID = 2).
2004-08-03 09:57:00.02 spid6 Using 'xpstar.dll' version '1998.11.13' to e
xecute extended stored procedure 'sp_MSgetversion'.
2004-08-03 10:16:44.05 kernel SQL Server shutdown due to Ctrl-C or Ctrl-Br
eak signal.
****************************************
***************
Here's the Veritas log:
****************************************
********
****************************************
********
"Keith Kratochvil" wrote:

> This method will work. I think Hari copied and pasted the answer directly
> from Books Online! I would have provided exactly the same answer. I
> probably would have also pointed you at Books Online (within your SQL Serv
er
> program group). BACKUP and RESTORE information and examples can be found
> there. Knowing how to restore the data that you are backing up is
> important. Cathy, you are smart to be doing your research now (instead of
> when the entire company is waiting for you to restore the data).
> --
> Keith
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:u%23SoMWKeEHA.1764@.TK2MSFTNGP10.phx.gbl...
> database.
> message
>|||This may be a problem with Veritas software. Can you get the backup files
from Veritas, and do the restore manually through Query Analyzer?
Restore database master from disk = "C:\whatever"
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Cathy Finnegan" <eliminates_spam_caeriel1@.cablespeed.com> wrote in message
news:jamsg0tqoig5fqs15kdonltvlitoiktf3u@.
4ax.com...
> Hi:
> I've got a problem that's causing me headaches! I'm using Veritas Backup
Exec to perform my db backups. I've just
> recently taken over the DBA responsibilities and one of the first things
I've wanted to do is test those backups since
> we've never had occassion to use the backups. I have a test system that
I'm using and have it configured exactly as the
> production system.
> After installing MS SQL Server 7 putting on SP4 and all the security
patches I try to restore all the databases. I can
> get all my databases restored except the master db that contains all my
security info. I keep getting the message
> "must be in single user mode". I've tried to set the master db into
single user mode and there's just no way to do it!!
> I've tried using the EM and the sp_dboption. Without that master db
restored I'm in deep doo-doo!
> Any ideas how I can handle this' Any help/suggestions is greatly
appreciated.
> TIA
> Cathy
> SQL Server 7( SP4), NT4.0|||Try restoring the backup files to disk using Veritas and then restore the
database using T-SQL executed via Query Analyzer.
RESTORE DATABASE foo FROM DISK = 'x:\foo.bak'
(syntax and examples within Books Online)
Keith
"caeriel" <caeriel@.discussions.microsoft.com> wrote in message
news:B6837C91-9566-4CB1-B3D0-73DB610E6232@.microsoft.com...
> Keith: I'm still unable to restore the master database...
> I tried starting the server as you & Hari instructed; the server started
in single user mode. However, the restore has to be issued from the Veritas
software interface. Here's the SQL Server log.
> *************************************
> ========================================
==============================
> Job server: Test
> Job name: MasterDB NoRecov 8-03
> Job started: Tuesday, August 03, 2004 at 10:08:07 AM
> Job type: Restore
> Job Log: BEX41.txt
> ========================================
==============================
> Drive and media information from media mount:
> Robotic Library Name:
> Drive Name: Quantum 1
> Slot: 0
> Media GUID: {2E9659D2-C5B5-43BF-BD2A-502B2E1054E1}
> Media Label: Test Wk1 Monday
> Overwrite Protection Time Left: None
> Append Time Left: Infinite
> ========================================
==============================
> Job Operation - Restore
> ========================================
==============================
> Restoring data to Test .
> Storage Media #1: " Media created 8/2/04 08:24:56 PM "
> Backup performed on 8/3/04 at 8:12 AM
> Backup set #6: " Full Backup "
> Restore started on 8/3/04 at 10:10:35 AM.
> An error occurred on a query to database master.
> ^ ^ ^ ^ ^
> SQL Server returned the following error message:
> RESTORE DATABASE must be used in single user mode when trying to restore
the master database.
> ^ ^ ^ ^ ^
> Restore completed on 8/3/04 at 10:13:42 AM.
> 1 item was skipped.
>
> Processed 0 bytes in 3 minutes and 7 seconds.
> Throughput rate: 0.0 MB/min
> ---
> ========================================
==============================
> Job ended: Tuesday, August 03, 2004 at 10:14:31 AM
> Job completion status: Failed
> ========================================
==============================
> ****************************************
************
> 2004-08-03 09:55:16.51 kernel Microsoft SQL Server 7.00 - 7.00.623
(Intel X86)
> Nov 27 1998 22:20:07
> Copyright (c) 1988-1998 Microsoft Corporation
> Standard Edition on Windows NT 4.0 (Build 1381: Service Pack 6)
> 2004-08-03 09:55:16.51 kernel Copyright (C) 1988-1997 Microsoft
Corporation.
> 2004-08-03 09:55:16.52 kernel All rights reserved.
> 2004-08-03 09:55:16.52 kernel Logging SQL Server messages in file
'D:\MSSQL7\log\ERRORLOG'.
> 2004-08-03 09:55:16.58 kernel initconfig: Number of user connections
limited to 32767.
> 2004-08-03 09:55:16.58 kernel SQL Server is starting at priority class
'normal'(1 CPU detected).
> 2004-08-03 09:55:16.70 kernel User Mode Scheduler configured for thread
processing
> 2004-08-03 09:55:17.96 server Directory Size: 10801
> 2004-08-03 09:55:18.05 spid1 Using dynamic lock allocation. [2500] Lock[/vb
col]
Blocks, [5000] Lock Owner Blocks[vbcol=seagreen]
> 2004-08-03 09:55:18.05 kernel Attempting to initialize Distributed
Transaction Coordinator.
> 2004-08-03 09:55:18.82 spid1 Failed to obtain
TransactionDispenserInterface: XACT_E_TMNOTAVAILABLE
> 2004-08-03 09:55:18.83 spid1 Warning ******************
> 2004-08-03 09:55:18.83 spid1 SQL Server started in single user mode.
Updates allowed to system catalogs.
> 2004-08-03 09:55:18.84 spid1 Warning ******************
> 2004-08-03 09:55:18.85 spid1 Starting up database 'master'.
> 2004-08-03 09:55:18.85 spid1 Opening file D:\MSSQL7\data\master.mdf.
> 2004-08-03 09:55:18.89 spid1 Opening file D:\MSSQL7\data\mastlog.ldf.
> 2004-08-03 09:55:18.99 spid1 Loading SQL Server's Unicode collation.
> 2004-08-03 09:55:19.01 spid1 Loading SQL Server's non-Unicode sort
order and character set.
> 2004-08-03 09:55:19.24 spid1 4 transactions rolled forward in database
'master' (1).
> 2004-08-03 09:55:19.25 spid1 0 transactions rolled back in database
'master' (1).
> 2004-08-03 09:55:19.35 spid1 Starting up database 'model'.
> 2004-08-03 09:55:19.36 spid1 Opening file D:\MSSQL7\DATA\model.mdf.
> 2004-08-03 09:55:19.43 spid1 Opening file D:\MSSQL7\DATA\modellog.ldf.
> 2004-08-03 09:55:19.63 spid1 Clearing tempdb database.
> 2004-08-03 09:55:19.69 spid1 Creating file D:\MSSQL7\DATA\TEMPDB.MDF.
> 2004-08-03 09:55:19.87 spid1 Closing file D:\MSSQL7\DATA\TEMPDB.MDF.
> 2004-08-03 09:55:19.97 spid1 Creating file D:\MSSQL7\DATA\TEMPLOG.LDF.
> 2004-08-03 09:55:19.99 spid1 Closing file D:\MSSQL7\DATA\TEMPLOG.LDF.
> 2004-08-03 09:55:20.12 spid1 Opening file D:\MSSQL7\DATA\TEMPDB.MDF.
> 2004-08-03 09:55:20.14 spid1 Opening file D:\MSSQL7\DATA\TEMPLOG.LDF.
> 2004-08-03 09:55:21.15 spid1 Closing file D:\MSSQL7\DATA\TEMPDB.MDF.
> 2004-08-03 09:55:21.25 spid1 Closing file D:\MSSQL7\DATA\TEMPLOG.LDF.
> 2004-08-03 09:55:21.34 spid1 Starting up database 'tempdb'.
> 2004-08-03 09:55:21.38 spid1 Opening file D:\MSSQL7\DATA\TEMPDB.MDF.
> 2004-08-03 09:55:21.41 spid1 Opening file D:\MSSQL7\DATA\TEMPLOG.LDF.
> 2004-08-03 09:55:21.69 spid1 Server name is 'AOATESTBOX'.
> 2004-08-03 09:55:21.70 kernel Using 'SQLEVN70.DLL' version '7.00.623'.
> 2004-08-03 09:55:21.71 kernel Using 'OPENDS60.DLL' version
'7.00.00.0623'.
> 2004-08-03 09:55:21.71 ods Using 'SSNMPN70.DLL' version '7.0.623' to
listen on '\\.\pipe\sql\query'.
> 2004-08-03 09:55:21.72 ods Using 'SSMSSO70.DLL' version '7.0.623' to
listen on '3180'.
> 2004-08-03 09:55:21.82 ods Using 'SSMSRP70.DLL' version '7.0.623' to
listen on 'AOATESTBOX'.
> 2004-08-03 09:55:22.68 spid5 Starting up database 'msdb'.
> 2004-08-03 09:55:22.68 spid5 Opening file D:\MSSQL7\DATA\msdbdata.mdf.
> 2004-08-03 09:55:22.70 spid6 Starting up database 'pubs'.
> 2004-08-03 09:55:22.70 spid6 Opening file D:\MSSQL7\DATA\pubs.mdf.
> 2004-08-03 09:55:22.72 spid7 Starting up database 'Northwind'.
> 2004-08-03 09:55:22.72 spid7 Opening file D:\MSSQL7\DATA\northwnd.mdf.
> 2004-08-03 09:55:22.82 spid5 Opening file D:\MSSQL7\DATA\msdblog.ldf.
> 2004-08-03 09:55:22.85 spid7 Opening file D:\MSSQL7\DATA\northwnd.ldf.
> 2004-08-03 09:55:22.87 spid6 Opening file D:\MSSQL7\DATA\pubs_log.ldf.
> 2004-08-03 09:55:23.55 spid1 Recovery complete.
> 2004-08-03 09:55:23.56 spid1 SQL Server's Unicode collation is:
> 2004-08-03 09:55:23.57 spid1 'English' (ID = 1033).
> 2004-08-03 09:55:23.57 spid1 comparison style = 196609.
> 2004-08-03 09:55:23.58 spid1 SQL Server's non-Unicode sort order is:
> 2004-08-03 09:55:23.58 spid1 'nocase_850' (ID = 42).
> 2004-08-03 09:55:23.58 spid1 SQL Server's non-Unicode character set is:
> 2004-08-03 09:55:23.58 spid1 'cp850' (ID = 2).
> 2004-08-03 09:57:00.02 spid6 Using 'xpstar.dll' version '1998.11.13' to
execute extended stored procedure 'sp_MSgetversion'.
> 2004-08-03 10:16:44.05 kernel SQL Server shutdown due to Ctrl-C or
Ctrl-Break signal.[vbcol=seagreen]
> ****************************************
***************
> Here's the Veritas log:
> ****************************************
********
> ****************************************
********
> "Keith Kratochvil" wrote:
>
directly[vbcol=seagreen]
Server[vbcol=seagreen]
found[vbcol=seagreen]
of[vbcol=seagreen]
C to[vbcol=seagreen]
service.[vbcol=seagreen]
other[vbcol=seagreen]
Backup[vbcol=seagreen]
things[vbcol=seagreen]
that[vbcol=seagreen]
my[vbcol=seagreen]

Can't Restore Database to New Server

Hi
I am attempting to transfer a database from my server to my customers
server. I took the backup file on CD however when I try to do a restore with
it I get the following error
Microsoft SQL-DMO (ODBC SQLState:42000)
Device activation error:The physical file name
'd:\sqldata\mssql\data\sa.mdf' may be incorrect.
File 'SA-V1_dat' cannot be restored to 'd:\sqlData\MSSQL\data\sa.mdf'. Use
WITH MOVE to identify a valid location for the file.
Device activation error. The physical file name
'd:\sqlData\MSSQL\data\sa_log.ldf' may be incorrect.
File 'SA-V1_log' cannot be restored to 'd:\sqlData\MSSQL\data\sa_log.ldf'.
Use WITH MOVE to identify a valid location for the file.
RESTORE DATABASE is terminating abnormally.
Suggestions how to overcome this?
Regards
Meir
Do what the error message say. Read about the RESTORE command in Books Online and use the MOVE
option to specify desired physical files names for your database files.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"mrrcomp" <mrrcomp@.discussions.microsoft.com> wrote in message
news:41F751D3-42E2-4292-B0F5-77E593B6C75D@.microsoft.com...
> Hi
> I am attempting to transfer a database from my server to my customers
> server. I took the backup file on CD however when I try to do a restore with
> it I get the following error
> Microsoft SQL-DMO (ODBC SQLState:42000)
> Device activation error:The physical file name
> 'd:\sqldata\mssql\data\sa.mdf' may be incorrect.
> File 'SA-V1_dat' cannot be restored to 'd:\sqlData\MSSQL\data\sa.mdf'. Use
> WITH MOVE to identify a valid location for the file.
> Device activation error. The physical file name
> 'd:\sqlData\MSSQL\data\sa_log.ldf' may be incorrect.
> File 'SA-V1_log' cannot be restored to 'd:\sqlData\MSSQL\data\sa_log.ldf'.
> Use WITH MOVE to identify a valid location for the file.
> RESTORE DATABASE is terminating abnormally.
> Suggestions how to overcome this?
> Regards
> Meir
>
|||As error says to you, use MOVE TO option of the RESTORE command to move
physical files to a new location. If you use EM, check the Options tab when
restoring, there you can specify a new location.
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"mrrcomp" <mrrcomp@.discussions.microsoft.com> wrote in message
news:41F751D3-42E2-4292-B0F5-77E593B6C75D@.microsoft.com...
> Hi
> I am attempting to transfer a database from my server to my customers
> server. I took the backup file on CD however when I try to do a restore
> with
> it I get the following error
> Microsoft SQL-DMO (ODBC SQLState:42000)
> Device activation error:The physical file name
> 'd:\sqldata\mssql\data\sa.mdf' may be incorrect.
> File 'SA-V1_dat' cannot be restored to 'd:\sqlData\MSSQL\data\sa.mdf'. Use
> WITH MOVE to identify a valid location for the file.
> Device activation error. The physical file name
> 'd:\sqlData\MSSQL\data\sa_log.ldf' may be incorrect.
> File 'SA-V1_log' cannot be restored to 'd:\sqlData\MSSQL\data\sa_log.ldf'.
> Use WITH MOVE to identify a valid location for the file.
> RESTORE DATABASE is terminating abnormally.
> Suggestions how to overcome this?
> Regards
> Meir
>
|||Hi,
To add on:
Try the below steps:-
From Query Analyzer:-
1. Using Restore filelistonly command identify the logical file names of the
database backup file
RESTORE FILELISTONLY from disk='c:\x.bak'
2. With the output of the above query use RESTORE database
RESTORE DATABASE <newdbname> from disk='c:\backup\x.bak'
WITH move 'logical_mdf_filename' to 'new physical name with path',
move 'logical_ldf_filename' to 'new physical log name with
Path'
After the restore refer sp_change_users_login to syncronize the Logins.
Thanks
Hari
SQL Server MVP
"mrrcomp" <mrrcomp@.discussions.microsoft.com> wrote in message
news:41F751D3-42E2-4292-B0F5-77E593B6C75D@.microsoft.com...
> Hi
> I am attempting to transfer a database from my server to my customers
> server. I took the backup file on CD however when I try to do a restore
> with
> it I get the following error
> Microsoft SQL-DMO (ODBC SQLState:42000)
> Device activation error:The physical file name
> 'd:\sqldata\mssql\data\sa.mdf' may be incorrect.
> File 'SA-V1_dat' cannot be restored to 'd:\sqlData\MSSQL\data\sa.mdf'. Use
> WITH MOVE to identify a valid location for the file.
> Device activation error. The physical file name
> 'd:\sqlData\MSSQL\data\sa_log.ldf' may be incorrect.
> File 'SA-V1_log' cannot be restored to 'd:\sqlData\MSSQL\data\sa_log.ldf'.
> Use WITH MOVE to identify a valid location for the file.
> RESTORE DATABASE is terminating abnormally.
> Suggestions how to overcome this?
> Regards
> Meir
>

Can't Restore Database to New Server

Hi
I am attempting to transfer a database from my server to my customers
server. I took the backup file on CD however when I try to do a restore with
it I get the following error
Microsoft SQL-DMO (ODBC SQLState:42000)
Device activation error:The physical file name
'd:\sqldata\mssql\data\sa.mdf' may be incorrect.
File 'SA-V1_dat' cannot be restored to 'd:\sqlData\MSSQL\data\sa.mdf'. Use
WITH MOVE to identify a valid location for the file.
Device activation error. The physical file name
'd:\sqlData\MSSQL\data\sa_log.ldf' may be incorrect.
File 'SA-V1_log' cannot be restored to 'd:\sqlData\MSSQL\data\sa_log.ldf'.
Use WITH MOVE to identify a valid location for the file.
RESTORE DATABASE is terminating abnormally.
Suggestions how to overcome this'
Regards
MeirDo what the error message say. Read about the RESTORE command in Books Onlin
e and use the MOVE
option to specify desired physical files names for your database files.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"mrrcomp" <mrrcomp@.discussions.microsoft.com> wrote in message
news:41F751D3-42E2-4292-B0F5-77E593B6C75D@.microsoft.com...
> Hi
> I am attempting to transfer a database from my server to my customers
> server. I took the backup file on CD however when I try to do a restore wi
th
> it I get the following error
> Microsoft SQL-DMO (ODBC SQLState:42000)
> Device activation error:The physical file name
> 'd:\sqldata\mssql\data\sa.mdf' may be incorrect.
> File 'SA-V1_dat' cannot be restored to 'd:\sqlData\MSSQL\data\sa.mdf'. Use
> WITH MOVE to identify a valid location for the file.
> Device activation error. The physical file name
> 'd:\sqlData\MSSQL\data\sa_log.ldf' may be incorrect.
> File 'SA-V1_log' cannot be restored to 'd:\sqlData\MSSQL\data\sa_log.ldf'.
> Use WITH MOVE to identify a valid location for the file.
> RESTORE DATABASE is terminating abnormally.
> Suggestions how to overcome this'
> Regards
> Meir
>|||As error says to you, use MOVE TO option of the RESTORE command to move
physical files to a new location. If you use EM, check the Options tab when
restoring, there you can specify a new location.
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"mrrcomp" <mrrcomp@.discussions.microsoft.com> wrote in message
news:41F751D3-42E2-4292-B0F5-77E593B6C75D@.microsoft.com...
> Hi
> I am attempting to transfer a database from my server to my customers
> server. I took the backup file on CD however when I try to do a restore
> with
> it I get the following error
> Microsoft SQL-DMO (ODBC SQLState:42000)
> Device activation error:The physical file name
> 'd:\sqldata\mssql\data\sa.mdf' may be incorrect.
> File 'SA-V1_dat' cannot be restored to 'd:\sqlData\MSSQL\data\sa.mdf'. Use
> WITH MOVE to identify a valid location for the file.
> Device activation error. The physical file name
> 'd:\sqlData\MSSQL\data\sa_log.ldf' may be incorrect.
> File 'SA-V1_log' cannot be restored to 'd:\sqlData\MSSQL\data\sa_log.ldf'.
> Use WITH MOVE to identify a valid location for the file.
> RESTORE DATABASE is terminating abnormally.
> Suggestions how to overcome this'
> Regards
> Meir
>|||Hi,
To add on:
Try the below steps:-
From Query Analyzer:-
1. Using Restore filelistonly command identify the logical file names of the
database backup file
RESTORE FILELISTONLY from disk='c:\x.bak'
2. With the output of the above query use RESTORE database
RESTORE DATABASE <newdbname> from disk='c:\backup\x.bak'
WITH move 'logical_mdf_filename' to 'new physical name with path',
move 'logical_ldf_filename' to 'new physical log name with
Path'
After the restore refer sp_change_users_login to syncronize the Logins.
Thanks
Hari
SQL Server MVP
"mrrcomp" <mrrcomp@.discussions.microsoft.com> wrote in message
news:41F751D3-42E2-4292-B0F5-77E593B6C75D@.microsoft.com...
> Hi
> I am attempting to transfer a database from my server to my customers
> server. I took the backup file on CD however when I try to do a restore
> with
> it I get the following error
> Microsoft SQL-DMO (ODBC SQLState:42000)
> Device activation error:The physical file name
> 'd:\sqldata\mssql\data\sa.mdf' may be incorrect.
> File 'SA-V1_dat' cannot be restored to 'd:\sqlData\MSSQL\data\sa.mdf'. Use
> WITH MOVE to identify a valid location for the file.
> Device activation error. The physical file name
> 'd:\sqlData\MSSQL\data\sa_log.ldf' may be incorrect.
> File 'SA-V1_log' cannot be restored to 'd:\sqlData\MSSQL\data\sa_log.ldf'.
> Use WITH MOVE to identify a valid location for the file.
> RESTORE DATABASE is terminating abnormally.
> Suggestions how to overcome this'
> Regards
> Meir
>

Cant pass 0 in stored procedure parameter

Hi
I have an if clause in my code to add the final parameter value to send to the database.

If Page.User.IsInRole("MICMS") Then
cmdCheckUser.Parameters.Add("@.C_ID", 0)
Else
cmdCheckUser.Parameters.Add("@.C_ID", Session("C_ID"))
End If

If the user is in the role, the error is triggered saying that @.C_IDis expected by the stored procedure. If i then change the value from 0to 10, the stored procedure works fine.
Is there any reason that the stored procedure is failing when the value 0 is used and not when any other value is used?
Thanking you in advance.Because the parameters.add is overridden based on what is passed (look at them when you hit the left parens). Since you are passing a numerical value of a low number, it thinks that it is the sqldbtype. You will need to cast the value to a string cStr(YOURVALUEHERE).
Nick

Can't make a connection to SQL Express as Server does not exist or access denied...

Hi

I have recently downloaded SQL Server Express which I have installed using Windows authentication mode. I cannot seem to be able to make a connection to SQL Server from Dreamweaver or Microsoft Visual Web Developer as I am getting an error Server does not exist or access is denied....

I am relatively new to all of this, so would appreciate any advice....

When I installed SQL Server Express I chose Windows Authentication. When I fire up SQL Express and view Security option - the installation program has set up an sa login with a random password. I did not set this password, but think this might be a reason why I can't connect. To rectify the problem I tried creating a new user in SQL Server Express with a password that I specified. On going in to check the settings, I notice SQL Server Express has gone and changed it from the password that I set up.

Now when I try and connect to SQL Server Express I specify the following in the connection paramaters

Server: BGIRL\SQLEXPRESS

Database The database name that I've created

User: the new user that I created

Password: the password I created as part of new user setup

Now I get error message Server does not exist or access denied

Is the problem to do with passwords or perhaps one of the many parameters one seems to have to set up? How can I change a password if I didn't create it? Is there anywhere I can reset it?

PLEASE HELP - I have literally spent 3 days attempting to get this working and am about to give up entirely. Due to my lack of experience, I need very specific step by step instructions..

DId you enable remote connections ? For a step by step guide, visit my site on the link below and visit the Screencast section, you will find the Enable Remote Connection on the list. Feel free to put a comment in there if you find it useful.

HTH, Jens SUessmeyer.

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

Thank you for your quick response. I viewed some of your screencasts, and they are very useful...

I had already enabled remote connections so this was not the problem, started up the SQL Browser too, and no luck.

I believe my problem is to do with SQL Express automatically setting up an sa password for me, I can't seem to be able to change this - any ideas as to how I can get around it? Also, when I create my own user in SQL Express, the software automatically changes the password so that I can't use the new user either. Is there a setting I need to change?

Regards Bron

|||There might be a firewall issue with your computer. By default SQL Server is listening on 1433 and uses 1434 additionally. Make sure these ones are not blocked. SQL Server won′t chnage the passwords for you, if you mean that after creating a new user the numbers of stars / dots in the password field differs from the one you entered, that is just for obfuscating the real length of the password and just means that there i something secure store, you don′t need to care about that.

HTH; Jens Suessmeyer.

http://www.sqlserver2005.de

Can't get unicode with ResultSet.getString()

Hi;
I am using MS Sql Server and it has an nvarchar field holding a name.
For ascii chars I get back the text in the database.
But if it is anything other than ascii, I get back a ? for each non
7-bit character. So a field with "a\u9f23b\u4011c" returns "a?b?c"
What do I need to set/do to get back the unicode values in the
database?
thanks - dave
david@.at-at-at@.windward.dot.dot.net
Windward Reports -- http://www.WindwardReports.com
Page 2 Stage -- http://www.Page2Stage.com
Enemy Nations -- http://www.EnemyNations.com
me -- http://dave.thielen.com
Barbie Science Fair -- http://www.BarbieScienceFair.info
(yes I have lots of links)
Here is some code that illustrates the problem - all return a?b?c
package net.windward.store.util.test;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.util.Properties;
import java.io.InputStream;
import java.io.Reader;
public class TestJdbcUnicode {
private static String className =
"com.microsoft.jdbc.sqlserver.SQLServerDriver" ;
private static String url =
"jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=StoreTest";
private static String username = "sa";
private static String password = "mmouse";
public static void main(String[] args) throws Exception {
String textInDb = "a?b?c"; // was the actual text (not
\u)
System.out.println("text = " + textInDb);
displayString(textInDb);
textInDb = "a\u98a8b\u0436c";
System.out.println("text = " + textInDb);
displayString(textInDb); // only correct display
System.out.println("Standard open");
Class.forName(className).newInstance();
Connection conn = DriverManager.getConnection(url,
username, password);
Statement stmt =
conn.createStatement(ResultSet.TYPE_SCROLL_INSENSI TIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("select * from Person
where PersonId = 25325");
displayResult(rs);
byte [] data = new byte[10];
InputStream is = rs.getAsciiStream("name");
int len = is.read(data);
//obj = rs.getBytes("name");
Reader rdr = rs.getCharacterStream("name");
char [] cbuf = new char[10];
len = rdr.read(cbuf);
System.out.println("Properties open");
Class.forName(className).newInstance();
Properties info = new Properties();
info.put("user", username);
info.put("password", password);
conn = DriverManager.getConnection(url, info);
stmt =
conn.createStatement(ResultSet.TYPE_SCROLL_INSENSI TIVE,
ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery("select * from Person where
PersonId = 25325");
displayResult(rs);
System.out.println("Properties open UTF-8");
Class.forName(className).newInstance();
info = new Properties();
info.put("user", username);
info.put("password", password);
info.put("charSet", "UTF-8");
conn = DriverManager.getConnection(url, info);
stmt =
conn.createStatement(ResultSet.TYPE_SCROLL_INSENSI TIVE,
ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery("select * from Person where
PersonId = 25325");
displayResult(rs);
System.out.println("Properties open UTF-16");
Class.forName(className).newInstance();
info = new Properties();
info.put("user", username);
info.put("password", password);
info.put("charSet", "UTF-16");
conn = DriverManager.getConnection(url, info);
stmt =
conn.createStatement(ResultSet.TYPE_SCROLL_INSENSI TIVE,
ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery("select * from Person where
PersonId = 25325");
displayResult(rs);
System.out.println("Properties open unicode");
Class.forName(className).newInstance();
info = new Properties();
info.put("user", username);
info.put("password", password);
info.put("charSet", "unicode");
conn = DriverManager.getConnection(url, info);
stmt =
conn.createStatement(ResultSet.TYPE_SCROLL_INSENSI TIVE,
ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery("select * from Person where
PersonId = 25325");
displayResult(rs);
}
private static void displayResult(ResultSet rs) throws
Exception {
if (! rs.next()) {
System.err.println("No results");
return;
}
String rtn = rs.getString("name");
displayString(rtn);
}
private static void displayString(String rtn) {
System.out.println("rtn = " + rtn);
System.out.print("rtn[] = ");
for (int ind=0; ind<rtn.length(); ind++)
System.out.print("x" +
Integer.toHexString((int)rtn.charAt(ind)) + " ");
}
}
david@.at-at-at@.windward.dot.dot.net
Windward Reports -- http://www.WindwardReports.com
Page 2 Stage -- http://www.Page2Stage.com
Enemy Nations -- http://www.EnemyNations.com
me -- http://dave.thielen.com
Barbie Science Fair -- http://www.BarbieScienceFair.info
(yes I have lots of links)
|||Yes this is a bug (I am assuming you are using the SQL Server 2005 JDBC
driver Beta2, perhaps not). We cut the getAsciiStream function we had a few
serious bugs in it that were not easy to resolve in a clear cut manner. The
JDBC spec is not super clear on how it is supposed to work.
For example, should getAsciiStream take the incoming TDS character data and
convert this to US-ASCII? What if the incoming data is in Japanese
collation and this is a lossy conversion, etc... there are lots of
situations where this could be lossy. Also, does it just mean send back the
raw bytes? Then why is it called Ascii Stream, etc... It makes my head
hurt bad when I am writing support for SQL Server's 1000+ TDS language
collations and I don't want to be lossy and corrupt customer data.
I think you can use getBytes to work around this for now, something like
this I believe ->
ByteArrayInputStream bas = new ByteArrayInputStream(rs.getBytes(2));
This might give you bytes in UNICODE that would necessitate you converting
to single byte stream, but this depends upon your back end collation. If
the collation is a simple 2:1 UNICODE -> Single Byte mapping then it is easy
to strip out every other byte.
You can do things like this as well:
InputStreamReader isr = new InputStreamReader(new
ByteArrayInputStream(rs.getBytes(2)), "US-ASCII");
Twiggle around with the "US-ASCII", maybe you want to convert using some
other encoding like UTF-8, etc...
Let me know which driver you are using and perhaps I can come up with a
better solution.
Also, I would like to hear your reasoning behind using getAsciiStream, why
do you find you need to use this API, let me know about this, this would be
good feedback for our team working on the SQL JDB 2005 driver.
Matt Neerincx [MSFT]
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.
"David Thielen" <david@.windward.net> wrote in message
news:c1srh1te9er4nh0h089oagua0frhgupuhm@.4ax.com...
> Here is some code that illustrates the problem - all return a?b?c
> package net.windward.store.util.test;
> import java.sql.DriverManager;
> import java.sql.Connection;
> import java.sql.Statement;
> import java.sql.ResultSet;
> import java.util.Properties;
> import java.io.InputStream;
> import java.io.Reader;
> public class TestJdbcUnicode {
> private static String className =
> "com.microsoft.jdbc.sqlserver.SQLServerDriver" ;
> private static String url =
> "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=StoreTest";
> private static String username = "sa";
> private static String password = "mmouse";
> public static void main(String[] args) throws Exception {
> String textInDb = "a?b?c"; // was the actual text (not
> \u)
> System.out.println("text = " + textInDb);
> displayString(textInDb);
> textInDb = "a\u98a8b\u0436c";
> System.out.println("text = " + textInDb);
> displayString(textInDb); // only correct display
> System.out.println("Standard open");
> Class.forName(className).newInstance();
> Connection conn = DriverManager.getConnection(url,
> username, password);
> Statement stmt =
> conn.createStatement(ResultSet.TYPE_SCROLL_INSENSI TIVE,
> ResultSet.CONCUR_UPDATABLE);
> ResultSet rs = stmt.executeQuery("select * from Person
> where PersonId = 25325");
> displayResult(rs);
> byte [] data = new byte[10];
> InputStream is = rs.getAsciiStream("name");
> int len = is.read(data);
> // obj = rs.getBytes("name");
> Reader rdr = rs.getCharacterStream("name");
> char [] cbuf = new char[10];
> len = rdr.read(cbuf);
>
> System.out.println("Properties open");
> Class.forName(className).newInstance();
> Properties info = new Properties();
> info.put("user", username);
> info.put("password", password);
> conn = DriverManager.getConnection(url, info);
> stmt =
> conn.createStatement(ResultSet.TYPE_SCROLL_INSENSI TIVE,
> ResultSet.CONCUR_UPDATABLE);
> rs = stmt.executeQuery("select * from Person where
> PersonId = 25325");
> displayResult(rs);
>
> System.out.println("Properties open UTF-8");
> Class.forName(className).newInstance();
> info = new Properties();
> info.put("user", username);
> info.put("password", password);
> info.put("charSet", "UTF-8");
> conn = DriverManager.getConnection(url, info);
> stmt =
> conn.createStatement(ResultSet.TYPE_SCROLL_INSENSI TIVE,
> ResultSet.CONCUR_UPDATABLE);
> rs = stmt.executeQuery("select * from Person where
> PersonId = 25325");
> displayResult(rs);
>
> System.out.println("Properties open UTF-16");
> Class.forName(className).newInstance();
> info = new Properties();
> info.put("user", username);
> info.put("password", password);
> info.put("charSet", "UTF-16");
> conn = DriverManager.getConnection(url, info);
> stmt =
> conn.createStatement(ResultSet.TYPE_SCROLL_INSENSI TIVE,
> ResultSet.CONCUR_UPDATABLE);
> rs = stmt.executeQuery("select * from Person where
> PersonId = 25325");
> displayResult(rs);
>
> System.out.println("Properties open unicode");
> Class.forName(className).newInstance();
> info = new Properties();
> info.put("user", username);
> info.put("password", password);
> info.put("charSet", "unicode");
> conn = DriverManager.getConnection(url, info);
> stmt =
> conn.createStatement(ResultSet.TYPE_SCROLL_INSENSI TIVE,
> ResultSet.CONCUR_UPDATABLE);
> rs = stmt.executeQuery("select * from Person where
> PersonId = 25325");
> displayResult(rs);
> }
> private static void displayResult(ResultSet rs) throws
> Exception {
> if (! rs.next()) {
> System.err.println("No results");
> return;
> }
> String rtn = rs.getString("name");
> displayString(rtn);
> }
> private static void displayString(String rtn) {
> System.out.println("rtn = " + rtn);
> System.out.print("rtn[] = ");
> for (int ind=0; ind<rtn.length(); ind++)
> System.out.print("x" +
> Integer.toHexString((int)rtn.charAt(ind)) + " ");
> }
> }
>
> david@.at-at-at@.windward.dot.dot.net
> Windward Reports -- http://www.WindwardReports.com
> Page 2 Stage -- http://www.Page2Stage.com
> Enemy Nations -- http://www.EnemyNations.com
> me -- http://dave.thielen.com
> Barbie Science Fair -- http://www.BarbieScienceFair.info
> (yes I have lots of links)

Cant get SProc to work

Hi

I can't seem to get this procedure to work correctly. Here's my code:

DECLARE @.PropertyDetails nvarchar (6)
Select @.PropertyDescription = PropertyDescription from Property where
ApplicationID = @.applicationid
If @.PropertyDescription is not null or @.PropertyDescription <> ''
Begin
If (select isnumeric(PropertyDescription) from Property where ApplicationID =
@.applicationid) = 1
INSERT INTO #errors VALUES (1410,@.ApplicationID, 0, 'Y')
ELSE
INSERT INTO #errors values (1410, @.ApplicationID, 0, 'N')
End

I am trying to bring up an error advising users not to capture alphabets in a
field called Property Description.
I need to bring up the error from the #ERRORS table with the rule number 1410.

My Syntax checks successful, but my error does not come up for the users. Am
I missing something?

Thanks for any help at all, guys.
Driesen Pillay

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200508/1If PropertyDescription should only have numbers, then why is it a
character data type? What do you consider "numeric" to mean - what
digits/characters are allowed? You'll probably have to give some more
details of what you're trying to do and what your data looks like to
get a good answer - can you post a short script which others can
actually execute themselves?

http://www.aspfaq.com/etiquette.asp?id=5006

Simon|||Hi Simon

Sorry about that. Even though I only want numbers in this field, I don't want
to stop the user from inputing alphabets (management red tape). Eg: If the
user enters "ERF 1234". I need to raise the error "Remove aplhabets". We have
an HTML interface with SQL running the actual background programming, so
unfortunately I can't supply you with a script. I know I didn't give you much
to go on, so I might scrap this rule, because it doesn't really make sense to
have it there. What would you recomend? Is this a waste of time?

Thanks for the help.
Driesen

Simon Hayes wrote:
>If PropertyDescription should only have numbers, then why is it a
>character data type? What do you consider "numeric" to mean - what
>digits/characters are allowed? You'll probably have to give some more
>details of what you're trying to do and what your data looks like to
>get a good answer - can you post a short script which others can
>actually execute themselves?
>http://www.aspfaq.com/etiquette.asp?id=5006
>Simon

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200508/1|||In general, this type of input validation would belong in the front
end, not the database; the database would have an integer column, and
the front end would make sure that only integer data is passed to it.

The problem with your current solution is that you have to load the
data into the database, check it, populate an error table, present the
contents of the error table to the user, wait for the user to enter new
values, and then repeat the process until the user gets it right. This
seems to be a lot of work compared to a simple input mask in your user
interface. Assuming that by "HTML interface" you mean ASP, PHP or
something similar, then this should be fairly easy.

But I appreciate that I don't know all the details of your application
or your environment, so you might try something like the code below
(untested).

Simon

declare @.PropertyDescription nvarchar (6)

select @.PropertyDescription = PropertyDescription
from dbo.Property
where ApplicationID = @.Applicationid

if coalesce(@.PropertyDescription, '') <> ''
and exists (
select *
from dbo.Property
where ApplicationID = @.ApplicationID
-- find non-numeric characters
and rtrim(ltrim(PropertyDescription)) not like '%[^0-9]%'
)
INSERT INTO #errors VALUES (1410, @.ApplicationID, 0, 'N')
ELSE
INSERT INTO #errors values (1410, @.ApplicationID, 0, 'Y')
End|||Oops - I think I put 'N' and 'Y' the wrong way round, but you should
see that from your data.

Simon|||Thanks very much for the help, Simon. I will give that a try.
Thanks again.

Driesen

--
Message posted via http://www.sqlmonster.com