transfer Data From Acces: February 2012

Friday, February 24, 2012

How can I use SMO within a CLR stored procedure

Hi,

I have a CLR stored procedure that I wish to use SMO.

However this caused a number of problems :

1. Add reference didnt show SMO. So I tracked down a redistributable i.e. SQL Server 2005 Feature pack to install - still didnt show up. However, in a normal .net project they do show up so I erhm hacked the SQL server database project file to manually add the references.

Great have the reference, added the include statements - intellisense working code away - even builds :)

using Microsoft.SqlServer.Management;
using Microsoft.SqlServer.Management.Smo;

2. Tried to deploy and came up with the error :

Error 1 Assembly 'microsoft.sqlserver.connectioninfo, version=9.0.242.0, culture=neutral, publickeytoken=89845dcd8080cc91.' was not found in the SQL catalog.

Shot down in flames..

Would very much appreciate if someone could point me in the right direction to utilising SMO or point out where I am going wrong - other than dont hack the project file :)

Note the reason why I wish to use SMO is so I can read sql files from disk and execute them in the database i.e. create stored procedures etc.

The standard mechanism I have found i.e. a server connection and using ExectueNonQuery fails as the script files use batch seperators in the text i.e. 'GO'.

I have also tried using xp_cmdshell but any error reporting is virtually non existant other than parsing the output which will be truly horrific.

Open to suggestions :)SMO is not supported inside SQL Server. Normally what you can do in a scenario when a system dll (like SMO) isn't supported, is to manually catalog the dll in the database (that way it would show up in th VS SQL Server project when you try to reference it) and be able to use it.

In the case of SMO however it won't work period.

Niels|||Ah, thanks Niels.|||

Hi,

(warning: CLR newbie)

I am trying to use Microsoft.Office.Interop.Excel within my CLR stored procedure. I added <Reference Include="Microsoft.Office.Interop.Excel" /> as an <ItemGroup> in the project file. That allowed me to have access to the namespace and build the stored procedure successfully. However, when I try to deploy the stored procedure, I get the same error as the initial post "Assembly 'microsoft.office.interop.excel, version=12.0.0.0, culture=neutral, publickeytoken=71e9bce111e9429c.' was not found in the SQL catalog."

So how do you "manually catalog the dll in the database" so that you can successfully deploy? Are you refering to the "CREATE ASSESSMBLY" command?

Any help is greatly appreciated.

Rocco

|||

Rocco Mastrangelo wrote:
Hi,
(warning: CLR newbie)
I am trying to use Microsoft.Office.Interop.Excel within my CLR stored procedure. I added <Reference Include="Microsoft.Office.Interop.Excel" /> as an <ItemGroup> in the project file. That allowed me to have access to the namespace and build the stored procedure successfully. However, when I try to deploy the stored procedure, I get the same error as the initial post "Assembly 'microsoft.office.interop.excel, version=12.0.0.0, culture=neutral, publickeytoken=71e9bce111e9429c.' was not found in the SQL catalog."
So how do you "manually catalog the dll in the database" so that you can successfully deploy? Are you refering to the "CREATE ASSESSMBLY" command?

Yes, you have to run CREATE ASSEMBLY. Be aware that you probably have to create the assembly with a permission set of UNSAFE. This in turn requires certain permissions/settings in the database. See this thread for more info.

Niels

How can I use SMO within a CLR stored procedure

Hi,

I have a CLR stored procedure that I wish to use SMO.

However this caused a number of problems :

Great have the reference, added the include statements - intellisense working code away - even builds :)

using Microsoft.SqlServer.Management;
using Microsoft.SqlServer.Management.Smo;

2. Tried to deploy and came up with the error :

Error 1 Assembly 'microsoft.sqlserver.connectioninfo, version=9.0.242.0, culture=neutral, publickeytoken=89845dcd8080cc91.' was not found in the SQL catalog.

Shot down in flames..

Would very much appreciate if someone could point me in the right direction to utilising SMO or point out where I am going wrong - other than dont hack the project file :)

Note the reason why I wish to use SMO is so I can read sql files from disk and execute them in the database i.e. create stored procedures etc.

The standard mechanism I have found i.e. a server connection and using ExectueNonQuery fails as the script files use batch seperators in the text i.e. 'GO'.

I have also tried using xp_cmdshell but any error reporting is virtually non existant other than parsing the output which will be truly horrific.

In the case of SMO however it won't work period.

Niels|||Ah, thanks Niels.|||

Hi,

(warning: CLR newbie)

So how do you "manually catalog the dll in the database" so that you can successfully deploy? Are you refering to the "CREATE ASSESSMBLY" command?

Any help is greatly appreciated.

Rocco

|||

Rocco Mastrangelo wrote:
Hi,
(warning: CLR newbie)
I am trying to use Microsoft.Office.Interop.Excel within my CLR stored procedure. I added <Reference Include="Microsoft.Office.Interop.Excel" /> as an <ItemGroup> in the project file. That allowed me to have access to the namespace and build the stored procedure successfully. However, when I try to deploy the stored procedure, I get the same error as the initial post "Assembly 'microsoft.office.interop.excel, version=12.0.0.0, culture=neutral, publickeytoken=71e9bce111e9429c.' was not found in the SQL catalog."
So how do you "manually catalog the dll in the database" so that you can successfully deploy? Are you refering to the "CREATE ASSESSMBLY" command?

Niels

How can I use SMo in VS 2002 SP1 c# applications?

I have got error: cannot add reference to Microsoft.sqlserver.smo.dll .....

I understand VS2005 is bettrer than VS2002 but I have what I have.

Thank you.

You need VS2005 as SMO depends on .NET Framework 2.0.|||

what if i install Framework 2.0 on Visual Studio 2003?

How can I use several resultsets from stored procedure?

I have create stored procedure that returns 2 resultsets. When I configure OLE DB Source to use this procedure I can not add second output for he source. I get following error:

TITLE: Microsoft Visual Studio

Error at Data Flow Task [OLE DB Source [1]]: An output cannot be added to the outputs collection.

ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC020800F (Microsoft.SqlServer.DTSPipelineWrap)

BUTTONS:

How to add second output?

The stock OLEDB source component supports one non-error output.

To populate multiple outputs from a source component (perhaps to avoid a double extract), you can write a script source transform with as many outputs as necessary. Building multi output transforms (script and custom) is not much more difficult than a single output source component.

Furthermore, you can write a custom source tranform if you wish to re-use the code (aka toolbox resident) more easily than cut/paste re-use of script components.

If a little coding is not up your alley, then you're stuck with a single output.

Of course there are probably workarounds to simulate two outputs, such as interleaving/marking the columns of the two result sets such that they form one result set you can later conditionally split, but that is likely beside the point.

How can I use SELECT DISTINCT but maintain the original order

Say I have this result set with two fields (numbers and letters):
1 A
3 A
1 B
2 B

The result set is ordered by the letters column. How can I select the distinct numbers from the result set but maintain the current order?

When I try select distinct Number from MyResultSet it will reorder the new result set by the Number field and return
1
2
3

However, I'd like maintain the Letter order and return
1
3
2

hmm.. tricky one. I think this is not exactly what you were hoping for but it's the only thing i could come up with that works for sure.

Remember that database theory thinks or records as collection in a mathematical sense, and these do not have an order.

Good luck,

John

Code Snippet

declare @.KeepOrder table (Num int, Ord int)
declare @.Num int,
@.Ord int

declare c cursor fast_forward for

-- change this query to your table
select Num from Test
order by Let

open c
fetch next from c into @.num
set @.Ord = 1

while @.@.fetch_status = 0
begin
if not exists(select num from @.Keeporder where Num = @.Num)
begin
insert into @.Keeporder(num, ord) values(@.num, @.Ord)
set @.Ord = @.Ord + 1
end
fetch next from c into @.num
end

close c
deallocate c

select num
from @.Keeporder
order by ord

|||

Try:

create table dbo.t1 (

c1 int not null,

c2 char(1) not null

)

insert into dbo.t1 values(1, 'A')

insert into dbo.t1 values(3, 'A')

insert into dbo.t1 values(1, 'B')

insert into dbo.t1 values(2, 'B')

;with cte

(

select

c1,

c2,

row_number() over(partition by c1 order by c2) as rn

from

dbo.t1

)

select

from

cte

where

rn = 1

order by

drop table dbo.t1

AMB

|||

You could try something along these lines... Bruce

declare @.t1 table (ID int NULL, CD varchar(1) NULL)

insert into @.t1 (ID, CD) select 1, 'A'

insert into @.t1 (ID, CD) select 3, 'A'

insert into @.t1 (ID, CD) select 1, 'B'

insert into @.t1 (ID, CD) select 2, 'B'

select * from @.t1

;

WITH work_tbl AS

(

SELECT ROW_NUMBER() OVER (ORDER BY CD) AS 'RowNumber',

ID, CD

FROM @.t1

)

SELECT min(RowNumber), ID

FROM work_tbl

GROUP BY ID

ORDER BY 1

|||I really have to start looking into these new 2005 functions....|||great, thanks Bruce and HunchBack! that's exactly what I was looking for.

Is the WITH clause new for 2005? I haven't seen it before, but it's a lot easier than creating and dropping temporary tables.|||Yes, I believe the WITH is new.. it's nice for clearer code when first making a derived table.. and then referencing it multiple times in the main query. Note that you must put the semi-colon just before the WITH, strange... It won't work without that semi-colon... Bruce

How can I use SELECT DISTINCT and maintain the original order

Say I have a result set with two fields numbers and letters.

1 A
3 A
1 B
2 B

The result set is ordered by the letters column. How can I select the distinct numbers from the result set but maintain the current order?

When I try

select distinct Number from MyResultSetit will reorder the new result set by the Number field and return

1
2
3

However, I'd like maintain the Letter order and return

1
3
2

try this

create

table #test(nnint,llchar(1))

insert

into #test

values

(1,'A')

insert

into #test

values

(3,'A')

insert

into #test

values

(1,'B')

insert

into #test

values

(2,'B')

select

nnfrom(select nn,min(ll)llfrom #test

group

by nn

)

order

by ll

drop

table #test

How can I use Reporting Services on ASP.NET Pages?

Hi all,

Could you tell the correct way to use Reporting Services on ASP.NET pages?

Now I wanna create an ASP.NET page to show some reports, and I hope to control all logic by program, so I need know the API between ASP.NET and Reporting Services.

For example, I wanna pass some data to Reporting Services via API, and then Reporting Services handle the data and return reports to me, and then I can show the reports on my web pages.

Please tell me how?

Hi Xinwen.

You can use the Report control and give hime the link to your report so it will show in side the control.

You don't interact with the report like you described - You can interact with the report with PARAMETERS passing through the URL.

So whan you give the report control the report url you can add to that url parameters that the report should get (like person id or project number) and also to change things in the report (like open it in diffrent ways, change the zoom, automaticlly export to pdf and so on) with the url command parameters.

for exm:
ttp://reportserver/myreportfolder/reportname.rdl?rc:format=PDF will export it to pdf.

search google about "report service url parameters"

good luck,
Roy.|||Thanks Roy!

How can I use Reporting Services on ASP.NET Pages?

Hi all,

Could you tell the correct way to use Reporting Services on ASP.NET pages?

Now I wanna create an ASP.NET page to show some reports, and I hope to control all logic by program, so I need know the API between ASP.NET and Reporting Services.

