Restore Database: creating

Showing posts with label creating. Show all posts

Thursday, March 29, 2012

Help with creating xml shema

can somebody, please help me with my problem. I want to create xml shema to
use it with bulkload in vb.net application. I read about it and I must have
xsd, and xml file. So, if anybody can create for me that files, I would be
very thankful. These are my example tables.
T1
ID name city
1 john NY
2 mark NY
3 eric LA
T2
ID car price color
1 mazda 10000 black
1 honda 12000 blue
2 toyota 9000 green
3 audi 4000 black
1 ford 7800 red
please, please...if someone can help me.
thanks!
What does your XML look like?
Thanks,
Irwin
Irwin Dolobowsky
Program Manager, SqlXml
http://blogs.msdn.com/irwando
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bernard" <bernard@.form.hr> wrote in message
news:clm1td$hbv$1@.ls219.htnet.hr...
> can somebody, please help me with my problem. I want to create xml shema
> to use it with bulkload in vb.net application. I read about it and I must
> have xsd, and xml file. So, if anybody can create for me that files, I
> would be very thankful. These are my example tables.
> T1
> --
> ID name city
> 1 john NY
> 2 mark NY
> 3 eric LA
> --
> T2
> --
> ID car price color
> 1 mazda 10000 black
> 1 honda 12000 blue
> 2 toyota 9000 green
> 3 audi 4000 black
> 1 ford 7800 red
> --
> please, please...if someone can help me.
> thanks!
>
|||Bernard:
It looks like you already have data in those tables, so the easy thing to might to be issue a query on them like this
select * from t1 for xml auto
Then save those results out to file. You'll have to edit out the extra line feeds and add the XML prolog to make that a valid XML file.
Once you have that, and assuming you have the .NET SDK install, you should be able to use XSD.EXE to generate usable schmea.
If you already have the XML files you want to import, you can just feed them to XSD.EXE one at a time to get a matching W3C schema.
> Irwon: What does your XML look like?
If I'm reading this right, he doesn't have any.
Thanks,
Kent Tegels
MVP - SQL Server
The SSX FAQ & Blog:
http://tinyurl.com/6r4gb
Looking for XM, the GUI for SSX? See both:
http://tinyurl.com/4dfee and http://tinyurl.com/53hts
My Blog:
http://www.tegels.org/

Help with creating SQL Statement to get data from single table...

Hi, I'm having some difficulty creating the SQL Statement for getting some data from a table:

I have the following table of data

__User___Votes___Month

__A_______14______2
__A_______12______3
__A_______17______4
__A_______11______5

__B_______19______2
__B_______12______3
__B_______15______4

I want to beable to pull out the total number of votes a user has had over a period of months.

eg Total up each users users votes for months 4 and 5

that would give:

__User____TotalVotes

___A________28
___B________15

An added complecation is that user B does not have any data for month 5

Any help or pointers would be fanstatic

Many thanks

select sum(Votes)
from table
where month in (4, 5)
group by User|||Of cource! thank you.|||rather,
select
[user], sum(Votes) as TotalVotes
from
yourTable
where
month in (4, 5)
group
by [User]

Tuesday, March 27, 2012

Help with Bkup jobs

