Friday, February 24, 2012

how i use the N Switch in Stored Procedure

Any One Tell me how i can use N'Switch in SQL Server Stored Procedure .
When I use Such a this syntax i am encountered and stoped with a error.
the is below(Stoerd Prdocedure Code)
~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
CREATE proc sp_check_userpass(@.username nvarchar(50),@.password nvarchar(50),@.first nvarchar(50),@.last nvarchar(50),@.email nvarchar(50),@.stage int )as
declare @.cnt int
begin
select @.cnt=count(*) from member_info where (@.username=username and @.password=password)
if(@.cnt=0)
begin
insert into atable(first,last,username,password,stage) values(@.first,@.last,@.username,@.password,@.stage)
end
else
return @.cnt
end
GO
~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
Other thing is that when exec this store proc it must be return a value return back to the @.cnt but it is return only This( The command(s) Completed succefully).
Any One guide me how i can must be do .
i must clear it that i am using this query in ASP.NET SqlClient Object To Execute Query.
Thanx In Advance. ArQaI can't see any use of the unicode string constant prefix (i.e. N'Hello world') in your procedure. Did I miss something?

A UDF (User Defined Function) might better suit your needs than a stored procedure, although a UDF couldn't write to a table in order to log the failure for you. Very few client tools have the ability to support return values from stored procedures, although that is possible in most of the languages that support ADO.NET. You can get more information about this in KB#308051 (http://support.microsoft.com/default.aspx?scid=kb;en-us;308051).

-PatP

How I use OpenRowSet with Truested Connection?

Hi,
How I use OpenRowSet with Truested Connection?
SELECT *
FROM OpenRowSet('SQLOLEDB.1', '{SQL Server};SERVER=name', 'select a=1')
Thanks,
Jefferson ValimJefferson,
Here's an example that accesses the local server. Hopefully you
can adapt it for your needs.
select *
from OpenRowset(
'SQLOLEDB.1',
'Trusted_Connection=Yes;Database=Northwi
nd;Integrated
Security=SSPI;Data Source =(local)',
'select 1 as a')
Jefferson Valim wrote:

>Hi,
>How I use OpenRowSet with Truested Connection?
>SELECT *
> FROM OpenRowSet('SQLOLEDB.1', '{SQL Server};SERVER=name', 'select a=1')
>Thanks,
>
>Jefferson Valim
>
>|||Hi
And make sure that Security account delegation is available on the client
and sending server.
http://msdn.microsoft.com/library/d...>
ity_2gmm.asp
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Steve Kass" <skass@.drew.edu> wrote in message
news:ezo0X1wOFHA.904@.tk2msftngp13.phx.gbl...
> Jefferson,
> Here's an example that accesses the local server. Hopefully you
> can adapt it for your needs.
>
> select *
> from OpenRowset(
> 'SQLOLEDB.1',
> 'Trusted_Connection=Yes;Database=Northwi
nd;Integrated
> Security=SSPI;Data Source =(local)',
> 'select 1 as a')
> Jefferson Valim wrote:
>

How I solve this ?

I get this error when i try to connect with my sql,

I created my SQL with MS SQL 2005 Workgroup,

the error is:

Server Error in '/' Application.

The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'. However, the current database schema is not compatible with this version. You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Configuration.Provider.ProviderException: The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'. However, the current database schema is not compatible with this version. You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version.

First obvious question:

What happened when you extended the schema with ASPNET_REGSQL.EXE?

Jeff

|||I agree with Jeff. We need more details to help you. Any update?

How i should show toolbar