For example, I wanna pass some data to Reporting Services via API, and then Reporting Services handle the data and return reports to me, and then I can show the reports on my web pages.

Please tell me how?

Hi Xinwen.

You can use the Report control and give hime the link to your report so it will show in side the control.

You don't interact with the report like you described - You can interact with the report with PARAMETERS passing through the URL.

for exm:
ttp://reportserver/myreportfolder/reportname.rdl?rc:format=PDF will export it to pdf.

search google about "report service url parameters"

good luck,
Roy.
|||Thanks Roy!|||

Hi,

I tried with this but instead of prompting to save Excel File,it is displaying the report

http://localhost/Reports/Pages/Report.aspx?ItemPath=NameOfReport&rs:Command=Renderl&rs:Format=Excel

-Thanks,

Digant Desai

|||

In Visual studio 2005,there is a report veiwer contrlo to view your RDL reports.

U need to link report viewer control to corresponding report in Reporting service project.This u can do

by setting Propertis of report viewer.

You can also pass parameters form ASP page to rdl file.

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class Default2 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
this.ReportViewer2.Visible = false;
}
protected void Button1_Click(object sender, EventArgs e)
{

this.ReportViewer2.Visible = true;
this.ReportViewer2.ServerReport.ReportServerUrl = new System.Uri("http://localhost/ReportServer");

string strReport = "/ReportProject14/Report1";

this.ReportViewer2.ServerReport.ReportPath = strReport;

Microsoft.Reporting.WebForms.ReportParameter[] RptParameters =
new Microsoft.Reporting.WebForms.ReportParameter[3];

string strComp = this.DropDownList1.SelectedItem.Value;
RptParameters[0] =
new Microsoft.Reporting.WebForms.ReportParameter("INTCOMPANYKEY", strComp);

string strFacility = this.DropDownList3.SelectedItem.Value;
RptParameters[1] =
new Microsoft.Reporting.WebForms.ReportParameter("INTFACILITYKEY", strFacility);

string strActivity = this.DropDownList2.SelectedItem.Value;
RptParameters[2] =
new Microsoft.Reporting.WebForms.ReportParameter("Activity", strActivity);

this.ReportViewer2.ServerReport.SetParameters(RptParameters);
this.ReportViewer2.ServerReport.Refresh();
}
}

How can I use regular expressions in WHERE clause directly?

select * from mytbl where myfld like '[0-9]*key'

Sorry - AFAIK SQL does not allow regular expressions in the WHERE claus.

You have to search for the closest match to your regex which you can express with the LIKE patterns and do the rest of the filtering on the client side.

--
SvenC

|||

this is not allowed..there is a turnaround though..

u can prepare the query and assign it to a variable and then use the 'execute' command to run it, takin ur example..

declare @.sql varchar(100)

set @.sql = 'select * from mytbl where myfld like '[0-9]*key''

execute(@.sql)

in addition u can also use variables while to prepare a query..eg u can take the column to compare in a variable to make it more dynamic

declare @.column varchar(10)

set @.column = myfld

set @.sql = 'select * from mytbl where' + @.column+ ' like '[0-9]*key''

|||

SQL Server doesn't directly support regular expressions. But it's easy to add them: just create a User Defined Function (UDF) which maps onto a .Net method. And >net does support regular expressions out of the box. To get an idea of how to do this, check this article:

http://www.u2u.be/Article.aspx?ART=RegExp

How can i use Recursive CTE here

Hello Everyone,

I have a purchase order table that holds say 2 columns.PO andOrgPO. That isPurchase Order # andOriginal Purchase Order # respectively. Assume i have the following rows in the table.

PO OrgPO

-- --

po1 NULL

co1 po1

co2 co1

co3 co2

po2 NULL

cpo1 po2

po3 NULL

Now what i would like to report in the output is the PO and along with the lastly generated change order for that po. For eg,

PO LastCO

-- --

po1 co3

po2 cpo1

po3 po3

Currently i 'm using function to achieve this effect and i believe this is not the efficient way. I would like to generate this in a much efficient way. Please help me to achieve this.

Assume your table named 'pot' and here's the code below:

WITH x (newPO, newLastCO, depth)AS(SELECTCASEWHEN OrgPOISNULLTHEN POELSE OrgPOEND AS newPO, POAS newLastCO, 0AS depthFROM potWHERE OrgPOISNULLUNIONALLSELECT x.newPO, POAS newLastCO, x.depth + 1FROM pot pJOIN xON x.newLastCO = p.OrgPO)SELECT x.newPO, x.newLastCOFROM xINNERJOIN (SELECT newPO,MAX(depth)AS deepestFROM XGROUP BY newPO)AS yON x.newPO = y.newPOAND x.depth = y. deepestORDER BY x.newPO

|||

Hi jackyang,

Thanx for the solution man Yes

How can I use Profile.UserName as a parameter in a SqlDataSource

I have looked and tried with no luck on using the Profile.UserName in an SQLDataSource Update select statement. and anyone please help me with this?

UpdateCommand="UPDATE tblDocumentsSET DocumentTypeID = @.DocumentTypeID, DocDescription = @.DocDescription, DocLocation = @.DocLocation, DocStartDate = @.DocStartDate, DocEndDate = @.DocEndDate, LastUpdate =GETDATE(), LastUpdateBy = @.ProfileUserWHERE (DocumentID = @.DocumentID)"

 <UpdateParameters> <asp:Parameter Name="DocumentID" /> <asp:Parameter Name="DocumentTypeID" /> <asp:Parameter Name="DocDescription" /> <asp:Parameter Name="DocLocation" /> <asp:Parameter Name="DocStartDate" /> <asp:Parameter Name="DocEndDate" /> <asp:Parameter Name="ClientID" /> <asp:Parameter Name="ProfileUser" /> </UpdateParameters>

Hi,

you should be able to use the <asp:ProfileParameter>.

Grz, Kris.

|||Thank You Very Much!

How can i use oracle package as datasource?

as title,is it possible?
thanks for help!Yes, Oracle stored procedures are supported. For more info see this post:
http://groups.google.com/groups?hl=en&lr=&frame=right&th=8cfc256acd0c552f&seekm=e1ec01c43c1d%24e418df90%24a301280a%40phx.gbl#link2
--
Rajeev Karunakaran [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"rui" <ruidong@.21cn.com> wrote in message
news:uFIcYM51EHA.1292@.TK2MSFTNGP10.phx.gbl...
> as title,is it possible?
> thanks for help!
>

How can I use one table for mutliple datasets?

Hi All,
I have a table and the dataset it populating all but four cells. These
four cells I want to populate using data from a different dataset (I
couldn;t figure out a query to do what I needed). The main dataset
returns a value I can use as a parameter in the second dataset. Is this
clear? Is this possible. I'm new to this type of reporting so please
bear with me. Thanks!
Kind regards - FredYou can create a sub report and put that in a cell of the table object. I do
this all the time. Sub reports are the only way to link two datasets
together.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<fblock@.tellurian.com> wrote in message
news:1164903187.594743.309230@.l39g2000cwd.googlegroups.com...
> Hi All,
> I have a table and the dataset it populating all but four cells. These
> four cells I want to populate using data from a different dataset (I
> couldn;t figure out a query to do what I needed). The main dataset
> returns a value I can use as a parameter in the second dataset. Is this
> clear? Is this possible. I'm new to this type of reporting so please
> bear with me. Thanks!
> Kind regards - Fred
>|||Thanks Bruce...
I see that I cannot merge cells vertically and the sub-report would
need to be two cells (one over the other). Have you a trick for this.
Also - why is it that my dataset field is "First(field_name)"... Why
the "First()"? The VS IDE was complaining of an aggregate which "sort
of" makes sense but my dataset was onle able to return one row (it
used: TOP 1).
Thanks again!
Kind regards - Fred|||Your subreport can have multiple cells. You create your subreport using the
table control with one cell over the other. You embed it into a single sell
and that cell grows vertically. However, if you are wanting the bottom cell
to line up with the second detail row you can't do it. It will expand the
cell you put it in and then your second detail row of the master will show.
You could have two subreports but remember, it is calling this subreport for
every row.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"frblock" <frblock@.yahoo.com> wrote in message
news:1164915731.670399.153450@.79g2000cws.googlegroups.com...
> Thanks Bruce...
> I see that I cannot merge cells vertically and the sub-report would
> need to be two cells (one over the other). Have you a trick for this.
> Also - why is it that my dataset field is "First(field_name)"... Why
> the "First()"? The VS IDE was complaining of an aggregate which "sort
> of" makes sense but my dataset was onle able to return one row (it
> used: TOP 1).
> Thanks again!
> Kind regards - Fred
>|||frblock,
You asked why you needed to have an aggregate function in the field
expression. Perhaps this will help.
When you create a report through the report wizard, it automatically
creates a table on your report. That table is actually "bound" to a
dataset. This binding allows you to reference fields from that dataset
by just saying "=Fields!fieldName.Value". This binding also tells the
table to create 1 detail row for each datarow in the dataset.
Microsoft included the ability to reference other datasets from a table
that has already been bound to a different dataset, but there is no way
to "relate" the rows from the 2 datasets. You would need a sub-report
to do this. As a result, you have to use a scalar function to ensure
that you get only 1 row and 1 column back, and you also have to
explicitly state which dataset you want to use. The formula looks like
this: "=Sum(Fields!fieldName.Value, "Dataset1").
If you are trying to reference fields that belong to the dataset that
is already assigned to your table, in the Edit Expression window, you
should select the "Fields (Dataset1)" option. If you are trying to
reference fields from other datasets, then you choose "Datasets". The
default aggregate function for integer fields is Sum, and the default
for string fields is First. You can change the default. For example, if
you had a query that said "SELECT COUNT(*) FROM tableName", you could
use First, Min, Max, Avg, or Sum. My opinion, just stick with First.
HTH,
Josh
frblock wrote:
> Thanks Bruce...
> I see that I cannot merge cells vertically and the sub-report would
> need to be two cells (one over the other). Have you a trick for this.
> Also - why is it that my dataset field is "First(field_name)"... Why
> the "First()"? The VS IDE was complaining of an aggregate which "sort
> of" makes sense but my dataset was onle able to return one row (it
> used: TOP 1).
> Thanks again!
> Kind regards - Fred

How can I use MDX functions in SQL Query Analyzer (Client Tool)

I'm very new to using MS-SQL stuffs and the IT dept here doesn't have any
DBMA to speak of. Thus, I'm a bit on my own - except for you helpful
people...
I just installed the Client Tools on my workstation and tried a simple query:
SELECT IIf(TBL.fld is NULL, "-N/A-", TBL.fld) FROM TBL;
and get the following error message:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'is'.
According to the help files, this should work, but clearly not. Is there
something(s) I need to enable, install, configure, tweek, activate, cajole,
or otherwise beg into acquiesence?
See reply in microsoft.public.sqlserver.setup
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"slintz" <slintz@.discussions.microsoft.com> wrote in message
news:A2D102C1-1023-413A-94D3-B82440DB08EC@.microsoft.com...
> I'm very new to using MS-SQL stuffs and the IT dept here doesn't have any
> DBMA to speak of. Thus, I'm a bit on my own - except for you helpful
> people...
> I just installed the Client Tools on my workstation and tried a simple
> query:
> SELECT IIf(TBL.fld is NULL, "-N/A-", TBL.fld) FROM TBL;
> and get the following error message:
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'is'.
>
> According to the help files, this should work, but clearly not. Is there
> something(s) I need to enable, install, configure, tweek, activate,
> cajole,
> or otherwise beg into acquiesence?
>