Hi,
I have a daily job programmed to backup two sets of dbs with their
respective BAK and TRN extensions.
Job 1 is executing normally creating the backup files BAK and TRN but job 2
is only creating the BAK file and not the TRN.
Both jobs were created with the db maintenance plans.
What could be possible wrong? Or where should I start looking?
Any help is appreciated.
Ana.
Here's the application's logger description:
SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
'DB Maintenance REGV'' (0xE61E1EABD380A84EBF33FB1AA12D6AD9) - Status:
Failed - Invoked on: 2006-09-17 13:27:11 - Message: The job failed. The Job
was invoked by User sa. The last step to run was step 1 (Step 1).
Job1:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
CF77F56D-B303-4429-AD22-D1FCADD80EEB -To
SQL_Errores" -WriteHistory -VrfyBackup -BkUpOnlyIfClean -CkDBRepair -BkUpMedia
DISK -BkUpLog -UseDefDir -DelBkUps 4WEEKS -BkExt "TRN"'
Job2:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
25E1CF45-FDAA-4B0B-B209-60176367F5BF -To
SQL_Errores" -WriteHistory -VrfyBackup -BkUpOnlyIfClean -CkDBRepair -BkUpMedia
DISK -BkUpLog -UseDefDir -DelBkUps 4WEEKS -BkExt "TRN"'Ana
I have already answered this question posted by you a few days ago
Please make sure that the second database is not set to SIMPLE recovery
mode
"Ana_T" <nospam@.yahoo.com> wrote in message
news:OKSW09J3GHA.1568@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have a daily job programmed to backup two sets of dbs with their
> respective BAK and TRN extensions.
> Job 1 is executing normally creating the backup files BAK and TRN but job
> 2
> is only creating the BAK file and not the TRN.
> Both jobs were created with the db maintenance plans.
> What could be possible wrong? Or where should I start looking?
>
> Any help is appreciated.
> Ana.
>
>
> Here's the application's logger description:
>
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance
> Plan
> 'DB Maintenance REGV'' (0xE61E1EABD380A84EBF33FB1AA12D6AD9) - Status:
> Failed - Invoked on: 2006-09-17 13:27:11 - Message: The job failed. The
> Job
> was invoked by User sa. The last step to run was step 1 (Step 1).
>
>
> Job1:
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> CF77F56D-B303-4429-AD22-D1FCADD80EEB -To
> QL_Errores" -WriteHistory -VrfyBackup -BkUpOnlyIfClean -CkDBRepair -BkUpMedia
> DISK -BkUpLog -UseDefDir -DelBkUps 4WEEKS -BkExt "TRN"'
> Job2:
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> 25E1CF45-FDAA-4B0B-B209-60176367F5BF -To
> QL_Errores" -WriteHistory -VrfyBackup -BkUpOnlyIfClean -CkDBRepair -BkUpMedia
> DISK -BkUpLog -UseDefDir -DelBkUps 4WEEKS -BkExt "TRN"'
>|||Thank you Uri, I couldn't find the post.
Where is the recovery selection hidden at? I cannot find it. I used the
wizard to create the jobs.
"Uri Dimant" <urid@.iscar.co.il> escribió en el mensaje
news:eHeaaIK3GHA.1256@.TK2MSFTNGP04.phx.gbl...
> Ana
> I have already answered this question posted by you a few days ago
> Please make sure that the second database is not set to SIMPLE recovery
> mode
>
> "Ana_T" <nospam@.yahoo.com> wrote in message
> news:OKSW09J3GHA.1568@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I have a daily job programmed to backup two sets of dbs with their
>> respective BAK and TRN extensions.
>> Job 1 is executing normally creating the backup files BAK and TRN but job
>> 2
>> is only creating the BAK file and not the TRN.
>> Both jobs were created with the db maintenance plans.
>> What could be possible wrong? Or where should I start looking?
>>
>> Any help is appreciated.
>> Ana.
>>
>>
>> Here's the application's logger description:
>>
>> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance
>> Plan
>> 'DB Maintenance REGV'' (0xE61E1EABD380A84EBF33FB1AA12D6AD9) - Status:
>> Failed - Invoked on: 2006-09-17 13:27:11 - Message: The job failed. The
>> Job
>> was invoked by User sa. The last step to run was step 1 (Step 1).
>>
>>
>> Job1:
>> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
>> CF77F56D-B303-4429-AD22-D1FCADD80EEB -To
>>
>> L_Errores" -WriteHistory -VrfyBackup -BkUpOnlyIfClean -CkDBRepair -BkUpMedia
>> DISK -BkUpLog -UseDefDir -DelBkUps 4WEEKS -BkExt "TRN"'
>> Job2:
>> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
>> 25E1CF45-FDAA-4B0B-B209-60176367F5BF -To
>>
>> L_Errores" -WriteHistory -VrfyBackup -BkUpOnlyIfClean -CkDBRepair -BkUpMedia
>> DISK -BkUpLog -UseDefDir -DelBkUps 4WEEKS -BkExt "TRN"'
>|||hi
make sure that your maintenance plane is not talking transactional log
backup of master database.
to get the actual error message query msdb.sysdbmaintplan_history table
Vinu
"Ana_T" wrote:
> Hi,
> I have a daily job programmed to backup two sets of dbs with their
> respective BAK and TRN extensions.
> Job 1 is executing normally creating the backup files BAK and TRN but job 2
> is only creating the BAK file and not the TRN.
> Both jobs were created with the db maintenance plans.
> What could be possible wrong? Or where should I start looking?
>
> Any help is appreciated.
> Ana.
>
>
> Here's the application's logger description:
>
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
> 'DB Maintenance REGV'' (0xE61E1EABD380A84EBF33FB1AA12D6AD9) - Status:
> Failed - Invoked on: 2006-09-17 13:27:11 - Message: The job failed. The Job
> was invoked by User sa. The last step to run was step 1 (Step 1).
>
>
> Job1:
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> CF77F56D-B303-4429-AD22-D1FCADD80EEB -To
> SQL_Errores" -WriteHistory -VrfyBackup -BkUpOnlyIfClean -CkDBRepair -BkUpMedia
> DISK -BkUpLog -UseDefDir -DelBkUps 4WEEKS -BkExt "TRN"'
> Job2:
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> 25E1CF45-FDAA-4B0B-B209-60176367F5BF -To
> SQL_Errores" -WriteHistory -VrfyBackup -BkUpOnlyIfClean -CkDBRepair -BkUpMedia
> DISK -BkUpLog -UseDefDir -DelBkUps 4WEEKS -BkExt "TRN"'
>
>|||Ana
SELECT DATABASEPROPERTYEX('Northwind', 'Recovery')
"Ana_T" <nospam@.yahoo.com> wrote in message
news:OGS$voK3GHA.480@.TK2MSFTNGP06.phx.gbl...
> Thank you Uri, I couldn't find the post.
> Where is the recovery selection hidden at? I cannot find it. I used the
> wizard to create the jobs.
> "Uri Dimant" <urid@.iscar.co.il> escribió en el mensaje
> news:eHeaaIK3GHA.1256@.TK2MSFTNGP04.phx.gbl...
>> Ana
>> I have already answered this question posted by you a few days ago
>> Please make sure that the second database is not set to SIMPLE recovery
>> mode
>>
>> "Ana_T" <nospam@.yahoo.com> wrote in message
>> news:OKSW09J3GHA.1568@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I have a daily job programmed to backup two sets of dbs with their
>> respective BAK and TRN extensions.
>> Job 1 is executing normally creating the backup files BAK and TRN but
>> job 2
>> is only creating the BAK file and not the TRN.
>> Both jobs were created with the db maintenance plans.
>> What could be possible wrong? Or where should I start looking?
>>
>> Any help is appreciated.
>> Ana.
>>
>>
>> Here's the application's logger description:
>>
>> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance
>> Plan
>> 'DB Maintenance REGV'' (0xE61E1EABD380A84EBF33FB1AA12D6AD9) - Status:
>> Failed - Invoked on: 2006-09-17 13:27:11 - Message: The job failed. The
>> Job
>> was invoked by User sa. The last step to run was step 1 (Step 1).
>>
>>
>> Job1:
>> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
>> CF77F56D-B303-4429-AD22-D1FCADD80EEB -To
>>
>> _Errores" -WriteHistory -VrfyBackup -BkUpOnlyIfClean -CkDBRepair -BkUpMedia
>> DISK -BkUpLog -UseDefDir -DelBkUps 4WEEKS -BkExt "TRN"'
>> Job2:
>> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
>> 25E1CF45-FDAA-4B0B-B209-60176367F5BF -To
>>
>> _Errores" -WriteHistory -VrfyBackup -BkUpOnlyIfClean -CkDBRepair -BkUpMedia
>> DISK -BkUpLog -UseDefDir -DelBkUps 4WEEKS -BkExt "TRN"'
>>
>

