Load data from text file into some table implemented in stored procedure.

Hello, I want to load data from text file to MS SQL DB table.

In MySQL, it is the "LOAD DATA INFILE..." query statement.

What is sutable query if I want to migration from Mysql to MS SQL Server 2005 Express?

I think you need some third party tool, which will do the needed convertions. Executing query form a file you need to read the SQLCMD form BOL, this is command line utility.

|||hi remedios,

you can use the bcp(Bulk Copy Program) to basically do the same as in MySql. or if not why not use DTS in Enterprise Manager and select the text file as the Source

hth

Listing Db values

Hi, I want to make a logfile where i store all tables, collnames and values of a specified database. Which statement can I use in SQLserver or Oracle? I already found the following statements:

Oracle:
select * from all_tables
select * from user_tables

SQLserver:
select * from sysobjects where type'='U'

So getting the tablenames isn't the problem. The question is how the get the matching columns with their type and value.

Tnx.try this one
select sysobjects.name as Table_Name,syscolumns.name as Column_Name,systypes.name as Data_Type from sysobjects
join syscolumns on sysobjects.id=syscolumns.id
join systypes on syscolumns.xtype=systypes.xtype and systypes.status=typestat
where sysobjects.type='u'

Originally posted by kixer
Hi, I want to make a logfile where i store all tables, collnames and values of a specified database. Which statement can I use in SQLserver or Oracle? I already found the following statements:

Oracle:
select * from all_tables
select * from user_tables

SQLserver:
select * from sysobjects where type'='U'

So getting the tablenames isn't the problem. The question is how the get the matching columns with their type and value.

Tnx.|||Thanx! ;) Now I know the objectnames. All I have to do now is to make a nice treeview with the generated values, so i can log some sort of a dictionary.

Listbox to only appear if there are records returned from the SQL select query

I would like to make a listbox only appear if there are results returned by the SQL select statement.

I want this to be assessed on a click event of a button before the listbox is rendered.

I obviously use the ".visible" property, but how do I assess the returned records is zero before it is rendered?

hi,

there must be datasource(dataset or reader) for that listbox i believe.

if its dataset then use dataset.tables[0].Rows.Count, if its reader then use reader.hasrows.

hope it helps.

regards,

satish.

|||

Thanks Satish,

I used an if statement to assess if rows > 0

Cheers,

Ben.

|||

cheers Ben.

satish.

sql

List of Users and Databases