How can I use mdf files from sql server 2005 exp. edition.

Hello,

I am a candidate for web programming..I would like to desing a website and I have a good host which is supporting .net 2.0 and sql server 2005 service. So I would like to ask you

1) how can I use sql server express edition's mdf database file there. or Can I store and use this "mdf"s in ftp folder? What should I do. ..

2) I have only one database "right" there and should I manage everything in one database. For example asp.net configuration manager created ASPNETDB.mdf and ldf...so if Can you inform me about this issue?

Maybe my questions is not advance, they are very basic but I want to learn

Thank you very much for your helps.

Hi,

From your description, it seems that you want to deploy the database in your SQLExpress to the SQLServer2005 database on your host server, right?

If so, I suggest that you should use some tools to achieve that instead of attaching MDF manually. First, you may use aspnet_regsql.exe tool convert the database in SQLExpress to your local SQLServer series database. In you case it's SQLServer2005. After that, use SQLServer export and import wizard to upload the database (including table and schema) to the SQLServer2005 instance on your server.

For aspnet_regsql.exe, see:

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

Thanks.

How can I use getdate() to be an input parameter in a sproc?

I am trying to write a sproc that automatically uses the system date (i.e. -
getdate()) as an input parameter. Even though it displays it does not beha
ve like an input parameter.Casey
Do you need the entire date down the minutes and seconds, in other words, an
exact snapshot
of the date?
If not, you can just refer to GETDATE() right within your procedure and
bypass the
parameter part.
"Casey" <cevans2@.edd.ca.gov> wrote in message
news:57B71663-C451-45D0-BB67-FE2B21BCCA25@.microsoft.com...
> I am trying to write a sproc that automatically uses the system date
(i.e. - getdate()) as an input parameter. Even though it displays it does
not behave like an input parameter.|||Hi,
Use the below sample,
alter proc test_proc2
as
begin
declare @.to_day smalldatetime
set @.to_day = getdate()
select @.to_day
end
Incase if it is a must to have date as input parameter then,
alter proc test_proc2 @.to_day datetime = '01/01/1900'
as
begin
set @.to_day = getdate()
select @.to_day
end
Thanks
Hari
MCDBA
"Casey" <cevans2@.edd.ca.gov> wrote in message
news:57B71663-C451-45D0-BB67-FE2B21BCCA25@.microsoft.com...
> I am trying to write a sproc that automatically uses the system date
(i.e. - getdate()) as an input parameter. Even though it displays it does
not behave like an input parameter.

How can I use full memory in x64 OS with 32bit SQL

I have the following:
Windows Server 2003 Enterprise x64
SQL Server Enterprise x32
In order for SQL to use all 16 GB of ram available are there in special
settings such as /pae, /awe, or /3GB that I need to use?
When using certain monitor tools it shows my memory usage at 100 percent
when that doesn't seem right.
Hope that is enough info.
On 64-bit platforms, you don't use the /PAE /3GB switches. You do have to
use sp_configure:
sp_configure 'awe enabled', 1
go
reconfigure with override
go
sp_configure 'max server memory (MB)', 14336
go
reconfigure with override
go
Stop and start SQL Server. I chose the max memory of 14GB, since you should
leave 2GB for the OS.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"jason7655" <jason7655@.discussions.microsoft.com> wrote in message
news:619021D5-16A6-4FB4-AF44-E1E0DB1209B2@.microsoft.com...
I have the following:
Windows Server 2003 Enterprise x64
SQL Server Enterprise x32
In order for SQL to use all 16 GB of ram available are there in special
settings such as /pae, /awe, or /3GB that I need to use?
When using certain monitor tools it shows my memory usage at 100 percent
when that doesn't seem right.
Hope that is enough info.
|||"Tom Moreau" wrote:

> On 64-bit platforms, you don't use the /PAE /3GB switches. You do have to
> use sp_configure:
> sp_configure 'awe enabled', 1
> go
> reconfigure with override
> go
> sp_configure 'max server memory (MB)', 14336
> go
> reconfigure with override
> go
> Stop and start SQL Server. I chose the max memory of 14GB, since you should
> leave 2GB for the OS.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "jason7655" <jason7655@.discussions.microsoft.com> wrote in message
> news:619021D5-16A6-4FB4-AF44-E1E0DB1209B2@.microsoft.com...
> I have the following:
> Windows Server 2003 Enterprise x64
> SQL Server Enterprise x32
> In order for SQL to use all 16 GB of ram available are there in special
> settings such as /pae, /awe, or /3GB that I need to use?
> When using certain monitor tools it shows my memory usage at 100 percent
> when that doesn't seem right.
> Hope that is enough info.
>
awe enable is set to :
run value: 0
Config Value:0
Minimum: 0
Maximum: 1
Currently my "mas server memory" is set to :
run value: 2147483647
Config Value:2147483647
Minimum: 4
Maximum:2147483647
Does your suggestion remain the same or would I just removed the max server
portion of that statement?
|||Just run my code and restart SQL Server.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"jason7655" <jason7655@.discussions.microsoft.com> wrote in message
news:8F14CDBE-1D38-4DBD-AE07-2B760EE0901A@.microsoft.com...
"Tom Moreau" wrote:

> On 64-bit platforms, you don't use the /PAE /3GB switches. You do have to
> use sp_configure:
> sp_configure 'awe enabled', 1
> go
> reconfigure with override
> go
> sp_configure 'max server memory (MB)', 14336
> go
> reconfigure with override
> go
> Stop and start SQL Server. I chose the max memory of 14GB, since you
> should
> leave 2GB for the OS.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "jason7655" <jason7655@.discussions.microsoft.com> wrote in message
> news:619021D5-16A6-4FB4-AF44-E1E0DB1209B2@.microsoft.com...
> I have the following:
> Windows Server 2003 Enterprise x64
> SQL Server Enterprise x32
> In order for SQL to use all 16 GB of ram available are there in special
> settings such as /pae, /awe, or /3GB that I need to use?
> When using certain monitor tools it shows my memory usage at 100 percent
> when that doesn't seem right.
> Hope that is enough info.
>
awe enable is set to :
run value: 0
Config Value:0
Minimum: 0
Maximum: 1
Currently my "mas server memory" is set to :
run value: 2147483647
Config Value:2147483647
Minimum: 4
Maximum:2147483647
Does your suggestion remain the same or would I just removed the max server
portion of that statement?

How can I use full memory in x64 OS with 32bit SQL

I have the following:
Windows Server 2003 Enterprise x64
SQL Server Enterprise x32
In order for SQL to use all 16 GB of ram available are there in special
settings such as /pae, /awe, or /3GB that I need to use?
When using certain monitor tools it shows my memory usage at 100 percent
when that doesn't seem right.
Hope that is enough info.On 64-bit platforms, you don't use the /PAE /3GB switches. You do have to
use sp_configure:
sp_configure 'awe enabled', 1
go
reconfigure with override
go
sp_configure 'max server memory (MB)', 14336
go
reconfigure with override
go
Stop and start SQL Server. I chose the max memory of 14GB, since you should
leave 2GB for the OS.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"jason7655" <jason7655@.discussions.microsoft.com> wrote in message
news:619021D5-16A6-4FB4-AF44-E1E0DB1209B2@.microsoft.com...
I have the following:
Windows Server 2003 Enterprise x64
SQL Server Enterprise x32
In order for SQL to use all 16 GB of ram available are there in special
settings such as /pae, /awe, or /3GB that I need to use?
When using certain monitor tools it shows my memory usage at 100 percent
when that doesn't seem right.
Hope that is enough info.|||"Tom Moreau" wrote:

> On 64-bit platforms, you don't use the /PAE /3GB switches. You do have to
> use sp_configure:
> sp_configure 'awe enabled', 1
> go
> reconfigure with override
> go
> sp_configure 'max server memory (MB)', 14336
> go
> reconfigure with override
> go
> Stop and start SQL Server. I chose the max memory of 14GB, since you shou
ld
> leave 2GB for the OS.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "jason7655" <jason7655@.discussions.microsoft.com> wrote in message
> news:619021D5-16A6-4FB4-AF44-E1E0DB1209B2@.microsoft.com...
> I have the following:
> Windows Server 2003 Enterprise x64
> SQL Server Enterprise x32
> In order for SQL to use all 16 GB of ram available are there in special
> settings such as /pae, /awe, or /3GB that I need to use?
> When using certain monitor tools it shows my memory usage at 100 percent
> when that doesn't seem right.
> Hope that is enough info.
>
awe enable is set to :
run value: 0
Config Value:0
Minimum: 0
Maximum: 1
Currently my "mas server memory" is set to :
run value: 2147483647
Config Value:2147483647
Minimum: 4
Maximum:2147483647
Does your suggestion remain the same or would I just removed the max server
portion of that statement?|||Just run my code and restart SQL Server.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"jason7655" <jason7655@.discussions.microsoft.com> wrote in message
news:8F14CDBE-1D38-4DBD-AE07-2B760EE0901A@.microsoft.com...
"Tom Moreau" wrote:

> On 64-bit platforms, you don't use the /PAE /3GB switches. You do have to
> use sp_configure:
> sp_configure 'awe enabled', 1
> go
> reconfigure with override
> go
> sp_configure 'max server memory (MB)', 14336
> go
> reconfigure with override
> go
> Stop and start SQL Server. I chose the max memory of 14GB, since you
> should
> leave 2GB for the OS.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "jason7655" <jason7655@.discussions.microsoft.com> wrote in message
> news:619021D5-16A6-4FB4-AF44-E1E0DB1209B2@.microsoft.com...
> I have the following:
> Windows Server 2003 Enterprise x64
> SQL Server Enterprise x32
> In order for SQL to use all 16 GB of ram available are there in special
> settings such as /pae, /awe, or /3GB that I need to use?
> When using certain monitor tools it shows my memory usage at 100 percent
> when that doesn't seem right.
> Hope that is enough info.
>
awe enable is set to :
run value: 0
Config Value:0
Minimum: 0
Maximum: 1
Currently my "mas server memory" is set to :
run value: 2147483647
Config Value:2147483647
Minimum: 4
Maximum:2147483647
Does your suggestion remain the same or would I just removed the max server
portion of that statement?

How can I use full memory in x64 OS with 32bit SQL