Help with Bkup jobs

Monday, March 26, 2012

Help with BCP!

Hi,
I am creating a text file using bcp in SQL2000. When I open the text
file which was created the =A3 sign has been replaced with something
else. WHen I look at the results in a query, the pound sign is there,
it's only when bcp'd to a text file.
It is running on Windows 2003 Server.
ANy help would be appreciated.This is a char, varchar, or text code page issue. The ascii value for pPound
sterling is 163m, which is greater than 127. If you don't want any
conversation, try to use -C RAW on the bcp command line.
Linchi
"nomad" wrote:

> Hi,
> I am creating a text file using bcp in SQL2000. When I open the text
> file which was created the ￡ sign has been replaced with something
> else. WHen I look at the results in a query, the pound sign is there,
> it's only when bcp'd to a text file.
> It is running on Windows 2003 Server.
> ANy help would be appreciated.
>|||On 22 Jun, 16:47, Linchi Shea <LinchiS...@.discussions.microsoft.com>
wrote:
> This is a char, varchar, or text code page issue. The ascii value for pPo=
und
> sterling is 163m, which is greater than 127. If you don't want any
> conversation, try to use -C RAW on the bcp command line.
> Hi,
Thanks for your response. I have tried setting the '=A3' to CHAR(163)
within the view, and the BCP is set to -c but still no joy. Could it
be something to do with the character set of the Server? as I am in
the UK, but it seems not to recognise '=A3', as if it is in America
ascii set.[vbcol=seagreen]
> Linchi
> "nomad" wrote:
>
>
>|||On 26 Jun, 13:43, nomad <d.bedg...@.ntlworld.com> wrote:[vbcol=seagreen]
> On 22 Jun, 16:47, Linchi Shea <LinchiS...@.discussions.microsoft.com>
> wrote:
>
Pound[vbcol=seagreen]
> Thanks for your response. I have tried setting the '=A3' to CHAR(163)
> within the view, and the BCP is set to -c but still no joy. Could it
> be something to do with the character set of the Server? as I am in
> the UK, but it seems not to recognise '=A3', as if it is in America
> ascii set.
>
>
>
>
>
Linchi, scrap that last reply, I wasn't adding -C RAW coorectly. It
worked a dream. Thank you very much for your answer. Much
appreciated.

Help with BCP!

