i'm not able to generate a sql script with the data in the tables! using sql express.
i need this script to ganerate the same database with its datas in a new sql server.
thanks in advance
Hi All
I have a table of students, and a table of lecturers, and I wish to use one select statement to return a list of users.
What I really need to do is something like this:
SELECT Students.UserID AS UserID, Lecturers.UserID AS UserID, Students.FullName AS FullName, Lecturers.FullName AS FullName FROM Students, Lecturers
which would (in my dream world!) produce something like:
UserID FullName
s0002131 Darren Student
s0054242 Richard Student
e13412 Michael Lecturer
x92342 Linda Lecturer
Does anyone know how I should do this? Should I be doing it in an stored proceedure and returning the record set from that? Currently I'm only using ASP and one line SQL statements, and would like to continue doing so.
Hello,
This request is for sql7 on nt4.
I am searching for a sp that can generate the entire objects on all my databass in the server. Something like the 'Generate sql scripts' for sql7 but i need it to be capable to run as a scheduled job and automaticly enters in all my databases in the server.
I need to take sql scripts for all my development databases every night. I have something like 80 databases in development on the server.
Thanks
I am trying to load the data from one data base to another using the Task->Generate Script->tables (data only). So I saved the script of the data and when tried to run the script to the destination DB, getting the below error when ran the script.
Error: Visual Studio has encountered an exception. This may be caused by an extension.You can get more information by running the application together with the /log parameter on the command line, and then examining the file'C:UsersKK67AppDataRoamingMicrosoftAppEnv10.0ActivityLog.xm'. When tried opening the folder using the above path... don't see anything for AppData folder from that path.
Collin writes "i have tried to do a query between 2 different tables that have different columns. within both these tables there is a column that has a column name different from the column name of the other table but the data within both columns is the same. i wanted a query that compares the data between the 2 tables.
Example is:
Table 1
Columns : firstname , age , id , grade.
Table 2
Columns : name, sex , school.
lets say that the data of column "firstname" of table 1 contains the same data as column "name" of table 2 and i would want to do a query that retrieves records for where data in the column "firstname" in table 1 is found in column "name" of table 2.
sql server 2000 with service pack 3 running on windows 2003 server service pack 1
Rgds,
Mr. C. "
I have this pivot table (I only post the static version as the problem only regards the single quotes)
SELECT * from(
select DATEPART(year,DeliverydatePackingSlip) as Year,
CASE WHEN DiffPromiseDateFirst < 0 Then '1 - too early'
WHEN DiffPromiseDateFirst = 0 Then '2 - on time'
ELSE '3 - too late' END as Delivery
from iq4bisprocess.FactOTDCustomer
WHERE OTD_Exclusion = 0)a
PIVOT ( COUNT(Year)
For Year
in ([2012],[2013],[2014],[2015])) as pvtNow, packing everything in a string parameter I always stumble over the single quotes. I tried to replace them with CHAR(39), I tried to define a parameter for each occurrence, but always get a syntax error. What am I doing wrong?declare @sql nvarchar(max)
declare @title1 nvarchar(20)
declare @title2 nvarchar(20)
declare @title3 nvarchar(20)
set @title1 = '1 - too early'
set @title2 = '2 - on time'
set @title3 = '3 - too late'
[Code] .....
exec sp_executesql @sqlThis would throw:Msg 102, Level 15, State 1, Line 3 Incorrect syntax near 'early'.
I have these 3 tables in a database for printed chamber music works:
Table INSTRUMENTS: List of musical instruments
Column ID -- int, primary key
Column sInstrument -- string
Table PTRS_INSTRUMENTATION:
Column ID -- int, primary key
Column lInstrument -- int, points to a row in table INSTRUMENTS via ID
Column lMainItem -- int, points to a row in table MAINITEMS via ID
Column lInstrSeqNum -- int, sequential number of like instruments within one Main Item
Table MAINITEMS: List of chamber music works
Column ID -- int, primary key
Column sItem -- string
Each chamber music work (Main Item) requires an ensemble of musicians playing several instruments. Some of them may be the same, such as 2 violins. A classical quartet, for instance, consist of 2 violins, 1 viola and 1 cello. I am concerned here only with one of the instruments (although it may occur several times).
I want to extract a list of Main Items where the instrument is given (e.g., 'violin'),
And the maximum of PTRS_INSTRUMENTATION.lInstrSeqNum is a given number (e.g., 2).
I wanna to have a script that will read a sertain table in a database. If a csrtain record that is being added exists in the table already I want it not to be added into teh specific tables. If it does not exist, i want it to be added.
Here is the logic that I wanna have:
READ OPCSHTO
GET CUST_CODE + LOC_CODE
IF EXIST CUST_CODE + LOC_CODE RECORD IN DPTORGANIZATIONSLOCATIONS
GO TO READ OPCSHTO
ELSE
DO ADD TO DPT.ORGANIZATIONLOCATIONS TABLE
DO ADD TO DPTORGANIZATIONS
Hi, I am hoping someone may have tried this before.......
Our application takes user details from Active directory, and stores the Guid in the database against an autonumber field for an easy to use userid. Any time the application wants to know anything about the user, it gets the information from Active Directory based upon the stored Guid.
I am writing a query to be used in generating reports, so I don't want to use .NET, Only SQL. I would like to be able to extract the username from Active Directory using SQL, so that the user's name, and not just their ID can be used in the report.
So far I have been able to extract all of my users names and their Guids from Active Directory using SQL, and I can extract the user Guid from our database. The problem I am having is comparing the 2! Visually they look the same, however the datatypes are different. If I convert the ActiveDirectory Guid to varchar I get gobbledegook, and if I convert the stored database Guid to varbinary then it's value is changed.
The query as it stands is below:
SELECT convert(varchar(50), [Name]) as FullName,objectGUID,ADSPath
FROM openquery(ADSI, 'SELECT name, objectGuid, ADSPath
FROM ''<LDAP Path>'' WHERE objectClass = ''User''')
WHERE objectGuid in(Select ADObjectGUID FROM users WHERE UserId='1')
I am working with SQL Server 2000 - as many of our clients are still using this system, so solutions based on SQL Server 2005 would not be practical. (I beleive there are ways of running .NET code from SQL 2005 which would solve this problem)
Any ideas anyone has would be much appreciated
Thanks
Gillian
Im try to use SQL server 2000 and use Enterprise to cript a table
But my result is only table structure in the script (myfile.sql)
(something like "create table Tb1, column..") and not have any data in my table included.
how good it is if it can write some insert statement (depend on number of records existed).
Could I have any way to do it with MS SQLServer 2000?
Thank you much.
DongMT
thanks
=============================
http://www.sqlserverstudy.com
HI Experts,
I am having 2 severs (SQL2000 & SQL2005), there is already one DTS package in SQL 2000, now i have migrated DTS package to SSIS package. Now the problem is DTS package is having ActiveX script and it was executing perfectly in 2000 server but after migrated to 2005 server if i am executing the coverted SSIS package it is giving one error and the error is displaying here.
Error 1 Validation error. DTSTask_DTSDataPumpTask_1: OLE DB Destination [181]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Microsoft OLE DB Provider for SQL Server Copy" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. SearsCDCC_Transfer (1).dtsx 0 0
and i am also displaying the ACtiveX code as well and here oma11pngrdb02 is 2000 server and sant01pngrdb is 2005 server.
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Dim gsErrorFile
gsErrorFile = "\oma11pngrdb02App_Data_ReportingSearserrors2.txt"
Function Main()
Main = DTSTaskExecResult_Success
set objftp = CreateObject("ftpx.aspftp")
objftp.sServerName = "10.1.3.175"
objftp.sUserID = "COS.FTP"
objftp.sPassword = "SENDFILE"
if objftp.bConnect then
dim fname, lfname
fname = "$IOMH04.PRDFFTAP.ST462330"
lfname = "\oma11pngrdb02App_Data_ReportingSearscdccfile.txt"
If objFTP.bGetFile (fname, lfname ) then
Main = DTSTaskExecResult_Success
Else
logError("Error getting file: " & objFTP.sErrorDesc)
Main = DTSTaskExecResult_Failure
End If
Else
logError("Error connecting: " & objFTP.sErrorDesc)
Main = DTSTaskExecResult_Failure
end if
End Function
Function logError(sErrorMsg)
Dim objErrorFile, objFSO
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objErrorFile = objFSO.OpenTextFile(gsErrorFile, 2,True)
objErrorFile.writeLine "Error!! Error!! Error!! Error!! Error!! Error!! Error!! Error!! Error!! Error!! "
objErrorFile.writeLine FormatDateTime(Now(),vbShortdate) & " " & FormatDateTime(Now(),vbShorttime)
objErrorFile.writeLine sErrorMsg
objErrorFile.writeLine "Error!! Error!! Error!! Error!! Error!! Error!! Error!! Error!! Error!! Error!! "
objErrorFile.close
Set objErrorFile = Nothing
Set objFSO = Nothing
End Function
Can any one give me the solution as early as possible, and your help is soo Appreciable
I am trying to import data from 4 columns in a spreadsheet, the Columns are (Last Name - First Name - ID - Code) and this spreadsheet has around 10k records. I want to add what is in this spreadsheet to the query I have below that uses the EXCEPT operator but I am not sure the best way to go about it.
Using the example I have filtered below for the name "Denise Test", at the end of the day I want everything that is in the spreadsheet to also be excluded from the results.
So before the spreadsheet lets say the 2nd query referencing table C has the following results for Denise Test
Last_Name First_Name ID Code
Test Denise 1 5
Test Denise 2 4
After adding the spreadsheet I want it to show this:
Last_Name First_Name ID Code
Test Denise 1 5
Test Denise 2 4
Test Denise 3 3
Here is the query as it stands now without the inclusion of the spreadsheet:
SELECT
ta.last_name,
ta.first_name,
tb.ID,
tb.code
FROM
TableA ta
INNER JOIN TableB tb
[code]....
Dear all,
Could you tell me which code of vb scrip that i can check file size as follow ?
Dim FSO, Fyl
Set FSO = CreateObject("Scripting.FileSystemObject")
Set Fyl = CreateObject("File")
If FSO.FileExists("C: emplcbseg.log") Then
Set Fyl = FSO.GetFile("C: emplcbseg.log")
If Fyl.Size > 0 Then
Main = DTSStepScriptResult_ExecuteTask
Else
Main = DTSStepScriptResult_DontExecuteTask
End If
End if
Thanks
Best regards,
Kusuma
This script generates a format file for every table in a database.
It's set up for fixed width files, not too common in this world...but they can easily be modified.
Also the do not at this time handle text or image columns.
Any hints/advice here would be great.
I'll post it when I figure it out.
happy bcping..
SELECT FORMAT_CARD FROM (
SELECT '7.0' AS FORMAT_CARD
, TABLE_NAME, null AS COLUMN_NAME, 1 AS SQLGroup, 1 AS RowGrouping
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_TYPE = 'BASE TABLE'
UNION ALL
SELECT CONVERT(varchar(5),MAX(ORDINAL_POSITION)) AS FORMAT_CARD
, c.TABLE_NAME, null AS COLUMN_NAME, 2 AS SQLGroup, 1 AS RowGrouping
FROM INFORMATION_SCHEMA.Columns c
INNER JOIN INFORMATION_SCHEMA.Tables t
ON c.TABLE_NAME = t.TABLE_NAME
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
AND TABLE_TYPE = 'BASE TABLE'
GROUP BY c.TABLE_NAME
UNION ALL
SELECT CONVERT(varchar(3),ORDINAL_POSITION)+CHAR(9)+'SQLCHAR'+CHAR(9)+'0'+CHAR(9)
+ CONVERT(varchar(5),
CASE WHEN DATA_TYPE IN ('char','varchar','nchar','nvarchar') THEN CHARACTER_MAXIMUM_LENGTH
WHEN DATA_TYPE = 'int' THEN 14
WHEN DATA_TYPE = 'smallint' THEN 7
WHEN DATA_TYPE = 'tinyint' THEN 3
WHEN DATA_TYPE = 'bit' THEN 1
ELSE 26
END)
+ CHAR(9)+'""'+CHAR(9)+CONVERT(varchar(3),ORDINAL_POSITION)+CHAR(9)+COLUMN_NAME AS FORMAT_CARD
, c.TABLE_NAME, null AS COLUMN_NAME, 3 AS SQLGroup, ORDINAL_POSITION AS RowGrouping
FROM INFORMATION_SCHEMA.Columns c
INNER JOIN INFORMATION_SCHEMA.Tables t
ON c.TABLE_NAME = t.TABLE_NAME
AND c.table_schema = t.table_schema
AND TABLE_TYPE = 'BASE TABLE'
WHERE ORDINAL_POSITION < (SELECT MAX(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.Columns i
WHERE i.TABLE_NAME = c.TABLE_NAME)
UNION ALL
SELECT CONVERT(varchar(3),ORDINAL_POSITION)+CHAR(9)+'SQLCHAR'+CHAR(9)+'0'+CHAR(9)+CONVERT(VARCHAR(5),
CASE WHEN DATA_TYPE IN ('char','varchar','nchar','nvarchar') THEN CHARACTER_MAXIMUM_LENGTH
WHEN DATA_TYPE = 'int' THEN 14
WHEN DATA_TYPE = 'smallint' THEN 7
WHEN DATA_TYPE = 'tinyint' THEN 3
WHEN DATA_TYPE = 'bit' THEN 1
ELSE 26
END)
+ char(9)+'"
"'+char(9)+CONVERT(varchar(3),ORDINAL_POSITION)+CHAR(9)+COLUMN_NAME AS FORMAT_CARD
, c.TABLE_NAME, null AS COLUMN_NAME, 4 AS SQLGroup, 1 AS RowGrouping
FROM INFORMATION_SCHEMA.Columns c
INNER JOIN INFORMATION_SCHEMA.Tables t
ON c.TABLE_NAME = t.TABLE_NAME
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
AND TABLE_TYPE = 'BASE TABLE'
WHERE ORDINAL_POSITION = (SELECT MAX(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.Columns i
WHERE i.TABLE_NAME = c.TABLE_NAME)
)AS XXX
ORDER BY TABLE_NAME, COLUMN_NAME, SQLGroup, RowGrouping
Brett
8-)
SELECT @@POST=NewId()
That's correct! It's an AlphaNumeric!
Hi,
I am developing database support for my application and I am using EDB. I would like to use sort orders to make the code easier but I was wondering if sort orders generate index tables. I mean, do they only sort the records during addition or they also generate index tables to speed up queries. In other words, do they make the database file bigger?
In the MSDN documentation I can not find anything on this.
Thanks,
Giulio
I have many new tables for which i need to write Insert,Update and delete triggers manually. Is there any way to generate triggers script which takes table name as a input parameter and print/generate trigger's script?
View 1 Replies View Related
Please help, i am really really struggling for a logic to handle 100's of reports we have via button click from asp.net webform.
in the button click i am constructing the url :
**************************************************************************************************************
http://localhost/reportserver?/MyReports/StatusReport&UserID=1&ContractID=1&subcode=null
*************************************************************************************************************
I have a table would like to maintain the parameters required for the chosen report:
when the user chooses from list box on the webform for StatusReport, immedeately it fetches the parameters related to Statusreport and gets everything which is stored with a space in between for each parameter, for this report i have 3 parameters:
UserID ContractID subcode
now how can i construct the string based on the above parameters , i am using vb.net as code behind for my webform(asp.net)
please any ideas will help me achieve the logic. please help thank you all very much.
I have created one table and one stored procedure for to insert/delete/update the data in that table.
So,I was trying to move the scripts from one database to another by Generating Scripts options in SQL Server.
Generating Scripts:
Object Explorer --> Databases --> Database --> Tasks --> Generate Scripts
The generated script output is in a order of stored procedure first and then table.
REQUIREMENT: My stored procedure is dependent on table. So, I need the table script first and then stored procedure.
Note: I can generate two separate scripts for table and stored procedure, But in a just curiosity to know, Is there any way, can we re order the Generate Scripts output in SQL Server.
Is there a way in SQL server that can generate stored procedures for select, insert, update, delete on certain tables?
View 4 Replies View RelatedI need to copy data from warehouse tables to master tables of different SQL instances. Refresh need to done once in an hour. What is the best way to do this? SQL agent jobs or SSIS packages?
View 3 Replies View RelatedI am needing to o exactly what Zippygoose says (with ordered ID numbers). How do yo make an insert statement that will make a loop until let's say the ID reaches 8000??
Thanks in advanced,
Edit:
User needs to fill his tables with sample data.
(Sorry for the edit, but I prefer to split this thread instead of continuing an old one (2003!!)).
Dear All,
I have some problems with the assignment from my professor.
Suppose there are two tables storing first names and last names, one table with column of first name and gender, the other with last name.
how to write stored procedures to get a random first name + random last name for Males or Female. like you got a list of all M & F first name and last name, then you try to match first name with any other last name (I believe this is random).
and the task is to write a script so each time when a female or male full name is need, it will return a random name for the requested gender.
I am totally struck and no idea how to deal with it, in fact I am just a beginning in SQL server, and I know there is a stored procedure to generate random number, however, I can't see the use of it here,,,,:eek:
Could anyone help me out ? thanks very much !!!:beer:
Hello,
I'm a network admin who understands sql but have no reason to write it everyday so I have no idea how to approach this problem.
I am trying to generate usage statistics of our computer labs. I have written a few scripts that populate a sql 2005 database with the following information:
[datetime] [username] [computer] [event]
2007-10-10 15:25:03 tom earth logon
2007-10-10 15:39:09 john mars logon
2007-10-10 15:41:13 dave pluto logoff
2007-10-10 15:47:29 john mars logoff
2007-10-10 15:59:48 tom earth logoff
Each time a user logs on or logs off one of our lab computers the information is inserted into this database.
It seems that was the easy part. Lacking any local sql experts, I'm pondering where to go from here to generate usage statistics.
What I'm thinking is a SQL query (which I don't know how I'd write) that would calculate the session time and insert it into a new table. A session is defined as the time between a logon and logoff where the username and computer match.
The new table would look something like this:
[user] | [computer] | [sessionTime] | [day]
tom | earth | 00:34:45 | 2007-10-10
john | mars | 00:08:20 | 2007-10-10
The logic of the query finds the first logon event and matches it with the first logoff event in which the username and computer match. It then subtracts the logon datetime from the logoff to calculate session time.
Is this a reasonable approach? Is the query easy to write?
Hi,
I have one problem in my project. Generating the report plz help me.
I have one table called Emp, which consists of fields, empid,tdate,attn,reason.
Emp
----
empid | tdate | attn | reason
2281 6/3/2006 Present null
2282 6/3/2006 Tour Hyderabad
2283 6/3/2006 Present null
2281 6/4/2006 Present null
2282 6/4/2006 Present null
2283 6/4/2006 Tour null
I want to generate a report as given below based on the date submitted by the user.
Frm Date: 6/3/2006 To Date: 6/4/2006
empid Present Tour Absent No.of working days
2281 2 2
2282 1 1 1
2283 1 1 1
Please write the query and reply back to me. ASAP.
Thanx in advance.
Hi,
Can Anyone tell me how to generate script with all table data from one database.I can generate script with table structure but not with table data.I want to generate with Table data.
Thanks,
Pavan
I have a question regarding generating SQL script from a database.
If you use SQL Server Management Express, it lets you generate SQL
script that can be used to recreate table structure, stored procedures,
etc. But it does not include the actual data stored in the tables.
What query should i use to generate a data of all days on last month, for example, if today is june so last month would be may.
View 3 Replies View RelatedHi
i have one problem in database to creating SQL script
i want to copy table strucure with table data
Pls help
Shailesh Kavathiya
http://www.codegroups.com/blog/
Hey,
I'm looking for a good way to rewrite my T-SQL code with 'bcp' to SSIS package, any help would be greatly appreciated?
I have table1 contain account numbers and output-filename for each account,
I need to join table2 and table3 to get data for each account in table1, and then export as a txt file.
Here is my code using bcp (bulk copy)
DECLARE @RowCnt int,
@TotalRows int,
@AccountNumber char(11),
@sql varchar(8000),
@date char(10),
@ArchPath varchar(500)
SET @RowCnt = 1
SET @date = CONVERT(CHAR(10),GETDATE(),110)
SET @ArchPath = '\D$EDATAWorkFoldersSendSendData'
SELECT @TotalRows = count(*) FROM table1
--select @ArchPath
WHILE (@RowCnt <= @TotalRows)
BEGIN
SELECT @AccountNumber = AccountNumber, @output_filename FROM table1 WHERE Identity_Number = @RowCnt
--PRINT @AccountNumber --test
SELECT @sql = N'bcp "SELECT h.HeaderText, d.RECORD FROM table2 d INNER JOIN table3 h ON d.HeaderID = h.HeaderID WHERE d.ccountNumber = '''
+ @AccountNumber+'''" queryout "'+@ArchPath+ @output_filename + '.txt" -T -c'
--PRINT @sql
EXEC master..xp_cmdshell @sql
SELECT @RowCnt = @RowCnt + 1
END
Hi.
Does anyone know if SQLServer 7.0 will generate dummy data for specific columns in tables?
TIA,
Jeff
It might be an old question but wanted to see, if we have any latest techniques (other than bcp).
SELECT Field1, Field2 FROM MyTable
If I want to export the output of the above query to a csv on a network folder? I would like to avoid usage of SSIS package or BCP (as user needs to get additional rights to execute bcp).
Hi
I have two below datacolumns
'code'- varchar 255 (Unique number) data : chr456Umx
'Packs'- integer data : 6
Is it posible to generate 13 digit number using the above two columns,
The reason is if I run the procedure I will get same 13 digit all the time depending on the above two colums
below is the sample procedure I am using
CREATE PROCEDURE AMZSelCen
@imglink nvarchar(255)
AS
Select code as sku,
PdtBarCode as [standard-product-id],
'EAN' as [product-id-type],
--generate 13 digit number
make+' '+model+' ' +', Price for '+cast(NumPacks as varchar(8)) +' '+'Packs' as title,
make as manufacturer,'
from tablename
where ......
Advance thanks