I have the following:
Windows Server 2003 Enterprise x64
SQL Server Enterprise x32
In order for SQL to use all 16 GB of ram available are there in special
settings such as /pae, /awe, or /3GB that I need to use?
When using certain monitor tools it shows my memory usage at 100 percent
when that doesn't seem right.
Hope that is enough info.On 64-bit platforms, you don't use the /PAE /3GB switches. You do have to
use sp_configure:
sp_configure 'awe enabled', 1
go
reconfigure with override
go
sp_configure 'max server memory (MB)', 14336
go
reconfigure with override
go
Stop and start SQL Server. I chose the max memory of 14GB, since you should
leave 2GB for the OS.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"jason7655" <jason7655@.discussions.microsoft.com> wrote in message
news:619021D5-16A6-4FB4-AF44-E1E0DB1209B2@.microsoft.com...
I have the following:
Windows Server 2003 Enterprise x64
SQL Server Enterprise x32
In order for SQL to use all 16 GB of ram available are there in special
settings such as /pae, /awe, or /3GB that I need to use?
When using certain monitor tools it shows my memory usage at 100 percent
when that doesn't seem right.
Hope that is enough info.|||"Tom Moreau" wrote:
> On 64-bit platforms, you don't use the /PAE /3GB switches. You do have to
> use sp_configure:
> sp_configure 'awe enabled', 1
> go
> reconfigure with override
> go
> sp_configure 'max server memory (MB)', 14336
> go
> reconfigure with override
> go
> Stop and start SQL Server. I chose the max memory of 14GB, since you should
> leave 2GB for the OS.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "jason7655" <jason7655@.discussions.microsoft.com> wrote in message
> news:619021D5-16A6-4FB4-AF44-E1E0DB1209B2@.microsoft.com...
> I have the following:
> Windows Server 2003 Enterprise x64
> SQL Server Enterprise x32
> In order for SQL to use all 16 GB of ram available are there in special
> settings such as /pae, /awe, or /3GB that I need to use?
> When using certain monitor tools it shows my memory usage at 100 percent
> when that doesn't seem right.
> Hope that is enough info.
>
awe enable is set to :
run value: 0
Config Value:0
Minimum: 0
Maximum: 1
Currently my "mas server memory" is set to :
run value: 2147483647
Config Value:2147483647
Minimum: 4
Maximum:2147483647
Does your suggestion remain the same or would I just removed the max server
portion of that statement?|||Just run my code and restart SQL Server.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"jason7655" <jason7655@.discussions.microsoft.com> wrote in message
news:8F14CDBE-1D38-4DBD-AE07-2B760EE0901A@.microsoft.com...
"Tom Moreau" wrote:
> On 64-bit platforms, you don't use the /PAE /3GB switches. You do have to
> use sp_configure:
> sp_configure 'awe enabled', 1
> go
> reconfigure with override
> go
> sp_configure 'max server memory (MB)', 14336
> go
> reconfigure with override
> go
> Stop and start SQL Server. I chose the max memory of 14GB, since you
> should
> leave 2GB for the OS.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "jason7655" <jason7655@.discussions.microsoft.com> wrote in message
> news:619021D5-16A6-4FB4-AF44-E1E0DB1209B2@.microsoft.com...
> I have the following:
> Windows Server 2003 Enterprise x64
> SQL Server Enterprise x32
> In order for SQL to use all 16 GB of ram available are there in special
> settings such as /pae, /awe, or /3GB that I need to use?
> When using certain monitor tools it shows my memory usage at 100 percent
> when that doesn't seem right.
> Hope that is enough info.
>
awe enable is set to :
run value: 0
Config Value:0
Minimum: 0
Maximum: 1
Currently my "mas server memory" is set to :
run value: 2147483647
Config Value:2147483647
Minimum: 4
Maximum:2147483647
Does your suggestion remain the same or would I just removed the max server
portion of that statement?

How can i use format as HH:MM:SS from seconds within the SQL query

I have the following SQL query where i want thease to be populate to GridView, but the Duration field is in Second format, I want it would be in HH:MM:SS format.

cmd ="select subscriber_id as Subscriber_no,,amount,duration from MyTable" ;

Please help me how to format this within the Query to display in GridView.

Hi tapan.behera,

Here is an example that does what you want:

declare @.durationintset @.duration = 1082587selectcast(@.duration / 60 / 60AS nvarchar) +'h:' +cast(@.duration / 60 % 60AS nvarchar) +'m:' +cast(@.duration % 3600 % 60AS nvarchar) +'s'

Kind regards,
Wim|||

Thanks for your suggestion.

But how can i embed your variable in my sql statement.

I don't want to use it as separate variable, i want to use it within my sql statement.

i.e Select amount,call_id,duration from my Table.

So how can i use your suggestion here, please help me.

|||

hi tapan.behera,

tapan.behera@.hotmail.com:
I don't want to use it as separate variable, i want to use it within my sql statement.

I was just making an example! When you use a select statement, that will eliminate the need of a variable.

tapan.behera@.hotmail.com:
i.e Select amount,call_id,duration from my Table.

It would be something like:

select amount, call_id,cast(duration / 60 / 60AS nvarchar) +'h:' +cast(duration / 60 % 60AS nvarchar) +'m:' +cast(duration % 3600 % 60AS nvarchar) +'s'as durationfrom myTable

Kind regards,
Wim

How can i use embbed sql(e.g. SQC) program to access MSSQL from linux or unix?

hello all!
Help!
I've a lot of programs written in sqlc to mssql on the windows platform.Now the boss wants to use linux server and I've to tune all my programs in sqlc to linux.But how to do it?Does anyone help me?Don't have any experience in linux, thought this link (http://www.linux.org/apps/AppId_8044.html) may give you required information.