Hi,
I am creating a text file using bcp in SQL2000. When I open the text
file which was created the =A3 sign has been replaced with something
else. WHen I look at the results in a query, the pound sign is there,
it's only when bcp'd to a text file.
It is running on Windows 2003 Server.
ANy help would be appreciated.This is a char, varchar, or text code page issue. The ascii value for pPound
sterling is 163m, which is greater than 127. If you don't want any
conversation, try to use -C RAW on the bcp command line.
Linchi
"nomad" wrote:
> Hi,
> I am creating a text file using bcp in SQL2000. When I open the text
> file which was created the Â£ sign has been replaced with something
> else. WHen I look at the results in a query, the pound sign is there,
> it's only when bcp'd to a text file.
> It is running on Windows 2003 Server.
> ANy help would be appreciated.
>|||On 22 Jun, 16:47, Linchi Shea <LinchiS...@.discussions.microsoft.com>
wrote:
> This is a char, varchar, or text code page issue. The ascii value for pPo=und
> sterling is 163m, which is greater than 127. If you don't want any
> conversation, try to use -C RAW on the bcp command line.
> Hi,
Thanks for your response. I have tried setting the '=A3' to CHAR(163)
within the view, and the BCP is set to -c but still no joy. Could it
be something to do with the character set of the Server? as I am in
the UK, but it seems not to recognise '=A3', as if it is in America
ascii set.
> Linchi
> "nomad" wrote:
> > Hi,
> > I am creating a text file using bcp in SQL2000. When I open the text
> > file which was created the =A3 sign has been replaced with something
> > else. WHen I look at the results in a query, the pound sign is there,
> > it's only when bcp'd to a text file.
> > It is running on Windows 2003 Server.
> > ANy help would be appreciated.|||On 26 Jun, 13:43, nomad <d.bedg...@.ntlworld.com> wrote:
> On 22 Jun, 16:47, Linchi Shea <LinchiS...@.discussions.microsoft.com>
> wrote:
> > This is a char, varchar, or text code page issue. The ascii value for p=Pound
> > sterling is 163m, which is greater than 127. If you don't want any
> > conversation, try to use -C RAW on the bcp command line.
> > Hi,
> Thanks for your response. I have tried setting the '=A3' to CHAR(163)
> within the view, and the BCP is set to -c but still no joy. Could it
> be something to do with the character set of the Server? as I am in
> the UK, but it seems not to recognise '=A3', as if it is in America
> ascii set.
> > Linchi
> > "nomad" wrote:
> > > Hi,
> > > I am creating a text file using bcp in SQL2000. When I open the text
> > > file which was created the =A3 sign has been replaced with something
> > > else. WHen I look at the results in a query, the pound sign is there,
> > > it's only when bcp'd to a text file.
> > > It is running on Windows 2003 Server.
> > > ANy help would be appreciated.
Linchi, scrap that last reply, I wasn't adding -C RAW coorectly. It
worked a dream. Thank you very much for your answer. Much
appreciated.

Wednesday, March 21, 2012

help with a simple query

hi all, i was wondering if there is an alternate way of creating a simple
query that'' get the results below, without using temp tables or declaring
any cursors.
Table:
col1 col2 col3 col4 col5
----
A Bob 2/2/2007 notes pepsi
A Bob 2/2/2007 notes coke
A Bob 2/2/2007 notes sarsi
Result:
col1 col2 col3 col4 col5
----
A Bob 2/2/2007 notes pepsi,coke,sarsi
any suggestions will be appreciated
Thanks a million!No. Use a FAST_FORWARD cursor and table variable/temp table, process
the data on the client side of your code, or use a reporting product
such as Reporting Services or (ack) Crystal to do it.
-Dave
ChiWhiteSox wrote:
> hi all, i was wondering if there is an alternate way of creating a simple
> query that'' get the results below, without using temp tables or declaring
> any cursors.
> Table:
> col1 col2 col3 col4 col5
> ----
> A Bob 2/2/2007 notes pepsi
> A Bob 2/2/2007 notes coke
> A Bob 2/2/2007 notes sarsi
> Result:
> col1 col2 col3 col4 col5
> ----
> A Bob 2/2/2007 notes pepsi,coke,sarsi
> any suggestions will be appreciated
> Thanks a million!|||See: http://www.aspfaq.com/show.asp?id=2529
Razvansql

Monday, March 19, 2012

help with a query