Hi group,
First time i am using reportservice.render method to render the report in my
aspx page. Here is the code snippet
result = rs.Render(report,"HTML4.0",null,null,paramValue,credentials,null,out
encoding,out mimetype,out paramvalues,out warning,out streamidsh.SessionId = rs.SessionHeaderValue.SessionId;
Response.BinaryWrite(result);
by doing so i am getting all the pages on the same aspx page and no tool
baar is shown. I also tried by passing toolbar = true in device info string
but no result.
So what i should do to show the toolbar .
Thanks
SandeepHi group,
i find this in msdn
If you render a report to HTML using the Render method, you cannot use the
report viewer toolbar as you can with a report that is rendered using URL
access. However, the Toolbar device information setting still affects the
appearance of the report. If you set Toolbar to a value of true, the report
server only renders the first page of the HTML report. If you set the value
to false, the report server renders all pages into a single Web page, using
<HR> tags as page delimiters.
Is there any work around.
Please help me out
Sandeep Garg
"Sandeep" <sandeep.garg@.approva.net> wrote in message
news:%23BBQR999EHA.3988@.TK2MSFTNGP11.phx.gbl...
> Hi group,
> First time i am using reportservice.render method to render the report in
> my aspx page. Here is the code snippet
> result => rs.Render(report,"HTML4.0",null,null,paramValue,credentials,null,out
> encoding,out mimetype,out paramvalues,out warning,out streamidsh.SessionId
> = rs.SessionHeaderValue.SessionId;
> Response.BinaryWrite(result);
> by doing so i am getting all the pages on the same aspx page and no tool
> baar is shown. I also tried by passing toolbar = true in device info
> string but no result.
> So what i should do to show the toolbar .
> Thanks
> Sandeep
>|||The toolbar is only available via URL access. If you are getting a rendered
report using the webservice, you will have to implement your own toolbar.
"Sandeep" <sandeep.garg@.approva.net> wrote in message
news:uTbKJl%239EHA.3120@.TK2MSFTNGP12.phx.gbl...
> Hi group,
> i find this in msdn
> If you render a report to HTML using the Render method, you cannot use the
> report viewer toolbar as you can with a report that is rendered using URL
> access. However, the Toolbar device information setting still affects the
> appearance of the report. If you set Toolbar to a value of true, the
> report server only renders the first page of the HTML report. If you set
> the value to false, the report server renders all pages into a single Web
> page, using <HR> tags as page delimiters.
> Is there any work around.
> Please help me out
> Sandeep Garg
> "Sandeep" <sandeep.garg@.approva.net> wrote in message
> news:%23BBQR999EHA.3988@.TK2MSFTNGP11.phx.gbl...
>> Hi group,
>> First time i am using reportservice.render method to render the report in
>> my aspx page. Here is the code snippet
>> result =>> rs.Render(report,"HTML4.0",null,null,paramValue,credentials,null,out
>> encoding,out mimetype,out paramvalues,out warning,out
>> streamidsh.SessionId = rs.SessionHeaderValue.SessionId;
>> Response.BinaryWrite(result);
>> by doing so i am getting all the pages on the same aspx page and no tool
>> baar is shown. I also tried by passing toolbar = true in device info
>> string but no result.
>> So what i should do to show the toolbar .
>> Thanks
>> Sandeep
>>
>|||Thaks for the suggestion.
Can u give me some clue how to implement my own toolbar. From where i got
the total page count and all the other required stuff.
Sandeep
"FNDS" <absolutelynospam@.nodomain_.com> wrote in message
news:exk34%23%239EHA.3616@.TK2MSFTNGP11.phx.gbl...
> The toolbar is only available via URL access. If you are getting a
> rendered report using the webservice, you will have to implement your own
> toolbar.
> "Sandeep" <sandeep.garg@.approva.net> wrote in message
> news:uTbKJl%239EHA.3120@.TK2MSFTNGP12.phx.gbl...
>> Hi group,
>> i find this in msdn
>> If you render a report to HTML using the Render method, you cannot use
>> the report viewer toolbar as you can with a report that is rendered using
>> URL access. However, the Toolbar device information setting still affects
>> the appearance of the report. If you set Toolbar to a value of true, the
>> report server only renders the first page of the HTML report. If you set
>> the value to false, the report server renders all pages into a single Web
>> page, using <HR> tags as page delimiters.
>> Is there any work around.
>> Please help me out
>> Sandeep Garg
>> "Sandeep" <sandeep.garg@.approva.net> wrote in message
>> news:%23BBQR999EHA.3988@.TK2MSFTNGP11.phx.gbl...
>> Hi group,
>> First time i am using reportservice.render method to render the report
>> in my aspx page. Here is the code snippet
>> result =>> rs.Render(report,"HTML4.0",null,null,paramValue,credentials,null,out
>> encoding,out mimetype,out paramvalues,out warning,out
>> streamidsh.SessionId = rs.SessionHeaderValue.SessionId;
>> Response.BinaryWrite(result);
>> by doing so i am getting all the pages on the same aspx page and no tool
>> baar is shown. I also tried by passing toolbar = true in device info
>> string but no result.
>> So what i should do to show the toolbar .
>> Thanks
>> Sandeep
>>
>>
>|||Hmm. I haven't tackled creating my own toolbar to show a rendered report.
If someone has and is willing to share, please post a sample or URL...
Thx
"Sandeep" <sandeep.garg@.approva.net> wrote in message
news:%23gB0X$F%23EHA.2112@.TK2MSFTNGP10.phx.gbl...
> Thaks for the suggestion.
> Can u give me some clue how to implement my own toolbar. From where i got
> the total page count and all the other required stuff.
> Sandeep
> "FNDS" <absolutelynospam@.nodomain_.com> wrote in message
> news:exk34%23%239EHA.3616@.TK2MSFTNGP11.phx.gbl...
>> The toolbar is only available via URL access. If you are getting a
>> rendered report using the webservice, you will have to implement your own
>> toolbar.
>> "Sandeep" <sandeep.garg@.approva.net> wrote in message
>> news:uTbKJl%239EHA.3120@.TK2MSFTNGP12.phx.gbl...
>> Hi group,
>> i find this in msdn
>> If you render a report to HTML using the Render method, you cannot use
>> the report viewer toolbar as you can with a report that is rendered
>> using URL access. However, the Toolbar device information setting still
>> affects the appearance of the report. If you set Toolbar to a value of
>> true, the report server only renders the first page of the HTML report.
>> If you set the value to false, the report server renders all pages into
>> a single Web page, using <HR> tags as page delimiters.
>> Is there any work around.
>> Please help me out
>> Sandeep Garg
>> "Sandeep" <sandeep.garg@.approva.net> wrote in message
>> news:%23BBQR999EHA.3988@.TK2MSFTNGP11.phx.gbl...
>> Hi group,
>> First time i am using reportservice.render method to render the report
>> in my aspx page. Here is the code snippet
>> result =>> rs.Render(report,"HTML4.0",null,null,paramValue,credentials,null,out
>> encoding,out mimetype,out paramvalues,out warning,out
>> streamidsh.SessionId = rs.SessionHeaderValue.SessionId;
>> Response.BinaryWrite(result);
>> by doing so i am getting all the pages on the same aspx page and no
>> tool baar is shown. I also tried by passing toolbar = true in device
>> info string but no result.
>> So what i should do to show the toolbar .
>> Thanks
>> Sandeep
>>
>>
>>
>

How I see what Service pack I have

I cannot find what service pack I had install in my Server.
Can anyone tell me where I can see it.
RafaelSELECT serverproperty('productlevel')
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Rafael Tejera" <rafaeltejera@.hotmail.com> wrote in message
news:ux2PQ82fGHA.324@.TK2MSFTNGP02.phx.gbl...
>I cannot find what service pack I had install in my Server.
> Can anyone tell me where I can see it.
>
> Rafael
>|||Rafael,
Check out:
How to identify your SQL Server version and edition
http://support.microsoft.com/kb/321185/en-us
HTH
Jerry
"Rafael Tejera" <rafaeltejera@.hotmail.com> wrote in message
news:ux2PQ82fGHA.324@.TK2MSFTNGP02.phx.gbl...
>I cannot find what service pack I had install in my Server.
> Can anyone tell me where I can see it.
>
> Rafael
>

How I see what Service pack I have

I cannot find what service pack I had install in my Server.
Can anyone tell me where I can see it.
RafaelSELECT serverproperty('productlevel')
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Rafael Tejera" <rafaeltejera@.hotmail.com> wrote in message
news:ux2PQ82fGHA.324@.TK2MSFTNGP02.phx.gbl...
>I cannot find what service pack I had install in my Server.
> Can anyone tell me where I can see it.
>
> Rafael
>|||Rafael,
Check out:
How to identify your SQL Server version and edition
http://support.microsoft.com/kb/321185/en-us
HTH
Jerry
"Rafael Tejera" <rafaeltejera@.hotmail.com> wrote in message
news:ux2PQ82fGHA.324@.TK2MSFTNGP02.phx.gbl...
>I cannot find what service pack I had install in my Server.
> Can anyone tell me where I can see it.
>
> Rafael
>

How I resolved "Failure sending mail: The Report Server has encountered a configuration er

Hi
It seems there are various issues that lead to this problem.
If no other postings appear to solve your particular problem check
this...
Are all subscriptions failing or just some of them?
If its just some of them your problem maybe that the failing reports
have a subscription owner of a specific domain user id that has been
removed.
1) Check the subscription to the failing report and see if there is an
"Owner" specified under the "Delivered By" option.
2) If there is, check with your admin that this id still exists.
In my case this was an ex-employee who had been removed.
3) If the id has been deleted, create a clone of the subscription and
delete the failing subscription. Test your new subscription by manually
starting the appropriate job on the report server.
TIP - I identify the correct JobName with this piece of SQL on the
Report Server. You might need to adapt it slightly but it works for me!
SELECT J.name AS JobName
,C.Name AS ReportName
,CASE CHARINDEX('RenderFormat', S.ExtensionSettings)
WHEN 0 THEN 'PATH'
ELSE SUBSTRING(S.ExtensionSettings
,CHARINDEX('RenderFormat', S.ExtensionSettings) + 26
,CHARINDEX('</Value>', S.ExtensionSettings,
CHARINDEX('RenderFormat', S.ExtensionSettings)) -
(CHARINDEX('RenderFormat', S.ExtensionSettings) + 26))
END AS ExportType
,S.DeliveryExtension AS DeliveryMethod
,S.DEscription + '...' AS Recipients
FROM ReportServer.dbo.Catalog C
,ReportServer.dbo.ReportSchedule R
,ReportServer.dbo.Subscriptions S
,msdb.dbo.sysjobs J
WHERE C.ItemID = R.ReportID
AND R.SubscriptionID = S.SubscriptionID
AND J.name = CAST(R.ScheduleID AS VARCHAR(255))
ORDER BY 2
,3I'm running into this as well excep I am the owner of the report...
The one thing that has occured recently is that my PC was switched from one
domain to another. The new domian I am operating on is setup w/ active
directory.
Any ideas?
Thanks,
Ben Sullins
"perrydyball@.hotmail.com" wrote:
> Hi
> It seems there are various issues that lead to this problem.
> If no other postings appear to solve your particular problem check
> this...
> Are all subscriptions failing or just some of them?
> If its just some of them your problem maybe that the failing reports
> have a subscription owner of a specific domain user id that has been
> removed.
> 1) Check the subscription to the failing report and see if there is an
> "Owner" specified under the "Delivered By" option.
> 2) If there is, check with your admin that this id still exists.
> In my case this was an ex-employee who had been removed.
> 3) If the id has been deleted, create a clone of the subscription and
> delete the failing subscription. Test your new subscription by manually
> starting the appropriate job on the report server.
> TIP - I identify the correct JobName with this piece of SQL on the
> Report Server. You might need to adapt it slightly but it works for me!
> SELECT J.name AS JobName
> ,C.Name AS ReportName
> ,CASE CHARINDEX('RenderFormat', S.ExtensionSettings)
> WHEN 0 THEN 'PATH'
> ELSE SUBSTRING(S.ExtensionSettings
> ,CHARINDEX('RenderFormat', S.ExtensionSettings) + 26
> ,CHARINDEX('</Value>', S.ExtensionSettings,
> CHARINDEX('RenderFormat', S.ExtensionSettings)) -
> (CHARINDEX('RenderFormat', S.ExtensionSettings) + 26))
> END AS ExportType
> ,S.DeliveryExtension AS DeliveryMethod
> ,S.DEscription + '...' AS Recipients
> FROM ReportServer.dbo.Catalog C
> ,ReportServer.dbo.ReportSchedule R
> ,ReportServer.dbo.Subscriptions S
> ,msdb.dbo.sysjobs J
> WHERE C.ItemID = R.ReportID
> AND R.SubscriptionID = S.SubscriptionID
> AND J.name = CAST(R.ScheduleID AS VARCHAR(255))
> ORDER BY 2
> ,3
>

How i Read the data from MS-Excel and insert into Sql-server table

Hi All,

I have one excel file the format is some thing like this->

These are the column name of the excel file

Avg. Stop Time by Customer-->> AMERICAN WOODMARK CORP MASTERBRAND CABINETS PANASONIC SIEMENS ACUSON WHIRLPOOL/QUALITY EXPRESS

All the column have Some Numeric value.

And i have Sql-Table Look like this->

FacilityCode ModeID VariableCode ByParamCode ParamValue WeekNumber WeekStart ActualValue CreateDate CreateBy LastUpdatedDate Comment

In this table we have column "ActualValue" that need to get the value from the Excel sheet

Like-

Avg. Stop Time by Customer AMERICAN WOODMARK CORP-Actual Value

Avg. Stop Time by Customer MASTERBRAND CABINETS--Actual Value

Avg. Stop Time by Customer PANASONIC--Actual Value

Avg. Stop Time by Customer SIEMENS ACUSON--Actual Value

Avg. Stop Time by Customer WHIRLPOOL/QUALITY EXPRESS--Actual Value

But the problem i have is How i read the data from Excel because they not in same column

Example->

Avg. Stop Time by Customer-->>

AMERICAN WOODMARK CORP

Are 2 different column and i want the value of AMERICAN WOODMARK CORP and store in the "Actual Value" column in table.

May you show us SQL SELECT command ?

How I post a message on MSMQ?

Hi,
I want to execute a T-SQL for adding a message on a message queue, I know
that I can create a DTS task but I want to include it on a trigger. I found
extended procedures like xp_createprivatequeue and so on but no procedure to
push a message, Also I know that I can create my own xp to do it but I was
wondering if there is a simpler way,
thanks!
Salva
Salvador Alvarez Patuel
salvador.alvarez@.exony.com
Exony Ltd - London, UKHi
That is an undocumented XP and you should not use it.
SQL Server has no built-in methods to acces MSMQ. That belongs in a
middle-tier or your application.
Regards
Mike
"Salvador" wrote:

> Hi,
> I want to execute a T-SQL for adding a message on a message queue, I know
> that I can create a DTS task but I want to include it on a trigger. I foun
d
> extended procedures like xp_createprivatequeue and so on but no procedure
to
> push a message, Also I know that I can create my own xp to do it but I was
> wondering if there is a simpler way,
> thanks!
> Salva
> --
> Salvador Alvarez Patuel
> salvador.alvarez@.exony.com
> Exony Ltd - London, UK

How I join in this case?