HTH|||Thanks, but this link was on MySQL, not MSSQL. I also have some information on this through using freeTDS or DBI, but on embedded sql, I found nothing!:(|||Then may be you need to do other way round, how about this link? (http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=921&lngWId=8)

How can I use DTS with Sql Server Express 2005?

I downloaded the tookit and it put the DTS tools in the installation,
but I still can't use them from within the CTP management studio.
There is still not export database.
Can DTS be used with Sql Server Express 2005? If not why did it
install? Thank you for any help.
jm wrote:
> I downloaded the tookit and it put the DTS tools in the installation,
> but I still can't use them from within the CTP management studio.
> There is still not export database.
> Can DTS be used with Sql Server Express 2005? If not why did it
> install? Thank you for any help.
I found that after installing the toolkit you have to go to External
Tools in the Management Studio and add it from the 90 binn directory.
I believe it was the dtsexec file.

How can I use DTS with Sql Server Express 2005?

I downloaded the tookit and it put the DTS tools in the installation,
but I still can't use them from within the CTP management studio.
There is still not export database.
Can DTS be used with Sql Server Express 2005? If not why did it
install? Thank you for any help.jm wrote:
> I downloaded the tookit and it put the DTS tools in the installation,
> but I still can't use them from within the CTP management studio.
> There is still not export database.
> Can DTS be used with Sql Server Express 2005? If not why did it
> install? Thank you for any help.
I found that after installing the toolkit you have to go to External
Tools in the Management Studio and add it from the 90 binn directory.
I believe it was the dtsexec file.

How can I use DTS with Sql Server Express 2005?

I downloaded the tookit and it put the DTS tools in the installation,
but I still can't use them from within the CTP management studio.
There is still not export database.
Can DTS be used with Sql Server Express 2005? If not why did it
install? Thank you for any help.jm wrote:
> I downloaded the tookit and it put the DTS tools in the installation,
> but I still can't use them from within the CTP management studio.
> There is still not export database.
> Can DTS be used with Sql Server Express 2005? If not why did it
> install? Thank you for any help.
I found that after installing the toolkit you have to go to External
Tools in the Management Studio and add it from the 90 binn directory.
I believe it was the dtsexec file.

How can i use default schemas of users in a function ?

Hi,

I've a Problem with a Function called by different users.

I want the different callers to query a table in their own default Schema.

So if i refence the full Path [Schema].[Tablename] it works Properly.

If i just Use the [Tablename] for reference it fails.

The function was created with EXECUTE AS CALLER option ;

if I call it with dynamic SQL i retreive the error-message

"Only functions and extended stored procedures can be executed from within a function."

does anybody know a solution for this problem?

thanks in advance

Raimund

Raimund wrote:

So if i refence the full Path [Schema].[Tablename] it works Properly.

If i just Use the [Tablename] for reference it fails.

The function was created with EXECUTE AS CALLER option ;

I quote from Books Online (search for "Object Visibility and Qualification Rules")

USE DBY SELECT * FROM DBY..TableX

Because LoginX is associated with UserA in DBY, SQL Server first looks for DBY.UserA.TableX. If there is no table with this name, SQL Server looks for a table DBY.dbo.TableX. "

So engine look for [yourCallerUser].[Tablename] and for [dbo].[Tablename] and find no [Tablename]

Then I think you have to maintain full path [Schema].[Tablename] to objects reference.

|||

Hm,

so i guess i have to path the query-results by parameter to the function.

It looks horrible but it works.

Best Regards

Raimund

How can I use create or alter statements with ODBC and Microsoft Access ODBC Driver (*mdb)?

Hi,

I am using VB.NET 2005 and set up an ODBC connection via ODBC.ODBCConnection to a MDB database. Therefor, I use the "Microsoft Access ODBC Driver (*.mdb)".

When I set up a ODBCCommand like "ALTER DATABASE..." or "CREATE TABLE..." and issue it with the com.ExecuteNonQuery() command, I get an error from ODBC driver, that a SQL statement has to begin with SELECT, INSERT, UPDATE or DELETE.

How can I use DDL statements via ODBC?

I would appreciate if you could help me to use ODBC for that - no OLE, no ADO.

Thanks for help!

Regards,

Stefan D.

Once upon a time (if I recall correctly -and even if I don't it makes for a good 'story')...

OBDC (Open DataBase Connectivity) was established as a result of a consortium of product vendors working to find a common API to allow users using a common 'language' (ANSI SQL) to access data in any vendors database. So far, the supported ANSI SQL is limited to SELECT INSERT, UPDATE, DELETE statements.

Each vendor has proprietary extensions to the SQL Language to create/define/change/remove the database objects.

Short version -you can't.

Your choices are to use the vendor specific database management tool to create the tables, and then use ODBC to handle the data operations,

(OR), embrace ADO, or even OLEDB.

|||

Ah, that's a pity, but thanks for your answer!

So then I have to use ADO. Is there a way to get access to any database format that is stored in files (like .mdb) without having to install a driver for it on a standard windows XP installation? For OLE to mdb I have to install the JET-Engine, am I right?

Thanks for answer again!

|||

This may be wrong, but, I think that the full JET functionality is included in MDAC 2.x, and that MDAC 2.x is automatically included with and installed with Windows OS (since Win 2000/XP) and service packs.

|||Alternatively, you might also consider using SQL Server Express or SQL Server CE as your backend database and then using the System.Data.SqlClient as your provider API. This would also be beneficial in that your developement experience would be greatly simplified with the data scenario support that is provided in Visual Studio 2005.|||

Well, if it is true that JET is installed with Windows service pack, then I wonder why I get an ISAM not found error...

|||

My software is already supporting MySQL and Oracle back ends (Oracle is in its XE 10g version also free) with a single class working with ODBC. That's the reason why I wanted to use ODBC to access the mdb, too.

In addition to the two server-based solutions now I want a third, file-based solution which is slim enough for a standard business laptop (which are server-based solutions definitely not) and does not need any extra installation effort (like an extra driver or database server).

So, thanks for your reply, but I still insist on a file-based solution. Any ideas?

|||

Stephan,

You might explore SQL Server CE (or maybe it's now called 'Mobile'). Very small footprint, very small demand on resources -and yet suprisingly a lot of functionality.

And as i indicated before, the JET drivers are automatically installed on Windows 2000 and above.

|||

ISAM not found errors are often a result of incorrect or improperly formed connection strings.

Check this thread for one type of problem.

|||

That's the reason why I wanted to use ODBC to access the mdb, too.

You CAN use ODBC to access the mdb file -SELECT, INSERT, UPDATE, DELETE works the same as in MySQL or ORACLE.

You just can't use ODBC to CREATE TABLES, etc.

You have to use JET (ADO / OLEDB) for that.

|||

Great, thank you!

After you've convinced me that Jet is really installed in standard windows installation, I came closer to my error. Didn't know that the OLE driver does not like additionally specified parameters in that string than those it needs.

Now it works, thanks!

This problem is thus resolved.

Thanks much to all for your help!

|||

After you've convinced me that the driver is really installed in an original windows installation, I came close to the real error. My connection string was indeed malformulated.

Thanks to all for their help!

This issue is thus resolved!

How can I use create or alter statements with ODBC and Microsoft Access ODBC Driver (*mdb)?

Hi,

I am using VB.NET 2005 and set up an ODBC connection via ODBC.ODBCConnection to a MDB database. Therefor, I use the "Microsoft Access ODBC Driver (*.mdb)".

How can I use DDL statements via ODBC?

I would appreciate if you could help me to use ODBC for that - no OLE, no ADO.

Thanks for help!

Regards,

Stefan D.

Once upon a time (if I recall correctly -and even if I don't it makes for a good 'story')...

Each vendor has proprietary extensions to the SQL Language to create/define/change/remove the database objects.

Short version -you can't.

Your choices are to use the vendor specific database management tool to create the tables, and then use ODBC to handle the data operations,

(OR), embrace ADO, or even OLEDB.

|||

Ah, that's a pity, but thanks for your answer!

Thanks for answer again!

|||

Well, if it is true that JET is installed with Windows service pack, then I wonder why I get an ISAM not found error...

|||

So, thanks for your reply, but I still insist on a file-based solution. Any ideas?

|||

Stephan,

You might explore SQL Server CE (or maybe it's now called 'Mobile'). Very small footprint, very small demand on resources -and yet suprisingly a lot of functionality.

And as i indicated before, the JET drivers are automatically installed on Windows 2000 and above.

|||

ISAM not found errors are often a result of incorrect or improperly formed connection strings.

Check this thread for one type of problem.

|||

That's the reason why I wanted to use ODBC to access the mdb, too.

You CAN use ODBC to access the mdb file -SELECT, INSERT, UPDATE, DELETE works the same as in MySQL or ORACLE.

You just can't use ODBC to CREATE TABLES, etc.

You have to use JET (ADO / OLEDB) for that.

|||

Great, thank you!

Now it works, thanks!

This problem is thus resolved.

Thanks much to all for your help!

|||

After you've convinced me that the driver is really installed in an original windows installation, I came close to the real error. My connection string was indeed malformulated.

Thanks to all for their help!

This issue is thus resolved!

how can i use COUNT in my stored procedure?

hello,

i have a list of Categories and each one contains a list of SubCategories, and i use a nested Repeater to show each Category with their SubCategories, but because are to many to show verticaly, i want to make 2 nested repeaters and in the first one to show only the first "n" Categories with their SubCategories and in the second Repeater should be the last n Categories, and like so they will be in 2 colums

How can i use the COUNT function in my stored procedure to take only first n Categories?

I used SELECT * FROM Categories WHERE CategoryID <= 5 but i don't want to order by the primary key i want to order by a COUNT or something like this...

here is my stored procedure (i use it for two nested Repeaters)

ALTER PROCEDURESubCategoriiInCategorii

CategoryIDint)

SELECTCategoryID,Name, DescriptionFROMCategoriesWHERECategoryID = @.CategoryID

ORDER BYName

SELECTp.SubCategoryID, p.Name,p.CategoryIDFROMSubCategorii p

ORDER BYp.Name

i hope you understand what i mean, thank you

Hi,

Have a look at this threadhttp://forums.asp.net/t/1138145.aspx it will tell you how to get N first rows or in otherwords N top rows.

Hope it will help you out.

Thanks and best regards,

|||

if i use the stored procedures from there i get error from the nested repeater...

i tryied this, but i get an error: invalid column name RowNumber ...why the alias don't work?

SELECTROW_NUMBER()OVER(ORDER BYCategoryID)ASRowNumber, CategoryID,Name, DescriptionFROMCategorii

WHEREDepartamentID = @.DepartamentIDANDRowNumber = 3

|||

Hi,

Actually I don't exactly get what are you trying to do in the above stored procedure.

Thanks and best regards,

|||

if i execute the stored procedure without the "AND" part, in the RowNumber i have values from 1 to 9 (counts how many CategoriyID's i have)

i think it work, try to execute the procedure

thank you

i just resolved it, here is the stored procedure

SELECT*FROM(

SELECTROW_NUMBER()OVER(ORDER BYCategoryID)ASRowNumber, CategoryID,Name, DescriptionFROMCategoriiWHEREDepartamentID = @.DepartamentID

)ASMyTable

WHERERowNumber <=5

i think is good for what i need, thank you again for help

how can i use comma like a decimal separator?

"update product set price='1,99' where cod='001'"

I need COMMA... not DOT

In oracle i use "alter session set language='Brazil'"... but... in SQL SERVER?

i need help...

You would have to change your Windows collation. That is where the translation is occuring.

how can i use comma like a decimal separator?

"update product set price='1,99' where cod='001'"

I need COMMA... not DOT

In oracle i use "alter session set language='Brazil'"... but... in SQL SERVER?

i need help...

hi,

SQL Server just uses "dot" as decimal separator... you have to replace your strings removing the thousands separator, if present, and modify the decimal separator as indicated..

or just use a Command object instead of dynamic SQL, so that the command parameter's value(s) will be set accordingly to your locale settings and you do not have to deal with that kind of troubles... more, you completely bypass lots of SQL injection troubles as, for instance, a datetime parameter can not accept something like

param.Value = '20060101'''; DELETE FROM other_table'

or the like... and you can better check each parameter for validity..

regards

How can i use comma like a decimal separator?

"update product set price='1,99' where cod='001'"

I need COMMA... not DOT

In oracle i use "alter session set language='Brazil'"... but... in SQL SERVER?

You're in the wrong place. Go here: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=93&SiteID=1

-Jamie

how can i use comma like a decimal separator in Sql Server Express?

I need HELP

"update product set price='1,99' where cod='001'"

I need COMMA... not DOT

In oracle i use "alter session set language='Brazil'"... but... in SQL SERVER?

Moving to the "Transact-SQL" forum.|||

Take a look if maybe that will help you:

ms-help://MS.MSDNQTR.2003FEB.1033/tsqlref/ts_sp_da-di_2tk5.htm

How can I use arrays in parameters.

Following code is just select category 1 but for example What can I do if I
want to select 1 and 2?
declare @.CategoryID as int
set @.CategoryID = 1
SELECT * FROM Northwind.dbo.Products WHERE CategoryID in (@.CategoryID)Hi
You would need to use dynamic SQL to have multiple values in a IN clause
when using a variable number of values.
Regards
Mike
"Murat BUDAK" wrote:

> Following code is just select category 1 but for example What can I do if
I
> want to select 1 and 2?
> declare @.CategoryID as int
> set @.CategoryID = 1
> SELECT * FROM Northwind.dbo.Products WHERE CategoryID in (@.CategoryID)
>
>|||http://www.sommarskog.se/arrays-in-sql.html
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Murat BUDAK" <mbudak@.islem.com> wrote in message
news:u1J9jfuAFHA.1392@.tk2msftngp13.phx.gbl...
> Following code is just select category 1 but for example What can I do if
I
> want to select 1 and 2?
> declare @.CategoryID as int
> set @.CategoryID = 1
> SELECT * FROM Northwind.dbo.Products WHERE CategoryID in (@.CategoryID)
>|||"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:96082D73-8AD1-4597-A0E6-55D9F16E4386@.microsoft.com...
> You would need to use dynamic SQL to have multiple values in a IN clause
> when using a variable number of values.
Mike,
You should read Erland's article that I posted the link to. You do not need
dynamic SQL for this.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Impresive. Thanks for this article :o)
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:Og0LJquAFHA.3492@.TK2MSFTNGP12.phx.gbl...
> http://www.sommarskog.se/arrays-in-sql.html
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Murat BUDAK" <mbudak@.islem.com> wrote in message
> news:u1J9jfuAFHA.1392@.tk2msftngp13.phx.gbl...
> I
>

How can I Use a User Defined function as a default for a column value?

Hi, I have an int column for which I want to set to a certain code value, e.g.: In my table "Biometric" I have a column called "BiometricStatusCode" which is a foreign key to a Table/Column "REF_PERSON_BiometricStatusCodes:BiometricStatusID"

In that REF_PERSON_BiometricStatusCodes table, I have a row of data that returns a value of "All Others", the PK id for this row happens to be 3.

I can enter 3 as my default for the column in my original "Biometric" table...that will work fine and return "All Others" when joined...as long as the PK for "All Others" is 3. However, I cannot guarantee that in customer installations, hence I want to use a function that returns the Int/PK code for the row of data that contains "All Others" in my REF_PERSON_BiometricStatusCodes table.

So..........I created this function:

Create FUNCTION [dbo].[ufn_SelectDefaultBiometricStatusCode]()

RETURNS int

BEGIN

DECLARE @.ret int;

SELECT @.ret = [BiometricStatusID]

FROM [AFR].[dbo].[REF_PERSON_BiometricStatusCodes] where

Upper([BiometricStatusDescr]) = 'ALL OTHERS';

IF (@.ret IS NULL)

SET @.ret = 0

RETURN @.ret

END;

The function works fine, it's not full proof...but is ok.

When I enter the function name, no quotes in the "Default Value or Binding" property in the table designer in sql 2005, it returns an error: "error validating the default for column".

The column is an int, the function returns an int... I can't figure it out..

Anyone have any ideas?

thanks.

You can use a computed column.

If you use SQL Server Management Studio to modify the table - use the column properties tab - Computed Column Specification - Formula.

How Can I Use a SubQuery to Sum a Column "Quantity" where subqry.item=mainqry.it

More to the point, how do I get the results to show one row per Item? I thought if I got all the rows evaluate the same, I would be able to use DISTINCT to return one row per item.

Actually, I thought if I did a RIGHT JOIN that for each instance (record) of an item in my ItemInventory table (in which all rows are unique) with my PurchaseOrderLine table (in which there can be multiple instances of an item) that I would get one lie, with the quantities on PO's that are open and received summed. Instead, I get multiple lines that all appear to have correct data but that are duplicates.

Here is my current query

SELECT ItemInventory.Name AS [Product Number],
ItemInventory.SalesDesc AS Description,
ItemInventory.PrefVendorRefFullName AS Vendor,
ItemInventory.QuantityOnHand AS Available,
ItemInventory.ReorderPoint AS Minimum,
(ItemInventory.QuantityOnHand-ItemInventory.ReorderPoint) AS Under,

(SELECT SUM(PurchaseOrderLine.PurchaseOrderLineQuantity)
FROM PurchaseOrderLine
WHERE ItemInventory.Name =
PurchaseOrderLine.PurchaseOrderLineItemRefFullName )
AS [On Order],

(SELECT SUM
(PurchaseOrderLine.PurchaseOrderLineReceivedQuanti ty)
FROM PurchaseOrderLine
WHERE ItemInventory.Name =
PurchaseOrderLine.PurchaseOrderLineItemRefFullName )
AS Recvd

PurchaseOrderLine.TxnDate AS [PO Date],
ItemInventory.PurchaseCost AS Price

FROM PurchaseOrderLine RIGHT JOIN ItemInventory ON PurchaseOrderLine.PurchaseOrderLineItemRefListID=I temInventory.ListID;

Here are my results:

"Large Box","Large Packing Box",,455,100,355,,,2
"Medium Box","Medium cardboard packing box",,55,100,-45,,,1.5
"Packing Tape","Packing Tape, 100 Yard roll",,100,100,0,150,100,1.5
"Packing Tape","Packing Tape, 100 Yard roll",,100,100,0,150,100,1.5
"Small Box","Small cardboard packing box",,55,100,-45,,,1
"Small wooden crate","Small wooden crate","Crate Guys",100,50,50,54,10,8
"Small wooden crate","Small wooden crate","Crate Guys",100,50,50,54,10,8
"Small wooden crate","Small wooden crate","Crate Guys",100,50,50,54,10,8
"Widget","A widget","Texaco",199,200,-1,224,125,5
"Widget","A widget","Texaco",199,200,-1,224,125,5
"Widget","A widget","Texaco",199,200,-1,224,125,5
"Will Excel Delete This Item",,,100,,,,,0

If I use DISTINCT, like I thought I could, MS ACCESS blows up on me. I don't know if it is my SQL or MS ACCESS (or if my data source has issues).

If I can verify my SQL is valid, then I'll know if I need to look elsewhere for a resolution to my problem.

TIA

Danielyou need to develop a query like this in two steps

the first step is to ensure that the join is working correctly, and the second step is to add GROUP BY and aggregate functions like SUM (i think your subqueries in the SELECT clause are wrong, but let's do that later after we fix the join)

for your first step, you need to understand that your RIGHT JOIN will return each ItemInventory with or without matching rows from PurchaseOrderLine

in other words, all products

and for any product that appears on any PurchaseOrderLine, you will get a row for each such occurrence, and that product will appear in the results multiple times

is that what you want? if not, we need to fix the join before you start thinking about SUMs|||I need to look at every item in the InventoryItem table.

For those items where there are purchase orders in the PurchaseOrderLine table, I need to sum the total number on all open purchase orders, minus the number already received against those open purchase orders (they receive partial fills on orders).

In the end, I need somethig that looks like this:

Name Description Vendor #Available Reorder_Point Max_On_Hand #Under Reorder_Point #On_Order #To_Reorder Cost_Per_Unit Cost_to_Reorder Total_Cost_to_Reorder

#Under = Max_On_Hand - #Avaialble (if #Available < Max_On_Hand)

We reorder an item if #Under < 0

#To_Reorder = #Under - #On_Order (we never want the total headed for the shelves to be > Max_On_Hand|||select II.Name AS [Product Number]
, II.SalesDesc AS Description
, II.PrefVendorRefFullName AS Vendor
, II.QuantityOnHand AS Available
, II.ReorderPoint AS Minimum
, (II.QuantityOnHand
-II.ReorderPoint) AS Under
, SUM(POL.PurchaseOrderLineQuantity)
AS [On Order]
, SUM(POL.PurchaseOrderLineReceivedQuantity)
AS Recvd
, MAX(POL.TxnDate) AS [PO Date]
, II.PurchaseCost AS Price
from ItemInventory as II
left outer
join PurchaseOrderLine as POL
ON POL.PurchaseOrderLineItemRefListID
= II.ListID
group
by II.Name
, II.SalesDesc
, II.PrefVendorRefFullName
, II.QuantityOnHand
, II.ReorderPoint
, II.PurchaseCost|||I will try this.

I may ask what makes it tick, but I think I get it ... I will play with it before I ask again tho.

How can i use a report from Access 97

I was wondering if there is a way I can use a report from Access 97 to print a certificate using SQL Server and being displayed in ASP as well?We use Access 97 to create monthly reports and have them available for the Web. When we run the report we save it in a format called Snapshot, there is a viewer that can be used in an ASP to view Snapshot reports. They look just like they do in Access.

Here is an example of the ASP code:

<OBJECT ID="SnapshotViewer" WIDTH=100% HEIGHT=97%
CODEBASE="/apps/CabFiles/Snapview.ocx"
CLASSID="CLSID:F0E42D60-368C-11D0-AD81-00A0C90DC8D9">
<PARAM NAME="_ExtentX" VALUE="16722">
<PARAM NAME="_ExtentY" VALUE="11774">
<PARAM NAME="_Version" VALUE="65536">
<PARAM NAME="SnapshotPath" VALUE="<%=reportName%>">
<PARAM NAME="Zoom" VALUE="0">
<PARAM NAME="AllowContextMenu" VALUE="-1">
<PARAM NAME="ShowNavigationButtons" VALUE="-1">
</OBJECT>

We only do this for monthly reports, I've created a Perl script that runs after hours to generate these reports. I'm not to sure of performance and concurrency if I had these reports generated on demand by the front-end.|||but you really should be using Access 2002 with Access Data Projects.

they are a lot faster for reporting than traditonal MDB.

How can i use a for loop in a query ?

hi all i have the following stored procedure.

declare @.AreaID int

select @.AreaID = 1

select DATEPART(hh, dbo.JobEvent.JobEventDateTime) AS hourbracket, count(ID) as NUMCOUNT

from
table1 INNER JOIN
table2 ON table1.JobId = table2.JobEvent_JobId
where

(table1.Job_AreaId = @.AREAID)

the there are about 300 AREAID's so my question is do i do a "LOOP" or is that no efficient and chews up resources on the server ?
what would be a nice clean way to do this ?

thanks
robby

Are you asking how to iterate over the results returned from your stored procedure?

depending on how you retrieve you results either from a data reader or you used a data adapter and filled it to a dataset

//if filled to a dataset you can do thisif (MyDataSet.Tables.Count != 0){foreach (DataRow rowin MyDataSet.Tables[0].Rows) {//now you can access each row returned }}//if using a data readerwhile (DataReader.Read()){//hear you can access each row }

|||

Hi,

if i lets say hardcode the areaid to lets say 1 i get a set of results. i want to next get the results for areaid 2 so as if i want to runt the stored procedure again. But can i lets say have some sort of loop which will pass the parameter (areaid ) into the stored procedure and get the results in c# ?

that would be i think a bit too long winded ... is that right ?

|||

Sorry, but I do not understand what you want.

|||

What I understood from the problem is that you want to execute the same query for around 300 AREAID.
If I am correct following query will solve your problem:

SELECT table1.Job_AreaId, DATEPART(hh, dbo.JobEvent.JobEventDateTime) AS hourbracket, count(ID) as NUMCOUNT
from table1 INNER JOIN table2 ON table1.JobId = table2.JobEvent_JobId
where table1.Job_AreaId in
(
SELECT the list of distinct AreaId for ex: select AreaId from MasterTable
)
Group by table1.Job_AreaId

Hope this helps you!!!

|||

Hi ,

Thanks for the response . It sure did help much appreciated

robby

How can I use a Date Picker in a report

I have a parametrized report which actually uses a combo with dates to render the report. And I want to change it for a Date picker. Can I do it?

Thanks in advance.VectorR3,

You can do this ins Reporting Services 2005, not in Reporting Services 2000. Go to "Reports", "Report Parameters". In here change the type on your field to a datetime field. Then when you select preview report you will see the calendar button for that parameter. You will also see it in the Report Manager when you run it.|||Hi,

I am using MS SQL Server 2205 Developer Edition Beta 2 (September 2004 release), but I cannot see the calendar button you mentioned. Is it becuase my version is old & does not have the feature. I am selecting the parameter data type as 'datetime'. Please advice.

Thanks in advance,|||That's right - the date picker was added after Beta 2 (from what I can recall, it first appeared in the June CTP). At this point, Beta 2 is way out of date, and you should be looking at the released version, if possible ...

How can I use a Date Picker in a report

I have a parametrized report which actually uses a combo with dates to render the report. And I want to change it for a Date picker. Can I do it?

Thanks in advance.VectorR3,

how can i use a conditional where clause in sql statment

I have a store procedure where i need to use conditionel where clause

One possibility would be to make the conditional parameter nullable and then add an IsNull check to your where clause that only evaluates the condition when the parameter is set.|||

you can use coalesce function to implement conditional where clause in sql statement.

for more details please refer this link

http://www.sqlteam.com/item.asp?ItemID=2077

regards

how can I use (contains) in stored procedeure?

how can I use:

where field_name LIKE '%' + variable + '%'

in stored procedure?

I tried field_name = %@.variable% but didn't work..

field_name LIKE '%' + variable + '%' should work, exactly as you have it.

Can you post more of the code that is not working?

|||

Use

where field_name LIKE '%' + @.variable + '%'

instead of

where field_name LIKE '%' + variable + '%'

You need to add the "@." character to your variable name.

Regards,

|||

this is the error I am getting followed by the stored procedure:

The resource cannot be found.

Description: HTTP 404. The resource you are looking for (or one of its dependencies) could have been removed, had its name changed, or is temporarily unavailable. Please review the following URL and make sure that it is spelled correctly.

Requested URL: /Special Numbers/www.yahoo.com

the Stored Procedure:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

ALTER PROCEDURE [shopnumbers].[sp_get_latest_number]

-- Add the parameters for the stored procedure here

-- <@.Param1, sysname, @.p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,

-- <@.Param2, sysname, @.p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>

@.country_code int

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

-- SELECT category.[category_english_name] + ' - ' + category.[sub_category_english_name] AS full_category_name, numbers.the_number, numbers.created_date FROM owners INNER JOIN (category INNER JOIN numbers ON category.category_id = numbers.category_id) ON owners.owner_id = numbers.owner_id;

SELECT numbers.number_id, numbers.number_guid, country.country_flag, numbers.category_id, numbers.the_number, numbers.created_date, numbers.amount, country.country_english_name, category.category_english_name + ' - ' + category.sub_category_english_name AS full_category_name

FROM numbers

INNER JOIN country ON numbers.country_id = country.country_id

INNER JOIN category ON numbers.category_id = category.category_id

WHERE (numbers.number_disabled <> 'Y') AND country.country_code LIKE '%' + @.country_code + '%'

END

|||

OK.. I know where is the problem.. it was a varChar and I changed it to Int which dosn't allow LIKE.

Thank's any way..

Happy holidays..

how can i use "order by" with empty values

hi all.
how can i use "order by" with empty values
In SQL server, if record have empty value wills display firts, i need display empty value at last
please help me, thanks

use following query...

Select Value1, Value2,Value3 From table

Order By Case When Value1 is Null or Value1 = '' Then 1 Else 0 End, Value1

how can i use % with IN Clause?

helo..
i wont to use IN Clause for searching but i do not know how to use % with IN Clause.

like this:
select * from infotbl where infotitle IN('%tree%', 'sun')

this is return err.

what i have to do?

Hi,

You can not use IN with '%%' for searching column values including a certain criteria

select * from infotbl (nolock) where infotitle IN ('%tree%', 'sun')

This will look for rows whose infotitle column value equals exactly '%tree%'

But you can use LIKE with '%%' to search values including "tree"

select * from infotbl (nolock) where infotitle LIKE '%tree%'

Eralper

http://www.kodyaz.com

|||

If you know how many values you want to check for then you could simply take the example below and modify as appropriate.

Chris

SELECT *

FROM infotbl

WHERE (infotitle LIKE '%tree%'

OR infotitle = 'sun')

How can i upload the files(any kinda) onto the database??

Hi everyone,

I am developing an web-database application using ASP.NET, C# and sql server 2000 .

In the application i want to upload different scanned files (pdf,doc,excel,jpeg,ttf,etc) onto the database.. how can i carry out this task :-?

If any one got the solution.. then, please let me know...

thanks in advance

Step 1: Use the ASP.NET file input control to let the user select a file. This is in the HTML section of the toolbox.

Step 2: Make sure that the is set correctly to support file uploads. This means settings its encType to multipart/form-data.

Step 3: Read the file details on the server side code from the Page.Request.Files property.

Step 4: From here you can then save the files to disk and just add an index to them in the database, or you can save their contents into the DB itself (as BLOBs).

Hope this helps.|||

Hey thanx man..

Will try it out.. and if any probs will ask for ur help

|||

Hi,

It really helped me.. but can u plz repeat the 2nd step.. coz.. theres a bit confusion reading that :(

|||In order to allow the uploading of files, you need to set the encType property of your form element.

Open the .aspx file in HTML view, locate the element and add

encType="multipart/form-data" inside the element.

e.g.

Essentially, this allows you to upload a combination of files and form variables.

Hope this helps.|||

Hey thanx U soo much man..

well i gonna try out this and will definitely tell u the out-put of the same.

Thanx again, and keep the good work going

|||

Hi,

I couldnt add the encType="multipart/form-data" into the file input :(

when i tried adding it to the input tag of the file, it gave an parse error,,

So, plz let me know the soln..

<form id="Form1" method="post" runat="server">
<asp:Label id="Label2" style="Z-INDEX: 102; LEFT: 386px; POSITION: absolute; TOP: 106px" runat="server"
ForeColor="Maroon" Font-Size="Medium" Font-Bold="True" Width="265px">Uploading new Employee Details</asp:Label><INPUT style="Z-INDEX: 121; LEFT: 494px; POSITION: absolute; TOP: 431px" type="file" id="File1" name="File1" runat="server">

I tried adding it in the input tag, but encountered an parse error,hope i will get the soln,,

thanks in advance

|||You need to add the encType attribute to the form element, not the input element.

How can I update the col value using extended stored procedure

Hi,
I have created extended stored procedure. In that I need to update the value
of the perticular column of the table and return the same value. Is that
possible using extended stored procedure.
Please help me to solve this problem. If you have any sample code plz send
me, it would be more helpful to me.
Regards
VeeruI can think of 2 strategies:
1) If you only have to update a single value at the end of your processing
inside the XP code, you are best of returning this as a output parameter or
set of output parameters and use this inside a regular SP to do an INSERT,
UPDATE or DELETE
2) If you need to do this within the XP, you need to create a loop back
connection, using ODBC, OLE-DB or ADO. And issue the INSERT, UPDATE or
DELETE on the loopback connection. There is a ODBC sample for setting up a
loop back connection using ODBC that ships with SQL Server. The rest is
normal ODBC programming of which there are many samples flying around.
GertD@.SQLDev.Net
"Veeru" <Veeru@.discussions.microsoft.com> wrote in message
news:5920B696-2415-4429-AEE9-D26086D3B208@.microsoft.com...
> Hi,
> I have created extended stored procedure. In that I need to update the
> value
> of the perticular column of the table and return the same value. Is that
> possible using extended stored procedure.
> Please help me to solve this problem. If you have any sample code plz send
> me, it would be more helpful to me.
>
> Regards
> Veeru|||SQL 2000 XP? There's a sample called xp_dblib that demonstrates how to
connect back to SQL Server from an XP and execute SQL commands.
http://msdn.microsoft.com/library/d...
o1f.asp
"Veeru" <Veeru@.discussions.microsoft.com> wrote in message
news:5920B696-2415-4429-AEE9-D26086D3B208@.microsoft.com...
> Hi,
> I have created extended stored procedure. In that I need to update the
> value
> of the perticular column of the table and return the same value. Is that
> possible using extended stored procedure.
> Please help me to solve this problem. If you have any sample code plz send
> me, it would be more helpful to me.
>
> Regards
> Veeru|||Hi Thanks for your responce.
The documentation in the below path you have sent saying that the sample is
available at x:\Program Files\Microsoft SQL
Server\80\Tools\Devtools\Samples\ODS\Xp_
dblib. but i do not have it. plz sen
d
if you have that.
"Mike C#" wrote:

> SQL 2000 XP? There's a sample called xp_dblib that demonstrates how to
> connect back to SQL Server from an XP and execute SQL commands.
> http://msdn.microsoft.com/library/d...r />
_2o1f.asp
>
> "Veeru" <Veeru@.discussions.microsoft.com> wrote in message
> news:5920B696-2415-4429-AEE9-D26086D3B208@.microsoft.com...
>
>|||Hi Gert E.R. Drapers,
Thanks for your response.
I need to implement first point what you mentioned in your post below in XP
code. Can you please guide me with sample. I just started learning and
working on SQL Server 2005. We are using OLEDB not ODBC.
Regards
Veeru
"Gert E.R. Drapers" wrote:

> I can think of 2 strategies:
> 1) If you only have to update a single value at the end of your processing
> inside the XP code, you are best of returning this as a output parameter o
r
> set of output parameters and use this inside a regular SP to do an INSERT,
> UPDATE or DELETE
> 2) If you need to do this within the XP, you need to create a loop back
> connection, using ODBC, OLE-DB or ADO. And issue the INSERT, UPDATE or
> DELETE on the loopback connection. There is a ODBC sample for setting up a
> loop back connection using ODBC that ships with SQL Server. The rest is
> normal ODBC programming of which there are many samples flying around.
> GertD@.SQLDev.Net
>
> "Veeru" <Veeru@.discussions.microsoft.com> wrote in message
> news:5920B696-2415-4429-AEE9-D26086D3B208@.microsoft.com...
>
>|||BTW if you're using SQL 2005, then don't use extended stored proc's, as they
are depracated. Use the SQLCLR to create assemblies.
"Veeru" <Veeru@.discussions.microsoft.com> wrote in message
news:79390102-F24A-40AC-A19E-A9D44491AF40@.microsoft.com...
> Hi Thanks for your responce.
> The documentation in the below path you have sent saying that the sample
> is
> available at x:\Program Files\Microsoft SQL
> Server\80\Tools\Devtools\Samples\ODS\Xp_
dblib. but i do not have it. plz
> send
> if you have that.
> "Mike C#" wrote:
>|||It should be in C:\Program Files\Microsoft SQL
Server\80\Tools\Devtools\Samples\ODS\Xp_
dblib, assuming you installed on the
C: drive. They are installed by the SQL 2000 installer.
"Veeru" <Veeru@.discussions.microsoft.com> wrote in message
news:79390102-F24A-40AC-A19E-A9D44491AF40@.microsoft.com...
> Hi Thanks for your responce.
> The documentation in the below path you have sent saying that the sample
> is
> available at x:\Program Files\Microsoft SQL
> Server\80\Tools\Devtools\Samples\ODS\Xp_
dblib. but i do not have it. plz
> send
> if you have that.
> "Mike C#" wrote:
>

How can I update relationship tables?

<----Ihave 2 tables are: 'customers (parent)' and 'open_ac (child)'

<---Ihave tried to insert and update data into sql database by using textboxes(don't use datagrid)

<---Mytables details are below

<---thistable uses for keeping user data

customersfields:

Columnname type length Description

cu_id int 4 Primary key Identifiers

cu_fname nvarchar 20 allow null first name

cu_lname nvarchar 40 allow null last name

cu_nat nvarchar 20 allownull nationality

cu_add nvarchar 40 allow null address

cu_wplace nvarchar 40 allownull workplace

cu_tel nvarchar 10 allownull telephone

cu_fax nvarchar 10 allow null fax

cu_email nvarchar 10 allownull email

<--theopen_ac uses for keeping register date/time of customers

open_acfields:

Columnname type length Description

cu_id int 4 link key

op_date date/time 8 register date

<----mycode

ImportsSystem.Data.SqlClient

Public Class cus_reg
Inherits System.Web.UI.Page
Dim DS As DataSet
Dim iRec As Integer 'Current Record
Dim m_Error As String = ""

Public Property MyError() As String
Get
Returnm_Error
End Get
Set(ByVal Value As String)
m_Error =Value
IfTrim(Value) = "" Then
Label3.Visible = False
Else
Label3.Text = Value
Label3.Visible = True
End If
End Set
End Property

Private Sub Page_Load(ByVal sender As System.Object,ByVal e As System.EventArgs) Handles MyBase.Load
If Not Page.IsPostBack Then
Dim C1 AsNew MISSQL
'DS =C1.GetDataset("select * from customers;select * from open_ac;select * fromaccounts")
DS =C1.GetDataset("select * from customers;select * from open_ac")

Session("data") = DS
iRec = 0
Viewstate("iRec") = iRec
Me.MyDataBind()

Dim Dtr AsDataRow = DS.Tables(0).NewRow
DS.Tables(0).Rows.Add(Dtr)
iRec =DS.Tables(0).Rows.Count - 1
viewstate("iRec") = iRec
Me.Label2.Text = DateTime.Now
Me.MyDataBind()
Else
DS =Session("data")
iRec =ViewState("iRec")
End If
Me.MyError = ""
End Sub

Public Function BindField(ByVal FieldName As String) AsString
Dim DT As DataTable = DS.Tables(0)
Return DT.Rows(iRec)(FieldName)& ""
End Function
Public Sub MyDataBind()
Label1.Text = "Record : "& iRec + 1 & " of " & DS.Tables(0).Rows.Count
txtcu_id.DataBind()
txtcu_fname.DataBind()
txtcu_lname.DataBind()
txtcu_add.DataBind()
txtcu_occ.DataBind()
txtcu_wplace.DataBind()
txtcu_nat.DataBind()
txtcu_tel.DataBind()
txtcu_fax.DataBind()
txtcu_email.DataBind()
End Sub

Here isupdate code

Private Sub bUpdate_Click(ByVal sender As System.Object,ByVal e As System.EventArgs) Handles bUpdate.Click
Dim DT As DataTable = DS.Tables(0)
Dim DR As DataRow = DT.Rows(iRec)
'Dim DR1 As DataRow = DT1.Rows(iRec)

If DR.RowState = DataRowState.AddedThen
Iftxtcu_id.Text.Trim = "" Then
Me.MyError = "please enter your id"
Exit Sub
End If
DR("cu_id")= txtcu_id.Text
End If

If txtcu_fname.Text.Trim ="" Then
Me.MyError ="please enter your name"
Exit Sub
Else
DR("cu_fname") = txtcu_fname.Text
End If

If txtcu_lname.Text.Trim ="" Then
Me.MyError ="please enter your last name"
Exit Sub
Else
DR("cu_lname") = txtcu_lname.Text
End If

If txtcu_add.Text.Trim ="" Then
Me.MyError ="please enter your address"
Exit Sub
Else
DR("cu_add") = txtcu_add.Text
End If

If txtcu_occ.Text.Trim ="" Then
Me.MyError ="please enter your occupation"
Exit Sub
Else
DR("cu_occ") = txtcu_occ.Text
End If

If txtcu_wplace.Text.Trim ="" Then
Me.MyError ="please enter your workplace"
Exit Sub
Else
DR("cu_wplace") = txtcu_wplace.Text
End If

If txtcu_nat.Text.Trim ="" Then
Me.MyError ="Please enter your nationality"
Exit Sub
Else
DR("cu_nat") = txtcu_nat.Text
End If

If txtcu_tel.Text.Trim ="" Then
DR("cu_tel") = DBNull.Value
Else
DR("cu_tel") = txtcu_tel.Text
End If

If txtcu_tel.Text.Trim ="" Then
DR("cu_fax") = DBNull.Value
Else
DR("cu_fax") = txtcu_fax.Text
End If

If txtcu_email.Text.Trim ="" Then
DR("cu_email") = DBNull.Value
Else
DR("cu_email") = txtcu_email.Text
End If

Dim Strsql As String
If DR.RowState = DataRowState.AddedThen
Strsql ="insert into customers (cu_id,cu_fname,cu_lname,cu_add,cu_occ,cu_wplace,cu_nat,cu_tel,cu_fax,cu_email)values (@.P1,@.P2,@.P3,@.P4,@.P5,@.P6,@.P7,@.P8,@.P9,@.P10)"
Else
Strsql ="update customers setcu_fname=@.P2,cu_lname=@.P3,cu_add=@.P4,cu_occ=@.P5,cu_wplace=@.P6,cu_nat=@.P7,cu_tel=@.P8,cu_fax=@.P9,cu_email=@.P10where cu_id =@.P1"
End If
Dim C1 As New MISSQL
Dim cmd As SqlCommand =C1.CreateCommand(Strsql)
C1.CreateParam(cmd,"ITTTTTTTTT")
cmd.Parameters("@.P1").Value = DR("cu_id")
cmd.Parameters("@.P2").Value= DR("cu_fname")
cmd.Parameters("@.P3").Value = DR("cu_lname")
cmd.Parameters("@.P4").Value = DR("cu_add")
cmd.Parameters("@.P5").Value = DR("cu_occ")
cmd.Parameters("@.P6").Value = DR("cu_wplace")
cmd.Parameters("@.P7").Value= DR("cu_nat")
cmd.Parameters("@.P8").Value = DR("cu_tel")
cmd.Parameters("@.P9").Value = DR("cu_fax")
cmd.Parameters("@.P10").Value = DR("cu_email")

Dim Y As Integer = C1.Execute(cmd)
If Y > 0 Then
DR.AcceptChanges()
Else
Me.MyError ="Can not register"
End If

<---code above in this sub it can update only customers tables and when I tried to coding below

<----it alerts can not update

Dim DT1 As DataTable = DS.Tables(1)
Dim DR1 As DataRow = DT1.Rows(iRec)
If DR1.RowState = DataRowState.AddedThen
Iftxtcu_id.Text.Trim = "" Then
Me.MyError = "Please enter id"
Exit Sub
End If
DR1("cu_id")= txtcu_id.Text
End If
If Label2.Text.Trim = ""Then
DR1("op_date") = Label2.Text
End If

Dim StrSql1 As String
If DR1.RowState =DataRowState.Deleted Then
StrSql1 ="insert into open_ac (cu_id,op_date) values (@.P13,@.P14)"
Else
StrSql1 ="update open_ac set op_date=@.P14 where cu_id=@.P13"
End If
Dim C2 As New MISSQL
Dim cmd2 As SqlCommand =C2.CreateCommand(StrSql1)
C2.CreateParam(cmd2, "ID")
cmd2.Parameters("@.P1").Value = DR1("cu_id")
cmd2.Parameters("@.P2").Value = DR1("op_date")

Dim Y1 As Integer = C2.Execute(cmd2)
If Y1 > 0 Then
DR1.AcceptChanges()
Else
Me.MyError ="Can not register"
End If
End Sub
End Class

<--thisis class I use for connecting to database and call parameters...

MISSQLclass

ImportsSystem.Data.SqlClient
Public Class MISSQL
Dim PV As String ="Server=web_proj;uid=sa;pwd=sqldb;"
Dim m_Database As String = "c1_itc"
Public Strcon As String
Public Sub New()
Strcon = PV &"database=" & m_Database
End Sub
Public Sub New(ByVal DBName As String)
m_Database = DBName
Strcon = PV &"database=" & m_Database
End Sub
Public Property Database() As String
Get
Returnm_Database
End Get
Set(ByVal Value As String)
m_Database =Value
Strcon = PV& "database=" & m_Database
End Set
End Property

Public Function GetDataset(ByVal Strsql As String, _
Optional ByVal DatasetName As String= "Dataset1", _
Optional ByVal TableName As String ="Table") As DataSet
Dim DA As New SqlDataAdapter(Strsql,Strcon)
Dim DS As New DataSet(DatasetName)
Try
DA.Fill(DS,TableName)
Catch x1 As Exception
Err.Raise(60002, , x1.Message)
End Try
Return DS
End Function

Public Function GetDataTable(ByVal Strsql As String, _
Optional ByVal TableName AsString = "Table") As DataTable
Dim DA As New SqlDataAdapter(Strsql,Strcon)
Dim DT As New DataTable(TableName)
Try
DA.Fill(DT)
Catch x1 As Exception
Err.Raise(60002, , x1.Message)
End Try
Return DT
End Function

Public Function CreateCommand(ByVal Strsql As String) AsSqlCommand
Dim cmd As New SqlCommand(Strsql)
Return cmd
End Function

Public Function Execute(ByVal Strsql As String) AsInteger
Dim cmd As New SqlCommand(Strsql)
Dim X As Integer = Me.Execute(cmd)
Return X
End Function

Public Function Execute(ByRef Cmd As SqlCommand) AsInteger
Dim Cn As New SqlConnection(Strcon)
Cmd.Connection = Cn
Dim X As Integer
Try
Cn.Open()
X =Cmd.ExecuteNonQuery()
Catch
X = -1
Finally
Cn.Close()
End Try
Return X
End Function

Public Sub CreateParam(ByRef Cmd As SqlCommand, ByValStrType As String)
'T:Text, M:Memo, Y:Currency,D:Datetime, I:Integer, S:Single, B:Boolean, P: Picture
Dim i As Integer
Dim j As String
For i = 1 To Len(StrType)
j =UCase(Mid(StrType, i, 1))
Dim P1 AsNew SqlParameter
P1.ParameterName = "@.P" & i
Select Casej
Case "T"
P1.SqlDbType = SqlDbType.NVarChar
Case "M"
P1.SqlDbType = SqlDbType.Text
Case "Y"
P1.SqlDbType = SqlDbType.Money
Case "D"
P1.SqlDbType = SqlDbType.DateTime
Case "I"
P1.SqlDbType = SqlDbType.Int
Case "S"
P1.SqlDbType = SqlDbType.Decimal
Case "B"
P1.SqlDbType = SqlDbType.Bit
Case "P"
P1.SqlDbType = SqlDbType.Image
End Select
Cmd.Parameters.Add(P1)
Next
End Sub
End Class

<---Thank you in advance

<---and Thank you very much for all help

Hi,

Try the following code :(one part of your code)

Dim DT1As DataTable = DS.Tables(1)
Dim DR1As DataRow = DT1.Rows(iRec)
If DR1.RowState = DataRowState.AddedThen
If txtcu_id.Text.Trim =""Then
Me.MyError ="Please enter id"
Exit Sub
End If
DR1("cu_id") = txtcu_id.Text
End If

////If Label2.Text.Trim ="" Then
//// DR1("op_date") = Label2.Text
////End If

If Label2.Text.Trim <>""Then
DR1("op_date") = Label2.Text
End If

Dim StrSql1As String
////If DR1.RowState = DataRowState.Deleted Then
If DR1.RowState = DataRowState.AddedThen
StrSql1 ="insert into open_ac (cu_id,op_date) values (@.P13,@.P14)"
Else
StrSql1 ="update open_ac set op_date=@.P14 where cu_id=@.P13"
End If
Dim C2As New MISSQL
Dim cmd2As SqlCommand = C2.CreateCommand(StrSql1)
C2.CreateParam(cmd2,"ID")
cmd2.Parameters("@.P1").Value = DR1("cu_id")
cmd2.Parameters("@.P2").Value = DR1("op_date")

Dim Y1As Integer = C2.Execute(cmd2)
If Y1 > 0Then
DR1.AcceptChanges()
Else
Me.MyError ="Can not register"
End If

Thanks.

|||

So thank you very much for help for coding.

I tried to follow your code below.

>Dim DT1As DataTable = DS.Tables(1)
> Dim DR1As DataRow = DT1.Rows(iRec)
> If DR1.RowState = DataRowState.AddedThen
> If txtcu_id.Text.Trim =""Then
> Me.MyError ="Please enter id"
> Exit Sub
> End If
> DR1("cu_id") = txtcu_id.Text
> End If////If Label2.Text.Trim ="" Then
> //// DR1("op_date") = Label2.Text
> ////End If

> If Label2.Text.Trim <>""Then
> DR1("op_date") = Label2.Text
> End If

> Dim StrSql1As String
> ////If DR1.RowState = DataRowState.Deleted Then
> If DR1.RowState = DataRowState.AddedThen
> StrSql1 ="insert into open_ac (cu_id,op_date) values (@.P13,@.P14)"
> Else
> StrSql1 ="update open_ac set op_date=@.P14 where cu_id=@.P13"
> End If
> Dim C2As New MISSQL
> Dim cmd2As SqlCommand = C2.CreateCommand(StrSql1)
> C2.CreateParam(cmd2,"ID")
> cmd2.Parameters("@.P1").Value = DR1("cu_id")
> cmd2.Parameters("@.P2").Value = DR1("op_date")

> Dim Y1As Integer = C2.Execute(cmd2)
> If Y1 > 0Then
> DR1.AcceptChanges()
> Else
> Me.MyError ="Can not register"
> End If

But still show "Can not register" I don't know why?

and I try another way by follow your code and editing something below this can work. But I don't know what I did wrong above

Dim DT1 As DataTable = DS.Tables(1)
Dim DR1 As DataRow = DT1.Rows(iRec)
If DR1.RowState = DataRowState.Added Then
DR1("cu_id") = txtcu_id.Text
End If

'////If Label2.Text.Trim = "" Then
' //// DR1("op_date") = Label2.Text
' ////End If

If Label2.Text.Trim <> "" Then
DR1("op_date") = Label2.Text
End If

Dim StrSql1 As String
'////If DR1.RowState = DataRowState.Deleted Then
If DR1.RowState = DataRowState.Added Then
StrSql1 = "insert into open_ac (cu_id,op_date) values (@.P_C1,@.P_C2)"
Else
StrSql1 = "update open_ac set op_date=@.P_C2 where cu_id=@.P_C1"
End If
Dim C2 As New MISSQL
Dim cmd_child As SqlCommand = C2.CreateCommand(StrSql1)
C2.CreateParam_child(cmd_child, "TD")
cmd_child.Parameters("@.P_C1").Value = DR1("cu_id")
cmd_child.Parameters("@.P_C2").Value = DR1("op_date")
Dim Y1 As Integer = C2.Execute(cmd_child)
If Y1 > 0 Then
DR1.AcceptChanges()
End If
End Sub

Thank you very very much for your help

Friday, February 24, 2012

The resource cannot be found.

Blog Archive

About Me