I need some help creating a QUERY..
here's an example of what I need to do: say for instance my database
contains 3 values:
4012345
3012345
4055555
I want to create a query that will count the number of records where
the last 5 are unique. In the example that count would equal 2. I won't
know what the last 5 digits are.
How would I go about this? My first though would be to use DISTINCT,
but I don't know how to format the SELECT DISINCT part of the query..
is there some kind of RIGHT(field,5) method I can use?
any help would be appreciated.SELECT COUNT(DISTINCT RIGHT(colname,5))
FROM TableName
On a big table, however, this will perform horribly, because every single
row will need to be passed through the string function.
If the right-most five digits of the number mean something special, then
perhaps you should consider storing those digits in a separate column.
<kevin@.questionmark.com> wrote in message
news:1122924156.826395.283970@.g49g2000cwa.googlegroups.com...
>I need some help creating a QUERY..
> here's an example of what I need to do: say for instance my database
> contains 3 values:
> 4012345
> 3012345
> 4055555
> I want to create a query that will count the number of records where
> the last 5 are unique. In the example that count would equal 2. I won't
> know what the last 5 digits are.
> How would I go about this? My first though would be to use DISTINCT,
> but I don't know how to format the SELECT DISINCT part of the query..
> is there some kind of RIGHT(field,5) method I can use?
> any help would be appreciated.
>|||>> is there some kind of RIGHT(field,5) method I can use?
Yes.
Anith|||Please post DDL along with the sample data and expected results, so we can
better answer your question [http://www.aspfaq.com/etiquette.asp?id=5006]
How about this (completed untested, due to lack of DDL):
SELECT COUNT(*) AS Count of FROM
(SELECT DISTINCT(SUBSTRING(MadeUpColumn, 4, 4) FROM MadeUpTable) FakeTable
Do you find yourself needing to break out the right 4 digits on a regular
basis? If so, you might want to consider creating separate columns for the
leading digits and trailing digits - that way, you would not need to use
parsing functions and you could place appropriate indices on the columns to
be searched/summarized.
IHTH
Jeremy
<kevin@.questionmark.com> wrote in message
news:1122924156.826395.283970@.g49g2000cwa.googlegroups.com...
> I need some help creating a QUERY..
> here's an example of what I need to do: say for instance my database
> contains 3 values:
> 4012345
> 3012345
> 4055555
> I want to create a query that will count the number of records where
> the last 5 are unique. In the example that count would equal 2. I won't
> know what the last 5 digits are.
> How would I go about this? My first though would be to use DISTINCT,
> but I don't know how to format the SELECT DISINCT part of the query..
> is there some kind of RIGHT(field,5) method I can use?
> any help would be appreciated.
>|||Sorry, for some reason I mistook your requirement as a need to split up a
phone number, so I used the right four characters instead of the right 5
characters - this should be better:
SELECT COUNT(DISTINCT(SUBSTRING(MadeUpColumn, 3, 5)) FROM MadeUpTable
"Jeremy Williams" <jeremydwill@.netscape.net> wrote in message
news:eX8sSItlFHA.2860@.TK2MSFTNGP15.phx.gbl...
> Please post DDL along with the sample data and expected results, so we can
> better answer your question [http://www.aspfaq.com/etiquette.asp?id=5006]
> How about this (completed untested, due to lack of DDL):
> SELECT COUNT(*) AS Count of FROM
> (SELECT DISTINCT(SUBSTRING(MadeUpColumn, 4, 4) FROM MadeUpTable) FakeTable
> Do you find yourself needing to break out the right 4 digits on a regular
> basis? If so, you might want to consider creating separate columns for the
> leading digits and trailing digits - that way, you would not need to use
> parsing functions and you could place appropriate indices on the columns
to
> be searched/summarized.
> IHTH
> Jeremy
> <kevin@.questionmark.com> wrote in message
> news:1122924156.826395.283970@.g49g2000cwa.googlegroups.com...
>|||> SELECT COUNT(DISTINCT(SUBSTRING(MadeUpColumn, 3, 5)) FROM MadeUpTable
Well, this assumes the value will always be exactly 8 digits... more sample
data would be useful, but the OP said right 5 characters...|||So what are you saying, I should have used RIGHT instead of SUBSTRING, just
because the OP said they wanted the right 5 characters? :-)
Just another example of "open mouth, insert foot". Thanks for the
correction, Aaron!
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23pPg4btlFHA.3336@.tk2msftngp13.phx.gbl...
> Well, this assumes the value will always be exactly 8 digits... more
sample
> data would be useful, but the OP said right 5 characters...
>|||Well, I don't necessarily think your solution is bad (or constitutes
foot-in-mouth syndrome); I prefer substring myself. Just trying to pre-empt
the inevitable, "Hey, Jeremy, this only works on columns where the value has
exactly 8 digits! Thanks a LOT!" ;-)
"Jeremy Williams" <jeremydwill@.netscape.net> wrote in message
news:urH3w3tlFHA.3316@.TK2MSFTNGP14.phx.gbl...
> So what are you saying, I should have used RIGHT instead of SUBSTRING,
> just
> because the OP said they wanted the right 5 characters? :-)
> Just another example of "open mouth, insert foot". Thanks for the
> correction, Aaron!
>|||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. It is very hard to debug code when you do not let us
see it.
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. No wonder that you are mimicing
a COBOL program and file.
If these "last five digits" (are they CHAR(7) or INTEGER or what?)
have a meaning apart from the **column**, then they need to be modeled
as another data element.

Monday, March 12, 2012

Help w/backup jobs

Friday, March 9, 2012

Help w/backup jobs

Help Using VBScript in an ActiveX task in DTS Package

Hi,

Thanks for reading.

I am creating a DTS package to import a .txt file into sql. I have everything in place, but the text file needs to have the last record deleted before the import. I need help with this part

I would like to delete the last record from a fixed width text file before I import it into sql. The number of rows will vary from file to file.

Can any one offer suggestions on the best way to do this.

I understand that I have to use the FSO to open and read the file, but I am not sure the best way to proceed after that.

Thanks in advance,
SteveThere are a couple easy ways to do this that I can think of:

1) Open the file up before import and delete the last record, then import to SQL Server.
2) Import to a temporary table that has a IDENTITY field in it, then delete the row with the highest value, then import to normal table.
3) If you want to delete the last line because it's an abnormal line (not a suitable record to go into the db), then just allow a certain number of errors. This way it'll basically error out without inserting the line.

The 1st solution needs the VBScript you're looking for. The problem I think with that is that the TextStream Object that you're looking for is a forward only object. This means that you'd have to open it, read each line at a time keeping track of which line you were on with some sort of local variable, then identify when you've reached the end of the file. Then you'd have to close the file, open it again, then read the file till you got to the last line (which you'd now know was the last line because of your local variable(s) that you initialized last time. Then you could delete that line. Here's the link for documentationhttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/jsobjtextstream.asp

The 2nd solution doesn't involve any VBScript, and would probably be simpler to explain and troubleshoot.

The 3rd solution was just a possible guess at what you're trying to do.

David

Wednesday, March 7, 2012

HELP to write stored procedure whose values are calculated automatically in database

Hi frdz,

I m creating my web-application in asp.net with C# 2005 and using sql server 2005.

I have created the stored procedure for the insert,update.

I want to know how to write the mathematical calculations in the stored procedure..

Pls tell me from the below stored procedure were i m making the mistake ??