Hello
I would like to join Stats.ClientID with Clients.ClientID using an inner
join to get hold of Clients.Client
but I cant figure out how....... (thanks to Rebecca for providing help
earlier)
SELECT
MonthAdded AS StartDate
,DATEADD(d,-1 ,DATEADD(m,1,MonthAdded)) AS EndDate
,ClientID
,Total AS Visitors
FROM
(
SELECT
CONVERT(DATETIME,CONVERT(CHAR(7),Created
,121) + '-01 23:59:59',121 ) AS
MonthAdded
,Stats.ClientID
,COUNT(*) AS Total
FROM
Stats
GROUP BY
CONVERT(DATETIME,CONVERT(CHAR(7),Created
,121) + '-01 23:59:59',121)
, Stats.ClientID
) vwResultsLike this perhaps:
SELECT
MonthAdded AS StartDate
,DATEADD(d,-1 ,DATEADD(m,1,MonthAdded)) AS EndDate
,C.ClientID
,Total AS Visitors
FROM
(...
... ) vwResults
JOIN Clients AS C
ON vwResults.client_id = C.client_id ;
David Portas
SQL Server MVP
--|||Try this:
SELECT
Client
,MonthAdded AS StartDate
,DATEADD(d,-1 ,DATEADD(m,1,MonthAdded)) AS EndDate
,ClientID
,Total AS Visitors
FROM
(
SELECT
Clients.Client,
CONVERT(DATETIME,CONVERT(CHAR(7),stat.Created,121) + '-01 23:59:59',121
) AS
MonthAdded
,Stats.ClientID
,COUNT(*) AS Total
FROM
Stats, Client
WHERE
Stats.ClientID = Clients.ClientID
GROUP BY
CONVERT(DATETIME,CONVERT(CHAR(7),Created
,121) + '-01 23:59:59',121)
, Stats.ClientID
, Clients.Client
) vwResults|||ahh thx...
doh, more coffee for me
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1128499953.118747.188950@.g44g2000cwa.googlegroups.com...
> Like this perhaps:
> SELECT
> MonthAdded AS StartDate
> ,DATEADD(d,-1 ,DATEADD(m,1,MonthAdded)) AS EndDate
> ,C.ClientID
> ,Total AS Visitors
> FROM
> (...
> ... ) vwResults
> JOIN Clients AS C
> ON vwResults.client_id = C.client_id ;
> --
> David Portas
> SQL Server MVP
> --
>|||If you want the end date to have the last part of the day,
Take off the time part from the convert, and take away 3ms from the EndDate.
This will give you the full scope of the month and not miss all but 3ms of
the StartDay.
SELECT
MonthAdded AS StartDate
,DATEADD(ms,-3 ,DATEADD(m,1,MonthAdded)) AS EndDate
,ClientID
,Total AS Visitors
FROM
(
SELECT
CONVERT(DATETIME,CONVERT(CHAR(7),Created
,121) + '-01',121 ) AS
MonthAdded
,Stats.ClientID
,COUNT(*) AS Total
FROM
Stats
GROUP BY
CONVERT(DATETIME,CONVERT(CHAR(7),Created
,121) + '-01',121)
, Stats.ClientID
) vwResults
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:%23la1mJYyFHA.3236@.TK2MSFTNGP14.phx.gbl...
> Hello
> I would like to join Stats.ClientID with Clients.ClientID using an inner
> join to get hold of Clients.Client
> but I cant figure out how....... (thanks to Rebecca for providing help
> earlier)
>
> SELECT
> MonthAdded AS StartDate
> ,DATEADD(d,-1 ,DATEADD(m,1,MonthAdded)) AS EndDate
> ,ClientID
> ,Total AS Visitors
> FROM
> (
> SELECT
> CONVERT(DATETIME,CONVERT(CHAR(7),Created
,121) + '-01 23:59:59',121 )
AS
> MonthAdded
> ,Stats.ClientID
> ,COUNT(*) AS Total
> FROM
> Stats
> GROUP BY
> CONVERT(DATETIME,CONVERT(CHAR(7),Created
,121) + '-01 23:59:59',121)
> , Stats.ClientID
> ) vwResults
>|||Hi again Rebecca
I got to work now perfectly, but now I need to add another view.... that
shows for "each w" (and works even next year) :S
Tried some but it left a mess......
is it possible to do such grouping with some modifications?
so startdate is (for this w as an example)
2005-10-03 2005-10-09
TIA
/Lasse
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:4343a346$0$134$7b0f0fd3@.mistral.news.newnet.co.uk...
> If you want the end date to have the last part of the day,
> Take off the time part from the convert, and take away 3ms from the
EndDate.
> This will give you the full scope of the month and not miss all but 3ms of
> the StartDay.
>
> SELECT
> MonthAdded AS StartDate
> ,DATEADD(ms,-3 ,DATEADD(m,1,MonthAdded)) AS EndDate
> ,ClientID
> ,Total AS Visitors
> FROM
> (
> SELECT
> CONVERT(DATETIME,CONVERT(CHAR(7),Created
,121) + '-01',121 ) AS
> MonthAdded
> ,Stats.ClientID
> ,COUNT(*) AS Total
> FROM
> Stats
> GROUP BY
> CONVERT(DATETIME,CONVERT(CHAR(7),Created
,121) + '-01',121)
> , Stats.ClientID
> ) vwResults
>
> "Lasse Edsvik" <lasse@.nospam.com> wrote in message
> news:%23la1mJYyFHA.3236@.TK2MSFTNGP14.phx.gbl...
23:59:59',121 )
> AS
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. You have been toldthis several times in this
newsgroup.
Instead of computing things like this, why don't you build a reporting
calendar table with date ranges? Instead of storing temporal data in
strings, why don't you use temproal data types? Oh, the standard
syntax in SQL is CAST(), not CONVERT(). The prefix "vw" is eitehr a
short hand for "VolksWagen" or a violationof ISO-11179 rules for naming
data elements.|||easier with "testing-code" i guess
CREATE TABLE #Test (
SomePk int identity(1,1) NOT NULL,
Person char(1) NOT NULL,
Datecreated datetime NOT NULL
)
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-10-03')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-10-04')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-10-04')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-10-05')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-10-06')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-10-11')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-10-14')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-10-15')
INSERT INTO #Test(Person,Datecreated)VALUES('A','200
5-10-16')
INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-10-03')
INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-10-04')
INSERT INTO #Test(Person,Datecreated)VALUES('B','200
5-10-05')
SELECT * FROM #Test
/*
Desired result:
Startdate Enddate Person Count
2005-10-03 2005-10-09 A 5
2005-10-10 2005-10-16 A 4
2005-10-17 2005-10-23 B 3
or if above is too complicated/impossible:
Year (?) Month (?) Person Count
2005 40 A 5
2005 41 A 4
2005 40 B 3
*/
DROP TABLE #Test
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1128607316.078911.121310@.z14g2000cwz.googlegroups.com...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. You have been toldthis several times in this
> newsgroup.
> Instead of computing things like this, why don't you build a reporting
> calendar table with date ranges? Instead of storing temporal data in
> strings, why don't you use temproal data types? Oh, the standard
> syntax in SQL is CAST(), not CONVERT(). The prefix "vw" is eitehr a
> short hand for "VolksWagen" or a violationof ISO-11179 rules for naming
> data elements.
>|||I don't understand why your first desired result has two rows for the
same month. Going by your second example this seems to be a mistake. I
think you want to GROUP BY DATEDIFF(MONTH, '/* some fixed date */,
datecreated). For the dates join to a Calendar table.
As posted your schema and data makes no sense. Why the duplicate rows?
You need a unique constraint on (person,datecreated).
David Portas
SQL Server MVP
--|||David,
since they are different ws. nothing to do with months
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1128608758.392918.172520@.g49g2000cwa.googlegroups.com...
> I don't understand why your first desired result has two rows for the
> same month. Going by your second example this seems to be a mistake. I
> think you want to GROUP BY DATEDIFF(MONTH, '/* some fixed date */,
> datecreated). For the dates join to a Calendar table.
> As posted your schema and data makes no sense. Why the duplicate rows?
> You need a unique constraint on (person,datecreated).
> --
> David Portas
> SQL Server MVP
> --
>

How I get time value:

I have value '1/1/2005 12:53:10' (smalldatetime). I wanna get time value of
it. So I will get '12:53:10'. How to get? and what type suitable for this
problem?CONVERT(CHAR(8), your_small_date_time, 8)
Jacco Schalkwijk
SQL Server MVP
"Bpk. Adi Wira Kusuma" <adi_wira_kusuma@.yahoo.com.sg> wrote in message
news:ud9QH43lFHA.3656@.TK2MSFTNGP09.phx.gbl...
>I have value '1/1/2005 12:53:10' (smalldatetime). I wanna get time value of
> it. So I will get '12:53:10'. How to get? and what type suitable for this
> problem?
>|||hi
hope this will give u an idea
select CONVERT(CHAR(8), getdate(), 8)
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Bpk. Adi Wira Kusuma" wrote:

> I have value '1/1/2005 12:53:10' (smalldatetime). I wanna get time value o
f
> it. So I will get '12:53:10'. How to get? and what type suitable for this
> problem?
>
>

How I get the random row from the table?

When I execute the following query several times, I get the same row if there is no new data inserted:
SELECT TOP 1 * FROM TableName
Is there a way to get a random row from the table? Thanks in advance.Declare @.value As Integer
set @.value = (RAND() * (select count(*) from table))+1

select *
from table
where id = @.value|||If I'm not mistaken this will require that there are no gaps whatsoever in the ID-field which is quite rare I think. You would have to make a while-loop and check if the ID exists I belive, and then loop for each ID that doesn't exist. have never done this myself so there might be a better way...|||The only issue with that is that you have to have an unique integer for your ID. Additionally your ID can not have gaps and must start at one (or is it zero).

Perhaps something like this??
----
Declare @.value As Integer
set @.value = (RAND() * (select count(*) from table))+1

executesql 'select top ' + @.value + ' from table order by XYZ'
----