Hi there,
I am wanting to get a list of databases (in sql 2000) and the users
who have access to each database. Does anyone know what SQL statement
I can use to get this information? Thanks
RichardTry this:
Declare @.Databasename varchar(100)
Declare @.MySQLString nvarchar(100)
SET NOCOUNT ON
declare c_Databasename insensitive cursor for
select name from master..sysdatabases
open c_Databasename
fetch next from c_Databasename into @.Databasename
while @.@.fetch_status = 0
BEGIN
SET @.MySQLString = 'Select '''+@.Databasename+''' [Database],name [users]
from '+@.Databasename+'.dbo.sysusers where sid is not null'
exec sp_executesql @.MySQLString
fetch next from c_Databasename into @.Databasename
END
close c_Databasename
deallocate c_Databasename
"Richie.Cunningham" wrote:
> Hi there,
> I am wanting to get a list of databases (in sql 2000) and the users
> who have access to each database. Does anyone know what SQL statement
> I can use to get this information? Thanks
> Richard
>|||Richie
Try something like that
SELECT *
FROM sysmembers membs
JOIN sysusers users on membs.memberuid = users.uid
JOIN sysusers groups on membs.groupuid = groups.uid
"Richie.Cunningham" <richplimmer@.yahoo.co.nz> wrote in message
news:1184017601.784136.45900@.z28g2000prd.googlegroups.com...
> Hi there,
> I am wanting to get a list of databases (in sql 2000) and the users
> who have access to each database. Does anyone know what SQL statement
> I can use to get this information? Thanks
> Richard
>

list of tables and views in a database

Hi
Can someone tell me the best way to extract a list of table names and views
from a database in a select statement? I'm using SQL Server 2000.
Many thanks
Andrewhttp://www.aspfaq.com/search.asp?q=schema%3A&category=1
"J055" <j055@.newsgroups.nospam> wrote in message
news:udiRk7RSGHA.3944@.TK2MSFTNGP10.phx.gbl...
> Hi
> Can someone tell me the best way to extract a list of table names and
> views from a database in a select statement? I'm using SQL Server 2000.
> Many thanks
> Andrew
>|||That's a very useful link.
Thank you
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OPVwtCSSGHA.336@.TK2MSFTNGP12.phx.gbl...
> http://www.aspfaq.com/search.asp?q=schema%3A&category=1
>
>
>
> "J055" <j055@.newsgroups.nospam> wrote in message
> news:udiRk7RSGHA.3944@.TK2MSFTNGP10.phx.gbl...
>|||select * from information_schema.tables
"J055" <j055@.newsgroups.nospam> wrote in message
news:udiRk7RSGHA.3944@.TK2MSFTNGP10.phx.gbl...
> Hi
> Can someone tell me the best way to extract a list of table names and
> views from a database in a select statement? I'm using SQL Server 2000.
> Many thanks
> Andrew
>

List of past Dates

Hello,
I want to create a list op dates using the SELECT statement. The dates range
from the current date till the current date - 10 days
The resultset for today should be as follows.
20050221
20050220
20050219
20050218
20050217
20050216
20050215
20050214
20050213
20050212
20050211
How do I create this. The result must be used in an other view, so if
possible I want a view.
Thanks
BartDoes this work for you?
select getdate()
union all
select getdate()-1
union all
select getdate()-2
union all
select getdate()-3
union all
select getdate()-4
union all
select getdate()-5
union all
select getdate()-6
union all
select getdate()-7
union all
select getdate()-8
union all
select getdate()-9
Bojidar Alexandro
"Bart Steur" <solnews@.xs4all.nl> wrote in message
news:%23kUS6iAGFHA.560@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I want to create a list op dates using the SELECT statement. The dates
range
> from the current date till the current date - 10 days
> The resultset for today should be as follows.
> 20050221
> 20050220
> 20050219
> 20050218
> 20050217
> 20050216
> 20050215
> 20050214
> 20050213
> 20050212
> 20050211
>
> How do I create this. The result must be used in an other view, so if
> possible I want a view.
> Thanks
> Bart
>|||Hi,
Maybe this will help you.
Tomasz B.
use tempdb
go
create view ten_dates
As
select convert(varchar(8), getdate(), 112) d
union all
select convert(varchar(8), getdate()-1, 112)
union all
select convert(varchar(8), getdate()-2, 112)
union all
select convert(varchar(8), getdate()-3, 112)
union all
select convert(varchar(8), getdate()-4, 112)
union all
select convert(varchar(8), getdate()-5, 112)
union all
select convert(varchar(8), getdate()-6, 112)
union all
select convert(varchar(8), getdate()-7, 112)
union all
select convert(varchar(8), getdate()-8, 112)
union all
select convert(varchar(8), getdate()-9, 112)
union all
select convert(varchar(8), getdate()-10, 112)
"Bart Steur" wrote:

> Hello,
> I want to create a list op dates using the SELECT statement. The dates ran
ge
> from the current date till the current date - 10 days
> The resultset for today should be as follows.
> 20050221
> 20050220
> 20050219
> 20050218
> 20050217
> 20050216
> 20050215
> 20050214
> 20050213
> 20050212
> 20050211
>
> How do I create this. The result must be used in an other view, so if
> possible I want a view.
> Thanks
> Bart
>
>|||Bart
Look at the script written by Irzik Ben-Gan
CREATE FUNCTION fn_dates(@.from AS DATETIME, @.to AS DATETIME)
RETURNS @.Dates TABLE(dt DATETIME NOT NULL PRIMARY KEY)
AS
BEGIN
DECLARE @.rc AS INT
SET @.rc = 1
INSERT INTO @.Dates VALUES(@.from)
WHILE @.from + @.rc * 2 - 1 <= @.to
BEGIN
INSERT INTO @.Dates
SELECT dt + @.rc FROM @.Dates
SET @.rc = @.rc * 2
END
INSERT INTO @.Dates
SELECT dt + @.rc FROM @.Dates
WHERE dt + @.rc <= @.to
RETURN
END
GO
SELECT dt FROM fn_dates('20050211', '20050221')
"Bart Steur" <solnews@.xs4all.nl> wrote in message
news:%23kUS6iAGFHA.560@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I want to create a list op dates using the SELECT statement. The dates
range
> from the current date till the current date - 10 days
> The resultset for today should be as follows.
> 20050221
> 20050220
> 20050219
> 20050218
> 20050217
> 20050216
> 20050215
> 20050214
> 20050213
> 20050212
> 20050211
>
> How do I create this. The result must be used in an other view, so if
> possible I want a view.
> Thanks
> Bart
>|||SELECT dt
FROM Calendar
WHERE dt >= DATEDIFF(DAY,10,CURRENT_TIMESTAMP)
http://www.aspfaq.com/show.asp?id=2519
David Portas
SQL Server MVP
--|||Thanks Guys,
It worked.
Bart
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:F3CD7AEB-9F22-435F-A569-32CE2D005F45@.microsoft.com...
> SELECT dt
> FROM Calendar
> WHERE dt >= DATEDIFF(DAY,10,CURRENT_TIMESTAMP)
> http://www.aspfaq.com/show.asp?id=2519
> --
> David Portas
> SQL Server MVP
> --
>

List availables tables

How to list available tables in a database using an sql
statement?
The following code does not work with ms-sql:
select table_name from user_tables;SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
"joey32" <joey32@.total.net> wrote in message
news:03ee01c3471f$7fa45420$a301280a@.phx.gbl...
> How to list available tables in a database using an sql
> statement?
> The following code does not work with ms-sql:
> select table_name from user_tables;
>|||i tried with your query, but i get 'table sysobjets not
recognized' from Access.
by the same time, i found a another query that looks like
the same style:
SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=1 Or (MSysObjects.Type)=5) AND
((Left([name],4))<>"Msys"))
ORDER BY MSysObjects.Name;
but again i get an error from ms-access:
>> Records can not be read, no read permission on
MSysObjects
by the way, i'm queying via odbc with sql statements, if
that might help you
>--Original Message--
>select name from sysobjects where type='U'. This will
>give you all the names of the tables present in the
>database. Make sure you are in the database in which you
>want to run the query.
>HTH
>>--Original Message--
>>How to list available tables in a database using an sql
>>statement?
>>The following code does not work with ms-sql:
>>select table_name from user_tables;
>>
>>.
>.
>|||When were you planning on mentioning you're using Access? You said sql,
ms-sql, etc.
Try SELECT * FROM MSysObjects or SELECT * FROM MSSysObjects (forget
which)...
"joey32" <joey32@.total.net> wrote in message
news:044301c34725$1c648e60$a301280a@.phx.gbl...
> i get this following message when executing the request:
> >> Could not find '...\INFORMATION_SCHEMA.mdb"
> and that's all i have.
> >--Original Message--
> >SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
> >
> >
> >
> >"joey32" <joey32@.total.net> wrote in message
> >news:03ee01c3471f$7fa45420$a301280a@.phx.gbl...
> >> How to list available tables in a database using an sql
> >> statement?
> >>
> >> The following code does not work with ms-sql:
> >> select table_name from user_tables;
> >>
> >>
> >
> >
> >.
> >|||Well, look at the other thread in same post, information is
there, but i might be not very visible to you, sorry for
that mistake.
I runned the query and get this error:
>> no read access to 'MSysObjets' table
by the way, i am quering via odbc using sql statements on a
ms-access database, i think version is 2002 (xp).
so how to i get the MSysObjets table visible for read
access?
>--Original Message--
>When were you planning on mentioning you're using Access?
You said sql,
>ms-sql, etc.
>Try SELECT * FROM MSysObjects or SELECT * FROM
MSSysObjects (forget
>which)...
>
>
>
>"joey32" <joey32@.total.net> wrote in message
>news:044301c34725$1c648e60$a301280a@.phx.gbl...
>> i get this following message when executing the request:
>> >> Could not find '...\INFORMATION_SCHEMA.mdb"
>> and that's all i have.
>> >--Original Message--
>> >SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
>> >
>> >
>> >
>> >"joey32" <joey32@.total.net> wrote in message
>> >news:03ee01c3471f$7fa45420$a301280a@.phx.gbl...
>> >> How to list available tables in a database using an
sql
>> >> statement?
>> >>
>> >> The following code does not work with ms-sql:
>> >> select table_name from user_tables;
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>.
>|||I got this answer from another ms-access forum wich is
exactly what i was looking for.
It might help somebody.
Thanks for your help.
----
Look in the MSysObjects table (tools|options| check system
objects). You
don't have to unhide the table to run the query, but it
wouldn't hurt for
you to poke around those tables to see what info is
available. Native
access tables are type 1. Attached access tables are type
6.
Select name, type from msysobjects where type = 1 or type
= 6
Richard Bernstein
"swat42" <swat42@.bit.com> wrote in
news:ETjPa.16788$Tx.811910@.news20.bellglobal.com:
> How to list available tables in a db by their table name
with an sql
> query?
> The following piece of code don't work:
> select table_name from user_tables;
>|||Thanks a lot but it's not MS-Access forum (it's MS SQL Server one) so I
don't think this might help anyone here
"joey32" <joey32@.total.net> wrote in message
news:057d01c3473a$0fc65fc0$a301280a@.phx.gbl...
> I got this answer from another ms-access forum wich is
> exactly what i was looking for.
> It might help somebody.