As the discount and the total amount are not calculated by itself...and stored in the database

How to convert the

@.discpercentnumeric(5,2) to

@.discpercent ="NoDiscount" should be displayed when no discount is being given to the customers...

ALTER PROCEDURE CalculationStoredProcedure @.accountidint output, @.accountnamevarchar(20), @.opbalnumeric(10, 2), @.opbalcodechar(2), @.totalnumeric(10, 2), @.clbalnumeric(10, 2), @.clbalcodechar(2), @.discpercentnumeric(5,2), @.discamtnumeric(10, 2)asbeginset nocount on if @.opbalISNULL OR @.opbal = 0beginselect @.opbal=0select @.opbalcode=' 'select @.clbal= 0select @.total= 0select @.clbalcode=' ' @.discpercent ="NoDiscount" @.discamt=0end select @.accountid =isnull(max(accountid),0) + 1from accountmasterselect @.total=@.opbal - @.clbalfrom accountmasterselect @.discamt=@.total* @.discpercent/100from accountmasterbegin insert into accountmaster(accountname,opbal,opbalcode,clbal,clbalcode )values ( @.accountname,@.opbal,@.opbalcode,@.clbal,@.clbalcode )end set nocount offend

Thanxs in adv...

Hello my friend,

It appears you are using 1 field for 2 purposes; numeric calculation and text display. I would avoid doing this.

Declare another parameter (e.g. @.DiscDisplay) to hold a copy of the @.discpercent variable when a discount is being applied, and have it set to 'NoDiscount' if @.discpercent is 0. Then just display @.DiscDisplay on the web page in both situations.

Kind regards

Scotty

|||

thanxs for the reply...

can u pls help to calculate this for my above stored procedure its not working..

select @.total=@.opbal - @.clbalfrom accountmaster
select @.discamt=@.total* @.discpercent/100from accountmaster

|||

Looks like there is some confusion here. You are calculating a percentage from values in parameters. Then why do you have a "..FROM AccountMaster" in your SELECT?

select @.discamt=(@.opbal - @.clbal )* @.discpercent/100

should do.

|||

select @.total=(@.opbal - @.clbal )
not working in database it shows me NULL only

I tried with this also but don't work pls help me...

if (@.total IS NULL or @.total = 0)
begin
select @.total=(@.opbal - @.clbal )
end

thanxs for ur replies....

|||

Could it be because either @.opbal or @.clbal is null?

I would use the following: -

IF (ISNULL(@.Total, 0) = 0)
BEGIN
SET @.total = (ISNULL(@.opbal, 0) - ISNULL(@.clbal, 0))
END

SELECT @.Total

Kind regards

Scotty

|||

thanxs very much again...

Tried with ur suggestion...but don't work...why it does not returns/set/stores the value into the database...??

What can be the problem ?? Not understanding...i think everything is correct now...putting all of ur suggestions..together but not able to get the solution

|||

Hello again Sheenaa,

You say that you have put it all together based on our help. Can you show us all of the procedure that you have put together and we can see if you have made any mistakes.

Kind regards

Scotty

Sunday, February 26, 2012

help Stored proc

Hi,
Could any one please help me in creating sp.
It should accept one input parameter. when you pass value 1 to this parameter it should show all odd numbers from 1 to 100 and when you pass value 2 it should show all even numbers from 1 to 100.
Thanks in advance.
-ssTry this:

create procedure p_odd_even(@.i int)
as
select number -1 + @.i from master..spt_values where type = 'P' and number % 2 = 1 and number <= 100|||Very clever trick - what is the purpose of this table in SQL|||too clever

you would need DISTINCT in there, vaxman, and also restrict number to between 0 and 100, not just less than or equal to 100

the use of master..spt_values is a hack

better to declare an integers table, because it will come in handy in so many other queries|||Not sure what spt_values is for. I have seen it used for things like this. In examining the table, it looks like type 'P' does return distinct integers, but as Rudy says, much better to have your own table. I create one like this:

select top 8000 id = identity(int,1,1) into Numbers from sysobjects s1, sysobjects s2, sysobjects s3

(8000 because I usually use it for parsing varchar strings but change for your needs)

Or you can just generate your 50 numbers on the fly. For 50 numbers this is probably more efficient (no I/O and 50 numbers is small enough not to generate a work table) but for larger counts a real table is better because statistics will be kept for it and indexes used.

create procedure p_odd_even(@.i int)
as

select (a0.id + a1.id) + @.i id
FROM
(select 0 id union select 2 union select 4 union select 6 union select 8 ) a0,
(select 0 id union select 10 union select 20 union select 30 union select 40
union select 50 union select 60 union select 70 union select 80 union select 90) a1
order by 1|||You could always use:CREATE PROCEDURE pSSkris
@.arg INT = 1
AS