then you need to select the first one or last one that is selected...

I don't know how you would do it though...

HTH|||If I'm not mistaken this will require that there are no gaps whatsoever in the ID-field which is quite rare I think

Providing the id column is unique, then

Declare @.value As Integer
set @.value = (RAND() * (select count(*) from table))+1

select *
from
(select columns,
(select count(*) from table where id <= t1.id) AS ID2
from table t1) v
where v.id2 = @.value|||Unfortunately I can't test this out on the machine I am on, for the above example wouldn't you need a group by clause in your select count(*) and a having instead of a where clause...

so something like...

Declare @.value As Integer
set @.value = (RAND() * (select count(*) from table))+1

select *
from
(select columns,
(select count(*) from table where id <= t1.id group by t1.id) AS ID2
from table t1) v
having v.id2 = @.value

once again, sorry I can't check it.|||No aggregate functions have been computed on the set 'V', meaning that the group by and having clauses are not required.

Consider,

Select a, b, (select count(*) from table) AS COUNT
from table t1
group by a, b

This is invalid as COUNT is interpreted as a column as opposed to an aggregate function of t1.|||Okie cool. :) Like I said, I couldn't check so. ;)

It's an interesting problem though... personally I wouldn't try and get the database to do this...

I'd get the app to generate a random id to select and just do a standard query on that id...

Each to their own though. :)|||Thank you for your all helps.

Since the ID field (primary key) does not start at one and also there may be a gap in this field (some data may be deleted), I modified the query posted by r123456

Declare @.value As Integer
SET @.value = (RAND() * (SELECT Count(*) FROM Users)) + 1

SELECT TOP 1 *
FROM Users
WHERE UserID >= @.value
ORDER BY UserID

How do you think about it?|||Try this and see:
SELECT TOP 1 * FROM TableName
order by newid()|||Originally posted by gyuan
Thank you for your all helps.

Since the ID field (primary key) does not start at one and also there may be a gap in this field (some data may be deleted), I modified the query posted by r123456

Declare @.value As Integer
SET @.value = (RAND() * (SELECT Count(*) FROM Users)) + 1

SELECT TOP 1 *
FROM Users
WHERE UserID >= @.value
ORDER BY UserID

How do you think about it?

The code above will still encounter problems with the gaps and the not starting at zero...

this is the one you want

Originally posted by r123456

Declare @.value As Integer
set @.value = (RAND() * (select count(*) from table))+1

select *
from
(select columns,
(select count(*) from table where id <= t1.id) AS ID2
from table t1) v
where v.id2 = @.value|||The problem you get with this solution

Declare @.value As Integer
SET @.value = (RAND() * (SELECT Count(*) FROM Users)) + 1

SELECT TOP 1 *
FROM Users
WHERE UserID >= @.value
ORDER BY UserID

is say you have 5000 records and you delete 4000 records.

Your rand value will be between 1 and 4000 but your max UserId is 5000 anything with an Id over 4000 is pretty much unreachable...

I think you'd be better with this...

Declare @.value As Integer
SET @.value = (RAND() * (SELECT max(UserID) FROM Users)) + 1

SELECT TOP 1 *
FROM Users
WHERE UserID >= @.value
ORDER BY UserID

It would mean when gaps occur the row after the gap would be hit more often, but atleast you would cover your entire collection of rows.

Hope that makes sense.|||quote:
------------------------
Originally posted by r123456

Declare @.value As Integer
set @.value = (RAND() * (select count(*) from table))+1

select *
from
(select columns,
(select count(*) from table where id <= t1.id) AS ID2
from table t1) v
where v.id2 = @.value

------------------------

What does columns stand for in the query?|||errr...won't my simple statement solve it?

SELECT TOP 1 * FROM TableName
order by newid()

I don't get it...|||the columns you want to select eg * or username, firstname, lastname etc...|||Hi Patrick,

I'm really not sure how your solution would work, what is the newid()?|||Its a build in command specifically to be use to "select random rows".
It is also use as a comand to auto gen Unique Identifier ids.

But when use in "order by newid()" it generates random rows.

Try it out and see...it works...unless..well theres something I'm missing in the whole discussion.|||Originally posted by Patrick Chua
errr...won't my simple statement solve it?

SELECT TOP 1 * FROM TableName
order by newid()

I don't get it...

It works too, but it takes 2 seconds, a little slower. Now I know the function NewID() and it helps. Thanks.|||rokslide,

That is a good idea to replace Count(*) to MAX(UserID). Thanks.|||Cool, I think I'll have to have a look into that one some time. :)

Thanks Patrick. :)|||No worries gyuan, please note though that (as I said above) it's not truely random.|||Correct.

Count(*) should not be replaced with max(id). The reason being that should a "gap" occur then the probability is increased for those values that occur past the "gap".

If TOP * 1 is used in conjunction with max(id) then only the first value past a "gap" value will be returned, should @.value be equal to a "gap" value.|||I think Patrick's query is better:

SELECT TOP 1 *
FROM Users
ORDER BY NewID()

although the running time is a little longer.|||I've used Patrick's method in the past with success.|||Isn't anyone going to ask WHY do you want to do this?|||I use Patricks method daily and it works out great for me.
NewID() takes a little longer due to it generates a GUID but it is truely random.|||Originally posted by Brett Kaiser
Isn't anyone going to ask WHY do you want to do this?

Brett ...
You are always after the "why" instead of the "how"? I like you for the spirit coz I believe "Prevention is better than cure".|||How's easy.....

And thanks...

Seen to many rocket ships built...|||I have to admit to orginally thinking why would you want to, but I have seen a few "scuffles" break out on here over the "why" of things so I decided to leave it alone.

Then of course the curiosity took over and I started to think,... hey, how would you do that...

anyhow...

r123456

with this statement...

Count(*) should not be replaced with max(id). The reason being that should a "gap" occur then the probability is increased for those values that occur past the "gap".

but if you use count and then compare count to the ID value you are going to completely miss some sections of the data entirely (eg. they will never have a chance to be selected). See the example that I noted eariler. The the max(id) option atleast you cover your entire span of data.

Of course Patrick's solution will cover everything perfectly so....|||Not true.

ID | ID2
1 1
2 2
3 3
4 4
5 5

Delete from table where id=2 OR id=3;

ID | ID2
1 1
4 2
5 3

You have two solutions. One of which requires very little code and an SQL Server function. The other requires a unique id, which for example can be the value of ROWID for an Oracle database.|||Ah yes, sorry, I forgot the second version of your solution with the new ID. :)

My fault entirely.

How i get "rank" in this one

Hello
I have this query that works good, but I cant figure out how to get the rank
and order by it desc.
SELECT * FROM Companies WHERE CONTAINS(*,'something')
how I add rank to this one?
TIA
/Lasse
you can't you need to use the contains table syntax for this, ie
SELECT * FROM Companies join ContainsTable(Companies, *, 'something') as FT
on FT.[key]=Companies.pk
where pk is the primary key/unique key of the table you are full text
indexing.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:%23b1BtZ8yEHA.3808@.tk2msftngp13.phx.gbl...
> Hello
> I have this query that works good, but I cant figure out how to get the
rank
> and order by it desc.
> SELECT * FROM Companies WHERE CONTAINS(*,'something')
> how I add rank to this one?
> TIA
> /Lasse
>
|||Hilary,
hmm, one problem, when i search for both Firstname and Lastname using OR it
doesnt really get the "most correct" values first, they get the highest rank
but...... and if I use AND nothing shows since no columns contain all
"keywords"
is there a way to solve this?
/Lasse
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uTfQ9M9yEHA.1196@.TK2MSFTNGP15.phx.gbl...
> you can't you need to use the contains table syntax for this, ie
> SELECT * FROM Companies join ContainsTable(Companies, *, 'something') as
FT
> on FT.[key]=Companies.pk
> where pk is the primary key/unique key of the table you are full text
> indexing.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
>
> "Lasse Edsvik" <lasse@.nospam.com> wrote in message
> news:%23b1BtZ8yEHA.3808@.tk2msftngp13.phx.gbl...
> rank
>
|||Hilary,
this one returns rank=128
5472 NULL 803 21 GVLE Gvle Valbogatan 35 Andersson & Edlund Sverige
5472
this one rank=112
3714 Sandvikens Kommun Kultur och Fritid 811 80 SANDVIKEN Sandviken
Rita Andersson Sverige 3714
when i search for 'rita OR andersson'
not sure i understand how that first one got higher value
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:OYVRKr9yEHA.748@.TK2MSFTNGP14.phx.gbl...
> Hilary,
> hmm, one problem, when i search for both Firstname and Lastname using OR
it
> doesnt really get the "most correct" values first, they get the highest
rank[vbcol=seagreen]
> but...... and if I use AND nothing shows since no columns contain all
> "keywords"
> is there a way to solve this?
> /Lasse
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uTfQ9M9yEHA.1196@.TK2MSFTNGP15.phx.gbl...
as[vbcol=seagreen]
> FT
the
>
|||Lasse,
Adding RANK is easy, understanding the results is a bit more difficult as
you will need to supply additional information about your environment in
order to understand the RANK values that are returned from your FTS queries.
First of all, you should review the SQL Server 2000 BOL title "Full-text
Search Recommendations" and the next to last paragraph on RANK.
Another factor is the number of rows in your table must be "statistically
significantly", i.e.. at least 10,000 rows in order for the query specific
RANK values to return the executants results. If you are testing with a very
small table, then this can explain why the rank values are not what you are
expecting.
As for searching [both Firstname and Lastname using OR it doesn't really get
the "most correct" values first, they get the highest rank
but...... and if I use AND nothing shows since no columns contain all
"keywords"], this is a different problem as SQL Server 2000 (correct?) does
not search across columns, see KB articles:
286787 (Q286787) FIX: Incorrect Results From Full-Text Search on Several
Columns
http://support.microsoft.com/default...b;en-us;286787
294809 (Q294809) FIX: Full-Text Search Queries with CONTAINS Clause Search
Across Columns
http://support.microsoft.com/default...b;en-us;294809
Note, while the above KB articles are specific to SQL Server 7.0, they also
apply to SQL Server 2000 as SQL Server 7.0's behavior was fixed to
correspond to the default SQL Server 2000 behavior.
Finally, one way to join multiple columns in one CONTAINSTABLE query and
influence the rank value of one column over another via the weight parameter
is the following:
use Northwind
SELECT e.LastName, e.FirstName, e.Title, e.Notes, B.[KEY], B.[RANK] as
B_RANK, A.[RANK] as A_RANK
from Employees AS e,
containstable(Employees, Notes, 'ISABOUT (BA weight (.2) )') as A,
containstable(Employees, Title, 'Sales') as B
where
A.[KEY] = e.EmployeeID and
B.[KEY] = e.EmployeeID
Note, the AND condition between the Primary key (PK) values as you can also
make this an OR clause, but you may need to use a DISTINCT parameter to
remove duplicate results. Keep in mind, that you still need a "statistically
significantly" number of rows to get the best results.
Regards,
John
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:O#mzCy9yEHA.2012@.TK2MSFTNGP15.phx.gbl...
> Hilary,
> this one returns rank=128
> 5472 NULL 803 21 GVLE Gvle Valbogatan 35 Andersson & Edlund
Sverige
> 5472
> this one rank=112
> 3714 Sandvikens Kommun Kultur och Fritid 811 80 SANDVIKEN Sandviken
> Rita Andersson Sverige 3714
> when i search for 'rita OR andersson'
> not sure i understand how that first one got higher value
>
>
>
> "Lasse Edsvik" <lasse@.nospam.com> wrote in message
> news:OYVRKr9yEHA.748@.TK2MSFTNGP14.phx.gbl...
> it
> rank
> as
> the
>