SELECT n
FROM (SELECT 1 + 10 * tens + ones AS n
FROM (SELECT 0 AS ones UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS a
CROSS JOIN (SELECT 0 AS tens UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS b) AS c
WHERE n % 2 = 2 - @.arg
ORDER BY n

RETURN
GO-PatP|||uh Pat, didn't I say that? (but you only need 50 numbers...)|||uh Pat, didn't I say that? (but you only need 50 numbers...)Sort of... You relied on another table outside of the problem definition, but my suggestion was self contained. I suspect that mine will be easier to explain too, but that's a relative kind of thing.

-PatP|||I think you missed this part:

create procedure p_odd_even(@.i int)
as
select (a0.id + a1.id) + @.i id
FROM
(select 0 id union select 2 union select 4 union select 6 union select 8 ) a0,
(select 0 id union select 10 union select 20 union select 30 union select 40
union select 50 union select 60 union select 70 union select 80 union select 90) a1
order by 1|||Sorry, I was only looking at your first posting in my previous comment.

-PatP|||As an interesting wrinkle, you could also use:SELECT n
FROM (SELECT d0.b + d1.b + d2.b + d3.b + d4.b + d5.b + d6.b AS n
FROM (SELECT 0 AS b UNION SELECT 1) AS d0
CROSS JOIN (SELECT 0 AS b UNION SELECT 2) AS d1
CROSS JOIN (SELECT 0 AS b UNION SELECT 4) AS d2
CROSS JOIN (SELECT 0 AS b UNION SELECT 8) AS d3
CROSS JOIN (SELECT 0 AS b UNION SELECT 16) AS d4
CROSS JOIN (SELECT 0 AS b UNION SELECT 32) AS d5
CROSS JOIN (SELECT 0 AS b UNION SELECT 64) AS d6
) AS z
WHERE n BETWEEN 1 AND 100
ORDER BY n-PatP|||As an interesting wrinkle, you could also use:CREATE PROCEDURE pSSkris2
@.arg INT = 1
AS

SELECT n
FROM (SELECT d0.b + d1.b + d2.b + d3.b + d4.b + d5.b + d6.b AS n
FROM (SELECT 2 - @.arg AS b) AS d0
CROSS JOIN (SELECT 0 AS b UNION SELECT 2) AS d1
CROSS JOIN (SELECT 0 AS b UNION SELECT 4) AS d2
CROSS JOIN (SELECT 0 AS b UNION SELECT 8) AS d3
CROSS JOIN (SELECT 0 AS b UNION SELECT 16) AS d4
CROSS JOIN (SELECT 0 AS b UNION SELECT 32) AS d5
CROSS JOIN (SELECT 0 AS b UNION SELECT 64) AS d6
) AS z
WHERE n BETWEEN 1 AND 100
ORDER BY n

RETURN-PatP|||I wonder if you'll get an "A"...|||Who wants to explain it? Without an explanation, I'd expect that the code is worthless.

-PatP|||if it's for a school assignment, i would stringly suggest to sskris to submit the solution in post #2

that'll get an A+|||I would expect that any of the solutions that we've offered will get a trip to the Dean's office and an opportunity to use this experience in their upcomming ethics class (whether it was part of the curriculum before now or not). These solutions may help them think about the answer they want to give, but I can't imagine any of them being "safe" to turn in as they are.

-PatP|||Thanks to all of you..
I really appreciate that.
Kris

Help Starting MSDE and creating a db + a log in problem! HELP!

Hello,
I downloaded MSDE from microsoft yesterday but I have been having alot of
problems setting it up. I started the install process with the following code
from te MSDN site:
Setup.exe /qb+ INSTANCENAME=VSDOTNET DISABLENETWORKPROTOCOLS=1 SAPWD=<my
password>
The instalation was to my local machine.
I have managed in visual studio to create a database with tables feild etc
but I keep on having a logon error
Can someone shed any light on the issue?
I assume my u/name is SA or my machine logon?
Did you try
OSQL -S<Servername\InstanceName> -U<Username> -P<password>
--or for integrated authentication
OSQL -S<Servername\InstanceName> -E
HTH, Jens Suessmeyer.

Friday, February 24, 2012

Help scripting backup devices

Hi guys

I am relatively new to SQL Server admin and I have been handed a task of creating backup devices for a particular SQL Server which has 204 databases.

Rather than go through and create Full, Incramental and Transaction Log backup devices for each database is ther anyway of doing the following

Create a script to go through and for each database name in 'sysdatabases'

create a folder e.g \sqldata\backup\%databasename%

and within each folder create a backup device called

%databasename%_full
%databasename%_inc
%databasename%_log

Also all these databases are running in 'Simple' recovery mode so obviously I need to change this to 'Full' to enable incramental and log backups - is this possible using the same script.

Hope someone can help as the thought of doing all of this individually for each database scares me silly!!! :)

Thanks in advance for any help

Hanleynot sure if this is an option but could you create a database maintenance plan inwhich the option 'All databases' is set and does the backup?|||I could do that but I need to create the backup devices first, that is my problem, I need to automate the creation of 3 backup devices for each database (240 of them)

:)|||This is 100% untested, but it should give you an idea or two:DECLARE @.cDb sysname

DECLARE zDb CURSOR FOR SELECT
sd.name
FROM master.dbo.sysdatabases AS sd

OPEN zDb
FETCH zDb INTO @.cDb