How I do to eliminate duplicate rows?

Hello,
I need to eliminate the duplicated rows in sql server 2000, but the duplicate is only for some fields of the row. However, I need all the fields of the row. For example, I have the next structure:
Id_type, number_type, date, diagnosis, sex, age, city

After many analysis I get many rows where the tree first field are repeated, so I need to leave only one but with the all another fields. This is because I need only the first time when the diagnosis appear.

How I can do it?

Thank you very much.

Regards,
Angela

As I understand you issue, when there are rows that have the same values for (ID_Type, Number_Type, Date), you wish to keep ONLY one (1) row, and it doens't matter which one of the duplicated rows is kept.

What if the non-duplicated fields is different, i.e., different diagnosis, or different sex, or different age, or different city (if that could happen)?

There are several methods to accomplish this task. First, a little more information is useful:

Version of SQL Server?

Are there other tables that have foreign key relationships to this table?

Approximately how much data is in the table (rows)?

Are there periods of time when no one is using the table?

Send this information and we can better assist you.

|||Hi Arnie, thanks you for your response.

Well, the problem is the information is the very bad quality .... so, I suppose that I get one row to the first time that some diagnosis appear to the pacient, but with data this not happen. So

I have found that to the same ID_Type, Number_Type, Date and same diagnosis exists rows that they have different sex or age or any other field, so I need to select only one, because I need the first time that this diagnosis appears...

Let me to response the questions:

Version of SQL Server?

R: Sql server 2000

Are there other tables that have foreign key relationships to this table?

R: yes, because some fields are only codes

Approximately how much data is in the table (rows)?

R: this table have 25 millions of rows... so much...

Are there periods of time when no one is using the table?

R: yes, this table is to datamining exercise.

I appreciate so much your help.

|||This kb should help:
http://support.microsoft.com/kb/139444|||

Here is an article that provides a bit more detailed instructions that the kb article.

http://www.sql-server-performance.com/rd_delete_duplicates.asp

One issue that neither article touches on is the size of your table. If there are many duplicates, attempting to work on the entire table could be a major struggle for your server due to the amount of Transaction Log activity and locks that will be required. You may find it more efficient to work with batches of, say 50,000 rows at a time. If there is a large amount of delete activity, there may be some Transaction Log issues that would have to be addressed.

|||

Thaks a lot, both articles are very nice....

Regards,

Angela

How I do this Query ?

Hello, Everyone