WHILE 0 = @.@.fetch_status
BEGIN
EXECUTE ('EXECUTE master.dbo.xp_cmdshell ''mkdir z:\sqldata\backup\'
+ @.cDb + '''')

EXECUTE ('EXECUTE sp_adddumpdevice ''disk'', '''
+ @.cDb + '_full'', ''z:\sqldata\backup\' + @.cDb + '_full.dmp''')
EXECUTE ('EXECUTE sp_adddumpdevice ''disk'', '''
+ @.cDb + '_inc'', ''z:\sqldata\backup\' + @.cDb + '_inc.dmp''')
EXECUTE ('EXECUTE sp_adddumpdevice ''disk'', '''
+ @.cDb + '_log'', ''z:\sqldata\backup\' + @.cDb + '_log.dmp''')

EXECUTE ('ALTER DATABASE ' + @.cDb + ' SET RECOVERY FULL')

FETCH zDb INTO @.cDb
END

CLOSE zDb
DEALLOCATE zDb-PatP|||Pat P

Thanks very much for that

I'll give it a try on Monday

Much appreciated

:)|||Pat P

Script was successful, exactly what I was looking for.

Many thanks

Hanley

:) :)

Sunday, February 19, 2012

Help required!

Hi all

I am creating a web services application in Visual Studio Dot net
which links to a MS SQL database.

I need to create a form which lets the user pick his field of
choice(doesnt matter what it is..) using drop down boxes,radio buttons
etc and query the database according to what he has picked and
retrieve only those columns and rows.

The thing is, I do not want to hardcode the queries using
OleDbDataAdapter or SqlDataAdapter, but want the query to be generated
on the fly, once the user picks his fields from the form.

I'd greatly appreciate any help.

Thanks,
mSeems you are looking to provide ad-hoc reporting capabilities. To avoid
hard-coding queries you would have to access the SQL Server meta-data to
retrieve information about tables and to provide options for users to pick
tables and columns. You can start by looking at the information schema views
in the SQL Server documentation. Here is one example of a query that returns
tables and columns:

SELECT c.table_catalog AS 'Database Name',
c.table_schema AS 'Owner',
c.table_name AS 'Table Name',
c.column_name AS 'Column Name',
c.data_type AS 'Column Data Type'
FROM INFORMATION_SCHEMA.COLUMNS c
INNER JOIN INFORMATION_SCHEMA.TABLES t
ON t.table_catalog = c.table_catalog
AND t.table_schema = c.table_schema
AND t.table_name = c.table_name
WHERE t.table_type = 'BASE TABLE'

Since the table and column names may not mean much to users, you may want to
build a mapping table with descriptive names. That involves some hard-coding
and maintenance (best stored in a table that you can join easily) to update
when a new table/column is added or changed, but greatly improves the user
experience.

Next you would allow selection of columns and placing filters (the WHERE
clause of the query). A good idea is to enforce a requirement to have at
least one condition. Users tend to forget about adding filters and could
possibly dump the whole table out. At this point it is very important to
check the column type and perform verification on any parameters entered.

Last is to build a dynamic query and return the results to the user. A good
reading for dynamic queries is Erland Sommarskog's article here:
http://www.sommarskog.se/dynamic_sql.html. Pay attention to the SQL
injection section as this is very common for ad-hoc query systems.

This is a very simplified approach to what you are looking for. It can get a
lot more complex if you need to provide special operations like summary
queries, or if you decide to provide capabilities to join multiple tables.

HTH,

Plamen Ratchev
http://www.SQLStudio.com|||On 27 Feb 2007 14:40:23 -0800, madhumita.iyengar@.gmail.com wrote:

Quote:

Originally Posted by

>Hi all
>
>I am creating a web services application in Visual Studio Dot net
>which links to a MS SQL database.
>
>I need to create a form which lets the user pick his field of
>choice(doesnt matter what it is..) using drop down boxes,radio buttons
>etc and query the database according to what he has picked and
>retrieve only those columns and rows.
>
>The thing is, I do not want to hardcode the queries using
>OleDbDataAdapter or SqlDataAdapter, but want the query to be generated
>on the fly, once the user picks his fields from the form.
>
>I'd greatly appreciate any help.
>
>Thanks,
>m

In addition to Plamen's suggestions, you might look into the report
builder part of Sql Server Reporting Services. The programmer sets up
the appropriate data model, and the user configures the report. There
is something of a learning curve for the user but designing a report
can be fairly trivial.|||On 27 Feb 2007 14:40:23 -0800, madhumita.iyengar@.gmail.com wrote:

Quote:

Originally Posted by

And you'll likely get more replies if you're specific in your post,
rather than just Help Required!.

Thursday, March 29, 2012

Help with creating xml shema

Help with creating SQL Statement to get data from single table...

Tuesday, March 27, 2012

Help with Bkup jobs

Help with Bkup jobs

Help with Bkup jobs

Monday, March 26, 2012

Help with BCP!

Help with BCP!

Wednesday, March 21, 2012

help with a simple query

Monday, March 19, 2012

help with a query

Monday, March 12, 2012

Help w/backup jobs

Friday, March 9, 2012

Help w/backup jobs

Help w/backup jobs

Help Using VBScript in an ActiveX task in DTS Package

Wednesday, March 7, 2012

HELP to write stored procedure whose values are calculated automatically in database

Sunday, February 26, 2012

help Stored proc

Help Starting MSDE and creating a db + a log in problem! HELP!

Friday, February 24, 2012

Help scripting backup devices

Sunday, February 19, 2012

Help required!

Restore Database

Blog Archive

About Me