I have a table that hold Phone Calls Data, I store in this table the following information :
- customer name
- vendor name (i`m a thirdparty company)
- call date (ex: 7/1/2007 00:00:00)
- destination of call
- duration of call
- other information

--

I want to create table for destination only, i want to disply destination, every hour and the rest of table columns from the above table

i want one row for each destination every hourSorry. Not clear what you want.
Is this for a class assignment?

How i could connecting to Analysis Services from BI Intelligence Studio with server authenticati

Hi:

I have been working at client work station with BI Intelligence Studio but i want deploy the project in server machine, but my windows credential don't have sufficient privilege for do it. However i know the server credential for do it but i can't find the project settings for do that.

The account you connect to the AS server as will need permisions to deploy the database. In the Windows Start menu, you can right-click on the BI Studio item and select Run As... and this will give you the option to run the UI under a different windows use account.

This account does not necessarily need permissions to access the source relational data as you can specify in a data source to use the service account or impersonate a specific account. Additionally, many data sources will let you save a user name and password in the connection string. (For security reasons, Analysis Services will remove the password form the connection string, but will prompt you for it later as necessary and will store encrypted under the current user credentials to avoid excessive prompting.)

how i convert varchar sal field to numeric in query

how i convert varchar sal field to numeric in query

select sum(sal) from emp1

error:the sum or average aggregate operation cannot take a varchar data type as an argument.

Use Cast as in:

select sum(cast(sal as float)) from emp1

How I can use SqlDataReader?

Hi..

Every time I want to read any record from data base I read it in dataset for example:

SqlConnection con =newSqlConnection(@."Data Source=localhost ;Initial Catalog=university ;Integrated Security=True");

SqlCommand cmd =newSqlCommand("select [User_AuthorityID] from users where [UserID]='" + TextBox1.Text +"' and [UserPassword]='" + TextBox2.Text +"' ", con);

SqlDataAdapter adp =newSqlDataAdapter();

adp.SelectCommand = cmd;

DataSet ds =newDataSet();

adp.Fill(ds,"UserID");

foreach (DataRow drin ds.Tables["UserID"].Rows)

{

user_type = dr[0].ToString();

Session.Add("User_AuthorityID", user_type);

.......

Is there easier way to read data from data base?

How I can use SqlDataReader to do that?

Thanks..

it looks like your returning a single value:

SqlConnection con =newSqlConnection(@."Data Source=localhost ;Initial Catalog=university ;Integrated Security=True");

SqlCommand cmd =newSqlCommand("select [User_AuthorityID] from users where [UserID]='" + TextBox1.Text +"' and [UserPassword]='" + TextBox2.Text +"' ", con);

string value = cmd.ExecuteScalar().ToString();

or

SqlConnection con =newSqlConnection(@."Data Source=localhost ;Initial Catalog=university ;Integrated Security=True");

SqlCommand cmd =newSqlCommand("select [User_AuthorityID] from users where [UserID]='" + TextBox1.Text +"' and [UserPassword]='" + TextBox2.Text +"' ", con);

Session.Add("User_AuthorityID",cmd.ExecuteScalar().ToString(), ;

|||

Example data reader:

SqlConnection con =newSqlConnection(@."Data Source=localhost ;Initial Catalog=university ;Integrated Security=True");

SqlCommand cmd =newSqlCommand("select [User_AuthorityID] from users where [UserID]='" + TextBox1.Text +"' and [UserPassword]='" + TextBox2.Text +"' ", con);

SqlDataReader reader = cmd.ExecuteReader();

string value =string.Empty;

while (reader.Read())

{

value = reader["User_AuthorityID"].ToString();

}

Session.Add("User_AuthorityID", value);

|||

Thanks for that but what should I use if I have more than one value reture from the query?

|||

You can go ahead with the above approach suggested by David.

manal.m.k:

what should I use if I have more than one value reture from the query?

This is pretty straight forward. In the post the while loop goes through all the rows that are returned from the query in your reader. The below example can fetch the column values for each column returned in a row.

while (reader.Read()){ value1 = reader["column1"].ToString(); value2 = reader["column2"].ToString(); . . . valueN = reader["columnN"].ToString();}

How I can undo drop Table ?

Hello:
Please, I need Help. I have an Access database linked to SQL Setver 2000
tables.
I delete the tables from Access. I inmediately stop Sql Server. I need to
restore the tables. How can I proceed ?
I really will appreciate any help .
Roberto
Roberto Carabajal wrote:
> Hello:
> Please, I need Help. I have an Access database linked to SQL Setver
> 2000 tables.
> I delete the tables from Access. I inmediately stop Sql Server. I
> need to restore the tables. How can I proceed ?
> I really will appreciate any help .
> Roberto
You'll need to restore the database from a backup file and then restore
any log files. If you are using a recovery model other than Simple, you
may be able to recover the table using a tool like Log Explorer from
Lumigent (but you'll need to purchase it).
There is no undo operation for things like this.
David G.
|||Restore from a backup. There is no built-in feature to undo deletions of
objects.
David Portas
SQL Server MVP
|||Hello Roberto
The table/data may be unrecoverable if the following are true :
- Command was not run within a transaction
- Command completed before SQL Server service was stopped
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||Do you mean you deleted the link in Access? If so, relink the table.
Deleting the link in Access will not drop the SQL Server table.
If Access is the linked server in SQL and you deleted the Access table, you
may be able to restore the table if the Access DB has not been compacted
since the deletion.
Kevin
"Roberto Carabajal" <RobertoCarabajal@.discussions.microsoft.com> wrote in
message news:DDFEECBE-2FE5-43C6-B30A-FA887A99E92A@.microsoft.com...
> Hello:
> Please, I need Help. I have an Access database linked to SQL Setver 2000
> tables.
> I delete the tables from Access. I inmediately stop Sql Server. I need to
> restore the tables. How can I proceed ?
> I really will appreciate any help .
> Roberto
|||I delete the table in Access tables's view, but the table was linked to Sql
Server, so the "real" table resided in Sql Server.
Thanks very much anyway.
"Kevin Doyle" wrote:

> Do you mean you deleted the link in Access? If so, relink the table.
> Deleting the link in Access will not drop the SQL Server table.
> If Access is the linked server in SQL and you deleted the Access table, you
> may be able to restore the table if the Access DB has not been compacted
> since the deletion.
> Kevin
> "Roberto Carabajal" <RobertoCarabajal@.discussions.microsoft.com> wrote in
> message news:DDFEECBE-2FE5-43C6-B30A-FA887A99E92A@.microsoft.com...
>
>
|||I'sorry. I should clarify it is an .ADP proyect used as a tool to managing
the server.
The problem was that droping table from the project <=> drop table in the
server.
Perhaps, it could be a "undo" in the proyect, but I must admit Access never
had similar one.
"Roberto Carabajal" wrote:
[vbcol=seagreen]
> I delete the table in Access tables's view, but the table was linked to Sql
> Server, so the "real" table resided in Sql Server.
> Thanks very much anyway.
>
> "Kevin Doyle" wrote:

How I can transfer login and password between SQL7 and SQL2000 Ser

Hi Good Day to everyone
In SQL7 Server have created some users ID with the passwords. I would like
to know how I can transfer those login IDs and Passwords into the SQL2000
server without re-create.
Besides, I would like to how to change the db ownership after I transfer the
ID and password?
Please advise.
Polar Bear.
* Polar Bear is new to SQL DB
Here are some articles you may be interested in:
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.databasejournal.com/featu...le.php/3379901 Moving
system DB's
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Polar Bear" <PolarBear@.discussions.microsoft.com> wrote in message
news:D76B2259-BEA3-4239-9BBB-2E24A2395DBC@.microsoft.com...
> Hi Good Day to everyone
> In SQL7 Server have created some users ID with the passwords. I would like
> to know how I can transfer those login IDs and Passwords into the SQL2000
> server without re-create.
> Besides, I would like to how to change the db ownership after I transfer
> the
> ID and password?
> Please advise.
> Polar Bear.
> * Polar Bear is new to SQL DB
|||Check out http://www.support.microsoft.com/?id=246133 for transferring
logins.
With regards changing the database ownership, check out SQL Books On
Line for the sproc sp_changedbowner.
Regards
ALI
Polar Bear wrote:
> Hi Good Day to everyone
> In SQL7 Server have created some users ID with the passwords. I would like
> to know how I can transfer those login IDs and Passwords into the SQL2000
> server without re-create.
> Besides, I would like to how to change the db ownership after I transfer the
> ID and password?
> Please advise.
> Polar Bear.
> * Polar Bear is new to SQL DB

How I can transfer login and password between SQL7 and SQL2000 Ser

Hi Good Day to everyone
In SQL7 Server have created some users ID with the passwords. I would like
to know how I can transfer those login IDs and Passwords into the SQL2000
server without re-create.
Besides, I would like to how to change the db ownership after I transfer the
ID and password?
Please advise.
Polar Bear.
* Polar Bear is new to SQL DBHere are some articles you may be interested in:
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.databasejournal.com/feat...cle.php/3379901 Moving
system DB's
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Polar Bear" <PolarBear@.discussions.microsoft.com> wrote in message
news:D76B2259-BEA3-4239-9BBB-2E24A2395DBC@.microsoft.com...
> Hi Good Day to everyone
> In SQL7 Server have created some users ID with the passwords. I would like
> to know how I can transfer those login IDs and Passwords into the SQL2000
> server without re-create.
> Besides, I would like to how to change the db ownership after I transfer
> the
> ID and password?
> Please advise.
> Polar Bear.
> * Polar Bear is new to SQL DB|||Check out http://www.support.microsoft.com/?id=246133 for transferring
logins.
With regards changing the database ownership, check out SQL Books On
Line for the sproc sp_changedbowner.
Regards
ALI
Polar Bear wrote:
> Hi Good Day to everyone
> In SQL7 Server have created some users ID with the passwords. I would like
> to know how I can transfer those login IDs and Passwords into the SQL2000
> server without re-create.
> Besides, I would like to how to change the db ownership after I transfer t
he
> ID and password?
> Please advise.
> Polar Bear.
> * Polar Bear is new to SQL DB

How I can transfer login and password between SQL7 and SQL2000 Ser

Hi Good Day to everyone
In SQL7 Server have created some users ID with the passwords. I would like
to know how I can transfer those login IDs and Passwords into the SQL2000
server without re-create.
Besides, I would like to how to change the db ownership after I transfer the
ID and password?
Please advise.
Polar Bear.
* Polar Bear is new to SQL DBHere are some articles you may be interested in:
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.databasejournal.com/features/mssql/article.php/3379901 Moving
system DB's
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Polar Bear" <PolarBear@.discussions.microsoft.com> wrote in message
news:D76B2259-BEA3-4239-9BBB-2E24A2395DBC@.microsoft.com...
> Hi Good Day to everyone
> In SQL7 Server have created some users ID with the passwords. I would like
> to know how I can transfer those login IDs and Passwords into the SQL2000
> server without re-create.
> Besides, I would like to how to change the db ownership after I transfer
> the
> ID and password?
> Please advise.
> Polar Bear.
> * Polar Bear is new to SQL DB|||Check out http://www.support.microsoft.com/?id=246133 for transferring
logins.
With regards changing the database ownership, check out SQL Books On
Line for the sproc sp_changedbowner.
Regards
ALI
Polar Bear wrote:
> Hi Good Day to everyone
> In SQL7 Server have created some users ID with the passwords. I would like
> to know how I can transfer those login IDs and Passwords into the SQL2000
> server without re-create.
> Besides, I would like to how to change the db ownership after I transfer the
> ID and password?
> Please advise.
> Polar Bear.
> * Polar Bear is new to SQL DB

How I can to treat a largest table, db?

Hi All,
I have one doubt about largest database, largest tables etc.
For example:
I have one db that have a table with 10.000.000.000 (or more) of register,
and I need get each register and do same thing with it (think that db is
about a credit card and the registers are the transactions of customers, and
I need print the invoices)
If I try use SELECT * FROM. the dm return 10.000.000.000, this is not good.
How I get one by one of table.
How I can to treat (to manipulate) a largest table, db? What are the ways?
What are the tricks?
Can you show me?
Thanks
Hi,
Execute the system stored procedure with out parameters to get the size of
all databases
sp_databases
To get the actual usage of data and indexes then execute the below comand in
the context of a database.
sp_spaceused
For getting the size of table in order:-
select object_name(id) as Table_name,rowcnt as record_count
from sysindexes
where indid IN (0, 1)
order by rowcnt desc
TO get the correct result execute DBCC UPDATEUSAGE (see books online) before
the above select statement.
Thanks
Hari
SQL Server MVP
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:OABd$z7aFHA.3808@.TK2MSFTNGP14.phx.gbl...
> Hi All,
>
> I have one doubt about largest database, largest tables etc.
>
> For example:
>
> I have one db that have a table with 10.000.000.000 (or more) of register,
> and I need get each register and do same thing with it (think that db is
> about a credit card and the registers are the transactions of customers,
> and I need print the invoices)
>
> If I try use SELECT * FROM. the dm return 10.000.000.000, this is not
> good.
> How I get one by one of table.
>
> How I can to treat (to manipulate) a largest table, db? What are the ways?
> What are the tricks?
>
> Can you show me?
>
> Thanks
>
|||that table should have a key, and you should use the key for the record you
want
along with 'WHERE'
it works something like,
SELECT *
FROM bigtable
WHERE thekey = '123'
this will select only one record.
"Hari Prasad" wrote:

> Hi,
> Execute the system stored procedure with out parameters to get the size of
> all databases
> sp_databases
> To get the actual usage of data and indexes then execute the below comand in
> the context of a database.
> sp_spaceused
> For getting the size of table in order:-
> select object_name(id) as Table_name,rowcnt as record_count
> from sysindexes
> where indid IN (0, 1)
> order by rowcnt desc
> TO get the correct result execute DBCC UPDATEUSAGE (see books online) before
> the above select statement.
> Thanks
> Hari
> SQL Server MVP
>
> "ReTF" <re.tf@.newsgroup.nospam> wrote in message
> news:OABd$z7aFHA.3808@.TK2MSFTNGP14.phx.gbl...
>
>

How I can to treat a largest table, db?

Hi All,
I have one doubt about largest database, largest tables etc.
For example:
I have one db that have a table with 10.000.000.000 (or more) of register,
and I need get each register and do same thing with it (think that db is
about a credit card and the registers are the transactions of customers, and
I need print the invoices)
If I try use SELECT * FROM. the dm return 10.000.000.000, this is not good.
How I get one by one of table.
How I can to treat (to manipulate) a largest table, db? What are the ways?
What are the tricks?
Can you show me?
ThanksHi,
Execute the system stored procedure with out parameters to get the size of
all databases
sp_databases
To get the actual usage of data and indexes then execute the below comand in
the context of a database.
sp_spaceused
For getting the size of table in order:-
select object_name(id) as Table_name,rowcnt as record_count
from sysindexes
where indid IN (0, 1)
order by rowcnt desc
TO get the correct result execute DBCC UPDATEUSAGE (see books online) before
the above select statement.
Thanks
Hari
SQL Server MVP
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:OABd$z7aFHA.3808@.TK2MSFTNGP14.phx.gbl...
> Hi All,
>
> I have one doubt about largest database, largest tables etc.
>
> For example:
>
> I have one db that have a table with 10.000.000.000 (or more) of register,
> and I need get each register and do same thing with it (think that db is
> about a credit card and the registers are the transactions of customers,
> and I need print the invoices)
>
> If I try use SELECT * FROM. the dm return 10.000.000.000, this is not
> good.
> How I get one by one of table.
>
> How I can to treat (to manipulate) a largest table, db? What are the ways?
> What are the tricks?
>
> Can you show me?
>
> Thanks
>|||that table should have a key, and you should use the key for the record you
want
along with 'WHERE'
it works something like,
SELECT *
FROM bigtable
WHERE thekey = '123'
this will select only one record.
"Hari Prasad" wrote:

> Hi,
> Execute the system stored procedure with out parameters to get the size of
> all databases
> sp_databases
> To get the actual usage of data and indexes then execute the below comand
in
> the context of a database.
> sp_spaceused
> For getting the size of table in order:-
> select object_name(id) as Table_name,rowcnt as record_count
> from sysindexes
> where indid IN (0, 1)
> order by rowcnt desc
> TO get the correct result execute DBCC UPDATEUSAGE (see books online) befo
re
> the above select statement.
> Thanks
> Hari
> SQL Server MVP
>
> "ReTF" <re.tf@.newsgroup.nospam> wrote in message
> news:OABd$z7aFHA.3808@.TK2MSFTNGP14.phx.gbl...
>
>

How I can to treat a largest table, db?

Hi All,
I have one doubt about largest database, largest tables etc.
For example:
I have one db that have a table with 10.000.000.000 (or more) of register,
and I need get each register and do same thing with it (think that db is
about a credit card and the registers are the transactions of customers, and
I need print the invoices)
If I try use SELECT * FROM. the dm return 10.000.000.000, this is not good.
How I get one by one of table.
How I can to treat (to manipulate) a largest table, db? What are the ways?
What are the tricks?
Can you show me?
ThanksHi,
Execute the system stored procedure with out parameters to get the size of
all databases
sp_databases
To get the actual usage of data and indexes then execute the below comand in
the context of a database.
sp_spaceused
For getting the size of table in order:-
select object_name(id) as Table_name,rowcnt as record_count
from sysindexes
where indid IN (0, 1)
order by rowcnt desc
TO get the correct result execute DBCC UPDATEUSAGE (see books online) before
the above select statement.
Thanks
Hari
SQL Server MVP
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:OABd$z7aFHA.3808@.TK2MSFTNGP14.phx.gbl...
> Hi All,
>
> I have one doubt about largest database, largest tables etc.
>
> For example:
>
> I have one db that have a table with 10.000.000.000 (or more) of register,
> and I need get each register and do same thing with it (think that db is
> about a credit card and the registers are the transactions of customers,
> and I need print the invoices)
>
> If I try use SELECT * FROM. the dm return 10.000.000.000, this is not
> good.
> How I get one by one of table.
>
> How I can to treat (to manipulate) a largest table, db? What are the ways?
> What are the tricks?
>
> Can you show me?
>
> Thanks
>|||that table should have a key, and you should use the key for the record you
want
along with 'WHERE'
it works something like,
SELECT *
FROM bigtable
WHERE thekey = '123'
this will select only one record.
"Hari Prasad" wrote:
> Hi,
> Execute the system stored procedure with out parameters to get the size of
> all databases
> sp_databases
> To get the actual usage of data and indexes then execute the below comand in
> the context of a database.
> sp_spaceused
> For getting the size of table in order:-
> select object_name(id) as Table_name,rowcnt as record_count
> from sysindexes
> where indid IN (0, 1)
> order by rowcnt desc
> TO get the correct result execute DBCC UPDATEUSAGE (see books online) before
> the above select statement.
> Thanks
> Hari
> SQL Server MVP
>
> "ReTF" <re.tf@.newsgroup.nospam> wrote in message
> news:OABd$z7aFHA.3808@.TK2MSFTNGP14.phx.gbl...
> > Hi All,
> >
> >
> >
> > I have one doubt about largest database, largest tables etc.
> >
> >
> >
> > For example:
> >
> >
> >
> > I have one db that have a table with 10.000.000.000 (or more) of register,
> > and I need get each register and do same thing with it (think that db is
> > about a credit card and the registers are the transactions of customers,
> > and I need print the invoices)
> >
> >
> >
> > If I try use SELECT * FROM. the dm return 10.000.000.000, this is not
> > good.
> >
> > How I get one by one of table.
> >
> >
> >
> > How I can to treat (to manipulate) a largest table, db? What are the ways?
> > What are the tricks?
> >
> >
> >
> > Can you show me?
> >
> >
> >
> > Thanks
> >
> >
>
>

How I can start SQL Manager 2005

I have a Visual Studio 2003 and intalled Visual Studio 2005 same operation I made with SQL Server 2005 CPT but didn′t install the SQL Server manager only install SQL Configuration Manager Can you help me?You just need to re-run the install and install the database tools/workstation subset. That will include the management studio component.

how i can skip error messeges within Procedures

how i can skip error messeges within Procedures
such as can't insert to duplicate keys
thanks
samEither *handle* errors or *avoid* them, but don't *skip* them.
It sounds like you should avoid inserting duplicate keys in the first
place. Example:
INSERT INTO YourTable (c1, c2, ...)
SELECT @.c1, @.c2, ...
WHERE NOT EXISTS
(SELECT *
FROM YourTable
WHERE c1 = @.c1) ;
Error handling:
http://www.sommarskog.se/error-handling-I.html
David Portas
SQL Server MVP
--|||Your insert failed, why would you want to ignore that?
How about preventing the error in the first place?
http://www.aspfaq.com/2509
"Sam" <focus10@.zahav.net.il> wrote in message
news:e6uyl5QmFHA.3648@.TK2MSFTNGP10.phx.gbl...
> how i can skip error messeges within Procedures
> such as can't insert to duplicate keys
> thanks
> sam
>
>|||Can't be done <yet>. All messages in SQL Server get sent to the client.
You cannot stop them....<yet>
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Sam" <focus10@.zahav.net.il> wrote in message
news:e6uyl5QmFHA.3648@.TK2MSFTNGP10.phx.gbl...
> how i can skip error messeges within Procedures
> such as can't insert to duplicate keys
> thanks
> sam
>
>

how i can see prceutre text in run time

hello to all
how can i see source of prceure after i pass its input parameter
thxAnswered to another post, no need to repost same Q.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"javad.ebrahimnezhad" <sorena@.parskhazar.net> wrote in message
news:uvnlUM7WFHA.2288@.TK2MSFTNGP14.phx.gbl...
> hello to all
> how can i see source of prceure after i pass its input parameter
> thx
>

How i can see how many user using database?

Hello,

I want do some store procedure for see how many users using database in this exactly moment.
I want do this because i need to stop DB for execute backup or restore.

And how i can kill all conection for do this.

thanks. Sorry i am learning !I see existe sp_who and sp_who2 , how i can filter user de one db ? example only msdb

Thanks|||Try this select statement to view connected users.

select spid, loginame,b.name db_name, hostname, program_name,a.status,
login_time, last_batch, lastwaittype
from master..sysprocesses a
join master..sysdatabases b on a.dbid = b.dbid

To kill all connections at once I would set server in single user mode and kill that last connection and login by myself.
The thing is as soon as you kill all connections they still can connect back.|||Well i used one store procedure for filters users, databases etc...

But now i dont know how i can see how many users ( only number ) is connected in one database and put one button in one windows aplication for kill all users.

thanks|||I already resolve problem for kill users. Now i just know how i can put in windows aplication some messsage ( Have x users conected in db )

How I can scan all rows of the datasource?

How I can scan all rows of the datasource?
How I can move at the first row of the datasource?
How I can move at the next row, the last, the previous...
It's the reporting designer of Visual Basic .Net.
I would like to scan all rows to find an element or to do an operation, ...
Thanks.

Huh? I didn't get the point.
Mira, si eres de un país de habla hispana escribe tu pregunta en espa?ol; tal vez pueda ayudarte.

|||Reporting Services is not a programmatic / cursor method of accessing data. The rows of the query results are processed by evaluating the expressions in the report definition. Your operations are included in these expressions (e.g. =Fields!A.Value - Fields!B.Value).

how i can save byte array in database?

hi all
i have Byte Array like this:
dim Arr(388,8) as Byte
its working good in my application, but after i close my application all
data saved in this array is lost.
i need save all data from this array to Database (MDB or SQL), then after i
run application again i load data from Database to my Array Variable.
how i can do that?
its byte aray.
Best Regards
Tark M. Siala
Software Development
INTERNATIONAL COMPUTER CENTER (ICC.Networking)
Mobile: +218-91-3125900
E-Mail: tarksiala@.icc-libya.com
Messenger: tarksiala@.hotmail.com
Web Page: http://www.icc-libya.com
Blog: http://spaces.msn.com/tarksiala
======================================
You can save these values in a Blob field
regards
Michel Posseth [MCP]
"Tark Siala" <tarksiala@.icc-libya.com> wrote in message
news:O%23GSIoIQGHA.3872@.TK2MSFTNGP15.phx.gbl...
> hi all
> i have Byte Array like this:
> dim Arr(388,8) as Byte
> its working good in my application, but after i close my application all
> data saved in this array is lost.
> i need save all data from this array to Database (MDB or SQL), then after
> i run application again i load data from Database to my Array Variable.
> how i can do that?
> its byte aray.
> --
> Best Regards
> Tark M. Siala
> Software Development
> INTERNATIONAL COMPUTER CENTER (ICC.Networking)
> Mobile: +218-91-3125900
> E-Mail: tarksiala@.icc-libya.com
> Messenger: tarksiala@.hotmail.com
> Web Page: http://www.icc-libya.com
> Blog: http://spaces.msn.com/tarksiala
> ======================================
>
|||hi
thanks
but i know how i can working with Blob to save Pictures, but how i can
working with Blob to save (Byte Array)?
Best Regards
Tark M. Siala
Development Manager
INTERNATIONAL COMPUTER CENTER (ICC.Networking)
Mobile: +218-91-3125900
E-Mail: tarksiala@.icc-libya.com
Messenger: tarksiala@.hotmail.com
Web Page: http://www.icc-libya.com
Blog: http://spaces.msn.com/tarksiala
======================================
"m.posseth" <michelp@.nohausystems.nl> wrote in message
news:uomlDeQQGHA.2012@.TK2MSFTNGP14.phx.gbl...
> You can save these values in a Blob field
> regards
> Michel Posseth [MCP]
>
>
>
> "Tark Siala" <tarksiala@.icc-libya.com> wrote in message
> news:O%23GSIoIQGHA.3872@.TK2MSFTNGP15.phx.gbl...
>

HOW I CAN SAVE AN XML IN SQL SERVER

I HAVE AN APP THAT RECEIVE FROM THE WEB A XML FILE
I NEED TO SAVE AS A TABLE IN SQL SERVER
CAN ANYBODY HELP ME ?
THANKS
GRS
Do you want to shred it into rows/columns or store it as blob?
If you want to shred it, you may use OpenXML or Dataset. If the file size is
big, consider using Bulkload which will stream the date to SQL server.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"GRS" <g_saturansky@.yahoo.com> wrote in message
news:1121727965.861690.60780@.g47g2000cwa.googlegro ups.com...
>I HAVE AN APP THAT RECEIVE FROM THE WEB A XML FILE
> I NEED TO SAVE AS A TABLE IN SQL SERVER
> CAN ANYBODY HELP ME ?
> THANKS
> GRS
>
|||

Quote:

Originally posted by GRS
I HAVE AN APP THAT RECEIVE FROM THE WEB A XML FILE
I NEED TO SAVE AS A TABLE IN SQL SERVER
CAN ANYBODY HELP ME ?
THANKS
GRS

http://www.kbalertz.com/kb_316244.aspx
That will get you started.
...

How I can restore to the certain LSN (not Point-In-Time)?

Dear Sir.
How I can restore to the certain LSN (not Point-In-Time)?
Also do you know program for viewing of Transaction Log and BackUp?
Thank you in advance.Have you taken a look at the stopatmark or stopbeforemark option in SQL2005
BOL
9ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/f7b3de5b-198d-448d-8c71-1cdd9239676c.htm)?
Linchi
"RedFox" wrote:
> Dear Sir.
> How I can restore to the certain LSN (not Point-In-Time)?
> Also do you know program for viewing of Transaction Log and BackUp?
> Thank you in advance.|||Dear Linchi Shea,
Thank you for your answer, but I have asked about CERTAIN LSN (= Log
Sequence Number) - NOT MARKED Transaction.
P.S. link is not correct.
"Linchi Shea" wrote:
> Have you taken a look at the stopatmark or stopbeforemark option in SQL2005
> BOL
> 9ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/f7b3de5b-198d-448d-8c71-1cdd9239676c.htm)?
> Linchi
> "RedFox" wrote:
> > Dear Sir.
> >
> > How I can restore to the certain LSN (not Point-In-Time)?
> > Also do you know program for viewing of Transaction Log and BackUp?
> >
> > Thank you in advance.|||The link I posted earlier is for BOL. You can also use this link to the Books
Online at the Microsoft site.
http://msdn2.microsoft.com/en-us/library/ms191459.aspx
> but I have asked about CERTAIN LSN (= Log
> Sequence Number) - NOT MARKED Transaction.
It has an option to allow you to restore to a log sequence number.
Linchi
"RedFox" wrote:
> Dear Linchi Shea,
> Thank you for your answer, but I have asked about CERTAIN LSN (= Log
> Sequence Number) - NOT MARKED Transaction.
> P.S. link is not correct.
> "Linchi Shea" wrote:
> > Have you taken a look at the stopatmark or stopbeforemark option in SQL2005
> > BOL
> > 9ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/f7b3de5b-198d-448d-8c71-1cdd9239676c.htm)?
> >
> > Linchi
> >
> > "RedFox" wrote:
> >
> > > Dear Sir.
> > >
> > > How I can restore to the certain LSN (not Point-In-Time)?
> > > Also do you know program for viewing of Transaction Log and BackUp?
> > >
> > > Thank you in advance.

How I can register MSSQL Server 2000 by IP Address

How I can register MSSQL Server 2000 by IP Address-

I installed MSSQL Server 2000 sp3a, Enterprise edition on windows 2000 advanced server box with sp4. Its primarily give instance with name like servername/sqlindia, Where sqlindia is instance and servername is physical machine name.

I would like to registration of sqlserver instance by IP Address. How I can do it.

Thanking You

R.MallEnsure the client network utility is mentioned with ALIAS using IP Address.

How I can pick between 5 - 20 rows in table

Mostly we are using to get 100 or more record with Top operator, but I want to take specific row in between like

10 - 100 or 100 to 200 etc.

How I can pick it. plz give suggestion

See

http://www.aspfaq.com/show.asp?id=2120

|||If you are using SQL 2005, look at ROW_NUMBER function at BOL.

How I can obtain the data from files Exel, throw ODBC provider?

What will be "Query string"?
ThankHello Sergey,
> What will be "Query string"?
> Thank
>
The query string will be like a typical SQL Select statement, except instead
of a table name you specify a named range from which the data should be drawn.
If you have a grid in an Excel sheet that looks like this:
ID FirstName LastName
1 George Washington
2 John Adams
3 Thomas Jefferson
And you define those cells as named range "Presidents", then:
SELECT ID, FirstName, LastName FROM [Presidents]
Here's some more info on ODBC/Excel. It targets ASP developers, but some
of the facts here may apply to you:
http://support.microsoft.com/kb/q195951/
-Chri

How I can make trigger to Add Row 4 to my data base?

Hi

I need help about this problem

I have a data grid view like this:

Index

Code(releted to other table)

Name

Price +

Price _

1

1

BMW X5

1000000 $

2

1

BMW X3

500000 $

3

1

BMW Z3

250000 $

4

1

SUM

1750000 $

Row 4 must add by code automatically.
How? I must Use trigger or . . .?

my user may want to edit the data (some row OR price) I have a Problem.

How I can make a Code for Make SUM add row 4 and Delete this row when it loaded to edit? (Select and delete)

Or I can do some thing better than delete the row and write it again?

This doesn't seem like a SQL Server problem. If you do not want to persist the results in the backend you should do it in the client-side and add the extra row to the DataGrid from code. You should post this question in the Visual Studio or .NET Framework newsgroups.

how i can make script Database with Data?

hi
i am working on Windows 2000 Advanced Server + SP4 and SQL2000 Enterprise +
SP4.
i have database contains Tables, and i want make script to build Tables on
other Database,
but i need this script include Data in Tables too, i can make that ? and
how?
thanks
--
Tarek M. Sialahi,
Tark Siala wrote:
> hi
> i am working on Windows 2000 Advanced Server + SP4 and SQL2000
> Enterprise + SP4.
> i have database contains Tables, and i want make script to build
> Tables on other Database,
> but i need this script include Data in Tables too, i can make that ?
> and how?
> thanks
you can have a look at a free prj of mine, at the link following my sign.,
DbaMgr2k, which include an "INSERT INTO" script generation feature at table
level...
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.bizhttp://italy.mvps.org
DbaMgr2k ver 0.19.0 - DbaMgr ver 0.63.0 and further SQL Tools
-- remove DMO to reply|||Try www.sqlscripter.com to generate data scripts. It's free.
"Tark Siala" wrote:

> hi
> i am working on Windows 2000 Advanced Server + SP4 and SQL2000 Enterprise
+
> SP4.
> i have database contains Tables, and i want make script to build Tables on
> other Database,
> but i need this script include Data in Tables too, i can make that ? and
> how?
> thanks
> --
> Tarek M. Siala
>
>

how i can make script Database with Data?

hi
i am working on Windows 2000 Advanced Server + SP4 and SQL2000 Enterprise +
SP4.
i have database contains Tables, and i want make script to build Tables on
other Database,
but i need this script include Data in Tables too, i can make that ? and
how?
thanks
--
Tarek M. Sialahi,
Tark Siala wrote:
> hi
> i am working on Windows 2000 Advanced Server + SP4 and SQL2000
> Enterprise + SP4.
> i have database contains Tables, and i want make script to build
> Tables on other Database,
> but i need this script include Data in Tables too, i can make that ?
> and how?
> thanks
you can have a look at a free prj of mine, at the link following my sign.,
DbaMgr2k, which include an "INSERT INTO" script generation feature at table
level...
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.bizhttp://italy.mvps.org
DbaMgr2k ver 0.19.0 - DbaMgr ver 0.63.0 and further SQL Tools
-- remove DMO to reply